.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$")) //

                {

                    enlist = Excel2003ToEntityList<T>(cellHeard, filePath, out errorMsg);

                }

                else if (Regex.IsMatch(filePath, ".xlsx$")) //

                {

                    //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(); // 获取此文件第一个Sheet页

                    for (int i = ; i <= sheet.PhysicalNumberOfRows; i++) // 从1开始，第0行为单元头

                    {

                        if (i >= )

                        {

                        }

                        // 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 = ; j < keys.Count; j++)

                        {

                            // 2.若属性头的名称包含'.',就表示是子类里的属性，那么就要遍历子类，eg：UserEn.TrueName

                            if (keys[j].IndexOf(".") >= )

                            {

                                // 2.1解析子类属性

                                string[] properotyArray = keys[j].Split(new string[] { "." }, StringSplitOptions.RemoveEmptyEntries);

                                string subClassName = properotyArray[]; // '.'前面的为子类的名称

                                string subClassProperotyName = properotyArray[]; // '.'后面的为子类的属性名称

                                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 == )

                                            {

                                                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 != )

                                        {

                                            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 == )

                                        {

                                            errStr = "第" + i + "行数据转换异常：";

                                        }

                                        errStr += cellHeard[keys[j]] + "列；";

                                    }

                                }

                            }

                        }

                        // 若有错误信息，就添加到错误信息里

                        if (errStr.Length > )

                        {

                            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;

            }

        }