C#通过NOPI将数据库中的数据生成Excel报表
ps:需要引入NOPI.dll库
代码如下:
/// <summary>
/// 生成Excel报表到指定目录下
/// <param name="path">导出目录绝对路径</param>
/// </summary>
public void ExportReport(string path)
{
DataTable dt = getDatatable();//从数据库中获取所需databale数据
HSSFWorkbook hssfworkbook = new HSSFWorkbook();//创建工作簿
ISheet sheet1 = hssfworkbook.CreateSheet("sheet1");//创建sheet
//生成第一行
IRow rowHeader = sheet1.CreateRow(0);
rowHeader.CreateCell(0, CellType.STRING).SetCellValue("ID");
rowHeader.CreateCell(1, CellType.STRING).SetCellValue("姓名");
rowHeader.CreateCell(2, CellType.STRING).SetCellValue("年龄");
rowHeader.CreateCell(3, CellType.STRING).SetCellValue("生日");
rowHeader.CreateCell(4, CellType.STRING).SetCellValue("薪水");
//设置列宽
sheet1.DefaultColumnWidth = 16;
for (int i = 1; i < dt.Rows.Count; i++)
{
rowHeader = sheet1.CreateRow(i);//第i行
DataRow row = dt.Rows[i];
rowHeader.CreateCell(0, CellType.NUMERIC).SetCellValue(Convert.ToInt32(row["id"]));
rowHeader.CreateCell(1, CellType.STRING).SetCellValue(row["sname"].ToString());
rowHeader.CreateCell(2, CellType.NUMERIC).SetCellValue(Convert.ToInt32(row["age"]));
rowHeader.CreateCell(3, CellType.STRING).SetCellValue(string.Format("{0:D}",Convert.ToDateTime(row["birthday"].ToString())));
rowHeader.CreateCell(4, CellType.NUMERIC).SetCellValue(Convert.ToDouble(row["salay"]));
}
using (Stream stream = File.OpenWrite(path))
{
hssfworkbook.Write(stream);
}
}