(.Net Core EPPlus使用)Aspose.Cells替代方案

由于之前使用的Aspose.Cells会有数量限制

所以转换一个excel解析包

首先引入NuGet包

点击查看代码

        /// 根据文件路径获取datatable
        /// <param name="filePath">文件路径</param>
        private DataTable GetDataTableByFilePath(string filePath)
        {
            //最终的datatable
            var resultData = new DataTable();

            try
            {
                using (var stream = new FileStream(filePath, FileMode.Open))
                {
                    ExcelPackage.LicenseContext = LicenseContext.NonCommercial;//不加会报错 ： Epplus: Please set the ExcelPackage.LicenseContext property
                    using (ExcelPackage package = new ExcelPackage(stream))
                    {
                        ExcelWorksheet sheet = package.Workbook.Worksheets[0];

                        var fileName = Path.GetFileName(filePath).ToLower();
                        int beginRowIndex = 0;//数据开始行下标
                        int columnCount = sheet.Dimension.End.Column;//最大列号
                        int rowCount = sheet.Dimension.End.Row;//最大行号

                        //数据总表
                        var data = (object[,])sheet.Cells.Value;

                        //时间类型的列
                        List<string> datetimeColumnNameList = new List<string>() 
                        {
                            "TimeCalendarDay"
                        };
                        
                        for (int i = 0; i < columnCount; i++)
                        {
                            var colname = (data[beginRowIndex, i] ?? "").ToString();
                            if (!string.IsNullOrEmpty(colname))
                            {
                                resultData.Columns.Add(colname);
                            }
                        }

                        for (int i = beginRowIndex + 1; i < rowCount; i++)
                        {
                            DataRow dataRow = resultData.NewRow();
                            for (int j = 0; j < columnCount; j++)
                            {
                                var value = data[i, j];

                                //判断为日期的字段将特殊处理
                                if (datetimeColumnNameList.Contains(resultData.Columns[j].ColumnName))
                                {
                                    value = DateTime.FromOADate(double.Parse(value.ToString())).ToString("yyyy/MM/dd", System.Globalization.DateTimeFormatInfo.InvariantInfo);
                                }

                                dataRow[j] = value;
                            }
                            resultData.Rows.Add(dataRow);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, ex.Message);
                throw new Exception(ex.Message);
            }

            return resultData;
        }