另一个ExcelHelper

有不少园友指点,用NPOI操作Excel会比用ADO.NET 和COM 要好,于是尝试一下用NPOI封装一个ExcelHelper,在使用本类之前,要添加NPOI.dll引用。要添加两个个命名空间   

using NPOI.SS.UserModel;

using NPOI.HSSF.UserModel;

类代码如下:

  1     public class NPOIExcelHelper
  2     {
  3         #region 公共方法
  4 
  5         #region 导出
  6 
  7         /// <summary>
  8         /// 数据导出
  9         /// </summary>
 10         /// <param name="fileName">导出到的文件全名</param>
 11         /// <param name="table">数据表DataTable</param>
 12         /// <param name="addHeader">是否生产表头</param>
 13         public static void ExportExcel(string fileName, DataTable table,bool addHeader)
 14         {
 15             if (addHeader)
 16             {
 17                 DataRow row = table.Rows.Add();
 18                 foreach (DataColumn col in table.Columns)
 19                     row[col] = col.ColumnName;
 20                 table.Rows.Remove(row);
 21                 table.Rows.InsertAt(row, 0);
 22             }
 23             EditExcel(fileName, "Sheet1", table, "A1");
 24         }
 25 
 26         /// <summary>
 27         /// 数据导出(生产表头)
 28         /// </summary>
 29         /// <param name="fileName">导出到的文件全名</param>
 30         /// <param name="table">数据表DataTable</param>
 31         public static void ExportExcel(string fileName, DataTable table)
 32         {
 33             ExportExcel(fileName, table, true);
 34         }
 35 
 36         #endregion
 37 
 38         #region 导入
 39 
 40         /// <summary>
 41         /// 数据导入
 42         /// </summary>
 43         /// <param name="fileName">导入的文件全名</param>
 44         /// <param name="hasHeader">需要生产表头</param>
 45         /// <returns>导入结果</returns>
 46         public static DataTable ImportExcel(string fileName,bool hasHeader)
 47         {
 48             DataTable table = ReadExcel(fileName, 0);
 49             if (hasHeader&&table.Rows.Count>0)
 50             {
 51                 DataRow row = table.Rows[0];
 52                 for (int i = 0; i < table.Columns.Count; i++)
 53                     table.Columns[i].ColumnName = table.Rows[0][i].ToString();
 54                 table.Rows.Remove(row);
 55             }
 56             return table;
 57         }
 58 
 59         /// <summary>
 60         /// 数据导入(要生成表头)
 61         /// </summary>
 62         /// <param name="fileName">导入的文件全名</param>
 63         /// <returns>导入结果</returns>
 64         public static DataTable ImportExcel(string fileName)
 65         {
 66             return ImportExcel(fileName, true);
 67         }
 68 
 69         #endregion
 70 
 71         #region 通用编辑
 72 
 73         /// <summary>
 74         /// 编辑Excel文档。检查不了文件则不保存,检查不了工作表则新建,覆盖编辑
 75         /// </summary>
 76         /// <param name="fileName">文件全名</param>
 77         /// <param name="sheetName">工作表名</param>
 78         /// <param name="table">数据表DataTable</param>
 79         /// <param name="cell">起始的单元格 如 "A1"</param>
 80         public static void EditExcel(string fileName, string sheetName, DataTable table, string cell)
 81         {
 82             IWorkbook workBook = null;
 83             ISheet sheet = null;
 84             FileStream fs = null;
 85             bool exist=false;
 86 
 87             try
 88             {
 89                 if (IsExistFile(fileName))
 90                 {
 91                     exist=true;
 92                     fs = File.Open(fileName, FileMode.Open);
 93                     workBook = new HSSFWorkbook(fs);
 94                 }
 95                 else
 96                 {
 97                     exist=false;
 98                     fs = File.Create(fileName);
 99                     fs.Close();//2013-10-6创建文件后关闭流,防止占用(有网友指出)
100                     fs.Dispose();//2013-10-6创建文件后关闭流,防止占用(有网友指出)
101                     workBook = new HSSFWorkbook();
102                 }
103                 sheet = workBook.GetSheet(sheetName);
104                 if (sheet == null)
105                     sheet = workBook.CreateSheet(sheetName);
106 
107                 Tuple<int, int> cellIndex = ConvertCell(cell);
108 
109                 IRow eRow = null;
110                 foreach (DataRow row in table.Rows)
111                 {
112                     eRow = sheet.CreateRow(table.Rows.IndexOf(row) + cellIndex.Item1);
113                     for (int c = 0; c < table.Columns.Count; c++)
114                         eRow.CreateCell(c).SetCellValue(row[c].ToString());
115                 }
116 
117                 fs = File.OpenWrite(fileName);
118                 workBook.Write(fs);
119             }
120             catch (Exception e)
121             {
122 
123                 throw e;
124             }
125             finally
126             {
127                 if (fs != null) fs.Close();
128             }
129         }
130 
131         /// <summary>
132         /// 编辑Excel文档
133         /// </summary>
134         /// <param name="fileName">文件全名</param>
135         /// <param name="sheetName">工作表名</param>
136         /// <param name="table">数据表DataTable</param>
137         public static void EditExcel(string fileName, string sheetName, DataTable table)
138         {
139             EditExcel(fileName, sheetName, table, "A1");
140         }
141 
142         #endregion
143 
144         #region 通用读取
145 
146         /// <summary>
147         /// 读取Excel文档
148         /// </summary>
149         /// <param name="fileName">文件全名</param>
150         /// <param name="sheetName">工作表名</param>
151         /// <param name="startCell">起始单元格 如 "A1",缺省填 ""或 null</param>
152         /// <param name="endCell">终止单元格 如 "A1",缺省填 ""或 null</param>
153         /// <returns>读取结果</returns>
154         public static DataTable ReadExcel(string fileName, string sheetName, string startCell,string endCell,bool evaluateAll)
155         {
156             DataTable table = null;
157 
158             if (!File.Exists(fileName))
159                 throw new Exception("文件不存在");
160 
161             IWorkbook workBook = null;
162             ISheet sheet = null;
163             FileStream fs = null;
164 
165             try
166             {
167                 fs=File.OpenRead(fileName);
168                 workBook = new HSSFWorkbook(fs);
169                 sheet = workBook.GetSheet(sheetName);
170 
171                 if (sheet == null)
172                     throw new Exception("工作表不存在");
173                 if (evaluateAll)//2013-9-6 重计算
174                 {
175                     //HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(workBook);
176                     //eva.EvaluateAll();
177                     EvaluateSheet(workBook, sheet);//2013-10-14 逐个重计算
178                 }
179                 table = ReadSheet(sheet, startCell, endCell);
180             }
181             catch (Exception e)
182             {
183 
184                 throw e;
185             }
186             finally
187             {
188                 if (fs != null) fs.Close();
189             }
190 
191             return table;
192         }
193 
194         /// <summary>
195         /// 读取Excel文档
196         /// </summary>
197         /// <param name="fileName">文件全名</param>
198         /// <param name="sheetName">工作表名</param>
199         /// <returns>读取结果</returns>
200         public static DataTable ReadExcel(string fileName, string sheetName)
201         {
202             return ReadExcel(fileName, sheetName, "", "",true);
203         }
204 
205         /// <summary>
206         /// 读取Excel文档
207         /// </summary>
208         /// <param name="fileName">文件全名</param>
209         /// <param name="sheetIndex">工作表索引</param>
210         /// <param name="startCell">起始单元格 如 "A1",缺省填 ""或 null</param>
211         /// <param name="endCell">终止单元格 如 "A1",缺省填 ""或 null</param>
212         /// <returns>读取结果</returns>
213         public static DataTable ReadExcel(string fileName, int sheetIndex, string startCell, string endCell, bool evaluateAll)
214         {
215             DataTable table = null;
216 
217             if (!File.Exists(fileName))
218                 throw new Exception("文件不存在");
219 
220             IWorkbook workBook = null;
221             ISheet sheet = null;
222             FileStream fs = null;
223 
224             try
225             {
226                 fs = File.OpenRead(fileName);
227                 workBook = new HSSFWorkbook(fs);
228                 sheet = workBook.GetSheetAt(sheetIndex);
229 
230                 if (sheet == null)
231                     throw new Exception("工作表不存在");
232                 if (evaluateAll)//2013-9-6 重计算
233                 {
234                     //HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(workBook);
235                     //eva.EvaluateAll();
236                     EvaluateSheet(workBook, sheet);//2013-10-14 逐个重计算
237                 }
238                 table = ReadSheet(sheet, startCell, endCell);
239             }
240             catch (Exception e)
241             {
242 
243                 throw e;
244             }
245             finally
246             {
247                 if (fs != null) fs.Close();
248             }
249 
250             return table;
251         }
252 
253         /// <summary>
254         /// 读取Excel文档
255         /// </summary>
256         /// <param name="fileName">文件全名</param>
257         /// <param name="sheetIndex">工作表索引</param>
258         /// <returns>读取结果</returns>
259         public static DataTable ReadExcel(string fileName, int sheetIndex)
260         {
261             return ReadExcel(fileName, sheetIndex,"","",true);
262         }
263 
264         /// <summary>
265         /// 读取Excel文档
266         /// </summary>
267         /// <param name="fileName">文件全名</param>
268         /// <returns>读取结果</returns>
269         public static DataTable ReadExcel(string fileName)
270         {
271             return ReadExcel(fileName, 0);
272         }
273 
274         #endregion
275 
276         #endregion
277 
278         #region 内部辅助方法
279 
280         /// <summary>
281         /// 检查文件是否存在,若不存在则会先确保文件所在的目录存在
282         /// </summary>
283         /// <param name="fileName">文件名</param>
284         /// <returns>检查结果</returns>
285         private static bool IsExistFile(string fileName)
286         {
287             if (File.Exists(fileName)) return true;
288             string path = fileName.Substring(0, fileName.LastIndexOf('\\') + 1).Trim('\\');
289             if (!Directory.Exists(path))
290                 Directory.CreateDirectory(path);
291             return false;
292         }
293 
294         /// <summary>
295         /// 转换单元格位置
296         /// </summary>
297         /// <param name="cell">单元格位置</param>
298         /// <returns>int二元组</returns>
299         private static Tuple<int, int> ConvertCell(string cell)
300         {
301             Match colM = Regex.Match(cell, @"[a-zA-Z]+");
302             if (string.IsNullOrEmpty(colM.Value))
303                 throw new Exception("单元格格式有误!");
304             string colStr = colM.Value.ToUpper();
305             int colIndex = 0;
306             foreach (char ci in colStr)
307                 colIndex += ci - 'A';
308             //colIndex += 1 + (ci - 'A');
309 
310             Match rowM = Regex.Match(cell, @"\d+");
311             if (string.IsNullOrEmpty(rowM.Value))
312                 throw new Exception("单元格格式有误!");
313             int rowIndex = Convert.ToInt32(rowM.Value)-1;
314 
315             Tuple<int, int> result = new Tuple<int, int>(rowIndex, colIndex);
316             return result;
317         }
318 
319         /// <summary>
320         /// 获取工作表指定区域最宽的列数
321         /// </summary>
322         /// <param name="sheet">ISheet工作表对象</param>
323         /// <param name="startRowIndex">开始行数</param>
324         /// <param name="toRowIndex">终结行数</param>
325         /// <returns>工作表最宽的列数</returns>
326         private static int GetLastCell(ISheet sheet, int startRowIndex,int toRowIndex)
327         {
328             int result = 0;
329             for (int i = startRowIndex; i < toRowIndex; i++)
330             {
331                 IRow row = sheet.GetRow(i);
332                 if (row == null) continue; //2013-9-5防止空引用异常
333                 int temp = row.Cells.Count;
334                 if (temp > result) result = temp;
335             }
336 
337             return result;
338         }
339 
340         /// <summary>
341         /// 读取工作表指定区域的内容
342         /// </summary>
343         /// <param name="sheet">ISheet工作表对象</param>
344         /// <param name="startCell">起始单元格 </param>
345         /// <param name="endCell">终止单元格</param>
346         /// <returns>读取结果</returns>
347         private static DataTable ReadSheet(ISheet sheet, string startCell, string endCell)
348         {
349             DataTable table = new DataTable();
350 
351             Tuple<int, int> sCellIndex;
352             if (!string.IsNullOrEmpty(startCell))
353                 sCellIndex = ConvertCell(startCell);
354             else
355                 sCellIndex = new Tuple<int, int>(0, 0);
356 
357             Tuple<int, int> eCellIndex;
358             if (!string.IsNullOrEmpty(endCell))
359                 eCellIndex = ConvertCell(endCell);
360             else
361             {
362                 int lastIndex = sheet.LastRowNum;
363                 eCellIndex = new Tuple<int, int>(lastIndex, GetLastCell(sheet, sCellIndex.Item1, lastIndex));
364             }
365 
366             IRow row = sheet.GetRow(sCellIndex.Item1);
367             int rowIndex = 0;
368             //sheet.ForceFormulaRecalculation = true;
369             //while (row != null )
370 
371             for (; rowIndex <= sheet.LastRowNum;  row = sheet.GetRow(sCellIndex.Item1 + rowIndex+1),rowIndex++) //2013-9-5 rowIndex<sheet.LastRowNum 使其有效范围扩大
372             {
373                 if (row == null)continue; 
374                 List<ICell> cellList = row.Cells;
375                 DataRow dtRow = table.Rows.Add();
376                 for (int i = 0; i < cellList.Count; i++)
377                 {
378                     while (table.Columns.Count < i + 1) //2013-9-6 加一列5够的,要加到够为止
379                         table.Columns.Add();
380 
381                     dtRow[cellList[ i].ColumnIndex] = TryGetCellValue(cellList[i]);//2013-9-5 获得正确的数据类型的数值
382                     //2013-10-6 填到正确的列里面
383                 }
384                 //row = sheet.GetRow(sCellIndex.Item1 + rowIndex);
385                 //rowIndex++;
386             }
387 
388             return table;
389         }
390 
391 
392         private static object TryGetCellValue(ICell cell)
393         {
394             try
395             {
396                 return cell.StringCellValue;
397             }
398             catch { }
399             try
400             {
401                 return cell.RichStringCellValue;
402             }
403             catch { }
404             try
405             {
406                 return cell.NumericCellValue;
407             }
408             catch { }
409             try
410             {
411                 return cell.DateCellValue;
412             }
413             catch { }
414             return cell;
415         }
416 
417         /// <summary>
418         /// 重计算工作表
419         /// </summary>
420         /// <param name="workBook"></param>
421         /// <param name="sheet"></param>
422         private static void EvaluateSheet(IWorkbook workBook, ISheet sheet)
423         {
424             HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(workBook);
425             IRow row;
426             List<ICell> cellList;
427             for (int i = 0; i < sheet.LastRowNum; i++)
428             {
429                 row = sheet.GetRow(i);
430                 if (row == null) continue;
431                 cellList = row.Cells;
432                 foreach (ICell cell in cellList)
433                 {
434                     try
435                     {
436                         eva.EvaluateInCell(cell);
437                     }
438                     catch { }
439                 }
440             }
441         }
442 
443         #endregion
444     }

上述代码在经网友指出错误后更改,还有本人在使用过程对功能欠缺的作了一些补充

原文地址:https://www.cnblogs.com/HopeGi/p/2969751.html