Deborah's Developer MindScape






         Tips and Techniques for Web and .NET developers.

July 23, 2009

Writing Data from a DataTable to Excel

Filed under: C#,Data,Microsoft Excel,VB.NET @ 3:24 pm

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!

72 Comments

1 2 3 8
  1.   Mark Wisecarver — July 23, 2009 @ 6:13 pm    Reply

    Awesome. Whatever we can do to keep you rolling please do…You’re kicking this stuff out at an unbelievable rate and it’s all uber cool. Thanks!

  2.   Paul Caesar — August 7, 2009 @ 4:10 pm    Reply

    Deborah,

    I’ve been looking for this code for days. You are my new hero! You saved me hours of effort in making this work.

  3.   Ken — September 9, 2009 @ 8:06 am    Reply

    Love the code; it works great with my code to pull XML data from a website into a dataset. One question though. If I want to leave the Excel file open so I can do further work on it, how do I do so with out leaving garbage out there?

  4.   Marton Toth — September 24, 2009 @ 5:30 pm    Reply

    Hi,

    I use Excel 2008 on Mac. It does not enable the use of macros. I am not familiar with Applescript or C#.
    My question is pretty similar and I wonder if it can be solved without the use of macros. I have several columns of data (heading is constant, length of columns could differ), which has to be transformed to fit to a statistical software so that each cell in the column produces as many cells beneath in the same column as the value of the cell (not negative integer). The value of the newly generated cells are the sequential number of the original cell in the column (except for the heading).

    Eg:
    XY
    0
    0
    1
    3
    4
    8

    This column should produce altogether 16 cells in the same column (or new spreadsheet) 1 with the value “3”, 3 with the value “4”, 4 with the value “5” and 8 with the value “6” (the sequential number could be read out form an other parallel column as well).
    I would very much appreciate if you could help solving ths frustrating problem.

    M.

  5.   DeborahK — September 25, 2009 @ 12:04 pm    Reply

    Hi Marton –

    My blog primarily focuses on .NET development. You may want to try posting your question to an Excel forum.

    Good luck!

  6.   Manu — October 5, 2009 @ 12:59 am    Reply

    Please tell me what is this “missing” field ??

  7.   DeborahK — October 5, 2009 @ 10:40 am    Reply

    Hi Manu –

    Thank you for stopping by the blog. Missing.Value is needed in C# because C# 3.0 does not support optional parameters.

    Here is a link to the msdn documentation on Missing.Value:

    http://msdn.microsoft.com/en-us/library/system.reflection.missing.value.aspx

  8.   noel — October 9, 2009 @ 3:19 pm    Reply

    try to use the code as it but i get an undeline error on “Customers” name “Customers” is not declared

  9.   DeborahK — October 9, 2009 @ 6:02 pm    Reply

    Hi Noel –

    Are you referring to this line?

    Dim dt As Data.DataTable = Customers.RetrieveAsDataTable

    As I mentioned in the description following the code, you can use any DataTable. In my example, the DataTable came from my Customers class. You will have to change the right side of this equation to reference your table from whereever it is.

    Hope this helps.

  10.   Manu — October 16, 2009 @ 5:14 am    Reply

    To Deborah Kurata

    Thanks a lot… I really appreciate your effort..

    this blog was really helpful..

1 2 3 8

RSS feed for comments on this post. TrackBack URI

Leave a comment

© 2019 Deborah's Developer MindScape   Provided by WPMU DEV -The WordPress Experts   Hosted by Microsoft MVPs