Click here to Skip to main content
Click here to Skip to main content

Exporting a .NET DataSet to an Excel Workbook

, 12 Feb 2009
Rate this:
Please Sign up or sign in to vote.
How to export 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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Mister Bee

United Kingdom United Kingdom
No Biography provided

Comments and Discussions

 
GeneralAlternative Pinmembergg423715-Nov-09 23:22 
GeneralMy vote of 1 PinmemberPavel Vladov13-Feb-09 3:01 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140827.1 | Last Updated 12 Feb 2009
Article Copyright 2009 by Mister Bee
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid