需要引入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(); }