[Asp.net] C# 操作Excel的几种方式 优缺点比较
在项目中我们常常需要将数据库中的数据导出成Excel文件
有一次工作中我的目的就是读取Excel到内存中,整理成指定格式 整理后再导出到Excel。
因为我要处理的每个Excel表格文件很大。一个表格多个sheet,每个sheet-一千到上W行不等。列数超过300列。
所有在这里我将在使用一些处理Excel的方法的时候,所遇到的一些问题记录下来,也做一个大致的比较。主要是针对此次数据处理
NPOI
目前比较流行的一款操作Excel的组件。移植于Java的POI,是一个开源项目,对Excel的操作很全面。
官网 https://github.com/tonyqus/npoi
优势:
1、免费
2、机器不用安装Office也可以直接操作Excel,免去很多事。
3、现在已至此的文件格式包括Excel2003和2007之后的xls,xlsx以及docx。
4、支持文件的导入和导出
5、网上有丰富的实例代码,遇到基本的问题可以上网参考网友的解决方法。
6、NPOI能支持绝大多数Excel里面的功能操作(Excel内的公式函数、设置单元格的格式样式)
7、导入导出速度快。内存占用大。
特定优势:
支持读取超过256列的Excel表格。
缺点:
参考目前很多网上的文档,npoi导出xlsx,使用 XSSFWorkbook 使用 Write 方法写入内存流后,返回的 MemoryStream 已经被关闭了,不能再使用了。
你需要花一点时间解决这个问题。
可以参考:http://www.holdcode.com/server/details/127
xlsx格式导出 推荐使用Epplus组件
NPOI辅助类:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 using System; 2 using System.Collections; 3 using System.Collections.Generic; 4 using System.Text; 5 using System.IO; 6 using System.Data; 7 using System.Windows.Forms; 8 using NPOI.SS.UserModel; 9 using NPOI.HSSF.UserModel; 10 using NPOI.XSSF.UserModel; 11 12 namespace TEMS.Service 13 { 14 public static class ExcelHelperForCs 15 { 16 #region 私有方法 17 18 /// <summary> 19 /// 获取要保存的文件名称(含完整路径) 20 /// </summary> 21 /// <returns></returns> 22 private static string GetSaveFilePath() 23 { 24 SaveFileDialog saveFileDig = new SaveFileDialog(); 25 saveFileDig.Filter = "Excel Office97-2003(*.xls)|*.xls|Excel Office2007及以上(*.xlsx)|*.xlsx"; 26 saveFileDig.FilterIndex = 0; 27 saveFileDig.Title = "导出到"; 28 saveFileDig.OverwritePrompt = true; 29 saveFileDig.InitialDirectory = Common.DesktopDirectory; 30 string filePath = null; 31 if (saveFileDig.ShowDialog() == DialogResult.OK) 32 { 33 filePath = saveFileDig.FileName; 34 } 35 36 return filePath; 37 } 38 39 /// <summary> 40 /// 获取要打开要导入的文件名称(含完整路径) 41 /// </summary> 42 /// <returns></returns> 43 private static string GetOpenFilePath() 44 { 45 OpenFileDialog openFileDig = new OpenFileDialog(); 46 openFileDig.Filter = "Excel Office97-2003(*.xls)|*.xls|Excel Office2007及以上(*.xlsx)|*.xlsx"; 47 openFileDig.FilterIndex = 0; 48 openFileDig.Title = "打开"; 49 openFileDig.CheckFileExists = true; 50 openFileDig.CheckPathExists = true; 51 openFileDig.InitialDirectory = Common.DesktopDirectory; 52 string filePath = null; 53 if (openFileDig.ShowDialog() == DialogResult.OK) 54 { 55 filePath = openFileDig.FileName; 56 } 57 58 return filePath; 59 } 60 61 /// <summary> 62 /// 判断是否为兼容模式 63 /// </summary> 64 /// <param name="filePath"></param> 65 /// <returns></returns> 66 private static bool GetIsCompatible(string filePath) 67 { 68 return filePath.EndsWith(".xls", StringComparison.OrdinalIgnoreCase); 69 } 70 71 /// <summary> 72 /// 创建工作薄 73 /// </summary> 74 /// <param name="isCompatible"></param> 75 /// <returns></returns> 76 private static IWorkbook CreateWorkbook(bool isCompatible) 77 { 78 if (isCompatible) 79 { 80 return new HSSFWorkbook(); 81 } 82 else 83 { 84 return new XSSFWorkbook(); 85 } 86 } 87 88 /// <summary> 89 /// 创建工作薄(依据文件流) 90 /// </summary> 91 /// <param name="isCompatible"></param> 92 /// <param name="stream"></param> 93 /// <returns></returns> 94 private static IWorkbook CreateWorkbook(bool isCompatible, dynamic stream) 95 { 96 if (isCompatible) 97 { 98 return new HSSFWorkbook(stream); 99 } 100 else 101 { 102 return new XSSFWorkbook(stream); 103 } 104 } 105 106 /// <summary> 107 /// 创建表格头单元格 108 /// </summary> 109 /// <param name="sheet"></param> 110 /// <returns></returns> 111 private static ICellStyle GetCellStyle(IWorkbook workbook) 112 { 113 ICellStyle style = workbook.CreateCellStyle(); 114 style.FillPattern = FillPattern.SolidForeground; 115 style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index; 116 117 return style; 118 } 119 120 /// <summary> 121 /// 从工作表中生成DataTable 122 /// </summary> 123 /// <param name="sheet"></param> 124 /// <param name="headerRowIndex"></param> 125 /// <returns></returns> 126 private static DataTable GetDataTableFromSheet(ISheet sheet, int headerRowIndex) 127 { 128 DataTable table = new DataTable(); 129 130 IRow headerRow = sheet.GetRow(headerRowIndex); 131 int cellCount = headerRow.LastCellNum; 132 133 for (int i = headerRow.FirstCellNum; i < cellCount; i++) 134 { 135 if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "") 136 { 137 // 如果遇到第一个空列,则不再继续向后读取 138 cellCount = i; 139 break; 140 } 141 DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); 142 table.Columns.Add(column); 143 } 144 145 for (int i = (headerRowIndex + 1); i <= sheet.LastRowNum; i++) 146 { 147 IRow row = sheet.GetRow(i); 148 //如果遇到某行的第一个单元格的值为空,则不再继续向下读取 149 if (row != null && !string.IsNullOrEmpty(row.GetCell(0).ToString())) 150 { 151 DataRow dataRow = table.NewRow(); 152 153 for (int j = row.FirstCellNum; j < cellCount; j++) 154 { 155 dataRow[j] = row.GetCell(j).ToString(); 156 } 157 158 table.Rows.Add(dataRow); 159 } 160 } 161 162 return table; 163 } 164 165 #endregion 166 167 #region 公共导出方法 168 169 /// <summary> 170 /// 由DataSet导出Excel 171 /// </summary> 172 /// <param name="sourceTable">要导出数据的DataTable</param> 173 /// <returns>Excel工作表</returns> 174 public static string ExportToExcel(DataSet sourceDs, string filePath = null) 175 { 176 177 if (string.IsNullOrEmpty(filePath)) 178 { 179 filePath = GetSaveFilePath(); 180 } 181 182 if (string.IsNullOrEmpty(filePath)) return null; 183 184 bool isCompatible = GetIsCompatible(filePath); 185 186 IWorkbook workbook = CreateWorkbook(isCompatible); 187 ICellStyle cellStyle = GetCellStyle(workbook); 188 189 for (int i = 0; i < sourceDs.Tables.Count; i++) 190 { 191 DataTable table = sourceDs.Tables[i]; 192 string sheetName = "result" + i.ToString(); 193 ISheet sheet = workbook.CreateSheet(sheetName); 194 IRow headerRow = sheet.CreateRow(0); 195 // handling header. 196 foreach (DataColumn column in table.Columns) 197 { 198 ICell cell = headerRow.CreateCell(column.Ordinal); 199 cell.SetCellValue(column.ColumnName); 200 cell.CellStyle = cellStyle; 201 } 202 203 // handling value. 204 int rowIndex = 1; 205 206 foreach (DataRow row in table.Rows) 207 { 208 IRow dataRow = sheet.CreateRow(rowIndex); 209 210 foreach (DataColumn column in table.Columns) 211 { 212 dataRow.CreateCell(column.Ordinal).SetCellValue((row[column] ?? "").ToString()); 213 } 214 215 rowIndex++; 216 } 217 } 218 219 FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite); 220 workbook.Write(fs); 221 fs.Dispose(); 222 workbook = null; 223 224 return filePath; 225 226 } 227 228 /// <summary> 229 /// 由DataTable导出Excel 230 /// </summary> 231 /// <param name="sourceTable">要导出数据的DataTable</param> 232 /// <returns>Excel工作表</returns> 233 public static string ExportToExcel(DataTable sourceTable, string sheetName = "result", string filePath = null) 234 { 235 if (sourceTable.Rows.Count <= 0) return null; 236 237 if (string.IsNullOrEmpty(filePath)) 238 { 239 filePath = GetSaveFilePath(); 240 } 241 242 if (string.IsNullOrEmpty(filePath)) return null; 243 244 bool isCompatible = GetIsCompatible(filePath); 245 246 IWorkbook workbook = CreateWorkbook(isCompatible); 247 ICellStyle cellStyle = GetCellStyle(workbook); 248 249 ISheet sheet = workbook.CreateSheet(sheetName); 250 IRow headerRow = sheet.CreateRow(0); 251 // handling header. 252 foreach (DataColumn column in sourceTable.Columns) 253 { 254 ICell headerCell = headerRow.CreateCell(column.Ordinal); 255 headerCell.SetCellValue(column.ColumnName); 256 headerCell.CellStyle = cellStyle; 257 } 258 259 // handling value. 260 int rowIndex = 1; 261 262 foreach (DataRow row in sourceTable.Rows) 263 { 264 IRow dataRow = sheet.CreateRow(rowIndex); 265 266 foreach (DataColumn column in sourceTable.Columns) 267 { 268 dataRow.CreateCell(column.Ordinal).SetCellValue((row[column]??"").ToString()); 269 } 270 271 rowIndex++; 272 } 273 FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite); 274 workbook.Write(fs); 275 fs.Dispose(); 276 277 sheet = null; 278 headerRow = null; 279 workbook = null; 280 281 return filePath; 282 } 283 284 /// <summary> 285 /// 由List导出Excel 286 /// </summary> 287 /// <typeparam name="T">类型</typeparam> 288 /// <param name="data">在导出的List</param> 289 /// <param name="sheetName">sheet名称</param> 290 /// <returns></returns> 291 public static string ExportToExcel<T>(List<T> data, IList<KeyValuePair<string, string>> headerNameList, string sheetName = "result", string filePath = null) where T : class 292 { 293 if (data.Count <= 0) return null; 294 295 if (string.IsNullOrEmpty(filePath)) 296 { 297 filePath = GetSaveFilePath(); 298 } 299 300 if (string.IsNullOrEmpty(filePath)) return null; 301 302 bool isCompatible = GetIsCompatible(filePath); 303 304 IWorkbook workbook = CreateWorkbook(isCompatible); 305 ICellStyle cellStyle = GetCellStyle(workbook); 306 ISheet sheet = workbook.CreateSheet(sheetName); 307 IRow headerRow = sheet.CreateRow(0); 308 309 for (int i = 0; i < headerNameList.Count; i++) 310 { 311 ICell cell = headerRow.CreateCell(i); 312 cell.SetCellValue(headerNameList[i].Value); 313 cell.CellStyle = cellStyle; 314 } 315 316 Type t = typeof(T); 317 int rowIndex = 1; 318 foreach (T item in data) 319 { 320 IRow dataRow = sheet.CreateRow(rowIndex); 321 for (int n = 0; n < headerNameList.Count; n++) 322 { 323 object pValue = t.GetProperty(headerNameList[n].Key).GetValue(item, null); 324 dataRow.CreateCell(n).SetCellValue((pValue ?? "").ToString()); 325 } 326 rowIndex++; 327 } 328 FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite); 329 workbook.Write(fs); 330 fs.Dispose(); 331 332 sheet = null; 333 headerRow = null; 334 workbook = null; 335 336 return filePath; 337 } 338 339 /// <summary> 340 /// 由DataGridView导出 341 /// </summary> 342 /// <param name="grid"></param> 343 /// <param name="sheetName"></param> 344 /// <param name="filePath"></param> 345 /// <returns></returns> 346 public static string ExportToExcel(DataGridView grid, string sheetName = "result", string filePath = null) 347 { 348 if (grid.Rows.Count <= 0) return null; 349 350 if (string.IsNullOrEmpty(filePath)) 351 { 352 filePath = GetSaveFilePath(); 353 } 354 355 if (string.IsNullOrEmpty(filePath)) return null; 356 357 bool isCompatible = GetIsCompatible(filePath); 358 359 IWorkbook workbook = CreateWorkbook(isCompatible); 360 ICellStyle cellStyle = GetCellStyle(workbook); 361 ISheet sheet = workbook.CreateSheet(sheetName); 362 363 IRow headerRow = sheet.CreateRow(0); 364 365 for (int i = 0; i < grid.Columns.Count; i++) 366 { 367 ICell cell = headerRow.CreateCell(i); 368 cell.SetCellValue(grid.Columns[i].HeaderText); 369 cell.CellStyle = cellStyle; 370 } 371 372 int rowIndex = 1; 373 foreach (DataGridViewRow row in grid.Rows) 374 { 375 IRow dataRow = sheet.CreateRow(rowIndex); 376 for (int n = 0; n < grid.Columns.Count; n++) 377 { 378 dataRow.CreateCell(n).SetCellValue((row.Cells[n].Value ?? "").ToString()); 379 } 380 rowIndex++; 381 } 382 383 FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite); 384 workbook.Write(fs); 385 fs.Dispose(); 386 387 sheet = null; 388 headerRow = null; 389 workbook = null; 390 391 return filePath; 392 } 393 394 #endregion 395 396 #region 公共导入方法 397 398 /// <summary> 399 /// 由Excel导入DataTable 400 /// </summary> 401 /// <param name="excelFileStream">Excel文件流</param> 402 /// <param name="sheetName">Excel工作表名称</param> 403 /// <param name="headerRowIndex">Excel表头行索引</param> 404 /// <param name="isCompatible">是否为兼容模式</param> 405 /// <returns>DataTable</returns> 406 public static DataTable ImportFromExcel(Stream excelFileStream, string sheetName, int headerRowIndex, bool isCompatible) 407 { 408 IWorkbook workbook = CreateWorkbook(isCompatible, excelFileStream); 409 ISheet sheet = null; 410 int sheetIndex = -1; 411 if (int.TryParse(sheetName, out sheetIndex)) 412 { 413 sheet = workbook.GetSheetAt(sheetIndex); 414 } 415 else 416 { 417 sheet = workbook.GetSheet(sheetName); 418 } 419 420 DataTable table = GetDataTableFromSheet(sheet, headerRowIndex); 421 422 excelFileStream.Close(); 423 workbook = null; 424 sheet = null; 425 return table; 426 } 427 428 /// <summary> 429 /// 由Excel导入DataTable 430 /// </summary> 431 /// <param name="excelFilePath">Excel文件路径,为物理路径。</param> 432 /// <param name="sheetName">Excel工作表名称</param> 433 /// <param name="headerRowIndex">Excel表头行索引</param> 434 /// <returns>DataTable</returns> 435 public static DataTable ImportFromExcel(string excelFilePath, string sheetName, int headerRowIndex) 436 { 437 if (string.IsNullOrEmpty(excelFilePath)) 438 { 439 excelFilePath = GetOpenFilePath(); 440 } 441 442 if (string.IsNullOrEmpty(excelFilePath)) 443 { 444 return null; 445 } 446 447 using (FileStream stream = System.IO.File.OpenRead(excelFilePath)) 448 { 449 bool isCompatible = GetIsCompatible(excelFilePath); 450 return ImportFromExcel(stream, sheetName, headerRowIndex, isCompatible); 451 } 452 } 453 454 /// <summary> 455 /// 由Excel导入DataSet,如果有多个工作表,则导入多个DataTable 456 /// </summary> 457 /// <param name="excelFileStream">Excel文件流</param> 458 /// <param name="headerRowIndex">Excel表头行索引</param> 459 /// <param name="isCompatible">是否为兼容模式</param> 460 /// <returns>DataSet</returns> 461 public static DataSet ImportFromExcel(Stream excelFileStream, int headerRowIndex, bool isCompatible) 462 { 463 DataSet ds = new DataSet(); 464 IWorkbook workbook = CreateWorkbook(isCompatible, excelFileStream); 465 for (int i = 0; i < workbook.NumberOfSheets; i++) 466 { 467 ISheet sheet = workbook.GetSheetAt(i); 468 DataTable table = GetDataTableFromSheet(sheet, headerRowIndex); 469 ds.Tables.Add(table); 470 } 471 472 excelFileStream.Close(); 473 workbook = null; 474 475 return ds; 476 } 477 478 /// <summary> 479 /// 由Excel导入DataSet,如果有多个工作表,则导入多个DataTable 480 /// </summary> 481 /// <param name="excelFilePath">Excel文件路径,为物理路径。</param> 482 /// <param name="headerRowIndex">Excel表头行索引</param> 483 /// <returns>DataSet</returns> 484 public static DataSet ImportFromExcel(string excelFilePath, int headerRowIndex) 485 { 486 if (string.IsNullOrEmpty(excelFilePath)) 487 { 488 excelFilePath = GetOpenFilePath(); 489 } 490 491 if (string.IsNullOrEmpty(excelFilePath)) 492 { 493 return null; 494 } 495 496 using (FileStream stream = System.IO.File.OpenRead(excelFilePath)) 497 { 498 bool isCompatible = GetIsCompatible(excelFilePath); 499 return ImportFromExcel(stream, headerRowIndex, isCompatible); 500 } 501 } 502 503 #endregion 504 505 #region 公共转换方法 506 507 /// <summary> 508 /// 将Excel的列索引转换为列名,列索引从0开始,列名从A开始。如第0列为A,第1列为B... 509 /// </summary> 510 /// <param name="index">列索引</param> 511 /// <returns>列名,如第0列为A,第1列为B...</returns> 512 public static string ConvertColumnIndexToColumnName(int index) 513 { 514 index = index + 1; 515 int system = 26; 516 char[] digArray = new char[100]; 517 int i = 0; 518 while (index > 0) 519 { 520 int mod = index % system; 521 if (mod == 0) mod = system; 522 digArray[i++] = (char)(mod - 1 + 'A'); 523 index = (index - 1) / 26; 524 } 525 StringBuilder sb = new StringBuilder(i); 526 for (int j = i - 1; j >= 0; j--) 527 { 528 sb.Append(digArray[j]); 529 } 530 return sb.ToString(); 531 } 532 533 /// <summary> 534 /// 转化日期 535 /// </summary> 536 /// <param name="date">日期</param> 537 /// <returns></returns> 538 public static DateTime ConvertToDate(object date) 539 { 540 string dtStr = (date ?? "").ToString(); 541 542 DateTime dt = new DateTime(); 543 544 if (DateTime.TryParse(dtStr, out dt)) 545 { 546 return dt; 547 } 548 549 try 550 { 551 string spStr = ""; 552 if (dtStr.Contains("-")) 553 { 554 spStr = "-"; 555 } 556 else if (dtStr.Contains("/")) 557 { 558 spStr = "/"; 559 } 560 string[] time = dtStr.Split(spStr.ToCharArray()); 561 int year = Convert.ToInt32(time[2]); 562 int month = Convert.ToInt32(time[0]); 563 int day = Convert.ToInt32(time[1]); 564 string years = Convert.ToString(year); 565 string months = Convert.ToString(month); 566 string days = Convert.ToString(day); 567 if (months.Length == 4) 568 { 569 dt = Convert.ToDateTime(date); 570 } 571 else 572 { 573 string rq = ""; 574 if (years.Length == 1) 575 { 576 years = "0" + years; 577 } 578 if (months.Length == 1) 579 { 580 months = "0" + months; 581 } 582 if (days.Length == 1) 583 { 584 days = "0" + days; 585 } 586 rq = "20" + years + "-" + months + "-" + days; 587 dt = Convert.ToDateTime(rq); 588 } 589 } 590 catch 591 { 592 throw new Exception("日期格式不正确,转换日期类型失败!"); 593 } 594 return dt; 595 } 596 597 /// <summary> 598 /// 转化数字 599 /// </summary> 600 /// <param name="d">数字字符串</param> 601 /// <returns></returns> 602 public static decimal ConvertToDecimal(object d) 603 { 604 string dStr = (d ?? "").ToString(); 605 decimal result = 0; 606 if (decimal.TryParse(dStr, out result)) 607 { 608 return result; 609 } 610 else 611 { 612 throw new Exception("数字格式不正确,转换数字类型失败!"); 613 } 614 615 } 616 617 /// <summary> 618 /// 转化布尔 619 /// </summary> 620 /// <param name="b"></param> 621 /// <returns></returns> 622 public static bool ConvertToBoolen(object b) 623 { 624 string bStr = (b ?? "").ToString().Trim(); 625 bool result = false; 626 if (bool.TryParse(bStr, out result)) 627 { 628 return result; 629 } 630 else if (bStr=="0" || bStr=="1") 631 { 632 return (bStr == "0"); 633 } 634 else 635 { 636 throw new Exception("布尔格式不正确,转换布尔类型失败!"); 637 } 638 } 639 640 #endregion 641 } 642 }
c#中NPOI处理excel中的各种类型
1 ICell cell = row.GetCell(i); 2 if (cell.CellType == CellType.NUMERIC) 3 { 4 //NPOI中数字和日期都是NUMERIC类型的,这里对其进行判断是否是日期类型 5 if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型 6 { 7 dataRow[i] = cell.DateCellValue; 8 } 9 else//其他数字类型 10 { 11 dataRow[i] = cell.NumericCellValue; 12 } 13 } 14 else if (cell.CellType == CellType.BLANK)//空数据类型 15 { 16 dataRow[i] = ""; 17 } 18 else if (cell.CellType == CellType.FORMULA)//公式类型 19 { 20 HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(workbook); 21 dataRow[i] = eva.Evaluate(cell).StringValue; 22 } 23 else //其他类型都按字符串类型来处理 24 { 25 dataRow[i] = cell.StringCellValue; 26 }
使用方法参考链接:
https://www.cnblogs.com/5tao/p/4302540.html
http://www.cnblogs.com/restran/p/3889479.html
http://blog.csdn.net/dcrmg/article/details/52356236
OLEDB
使用Microsoft Jet 提供程序用于连接到 Excel 工作簿,将Excel文件作为数据源来读写
优点:
简单快速,能够操作高版本Excel,占用内存很少。
缺点:
不灵活,只能够进行有限的操作(读、写)
特定缺点:读取列最多只能读取到255列。
使用参考链接:
https://www.cnblogs.com/ammy714926/p/4905026.html
http://blog.csdn.net/zzq900503/article/details/8802855
https://www.ctolib.com/topics-52087.html
COM组件
利用office的com组件对Excel进行操作。
有人在使用com组件的时候发现效率很低,其实是使用不当导致的,COM来操作Excel的朋友可以用数组的方式来操作读写。效率提升很大
优点:
操作excel多一种选择(相比较NPOI缺点更突出)
明显缺点:
要求本机安装了Microsoft Office组件。
代码复杂不易理解。
在操作中需要处理大量的数据类型。
使用方法参考链接:
https://www.cnblogs.com/daviddai/archive/2013/06/10/Excel.html
EPPlus
官网:https://epplus.codeplex.com/
Epplus是一个使用Open Office XML文件格式,能读写Excel2007/2010文件的开源组件,在导出Excel的时候不需要电脑上安装office。
优点:
不需要安装office
Epplus另一个出色的地方就是支持图表的列印
导入导出速度快,支持高版本Excel格式,Epplus可以实现Excel上的各种基本功能
唯一缺点:
不支持导出2003版Excel
导出Excel(xlsx)辅助类
/// <summary> /// 使用EPPlus导出Excel(xlsx) /// </summary> /// <param name="sourceTable">数据源</param> /// <param name="strFileName">xlsx文件名(不含后缀名)</param> /// <param name="saveFilePath">保存文件路径</param> /// <param name="isOpen"></param> public static void ExportByEPPlus(DataTable sourceTable, string strFileName, string saveFilePath) { try { using (ExcelPackage pck = new ExcelPackage()) { //创建工作簿 string sheetName = string.IsNullOrEmpty(sourceTable.TableName) ? "Sheet1" : sourceTable.TableName; using (ExcelWorksheet ws = pck.Workbook.Worksheets.Add(sheetName)) { //从第一行加载表格 ws.Cells["A1"].LoadFromDataTable(sourceTable, true); //写入文件 string saveFullPath = saveFilePath + "\\" + strFileName + ".xlsx"; using (FileStream fs = new FileStream(saveFullPath, FileMode.Create, FileAccess.Write)) { byte[] data = pck.GetAsByteArray(); fs.Write(data, 0, data.Length); fs.Flush(); } } } } catch (Exception e) { Console.WriteLine(e); throw; } }
使用方式参考链接:
https://www.idaima.com/article/2837
https://www.cnblogs.com/dzw2017/p/6663714.html
Aspose.Cell
官网:https://zh.aspose.com/products/cells/net
优点:
不需要安装office
和NPOI一样有团队一直在更新,维护,支持的操作非常多。
控件功能非常强大:支持多种参数变量的绑定操作,如支持DataSet、Datatable、IList集合,实体类集合、类对象等。
多种格式的转换,将Excel文件转换成PDF格式,打开加密的excel,
缺点:
收费
可参考的资料较少
使用方式参考链接:
https://www.cnblogs.com/wuhuacong/archive/2011/02/23/1962147.html
https://www.cnblogs.com/kenblove/archive/2009/01/07/1371104.html
以上就是我接触到的几款c# 操作Excel的组件 大家可以根据自己的需求来选择合适的开发方式。
有不对的地方欢迎斧正。谢谢