第一步:引用文件
using NPOI.HSSF.UserModel;
using System.Data;
using CTUClassLibrary;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
这些文件，CTUClassLibrary;是自己写大sql查询语句，可以不用。其他的都需要引用
主体代码：
protected void Page_Load(object sender, EventArgs e) { ZHLSExcel(); } HSSFWorkbook workbook = new HSSFWorkbook(); //设置最初的列 protected List<string> ListZM = new List<string>() { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", }; public void ZHLSExcel() { DataTable dt = new DataTable(); string strWhere = " BankID=0"; //一下是其他js传过来的参数 if (Request.Params["opstate"] != null) { string opstate = Request.Params["opstate"].ToString().Trim(); if (opstate.Trim().Length > 0 && opstate != "0") { strWhere += " and ops='" + Request.Params["opstate"].ToString().Trim() + "'"; } } string sql = " select hu as oney, df as money from table where" + strWhere; classDBOP dbop = new classDBOP(); dt = dbop.ExcSql(sql, 4); //sql插叙结果集。可以自己写 // 日期 金额 经办人 财务 出纳 对方账户 string title = "流水表";//标题 string[] biaoTou = new string[] { "日期", "收", "支", "经办人", "财务", "出纳", "对方账户" };//表头 try { MemoryStream ms = new MemoryStream(); HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("newSheet1"); string fileName = "流水表.xls";//文件名 string filePath = Server.MapPath("~/TMFile/OAFile/BB_Report/CDay/" + fileName);//文件路径 #region 填充标题 IRow row = sheet.CreateRow(0);//第几行 row.HeightInPoints = 40;//行高 ICell cell = row.CreateCell(0);//在行中添加一列 cell.SetCellValue(title);//设置列的内容 ICellStyle style = workbook.CreateCellStyle(); style.Alignment = HorizontalAlignment.CENTER; style.VerticalAlignment = VerticalAlignment.CENTER; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 24; style.SetFont(font); cell.CellStyle = style; int cellRows = dt.Rows.Count; int cellNo = dt.Columns.Count; sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, cellNo - 1)); CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, cellNo - 1); sheet.AddMergedRegion(cellRangeAddress); #endregion #region 填充表头 IRow row1 = sheet.CreateRow(1); for (int j = 0; j < biaoTou.Length; j++) { //吧biaoTou表头得类容写入 row1.CreateCell(j).SetCellValue(biaoTou[j]); } #endregion #region 填充内容 decimal allshou = 0, allzhi = 0; for (int r = 0; r < dt.Rows.Count; r++) { IRow rows = sheet.CreateRow(r + 2);//cellRows + 2 for (int c = 0; c < dt.Columns.Count; c++) { rows.CreateCell(c).SetCellValue(dt.Rows[r][c].ToString()); //读取dt里面的数据。 } allshou += Convert.IsDBNull(dt.Rows[r]["oney"]) ? 0 : decimal.Parse(dt.Rows[r]["oney"].ToString()); allzhi += Convert.IsDBNull(dt.Rows[r]["money"]) ? 0 : decimal.Parse(dt.Rows[r]["money"].ToString()); } #endregion #region 尾部 IRow rowM = sheet.CreateRow(cellRows + 2); rowM.HeightInPoints = 25; ICell ceeM = rowM.CreateCell(1); ceeM.SetCellValue("收入：" + allshou.ToString()); ceeM = rowM.CreateCell(2); ceeM.SetCellValue("支出：" + allzhi.ToString()); IRow rowAdd = sheet.CreateRow(cellRows + 3); rowAdd.HeightInPoints = 25; ICell ceeAdd = rowAdd.CreateCell(0); ceeAdd.SetCellValue("制单人：" + Session["Name"].ToString().Trim() + " 日期：" + DateTime.Now.ToShortDateString()); IRow rowEnd = sheet.CreateRow(cellRows + 4); rowEnd.HeightInPoints = 25; ICell cellEnd = rowEnd.CreateCell(0); cellEnd.SetCellValue("此表由XXXXX" + DateTime.Now.ToString("yyyy年MM月dd日") + "导出 "); ICellStyle styleEnd = workbook.CreateCellStyle(); styleEnd.Alignment = HorizontalAlignment.RIGHT; styleEnd.VerticalAlignment = VerticalAlignment.CENTER; IFont fontEnd = workbook.CreateFont(); fontEnd.FontHeightInPoints = 10; fontEnd.Color = HSSFColor.GREY_50_PERCENT.index; fontEnd.Boldweight = (short)FontBoldWeight.BOLD; styleEnd.SetFont(fontEnd); cellEnd.CellStyle = styleEnd; sheet.AddMergedRegion(new CellRangeAddress(cellRows + 4, cellRows + 4, 0, cellNo - 1));//起始行号，终止行号， 起始列号，终止列号 #endregion #region 保存 workbook.Write(ms); using (FileStream stream = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite)) { //创建文件，将workbook写入文件 workbook.Write(stream); } workbook = null; ms.Close(); ms.Dispose(); FileInfo file = new FileInfo(filePath); Response.Clear(); Response.Charset = "GB2312"; Response.ContentEncoding = System.Text.Encoding.UTF8; //添加头信息，为"文件下载/另存为"对话框指定默认文件名 Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName); Response.AddHeader("Content-Length", file.Length.ToString());//添加头信息，指定文件大小，让浏览器能够显示下载进度 Response.ContentType = "application/ms-excel";//指定返回的是一个不能被客户端读取的流，必须被下载 Response.WriteFile(file.FullName);//把文件流发送到客户端 Response.End();//停止页面的执行 #endregion } catch (System.Exception ex) { } }
我觉得这方法不错，自己记录一下。也给你看看
以下是结果图
