npoi 導出excel_ZenDei技術網路在線

首先用Vs自帶的Nuget包管理器下載並安裝npoi 2.0,如果沒有NuGet程式包選項，在菜單-->工具-->擴展管理器中搜索NuGet。然後寫一個方法讀取DataTable中的內容，並輸出到MemoryStream中這是我的NPOIHelper 1 using System; 2 usin

首先用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

使用方法是:

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