平时经常要将datatable 里面的内容导出到excel中,我用npoi做了一个简单的例子。
我是菜鸟,写错了请谅解。
首先再NUGET里面引入npoi dll ,如图
然后写一个简单的类,代码如下
public class NpoiExcel
{
public static void TableToExcel(DataTable dt, string file)
{
try
{
IWorkbook workbook;
string fileExt = Path.GetExtension(file).ToLower();
if (fileExt == ".xlsx")
{
workbook = new XSSFWorkbook();
}
else if (fileExt == ".xls")
{
workbook = new HSSFWorkbook();
}
else
{
workbook = null;
}
if (workbook == null)
{
return;
}
ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);
//表头
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);
}
//数据
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();
workbook.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();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
值得注意的是头部命名空间需要引入三个
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;