Asp.Net项目中有时需要生成一些比较复杂的报表,NPIO控件操作简单,易于理解,是一个不错的选择.
效果:
下载NPOI控件,注册,添加引用
添加命名空间:
using NPOI;
using NPOI.SS.Util;
using NPOI.SS.UserModel;
using NPOI.HPSF;
using NPOI.HSSF;
using NPOI.HSSF.Util;
using NPOI.HSSF.Model;
using NPOI.HSSF.UserModel;
using NPOI.POIFS;
using NPOI.Util;
using System.Text;
using System.IO;
具体代码:
//创建一个工作表
HSSFWorkbook hssfworkbook;
hssfworkbook = new HSSFWorkbook();
//创建Sheet
Sheet sheet = hssfworkbook.CreateSheet("new sheet");
//设置Cell的Style
HSSFCellStyle style = (HSSFCellStyle)hssfworkbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
HSSFFont font = (HSSFFont)hssfworkbook.CreateFont();
font.FontHeight = 15 * 15;
//自动换行
style.WrapText = true;
//创建两行
Row row = sheet.CreateRow(0);
Row row1 = sheet.CreateRow(1);
row.HeightInPoints = 30;
//创建第一行的第一列
Cell cell = row.CreateCell(0);
cell.SetCellValue("日期");
row.GetCell(0).CellStyle = style;
//合并单元格0,0和3,0,实际上就是第一列的前三行
sheet.AddMergedRegion(new CellRangeAddress(0, 3, 0, 0));
//下面设置表头数据,参照图片查看
row.CreateCell(1).SetCellValue("单元代码");
row.GetCell(1).CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(0, 3, 1, 1));
row.CreateCell(2).SetCellValue("注入时间");
row.GetCell(2).CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 2, 3));
row1.CreateCell(2).SetCellValue("油管");
row1.GetCell(2).CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(1, 3, 2, 2));
row1.CreateCell(3).SetCellValue("套管");
row1.GetCell(3).CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(1, 3, 3, 3));
row.CreateCell(4).SetCellValue("注入方式");
row.GetCell(4).CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(0, 3, 4, 4));
row.CreateCell(5).SetCellValue("注水压力");
row.GetCell(5).CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 5, 7));
row1.CreateCell(5).SetCellValue("泵压");
row1.GetCell(5).CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(1, 3, 5, 5));
row1.CreateCell(6).SetCellValue("套压");
row1.GetCell(6).CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(1, 3, 6, 6));
row1.CreateCell(7).SetCellValue("油压");
row1.GetCell(7).CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(1, 3, 7, 7));
row.CreateCell(8).SetCellValue("注水层位");
row.GetCell(8).CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(0, 3, 8, 8));
row.CreateCell(9).SetCellValue("日配注水量");
row.GetCell(9).CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(0, 3, 9, 9));
row.CreateCell(10).SetCellValue("全井注入量");
row.GetCell(10).CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(0, 3, 10, 10));
row.CreateCell(11).SetCellValue("1");
row.GetCell(11).CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 11, 12));
row1.CreateCell(11).SetCellValue("吸水百分比");
row1.GetCell(11).CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(1, 3, 11, 11));
row1.CreateCell(12).SetCellValue("注入量");
row1.GetCell(12).CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(1, 3, 12, 12));
row.CreateCell(13).SetCellValue("2");
row.GetCell(13).CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 13, 14));
row1.CreateCell(13).SetCellValue("吸水百分比");
row1.GetCell(13).CellStyle = style;
style.SetFont(font);
sheet.AddMergedRegion(new CellRangeAddress(1, 3, 13, 13));
row1.CreateCell(14).SetCellValue("注入量");
row1.GetCell(14).CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(1, 3, 14, 14));
row.CreateCell(15).SetCellValue("3");
row.GetCell(15).CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 15, 16));
row1.CreateCell(15).SetCellValue("吸水百分比");
row1.GetCell(15).CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(1, 3, 15, 15));
row1.CreateCell(16).SetCellValue("注入量");
row1.GetCell(16).CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(1, 3, 16, 16));
row.CreateCell(17).SetCellValue("4");
row.GetCell(17).CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 17, 18));
row1.CreateCell(17).SetCellValue("吸水百分比");
row1.GetCell(17).CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(1, 3, 17, 17));
row1.CreateCell(18).SetCellValue("注入量");
row1.GetCell(18).CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(1, 3, 18, 18));
//从数据库中获取并添加数据
DataBase dataLink = new DataBase("chunliang");
DataBase dataLinkSec = new DataBase("chunliang");
dataLink.SQLExcByAdapter("........ ");
Row dataRow;
for(int i=0;i<dataLink.dateSet.Tables[0].Rows.Count;i++)
{
// 创建行
dataRow = sheet.CreateRow(i + 4);
//日期:
dataRow.CreateCell(0).SetCellValue(Convert.ToDateTime(dataLink.dateSet.Tables[0].Rows[i]["rq"]).Day.ToString());
dataRow.GetCell(0).CellStyle = style;
//生产时间
dataRow.CreateCell(1).SetCellValue(dataLink.dateSet.Tables[0].Rows[i]["dydm"].ToString());
dataRow.GetCell(1).CellStyle = style;
dataRow.CreateCell(2).SetCellValue(strtg);
dataRow.GetCell(2).CellStyle = style;
dataRow.CreateCell(3).SetCellValue(stryg);
dataRow.GetCell(3).CellStyle = style;
dataRow.CreateCell(4).SetCellValue(dataLink.dateSet.Tables[0].Rows[i]["GXYL"].ToString());
dataRow.GetCell(4).CellStyle = style;
dataRow.CreateCell(5).SetCellValue(dataLink.dateSet.Tables[0].Rows[i]["ty"].ToString());
dataRow.GetCell(5).CellStyle = style;
dataRow.CreateCell(6).SetCellValue(dataLink.dateSet.Tables[0].Rows[i]["yy"].ToString());
dataRow.GetCell(6).CellStyle = style;
dataRow.CreateCell(7).SetCellValue(dataLink.dateSet.Tables[0].Rows[i]["zscw"].ToString());
dataRow.GetCell(7).CellStyle = style;
dataRow.CreateCell(8).SetCellValue(dataLink.dateSet.Tables[0].Rows[i]["rpzsl"].ToString());
dataRow.GetCell(8).CellStyle = style;
dataRow.CreateCell(9).SetCellValue(dataLink.dateSet.Tables[0].Rows[i]["QJZSL"].ToString());
dataRow.GetCell(9).CellStyle = style;
dataRow.CreateCell(10).SetCellValue(dataLink.dateSet.Tables[0].Rows[i]["QJZSL"].ToString());
dataRow.GetCell(10).CellStyle = style;
dataRow.CreateCell(11).SetCellValue(dataLink.dateSet.Tables[0].Rows[i]["P1BFS"].ToString());
dataRow.GetCell(11).CellStyle = style;
dataRow.CreateCell(12).SetCellValue(dataLink.dateSet.Tables[0].Rows[i]["CDRZSL1"].ToString());
dataRow.GetCell(12).CellStyle = style;
dataRow.CreateCell(13).SetCellValue(dataLink.dateSet.Tables[0].Rows[i]["P2BFS"].ToString());
dataRow.GetCell(13).CellStyle = style;
dataRow.CreateCell(14).SetCellValue(dataLink.dateSet.Tables[0].Rows[i]["CDRZSL2"].ToString());
dataRow.GetCell(14).CellStyle = style;
dataRow.CreateCell(15).SetCellValue(dataLink.dateSet.Tables[0].Rows[i]["P3BFS"].ToString());
dataRow.GetCell(15).CellStyle = style;
dataRow.CreateCell(16).SetCellValue(dataLink.dateSet.Tables[0].Rows[i]["CDRZSL3"].ToString());
dataRow.GetCell(16).CellStyle = style;
dataRow.CreateCell(17).SetCellValue(dataLink.dateSet.Tables[0].Rows[i]["P4BFS"].ToString());
dataRow.GetCell(17).CellStyle = style;
dataRow.CreateCell(18).SetCellValue(dataLink.dateSet.Tables[0].Rows[i]["CDRZSL4"].ToString());
dataRow.GetCell(18).CellStyle = style;
}
dataLink.DisConn();
dataLinkSec.DisConn();
//出现下载页面,然后写入到excel.xls文件中
System.IO.MemoryStream ms = new System.IO.MemoryStream();
hssfworkbook.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename=excel.xls"));
Response.BinaryWrite(ms.ToArray());
hssfworkbook = null;
ms.Close();
ms.Dispose();