Introduction
This article describes exporting a DataGrid cell value to an Excel sheet. It is very easy to do that, you just need to use a small procedure. In this article I have used the Microsoft.Office.Interop.Excel namespace to export data. Microsoft.Office.Interop.Excel is a namespace provided with Visual Studio 2005 and later.
The namespace Microsoft.Office.Interop.Excel is already integrated into Visual Studio, to use the namespace you need to provide a reference for this namespace. Use the following procedure to get a reference of the namespace.
- Right-click on your application
- Go to Add Reference and click on that.
- The Reference Manager window will open, select Framework from the left side panel and select Microsoft.Office.Interop.Excel from the listbox.
Getting Started
- Fill in the DataGrid with items
- Create an object of Excel application
- Create an object of Workbook
- Create an object of WorkSheet
- Create header of WorkSheet
- Insert data into the cell
- Save and close the Excel application and remove COM references
1. Fill DataGrid
Take a DataGrid control from the Tools panel and set the item's Source to what you are going to export.
2. Create an Object Of Excel Application
Create an object of the Application class in the Microsoft.Office.Interop.Excel namespace as in the following:
public Microsoft.Office.Interop.Excel.Application APP= new Microsoft.Office.Interop.Excel.Application();
3. Create an object of Workbook.
Using the WorkBook class, create a WorkBook object containing a worksheet and assign a workbook from the Application object to this object.
public Microsoft.Office.Interop.Excel.Workbook WB = APP.Workbooks.Add(1);
4. Create an object of WorkSheet.
Create an object of the WorkSheet class and again assign a worksheet from the workbook object created above to this worksheet object, here I assigned the first worksheet to this object.
this.WS = (Microsoft.Office.Interop.Excel.Worksheet)WB.Sheets[1];
5. Create a header of WorkSheet
Retrieve all the column headers from the DataGrid to create headers of the Excel Sheet. Using LINQ you can retrieve header text from the DataGrid Control. List all header text and use a for loop to set the header as in the following code.
int ind=1;
foreach (object ob in this.dgvFields.Columns.Select(cs => cs.Header).ToList())
{
this.WS.Cells[1, ind] = ob.ToStrihng();
ind++;
}
6. Insert Data into Cell
As in the code above, using a foreach loop retrieve all the items of the datagrid from the itemsource and insert it into the Excel sheet cells one by one, but here the data insertion starts from the second row.
ind= 2;
foreach (Field field in dgvFields.ItemsSource)
{
DataRow DR = DRV.Row;
for (int ind1 = 1; ind1 <= dgvFields.Columns.Count; ind1++)
{
WS.Cells[ind][ind1] = DR[ind1 - 1];
}
ind++;
}
7. Save and close Excel application and remove COM references
After inserting data to the Excel cells, save the Excel application, close and remove COM objects from the process, you can remove the objects using the Marshal class. See the code below.
if (this.APP.ActiveWorkbook != null)
this.APP.ActiveWorkbook.Save();
if (this.APP != null)
{
if (this.WB != null)
{
if (this.WS != null)
Marshal.ReleaseComObject(this.WS);
this.WB.Close(false, Type.Missing, Type.Missing);
Marshal.ReleaseComObject(this.WB);
}
this.APP.Quit();
Marshal.ReleaseComObject(this.APP);
}
Full Code
-------------
XAML
------------
- public class MyExcel
- {
- public Microsoft.Office.Interop.Excel.Application APP = null;
- public Microsoft.Office.Interop.Excel.Workbook WB = null;
- public Microsoft.Office.Interop.Excel.Worksheet WS = null;
- public Microsoft.Office.Interop.Excel.Range Range = null;
-
- public MyExcel()
- {
- this.APP = new Microsoft.Office.Interop.Excel.Application();
- this.Open("C:\\MyExcel.xlsx","Sheet1");
- this.CreateHeader();
- this.InsertDate()
- this.Close();
- }
-
- private void Open(string Location, int workSheet)
- {
- this.WB= this.APP.Workbooks.Open(Location);
- this.WS = (Microsoft.Office.Interop.Excel.Worksheet)WB.Sheets[workSheet];
- return this.WS;
- }
- private void CreateHeader()
- {
- int ind=1;
- foreach (object ob in this.dgvFields.Columns.Select(cs => cs.Header).ToList())
- {
- this.WS.Cells[1, ind] = ob.ToStrihng();
- ind++;
- }
- }
- private void InsertData()
- {
- ind= 2;
- foreach (Field field in dgvFields.ItemsSource)
- {
- DataRow DR = DRV.Row;
- for (int ind1 = 1; ind1 <= dgvFields.Columns.Count; ind1++)
- {
- WS.Cells[ind][ind1] = DR[ind1 - 1];
- }
- ind++;
- }
- }
- private void Close()
- {
- if (this.APP.ActiveWorkbook != null)
- this.APP.ActiveWorkbook.Save();
- if (this.APP != null)
- {
- if (this.WB != null)
- {
- if (this.WS != null)
- Marshal.ReleaseComObject(this.WS);
- this.WB.Close(false, Type.Missing, Type.Missing);
- Marshal.ReleaseComObject(this.WB);
- }
- this.APP.Quit();
- Marshal.ReleaseComObject(this.APP);
- }
- }
Conclusion
In this article we learn how to export data from a WPF Data Grid to a Microsoft Excel Sheet using the namespace Microsoft.Office.Interop.Excel.