记点笔记，加深印象！最近有个导入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 = ;
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 = ; 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();
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 = ; 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 + , i, j+, errorMsg);
continue;
}
}
}
dataRow[j] = cellValue;
}
}
if (!string.IsNullOrEmpty(msg))
{
return new DataTable[] { };
}
tables[sheetIndex] = dt;
}
}
}
catch (Exception e)
{ throw e;
}
return tables;
}
}