Click here to Skip to main content
12,820,521 members (28,743 online)
Click here to Skip to main content
Add your own
alternative version


14 bookmarked
Posted 12 Feb 2009

Exporting a .NET DataSet to an Excel Workbook

, 12 Feb 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
How to export a .NET DataSet to an Excel Workbook.


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


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;


  • 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.


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


About the Author

Mister Bee
United Kingdom United Kingdom
No Biography provided

You may also be interested in...

Comments and Discussions

GeneralAlternative Pin
gg423716-Nov-09 0:22
membergg423716-Nov-09 0:22 
GeneralMy vote of 1 Pin
Pavel Vladov13-Feb-09 4:01
memberPavel Vladov13-Feb-09 4:01 

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

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170308.1 | Last Updated 12 Feb 2009
Article Copyright 2009 by Mister Bee
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid