.net core 基于NPOI 的excel导入导出类，支持自定义导出哪些字段，和判断导入是否有失败的记录

#region 从Excel导入
        //用法
        //var cellHeader = new Dictionary<string, string>();
        //cellHeader.Add("dpdm","中文");
        //    cellHeader.Add("dpmc", "中文");
        //    cellHeader.Add("xz", "xz");
        //    cellHeader.Add("qy", "qy");
        //    cellHeader.Add("spdm", "spdm");
        //    cellHeader.Add("dl", "dl");
        //    cellHeader.Add("dpj", "dpj");
        //    cellHeader.Add("ys", "ys");
        //    cellHeader.Add("cm", "cm");
        //    cellHeader.Add("sl", "sl");
        //    cellHeader.Add("bzje", "bzje");
        //    cellHeader.Add("dhje", "dhje");
        //    var errorMsg = new StringBuilder();
        //var list = ExcelHelper.ExcelToEntityList<DHHSJEntity>(cellHeader, fileName, out errorMsg);
        /// <summary>
        /// 从Excel取数据并记录到List集合里
        /// </summary>
        /// <param name="cellHeard">单元头的值和名称：{ { "UserName", "姓名" }, { "Age", "年龄" } };</param>
        /// <param name="filePath">保存文件绝对路径</param>
        /// <param name="errorMsg">错误信息</param>
        /// <returns>转换后的List对象集合</returns>
        public static List<T> ExcelToEntityList<T>(Dictionary<string, string> cellHeard, string filePath, out StringBuilder errorMsg) where T : new()
        {
            List<T> enlist = new List<T>();
            errorMsg = new StringBuilder();
            try
            {
                if (Regex.IsMatch(filePath, ".xls$")) // 2003
                {
                    enlist = Excel2003ToEntityList<T>(cellHeard, filePath, out errorMsg);
                }
                else if (Regex.IsMatch(filePath, ".xlsx$")) // 2007
                {
                    //return FailureResultMsg("请选择Excel文件"); // 未设计
                }
                return enlist;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        
        /// <summary>
        /// 从Excel2003取数据并记录到List集合里
        /// </summary>
        /// <param name="cellHeard">单元头的Key和Value：{ { "UserName", "姓名" }, { "Age", "年龄" } };</param>
        /// <param name="filePath">保存文件绝对路径</param>
        /// <param name="errorMsg">错误信息</param>
        /// <returns>转换好的List对象集合</returns>
        private static List<T> Excel2003ToEntityList<T>(Dictionary<string, string> cellHeard, string filePath, out StringBuilder errorMsg) where T : new()
        {
            errorMsg = new StringBuilder(); // 错误信息,Excel转换到实体对象时，会有格式的错误信息
            List<T> enlist = new List<T>(); // 转换后的集合
            List<string> keys = cellHeard.Keys.ToList(); // 要赋值的实体对象属性名称
            try
            {
                using (FileStream fs = File.OpenRead(filePath))
                {
                    HSSFWorkbook workbook = new HSSFWorkbook(fs);
                    HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(0); // 获取此文件第一个Sheet页
                    for (int i = 1; i <= sheet.PhysicalNumberOfRows; i++) // 从1开始，第0行为单元头
                    {
                        if (i >= 289)
                        {

                        }
                        // 1.判断当前行是否空行，若空行就不在进行读取下一行操作，结束Excel读取操作
                        if (sheet.GetRow(i) == null)
                        {
                            break;
                        }
                        //var cells = sheet.GetRow(i).Cells;
                        //var flag = false;
                        //for(int t=0;t<cells.Count;t++)
                        //{
                        //  //var a = sheet.GetRow(i).GetCell(t);
                        //  //  if (!string.IsNullOrEmpty(sheet.GetRow(i).GetCell(t).ToString()))
                        //  //  {
                        //  //      flag = true;
                        //  //  }
                        //}
                        //if (!flag)
                        //{
                        //    break;
                        //}


                        T en = new T();
                        string errStr = ""; // 当前行转换时，是否有错误信息，格式为：第1行数据转换异常：XXX列；
                        for (int j = 0; j < keys.Count; j++)
                        {
                            // 2.若属性头的名称包含'.',就表示是子类里的属性，那么就要遍历子类，eg：UserEn.TrueName
                            if (keys[j].IndexOf(".") >= 0)
                            {
                                // 2.1解析子类属性
                                string[] properotyArray = keys[j].Split(new string[] { "." }, StringSplitOptions.RemoveEmptyEntries);
                                string subClassName = properotyArray[0]; // '.'前面的为子类的名称
                                string subClassProperotyName = properotyArray[1]; // '.'后面的为子类的属性名称
                                System.Reflection.PropertyInfo subClassInfo = en.GetType().GetProperty(subClassName); // 获取子类的类型
                                if (subClassInfo != null)
                                {
                                    // 2.1.1 获取子类的实例
                                    var subClassEn = en.GetType().GetProperty(subClassName).GetValue(en, null);
                                    // 2.1.2 根据属性名称获取子类里的属性信息
                                    System.Reflection.PropertyInfo properotyInfo = subClassInfo.PropertyType.GetProperty(subClassProperotyName);
                                    if (properotyInfo != null)
                                    {
                                        try
                                        {

                                            // Excel单元格的值转换为对象属性的值，若类型不对，记录出错信息
                                            properotyInfo.SetValue(subClassEn, GetExcelCellToProperty(properotyInfo.PropertyType, sheet.GetRow(i).GetCell(j), errStr).Item1, null);
                                        }
                                        catch (Exception e)
                                        {
                                            if (errStr.Length == 0)
                                            {
                                                errStr = "第" + i + "行数据转换异常：";
                                            }
                                            errStr += cellHeard[keys[j]] + "列；";
                                        }

                                    }
                                }
                            }
                            else
                            {
                                // 3.给指定的属性赋值
                                System.Reflection.PropertyInfo properotyInfo = en.GetType().GetProperty(keys[j]);
                                if (properotyInfo != null)
                                {
                                    try
                                    {
                                        if (GetExcelCellToProperty(properotyInfo.PropertyType, sheet.GetRow(i).GetCell(j), errStr).Item2.Length != 0)
                                        {
                                            errStr += cellHeard[keys[j]] + "列";
                                        }
                                        // Excel单元格的值转换为对象属性的值，若类型不对，记录出错信息
                                        properotyInfo.SetValue(en, GetExcelCellToProperty(properotyInfo.PropertyType, sheet.GetRow(i).GetCell(j), errStr).Item1, null);
                                    }
                                    catch (Exception e)
                                    {
                                        if (errStr.Length == 0)
                                        {
                                            errStr = "第" + i + "行数据转换异常：";
                                        }
                                        errStr += cellHeard[keys[j]] + "列；";
                                    }
                                }
                            }
                        }
                        // 若有错误信息，就添加到错误信息里
                        if (errStr.Length > 0)
                        {
                            errorMsg.AppendLine(errStr);
                        }
                        enlist.Add(en);
                    }
                }
                return enlist;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 从Excel获取值传递到对象的属性里
        /// </summary>
        /// <param name="distanceType">目标对象类型</param>
        /// <param name="sourceCell">对象属性的值</param>
        private static Tuple<Object, string> GetExcelCellToProperty(Type distanceType, ICell sourceCell, string errStr)
        {
            object rs = distanceType.IsValueType ? Activator.CreateInstance(distanceType) : null;

            // 1.判断传递的单元格是否为空
            if (sourceCell == null || string.IsNullOrEmpty(sourceCell.ToString()))
            {
                return new Tuple<Object, string>(rs, errStr);
            }

            // 2.Excel文本和数字单元格转换，在Excel里文本和数字是不能进行转换，所以这里预先存值
            object sourceValue = null;
            switch (sourceCell.CellType)
            {
                case CellType.Blank:
                    break;

                case CellType.Boolean:
                    break;

                case CellType.Error:
                    break;

                case CellType.Formula:
                    break;

                case CellType.Numeric:
                    sourceValue = sourceCell.NumericCellValue;
                    break;

                case CellType.String:
                    sourceValue = sourceCell.StringCellValue;
                    break;

                case CellType.Unknown:
                    errStr += "数据转换异常";
                    break;

                default:
                    break;
            }

            string valueDataType = distanceType.Name;

            // 在这里进行特定类型的处理
            switch (valueDataType.ToLower()) // 以防出错，全部小写
            {
                case "string":
                    rs = sourceValue.ToString();
                    break;
                case "int":
                case "int16":
                case "int32":
                    rs = (int)Convert.ChangeType(sourceValue, distanceType);
                    break;
                case "float":
                case "single":
                    rs = (float)Convert.ChangeType(sourceValue, distanceType);
                    break;
                case "double":
                    rs = (double)Convert.ChangeType(sourceValue, distanceType);
                    break;
                case "decimal":
                    rs = (decimal)Convert.ChangeType(sourceValue, distanceType);
                    break;
                case "datetime":
                    rs = sourceCell.DateCellValue;
                    break;
                case "guid":
                    rs = (Guid)Convert.ChangeType(sourceValue, distanceType);
                    return new Tuple<Object, string>(rs, errStr);
            }
            return new Tuple<Object, string>(rs, errStr);
        }
        #endregion

 

/// <summary>  
        /// 导出Excel  
        /// </summary>  
        /// <param name="lists"></param>  
        /// <param name="head">英文中文列名对照</param>  
        /// <param name="workbookFile">保存路径</param>  
        public static void getExcel<T>(List<T> lists, Dictionary<string,string> head, string workbookFile)
        {
            try
            {

                XSSFWorkbook workbook = new XSSFWorkbook();
                using (MemoryStream ms = new MemoryStream())
                {
                    var sheet = workbook.CreateSheet();
                    var headerRow = sheet.CreateRow(0);
                    bool h = false;
                    int j = 1;
                    Type type = typeof(T);
                    PropertyInfo[] properties = type.GetProperties();
                    foreach (T item in lists)
                    {
                        var dataRow = sheet.CreateRow(j);
                        int i = 0;
                        foreach (PropertyInfo column in properties)
                        {
                            if (!h)
                            {
                                if (head.Keys.Contains(column.Name))
                                {
                                    headerRow.CreateCell(i).SetCellValue(head[column.Name] == null ? column.Name : head[column.Name].ToString());
                                    dataRow.CreateCell(i).SetCellValue(column.GetValue(item, null) == null ? "" : column.GetValue(item, null).ToString());

                                }
                                else
                                {
                                    i -= 1;
                                }

                            }
                            else
                            {
                                if (head.Keys.Contains(column.Name))
                                {
                                    dataRow.CreateCell(i).SetCellValue(column.GetValue(item, null) == null ? "" : column.GetValue(item, null).ToString());
                                }
                                else
                                {
                                    i -= 1;
                                }

                            }
                            i++;
                        }
                        h = true;
                        j++;
                    }
                   
                    workbook.Write(ms);
                    using (FileStream fs = new FileStream(workbookFile, FileMode.Create, FileAccess.Write))
                    {
                        byte[] data = ms.ToArray();
                        fs.Write(data, 0, data.Length);
                        fs.Flush();
                    }
                    sheet = null;
                    headerRow = null;
                    workbook = null;
                }
            }
            catch (Exception ee)
            {
                string see = ee.Message;
            }
        }