NPOI+Json文件解析Excel

记点笔记，加深印象！最近有个导入Excel工能需要完成，Excel列名是中文的，导入Excel我用的NPOI插件，如果不对Excel做解析，列名有可能会给我带来一些字符方面的麻烦，于是想到了一个比较low的办法，做一个Json配置文件，对应我的Excel列头,然后读取Excel时与json文件匹配，同时需要校验Excel数据是否合法，创建DataTable。主要代码如下：

1.Json文件：

[
  {
    "Name": "Inventory",
    "CnName":"盘点单明细",
    "Columns": [
      {
        "Name": "warehouseCode",
        "CnName": "仓库编码",
        "Regex": "/^[\\s\\S]*.*[^\\s][\\s\\S]*$/",
        "ErrorMsg": "4rwrewrwr"
      },
      {
        "Name": "ownerCode",
        "CnName": "货主编码",
        "Regex": "",
        "ErrorMsg": "4rwrewrwr"
      },
      {
        "Name": "itemCode",
        "CnName": "商品编码",
        "Regex": "",
        "ErrorMsg": "4rwrewrwr"
      },
      {
        "Name": "itemName",
        "CnName": "商品名称",
        "Regex": "",
        "ErrorMsg": "4rwrewrwr"
      },
      {
        "Name": "barCode",
        "CnName": "条形码",
        "Regex": "",
        "ErrorMsg": "4rwrewrwr"
      },
      {
        "Name": "quantity",
        "CnName": "系统数量",
        "Regex": "",
        "ErrorMsg": "4rwrewrwr"
      },
      {
        "Name": "location",
        "CnName": "货位",
        "Regex": "",
        "ErrorMsg": "4rwrewrwr"
      },
      {
        "Name": "expireDate",
        "CnName": "过期日期",
        "Regex": "",
        "ErrorMsg": "4rwrewrwr"
      }
    ]
  }
]

2.接受Json文件类：

 public class Sheets
    {
        public string Name { get; set; }
        public string CnName { get; set; }
        public List<Column> Columns { get; set; }
    }

 public class Column
    {
        public string Name { get; set; }
        public string CnName { get; set; }
        public string Regex { get; set; }
        public string ErrorMsg { get; set; }
    }

3.具体解析Excel函数

public static DataTable[] ReadExcelToDataTables2(string mapJsonPath, string excelPath,out string msg)
        {
            msg = "";
            List<Sheets> listSheet = new List<Sheets>();
            string jsonFile = mapJsonPath;//@"C:\Users\user\source\repos\ConsoleApp1\ConsoleApp1\Map.json";
            string json = System.IO.File.ReadAllText(jsonFile, Encoding.Default);
            listSheet = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Sheets>>(json);
            string excelpath = excelPath; //@"C:\Users\user\Desktop\test.xls";
            FileStream fs = null;
            DataTable[] tables = null;
            int sheetCount = 0;
            NPOI.HSSF.UserModel.HSSFWorkbook book = null;
            NPOI.XSSF.UserModel.XSSFWorkbook workbook = null;
            try
            {
                using (fs = new FileStream(excelpath, FileMode.Open, FileAccess.Read))
                {
                    try
                    {
                        book = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);
                    }
                    catch (Exception)
                    {
                        fs = new FileStream(excelpath, FileMode.Open, FileAccess.Read);
                        workbook = new XSSFWorkbook(fs);
                    }
                    if (book != null)
                    {
                        sheetCount = book.NumberOfSheets;
                    }
                    else
                    {
                        sheetCount = workbook.NumberOfSheets;
                    }
                    tables = new DataTable[sheetCount];
                    DataTable dt = null;
                    for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++)
                    {                       
                        NPOI.SS.UserModel.ISheet sheet = null;
                        if (book != null)
                        {
                            sheet = book.GetSheetAt(sheetIndex);
                        }
                        else
                        {
                            sheet = workbook.GetSheetAt(sheetIndex);
                        }
                        if (sheet == null) continue;
                        NPOI.SS.UserModel.IRow row = sheet.GetRow(0);
                        if (row == null) continue;
                        int firstCellNum = row.FirstCellNum;
                        int lastCellNum = row.LastCellNum;
                        if (firstCellNum == lastCellNum) continue;
                        string tempSheetName = sheet.SheetName;
                        string sheetName = listSheet.Find(p => p.CnName == tempSheetName).Name;
                        dt = new DataTable(sheetName);
                        for (int i = firstCellNum; i < lastCellNum; i++)
                        {
                            string fieldTempName = row.GetCell(i).StringCellValue;
                            var fieldName = listSheet[sheetIndex].Columns.Find(p => p.CnName == fieldTempName).Name;
                             dt.Columns.Add(fieldName, typeof(string));
                        }
                        for (int i = 1; i <= sheet.LastRowNum; i++)
                        {
                           
                            DataRow dataRow = dt.Rows.Add();

                            for (int j = firstCellNum; j < lastCellNum; j++)
                            {
                                var cellValue = "";
                                if (sheet.GetRow(i).GetCell(j) != null)
                                {
                                    if (sheet.GetRow(i).GetCell(j).CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(sheet.GetRow(i).GetCell(j)))
                                    {
                                        cellValue = sheet.GetRow(i).GetCell(j).DateCellValue.ToString("yyyy/MM/dd");
                                    }
                                    else
                                    {
                                        sheet.GetRow(i).GetCell(j).SetCellType(NPOI.SS.UserModel.CellType.String);
                                        cellValue = sheet.GetRow(i).GetCell(j).StringCellValue;
                                    }

                                }
                                string errorMsg = listSheet[sheetIndex].Columns[j].ErrorMsg;
                                if (!string.IsNullOrEmpty(errorMsg))
                                {
                                    string regex = listSheet[sheetIndex].Columns[j].Regex;
                                    if (!string.IsNullOrEmpty(regex))
                                    {
                                        Match result = Regex.Match(cellValue, regex); //正则校验
                                        if (!result.Success)
                                        {
                                            msg += string.Format("Excel第{0}个sheet的第{1}行，第{2}列，数据错误：{3}\r\n", sheetIndex + 1, i, j+1, errorMsg);
                                            continue;
                                        }
                                    }                                   
                                }
                                dataRow[j] = cellValue;                               
                            }
                        }
                        if (!string.IsNullOrEmpty(msg))
                        {
                            return new DataTable[] { };
                        }
                        tables[sheetIndex] = dt;
                    }
                }
            }
            catch (Exception e)
            {

                throw e;
            }
            return tables;
        }
    }