NPOI导出excel

/// <summary>
/// 将DataTable数据导出到Excel文件中(xlsx)
/// </summary>
/// <param name="dt"></param>
/// <param name="file"></param>
public static void ExportDataTableToExcelForXLSX(DataTable dt, string file)
{
XSSFWorkbook xssfworkbook = new XSSFWorkbook();
ISheet sheet = xssfworkbook.CreateSheet(dt.TableName);//sheet名
//表头
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);
}

//取得列宽
int[] arrColWidth = new int[dt.Columns.Count];
foreach (DataColumn item in dt.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dt.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
foreach (DataColumn column in dt.Columns)
{
////设置列宽
//sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
//设置列宽
int characteerNum = arrColWidth[column.Ordinal] + 1;
sheet.SetColumnWidth(column.Ordinal, (characteerNum > 255 ? 255 : characteerNum) * 256);//The maximum column width for an individual cell is 255 characters.
}

//写入数据
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());
}
}

//转为字节数组
MemoryStream stream = new MemoryStream();
xssfworkbook.Write(stream);
var buf = stream.ToArray();

//保存为Excel文件
using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Close();
}
}

转载于:https://www.cnblogs.com/hobby0524/p/6733398.html