通用导入解析方案 输出导入模板 excel自动解析为实体 基于npoi

使用方法

0. 建立实体
   注意：通过Description在用户获取模板时输出到标题中，如果不配做则为字段名;
   ExcelExIndexAttrbute可以调整字段所在的列索引，否则将按字段顺序依次向后排序;

另外，ExcelExIndexAttrbute为重设列索引，其他字段如果未设置此特性对应的列属性将依次向后排列，

例如里面的模型，字段对应列索引关系如下

A：0
B：1
C：2
D：9
E：3

public class ExcelExModel
    {
        //映射导入模板的标题
        [Description("X")]
        public string A { get; set; }
        public string B { get; set; }
        public int C { get; set; }

        [ExcelExIndexAttrbute(9)]
        public double D { get; set; }


        public int E { get; set; }
    }

1. 导出实体类对应的模板

var x = new ExcelHelperEx<ExcelExModel>();
x.GetImportTemplate<ExcelExModel>("C:\\Users\\t\\Desktop\\result.xlsx");

2. 解析导入的数据

• 自动跳过空白行
• 自动转成实体类

var x = new ExcelHelperEx<ExcelExModel>();
            //var model = x.ParseFile("C:\\Users\\ivesBao\\Desktop\\a.xlsx");
            var model = x.ParseFile("C:\\Users\\ivesBao\\Desktop\\b.xlsx");
            Console.WriteLine(JsonConvert.SerializeObject(model));

2. 自定义部分标题列名称
   UserRoleCreateInDto为自定义的一个类型

var excelHelper = new ExcelHelperEx<UserRoleCreateInDto>();
var xx = excelHelper.ParseFile(@"C:\Users\ives\Desktop\user.xlsx");

//excelHelper.GetCusImportTemplate()
var cusTitle = new Dictionary<int, string>();
cusTitle.Add(0, "ddd");
cusTitle.Add(1, "asfd");
excelHelper.GetImportTemplate(@"C:\Users\ives\Desktop\ttt.xlsx", cusTitle);

var cusList = excelHelper.ParseFile(@"C:\Users\ives\Desktop\user.xlsx", true, cusTitle);

excelHelper.Export(@"C:\Users\ives\Desktop\ttt.xlsx", cusList,false,cusTitle);

UserRoleCreateInDto

 public class UserRoleCreateInDto 
 {

     /// <summary>
     /// 用户编号
     /// </summary>
     [ExcelExIndexAttrbute(0)]
     public string UserNo { get; set; }

     /// <summary>
     /// 用户姓名
     /// </summary>
     [ExcelExIndexAttrbute(1)]
     public string UserName { get; set; }

     /// <summary>
     /// 用户邮箱
     /// </summary>
     [ExcelExIndexAttrbute(2)]
     public string? UserMail { get; set; }

     /// <summary>
     /// 用户手机
     /// </summary>
     [ExcelExIndexAttrbute(3)]
     public string? UserMobile { get; set; }

     /// <summary>
     /// 用户微信
     /// </summary>
     [ExcelExIndexAttrbute(4)]
     public string? UserWechat { get; set; }

     /// <summary>
     /// 用户钉钉
     /// </summary>
     [ExcelExIndexAttrbute(5)]
     public string? UserDingding { get; set; }


     /// <summary>
     /// 是否启用 N--未启用;Y--启用
     /// </summary>
     [ExcelExIndexAttrbute(6)]
     public string IsEnabled { get; set; }

     /// <summary>
     /// 性别 0--男;1--女
     /// </summary>
     [ExcelExIndexAttrbute(7)]
     public int Sex { get; set; }


     /// <summary>
     /// 用户角色
     /// </summary>
     [ExcelExIndexAttrbute(8)]
     public string UserRoleStr { get; set; }
 }

excel解析相关逻辑处理

public class ExcelHelper
    {
        public IWorkbook obook;
        public ISheet osheet;
        private string filePath;
        private bool isXls = false;

        /// <summary>
        /// 
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="isWrite">是否为读取excel</param>
        /// <exception cref="NotSupportedException"></exception>
        public ExcelHelper(string filePath, bool isWrite = false)
        {
            ExceptionHelper.CheckException(!isWrite && !File.Exists(filePath), "FileNotExist");
            this.filePath = filePath;
            string extension = System.IO.Path.GetExtension(filePath);

            switch (extension.ToLower())
            {
                case ".xls":
                    isXls = true;
                    obook = new HSSFWorkbook();
                    break;
                case ".xlsx":
                    obook = new XSSFWorkbook();
                    break;
                default:
                    throw new NotSupportedException();

            }
            if (isWrite)
            {

                osheet = obook.CreateSheet("sheet1");
            }
            else
            {
                using (FileStream fs = File.OpenRead(filePath))
                {
                    if (isXls)
                    {
                        obook = new HSSFWorkbook(fs); //把xls文件中的数据写入wk中
                    }
                    else
                    {
                        obook = new XSSFWorkbook(fs);//把xlsx文件中的数据写入wk中
                    }
                }

                osheet = obook.GetSheetAt(0);
            }
        }

        /// <summary>
        /// 切换sheet
        /// </summary>
        /// <param name="sheetNo"></param>
        public void ChangeSheet(int sheetNo)
        {
            osheet = obook.GetSheetAt(sheetNo);
        }

        /// <summary>
        /// 获取指定行列的数据
        /// </summary>
        /// <param name="row"></param>
        /// <param name="col"></param>
        /// <returns></returns>
        private ICell GetCell(int row, int col)
        {
            return osheet.GetRow(row).GetCell(col);
        }

        public string GetString(int row, int col)
        {
            return GetCell(row, col).StringCellValue;
        }

        public double GetDouble(int row, int col)
        {
            return GetCell(row, col).NumericCellValue;
        }

        public DateTime GetDateTime(int row, int col)
        {
            return GetCell(row, col).DateCellValue;
        }

        public bool GetBool(int row, int col)
        {
            return GetCell(row, col).BooleanCellValue;
        }

        public int GetLastRowNo()
        {
            return osheet.LastRowNum;
        }

        public bool IsCellNull(int row, int col)
        {
            return GetCell(row, col) == null;
        }

        public bool IsCellBlank(int row, int col)
        {
            if (IsCellNull(row, col))
            {
                return true;
            }
            else
            {
                return GetCell(row, col).CellType == CellType.Blank;
            }
        }



        public string GetValue(int row, int col)
        {
            ICell cell = GetCell(row, col);

            switch (cell.CellType)
            {
                case CellType.Blank:
                    return string.Empty;
                case CellType.Boolean:
                    return cell.BooleanCellValue.ToString(); ;
                case CellType.Error:
                    return cell.ErrorCellValue.ToString(); ;
                case CellType.Numeric:
                    return (DateUtil.IsCellDateFormatted(cell)) ? cell.DateCellValue.ToString() : cell.NumericCellValue.ToString(); ;
                case CellType.String:
                    return cell.StringCellValue;
                case CellType.Formula: //公式
                    try
                    {
                        IFormulaEvaluator formulaEvaluator;
                        if (isXls)
                        {
                            formulaEvaluator = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                        }
                        else
                        {
                            formulaEvaluator = new XSSFFormulaEvaluator(cell.Sheet.Workbook);
                        }

                        var evaluatCell = formulaEvaluator.EvaluateInCell(cell);
                        return evaluatCell.ToString();
                    }
                    catch
                    {
                        return "CellType: Formula. " + cell.NumericCellValue.ToString();
                    }
                case CellType.Unknown: //无法识别类型
                default: //默认类型
                    return cell.ToString();
            }
        }


        public object GetValueAuto(int row, int col)
        {
            ICell cell = GetCell(row, col);

            return GetByCell(cell);
        }

        private object GetByCell(ICell cell)
        {
            if (cell == null)
            {
                return null;
            }
            switch (cell.CellType)
            {
                case CellType.Blank:
                    return string.Empty;
                case CellType.Boolean:
                    return cell.BooleanCellValue; ;
                case CellType.Error:
                    return cell.ErrorCellValue.ToString(); ;
                case CellType.Numeric:
                    return (DateUtil.IsCellDateFormatted(cell)) ? cell.DateCellValue : cell.NumericCellValue;
                case CellType.String:
                    return cell.StringCellValue;
                case CellType.Formula: //公式
                    try
                    {
                        IFormulaEvaluator formulaEvaluator;
                        if (isXls)
                        {
                            formulaEvaluator = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                        }
                        else
                        {
                            formulaEvaluator = new XSSFFormulaEvaluator(cell.Sheet.Workbook);
                        }

                        formulaEvaluator.EvaluateInCell(cell);

                        var evaluatCell = formulaEvaluator.EvaluateInCell(cell);
                        return GetByCell(evaluatCell);
                    }
                    catch
                    {
                        return "CellType: Formula. " + cell.StringCellValue;
                    }
                case CellType.Unknown: //无法识别类型
                default: //默认类型
                    return cell.ToString();
            }
        }

        /// <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="r1">左上角单元格行标(从0开始，下同)</param>
        /// <param name="c1">左上角单元格列标</param>
        /// <param name="r2">右下角单元格行标</param>
        /// <param name="c2">右下角单元格列标</param>
        public void Merge(int r1, int c1, int r2, int c2)
        {
            osheet.AddMergedRegion(new CellRangeAddress(r1, c1, r2, c2));
        }

        /// <summary>
        /// 设置单元格内容
        /// </summary>
        /// <param name="row">单元格行标(从0开始，下同)</param>
        /// <param name="col">单元格列标</param>
        /// <param name="o">写入内容</param>
        public void SetValue(int r, int c, object o)
        {
            if (o != null)
            {
                if (r <= osheet.LastRowNum)
                {
                    IRow row = osheet.GetRow(r);
                    if (row == null)
                    {
                        row = osheet.CreateRow(r);
                        row.HeightInPoints = 14;
                    }
                    if (c <= row.LastCellNum)
                    {
                        ICell cell = row.CreateCell(c);
                        cell.SetCellValue(o.ToString());
                    }
                    else
                    {
                        for (int j = row.LastCellNum; j < c; j++)
                        {
                            row.CreateCell(j + 1);
                            ICell cell22 = row.GetCell(j + 1);
                            ICellStyle style = obook.CreateCellStyle();
                            cell22.CellStyle = style;
                        }
                        ICell cell = row.GetCell(c);
                        cell.SetCellValue(o.ToString());
                    }
                }
                else
                {
                    for (int i = osheet.LastRowNum; i < r; i++)
                    {
                        IRow row22 = osheet.CreateRow(i + 1);
                        row22.HeightInPoints = 14;
                    }
                    IRow row = osheet.GetRow(r);
                    for (int j = row.LastCellNum; j < c; j++)
                    {
                        row.CreateCell(j + 1); ;
                        ICell cell22 = row.GetCell(j + 1);
                        ICellStyle style = obook.CreateCellStyle();
                        cell22.CellStyle = style;
                    }
                    ICell cell = row.GetCell(c);
                    cell.SetCellValue(o.ToString());

                }

            }
        }

        /// <summary>
        /// 设置行高
        /// </summary>
        /// <param name="r">行数</param>
        /// <param name="height">高度</param>
        public void SetRowHeight(int r, int height)
        {
            if (r <= osheet.LastRowNum)
            {
                IRow row = osheet.GetRow(r);
                if (row != null)
                {
                    row.HeightInPoints = height;
                }
            }
        }

        /// <summary>
        /// 设置字体宽度
        /// </summary>
        /// <param name="c">列标</param>
        /// <param name="width">宽度值（例如设置为1，表示一个英文字符的宽度）</param>
        public void SetCollumWdith(int c, int width)
        {
            osheet.SetColumnWidth(c, 256 * width);
        }

        /// <summary>
        /// 设置单元格对齐方式
        /// </summary>
        /// <param name="r">行标</param>
        /// <param name="c">列标</param>
        /// <param name="align">对齐方式（'L',左对齐；'C'居中；'R'右对齐）</param>
        public void SetCellAlignment(int r, int c, char align)
        {
            if (r <= osheet.LastRowNum)
            {
                IRow row = osheet.GetRow(r);
                if (row != null)
                {
                    if (c <= row.LastCellNum)
                    {
                        ICell cell = row.GetCell(c);
                        ICellStyle style = cell.CellStyle;
                        //设置单元格的样式：水平对齐居中
                        if (align == 'C')
                            style.Alignment = HorizontalAlignment.Center;
                        else if (align == 'L')
                            style.Alignment = HorizontalAlignment.Left;
                        else if (align == 'R')
                            style.Alignment = HorizontalAlignment.Right;
                        cell.CellStyle = style;
                    }
                }
            }
        }

        /// <summary>
        /// 设置字体样式
        /// </summary>
        /// <param name="r">行标</param>
        /// <param name="c">列标</param>
        /// <param name="size">字体大小，0为默认</param>
        /// <param name="f">字体样式（‘B’加粗，‘I’斜体）</param>
        /// <param name="color">字体颜色('R'红,'B'蓝,'G'绿,'Y'黄,'P'粉,'O'橙,'W'白)</param>
        public void SetCellFont(int r, int c, int size, char f, char color)
        {
            if (r <= osheet.LastRowNum)
            {
                IRow row = osheet.GetRow(r);
                if (row != null)
                {
                    if (c <= row.LastCellNum)
                    {
                        ICell cell = row.GetCell(c);
                        ICellStyle style = cell.CellStyle;
                        //新建一个字体样式对象
                        IFont font = obook.CreateFont();
                        //设置字体大小
                        if (size > 0)
                            font.FontHeightInPoints = Convert.ToInt16(size);
                        switch (f)
                        {
                            case 'B':
                                {
                                    //设置字体加粗样式
                                    font.IsBold = true;
                                }
                                break;
                            case 'I':
                                {
                                    //设置字体加粗样式
                                    font.IsItalic = true;
                                }
                                break;
                        }
                        switch (color)
                        {
                            case 'R': { font.Color = HSSFColor.Red.Index; } break;
                            case 'B': { font.Color = HSSFColor.Blue.Index; } break;
                            case 'G': { font.Color = HSSFColor.Green.Index; } break;
                            case 'Y': { font.Color = HSSFColor.Yellow.Index; } break;
                            case 'P': { font.Color = HSSFColor.Pink.Index; } break;
                            case 'O': { font.Color = HSSFColor.Orange.Index; } break;
                            case 'W': { font.Color = HSSFColor.White.Index; } break;
                        }
                        //使用SetFont方法将字体样式添加到单元格样式中 
                        style.SetFont(font);
                        //将新的样式赋给单元格
                        cell.CellStyle = style;
                    }
                }
            }
        }

        /// <summary>
        /// 写入到Excel文档
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="path"></param>
        /// <returns></returns>
        public bool Export(string path)
        {
            // 写入到客户端  
            using (FileStream fs = new FileStream(path, FileMode.OpenOrCreate, FileAccess.ReadWrite))
            {
                obook.Write(fs);
            }
            return true;
        }

        /// <summary>
        /// 使用构造对象时的文件名保存文件
        /// </summary>
        /// <returns></returns>
        public bool Export()
        {
            return Export(filePath);
        }

        /// <summary>
        /// 导出数据设置
        /// </summary>
        /// <param name="dt">源数据</param>
        ///  <param name="IsHead">是否需要表头</param>
        public void ExportDataToExcel(DataTable dt, bool IsHead = true)
        {
            // 数据行索引
            int rowIndex = 0;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (IsHead)
                {
                    // 设置表头
                    IRow headerRow = osheet.CreateRow(0);
                    foreach (DataColumn column in dt.Columns)
                    {
                        int columnIndex = column.Ordinal;
                        headerRow.CreateCell(columnIndex).SetCellValue(column.Caption);
                        osheet.SetColumnWidth(columnIndex, 50 * 128);
                    }

                    // 固定首行
                    osheet.CreateFreezePane(0, 1, 0, dt.Columns.Count - 1);
                    rowIndex++;
                }
                // 填充数据
                IRow dataRow = osheet.CreateRow(rowIndex);
                foreach (DataColumn column in dt.Columns)
                {
                    dataRow.CreateCell(column.Ordinal).SetCellValue(Convert.ToString(dt.Rows[i][column]));
                    //dataRow.GetCell(column.Ordinal).CellStyle = styleText;
                }
                rowIndex++;
            }
        }


        public void Dispose()
        {
            obook.Close();
            obook = null;
            osheet = null;
            GC.Collect();
        }
    }

工具属性类型

public class ExcelParseModel
    {
        public string Title { get; set; }
        public PropertyInfo PropInfo { get; set; }
    }


public class ExcelExIndexAttrbute : Attribute
    {
        /// <summary>
        /// 列索引 - 从0开始
        /// </summary>
        public int ColIndex { get; set; }


        public ExcelExIndexAttrbute(int colIndex)
        {
            ColIndex = colIndex;
        }
    }

导入扩展逻辑

public class ExcelHelperEx<T>
{
    /// <summary>
    /// 获取自定义标题
    /// </summary>
    public Dictionary<int, ExcelParseModel> GetCusTitle(Dictionary<int, string> cusTitle)
    {
        var typeInfo = typeof(T).GetProperties();
        ExceptionHelper.CheckException(typeInfo.IsNullOrEmpty(), "TemplateWithoutProperty");

        var titleDic = new Dictionary<int, ExcelParseModel>();
        int index = 0;

        //标题获取
        foreach (var item in typeInfo)
        {
            string propName = item.Name;

            var attrList = item.GetCustomAttributes(false);
            ExcelExIndexAttrbute indexAttr = null;
            if (!attrList.IsNullOrEmpty())
            {
                DescriptionAttribute descAttr = null;

                //解析需要的特性
                foreach (var attr in attrList)
                {
                    if (attr is ExcelExIndexAttrbute indexAttrData)
                    {
                        indexAttr = indexAttrData;
                    }
                }

                if (descAttr != null)
                {
                    propName = descAttr.Description;
                }
            }

            var finalIndex = 0;

            if (indexAttr != null)
            {
                finalIndex = indexAttr.ColIndex;
            }
            else
            {
                //自定义标题时跳过未配置的字段
                continue;
                //finalIndex = index++;
            }

            if (cusTitle.ContainsKey(finalIndex))
            {
                propName = cusTitle[finalIndex];
            }

            titleDic.Add(finalIndex, new ExcelParseModel { Title = propName, PropInfo = item });
        }

        return titleDic;
    }

    /// <summary>
    /// 根据对象获取导入模板
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public void GetImportTemplate(string fileName, Dictionary<int, string> cusTitle = null)
    {
        var titleDic = cusTitle != null ? GetCusTitle(cusTitle) : ParseTitleDic();

        ExceptionHelper.CheckException(titleDic == null || titleDic.Count == 0, "TemplateWithoutProperty");

        var excelHelper = new ExcelHelper(fileName, true);

        //标题导出
        foreach (var item in titleDic)
        {
            excelHelper.SetValue(0, item.Key, item.Value.Title);
        }
        excelHelper.Export();
        excelHelper.Dispose();
    }

    /// <summary>
    /// 解析标题字典
    /// </summary>
    /// <param name="typeInfo">key为列索引</param>
    /// <returns></returns>
    private Dictionary<int, ExcelParseModel> ParseTitleDic()
    {
        var typeInfo = typeof(T).GetProperties();
        ExceptionHelper.CheckException(typeInfo.IsNullOrEmpty(), "TemplateWithoutProperty");

        var titleDic = new Dictionary<int, ExcelParseModel>();
        int index = 0;

        //标题获取
        foreach (var item in typeInfo)
        {
            string propName = item.Name;

            var attrList = item.GetCustomAttributes(false);
            ExcelExIndexAttrbute indexAttr = null;
            if (!attrList.IsNullOrEmpty())
            {
                DescriptionAttribute descAttr = null;

                //解析需要的特性
                foreach (var attr in attrList)
                {
                    if (attr is DescriptionAttribute descAttrData)
                    {
                        descAttr = descAttrData;
                    }
                    if (attr is ExcelExIndexAttrbute indexAttrData)
                    {
                        indexAttr = indexAttrData;
                    }
                }

                if (descAttr != null)
                {
                    propName = descAttr.Description;
                }
            }

            var finalIndex = 0;

            if (indexAttr != null)
            {
                finalIndex = indexAttr.ColIndex;
            }
            else
            {
                finalIndex = index++;
            }
            titleDic.Add(finalIndex, new ExcelParseModel { Title = propName, PropInfo = item });
        }

        return titleDic;
    }

    public List<T> ParseFile(string fileName, bool skipTitle = true, Dictionary<int, string> cusTitle = null)
    {
        int startRow = 0;
        if (skipTitle)
        {
            startRow++;
        }
        var result = new List<T>();

        var titleDic = cusTitle != null ? GetCusTitle(cusTitle) : ParseTitleDic();
        ExceptionHelper.CheckException(titleDic == null || titleDic.Count == 0, "TemplateWithoutProperty");


        var excelHelper = new ExcelHelper(fileName);
        var lastRowNo = excelHelper.GetLastRowNo();
        for (int i = startRow; i <= lastRowNo; i++)
        {
            //var ins = Activator.CreateInstance<T>();
            var tempData = new Dictionary<string, object>();
            bool isBlankLine = true;

            foreach (var item in titleDic)
            {
                var cell = excelHelper.GetCell(i, item.Key);
                if (cell == null || cell.CellType == NPOI.SS.UserModel.CellType.Blank)
                {
                    continue;
                }

                isBlankLine = false;

                object finalData = null;
                var data = excelHelper.GetValueAuto(i, item.Key);
                finalData = data;

                if (item.Value.PropInfo.PropertyType == typeof(string))
                {
                    var intValue = Convert.ToString(data);
                    finalData = intValue;
                }

                if (item.Value.PropInfo.PropertyType == typeof(double))
                {
                    var intValue = Convert.ToDouble(data);
                    finalData = intValue;
                }

                if (item.Value.PropInfo.PropertyType == typeof(int))
                {
                    var intValue = Convert.ToInt32(data);
                    finalData = intValue;
                }

                tempData.Add(item.Value.PropInfo.Name, finalData);
            }

            if (isBlankLine)
            {
                continue;
            }

            var jsonData = JsonConvert.SerializeObject(tempData);
            var convertData = JsonConvert.DeserializeObject<T>(jsonData);

            result.Add(convertData);
        }

        excelHelper.Dispose();


        return result;
    }

    public bool Export(string fileName, List<T> data, bool skipTitle = false, Dictionary<int, string> cusTitle = null)
    {
        int startRow = 0;
        if (skipTitle)
        {
            startRow++;
        }
        var result = new List<T>();
        var titleDic = cusTitle != null ? GetCusTitle(cusTitle) : ParseTitleDic();
        ExceptionHelper.CheckException(titleDic == null || titleDic.Count == 0, "TemplateWithoutProperty");

        var excelHelper = new ExcelHelper(fileName, true);

        int row = 0;
        if (!skipTitle)
        {
            foreach (var item in titleDic)
            {
                excelHelper.SetValue(row, item.Key, item.Value.Title);
            }

            row++;
        }
        foreach (var item in data)
        {
            foreach (var field in titleDic)
            {
                var itemValue = field.Value.PropInfo.GetValue(item);

                excelHelper.SetValue(row, field.Key, itemValue);
            }

            row++;
        }

        return excelHelper.Export();
    }
}