前阵子，做过一个NPOI 导出EXCEL的项目，现在总结一下：
1 public static Stream ExportTotalDataTableToExcel(DataTable sourceTable1, DataTable sourceTable2, string sheetName, string strHeaderText, string footText) 2 { //sourceTable1,sourceTable2是DATATABLE类型的两个数据源，用来组合在excel表中的数据，strHeaderText是要设置的excel表的title名字，footText是excel表你末尾想加入的东西。
3 HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream(); 4 HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(sheetName); //创建一个excel的sheet
//设置excel的列宽。
5 if (sourceTable2 != null) //set the sourceTable2's column width. 6 { 7 sheet.SetColumnWidth(1, 20 * 256); 8 sheet.SetColumnWidth(2, 15 * 256); 9 sheet.SetColumnWidth(3, 30 * 256); 10 } //创建excel表头，第一行为title标题，并设置样式。
11 HSSFRow header = (HSSFRow)sheet.CreateRow(0); 12 header.HeightInPoints = 25; 13 header.CreateCell(0).SetCellValue(strHeaderText); 14 15 //set the title of this sheet table. 16 HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle(); 17 HSSFFont font = (HSSFFont)workbook.CreateFont(); 18 font.FontHeightInPoints = 13; 19 font.Boldweight = 200; 20 headStyle.SetFont(font); 21 header.GetCell(0).CellStyle = headStyle; 22 23 //set the header column style of the sheet. 24 HSSFCellStyle styleBorder = (HSSFCellStyle)workbook.CreateCellStyle(); 25 styleBorder.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; 26 styleBorder.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; 27 styleBorder.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; 28 styleBorder.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; 29 styleBorder.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; 30 styleBorder.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; 31 styleBorder.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; 32 styleBorder.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; 33 styleBorder.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground; 34 styleBorder.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index; 35 HSSFFont fieldfont = (HSSFFont)workbook.CreateFont(); 36 fieldfont.FontHeightInPoints = 10; 37 fieldfont.Boldweight = 100; 38 styleBorder.SetFont(fieldfont); 39 40 //set the column's style expect the header column row. 41 HSSFCellStyle columnStyle = (HSSFCellStyle)workbook.CreateCellStyle(); 42 columnStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; 43 columnStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; 44 columnStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; 45 columnStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; 46 columnStyle.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; 47 columnStyle.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; 48 columnStyle.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; 49 columnStyle.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; 50 columnStyle.SetFont(fieldfont); 51 52 HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1); // handling header. 53 foreach (DataColumn column in sourceTable1.Columns) 54 { 55 headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); 56 headerRow.GetCell(column.Ordinal).CellStyle = styleBorder; 57 58 } 59 int rowIndex = 2; 60 foreach (DataRow row in sourceTable1.Rows) 61 { 62 HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex); 63 foreach (DataColumn column in sourceTable1.Columns) 64 { 65 dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString()); 66 dataRow.GetCell(column.Ordinal).CellStyle = columnStyle; 67 } 68 rowIndex++; 69 } 70 int extraRowIndex = rowIndex++; //组合sourceTable2里的汇总数据拼接到excel表中
71 if (sourceTable2 != null) 72 { 73 74 HSSFRow dataRow1 = (HSSFRow)sheet.CreateRow(extraRowIndex); 75 76 foreach (DataColumn column in sourceTable1.Columns) 77 { 78 if (column.Ordinal == 0) 79 { 80 dataRow1.CreateCell(0).SetCellValue("汇总:"); 81 } 82 else 83 { 84 dataRow1.CreateCell(column.Ordinal).SetCellValue(""); 85 } 86 dataRow1.GetCell(column.Ordinal).CellStyle = columnStyle; 87 } 88 extraRowIndex++; 89 foreach (DataRow row in sourceTable2.Rows) 90 { 91 HSSFRow dataRow = (HSSFRow)sheet.CreateRow(extraRowIndex); 92 93 foreach (DataColumn column in sourceTable1.Columns) 94 { 95 if (column.Ordinal == 0) 96 { 97 dataRow.CreateCell(0).SetCellValue(row[0].ToString() + ":" + (row[1] == null ? "0" : row[1].ToString()) + "个"); 98 } 99 else 100 { 101 dataRow.CreateCell(column.Ordinal).SetCellValue(""); 102 } 103 dataRow.GetCell(column.Ordinal).CellStyle = columnStyle; 104 } 105 extraRowIndex++; 106 } 107 108 HSSFRow lastRow = (HSSFRow)sheet.CreateRow(extraRowIndex); 109 foreach (DataColumn column in sourceTable1.Columns) 110 { 111 if (column.Ordinal == 0) 112 { //统计合计信息
113 lastRow.CreateCell(0).SetCellValue("合计:" + sourceTable1.Rows.Count+"个"); 114 } 115 else 116 { 117 lastRow.CreateCell(column.Ordinal).SetCellValue(""); 118 } 119 lastRow.GetCell(column.Ordinal).CellStyle = columnStyle; 125 } 126 extraRowIndex++; 127 } 128 extraRowIndex = extraRowIndex + 1; 129 HSSFRow lastTotalRow = (HSSFRow)sheet.CreateRow(extraRowIndex); 130 lastTotalRow.CreateCell(0).SetCellValue(footText); 131 lastTotalRow.GetCell(0).CellStyle = headStyle; 132 133 workbook.Write(ms); 134 ms.Flush(); 135 ms.Position = 0; 136 sheet = null; 137 headerRow = null; 138 workbook = null; 139 return ms; 140 }
以上就是一个完整的实现excel样式及填充数据的方法函数，具体调用执行：
public static void ExportTotalDataTableToExcel(DataTable sourceTable, DataTable sourceTable1, string fileName, string sheetName, string strHeaderText,string footText) { MemoryStream ms = ExportTotalDataTableToExcel(sourceTable, sourceTable1, sheetName, strHeaderText,footText) as MemoryStream; if (ms != null) { HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName); HttpContext.Current.Response.BinaryWrite(ms.ToArray()); HttpContext.Current.Response.End(); ms.Close(); } ms = null; }
