1 public class NPOIExcel
2 {
3 /// <summary>
4 /// 将excel导入到datatable
5 /// </summary>
6 /// <param name="filePath">excel路径</param>
7 /// <param name="isColumnName">第一行是否是列名</param>
8 /// <returns>返回datatable</returns>
9 public static DataTable ExcelToDataTable(string filePath, bool isColumnName)
10 {
11 DataTable dataTable = null;
12 FileStream fs = null;
13 DataColumn column = null;
14 DataRow dataRow = null;
15 IWorkbook workbook = null;
16 ISheet sheet = null;
17 IRow row = null;
18 ICell cell = null;
19 int startRow = 0;
20 try
21 {
22 using (fs = File.OpenRead(filePath))
23 {
24 // 2007版本
25 if (filePath.IndexOf(".xlsx") > 0)
26 workbook = new XSSFWorkbook(fs);
27 // 2003版本
28 else if (filePath.IndexOf(".xls") > 0)
29 workbook = new HSSFWorkbook(fs);
30
31 if (workbook != null)
32 {
33 sheet = workbook.GetSheetAt(0);//读取第一个sheet，当然也可以循环读取每个sheet
34 dataTable = new DataTable();
35 if (sheet != null)
36 {
37 int rowCount = sheet.LastRowNum;//总行数
38 if (rowCount > 0)
39 {
40 IRow firstRow = sheet.GetRow(0);//第一行
41 int cellCount = firstRow.LastCellNum;//列数
42
43 //构建datatable的列
44 if (isColumnName)
45 {
46 startRow = 1;//如果第一行是列名，则从第二行开始读取
47 for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
48 {
49 cell = firstRow.GetCell(i);
50 if (cell != null)
51 {
52 if (cell.StringCellValue != null)
53 {
54 column = new DataColumn(cell.StringCellValue);
55 dataTable.Columns.Add(column);
56 }
57 }
58 }
59 }
60 else
61 {
62 for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
63 {
64 column = new DataColumn("column" + (i + 1));
65 dataTable.Columns.Add(column);
66 }
67 }
68
69 //填充行
70 for (int i = startRow; i <= rowCount; ++i)
71 {
72 row = sheet.GetRow(i);
73 if (row == null) continue;
74
75 dataRow = dataTable.NewRow();
76 for (int j = row.FirstCellNum; j < cellCount; ++j)
77 {
78 cell = row.GetCell(j);
79 if (cell == null)
80 {
81 dataRow[j] = "";
82 }
83 else
84 {
85 //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)
86 switch (cell.CellType)
87 {
88 case CellType.Blank:
89 dataRow[j] = "";
90 break;
91 case CellType.Numeric:
92 short format = cell.CellStyle.DataFormat;
93 //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
94 if (format == 14 || format == 31 || format == 57 || format == 58)
95 dataRow[j] = cell.DateCellValue;
96 else
97 dataRow[j] = cell.NumericCellValue;
98 break;
99 case CellType.String:
100 dataRow[j] = cell.StringCellValue;
101 break;
102 }
103 }
104 }
105 dataTable.Rows.Add(dataRow);
106 }
107 }
108 }
109 }
110 }
111 return dataTable;
112 }
113 catch (Exception)
114 {
115 if (fs != null)
116 {
117 fs.Close();
118 }
119 return null;
120 }
121 }
122
123 /// <summary>
124 /// 写入excel
125 /// </summary>
126 /// <param name="dt">datatable</param>
127 /// <param name="strFile">strFile</param>
128 /// <returns></returns>
129 public static bool DataTableToExcel(DataTable dt, string strFile)
130 {
131 bool result = false;
132 IWorkbook workbook = null;
133 FileStream fs = null;
134 IRow row = null;
135 ISheet sheet = null;
136 ICell cell = null;
137 try
138 {
139 if (dt != null && dt.Rows.Count > 0)
140 {
141 workbook = new HSSFWorkbook();
142 sheet = workbook.CreateSheet("Sheet0");//创建一个名称为Sheet0的表
143 int rowCount = dt.Rows.Count;//行数
144 int columnCount = dt.Columns.Count;//列数
145
146 //设置列头
147 row = sheet.CreateRow(0);//excel第一行设为列头
148 for (int c = 0; c < columnCount; c++)
149 {
150 cell = row.CreateCell(c);
151 cell.SetCellValue(dt.Columns[c].ColumnName);
152 }
153
154 //设置每行每列的单元格,
155 for (int i = 0; i < rowCount; i++)
156 {
157 row = sheet.CreateRow(i + 1);
158 for (int j = 0; j < columnCount; j++)
159 {
160 cell = row.CreateCell(j);//excel第二行开始写入数据
161 cell.SetCellValue(dt.Rows[i][j].ToString());
162 }
163 }
164 using (fs = File.OpenWrite(strFile))
165 {
166 workbook.Write(fs);//向打开的这个xls文件中写入数据
167 result = true;
168 }
169 }
170 return result;
171 }
172 catch (Exception ex)
173 {
174 if (fs != null)
175 {
176 fs.Close();
177 }
178 return false;
179 }
180 }
181
182 /// <summary>
183 /// Excel导入成Datable
184 /// </summary>
185 /// <param name="file">导入路径(包含文件名与扩展名)</param>
186 /// <returns></returns>
187 public static DataTable ExcelToTable(string file)
188 {
189 DataTable dt = new DataTable();
190 IWorkbook workbook;
191 string fileExt = Path.GetExtension(file).ToLower();
192 using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
193 {
194 //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
195 if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(fs); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(fs); } else { workbook = null; }
196 if (workbook == null) { return null; }
197 ISheet sheet = workbook.GetSheetAt(0);
198
199 //表头
200 IRow header = sheet.GetRow(sheet.FirstRowNum);
201 List<int> columns = new List<int>();
202 for (int i = 0; i < header.LastCellNum; i++)
203 {
204 object obj = GetValueType(header.GetCell(i));
205 if (obj == null || obj.ToString() == string.Empty)
206 {
207 dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
208 }
209 else
210 dt.Columns.Add(new DataColumn(obj.ToString()));
211 columns.Add(i);
212 }
213 //数据
214 for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
215 {
216 DataRow dr = dt.NewRow();
217 bool hasValue = false;
218 foreach (int j in columns)
219 {
220 dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));
221 if (dr[j] != null && dr[j].ToString() != string.Empty)
222 {
223 hasValue = true;
224 }
225 }
226 if (hasValue)
227 {
228 dt.Rows.Add(dr);
229 }
230 }
231 }
232 return dt;
233 }
234
235 /// <summary>
236 /// Datable导出成Excel
237 /// </summary>
238 /// <param name="dt"></param>
239 /// <param name="file">导出路径(包括文件名与扩展名)</param>
240 public static void TableToExcel(DataTable dt, string file)
241 {
242 IWorkbook workbook;
243 string fileExt = Path.GetExtension(file).ToLower();
244 if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }
245 if (workbook == null) { return; }
246 ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);
247
248 //表头
249 IRow row = sheet.CreateRow(0);
250 for (int i = 0; i < dt.Columns.Count; i++)
251 {
252 ICell cell = row.CreateCell(i);
253 cell.SetCellValue(dt.Columns[i].ColumnName);
254 }
255
256 //数据
257 for (int i = 0; i < dt.Rows.Count; i++)
258 {
259 IRow row1 = sheet.CreateRow(i + 1);
260 for (int j = 0; j < dt.Columns.Count; j++)
261 {
262 ICell cell = row1.CreateCell(j);
263 cell.SetCellValue(dt.Rows[i][j].ToString());
264 }
265 }
266
267 //转为字节数组
268 MemoryStream stream = new MemoryStream();
269 workbook.Write(stream);
270 var buf = stream.ToArray();
271
272 //保存为Excel文件
273 using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
274 {
275 fs.Write(buf, 0, buf.Length);
276 fs.Flush();
277 }
278 }
279
280 /// <summary>
281 /// 获取单元格类型
282 /// </summary>
283 /// <param name="cell"></param>
284 /// <returns></returns>
285 private static object GetValueType(ICell cell)
286 {
287 if (cell == null)
288 return null;
289 switch (cell.CellType)
290 {
291 case CellType.Blank: //BLANK:
292 return null;
293 case CellType.Boolean: //BOOLEAN:
294 return cell.BooleanCellValue;
295 case CellType.Numeric: //NUMERIC:
296 return cell.NumericCellValue;
297 case CellType.String: //STRING:
298 return cell.StringCellValue;
299 case CellType.Error: //ERROR:
300 return cell.ErrorCellValue;
301 case CellType.Formula: //FORMULA:
302 default:
303 return "=" + cell.CellFormula;
304
305 }
306 }
307
308 }
