I was looking over my generic export DataTable to Excel function the other day and noticed an issue. My default method was throwing everything into a string format, which was preventing users from highlighting number columns for subtotals. To fix the problem they could use “Text to Columns”, but this was becoming a common complaint I wanted to resolve. I came up with an improved “ExportToExcel” function that will insert the correct data type.
/// <summary>
/// Render DataTable to Excel File
/// </summary>
/// <param name = "sourceTable">Source DataTable</param>
/// <param name = "fileName">Destination File Name</param>
public static void ExportToExcel(DataTable sourceTable, string fileName)
{
HSSFWorkbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.CreateSheet("Sheet1");
Row headerRow = sheet.CreateRow(0);
// Create Header Style
CellStyle headerCellStyle = workbook.CreateCellStyle();
headerCellStyle.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
headerCellStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
// Create Date Style
CellStyle dateCellStyle = workbook.CreateCellStyle();
dateCellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy");
// Build Header
foreach (DataColumn column in sourceTable.Columns)
{
Cell headerCell = headerRow.CreateCell(column.Ordinal);
headerCell.SetCellValue(column.ColumnName);
headerCell.CellStyle = headerCellStyle;
}
// Build Details (rows)
int rowIndex = 1;
int sheetIndex = 1;
const int maxRows = 65536;
foreach (DataRow row in sourceTable.Rows)
{
// Start new sheet max rows reached
if (rowIndex % maxRows == 0)
{
// Auto size columns on current sheet
for (int h = 0; h < headerRow.LastCellNum; h++)
{
sheet.AutoSizeColumn(h);
}
sheetIndex++;
sheet = workbook.CreateSheet("Sheet" + sheetIndex);
Row additionalHeaderRow = sheet.CreateRow(0);
for (int h = 0; h < headerRow.LastCellNum; h++)
{
Cell additionalHeaderColumn = additionalHeaderRow.CreateCell(h);
additionalHeaderColumn.CellStyle = headerRow.GetCell(h).CellStyle;
additionalHeaderColumn.SetCellValue(headerRow.GetCell(h).RichStringCellValue);
}
rowIndex = 1;
}
// Create new row in sheet
Row dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
{
Cell dataCell = dataRow.CreateCell(column.Ordinal);
switch (column.DataType.FullName)
{
case "System.String":
dataCell.SetCellValue(row[column].ToString());
break;
case "System.Int":
case "System.Int32":
case "System.Int64":
case "System.Double":
case "System.Decimal":
double val;
dataCell.SetCellValue(Double.TryParse(row[column].ToString(), out val) ? val : 0);
break;
case "System.DateTime":
DateTime dt = new DateTime(1900, 01, 01);
DateTime.TryParse(row[column].ToString(), out dt);
dataCell.SetCellValue(dt);
dataCell.CellStyle = dateCellStyle;
break;
default:
dataCell.SetCellValue(row[column].ToString());
break;
}
}
rowIndex++;
}
for (int h = 0; h < headerRow.LastCellNum; h++)
{
sheet.AutoSizeColumn(h);
}
ExportToExcel(workbook, fileName);
}
The key part of the function above to review is the “switch (column.DataType.FullName)” code block. This grabs the DataTable’s column data type to use in the SetCellValue() call.
Once the workbook is built, we call a overload of the same function that expects a NPOI workbook. This overload will send a XLS file back to the user via their web browser using the HttpResponse stream.
/// <summary>
/// Render Excel File to HttpResponse (Browser)
/// </summary>
/// <param name="workbook">NPOI Workbook</param>
/// <param name="fileName">Destination File Name</param>
public static void ExportToExcel(HSSFWorkbook workbook, string fileName)
{
using (MemoryStream memoryStream = new MemoryStream())
{
workbook.Write(memoryStream);
memoryStream.Flush();
try
{
HttpResponse response = HttpContext.Current.Response;
response.ClearContent();
response.ClearHeaders();
response.Buffer = true;
response.ContentType = "application/vnd.ms-excel";
response.AddHeader("Content-Length", memoryStream.Length.ToString());
response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", fileName));
response.BinaryWrite(memoryStream.GetBuffer());
response.Flush();
response.End();
}
catch
{
// Do nothing, error expected due to Flush();
}
}
}
I’ve used the above function to send files with 3-4 sheets full of data back to the browser… but the file size is usually “VERY LARGE”. To work around this, I have a helper function which will add the excel file to a zip (in memory) before sending back to the client.
In my last test, a 17.5MB document with 2 sheets of data was compressed to 3.5MB with my zip utility. There was still a ~13 second delay to generate the XLS file, but once prompted to download form the browser the file download completed in a few seconds.
