Example #1
0
        /// <summary>
        /// 批量保存数据
        /// </summary>
        /// <param name="dt">数据</param>
        /// <param name="extraInfo">额外参数</param>
        /// <param name="userInfo">用户信息</param>
        public override object SaveImportData(DataTable dt, Dictionary<string, object> extraInfo, UserInfo userInfo)
        {
            string columnName = string.Empty;
            object objExtra = null;
            Dictionary<string, string> dict = null;
            object objCellValue = null;

            List<string> listAssetsId = new List<string>();
            string strAssetsId = string.Empty;

            foreach (DataRow dr in dt.Rows)
            {
                foreach (DataColumn dc in dt.Columns)
                {
                    columnName = dc.ColumnName;
                    if (extraInfo.TryGetValue(columnName, out objExtra))
                    {
                        dict = objExtra as Dictionary<string, string>;
                        if (dict != null)
                        {
                            objCellValue = dr[columnName];
                            if (!ExcelImportHelper.ObjectIsNullOrEmpty(objCellValue))
                            {
                                dr[columnName] = dict[objCellValue.ToString()];
                            }
                        }
                    }
                }
            }

            try
            {
                //保存任务数据
                List<TaskUtil> list = dt.ToList<TaskUtil>();
                foreach (var item in list)
                {
                    TaskHelper.SaveTask(item);
                }
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Example #2
0
 /// <summary>
 /// 批量保存数据
 /// </summary>
 /// <param name="dt">数据,可以调用CPQuery.MultiInsert(strSQL,dt)方法进行批量保存</param>
 /// <param name="extraInfo">额外参数</param>
 /// <param name="userInfo">用户信息</param>
 /// <returns>返回的额外数据信息,用于导入查询后台返回excel数据使用</returns>
 public abstract object SaveImportData(DataTable dt, Dictionary<string, object> extraInfo, UserInfo userInfo);
Example #3
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;
        }
Example #4
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;
        }