你的位置:首页 > ASP.net教程

[ASP.net教程]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 #endregion103 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 #endregion130 131 rowIndex = 2;132 }133 #endregion134 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 #endregion183 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; //没有数据的行默认是null209 if (row.GetCell(j) == null){continue;} //同理,没有数据的单元格都默认是null210 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

 使用方法是:

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();