C# NPOI导入

特别注意：
导入的Excel中标题不能有重复的，否则会导入出错

Excel模板1

Excel模板2

模板1在导入时，Excel的title对应后台字段的写法：

Dictionary<string, string> dict = new Dictionary<string, string>()
                {
   
   
                    {
   
     "清单层级码", "ListLevelCode" },
                    {
   
     "清单名称", "ListName" },
                    {
   
     "清单编码", "ListCode" },
                    {
   
     "清单单位", "ListUnit" },
                    {
   
     "单价(元)", "UnitPrice" },
                    {
   
     "合同工作量工程量", "Quantities" },
                    {
   
     "合价（元)", "TotalPrice" },
                };

模板2在导入时，Excel的title对应后台字段的写法：
因为模板2是跨行标题，其实在后台读取时，第一行的title和第二行第一个单元格名称合并，其余不变，如主合同那一栏，后台读取的title其实是“主合同清单层级码”， “清单名称”，“清单编码”，所以对应的写法就为：

Dictionary<string, string> dict = new Dictionary<string, string>()
                {
   
   
                    {
   
     "主合同清单层级码", "ListLevelCode" },
                    {
   
     "清单名称", "ListName" },
                    {
   
     "清单编码", "ListCode" },
                    {
   
     "清单单位", "ListUnit" },
                    {
   
     "单价(元)", "UnitPrice" },
                   //........剩余的省略不写
                };

公用方法：

 public class ExcelHelper
    {
   
   
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="excelPath"></param>
        /// <param name="headerPropDict">表头字典</param>
        /// <param name="sheetName"></param>
        /// <param name="indexHeaderStart">表头开始行</param>
        /// <param name="indexHeaderEnd">表头结束行</param>
        /// <returns></returns>
        public List<T> ExcelToCollection<T>(Stream excelFileStream, Dictionary<string, string> headerPropDict, string extension, string sheetName, int indexHeaderStart, int indexHeaderEnd ,ref Dictionary<T, string> errorDic)
        {
   
   
            var result = new List<T>();
            List<string> errorInfoList = new List<string>();
            if (indexHeaderStart < 0 || indexHeaderEnd < 0 || indexHeaderStart > indexHeaderEnd)
            {
   
   
                throw new ArgumentException();
            }
            try
            {
   
   
                IWorkbook workbook;
                if (extension == ".xls")
                {
   
   
                    workbook = new HSSFWorkbook(excelFileStream);
                }
                else if (extension == ".xlsx")
                {
   
   
                    workbook = new XSSFWorkbook(excelFileStream);
                }
                else
                {
   
   
                    throw new Exception("格式不支持");
                }

                ISheet sheet;
                if (!string.IsNullOrEmpty(sheetName))
                {
   
   
                    sheet = workbook.GetSheet(sheetName);
                    if (sheet == null)
                    {
   
   
                        throw new Exception("sheet name Error");
                    }
                }
                else
                {
   
   
                    sheet = workbook.GetSheetAt(0);
                }

                var headerDict = GetHeaderIndexDict(sheet, indexHeaderStart, indexHeaderEnd);
                if (headerDict.Count == 0)
                {
   
   
                    throw new Exception("未读取到表头，请检查Excel模板！");
                }
                int errRow = 0;
                for (int i = indexHeaderEnd; i <= sheet.LastRowNum; i++)
                {
   
   
                    // 产生一个新的泛型对象
                    var model = Activator.CreateInstance<T>();

                    // 是否为空行
                    bool isEmptyRow = true;

                    IRow dataRow = sheet.GetRow(i);
                    int cellCount = headerDict.Count;
                    errRow = i + 1;
                    if (dataRow != null)
                    {
   
   
                        // 循环列数据
                        for (int j = dataRow.FirstCellNum; j < cellCount; j++)
                        {
   
   
                            var propertyName = headerPropDict[headerDict[j]];
                            string keyName = headerDict[j];
                            PropertyInfo prop = model.GetType().GetProperty(propertyName);
                            //string keyName = headerPropDict[j].Key;
                            ICell cell = dataRow.GetCell(j);
                            var value = GetCellValue<T>(sheet, cell, j, i, indexHeaderEnd, result, prop.Name);
                            
                            if (!string.IsNullOrEmpty(value))
                            {
   
   
                                // 特殊格式：
                                string proFullName = prop.PropertyType.FullName;
                                if (proFullName.Contains("Decimal"))
                                {
   
   
                                    decimal intV = 0;
                                    if (value.IndexOf('*') > -1)
                                    {
   
   
                                        value = value.Replace("*", string.Empty);</