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

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

1.1. Demo代码框架

1.1.1. Demo代码思路：

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

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

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

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

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

  • 1. 合并单元格
  • 2. 单元格样式
  • 3. 公式计算
  • 4. 插入图

1.1.2. 代码框架

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

• NPOI

• Aspose.Cells for .NET

• EPPlus

https://github.com/JanKallman/EPPlus/wiki/Formula-Calculation

• 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 ReadData4ExcelDemo
        private 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 WriteData2ExcelDemo
        private 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  |");
        }
        #endregion

        private 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)

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

2.2. 读取Excel

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

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

2.3. Excel特殊功能

1. AsposeCell合并单元格

https://blog.csdn.net/u010104384/article/details/108123728

2. AsposeCell设置单元格样式

https://wenku.baidu.com/view/52e34c10f211f18583d049649b6648d7c1c708d0.html

3. AsposeCell公式计算

XlsSaveOptions saveOpt = new XlsSaveOptions();
CurrentWorkBook.CalculateFormula(true);

4. AsposeCell插入图表

https://blog.csdn.net/qq_46035189/article/details/119966058

5. 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();
        else
            return "";
    object value =
        wSheet.Cells[(new ExcelAddress(range)).Start.Row, (new ExcelAddress(range)).Start.Column].Value;
    if (value != null)
        return value.ToString();
    else
        return "";
}

6. EPPlus 设置单元格样式

https://wenku.baidu.com/view/5061cae50f22590102020740be1e650e52eacfd9.html

7. 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;

8. EPPlus 插入图表

https://www.cnblogs.com/miaosha5s/p/13168868.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);//得到里面第一个sheet
                int 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);
                    }
                }
                #endregion
                for (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; }
    }
}