예제 #1
0
        /// <summary>
        ///返回对应的导出模版数据
        /// </summary>
        /// <param name="ins">导入文件流</param>
        /// <param name="fileName">文件名</param>
        /// <param name="userInfo">用户信息</param>
        /// <returns>ImportResult</returns>
        public virtual ImportResult ImportTemplate(Stream ins, string fileName, UserInfo userInfo)
        {
            if (DictFields == null)
            {
                throw new ArgumentNullException("Excel字段映射及校验缓存字典DictFields空异常");
            }
            //1.读取数据
            ISheet    datasheet = null;
            DataTable dt        = GetDataFromExcel(ins, out datasheet);

            //2.校验列是否正确
            //相同列数
            int equalCount = (from p in GetColumnList(dt)
                              join q in DictFields.Keys
                              on p equals q
                              select p).Count();

            if (equalCount < DictFields.Keys.Count)
            {
                throw new Exception(string.Format("模版列和规定的不一致,正确的列为({0})", string.Join(",", DictFields.Keys)));
            }


            //2.改变列名为英文字段名
            ImportVerify  objVerify     = null;
            List <string> columns       = new List <string>();
            List <string> removeColumns = new List <string>();

            foreach (DataColumn dc in dt.Columns)
            {
                if (DictFields.TryGetValue(dc.ColumnName, out objVerify))
                {
                    if (objVerify != null)
                    {
                        dc.ColumnName = objVerify.FieldName;
                        columns.Add(objVerify.FieldName);
                        continue;
                    }
                }
                removeColumns.Add(dc.ColumnName);
            }
            //3.删除无效列
            foreach (string remove in removeColumns)
            {
                dt.Columns.Remove(remove);
            }

            //4.获取校验所需额外参数
            Dictionary <string, object> extraInfo = GetExtraInfo(columns, dt);

            // 英文字段名到中文列名映射关系
            Dictionary <string, ImportVerify> DictColumnFields = DictFields.Values.ToDictionary(e => e.FieldName, e => e);

            //5.开始校验
            ImportResult result = Verify(dt, datasheet, extraInfo, userInfo, fileName, DictColumnFields);

            if (result.IsSuccess)
            {
                //校验完成后进行数据类型转换
                ImportVerify iv         = null;
                Type         columnType = null;
                DataTable    dtNew      = dt.Clone();
                foreach (DataColumn dc in dtNew.Columns)
                {
                    if (DictColumnFields != null && DictColumnFields.TryGetValue(dc.ColumnName, out iv))
                    {
                        if (iv.DataType != null)
                        {
                            columnType = iv.DataType;
                        }
                        else
                        {
                            columnType = dc.DataType;
                        }
                    }
                    else
                    {
                        columnType = typeof(string);
                    }
                    dc.DataType = columnType;
                }
                //复制数据到克隆的datatable里
                try
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        dtNew.ImportRow(dr);
                    }
                }
                catch { }

                //3.保存数据
                result.ExtraInfo = SaveImportData(dtNew, extraInfo, userInfo);
                result.Message   = string.Format("成功导入{0}条数据", dtNew.Rows.Count);
            }
            return(result);
        }
예제 #2
0
        /// <summary>
        /// 校验数据是否正常
        /// </summary>
        /// <param name="dt">数据集</param>
        /// <param name="outputStream">输出流</param>
        /// <param name="sheet">数据sheet</param>
        /// <param name="userInfo">用户信息</param>
        /// <param name="fileName">文件名称</param>
        /// <param name="DictColumnFields">英文字段名到中文列名映射关系</param>
        /// <returns>ImportResult</returns>
        public virtual ImportResult Verify(DataTable dt, ISheet sheet, Dictionary <string, object> extraInfo, UserInfo userInfo, string fileName, Dictionary <string, ImportVerify> DictColumnFields)
        {
            IWorkbook    wb     = sheet.Workbook;
            ImportResult result = new ImportResult();

            string[]          arrErrorMsg    = null;
            string            errorMsg       = string.Empty;
            int               columnCount    = dt.Columns.Count;
            string            columnName     = string.Empty;
            ImportVerify      objVerify      = null;
            ImportVerifyParam objVerifyParam = new ImportVerifyParam {
                DTExcel = dt, CellValue = null, ColName = columnName, ColumnIndex = 0, RowIndex = 0
            };
            DataRow row       = null;
            object  objExtra  = null;
            bool    isCorrect = true;

            //错误数据行样式
            var   cellErrorStyle = NPOIHelper.GetErrorCellStyle(wb);
            ICell errorCell      = null;
            IRow  sheetRow       = null;

            for (int i = 0, rLength = dt.Rows.Count; i < rLength; i++)
            {
                row         = dt.Rows[i];
                arrErrorMsg = new string[columnCount];
                for (int j = 0; j < columnCount; j++)
                {
                    columnName = dt.Columns[j].ColumnName;
                    if (DictColumnFields.TryGetValue(columnName, out objVerify))
                    {
                        if (objVerify.VerifyFunc != null)
                        {
                            objVerifyParam.CellValue   = row[j];
                            objVerifyParam.ColumnIndex = j;
                            objVerifyParam.RowIndex    = i;
                            objVerifyParam.ColName     = objVerify.ColumnName;
                            if (extraInfo != null)
                            {
                                extraInfo.TryGetValue(columnName, out objExtra);
                            }
                            arrErrorMsg[j] = objVerify.VerifyFunc(objVerifyParam, objExtra);
                        }
                    }
                }
                errorMsg = string.Join(",", arrErrorMsg.Where(e => !string.IsNullOrEmpty(e)));
                if (!string.IsNullOrEmpty(errorMsg))
                {
                    isCorrect = false;
                    //设置错误信息
                    sheetRow  = sheet.GetRow(StartRowIndex + 1 + i);
                    errorCell = sheetRow.GetCell(columnCount);
                    if (errorCell == null)
                    {
                        errorCell = sheetRow.CreateCell(columnCount);
                    }
                    errorCell.CellStyle = cellErrorStyle;
                    errorCell.SetCellValue(errorMsg);
                }
            }

            //输出错误信息模版
            if (!isCorrect)
            {
                sheetRow  = sheet.GetRow(StartRowIndex);
                errorCell = sheetRow.GetCell(columnCount);
                if (errorCell == null)
                {
                    errorCell = sheetRow.CreateCell(columnCount);
                }
                ICellStyle copyStyle = sheetRow.GetCell(columnCount - 1).CellStyle;
                ICellStyle style     = NPOIHelper.GetErrorHeadCellStyle(wb);
                IFont      font      = style.GetFont(wb);
                IFont      copyfont  = copyStyle.GetFont(wb);
                font.FontHeight           = copyfont.FontHeight;
                font.FontName             = copyfont.FontName;
                style.FillForegroundColor = copyStyle.FillForegroundColor;
                style.BorderBottom        = copyStyle.BorderBottom;
                style.BorderLeft          = copyStyle.BorderLeft;
                style.BorderRight         = copyStyle.BorderRight;
                style.BorderTop           = copyStyle.BorderTop;
                errorCell.CellStyle       = style;
                errorCell.SetCellValue("错误信息");

                //自适应列宽度
                sheet.AutoSizeColumn(columnCount);
                int width = sheet.GetColumnWidth(columnCount) + 2560;
                sheet.SetColumnWidth(columnCount, width > NPOIHelper.MAX_COLUMN_WIDTH ? NPOIHelper.MAX_COLUMN_WIDTH : width);

                result.Message = ExcelImportHelper.GetErrorExcel(wb, fileName);
            }
            else
            {
                result.IsSuccess = true;
            }
            return(result);
        }