C# 使用NPOI导入数据 NPOIHelper.cs-开发框架文库

C# 使用NPOI导入数据 NPOIHelper.cs
NPOIHelper.cs

C# 全选
    /// <summary>
    /// NPOI组件帮助类
    /// </summary>
    public class NPOIHelper
    {
        /// <summary>
        /// 获取Excel第1行列头
        /// </summary>
        /// <param name="file">excel文件</param>
        /// <param name="sheetName">表格名</param>
        /// <param name="rowIndex">列头序号，0:第1行</param>
        /// <returns></returns>
        public static List<String> GetColumnNames(string file, string sheetName, int rowIndex = 0)
        {
            List<String> result = new List<String>();
            IWorkbook workbook;
            string fileExt = Path.GetExtension(file).ToLower();
            using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
            {
                //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
                if (fileExt == ".xlsx")
                {
                    workbook = new XSSFWorkbook(fs);
                }
                else if (fileExt == ".xls")
                {
                    workbook = new HSSFWorkbook(fs);
                }
                else
                {
                    workbook = null;
                }
                if (workbook == null) { return null; }
                ISheet sheet = workbook.GetSheet(sheetName);

                //表头  
                NPOI.SS.UserModel.IRow header = sheet.GetRow(rowIndex);
                for (int i = 0; i < header.LastCellNum; i++)
                {
                    object obj = GetCellValue(file, workbook, header.GetCell(i));

                    if (obj == null || obj.ToString() == string.Empty)
                    {
                        break;
                    }
                    else
                    {
                        result.Add(obj.ToString());
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// 获取所有表格名称(Sheet)
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>
        public static List<String> GetSheetNames(string file)
        {
            List<String> result = new List<String>();
            IWorkbook workbook;
            string fileExt = Path.GetExtension(file).ToLower();
            using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
            {
                //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
                if (fileExt == ".xlsx")
                {
                    workbook = new XSSFWorkbook(fs);
                }
                else if (fileExt == ".xls")
                {
                    workbook = new HSSFWorkbook(fs);
                }
                else
                {
                    workbook = null;
                }
                if (workbook == null) { return null; }

                for (int i = 0; i <= workbook.NumberOfSheets - 1; i++)
                {
                    result.Add(workbook.GetSheetAt(i).SheetName);
                }
            }
            return result;
        }

        /// <summary>
        /// 获取Excel有效行数
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="validateColumnIndex">用于校验资料的列序号</param>
        /// <returns></returns>
        public static int GetMaxRows(ISheet sheet, int validateColumnIndex = 0)
        {
            if (sheet == null) { return 0; }
            try
            {
                IRow row;
                var rowIndex = 0;
                object value = null;
                while (true)
                {
                    row = sheet.GetRow(rowIndex);
                    if (row != null)
                    {
                        value = GetValueByCellStyle(row.Cells[validateColumnIndex], row.Cells[validateColumnIndex].CellType);
                        if (value == null || String.IsNullOrWhiteSpace(value.ToString()))
                        {
                            return rowIndex;
                        }
                        else
                        {
                            rowIndex++;
                        }
                    }
                    else //无效行，退出
                    {
                        return rowIndex;
                    }
                }
            }
            catch
            {
                return sheet.LastRowNum;
            }
        }

        /// <summary>
        /// 获取Excel有效列数
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="validateColumnIndex">用于校验资料的行序号</param>
        /// <returns></returns>
        public static int GetMaxColumns(ISheet sheet, int validateRowIndex = 0)
        {
            IRow row = sheet.GetRow(validateRowIndex);
            var coIndex = 0;
            object value = null;
            while (true)
            {
                if (coIndex >= row.PhysicalNumberOfCells)
                {
                    return row.PhysicalNumberOfCells;
                }

                value = GetValueByCellStyle(row.Cells[coIndex], row.Cells[coIndex].CellType);
                if (value == null || String.IsNullOrWhiteSpace(value.ToString()))
                {
                    return coIndex;
                }
                else
                {
                    coIndex++;
                }
            }
        }

        /// <summary>
        /// Excel导入成DataTable
        /// </summary>
        /// <param name="file">导入路径(包含文件名与扩展名)</param>
        /// <returns></returns>
        public static DataTable ToTable(string file, string sheetName, Type type)
        {
            DataTable dt = new DataTable();
            IWorkbook workbook;
            string fileExt = Path.GetExtension(file).ToLower();
            using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
            {
                //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
                if (fileExt == ".xlsx")
                {
                    workbook = new XSSFWorkbook(fs);
                }
                else if (fileExt == ".xls")
                {
                    workbook = new HSSFWorkbook(fs);
                }
                else
                {
                    workbook = null;
                }
                if (workbook == null) { return null; }
                ISheet sheet = workbook.GetSheet(sheetName);

                var maxRows = GetMaxRows(sheet, 0); //excel有效资料行数
                var maxCols = GetMaxColumns(sheet, 0);//excel有效资料列数

                //表头  
                IRow header = sheet.GetRow(sheet.FirstRowNum);
                List<int> columns = new List<int>();
                for (int i = 0; i < maxCols; i++)
                {
                    object obj = GetCellValue(file, workbook, header.GetCell(i));
                    var colType = type.GetProperty(obj.ToString()).PropertyType;
                    if (colType.IsGenericType)
                    {
                        colType = colType.GenericTypeArguments[0];
                    }

                    dt.Columns.Add(new DataColumn(obj.ToString(), colType));
                    columns.Add(i);
                }

                //数据 , 从第2行开始 
                for (int i = sheet.FirstRowNum + 1; i <= maxRows - 1; i++)
                {
                    DataRow dr = dt.NewRow();
                    foreach (int j in columns)
                    {
                        try
                        {
                            dr[j] = GetCellValue(file, workbook, sheet.GetRow(i).GetCell(j));
                        }
                        catch (Exception ee)
                        {
                            continue;
                        }
                    }
                    dt.Rows.Add(dr);
                }
            }
            return dt;

        }

        /// <summary>
        /// 获取单元格类型
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static object GetCellValue(string strFileName, IWorkbook workbook, ICell cell)
        {

            if (cell == null) return null;

            switch (cell.CellType)
            {
                case CellType.Blank: //BLANK:  
                    return null;
                case CellType.Boolean: //BOOLEAN:  
                    return cell.BooleanCellValue;
                case CellType.Numeric: //NUMERIC:
                    if (DateUtil.IsCellDateFormatted(cell))
                        return cell.DateCellValue;
                    else
                        return cell.NumericCellValue;
                case CellType.String: //STRING:  
                    return cell.StringCellValue;
                case CellType.Error: //ERROR:  
                    return cell.ErrorCellValue;
                case CellType.Formula: //FORMULA:
                    object rv = null;
                    if (Path.GetExtension(strFileName).ToLower().Trim() == ".xlsx")
                    {
                        XSSFFormulaEvaluator eva = new XSSFFormulaEvaluator(workbook);
                        if (eva.Evaluate(cell).CellType == CellType.Numeric)
                        {
                            rv = eva.Evaluate(cell).NumberValue;
                        }
                        else
                        {
                            rv = eva.Evaluate(cell).StringValue;
                        }
                    }
                    else
                    {
                        HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(workbook);
                        if (eva.Evaluate(cell).CellType == CellType.Numeric)
                        {
                            rv = eva.Evaluate(cell).NumberValue;
                        }
                        else
                        {
                            rv = eva.Evaluate(cell).StringValue;
                        }
                    }
                    return rv;
                default:
                    return "=" + cell.CellFormula;
            }
        }

        /// <summary>
        /// 根据单元格的类型获取单元格的值
        /// </summary>
        /// <param name="rowCell"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public static string GetValueByCellStyle(ICell rowCell, CellType? type)
        {
            string value = string.Empty;
            switch (type)
            {
                case CellType.String:
                    value = rowCell.StringCellValue;
                    break;
                case CellType.Numeric:
                    if (DateUtil.IsCellInternalDateFormatted(rowCell))
                    {
                        value = DateTime.FromOADate(rowCell.NumericCellValue).ToString();
                    }
                    else if (DateUtil.IsCellDateFormatted(rowCell))
                    {
                        value = DateTime.FromOADate(rowCell.NumericCellValue).ToString();
                    }
                    //有些情况，时间搓？数字格式化显示为时间,不属于上面两种时间格式
                    else if (rowCell.CellStyle.GetDataFormatString() == null)
                    {
                        value = DateTime.FromOADate(rowCell.NumericCellValue).ToString();
                    }
                    else
                    {
                        value = rowCell.NumericCellValue.ToString();
                    }
                    break;
                case CellType.Boolean:
                    value = rowCell.BooleanCellValue.ToString();
                    break;
                case CellType.Error:
                    value = ErrorEval.GetText(rowCell.ErrorCellValue);
                    break;
                case CellType.Formula:
                    //  TODO: 是否存在 嵌套 公式类型
                    value = GetValueByCellStyle(rowCell, rowCell?.CachedFormulaResultType);
                    break;
            }
            return value;
        }

        /// <summary>
        /// Excel文件是否有效
        /// </summary>
        /// <param name="file"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        internal static bool IsFileValid(string file, out string msg)
        {
            msg = string.Empty;
            try
            {
                if (!File.Exists(file)) throw new Exception("Excel文件不存在！");

                IWorkbook workbook;
                string fileExt = Path.GetExtension(file).ToLower();
                using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
                {
                    //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
                    if (fileExt == ".xlsx")
                    {
                        workbook = new XSSFWorkbook(fs);
                    }
                    else if (fileExt == ".xls")
                    {
                        workbook = new HSSFWorkbook(fs);
                    }
                    else
                    {
                        workbook = null;
                    }

                    if (workbook == null)
                    {
                        throw new Exception("加载Excel文件失败！");
                    }

                    workbook.Close();
                }
                return true;
            }
            catch (Exception ex)
            {
                msg = ex.Message;
                return false;
            }
        }
    }
原文链接：https://www.cscode.net/archive/newdoc/479940898418693.html
NewDoc C/S框架网 2023-11-07 14:24 2023-11-07 14:24