上一篇文章介紹了一些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表格資料的設計流程與主要架構,附帶部分規則代碼。具體工具内接口方法實作,請關注下一篇文章。
原創文章,代碼都是從自己項目裡貼出來的。轉載請注明出處哦,親~~~