The code explained in this article exports a
DataSet to an Excel Workbook, one worksheet per
DataTable within the
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;
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.