// 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"];

            ValidateExcel();
            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);
        }
Exemple #3
0
        /// <summary>
        /// 解析并检查EXCEL表头数据
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="uploadExcelFileResult"></param>
        /// <param name="list"></param>
        /// <returns></returns>
        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);
        }
Exemple #4
0
        /// <summary>
        /// 检查excel数据
        /// </summary>
        /// <param name="sheet">excel工作表</param>
        /// <param name="list">规则集</param>
        /// <param name="dict">表头</param>
        /// <param name="rowCount">总数据行数</param>
        /// <returns>检查结果</returns>
        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);
        }