首页 > 代码库 > c# NPOI 导出EXCEL
c# NPOI 导出EXCEL
需要引入dll文件
using System;using System.Collections.Generic;using System.Linq;using System.Text;namespace CSR_Web.Common{ public class NPOIExport { public static NPOI.HSSF.UserModel.HSSFWorkbook DoExport(System.Data.DataTable dt, string notile) { //创建工作簿 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //创建表 NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(notile); //自适应列宽 // sheet.AutoSizeColumn(1, true); //标题行合并单元格 sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dt.Columns.Count-1)); NPOI.SS.UserModel.IRow firstrow = sheet.CreateRow(0); NPOI.SS.UserModel.ICell firstcell = firstrow.CreateCell(0); //表名样式 NPOI.SS.UserModel.ICellStyle styleHeader = book.CreateCellStyle(); NPOI.SS.UserModel.IFont fontHeader = book.CreateFont(); styleHeader.Alignment =NPOI.SS.UserModel.HorizontalAlignment.Center; styleHeader.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; fontHeader.FontHeightInPoints =20; styleHeader.SetFont(fontHeader); firstcell.CellStyle = styleHeader; firstcell.SetCellValue(notile); try { //列名样式 NPOI.SS.UserModel.ICellStyle styleColName = book.CreateCellStyle(); NPOI.SS.UserModel.IFont fontColName = book.CreateFont(); styleColName.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; styleColName.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; fontColName.FontHeightInPoints = 14; styleColName.SetFont(fontColName); //数据的样式、字体大小 NPOI.SS.UserModel.ICellStyle styleBody = book.CreateCellStyle(); NPOI.SS.UserModel.IFont fontBody = book.CreateFont(); styleBody.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; styleBody.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; fontBody.FontHeightInPoints = 12; styleBody.SetFont(fontBody); //创建具体单元格数据 int rowCount = dt.Rows.Count; int colCount = dt.Columns.Count; NPOI.SS.UserModel.IRow colNameRow = sheet.CreateRow(1); for (int x = 0; x < colCount; x++) { //将列名写入单元格 NPOI.SS.UserModel.ICell colNameCell = colNameRow.CreateCell(x); colNameCell.SetCellValue(dt.Columns[x].ColumnName); colNameCell.CellStyle = styleColName; } for (int i = 0; i < rowCount; i++) { NPOI.SS.UserModel.IRow row = sheet.CreateRow(i + 2);//数据从第三上开始 第一行表名 第二行列名 for (int j = 0; j < colCount; j++) { //填充数据 NPOI.SS.UserModel.ICell cell = row.CreateCell(j); if (dt.Rows[i][j] != null) { cell.SetCellValue(dt.Rows[i][j].ToString()); } else { cell.SetCellValue(""); } cell.CellStyle = styleBody; } } //自适应列宽 for (int x = 0; x < colCount; x++) { sheet.AutoSizeColumn(x, true); } //此处代码是将 xls文件发到页面通过浏览器直接下载到本地 可以放到 界面调用的地方 //System.IO.MemoryStream ms = new System.IO.MemoryStream(); //book.Write(ms); //Response.AddHeader("Content-Disposition", string.Format("attachment; filename=绩效统计.xls")); //Response.BinaryWrite(ms.ToArray()); //book = null; //ms.Close(); //ms.Dispose(); return book; } catch { throw new Exception(); }finally{ book=null; } } }}
/// <summary> ///导出 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnExport_Click(object sender, EventArgs e) { DataTable dt = cmbll.getdt(); NPOI.HSSF.UserModel.HSSFWorkbook book = NPOIExport.DoExport(dt, "xxx报表"); //写入客户端 try { WriteClient(book); } catch { } finally { book = null; } } public void WriteClient(NPOI.HSSF.UserModel.HSSFWorkbook book) { System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); Response.AddHeader("Content-Disposition", string.Format("attachment; filename=客户资料"+DateTime.Now.ToString("yyyyMMddHHmmss")+".xls")); Response.BinaryWrite(ms.ToArray()); book = null; ms.Close(); ms.Dispose(); }
c# NPOI 导出EXCEL
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。