NPOI操作EXCEL（三）——反射机制进行excel表格数据的解析

我们先来回忆回忆上篇文章讲到的通过xml配置文件实现excel批量模板解析的整体思路：

1.对每个excel模板制定xml配置规则集，实现xml配置文件的解析服务

2.为每个excel模板制定DTO，继承于一个BaseDTO

3.实现两个工厂方法，一是获取某excel模板xml配置文件路径；一是获取某excel模板DTO对象

4.EXCEL工具类对外暴露两个接口，一是数据验证接口，返回整个excel所有错误单元格信息；一是数据解析接口，读取单元格数据到DTO。通过构造方法传入配置文件

5.整体流程：用户上传excel文件，首先存储到临时文件夹，其次获取xml配置文件路径，获取DTO对象，然后初始化excel工具类，再验证excel单元格数据，解析数据返回DTO，最后调用入库服务实现DTO数据入库

 

接下来我们就来看看excel工具类的具体实现代码

    public class ExcelImportService : ExcelAnalyzeService, IExcelImportService
    {
        private string _filePath;
        private string _xmlPath;
        private Dictionary<int, int> _rowCount = new Dictionary<int, int>();
        private List<Regular> _list;// 规则集

        /// <summary>
        /// 构造方法
        /// </summary>
        /// <param name="filePath">excel文件路径</param>
        /// <param name="xmlPath">配置文件路径</param>
        public ExcelImportService(string filePath, string xmlPath)
        {
            _filePath = filePath;
            _xmlPath = xmlPath;
            _list = this.GetXMLInfo(_xmlPath);
        }

           // excel所有单元格数据验证
        public UploadExcelFileResult ValidateExcel()
        {
            var result = new UploadExcelFileResult();
            result.Success = true;

            _rowCount = new Dictionary<int, int>();

            Stream fileStream = new FileStream(_filePath, FileMode.Open);
            int edition = this.GetExcelEdition(_filePath);
            if (edition != 0)
            {
                IWorkbook workbook = this.CreateWorkBook(edition, fileStream);
                int sheetCount = _list.Find(e => e.HeaderRegular != null).HeaderRegular["sheetCount"];

                for (int i = 0; i < sheetCount; i++)
                {
                    ISheet sheet = workbook.GetSheetAt(i);
                    Dictionary<int, string> dict = this.GetExcelHeaders(sheet, ref result, _list);
                    if (result.Success)
                    {
                        _rowCount.Add(i, sheet.LastRowNum);
                        result = this.CheckExcelDatasEnableNull(sheet, _list, dict, _rowCount[i]);
                    }
                    else
                    {
                        break;
                    }
                }
            }
            else
            {
                result.Success = false;
                result.Message = "文件类型错误!";
            }

            fileStream.Close();
            return result;
        }

           // 解析excel数据到DTO
        public List<TableDTO> Import<TableDTO>()
        {
            var uploadExcelFileResult = new UploadExcelFileResult();
            var resultList = new List<TableDTO>();

            Stream fileStream = new FileStream(_filePath, FileMode.Open);
            int edition = this.GetExcelEdition(_filePath);
            IWorkbook workbook = this.CreateWorkBook(edition, fileStream);
            int sheetCount = _list.Find(e => e.HeaderRegular != null).HeaderRegular["sheetCount"];

            for (int i = 0; i < sheetCount; i++)
            {
                ISheet sheet = workbook.GetSheetAt(i);
                string sheetName = sheet.SheetName;
                Dictionary<int, string> dict = this.GetExcelHeaders(sheet, ref uploadExcelFileResult, _list);
                var sheetLists = this.GetExcelDatas<TableDTO>(sheet, sheetName, _list, dict, _rowCount[i]);
                resultList.AddRange(sheetLists);
            }

            fileStream.Close();
            return resultList;
        }
    }

1.我们看到17行用到了GetXMLInfo()方法，就是第二篇文章中说到的XML文件解析方法，返回该excel的规则集

2.第28行GetExcelEdition()方法，是基础解析接口IExcelAnalyzeService的方法，验证返回excel版本

        public int GetExcelEdition(string fileName)
        {
            var edition = 0;
            string[] items = fileName.Split(new char[] { '.' });
            int count = items.Length;
            switch (items[count - 1])
            {
                case "xls":
                    edition = 3;
                    break;
                case "xlsx":
                    edition = 7;
                    break;
                default:
                    break;
            }

            return edition;
        }

3.第31行CreateWorkBook()方法，是基础解析接口IExcelAnalyzeService的方法，返回excel工作簿对象

        public IWorkbook CreateWorkBook(int edition, Stream  excelFileStream)
        {
            switch (edition)
            {
                case 7:
                    return new XSSFWorkbook(excelFileStream);
                case 3:
                    return new HSSFWorkbook(excelFileStream);
                default:
                    return null;
            }
        }

4.第32行是读取配置文件中excel中sheet个数（例如员工模板：我们支持一个excel文件多个sheet表单，可以是每个表单代表一个地区等等）

5.第37行中GetExcelHeaders()方法，是基础解析接口IExcelAnalyzeService的方法，验证返回excel表头数据

        public Dictionary<int, string> GetExcelHeaders(ISheet sheet, ref UploadExcelFileResult uploadExcelFileResult,
            List<Regular> list)
        {
            int firstHeaderRowIndex = list.Find(e => e.HeaderRegular != null).HeaderRegular["firstHeaderRow"];
            int lastHeaderRowIndex = list.Find(e => e.HeaderRegular != null).HeaderRegular["lastHeaderRow"];

            var dict = new Dictionary<int, string>();

            try
            {
                // 循环获得表头
                for (int i = firstHeaderRowIndex - 1; i < lastHeaderRowIndex; i++)
                {
                    IRow headerRow = sheet.GetRow(i);
                    int cellCount = headerRow.LastCellNum;

                    for (int j = headerRow.FirstCellNum; j < cellCount; j++)
                    {
                        if (!string.IsNullOrEmpty(headerRow.GetCell(j).StringCellValue.Trim()))
                        {
                            // 根据 键－值 是否已存在做不同处理
                               //TODO 代码待重构！！！
                            try
                            {
                                string oldValue = dict[j];
                                dict.Remove(j);
                                dict.Add(j, oldValue + headerRow.GetCell(j).StringCellValue.Trim());
                            }
                            catch (Exception)
                            {
                                dict.Add(j, headerRow.GetCell(j).StringCellValue.Trim());
                            }
                        }
                    }
                }
                // 遍历表头字典，消除空格
                for (int i = 0; i < dict.Count; i++)
                {
                    var value = dict[i];
                    this.ReplaceSpace(ref value);
                    dict[i] = value;
                }
                // 检查表头模板是否被修改
                for (int count = 0; count < dict.Count; count++)
                {
                    Regular header = list.Find(h => h.HeaderText == dict[count]);

                    if (header == null)
                    {
                        uploadExcelFileResult.Success = false;
                        uploadExcelFileResult.Message = "读取EXCEL表头模板时发生错误，可能造成原因是：EXCEL模板被修改！请下载最新EXCEL模板！";
                    }
                }
            }
            catch (Exception e)
            {
                uploadExcelFileResult.Success = false;
                uploadExcelFileResult.Message = "读取EXCEL表头模板时发生错误，可能造成原因是：EXCEL模板被修改！请下载最新EXCEL模板！";
            }

            return dict;
        }

其中39行ReplaceSpace()是消除字符串中空格方法（所有半角、全角）。一直想通过正则表达式来做，但是没学到家，还没写好能够做到的正则表达式，所以写的有点复杂，若果谁有这样的正则表达式，请指点一二，感激不尽！！！

        // 去除空值
        public void ReplaceSpace(ref string cellValue)
        {
            cellValue = TruncateString(cellValue, new char[] { ' ' }, new char[] { '　' });
        }

        // 对字符串做空格剔除处理
        private string TruncateString(string originalWord, char[] spiltWord1, char[] spiltWord2)
        {
            var result = "";
            var valueReplaceDbcCase = originalWord.Split(spiltWord1);

            if (valueReplaceDbcCase.Count() > 1)
            {
                for (int i = 0; i < valueReplaceDbcCase.Count(); i++)
                {
                    if (valueReplaceDbcCase[i] != "" && valueReplaceDbcCase[i] != " " &&
                        valueReplaceDbcCase[i] != "　")
                    {
                        result += TruncateString(valueReplaceDbcCase[i], spiltWord2, new char[0]);
                    }
                }
            }
            else
            {
                if (spiltWord2.Any())
                {
                    result = TruncateString(originalWord, spiltWord2, new char[0]);
                }
                else
                {
                    result = originalWord;
                }
            }

            return result;
        }

6.第41行CheckExcelDatasEnableNull()方法，是基础解析接口IExcelAnalyzeService的方法，返回excel数据验证结果

        public UploadExcelFileResult CheckExcelDatasEnableNull(ISheet sheet, List<Regular> list, Dictionary<int, string> dict, int rowCount)
        {
            var result = new UploadExcelFileResult();
            result.Success = true;

            // 记录单个sheet所有错误信息
            var sheetErrors = new List<ExcelFileErrorPosition>();
            // 表头结束行
            int lastHeaderRowIndex = list.Find(e => e.HeaderRegular != null).HeaderRegular["lastHeaderRow"];

            // 循环行数据
            for (int i = lastHeaderRowIndex; i <= rowCount; i++)
            {
                // 标注该行是否出错
                bool isrowfalse = false;
                // 记录该行数据临时对象
                var rowDatas = new List<string>();
                // 记录该行错误列
                var rowErrorCell = new List<int>();
                // 记录该行错误列具体错误信息
                var rowErrorMessages = new List<string>();
                // 记录该行空值数
                int nullcount = 0;


                IRow dataRow = sheet.GetRow(i);
                int cellCount = dict.Count;

                // 循环列数据
                for (int j = dataRow.FirstCellNum; j < cellCount; j++)
                {
                    string value = "";
                    Regular header = list.Find(h => h.HeaderText == dict[j]);
                    //value = dataRow.GetCell(j).ToString();
                    switch (dataRow.GetCell(j).CellType)
                    {
                        case CellType.Formula:
                            value = dataRow.GetCell(j).StringCellValue.ToString();
                            break;
                        default:
                            value = dataRow.GetCell(j).ToString();
                            break;
                    }

                    // 记录可能出错数据
                    rowDatas.Add(value);

                    // 检查空值
                    if (!this.CheckNull(value, ref nullcount))
                    {
                        // 检查类型
                        if (!this.CheckDataType(header.DataType, value))
                        {
                            isrowfalse = true;
                            result.Success = false;
                            // 记录该行错误信息
                            rowErrorCell.Add(j + 1);
                            rowErrorMessages.Add("读取EXCEL数据时发生数据格式错误，请检查该行该列数据格式！");
                        }
                        else
                        {
                            if (header.DataType == "System.string" || header.DataType == "System.String")
                            {
                                this.ReplaceSpace(ref value);
                            }
                        }
                    }
                }
                // 报错处理(空行不报错)
                if (isrowfalse && nullcount < cellCount)
                {
                    sheetErrors.Add(new ExcelFileErrorPosition
                    {
                        RowContent = rowDatas,
                        RowIndex = i + 1,
                        CellIndex = rowErrorCell,
                        ErrorMessage = rowErrorMessages
                    });
                }
            }
            result.ExcelFileErrorPositions = sheetErrors;
            return result;
        }

CheckNull()检查空值，是空值则nullcount++；

7.第74行GetExcelDatas()方法，是基础解析接口IExcelAnalyzeService的方法，返回excel数据解析结果

        public List<TableDTO> GetExcelDatas<TableDTO>(ISheet sheet, string sheetName, List<Regular> list,
            Dictionary<int, string> dict, int rowCount)
        {
            // 返回数据对象集合
            var resultList = new List<TableDTO>();
            // 表头结束行
            int lastHeaderRowIndex = list.Find(e => e.HeaderRegular != null).HeaderRegular["lastHeaderRow"];

            // 循环行数据
            for (int i = lastHeaderRowIndex; i <= rowCount; i++)
            {
                // 产生一个新的泛型对象
                var model = Activator.CreateInstance<TableDTO>();
                // 记录该行空值数
                int nullcount = 0;

                IRow dataRow = sheet.GetRow(i);
                int cellCount = dict.Count;

                if (dataRow != null)
                {
                    // 循环列数据
                    for (int j = dataRow.FirstCellNum; j < cellCount; j++)
                    {
                        string value = "";
                        Regular header = list.Find(h => h.HeaderText == dict[j]);
                        PropertyInfo prop = model.GetType().GetProperty(header.PropertyName);
                        //value = dataRow.GetCell(j).ToString();
                        switch (dataRow.GetCell(j).CellType)
                        {
                            case CellType.Formula:
                                value = dataRow.GetCell(j).StringCellValue.ToString();
                                break;
                            default:
                                value = dataRow.GetCell(j).ToString();
                                break;
                        }

                        // 去除空值
                        this.ReplaceSpace(ref value);

                        if (value == "")
                        {
                            nullcount++;
                        }

                        // 赋值
                        switch (header.DataType)
                        {
                            case "System.double":
                                double valueDecimal;
                                if (double.TryParse(value, out valueDecimal))
                                {
                                    prop.SetValue(model, valueDecimal, null);
                                }
                                break;
                            case "System.Int16":
                                short valueInt16;
                                if (Int16.TryParse(value, out valueInt16))
                                {
                                    prop.SetValue(model, valueInt16, null);
                                }
                                break;
                            case "System.Int32":
                                int valueInt32;
                                if (Int32.TryParse(value, out valueInt32))
                                {
                                    prop.SetValue(model, valueInt32, null);
                                }
                                break;
                            case "System.Boolean":
                                bool valueBoolean;
                                if (Boolean.TryParse(value, out valueBoolean))
                                {
                                    prop.SetValue(model, valueBoolean, null);
                                }
                                break;
                            case "System.DateTime":
                                DateTime valueDateTime;
                                if (DateTime.TryParse(value, out valueDateTime))
                                {
                                    prop.SetValue(model, valueDateTime, null);
                                }
                                break;
                            default:
                                prop.SetValue(model, value, null);
                                break;
                        }
                    }
                }

                // 添加非空行数据到DTO
                if (nullcount < cellCount)
                {
                    resultList.Add(model);
                }
            }

            return resultList;
        }

 

OK，整体流程中所有代码都贴出来了，写得比较匆忙，如有不当的地方，请大家不吝赐教~~~

 

附：

博主的需求中还有非单行的复杂表头，涉及到合并单元格表头，甚至左表头等等复杂excel模板，如：

 

......

 

这部分excel的解析简直就是非人类的需求，如有需要，会在后续博文继续贴出相关代码，请多多支持....

 

原创文章，代码都是从自己项目里贴出来的。转载请注明出处哦，亲~~~