另一个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 }
上述代码在经网友指出错误后更改，还有本人在使用过程对功能欠缺的作了一些补充
