ASP.NET使用NPOI类库导出Excel

1 using System;
2 using System.Collections.Generic;
3 using System.Data;
4 using System.IO;
5 using System.Linq;
6 using System.Web;
7 using NPOI;
8 using NPOI.HPSF;
9 using NPOI.HSSF;
10 using NPOI.HSSF.UserModel;
11 using NPOI.POIFS;
12 using NPOI.Util;
13
14 public class DataTableRenderToExcel
15 {
16 public static Stream RenderDataTableToExcel(DataTable SourceTable)
17 {
18 HSSFWorkbook workbook = new HSSFWorkbook();
19 MemoryStream ms = new MemoryStream();
20 HSSFSheet sheet = workbook.CreateSheet();
21 HSSFRow headerRow = sheet.CreateRow(0);
22
23 // handling header.
24 foreach (DataColumn column in SourceTable.Columns)
25 headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
26
27 // handling value.
28 int rowIndex = 1;
29
30 foreach (DataRow row in SourceTable.Rows)
31 {
32 HSSFRow dataRow = sheet.CreateRow(rowIndex);
33
34 foreach (DataColumn column in SourceTable.Columns)
35 {
36 dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
37 }
38
39 rowIndex++;
40 }
41
42 workbook.Write(ms);
43 ms.Flush();
44 ms.Position = 0;
45
46 sheet = null;
47 headerRow = null;
48 workbook = null;
49
50 return ms;
51 }
52
53 public static void RenderDataTableToExcel(DataTable SourceTable, string FileName)
54 {
55 MemoryStream ms = RenderDataTableToExcel(SourceTable) as MemoryStream;
56 FileStream fs = new FileStream(FileName, FileMode.Create, FileAccess.Write);
57 byte[] data = ms.ToArray();
58
59 fs.Write(data, 0, data.Length);
60 fs.Flush();
61 fs.Close();
62
63 data = null;
64 ms = null;
65 fs = null;
66 }
67
68 public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, string SheetName, int HeaderRowIndex)
69 {
70 HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
71 HSSFSheet sheet = workbook.GetSheet(SheetName);
72
73 DataTable table = new DataTable();
74
75 HSSFRow headerRow = sheet.GetRow(HeaderRowIndex);
76 int cellCount = headerRow.LastCellNum;
77
78 for (int i = headerRow.FirstCellNum; i < cellCount; i++)
79 {
80 DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
81 table.Columns.Add(column);
82 }
83
84 int rowCount = sheet.LastRowNum;
85
86 for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++)
87 {
88 HSSFRow row = sheet.GetRow(i);
89 DataRow dataRow = table.NewRow();
90
91 for (int j = row.FirstCellNum; j < cellCount; j++)
92 dataRow[j] = row.GetCell(j).ToString();
93 }
94
95 ExcelFileStream.Close();
96 workbook = null;
97 sheet = null;
98 return table;
99 }
100
101 public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex)
102 {
103 HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
104 HSSFSheet sheet = workbook.GetSheetAt(SheetIndex);
105
106 DataTable table = new DataTable();
107
108 HSSFRow headerRow = sheet.GetRow(HeaderRowIndex);
109 int cellCount = headerRow.LastCellNum;
110
111 for (int i = headerRow.FirstCellNum; i < cellCount; i++)
112 {
113 DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
114 table.Columns.Add(column);
115 }
116
117 int rowCount = sheet.LastRowNum;
118
119 for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++)
120 {
121 HSSFRow row = sheet.GetRow(i);
122 DataRow dataRow = table.NewRow();
123
124 for (int j = row.FirstCellNum; j < cellCount; j++)
125 {
126 if (row.GetCell(j) != null)
127 dataRow[j] = row.GetCell(j).ToString();
128 }
129
130 table.Rows.Add(dataRow);
131 }
132
133 ExcelFileStream.Close();
134 workbook = null;
135 sheet = null;
136 return table;
137 }
138
139 /// <summary>读取excel
140 /// 默认第一行为标头
141 /// </summary>
142 /// <param name="path">excel文档路径</param>
143 /// <returns></returns>
144 public static DataTable RenderDataTableFromExcel(string path) {
145 DataTable dt = new DataTable();
146
147 HSSFWorkbook hssfworkbook;
148 using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
149 {
150 hssfworkbook = new HSSFWorkbook(file);
151 }
152 HSSFSheet sheet = hssfworkbook.GetSheetAt(0);
153 System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
154
155 HSSFRow headerRow = sheet.GetRow(0);
156 int cellCount = headerRow.LastCellNum;
157
158 for (int j = 0; j < cellCount; j++)
159 {
160 HSSFCell cell = headerRow.GetCell(j);
161 dt.Columns.Add(cell.ToString());
162 }
163
164 for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
165 {
166 HSSFRow row = sheet.GetRow(i);
167 DataRow dataRow = dt.NewRow();
168
169 for (int j = row.FirstCellNum; j < cellCount; j++)
170 {
171 if (row.GetCell(j) != null)
172 dataRow[j] = row.GetCell(j).ToString();
173 }
174
175 dt.Rows.Add(dataRow);
176 }
177
178 //while (rows.MoveNext())
179 //{
180 // HSSFRow row = (HSSFRow)rows.Current;
181 // DataRow dr = dt.NewRow();
182
183 // for (int i = 0; i < row.LastCellNum; i++)
184 // {
185 // HSSFCell cell = row.GetCell(i);
186
187
188 // if (cell == null)
189 // {
190 // dr[i] = null;
191 // }
192 // else
193 // {
194 // dr[i] = cell.ToString();
195 // }
196 // }
197 // dt.Rows.Add(dr);
198 //}
199
200 return dt;
201 }
202 } 

 

 

NOPI，导出EXCEL的类，使用说明见：http://msdn.microsoft.com/zh-tw/ee818993.aspx

NOPI能够模拟excel（在进程中new出excel，而又无需安装excel实体软件），看完，我真的一阵感动，感谢作者，也感谢Fighting Team.SDF的推荐。好料就该大家推广，给他犀利用起来。