Writing Data from a DataTable to Excel
Posted
Thu, Jul 23 2009 13:24
by
Deborah Kurata
As with most things in Visual Studio, there are many ways to export data from your .NET application to an Excel spreadsheet. This post covers one straightforward technique.
First, set a reference to the desired version of the Microsoft Excel Object Library from the COM tab of the Add Reference dialog. The resulting reference appears as Microsoft.Office.Interop.Excel.
Import the Microsoft.Office.Interop.Excel library in your code file. To keep the namespaces clear, consider using an alias.
In C#:
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
NOTE: C# also requires a reference to System.Reflection to use the Missing.Value field. This is required because C# 3.0 does not support optional parameters. Every parameter must have a value, so Missing.Value is used to provide a value for all unspecified optional parameters.
In VB:
Imports Excel = Microsoft.Office.Interop.Excel
The rest of the code is presented in VB and C# and then discussed below. It is provided in one big chunk to make it easier to copy/paste into your application.
In C#:
Excel.Application oXL;
Excel.Workbook oWB;
Excel.Worksheet oSheet;
Excel.Range oRange;
// Start Excel and get Application object.
oXL = new Excel.Application();
// Set some properties
oXL.Visible = true;
oXL.DisplayAlerts = false;
// Get a new workbook.
oWB = oXL.Workbooks.Add(Missing.Value);
// Get the active sheet
oSheet = (Excel.Worksheet)oWB.ActiveSheet ;
oSheet.Name = "Customers";
// Process the DataTable
// BE SURE TO CHANGE THIS LINE TO USE *YOUR* DATATABLE
DataTable dt = Customers.RetrieveAsDataTable();
int rowCount = 1;
foreach (DataRow dr in dt.Rows)
{
rowCount += 1;
for (int i = 1; i < dt.Columns.Count+1; i++)
{
// Add the header the first time through
if (rowCount==2)
{
oSheet.Cells[1, i] = dt.Columns[i - 1].ColumnName;
}
oSheet.Cells[rowCount, i] = dr[i - 1].ToString();
}
}
// Resize the columns
oRange = oSheet.get_Range(oSheet.Cells[1, 1],
oSheet.Cells[rowCount, dt.Columns.Count]);
oRange.EntireColumn.AutoFit();
// Save the sheet and close
oSheet = null;
oRange = null;
oWB.SaveAs("test.xls", Excel.XlFileFormat.xlWorkbookNormal,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Excel.XlSaveAsAccessMode.xlExclusive,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
oWB.Close(Missing.Value, Missing.Value, Missing.Value);
oWB = null;
oXL.Quit();
// Clean up
// NOTE: When in release mode, this does the trick
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
In VB:
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRange As Excel.Range
' Start Excel and get Application object.
oXL = New Excel.Application
' Set some properties
oXL.Visible = True
oXL.DisplayAlerts = False
' Get a new workbook.
oWB = oXL.Workbooks.Add
' Get the active sheet
oSheet = DirectCast(oWB.ActiveSheet, Excel.Worksheet)
oSheet.Name = "Customers"
' Process the DataTable
'BE SURE TO CHANGE THIS LINE TO USE *YOUR* DATATABLE
Dim dt As Data.DataTable = Customers.RetrieveAsDataTable
' Create the data rows
Dim rowCount As Integer = 1
For Each dr As DataRow In dt.Rows
rowCount += 1
For i As Integer = 1 To dt.Columns.Count
' Add the header the first time through
If rowCount = 2 Then
oSheet.Cells(1, i) = dt.Columns(i - 1).ColumnName
End If
oSheet.Cells(rowCount, i) = dr.Item(i - 1).ToString
Next
Next
' Resize the columns
oRange = oSheet.Range(oSheet.Cells(1, 1), _
oSheet.Cells(rowCount, dt.Columns.Count))
oRange.EntireColumn.AutoFit()
' Save the sheet and close
oSheet = Nothing
oRange = Nothing
oWB.SaveAs("test.xls")
oWB.Close()
oWB = Nothing
oXL.Quit()
' Clean up
' NOTE: When in release mode, this does the trick
GC.WaitForPendingFinalizers()
GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()
NOTE: The VB code code above is set up to run with Option Strict ON.
The code begins by declaring the variables used in the code. Notice that in both C# and in VB this makes use of the namespace alias as shown at the very beginning of this post.
The code then starts Excel and makes it visible. If you want to create the spreadsheet without making Excel visible to the user, you could set the Visible to false instead.
A workbook is added, the active sheet of the workbook is referenced, and the sheet is given a name.
The DataTable is then processed. You can use any DataTable here. The code loops through the rows of the DataTable and for each row it loops through the columns. The first time through the columns it adds column headings.
To make the spreadsheet easier to read, the columns are then resized to show the contents of the cells. If you have lots of data in a cell, you may want to skip this step.
The workbook is then saved. Since no directory was specified, Excel will save the file to your My Documents folder. Notice the massive number of parameters in the C# code when calling the SaveAs (and Close) methods. This is required because C# does not have optional parameters. So the code must fill in each and every parameter in the call. VB does support optional parameters, so does not need to set the extra parameters.
NOTE: C# is getting optional parameters in .NET 4.0 (VS 2010).
Finally, the code is cleaned up. Since Excel is accessed through COM interop, the double garbage collection code is added to ensure Excel is correctly closed. See this link for more information on why this specific garbage collection code is recommended.
Enjoy!