首页 > 编程入门 文章详情

C#开源Excel组件使用说明

编程入门 行业动态 更新时间:2024-10-13 08:20:57

C#开源Excel组件<a href=https://www.elefans.com/category/jswz/34/1769912.html style=使用说明"/>

C#开源Excel组件使用说明

前文我们说了CSharp中四种开源操作Excel的组件，其中包括NPOI、Aspose.Cells for .NET、EPPlus、MiniExcel。本文来详细的讲解一下四种开源组件操作Excel的代码和效率比对

1. Demo代码框架和数据说明

1.1. Demo代码框架

1.1.1. Demo代码思路：

主题思路为以下几条内容：

  • 从表中读取数据并将数据写入到Excel中

      1. 使用不同的数据体量写入到Excel中
      1. 同时也测试将数据按每一个单页最大为50000行数据，测试大数据量数据

  • 读取Excel中的数据,测试不同的数据量，测试打开的方式是否会出现OOM问题。而读取之后的信息不使用内存记录，只是取出来即可。

  • 测试Excel的特殊功能是否支持，比如：

      1. 合并单元格
      1. 单元格样式
      1. 公式计算
      1. 插入图

1.1.2. 代码框架

  1. 代码使用.NetFramework4.8的控制台应用程序，设置不同数据量级别的集合，并将其写入到Excel中。
  2. 上述的四种操作Excel的开源组件都通过Nuget获取。
  • NPOI

  • Aspose.Cells for .NET

  • EPPlus

  • MiniExcel

以下为代码引用结果

1.1.3. 代码

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;namespace ConsoleReadWriteExcel
{internal class Program{private static string OutputExcelFolderPath = @"C:\Users\HTHT\Desktop\caogao\导出文件夹";private static string InputExcelFolderPath = @"C:\Users\HTHT\Desktop\caogao\导出文件夹";static void Main(string[] args){EPPlus_ExcelHelper.EPPlus_ExcelHelperLicenseContext();//WriteData2ExcelDemo();ReadData4ExcelDemo();Console.ReadKey(); ;}#region ReadData4ExcelDemoprivate static void ReadData4ExcelDemo(){ReadData4Excel(1000);ReadData4Excel(10000);ReadData4Excel(50000);ReadData4Excel(100000);ReadData4Excel(200000);ReadData4Excel(500000);ReadData4Excel(1000000);Console.WriteLine("-------------------------***********************-------------------------");Console.WriteLine("读取Excel完成...");Console.WriteLine("-------------------------***********************-------------------------");}private static void ReadData4Excel(int dataCount){Console.Write($"| {dataCount} |");SimpleUseReadExcel(dataCount, ReadData4Excel4NPOI);SimpleUseReadExcel(dataCount, ReadData4Excel4AsposeCell);SimpleUseReadExcel(dataCount, ReadData4Excel4EPPlus);SimpleUseReadExcel(dataCount, ReadData4Excel4MiniExcel);Console.WriteLine();}private static void ReadData4Excel4MiniExcel(int dataCount){string strOutputExcelPath = System.IO.Path.Combine(InputExcelFolderPath, "MiniExcel", $"导出数据_{dataCount}.xlsx");MiniExcel_ExcelHelper pMiniExcel_ExcelHelper = new MiniExcel_ExcelHelper();pMiniExcel_ExcelHelper.ReadData(strOutputExcelPath);}private static void ReadData4Excel4EPPlus(int dataCount){string strOutputExcelPath = System.IO.Path.Combine(InputExcelFolderPath, "EPPlus", $"导出数据_{dataCount}.xlsx");EPPlus_ExcelHelper pEPPlus_ExcelHelper = new EPPlus_ExcelHelper();pEPPlus_ExcelHelper.ReadData(ReadData, strOutputExcelPath, 1, 1);}private static void ReadData4Excel4AsposeCell(int dataCount){string strOutputExcelPath = System.IO.Path.Combine(InputExcelFolderPath, "AsposeCell", $"导出数据_{dataCount}.xls");AsposeCell_ExcelHelper pAsposeCell_ExcelHelper = new AsposeCell_ExcelHelper(strOutputExcelPath);pAsposeCell_ExcelHelper.GetData(ReadData, 0, 0);}public static bool ReadData(List<object> ListRangeObjValue, bool IsColumn){return true;}private  static void ReadData4Excel4NPOI(int dataCount){string strOutputExcelPath = System.IO.Path.Combine(InputExcelFolderPath, "NPOI", $"导出数据_{dataCount}.xlsx");NPOI_ExcelHelper pNPOI_ExcelHelper = new NPOI_ExcelHelper(strOutputExcelPath);pNPOI_ExcelHelper.ReadExcel(strOutputExcelPath,1,0);}/// <summary>/// 数据结构的简单使用/// </summary>/// <param name="action"></param>public static void SimpleUseReadExcel(int dataCount, Action<int> action){Stopwatch sw = new Stopwatch();sw.Start();try{action(dataCount);sw.Stop();TimeSpan ts2 = sw.Elapsed;//显示程序的内存占用信息ShowProcessInfoTabel();显示程序的耗时信息//Console.Write($"   {ts2.TotalSeconds} s  |");}catch (Exception ex){sw.Stop();//显示程序的耗时信息Console.Write($"   {ex.Message}  |");}}#endregion#region WriteData2ExcelDemoprivate static void WriteData2ExcelDemo(){System.IO.Directory.Delete(OutputExcelFolderPath, true);WriteData2Excel(1000);WriteData2Excel(10000);WriteData2Excel(50000);WriteData2Excel(100000);WriteData2Excel(200000);WriteData2Excel(500000);WriteData2Excel(1000000);Console.WriteLine("-------------------------***********************-------------------------");Console.WriteLine("导出Excel完成...");Console.WriteLine("-------------------------***********************-------------------------");}private static void WriteData2Excel(int dataCount){string strDemoModelExcel = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"Config\Demo.xlsx");List<string> listData = new List<string>();for (int i = 0; i < dataCount; i++){listData.Add($"导出数据到Excel文件中_{i}");}Console.Write($"| {dataCount} |");SimpleUseWriteExcel(strDemoModelExcel, listData, WriteData2Excel4NPOI);SimpleUseWriteExcel(strDemoModelExcel, listData, WriteData2Excel4AsposeCell);SimpleUseWriteExcel(strDemoModelExcel, listData, WriteData2Excel4EPPlus);SimpleUseWriteExcel(strDemoModelExcel, listData, WriteData2Excel4MiniExcel);Console.WriteLine();}private static void WriteData2Excel4MiniExcel(string strDemoModelExcel, List<string> listData){string strOutputExcelPath = System.IO.Path.Combine(OutputExcelFolderPath, "MiniExcel", $"导出数据_{listData.Count}.xlsx");if (!System.IO.Directory.Exists(System.IO.Path.GetDirectoryName(strOutputExcelPath))){System.IO.Directory.CreateDirectory(System.IO.Path.GetDirectoryName(strOutputExcelPath));}MiniExcel_ExcelHelper pMiniExcel_ExcelHelper = new MiniExcel_ExcelHelper();pMiniExcel_ExcelHelper.SaveExcel(strOutputExcelPath, listData);}private static void WriteData2Excel4EPPlus(string strDemoModelExcel, List<string> listData){string strOutputExcelPath = System.IO.Path.Combine(OutputExcelFolderPath, "EPPlus", $"导出数据_{listData.Count}.xlsx");if (!System.IO.Directory.Exists(System.IO.Path.GetDirectoryName(strOutputExcelPath))){System.IO.Directory.CreateDirectory(System.IO.Path.GetDirectoryName(strOutputExcelPath));}System.IO.File.Copy(strDemoModelExcel, strOutputExcelPath, true);EPPlus_ExcelHelper pEPPlus_ExcelHelper = new EPPlus_ExcelHelper();pEPPlus_ExcelHelper.SaveExcel(strOutputExcelPath, listData);}private static void WriteData2Excel4AsposeCell(string strDemoModelExcel, List<string> listData){string strOutputExcelPath = System.IO.Path.Combine(OutputExcelFolderPath, "AsposeCell", $"导出数据_{listData.Count}.xls");if (!System.IO.Directory.Exists(System.IO.Path.GetDirectoryName(strOutputExcelPath))){System.IO.Directory.CreateDirectory(System.IO.Path.GetDirectoryName(strOutputExcelPath));}System.IO.File.Copy(strDemoModelExcel, strOutputExcelPath, true);AsposeCell_ExcelHelper pAsposeCell_ExcelHelper = new AsposeCell_ExcelHelper(strOutputExcelPath);pAsposeCell_ExcelHelper.WriteExcel(0, listData);pAsposeCell_ExcelHelper.Save();}private static void WriteData2Excel4NPOI(string strDemoModelExcel, List<string> listData){string strOutputExcelPath = System.IO.Path.Combine(OutputExcelFolderPath, "NPOI", $"导出数据_{listData.Count}.xlsx");if (!System.IO.Directory.Exists(System.IO.Path.GetDirectoryName(strOutputExcelPath))){System.IO.Directory.CreateDirectory(System.IO.Path.GetDirectoryName(strOutputExcelPath));}System.IO.File.Copy(strDemoModelExcel, strOutputExcelPath, true);NPOI_ExcelHelper pNPOI_ExcelHelper = new NPOI_ExcelHelper(strOutputExcelPath);pNPOI_ExcelHelper.WriteExcel(0, listData);pNPOI_ExcelHelper.SaveXlsxChange(strOutputExcelPath);}/// <summary>/// 数据结构的简单使用/// </summary>/// <param name="action"></param>public static void SimpleUseWriteExcel(string strPhaseInfo, List<string> listData, Action<string,List<string>> action){Stopwatch sw = new Stopwatch();sw.Start();action(strPhaseInfo, listData);sw.Stop();TimeSpan ts2 = sw.Elapsed;显示程序的内存占用信息//ShowProcessInfoTabel();//显示程序的耗时信息Console.Write($"   {ts2.TotalSeconds} s  |");}#endregionprivate static int MB_DIV = 1024 * 1024;/// <summary>/// 显示程序的内存占用信息/// </summary>/// <param name="strPhaseInfo"></param>public static void ShowProcessInfoTabel(){var name = Process.GetCurrentProcess().ProcessName;PerformanceCounter curpcp = new PerformanceCounter("Process", "Working Set - Private", name);PerformanceCounter curtime = new PerformanceCounter("Process", "% Processor Time", name);Console.Write($"   {curpcp.NextValue() / MB_DIV}  |");//实时分析内存至关重要GC.Collect();Application.DoEvents();}}
}

1.2. 数据说明

加载指定个数的List数据写入到Excel中


private static void WriteData2Excel(int dataCount)
{....List<string> listData = new List<string>();for (int i = 0; i < dataCount; i++){listData.Add($"导出数据到Excel文件中_{i}");}....
}

2. 使用场景

2.1. 写入Excel(最大测试数据量100w)

2.1.1. 时间复杂度随数据变化表(单位秒s)

数量NPOIAsposeCellEPPlusMiniExcel
10000.655017 s3.3880952 s0.5505515 s0.0799069 s
100000.6553409 s0.0792955 s0.2258814 s0.4185034 s
500003.9576648 s0.3697489 s0.9928818 s1.2196221 s
1000004.3536023 s0.5724796 s2.0721173 s2.4347283 s
2000008.50407 s1.3098775 s4.0333762 s4.8701496 s
50000022.2753958 s2.8274853 s10.5082423 s12.2345609 s
100000045.3316621 s6.1340543 s21.8507034 s25.1136424 s

2.1.2. 空间复杂度随数据变化表(单位MB)

数量NPOIAsposeCellEPPlusMiniExcel
100025.7656327.0468828.5742229.11719
1000050.062536.0195336.8281339.96484
50000127.003979.0742240.3789157.02734
100000237.3555114.609450.4726683.75
200000449.0469191.878970.65625137.7734
500000981.5508428.0078155.4609320.4336
10000001942.848527.793324.6563657.3281

2.2. 读取Excel

2.2.1. 时间复杂度随数据变化表(单位秒s)

数量NPOIAsposeCellEPPlusMiniExcel
10000.317878 s0.4092103 s0.1444332 s0.1053007 s
100000.9976334 s0.0674619 s0.2152765 s0.457786 s
500005.9264671 s0.1819693 s0.8495237 s1.5807224 s
10000010.4264675 s0.2611479 s1.9973989 s3.1554743 s
20000021.0787552 s0.3267718 s3.3810386 s6.3185499 s
50000055.867668 s0.7786071 s7.9100544 s15.2742102 s
1000000OOM1.772818 s17.7469745 s31.9800223 s

2.2.2. 空间复杂度随数据变化表(单位MB)

数量NPOIAsposeCellEPPlusMiniExcel
100026.5898427.2968828.4804729.44922
1000087.0234435.4179740.0468839.5
50000340.730551.4960965.7890663.10547
100000674.83274.82422104.253997.94141
2000001340.066113.4141181.9219167.75
5000001788.254216.7305419.707381.0391
1000000OOM454.9883654.3984578.8906

2.3. Excel特殊功能

特殊功能NPOIAsposeCellEPPlusMiniExcel
合并单元格TrueTrueTrue暂时没有发现
单元格样式TrueTrueTrue暂时没有发现
公式计算TrueTrueTrue暂时没有发现
插入图TrueTrueTrue暂时没有发现
  1. AsposeCell合并单元格

  1. AsposeCell设置单元格样式

.html

  1. AsposeCell公式计算
XlsSaveOptions saveOpt = new XlsSaveOptions();
CurrentWorkBook.CalculateFormula(true);
  1. AsposeCell插入图表

  1. EPPlus 合并单元格
ws.Cells["A1:C1"].Merge = true;

public static string GetMegerValue(ExcelWorksheet wSheet, int row, int column)
{string range = wSheet.MergedCells[row, column];if (range == null)if (wSheet.Cells[row, column].Value != null)return wSheet.Cells[row, column].Value.ToString();elsereturn "";object value =wSheet.Cells[(new ExcelAddress(range)).Start.Row, (new ExcelAddress(range)).Start.Column].Value;if (value != null)return value.ToString();elsereturn "";
}
  1. EPPlus 设置单元格样式

.html

  1. EPPlus 公式计算
// RowCount、ColumnCount 整数，分别是行计数器、列计数器
ThisCell = Worksheet.Cells[RowCount, ColumnCount];
string StartCell = Worksheet.Cells[4, ColumnCount].Address;
string EndCell = Worksheet.Cells[(RowCount - 1), ColumnCount].Address;
Formula = String.Format("=SUM({0}:{1})", StartCell, EndCell);
ThisCell.Formula = Formula;string cell = Worksheet.Cells[1, 1].Address;
  1. EPPlus 插入图表

.html?ivk_sa=1024320u

3. 附件代码

using NPOI.SS.UserModel;
using NPOI.XSSF.Streaming;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;namespace ConsoleReadWriteExcel
{public class NPOI_ExcelHelper{public NPOI_ExcelHelper(){}public NPOI_ExcelHelper(string strFilePath){Open(strFilePath, 0);CurrentSheet = CurrentExcelWorkbook.GetSheetAt(CurrentExcelWorkbook.ActiveSheetIndex);}#region Property/// <summary>/// 当前Excel操作文档/// </summary>public IWorkbook CurrentExcelWorkbook{get;private set;}/// <summary>/// 当前Excel操作Sheet/// </summary>public ISheet CurrentSheet{get;private set;}/// <summary>/// 当前操作中的异常Exception/// </summary>public Exception CurrentException { get; set; }private IFont _CurrentFont = null;/// <summary>/// 当前的字体/// </summary>public IFont CurrentFont{get{if (_CurrentFont == null && CurrentExcelWorkbook != null){_CurrentFont = CurrentExcelWorkbook.CreateFont();}return _CurrentFont;}private set{_CurrentFont = value;}}private ICellStyle _CurrentCellStyle = null;/// <summary>/// 当前的单元格样式/// </summary>public ICellStyle CurrentCellStyle{get{if (_CurrentCellStyle == null && CurrentExcelWorkbook != null){_CurrentCellStyle = CurrentExcelWorkbook.CreateCellStyle();}return _CurrentCellStyle;}set{_CurrentCellStyle = value;}}/// <summary>/// 得到当前Sheet页的行数/// </summary>public int CurrentSheetRow{get{return CurrentSheet == null ? 0 : CurrentSheet.PhysicalNumberOfRows;}}private int sheetNum = -1;/// <summary>/// 得到当前Sheet页最大单元格数/// </summary>public int CurrentSheetColumn{get{if (sheetNum < 0){sheetNum = 0;if (CurrentSheet != null){for (int i = 0; i < CurrentSheet.PhysicalNumberOfRows; i++){int colNum = GetRowColNum(i);if (colNum > sheetNum){sheetNum = colNum;}}}}return sheetNum;}}public int GetRowColNum(int rowIndex){int colNum = 0;if (CurrentSheet != null && rowIndex >= 0){IRow pRow = CurrentSheet.GetRow(rowIndex);if (pRow != null){colNum = pRow.LastCellNum;}}return colNum;}#endregion/// <summary>/// 打开Excel中的Sheet页（索引）/// </summary>/// <param name="strFilePath"></param>/// <param name="SheetIndex"></param>/// <param name="IsBigOperation"></param>/// <returns></returns>public bool Open(string strFilePath, int SheetIndex, bool IsBigOperation = false){CurrentExcelWorkbook = OpenFile(strFilePath, IsBigOperation);try{CurrentSheet = CurrentExcelWorkbook.GetSheetAt(SheetIndex);}catch (Exception ex){CurrentException = ex;return false;}return true;}/// <summary>/// 打开Excel文件/// </summary>/// <param name="strFilePath"></param>/// <param name="isBigOperation"></param>/// <returns></returns>private IWorkbook OpenFile(string strFilePath, bool isBigOperation){try{using (FileStream fs = new FileStream(strFilePath, FileMode.Open, FileAccess.Read)){IWorkbook workbook = null;if (isBigOperation){XSSFWorkbook pXSSFWorkbook = new XSSFWorkbook(fs);workbook = new SXSSFWorkbook(pXSSFWorkbook, 1000);}else{workbook = WorkbookFactory.Create(fs);}return workbook;}}catch (Exception ex){CurrentException = ex;return null;}}/// <summary>/// 写Excel/// </summary>/// <param name="startRowIndex"></param>/// <param name="pTable"></param>/// <returns></returns>public bool WriteExcel(int startRowIndex, List<string> listData){try{ISheet sheet = CurrentExcelWorkbook.GetSheetAt(0);for (int i = 0; i < listData.Count; i++){IRow pRow = sheet.CreateRow(startRowIndex);for (int j = 0; j < 5; j++){pRow.CreateCell(j).SetCellValue(listData[i].ToString());}startRowIndex++;}return true;}catch (Exception ex){return false;}}/// <summary>/// 保存Xlsx格式修改/// </summary>/// <param name="strExcelFilePath"></param>/// <returns></returns>public bool SaveXlsxChange(string strExcelFilePath){try{using (var file = new FileStream(strExcelFilePath, FileMode.Create, FileAccess.Write)){CurrentExcelWorkbook.Write(file);file.Close();}return true;}catch (Exception ex){return false;}}/// <summary>/// 读取Excel文件内容转换为DataSet,列名依次为 "c0"……c[columnlength-1]/// </summary>/// <param name="FileName">文件绝对路径</param>/// <param name="startRow">数据开始行数(1为第一行)</param>/// <param name="StrartCol">每列的数据类型</param>/// <returns></returns>public DataTable ReadExcel(string FileName, int startRow, int StrartCol){int ertime = 0;int intime = 0;DataTable dt = new DataTable(Path.GetFileName(FileName));DataRow dr;StringBuilder sb = new StringBuilder();NpoiDataType[] ColumnDataType = null;string ColName = "ColName";using (FileStream stream = new FileStream(@FileName, FileMode.Open, FileAccess.Read)){IWorkbook workbook = WorkbookFactory.Create(stream);  //使用接口，自动识别excel2003/2007格式ISheet sheet = workbook.GetSheetAt(0);//得到里面第一个sheetint sheetNum = workbook.NumberOfSheets;int j;IRow row;#region ColumnDataType赋值if (ColumnDataType == null || ColumnDataType.Length <= 0){row = sheet.GetRow(startRow - 1);//得到第i行ColumnDataType = new NpoiDataType[row.LastCellNum + 30 - StrartCol];for (int i = StrartCol; i < ColumnDataType.Length; i++){ICell hs = row.GetCell(i);ColumnDataType[i - StrartCol] = GetCellDataType(hs);}}#endregionfor (j = StrartCol; j < ColumnDataType.Length; j++){row = sheet.GetRow(startRow - 1);//得到第i行Type tp = GetDataTableType(ColumnDataType[j]);string strColumnName = string.Format("{0}{1}", ColName, j); ;dt.Columns.Add(strColumnName, tp);}Dictionary<int, int> dic = new Dictionary<int, int>();List<int> startCols = new List<int>();List<int> endCols = new List<int>();int RowCout = sheet.PhysicalNumberOfRows > sheet.LastRowNum ? sheet.PhysicalNumberOfRows : sheet.LastRowNum;for (int k = 0; k <= RowCout; k++){row = sheet.GetRow(k);if (row == null) continue;int start = 0, end = 0;for (int i = 0; i < row.Cells.Count; i++){if (!string.IsNullOrEmpty(row.Cells[i].ToString())){start = i;break;}}startCols.Add(start);for (int i = 0; i < row.Cells.Count; i++){if (!string.IsNullOrEmpty(row.Cells[i].ToString())){end = row.Cells[i].ColumnIndex;}}endCols.Add(end);}int Start_Col = 9999, End_Col = 0;for (int i = 0; i < startCols.Count; i++){int s = startCols[i], e = endCols[i];if (s < Start_Col)Start_Col = s;if (e > End_Col)End_Col = e;}for (int i = startRow; i <= RowCout; i++){row = sheet.GetRow(i);//得到第i行if (row == null) continue;try{dr = dt.NewRow();for (j = StrartCol; j < ColumnDataType.Length; j++){dr[j - StrartCol] = GetCellData(row, j);}dt.Rows.Add(dr);intime++;}catch (Exception ex){ertime++;sb.Append(string.Format("第{0}行出错：{1}\r\n", i + 1, ex.Message));continue;}}for (int i = dt.Columns.Count - 1; i > End_Col - StrartCol; i--){dt.Columns.RemoveAt(i);}if (Start_Col > 0){for (int l = Start_Col - 1; l >= 0; l--){dt.Columns.RemoveAt(l);}}}return dt;}/// <summary>/// 读Excel-根据NpoiDataType创建的DataTable列的数据类型/// </summary>/// <param name="datatype"></param>/// <returns></returns>private Type GetDataTableType(NpoiDataType datatype){Type tp = typeof(string);//Type.GetType("System.String")switch (datatype){case NpoiDataType.Bool:tp = typeof(bool);break;case NpoiDataType.Datetime:tp = typeof(DateTime);break;case NpoiDataType.Numeric:tp = typeof(double);break;case NpoiDataType.Error:tp = typeof(string);break;case NpoiDataType.Blank:tp = typeof(string);break;}return tp;}/// <summary>/// 获取单元格数据类型/// </summary>/// <param name="hs"></param>/// <returns></returns>private NpoiDataType GetCellDataType(ICell hs){NpoiDataType dtype = NpoiDataType.String;return dtype;}/// <summary>/// 读Excel-得到不同数据类型单元格的数据/// </summary>/// <param name="datatype">数据类型</param>/// <param name="row">数据中的一行</param>/// <param name="column">哪列</param>/// <returns></returns>private object GetCellData(IRow row, int column){ICell cell = row.GetCell(column);if (cell != null){if (cell == null){return "";}CellType datatype = cell.CellType;switch (datatype){case CellType.String:try { return cell.StringCellValue; }catch (Exception ex){return "";}case CellType.Boolean:try { return cell.BooleanCellValue; }catch (Exception ex){return cell.StringCellValue;}case CellType.Numeric:decimal d;string strValue = cell.ToString();if (System.Decimal.TryParse(strValue, out d)){return d;}else{try { return cell.DateCellValue; }catch (Exception ex){return cell.StringCellValue;}}case CellType.Formula:try{return cell.NumericCellValue;}catch (Exception){try{return cell.StringCellValue;}catch (Exception ex){return "";}}case CellType.Error:try{return cell.ErrorCellValue;}catch (Exception ex){return cell.StringCellValue;}case CellType.Blank:try{return cell.StringCellValue;}catch (Exception ex){return "";}default:return "";}}else{return "";}}}/// <summary>/// 枚举(Excel单元格数据类型)/// </summary>public enum NpoiDataType{/// <summary>/// 字符串类型-值为1/// </summary>String,/// <summary>/// 布尔类型-值为2/// </summary>Bool,/// <summary>/// 时间类型-值为3/// </summary>Datetime,/// <summary>/// 数字类型-值为4/// </summary>Numeric,/// <summary>/// 复杂文本类型-值为5/// </summary>Richtext,/// <summary>/// 空白/// </summary>Blank,/// <summary>/// 错误/// </summary>Error}
}

using Aspose.Cells;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;namespace ConsoleReadWriteExcel
{public class AsposeCell_ExcelHelper{public AsposeCell_ExcelHelper() { }public AsposeCell_ExcelHelper(string excelPath){Open(excelPath, 0);CurrentSheet = CurrentWorkBook.Worksheets[CurrentWorkBook.Worksheets.ActiveSheetIndex];}private Exception _CurrentException = null;/// <summary>/// Excel操作异常/// </summary>public Exception CurrentException{get{return _CurrentException;}set{_CurrentException = value;}}/// <summary>/// 当前操作的excel/// </summary>public Workbook CurrentWorkBook{get;private set;}/// <summary>/// 当前操作的Sheet/// </summary>public Worksheet CurrentSheet{get;private set;}public int CurrentSheetColumn{get{int colNum = 0;if (CurrentSheet != null){colNum = CurrentSheet.Cells.MaxColumn + 1;}return colNum;}}public int CurrentSheetRow{get{int rowNum = 0;if (CurrentSheet != null){rowNum = CurrentSheet.Cells.Rows.Count;}return rowNum;}}/// <summary>/// 打开Excel指定Sheet索引/// </summary>/// <param name="strFilePath"></param>/// <param name="SheetIndex"></param>/// <param name="IsBigOperation"></param>/// <returns></returns>public bool Open(string strFilePath, int SheetIndex, bool IsBigOperation = false){try{CurrentWorkBook = new Workbook(strFilePath);CurrentSheet = CurrentWorkBook.Worksheets[SheetIndex];return true;}catch (Exception ex){CurrentException = ex;return false;}}/// <summary>/// 保存当前Excel/// </summary>/// <returns></returns>public bool Save(){try{if (CurrentWorkBook != null){if (!string.IsNullOrEmpty(CurrentWorkBook.FileName)){string filePath = CurrentWorkBook.FileName;XlsSaveOptions saveOpt = new XlsSaveOptions();CurrentWorkBook.CalculateFormula(true);saveOpt.CreateDirectory = true;CurrentWorkBook.Save(filePath, saveOpt);return true;}}return false;}catch (Exception ex){CurrentException = ex;return false;}}/// <summary>/// 写Excel/// </summary>/// <param name="startRowIndex"></param>/// <param name="pTable"></param>/// <returns></returns>public bool WriteExcel(int startRowIndex, List<string> listData){try{for (int i = 0; i < listData.Count; i++){for (int j = 0; j < 5; j++){CurrentSheet.Cells[i, j].PutValue(listData[i].ToString());}}return true;}catch (Exception ex){CurrentException = ex;return false;}}/// <summary>/// 按行读取指定范围单元格值/// </summary>/// <param name="pReadDataDelegate"></param>/// <param name="StartRowIndex"></param>/// <param name="StartColumnIndex"></param>public void GetData(ReadDataDelegate pReadDataDelegate, int StartRowIndex, int StartColumnIndex){if (CurrentSheet != null){int rowMax = CurrentSheet.Cells.MaxRow + 1;int colMax = CurrentSheet.Cells.MaxColumn + 1;for (int i = StartRowIndex; i < CurrentSheet.Cells.Rows.Count; i++){List<object> readDatas = new List<object>();for (int j = StartColumnIndex; j < CurrentSheet.Cells.Columns.Count; j++){readDatas.Add(CurrentSheet.Cells.Rows[i][j].Value);}pReadDataDelegate.Invoke(readDatas, false);}}}}
}
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;namespace ConsoleReadWriteExcel
{public class EPPlus_ExcelHelper{public static void EPPlus_ExcelHelperLicenseContext(){ExcelPackage.LicenseContext = LicenseContext.NonCommercial;}public void SaveExcel(string strExcelPath,List<string> listData){using (var package = new ExcelPackage(new FileInfo(strExcelPath))){ExcelWorksheet worksheet = package.Workbook.Worksheets[0];for (int i = 0; i < listData.Count; i++){for (int j = 0; j < 5; j++){worksheet.Cells[i+1, j+1].Value=listData[i].ToString();}}package.Save();//保存excel}}public void ReadData(ReadDataDelegate pReadDataDelegate, string path, int StartRowIndex, int StartColumnIndex){var fs = new FileStream(path, FileMode.Open, FileAccess.Read);var excel = new ExcelPackage(fs);var worksheet = excel.Workbook.Worksheets[0];var row = worksheet.Dimension.End.Row;var col = worksheet.Dimension.End.Column;for (var i = StartRowIndex; i <= row; i++){List<object> readDatas = new List<object>();for (var j = StartColumnIndex; j <= col; j++){readDatas.Add(worksheet.Cells[i, j].Value);}pReadDataDelegate.Invoke(readDatas, false);}}}
}
using MiniExcelLibs;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;namespace ConsoleReadWriteExcel
{public class MiniExcel_ExcelHelper{public void SaveExcel(string strExcelPath, List<string> listData){var values = new List<Dictionary<string, object>>();foreach (var item in listData){Dictionary<string, object> dic = new Dictionary<string, object>();for (int i = 0; i < 5; i++){dic.Add($"Column{i + 1}", item);}values.Add(dic);}MiniExcel.SaveAs(strExcelPath, values);}internal IEnumerable<UserAccount> ReadData(string strOutputExcelPath){var rows = MiniExcel.Query<UserAccount>(strOutputExcelPath);int count = rows.Count(a => true);return rows;}}class UserAccount{public string Column1 { get; set; }public string Column2 { get; set; }public string Column3 { get; set; }public string Column4 { get; set; }public string Column5 { get; set; }}
}using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;namespace ConsoleReadWriteExcel
{public class MiniExcel_ExcelHelper{public void SaveExcel(string strExcelPath, List<string> listData){var values = new List<Dictionary<string, object>>();foreach (var item in listData){Dictionary<string, object> dic = new Dictionary<string, object>();for (int i = 0; i < 5; i++){dic.Add($"Column{i + 1}", item);}values.Add(dic);}MiniExcel.SaveAs(strExcelPath, values);}internal IEnumerable<UserAccount> ReadData(string strOutputExcelPath){var rows = MiniExcel.Query<UserAccount>(strOutputExcelPath);int count = rows.Count(a => true);return rows;}}class UserAccount{public string Column1 { get; set; }public string Column2 { get; set; }public string Column3 { get; set; }public string Column4 { get; set; }public string Column5 { get; set; }}
}

更多推荐

C#开源Excel组件使用说明

本文发布于:2024-02-25 08:49:33，感谢您对本站的认可！
本文链接:https://www.elefans.com/category/jswz/34/1698415.html
版权声明:本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系，我们将在24小时内删除。
本文标签：使用说明   开源   组件   Excel

发布评论

评论列表 （有 0 条评论）

最近发表

草根站长

>www.elefans.com

编程频道|电子爱好者 - 技术资讯及电子产品介绍！

热门文章

标签列表