npoi 导出excel
首先用Vs自带的Nuget包管理器下载并安装npoi 2.0,如果没有NuGet程序包选项，在菜单-->工具-->扩展管理器中搜索NuGet。
然后写一个方法读取DataTable中的内容，并输出到MemoryStream中
这是我的NPOIHelper
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Web; 5 using System.Data; 6 using System.IO; 7 using NPOI.HSSF.UserModel; 8 using NPOI.HPSF; 9 using System.Text; 10 using NPOI.SS.Util; 11 using NPOI.SS.UserModel; 12 13 /// <summary> 14 ///NPOIHelper 的摘要说明 15 /// </summary> 16 public static class NPOIHelper 17 { 18 public static HSSFWorkbook workbook; 19 public static void CloseWorkBook() 20 { 21 workbook = null; 22 } 23 public static void OpenWorkBook() { 24 workbook = new HSSFWorkbook(); 25 } 26 /// <summary> 27 /// DataTable导出到Excel的MemoryStream 28 /// </summary> 29 /// <param name="dtSource">源DataTable</param> 30 /// <param name="strHeaderText">表头文本</param> 31 public static void Export(DataTable dtSource, string strHeaderText) { 32 33 HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(); 34 35 #region 右击文件 属性信息 36 { 37 DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); 38 dsi.Company = "北京通信科技有限公司"; 39 workbook.DocumentSummaryInformation = dsi; 40 41 SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); 42 //si.Author = "文件作者信息"; //填加xls文件作者信息 43 //si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息 44 //si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息 45 //si.Comments = "作者信息"; //填加xls文件作者信息 46 //si.Title = "标题信息"; //填加xls文件标题信息 47 //si.Subject = "主题信息";//填加文件主题信息 48 si.CreateDateTime = DateTime.Now; 49 workbook.SummaryInformation = si; 50 } 51 #endregion 52 53 HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle(); 54 HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat(); 55 dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); 56 57 HSSFCellStyle tdStyle = (HSSFCellStyle)workbook.CreateCellStyle(); 58 //设置单元格边框 59 tdStyle.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中 60 tdStyle.BorderBottom = BorderStyle.THIN; 61 tdStyle.BorderLeft = BorderStyle.THIN; 62 tdStyle.BorderRight = BorderStyle.THIN; 63 tdStyle.BorderTop = BorderStyle.THIN; 64 //取得列宽 65 int[] arrColWidth = new int[dtSource.Columns.Count]; 66 foreach (DataColumn item in dtSource.Columns) { 67 arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; 68 } 69 for (int i = 0; i < dtSource.Rows.Count; i++) { 70 for (int j = 0; j < dtSource.Columns.Count; j++) { 71 int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; 72 if (intTemp > arrColWidth[j]) { 73 arrColWidth[j] = intTemp; 74 } 75 } 76 } 77 int rowIndex = 0; 78 foreach (DataRow row in dtSource.Rows) { 79 #region 新建表，填充表头，填充列头，样式 80 if (rowIndex == 65535 || rowIndex == 0) { 81 if (rowIndex != 0) { 82 sheet = (HSSFSheet)workbook.CreateSheet(); 83 } 84 85 #region 表头及样式 86 { 87 HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0); 88 headerRow.HeightInPoints = 25; 89 headerRow.CreateCell(0).SetCellValue(strHeaderText); 90 91 HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle(); 92 headStyle.Alignment = HorizontalAlignment.CENTER; 93 HSSFFont font = (HSSFFont)workbook.CreateFont(); 94 95 font.FontHeightInPoints = 20; 96 font.Boldweight = 700; 97 headStyle.SetFont(font); 98 headerRow.GetCell(0).CellStyle = headStyle; 99 sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); 100 //headerRow.Dispose(); 101 } 102 #endregion 103 104 105 #region 列头及样式 106 { 107 HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1); 108 HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle(); 109 headStyle.Alignment = HorizontalAlignment.CENTER; 110 HSSFFont font = (HSSFFont)workbook.CreateFont(); 111 //设置单元格边框 112 headStyle.BorderBottom = BorderStyle.THIN; 113 headStyle.BorderLeft = BorderStyle.THIN; 114 headStyle.BorderRight = BorderStyle.THIN; 115 headStyle.BorderTop = BorderStyle.THIN; 116 117 font.FontHeightInPoints = 10; 118 font.Boldweight = 700; 119 headStyle.SetFont(font); 120 foreach (DataColumn column in dtSource.Columns) { 121 headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); 122 headerRow.GetCell(column.Ordinal).CellStyle = headStyle; 123 124 //设置列宽 125 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); 126 } 127 //headerRow.Dispose(); 128 } 129 #endregion 130 131 rowIndex = 2; 132 } 133 #endregion 134 135 136 #region 填充内容 137 HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex); 138 foreach (DataColumn column in dtSource.Columns) { 139 HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal); 140 newCell.CellStyle = tdStyle; 141 142 string drValue = row[column].ToString(); 143 switch (column.DataType.ToString()) { 144 case "System.String"://字符串类型 145 newCell.SetCellValue(drValue); 146 break; 147 case "System.DateTime"://日期类型 148 DateTime dateV; 149 DateTime.TryParse(drValue, out dateV); 150 newCell.SetCellValue(dateV); 151 152 newCell.CellStyle = dateStyle;//格式化显示 153 break; 154 case "System.Boolean"://布尔型 155 bool boolV = false; 156 bool.TryParse(drValue, out boolV); 157 newCell.SetCellValue(boolV); 158 break; 159 case "System.Int16"://整型 160 case "System.Int32": 161 case "System.Int64": 162 case "System.Byte": 163 int intV = 0; 164 int.TryParse(drValue, out intV); 165 newCell.SetCellValue(intV); 166 break; 167 case "System.Decimal"://浮点型 168 case "System.Double": 169 double doubV = 0; 170 double.TryParse(drValue, out doubV); 171 newCell.SetCellValue(doubV); 172 break; 173 case "System.DBNull"://空值处理 174 newCell.SetCellValue(""); 175 break; 176 default: 177 newCell.SetCellValue(""); 178 break; 179 } 180 181 } 182 #endregion 183 184 rowIndex++; 185 } 186 using (MemoryStream ms = new MemoryStream()) { 187 workbook.Write(ms); 188 } 189 } 190 191 public static MemoryStream Export2(DataTable dt, string p) { 192 Export(dt,p); 193 MemoryStream ms = new MemoryStream(); 194 ISheet sheet=workbook.GetSheet("Sheet1"); 195 int FirstRow=2; 196 int LastRow=sheet.LastRowNum; 197 int Start=0; 198 int End=0; 199 string temp = ""; 200 HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle(); 201 dateStyle.Alignment = HorizontalAlignment.RIGHT; 202 dateStyle.VerticalAlignment = VerticalAlignment.CENTER; 203 for (int i = FirstRow; i < LastRow; i++) { 204 for (int j = 1; j < 5; j++) { 205 if (j == 1) 206 { 207 IRow row=sheet.GetRow(i); 208 if (row == null) continue; //没有数据的行默认是null 209 if (row.GetCell(j) == null){continue;} //同理，没有数据的单元格都默认是null 210 ICell cell=row.GetCell(j); 211 string cellText=cell.StringCellValue; 212 if (cellText == temp)//上下行相等，记录要合并的最后一行 213 { 214 End = i; 215 } 216 else//上下行不等，记录 217 { 218 if (Start != End) { 219 for (int n = Start; n < End; n++) { 220 ICell tempcell=sheet.GetRow(n).GetCell(2); 221 tempcell.SetCellValue(""+( End-Start+1 )); 222 tempcell.CellStyle = dateStyle; 223 } 224 for (int m = 1; m < 5; m++) { 225 CellRangeAddress region = new CellRangeAddress(Start, End, m,m); 226 sheet.AddMergedRegion(region); 227 } 228 229 } 230 Start = i; 231 End = i; 232 temp = cellText; 233 } 234 } 235 236 } 237 } 238 workbook.Write(ms); 239 return ms; 240 } 241 }
使用方法是:
NPOIHelper.OpenWorkBook();
DataTable dt=EconomicHelper4.GetSumProjectManager();
NPOIHelper.Export(dt,“管理"); //Export1用于生成DataTable
DataTable dt2 = EconomicHelper4.GetExtensionProjectManager();
MemoryStream ms = NPOIHelper.Export2(dt2, "数据"); //Export2用于合并单元格
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
Response.BinaryWrite(ms.ToArray());
ms.Close();
ms.Dispose();
NPOIHelper.CloseWorkBook();
