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.   John C — October 4, 2012 @ 12:06 pm    Reply

    Hi Deborah

    Thanks for your reply.

    I followed the instructions from the link above and I have managed to get it to work. 🙂

    What I was actually trying to do was access the dataset that was produced when I followed the steps to connect to my Database using the Datasource Configuration Wizard.
    The file it produces ends with the extension “.xsd”. It contains the tables (table-adaptors?) from my database and allows you to build query’s within each datatable. I was hoping that there was a way of calling the query’s from the datatable to extract the data and insert it into an Excel sheet the same as your example above?

    Your example above is excellent, and transfers the data really quickly!

    I encountered an issue with sending dates to Excel, I’m aware that the date value is converted to a string value, is there an easy way for me to send the date as a “short date” and not display the time as well? EG: 10/09/2010 00:00:00. Do I need to send it as a datevalue?

    Thanks for steering me in the right direction, your help is much appreciated.

    John C

  2.   John C — October 5, 2012 @ 10:35 am    Reply

    Hi Deborah

    I managed to sort out the date format, I removed the “.toString” and it now puts the date into the sheet in the same format as the database.

    John C

  3.   djiesoft — March 25, 2013 @ 4:13 am    Reply

    how if the file size is big ? or row is more than 65000 ?
    it will takes long time

  4.   Anu — July 5, 2013 @ 8:33 am    Reply

    Thanks a ton! The article was of great help!!

  5.   RK — July 15, 2013 @ 2:24 am    Reply

    Hi Deborah,
    In the above code how can i open the excel and prompt the user to save it at his intended path instead of passing a hardcoded path.

  6.   Saeid Rasouli — July 16, 2013 @ 2:58 pm    Reply

    hello
    after searching more than 30 websites your code helped me so much, it was clear and useful.

    thank you

  7.   Jessica — September 24, 2013 @ 12:59 pm    Reply

    OMG you are you are awesome!!

    i love your code =)

    Thank you very much

  8.   Debopam — October 31, 2013 @ 2:44 am    Reply

    Useful article…Thank You.

  9.   Asres — January 21, 2014 @ 1:16 am    Reply

    I need it Please Help me…..

  10.   el wahyu — March 3, 2014 @ 10:12 pm    Reply

    Nice Tutorial It Works.

    By the way how to format date value in order to not showing minutes and second just regular day, month and year as “dd-MM-yyy”

    Thanks.

RSS feed for comments on this post. TrackBack URI

Leave a comment

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