Export an ADO.NET DataTable to Excel using NPOI

My latest article on how to create Excel spreadsheets using NPOI. NPOI is a free, open-source .NET library for creating and reading Excel spreadsheets and is a port of the Java POI library. In the article I show how to use NPOI to programmatically export data into a spreadsheet with multiple sheets, formatting, and so on. Specifically, my demos look at having a set of objects to export – for example, a set of Linq-to-Sql entity objects – and then crafting an Excel spreadsheet by enumerating those objects and adding applicable rows and columns to the spreadsheet.

Recently, I needed the ability to allow for more generic exports to Excel. In one of the web applications I work on there is an Excel Export page that offers a number of links that, when clicked, populate an ADO.NET DataTable with the results of a particular database view, generate a CSV file, and then stream that file down to the client with a Content-Type of application/vnd.ms-excel, which prompts the browser to display the CSV content in Excel. This has worked well enough over the years, but unfortunately such data cannot be viewed from the iPad; however, the iPad can display a native Excel file (.xls). The solution, then, was to update the code to use NPOI to return an actual Excel spreadsheet rather than a CSV file.

To accomplish this I wrote a bit of code that exports the contents of any ol’ DataTable into an Excel spreadsheet using NPOI. It’s pretty straightforward, looping through the rows of the DataTable and adding each as a row to the Excel spreadsheet. There were, however, a couple of gotcha points:

1. Excel 2003 limits a sheet inside a workbook to a maximum of 65,535 rows. To export more rows than this you need to use multiple sheets.  NPOI and the Excel 2003 Row Limit, provided a simple approach to avoiding this problem. In short, I keep track of how many rows I’ve added to the current sheet and once it exceeds a certain threshold I create a new sheet and start from the top.
2. Excel has limits and restrictions on the length of sheet names and what characters can appear in a sheet name. I have a method named EscapeSheetName that ensures the sheet name is of a valid length and does not contain any offending characters.
3. When exporting very large Excel spreadsheets you may bump into OutOfMemoryExceptions if you are developing on a 32-bit system and are trying to dump the Excel spreadsheet into a MemoryStream object, which is a common technique for streaming the data to the client. See this Stackoverflow discussion for more information and possible workarounds: OutOfMemoryException When Generating a Large Excel Spreadsheet.

To demonstrate exporting a DataTable to Excel using NPOI, I augmented the code demo available for download from my DotNetSlackers article to include a new class in the App_Code folder named NPoiExport, which you can download from http://scottonwriting.net/demos/ExcelExportToDataTable.zip. This class offers anExportDataTableToWorkbook method that takes as input a DataTable and the sheet name to use for the Excel workbook. (If there are multiple sheets needed, the second sheet is named “sheetName – 2,” the third, “sheetName – 3,” and so forth.)

The ExportDataTableToWorkbook method follows:

01	public void ExportDataTableToWorkbook(DataTable exportData, string sheetName)

02

{

03	// Create the header row cell style

04	var headerLabelCellStyle = this.Workbook.CreateCellStyle();

05	headerLabelCellStyle.BorderBottom = CellBorderType.THIN;

06	var headerLabelFont = this.Workbook.CreateFont();

07	headerLabelFont.Boldweight = (short)FontBoldWeight.BOLD;

08	headerLabelCellStyle.SetFont(headerLabelFont);

09

10	var sheet = CreateExportDataTableSheetAndHeaderRow(exportData, sheetName, headerLabelCellStyle);

11	var currentNPOIRowIndex = 1;

12	var sheetCount = 1;

13

14	for (var rowIndex = 0; rowIndex < exportData.Rows.Count; rowIndex++)

15

{

16	if (currentNPOIRowIndex >= MaximumNumberOfRowsPerSheet)

17

{

18	sheetCount++;

19	currentNPOIRowIndex = 1;

20

21	sheet = CreateExportDataTableSheetAndHeaderRow(exportData,

22	sheetName + " - " + sheetCount,

23	headerLabelCellStyle);

24

}

25

26	var row = sheet.CreateRow(currentNPOIRowIndex++);

27

28	for (var colIndex = 0; colIndex < exportData.Columns.Count; colIndex++)

29

{

30	var cell = row.CreateCell(colIndex);

31	cell.SetCellValue(exportData.Rows[rowIndex][colIndex].ToString());

32

}

33

}

34

}

Whenever a new sheet needs to be generated – either when starting or when the maximum number of rows per sheet is exceeded – the CreateExportDataTableSheetAndHeaderRow method is called. This method creates a header row, listing the name of each column in the DataTable.

01	protected Sheet CreateExportDataTableSheetAndHeaderRow(DataTable exportData, stringsheetName, CellStyle headerRowStyle)

02

{

03	var sheet = this.Workbook.CreateSheet(EscapeSheetName(sheetName));

04

05	// Create the header row

06	var row = sheet.CreateRow(0);

07

08	for (var colIndex = 0; colIndex < exportData.Columns.Count; colIndex++)

09

{

10	var cell = row.CreateCell(colIndex);

11	cell.SetCellValue(exportData.Columns[colIndex].ColumnName);

12

13	if (headerRowStyle != null)

14	cell.CellStyle = headerRowStyle;

15

}

16

17	return sheet;

18

}

Here’s how you would go about using the NpoiExport class to export a DataTable and then stream it down to the client:

1. Create and populate the DataTable with the data to export. Remember, the DataTable’s column names are what will appear in the header row so use aliases in the SQL query to provide more description/formatted column names, if you prefer.
2. Create an instance of the NpoiExport class.
3. Call the object’s ExportDataTableToWorkbook method passing in the DataTable from step 1 (along with a sheet name of your choice).
4. Set the Content-Type and Content-Disposition response headers appropriately and then stream down the contents of the Excel document, which is accessible via the NpoiExport object’s GetBytes method.

The following code snippet illustrates the above four steps.

01	// Populate the DataTable

02	var myDataTable = new DataTable();

03	using (var myConnection = new SqlConnection(connectionString)

04

{

05	using (var myCommand = new SqlCommand())

06

{

07	myCommand.Connection = myConnection;

08	myCommand.CommandText = sqlQuery;

09

10	using (var myAdapter = new SqlDataAdapter(myCommand))

11

{

12	myAdapter.Fill(myDataTable);

13

}

14

}

15

}

16

17

18	// Creat the NpoiExport object

19	using (var exporter = new NpoiExport())

20

{

21	exporter.ExportDataTableToWorkbook(myDataTable, "Results");

22

23	string saveAsFileName = string.Format("Results-{0:d}.xls", DateTime.Now);

24

25	Response.ContentType = "application/vnd.ms-excel";

26	Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));

27	Response.Clear();

28	Response.BinaryWrite(exporter.GetBytes());

29	Response.End();

30

}

That’s all there is to it. The screen shot below shows an example of the exported report. Note that it lacks the nice formatting, auto-sized columns, and other bells and whistles that are possible with NPOI when constructing a report by hand (as I showed in Create Excel Spreadsheets Using NPOI), but it does make exporting data to Excel an exercise of just a few lines of code. And its exported data that can be opened and viewed from an iPad.

The above Excel spreadsheet was created using the ad-hoc query:

1	SELECT CategoryName AS [Category],

2	Description,

3	(SELECT COUNT(*)

4	FROM Products

5	WHERE Products.CategoryID = Categories.CategoryID)

6	AS [Product Count]

7	FROM Categories

8	WHERE CategoryID >= 3

Happy Programming!