I was looking over my “generic” export a 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 columns for subtotals. To fix the problem they could use “Text to Columns”, but this was really a work-around to my lack of creativeness. I came up with an improved “ExportDataTableToExcel” function that will now default the data types Int/Double/Decimal into the NPOI Double type.
/// <summary>
/// Render DataTable to Excel File
/// </summary>
/// <param name="sourceTable">Source DataTable</param>
/// <param name="fileName">Destination File name</param>
public static void ExportDataTableToExcel(DataTable sourceTable, string fileName)
{
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream memoryStream = new MemoryStream();
HSSFSheet sheet = workbook.CreateSheet("Sheet1");
HSSFRow headerRow = sheet.CreateRow(0);
// Build Header
foreach (DataColumn column in sourceTable.Columns)
{
// Create New Cell
HSSFCell headerCell = headerRow.CreateCell(column.Ordinal);
// Set Cell Value
headerCell.SetCellValue(column.ColumnName);
// Create Style
HSSFCellStyle headerCellStyle = workbook.CreateCellStyle();
headerCellStyle.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
headerCellStyle.FillPattern = CellFillPattern.SOLID_FOREGROUND;
// Add Style to Cell
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);
HSSFRow additionalHeaderRow = sheet.CreateRow(0);
for (int h = 0; h < headerRow.LastCellNum; h++)
{
HSSFCell additionalHeaderColumn = additionalHeaderRow.CreateCell(h);
additionalHeaderColumn.CellStyle = headerRow.GetCell(h).CellStyle;
additionalHeaderColumn.SetCellValue(headerRow.GetCell(h).RichStringCellValue);
}
rowIndex = 1;
}
// Create new row in sheet
HSSFRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
{
switch (column.DataType.FullName)
{
case "System.String":
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
break;
case "System.Int":
case "System.Int32":
case "System.Int64":
case "System.Double":
case "System.Decimal":
dataRow.CreateCell(column.Ordinal).SetCellValue(Double.Parse(row[column].ToString()));
break;
default:
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
break;
}
}
rowIndex++;
}
for (int h = 0; h < headerRow.LastCellNum; h++)
{
sheet.AutoSizeColumn(h);
}
workbook.Write(memoryStream);
memoryStream.Flush();
HttpResponse response = HttpContext.Current.Response;
response.ContentType = "application/vnd.ms-excel";
response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", fileName));
response.Clear();
response.BinaryWrite(memoryStream.GetBuffer());
response.End();
}
The key part to look at above is the “switch (column.DataType.FullName)” block. This grabs the data type of the source column to use in the SetCellValue() call. The result is an Excel file with numeric data types formatted as numbers!
