文本:@
日期:yyyy/mm/dd
数字:#,##0.00
货币:¥#,##0.00
百分比:#0.00%
public static MemoryStream ExportToExcelText(DataTable table)
{
MemoryStream ms = new MemoryStream();
IWorkbook workbook = new HSSFWorkbook();
IDataFormat dataformat = workbook.CreateDataFormat();
ICellStyle style0 = workbook.CreateCellStyle();
style0.DataFormat = dataformat.GetFormat("@");//文本格式
int rowIndex = 1;
ISheet sheet = null;
int n = 1;
int count = table.Rows.Count;
if (count == 0)
{//没有数据导出表头
sheet = workbook.CreateSheet("Sheet1");//创建sheet页
IRow headerRow = sheet.CreateRow(0);
foreach (DataColumn column in table.Columns)//表头
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);//If Caption not set, returns the ColumnName value
}
}
else
{
foreach (DataRow row in table.Rows)//循环数据
{
if (rowIndex == 1 || rowIndex % 60000 == 0)
{
sheet = workbook.CreateSheet("Sheet" + n);//创建sheet页
IRow headerRow = sheet.CreateRow(0);
foreach (DataColumn column in table.Columns)//表头
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);//If Caption not set, returns the ColumnName value
}
n++;
rowIndex = 1;
}
IRow dataRow = sheet.CreateRow(rowIndex);//行
foreach (DataColumn column in table.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
dataRow.GetCell(column.Ordinal).CellStyle = style0;//每行单元格格式都为文本格式
}
rowIndex++;
}
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return ms;
}