コード例 #1
0
        private ExcelOperationResult ValidDefault(ExcelWorksheet ws, ExcelOperationOption option, List <ImportConfig> configList)
        {
            ExcelOperationResult result = new ExcelOperationResult();

            result.Messages = new List <ErrorMessage>();
            var clumns = GetHeaderFromSheet(ws);
            //验证是否有名称不在允许的列中

            var notExistList = new List <string>();

            if (configList == null || configList.Count == 0)
            {
                throw new System.Exception("excel到表字段的配置表不存在");
            }
            var notExistImportClumns = clumns.FindAll(a => !configList.Exists(b => b.ExcelName == a));

            if (notExistImportClumns.Count > 0)
            {
                var notExistsColumnStr = string.Join(",", notExistImportClumns.ToArray());
                result.Messages.Add(new ErrorMessage()
                {
                    Name = "列不存在:" + notExistsColumnStr, Nums = notExistList.Count.ToString()
                });
            }
            //验证有重复列
            if (clumns.Count != ws.Dimension.End.Column)
            {
                result.Messages.Add(new ErrorMessage()
                {
                    Name = "有名称重复的列", Nums = (ws.Dimension.End.Column - clumns.Count).ToString()
                });
            }

            return(result);
        }
コード例 #2
0
        private int CreateImportChief(ExcelOperationOption option, string name)
        {
            var sql = string.Format("insert into LC_Meta_ImportChiefs(ExcelName,OperationResult,InsertNum,UpdateNum,Operator,OperationTime,IsUpdated) " +
                                    "values('{0}','','0','0','',Getdate(),0)" +
                                    "SELECT CAST(SCOPE_IDENTITY() AS INT)", name);

            using (var con = new SqlConnection(option.BatchOption.ConnectionString))
            {
                var chiefId = con.ExecuteScalar(sql);
                return((int)chiefId);
            }
        }
コード例 #3
0
        public async Task <ExcelOperationResult> Upload(IFormFile file, ExcelOperationOption option)
        {
            ExcelOperationResult validResult = new ExcelOperationResult();

            try
            {
                using (var memoryStream = new MemoryStream())
                {
                    await file.CopyToAsync(memoryStream).ConfigureAwait(false);

                    using (var package = new ExcelPackage(memoryStream))
                    {
                        var worksheet  = package.Workbook.Worksheets[1]; // Tip: To access the first worksheet, try index 1, not 0
                        var configList = _batchSqlOperation.Select <ImportConfig>(new BatchOperationOption()
                        {
                            ConnectionString = option.BatchOption.ConnectionString,
                            TableName        = string.IsNullOrEmpty(option.TableName) ? "LC_Meta_ImportConfigs" : option.TableName,
                            Where            = string.Format(" ImportType={0} and IsUpdate=1", (int)option.ImportType)
                        });
                        //var list = configList.ToList();
                        //configList.CopyTo(list);
                        option.BatchOption.AllowedAllColumns = configList.ToArray();
                        validResult = ValidDefault(worksheet, option, configList);
                        if (validResult != null && validResult.Messages != null && validResult.Messages.Count > 0)
                        {
                            return(validResult);
                        }
                        ExcelValidContext context = new ExcelValidContext()
                        {
                            Worksheet = worksheet, OperationOption = option
                        };
                        validResult = BeforeUploadValid(context);
                        if (validResult != null && validResult.Messages != null && validResult.Messages.Count > 0)
                        {
                            return(validResult);
                        }
                        var chiefId = CreateImportChief(option, file.Name);
                        var table   = worksheet.ToMappedDataTable(configList);

                        context = new ExcelValidContext()
                        {
                            Worksheet = worksheet, OperationOption = option, Table = table
                        };
                        validResult = AfterUploadValid(context);
                        if (validResult != null && validResult.Messages != null && validResult.Messages.Count > 0)
                        {
                            return(validResult);
                        }
                        var importColumns = GetHeaderFromSheet(worksheet);
                        configList = configList.FindAll(a => importColumns.Exists(b => b == a.ExcelName));
                        Dictionary <string, string> dict = new Dictionary <string, string>();
                        foreach (var item in configList)
                        {
                            dict.Add(item.ExcelName, item.ColumnName);
                        }
                        option.BatchOption.ColumnMapping = dict;

                        if (chiefId > 0)
                        {
                            option.BatchOption.ChiefId = chiefId;
                            _batchSqlOperation.InsertOrUpdate(table, option.BatchOption);
                        }
                    }
                }
            }
            catch (System.Exception ex)
            {
                validResult = new ExcelOperationResult();
                validResult.Messages.Add(new ErrorMessage()
                {
                    Name = "上传错误:" + ex.Message, Nums = "1"
                });
            }

            return(validResult);
        }