上一篇文章介绍了一些NPOI的基础接口,我们现在就来看看具体怎么用NPOI来解析一个EXCEL。
博主现在有这么一堆excel需要解析数据入库:
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5SN5QzN5ATO1ETMtkzN2kTM4IjNxYTMwETNxAjMtkjN2cDM38CXwETNxAjMvwVO2YzNwczLcd2bsJ2Lc12bj5ycn9Gbi52YuUTMwIzcldWYtl2Lc9CX6MHc0RHaiojIsJye.png)
当然这只是员工的简要模板,还有很多其他的模板。我们可以要求线下人员把表头都做成像这样的表头,但是数据的列数与各列内容是不受我们所控制的。那么我们需要的就是一个公用的方法,能够解析这一类表头的excel数据。
既然每种表对应着一张数据库表,字段不一样,那么我们的方法就考虑到使用反射机制来给泛型DTO属性赋值。具体每个excel表的各列与DTO属性字段的对应以及表本身信息我们用XML文件来做配置。OK,我们得到下面的一个基本流程:
1.用户上传excel文件,调用uploadExcelFile()接口
2.uploadExcelFile()接口保存文件到指定路径,调用excel解析工具类ImportExcel()方法,传入泛型参数ExcelDataDTO与excel配置文件xml路径
3.ImportExcel()服务首先验证excel数据(表头与xml是否匹配,各单元格数据格式,空格等等)
4.验证通过后调用获取数据方法,失败值直接返回具体失败数据(定位到每一行的某一列,并附带具体错误原因)
OK,那我们先来看看excel的配置xml文件具体怎么配置:
1 <?xml version="1.0" encoding="utf-8" ?>
2 <module>
3 <add firstHeaderRow="2" lastHeaderRow="2" sheetCount="1"/>
4 <add headerText="姓名" propertyName="Name" dataType="System.String"/>
5 <add headerText="手机号码" propertyName="PhoneNumber" dataType="System.String"/>
6 <add headerText="性别" propertyName="Sex" dataType="System.Boolean"/>
7 <add headerText="民族" propertyName="Nation" dataType="System.String"/>
8 <add headerText="出生日期" propertyName="Birthday" dataType="System.String"/>
9 <add headerText="身份证号码" propertyName="Cardid" dataType="System.String"/>
10 </module>
再写一个读取excel配置xml文件的方法:
1 // 读取XML配置信息集
2 public List<Regular> GetXMLInfo(string xmlpath)
3 {
4 var reader = new XmlTextReader(xmlpath);
5 var doc = new XmlDocument();
6 doc.Load(reader);
7
8 var headerList = new List<Regular>();
9 foreach (XmlNode node in doc.DocumentElement.ChildNodes)
10 {
11 var header = new Regular();
12
13 if (node.Attributes["firstHeaderRow"] != null)
14 header.HeaderRegular.Add("firstHeaderRow", int.Parse(node.Attributes["firstHeaderRow"].Value));
15 if (node.Attributes["lastHeaderRow"] != null)
16 header.HeaderRegular.Add("lastHeaderRow", int.Parse(node.Attributes["lastHeaderRow"].Value));
17 if (node.Attributes["sheetCount"] != null)
18 header.HeaderRegular.Add("sheetCount", int.Parse(node.Attributes["sheetCount"].Value));
19
20 if (node.Attributes["headerText"] != null)
21 header.HeaderText = node.Attributes["headerText"].Value;
22 if (node.Attributes["propertyName"] != null)
23 header.PropertyName = node.Attributes["propertyName"].Value;
24 if (node.Attributes["dataType"] != null)
25 header.DataType = node.Attributes["dataType"].Value;
26
27 headerList.Add(header);
28 }
29 return headerList;
30 }
其中涉及到一个我们自己创建的规则类,Regular
1 /// <summary>
2 /// 模板规则类
3 /// </summary>
4 public class Regular
5 {
6 /// <summary>
7 /// 表头文本
8 /// </summary>
9 public string HeaderText { set; get; }
10
11 /// <summary>
12 /// 属性名称
13 /// </summary>
14 public string PropertyName { set; get; }
15
16 /// <summary>
17 /// 数据类型
18 /// </summary>
19 public string DataType { set; get; }
20
21 private Dictionary<string, int> _regular = new Dictionary<string, int>();
22
23 /// <summary>
24 /// 表头规则
25 /// </summary>
26 public Dictionary<string, int> HeaderRegular
27 {
28 get { return _regular; }
29 set { _regular = value; }
30 }
31 }
这样,我们就能将一个excel的配置信息读取出来备用。
具体上传文件的接口就不在这儿粘贴了,以前有一篇文章介绍过wenAPI做文件上传,地址:
http://www.cnblogs.com/csqb-511612371/p/4871574.html
我们在文件上传成功后的逻辑是调用服务ImportExcel,解析出excel数据到DTO,然后再将DTO映射到实体入库。
那么就会有这样一段代码:
1 var xmlName = getXml();// 自己定义的获取配置文件名称方法
2 var excelDataDtos = new List<ExcelDataDTO>();
3 var result = attachmentFileService.ImportExcel(excelFilePath, xmlName, ref excelDataDtos);
4 if (result.Success)
5 {
9 foreach (var excelDataDto in excelDataDtos)
10 {
// 数据入库
19 employeeInfoService.HR_Add_EmployeeInfo(excelDataDto);
20 }
}
ExcelDataDTO是我们对应这个excel文件的DTO,此处简要的直接new了,也应该是单独服务产生(因为我们这个方法是实现多模板上传)。
第3行ImportExcel方法中将执行逻辑:获取基础配置信息->验证excel数据->读取excel数据
1 public UploadExcelFileResult ImportExcel(string filePath, string xmlPath, ref List<ExcelDataDTO> excelDTO)
2 {
3 // XML配置文件绝对路径
4 var xmlFilePath = ExcelTemplateBasePath + xmlPath;
5
6 var excelImportService = new ExcelImportService(filePath, xmlFilePath);
7 var result = excelImportService.ValidateExcel();
8 if (result.Success)
9 {
10 excelDTO = excelImportService.Import<ExcelDataDTO>();
11 }
12 return result;
13 }
注:
1.第6行初始化excel导入服务(初始化基本配置信息)
2.第7行验证excel数据,失败则返回具体错误信息
3.验证通过则读取excel数据到DTO
这儿的excelImportService就涉及到整个excel解析工具了,我们先看看整个excel解析的接口与实现文件:
其中Regular前面已经讲过了,是规则集。UploadExcelFileResult则是解析返回结果,内含成功与否,总Message,文件信息,具体错误信息等数据:
/// <summary>
/// EXCEL文件上传检查返回数据
/// </summary>
public class UploadExcelFileResult
{
/// <summary>
/// 是否成功
/// </summary>
public bool Success { get; set; }
/// <summary>
/// 附带消息
/// </summary>
public string Message { get; set; }
/// <summary>
/// 文件基本信息
/// </summary>
public FileMessage FileMessage { get; set; }
/// <summary>
/// 解析失败后错误位置定位信息
/// </summary>
public List<ExcelFileErrorPosition> ExcelFileErrorPositions { get; set; }
}
public class FileMessage
{
/// <summary>
/// 上传文件名称
/// </summary>
public string FileName { get; set; }
/// <summary>
/// 文件大小
/// </summary>
public int Length { get; set; }
/// <summary>
/// 文件类型
/// </summary>
public string Type { get; set; }
}
public class ExcelFileErrorPosition
{
/// <summary>
/// 错误行
/// </summary>
public int RowIndex { get; set; }
/// <summary>
/// 错误列集
/// </summary>
public List<int> CellIndex { get; set; }
/// <summary>
/// 错误列具体错误信息
/// </summary>
public List<string> ErrorMessage { get; set; }
/// <summary>
/// 错误行数据
/// </summary>
public List<string> RowContent { get; set; }
}
然后我们来看具体的三个接口:
1.IExcelParseBaseService接口是最基础服务接口,里面包含所有需要用到的抽象方法:
1 /// <summary>
2 /// EXCEL解析基本服务接口
3 /// </summary>
4 public interface IExcelParseBaseService
5 {
6 /// <summary>
7 /// 检查单元格数据类型
8 /// </summary>
9 /// <param name="cellType">类型</param>
10 /// <param name="cellValue">单元格值</param>
11 /// <returns>类型是否出错</returns>
12 bool CheckDataType(string cellType, string cellValue);
13
14 /// <summary>
15 /// 检查单元格数据是否为空
16 /// </summary>
17 /// <param name="cellValue">单元格值</param>
18 /// <param name="nullcount">行空值计数器</param>
19 /// <returns>数据是否为空</returns>
20 bool CheckNull(string cellValue, ref int nullcount);
21
22 /// <summary>
23 /// 去除数据空格
24 /// </summary>
25 /// <param name="cellValue">单元格值</param>
26 void ReplaceSpace(ref string cellValue);
27
28 /// <summary>
29 /// 判断当前单元格是否为合并单元格
30 /// </summary>
31 /// <param name="cellIndex">单元格所在列序号</param>
32 /// <param name="rowIndex">单元格所在行序号</param>
33 /// <param name="sheet">EXCEL工作表</param>
34 /// <returns>合并单元格为true</returns>
35 bool IsMergedRegionCell(int cellIndex, int rowIndex, ISheet sheet, ref int firstRegionRow);
36
37 /// <summary>
38 /// 读取EXCEL XML配置信息集
39 /// </summary>
40 /// <param name="xmlpath">xml文件路径</param>
41 /// <returns></returns>
42 List<Regular> GetXMLInfo(string xmlpath);
43 }
它的实现类是抽象类ExcelParseBaseService
2.IExcelAnalyzeService接口是excel解析的核心服务,实现对excel的操作
1 /// <summary>
2 /// EXCEL 解析基础服务
3 /// </summary>
4 public interface IExcelAnalyzeService
5 {
6 /// <summary>
7 /// 获取指定excel文件版本
8 /// </summary>
9 /// <param name="fileName">EXCEL文件名称</param>
10 /// <returns></returns>
11 int GetExcelEdition(string fileName);
12
13 /// <summary>
14 /// 根据EXCEL版本创建WorkBook
15 /// </summary>
16 /// <param name="edition">EXCEL版本</param>
17 /// <param name="excelFileStream">EXCEL文件</param>
18 /// <returns>excel文件对应workbook</returns>
19 IWorkbook CreateWorkBook(int edition, Stream excelFileStream);
20
21 /// <summary>
22 /// 解析并检查EXCEL表头数据
23 /// </summary>
24 /// <param name="sheet"></param>
25 /// <param name="uploadExcelFileResult"></param>
26 /// <param name="list"></param>
27 /// <returns></returns>
28 Dictionary<int, string> GetExcelHeaders(ISheet sheet, ref UploadExcelFileResult uploadExcelFileResult,
29 List<Regular> list);
30
31 /// <summary>
32 /// 读取EXCEL数据
33 /// </summary>
34 /// <typeparam name="TableDTO">数据对象</typeparam>
35 /// <param name="sheet">工作簿对应工作表</param>
36 /// <param name="sheetName">excel工作表名称</param>
37 /// <param name="list">该excel规则集</param>
38 /// <param name="dict">表头字典</param>
39 /// <param name="rowCount">总数据行数</param>
40 /// <returns>解析后的Excel数据集</returns>
41 List<TableDTO> GetExcelDatas<TableDTO>(ISheet sheet, string sheetName, List<Regular> list,
42 Dictionary<int, string> dict, int rowCount);
43
44 /// <summary>
45 /// 检查excel数据
46 /// </summary>
47 /// <param name="sheet">excel工作表</param>
48 /// <param name="list">规则集</param>
49 /// <param name="dict">表头</param>
50 /// <param name="rowCount">总数据行数</param>
51 /// <returns>检查结果</returns>
52 UploadExcelFileResult CheckExcelDatasEnableNull(ISheet sheet, List<Regular> list, Dictionary<int, string> dict,int rowCount);
53 }
它的实现类是ExcelAnalyzeService,也是抽象类并继承于Base服务ExcelParseBaseService
3.IExcelImportService接口就是对外暴漏的excel解析工具类的接口,只含两个方法:验证和读取;
1 /// <summary>
2 /// Excel 导入基础服务接口
3 /// </summary>
4 public interface IExcelImportService
5 {
6 /// <summary>
7 /// 综合验证Excel表格符合性
8 /// </summary>
9 /// <returns></returns>
10 UploadExcelFileResult ValidateExcel();
11
12 /// <summary>
13 /// 导入EXCEL文件
14 /// </summary>
15 /// <typeparam name="TableDTO">数据对象DTO</typeparam>
16 /// <returns>EXCEL数据集合</returns>
17 List<TableDTO> Import<TableDTO>();
18 }
它的实现是ExcelImportService,继承于抽象类ExcelAnalyzeService。是我们外部调用excel工具的入口
具体各个类方法的实现,下一篇文章再继续贴代码。
本篇文章主要讲述了通过配置xml文件解析多模板excel表格数据的设计流程与主要框架,附带部分规则代码。具体工具内接口方法实现,请关注下一篇文章。
原创文章,代码都是从自己项目里贴出来的。转载请注明出处哦,亲~~~