Exporting a .NET DataSet to an Excel Workbook

Introduction

The code explained in this article exports a DataSet to an Excel Workbook, one worksheet per DataTable within the DataSet.

Background

None really, just a nice neat/handy way of dumping out a DataSet to an Excel spreadsheet. I find it helps when trying to debug/investigate data errors.

Using the code

Here it is, in all its glory. The generateIdentity boolean is used to force/insert an IDENTITY field over the top of the first column, so you must ensure that the IDENTITY column is defined in the table, but not populated - however, it would be quite easy to INSERT this column with minor changes to the code. Please note that you need to reference the Microsoft Excel 11.0 Object Library in your project.

using Office = Microsoft.Office.Core;
using Excel;

[...]

private static void DataSetToExcel(DataSet ds, Boolean generateIdentity)
{
    Excel.Application xlApp = new Excel.Application();
    xlApp.Visible = false;
    Workbook wb = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
    for (int k = 0; k < ds.Tables.Count; k++)
    {
        System.Data.DataTable dt = ds.Tables[k];
        Worksheet ws = (Worksheet) wb.Worksheets.Add(Missing.Value, 
                        Missing.Value, Missing.Value, Missing.Value);
        ws.Name = dt.TableName;

        for (int i = 0; i < dt.Rows.Count; i++)
        {
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                if (i == 0)
                    ws.Cells[1, j + 1] = dt.Columns[j].ColumnName;

                ws.Cells[i + 2, j + 1] = (j == 0 && generateIdentity) ? 
                                         (i + 1).ToString() : dt.Rows[i][j].ToString();
            }
        }
    }
    xlApp.Visible = true;
}

History

• Version 0.1 - First release, comments welcome! Especially if you don't like the back-to-front way that the Worksheets are added to the Workbook; any advice there humbly and gratefully received.