因为微软的office成本太高了，所以开发项目的时候电脑上没安装office,而是安装了wps。但开发语言用的是C#,所以直接调用微软的office组件是很方便的，但一方面慢，一方面成本高，所以从网上找到了NPOI这个开源的项目。http://npoi.codeplex.com/,引用的dll下载目录 http://npoi.codeplex.com/downloads/get/1476595
并且封装了通用的处理Excel 跟DataSet,DataTable的方法。方便调用
以上是代码 （当前项目是.net 2.0 下的，如果需要.net 4.0则到NPOI官网下载相应的dll就可以了）
1 using NPOI.SS.UserModel; 2 using System; 3 using System.Collections.Generic; 4 using System.Data; 5 using System.IO; 6 using System.Text; 7 8 namespace MrLiu.Tools 9 { 10 public sealed class ExcelHelper 11 { 12 #region Excel导入 13 /// <summary> 14 /// Excel 转换为DataTable 15 /// </summary> 16 /// <param name="file">文件路径</param> 17 /// <param name="sheetName">Sheet名称，如果只有一个sheet可以传 null</param> 18 /// <returns></returns> 19 public static DataTable ExcelToDataTable(string file, string sheetName) 20 { 21 try 22 { 23 DataTable dt = new DataTable(); 24 using (FileStream fs = new FileStream(file, FileMode.Open, Fileaccess.Read, FileShare.Read)) 25 { 26 var workbook = NPOI.SS.UserModel.WorkbookFactory.Create(fs); 27 ISheet sheet = null; 28 if (sheetName == null) 29 { 30 sheet = workbook.GetSheetAt(0); 31 } 32 else 33 { 34 sheet = workbook.GetSheet(sheetName); 35 } 36 //列名 37 IRow rowHead = sheet.GetRow(sheet.FirstRowNum); 38 for (int i = 0; i < rowHead.LastCellNum; i++) 39 { 40 string fildName = rowHead.GetCell(i).StringCellValue; 41 dt.Columns.Add(fildName, typeof(String)); 42 } 43 44 //数据 45 for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++) 46 { 47 IRow row = sheet.GetRow(i); 48 DataRow dr = dt.NewRow(); 49 for (int j = row.FirstCellNum; j < row.LastCellNum; j++) 50 { 51 var cell = row.GetCell(j); 52 dr[j] = GetValueTypeForICell(cell); 53 if (dr[j] == null) 54 { 55 dr[j] = string.Empty; 56 } 57 } 58 dt.Rows.Add(dr); 59 } 60 } 61 return dt; 62 } 63 catch (Exception ex) 64 { 65 throw new Exception(ex.Message); 66 } 67 } 68 /// <summary> 69 /// Excel 导入为DataTable 70 /// </summary> 71 /// <param name="file">文件路径</param> 72 /// <param name="extension">后续名 XLS XLSX</param> 73 /// <returns></returns> 74 public static DataTable ExcelToDataTable(string file) 75 { 76 try 77 { 78 DataTable dt = new DataTable(); 79 string extension = Path.GetExtension(file); 80 if (extension.ToUpper() == ".XLS") 81 { 82 dt = ExcelToTableForXLS(file); 83 } 84 else if (extension.ToUpper() == ".XLS") 85 { 86 dt = ExcelToTableForXLSX(file); 87 } 88 else 89 { 90 throw new Exception("文件格式不正确"); 91 } 92 return dt; 93 } 94 catch (Exception ex) 95 { 96 throw new Exception(ex.Message); 97 } 98 } 99 /// <summary>100 /// 读取xls格式的Excel101 /// </summary>102 /// <param name="file">文件全路径</param>103 /// <returns>返回DaTaTable</returns>104 public static DataTable ExcelToTableForXLS(string file)105 {106 try107 {108 DataTable dt = new DataTable();109 using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read, FileShare.Read))110 {111 var hssfworkbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);112 ISheet sheet = hssfworkbook.GetSheetAt(0);113 114 //列名115 IRow rowHead = sheet.GetRow(sheet.FirstRowNum);116 for (int i = 0; i < rowHead.LastCellNum; i++)117 {118 string fildName = rowHead.GetCell(i).StringCellValue;119 dt.Columns.Add(fildName, typeof(String));120 }121 122 //数据123 for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)124 {125 IRow row = sheet.GetRow(i);126 DataRow dr = dt.NewRow();127 for (int j = row.FirstCellNum; j < row.LastCellNum; j++)128 {129 NPOI.HSSF.UserModel.HSSFCell cell = row.GetCell(j) as NPOI.HSSF.UserModel.HSSFCell;130 dr[j] = GetValueTypeForXLS(cell);131 if (dr[j] == null)132 {133 break;134 }135 }136 dt.Rows.Add(dr);137 }138 }139 return dt;140 }141 catch (Exception ex)142 {143 throw new Exception(ex.Message);144 }145 }146 147 /// <summary>148 /// 获取单元格类型149 /// </summary>150 /// <param name="cell"></param>151 /// <returns></returns>152 PRivate static object GetValueTypeForXLS(NPOI.HSSF.UserModel.HSSFCell cell)153 {154 try155 {156 if (cell == null)157 {158 return null;159 }160 switch (cell.CellType)161 {162 case CellType.Blank: //BLANK: 163 return null;164 case CellType.Boolean: //BOOLEAN: 165 return cell.BooleanCellValue;166 case CellType.Numeric: //NUMERIC: 167 return cell.NumericCellValue;168 case CellType.String: //STRING: 169 return cell.StringCellValue;170 case CellType.Error: //ERROR: 171 return cell.ErrorCellValue;172 case CellType.Formula: //FORMULA: 173 default:174 return "=" + cell.CellFormula;175 }176 }177 catch (Exception ex)178 {179 throw new Exception(ex.Message);180 }181 }182 183 /// <summary>184 /// 读取xlsx格式的Excel185 /// </summary>186 /// <param name="file">文件全路径</param>187 /// <returns>返回DaTaTable</returns>188 public static DataTable ExcelToTableForXLSX(string file)189 {190 try191 {192 DataTable dt = new DataTable();193 using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read, FileShare.Read))194 {195 var hssfworkbook = new NPOI.XSSF.UserModel.XSSFWorkbook(fs);196 ISheet sheet = hssfworkbook.GetSheetAt(0);197 198 //列名199 IRow rowHead = sheet.GetRow(sheet.FirstRowNum);200 for (int i = 0; i < rowHead.LastCellNum; i++)201 {202 string fildName = rowHead.GetCell(i).StringCellValue;203 dt.Columns.Add(fildName, typeof(String));204 }205 206 //数据207 for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)208 {209 IRow row = sheet.GetRow(i);210 DataRow dr = dt.NewRow();211 for (int j = row.FirstCellNum; j < row.LastCellNum; j++)212 {213 NPOI.HSSF.UserModel.HSSFCell cell = row.GetCell(j) as NPOI.HSSF.UserModel.HSSFCell;214 dr[j] = GetValueTypeForXLS(cell);215 if (dr[j] == null)216 {217 break;218 }219 }220 dt.Rows.Add(dr);221 }222 }223 return dt;224 }225 catch (Exception ex)226 {227 throw new Exception(ex.Message);228 }229 }230 /// <summary> 231 /// 获取单元格类型(xlsx) 232 /// </summary> 233 /// <param name="cell"></param> 234 /// <returns></returns> 235 private static object GetValueTypeForXLSX(NPOI.XSSF.UserModel.XSSFCell cell)236 {237 try238 {239 if (cell == null)240 {241 return null;242 }243 switch (cell.CellType)244 {245 case CellType.Blank: //BLANK: 246 return null;247 case CellType.Boolean: //BOOLEAN: 248 return cell.BooleanCellValue;249 case CellType.Numeric: //NUMERIC: 250 return cell.NumericCellValue;251 case CellType.String: //STRING: 252 return cell.StringCellValue;253 case CellType.Error: //ERROR: 254 return cell.ErrorCellValue;255 case CellType.Formula: //FORMULA: 256 default:257 return "=" + cell.CellFormula;258 }259 }260 catch (Exception ex)261 {262 throw new Exception(ex.Message);263 }264 }265 266 /// <summary> 267 /// 获取单元格类型不定268 /// </summary> 269 /// <param name="cell"></param> 270 /// <returns></returns> 271 private static object GetValueTypeForICell(ICell cell)272 {273 try274 {275 if (cell == null)276 {277 return null;278 }279 switch (cell.CellType)280 {281 case CellType.Blank: //BLANK: 282 return null;283 case CellType.Boolean: //BOOLEAN: 284 return cell.BooleanCellValue;285 case CellType.Numeric: //NUMERIC: 286 return cell.NumericCellValue;287 case CellType.String: //STRING: 288 return cell.StringCellValue;289 case CellType.Error: //ERROR: 290 return cell.ErrorCellValue;291 case CellType.Formula: //FORMULA: 292 default:293 return "=" + cell.CellFormula;294 }295 }296 catch (Exception ex)297 {298 throw new Exception(ex.Message);299 }300 }301 302 /// <summary>303 /// Excel 转换为DataSet304 /// </summary>305 /// <param name="fileName">文件名</param>306 /// <returns>DataSet</returns>307 public static DataSet ExcelToDataSet(string fileName)308 {309 try310 {311 if (!File.Exists(fileName))312 {313 throw new Exception("文件不存在");314 }315 else316 {317 DataSet ds = new DataSet();318 using (FileStream reader = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite))319 {320 IWorkbook book = WorkbookFactory.Create(reader);321 int cnt = book.NumberOfSheets;322 if (cnt <= 0)323 {324 throw new Exception("文件不是Excel文件");325 }326 327 for (int i = 0; i < cnt; i++)328 {329 ISheet sheet = book.GetSheetAt(i);330 DataTable dt = new DataTable(sheet.SheetName);331 IRow rowHead = sheet.GetRow(sheet.FirstRowNum);332 for (int j = rowHead.FirstCellNum; j < rowHead.LastCellNum; j++)333 {334 ICell cell = rowHead.GetCell(j);335 dt.Columns.Add(cell.StringCellValue);336 }337 for (int j = sheet.FirstRowNum + 1; j <= sheet.LastRowNum; j++)338 {339 DataRow dr = dt.NewRow();340 IRow row = sheet.GetRow(j);341 for (int k = rowHead.FirstCellNum; k < rowHead.LastCellNum; k++)342 {343 dr[k] = row.GetCell(k).StringCellValue;344 }345 dt.Rows.Add(dr);346 }347 ds.Tables.Add(dt);348 }349 }350 return ds;351 }352 }353 catch (Exception ex)354 {355 throw new Exception(ex.Message);356 }357 }358 #endregion Excel导出359 360 #region Excel导出361 362 /// <summary>363 /// Excel导出364 /// </summary>365 /// <param name="dt">虚拟表</param>366 /// <param name="fileName">文件路径</param>367 /// <param name="sheetName">Sheet路径为空请传null</param>368 /// <returns></returns>369 public static bool DataTableToXLS(DataTable dt, string fileName, string sheetName)370 {371 try372 {373 if (dt == null)374 {375 return false;376 }377 if (String.IsNullOrEmpty(sheetName))378 {379 sheetName = Path.GetFileName(fileName);380 }381 var book = new NPOI.HSSF.UserModel.HSSFWorkbook();382 book.CreateSheet();383 var sheet = book.CreateSheet(sheetName);384 385 IRow rowHead = sheet.CreateRow(0);386 for (int i = 0; i < dt.Columns.Count; i++)387 {388 ICell cell = rowHead.CreateCell(i);389 cell.SetCellValue(dt.Columns[i].ColumnName);390 }391 for (int i = 0; i < dt.Rows.Count; i++)392 {393 IRow row = sheet.CreateRow(i + 1);394 for (int j = 0; j < dt.Columns.Count; j++)395 {396 ICell cell = row.CreateCell(j);397 cell.SetCellValue(dt.Rows[i][j].ToString());398 }399 }400 401 using (FileStream fsWriter = new FileStream(fileName, FileMode.Append, FileAccess.Write, FileShare.Write))402 {403 book.Write(fsWriter);404 return true;405 }406 }407 catch (Exception ex)408 {409 throw new Exception(ex.Message);410 }411 }412 413 414 /// <summary>415 /// DataSet 导出 到Excel416 /// </summary>417 /// <param name="ds">DataSet 表名默认为sheet名</param>418 /// <param name="fileName">文件路径</param>419 public static bool DataSetToExcel(DataSet ds, string fileName)420 {421 try422 {423 String extension = Path.GetExtension(fileName).ToUpper();424 IWorkbook book = null;425 if (extension == ".XLS")426 {427 book = DataSetToHSSFWordbook(ds);428 }429 else if (extension == ".XLSX")430 {431 book = DataSetToXSSFWorkbook(ds);432 }433 else434 {435 throw new Exception("导入格式必须为xls或者xlsx");436 }437 438 using (FileStream fsWriter = new FileStream(fileName, FileMode.CreateNew, FileAccess.Write, FileShare.ReadWrite))439 {440 book.Write(fsWriter);441 return true;442 }443 }444 catch (Exception ex)445 {446 throw new Exception(ex.Message);447 }448 }449 /// <summary>450 /// DataSet 转换为 XSSFWorkbook 07451 /// </summary>452 /// <param name="ds"></param>453 /// <returns></returns>454 private static NPOI.XSSF.UserModel.XSSFWorkbook DataSetToXSSFWorkbook(DataSet ds)455 {456 try457 {458 var book = new NPOI.XSSF.UserModel.XSSFWorkbook();459 foreach (DataTable dt in ds.Tables)460 {461 ISheet sheet = book.CreateSheet(dt.TableName);462 IRow rowHead = sheet.CreateRow(0);463 ICellStyle style = book.CreateCellStyle();464 style.BorderBottom = BorderStyle.Thin;465 style.BorderTop = BorderStyle.Thin;466 style.BorderLeft = BorderStyle.Thin;467 style.BorderRight = BorderStyle.Thin;468 IFont font = book.CreateFont();469 font.FontHeightInPoints = 12;470 font.IsBold = true;471 style.SetFont(font);472 for (int i = 0; i < dt.Columns.Count; i++)473 {474 ICell cell = rowHead.CreateCell(i);475 cell.CellStyle = style;476 cell.SetCellValue(dt.Columns[i].ColumnName);477 }478 font.IsBold = false;479 style.SetFont(font);480 for (int i = 0; i < dt.Rows.Count; i++)481 {482 IRow row = sheet.CreateRow(i + 1);483 DataRow dr = dt.Rows[i];484 for (int j = 0; j < dt.Columns.Count; j++)485 {486 ICell cell = row.CreateCell(j);487 cell.CellStyle = style;488 cell.SetCellValue(dr[j].ToString());489 }490 }491 }492 return book;493 }494 catch (Exception ex)495 {496 throw new Exception(ex.Message);497 }498 }499 500 /// <summary>501 /// DataSet 转换为 HSSFWorkbook 03502 /// </summary>503 /// <param name="ds"></param>504 /// <returns></returns>505 private static NPOI.HSSF.UserModel.HSSFWorkbook DataSetToHSSFWordbook(DataSet ds)506 {507 try508 {509 var book = new NPOI.HSSF.UserModel.HSSFWorkbook();510 var dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation();511 dsi.Company = "xx软件股份有限公司";512 var si = NPOI.HPSF.PropertySetFactory.CreateSummaryInformation();513 si.Subject = "xx系统自动导出";514 book.DocumentSummaryInformation = dsi;515 book.SummaryInformation = si;516 517 foreach (DataTable dt in ds.Tables)518 {519 ISheet sheet = book.CreateSheet(dt.TableName);520 IRow rowHead = sheet.CreateRow(0);521 ICellStyle style = book.CreateCellStyle();522 style.BorderBottom = BorderStyle.Thin;523 style.BorderTop = BorderStyle.Thin;524 style.BorderLeft = BorderStyle.Thin;525 style.BorderRight = BorderStyle.Thin;526 IFont font = book.CreateFont();527 font.FontHeightInPoints = 12;528 font.IsBold = true;529 style.SetFont(font);530 for (int i = 0; i < dt.Columns.Count; i++)531 {532 ICell cell = rowHead.CreateCell(i);533 cell.CellStyle = style;534 cell.SetCellValue(dt.Columns[i].ColumnName);535 }536 font.IsBold = false;537 style.SetFont(font);538 for (int i = 0; i < dt.Rows.Count; i++)539 {540 IRow row = sheet.CreateRow(i + 1);541 DataRow dr = dt.Rows[i];542 for (int j = 0; j < dt.Columns.Count; j++)543 {544 ICell cell = row.CreateCell(j);545 cell.CellStyle = style;546 cell.SetCellValue(dr[j].ToString());547 }548 }549 }550 return book;551 }552 catch (Exception ex)553 {554 throw new Exception(ex.Message);555 }556 }557 558 #endregion559 }560 }
新闻热点
疑难解答
