public static void ExportToExcel(GridEX grid, HSSFWorkbook workbook, string sheetName)
{
var sheet = workbook.CreateSheet(sheetName);
var index = 0;
var hdr = sheet.CreateRow(index++);
for (int i = 0; i < grid.RootTable.Columns.Count; i++)
{
var cell = hdr.CreateCell(i);
cell.SetCellValue(grid.RootTable.Columns[i].Caption);
cell.CellStyle.WrapText = true;
cell.CellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
}
foreach (var r in grid.GetRows())
{
var sr = sheet.CreateRow(index++);
for (int i = 0; i < r.Cells.Count; i++)
{
if (r.Cells[i] == null || r.Cells[i].Value == null)
{
continue;
}
if (r.Cells[i].Value.GetType() == typeof(string))
{
var cell = sr.CreateCell(i);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue((string)r.Cells[i].Value);
}
else if (r.Cells[i].Value.GetType() == typeof(short))
{
var cell = sr.CreateCell(i);
cell.SetCellType(NPOI.SS.UserModel.CellType.Numeric);
cell.SetCellValue((short)r.Cells[i].Value);
}
else if (r.Cells[i].Value.GetType() == typeof(int))
{
var cell = sr.CreateCell(i);
cell.SetCellType(NPOI.SS.UserModel.CellType.Numeric);
cell.SetCellValue((int)r.Cells[i].Value);
}
else if (r.Cells[i].Value.GetType() == typeof(double))
{
var cell = sr.CreateCell(i);
cell.SetCellType(NPOI.SS.UserModel.CellType.Numeric);
cell.SetCellValue((double)r.Cells[i].Value);
}
else
{
sr.CreateCell(i).SetCellValue(r.Cells[i].Text);
}
sheet.AutoSizeColumn(i);
}
}
}