NPOI是指构建在POI 3.x版本之上的一个程序,NPOI可以在没有安装Office的情况下对Word或Excel文档进行读写操作。
将DataSet导出EXCEL
public static void GetExcelByDataSet(DataSet ds, string file)
{
IWorkbook fileWorkbook = new HSSFWorkbook();
ICellStyle cellStyle = fileWorkbook.CreateCellStyle();
//设置单元格上下左右边框线
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//文字水平和垂直对齐方式
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
//是否换行
//cellStyle.WrapText = true;
//缩小字体填充
//cellStyle.ShrinkToFit = false;
ICellStyle headCellStyle = fileWorkbook.CreateCellStyle();
headCellStyle.CloneStyleFrom(cellStyle);
//head
int index = 0;
foreach (DataTable dt in ds.Tables)
{
index++;
ISheet sheet = fileWorkbook.CreateSheet("Sheet" + index);
//表头
IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
cell.CellStyle = cellStyle;
sheet.SetColumnWidth(i, 5000);
}
//数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
cell.CellStyle = cellStyle;
//自动尺寸,加上这个的话,大数据导出时速度要拖慢很多。
// sheet.AutoSizeColumn(j);
}
}
// for (int columnNum = 0; columnNum <= dt.Rows.Count; columnNum++)
// {
// int columnWidth = sheet.GetColumnWidth(columnNum) / 256;
// for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++)
// {
// IRow currentRow;
// //当前行未被使用过
// if (sheet.GetRow(rowNum) == null)
// {
// currentRow = sheet.CreateRow(rowNum);
// }
// else
// {
// currentRow = sheet.GetRow(rowNum);
// }
// if (currentRow.GetCell(columnNum) != null)
// {
// ICell currentCell = currentRow.GetCell(columnNum);
// int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
// if (columnWidth < length)
// {
// columnWidth = length;
// }
// }
// }
// sheet.SetColumnWidth(columnNum, columnWidth * 256);
// }
}
//转为字节数组
MemoryStream stream = new MemoryStream();
fileWorkbook.Write(stream);
var buf = stream.ToArray();
//保存为Excel文件
using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
}