npoi 導出excel，npoiexcel
首先用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();
