public async Task <ResponseResult> Handle( Command request, CancellationToken cancellationToken) { IEnumerable <MaterialExport> queryResult; using (var scope = _databaseScopeFactory.CreateReadOnly()) { queryResult = await _repository.FindMaterialsWithAllPropertiesByTypeAsync <MaterialExport>(request.TypeId); } if (queryResult?.Any() ?? false) { var list = queryResult.ToList(); var createResult = await CreateFolder(); var path = createResult.Item1; var fileName = $"{request.TypeName}{request.UserName}-{createResult.Item2}.xlsx"; FileInfo file = new FileInfo($@"{path}\{fileName}"); if (file?.Exists ?? false) { file.Delete(); } using (ExcelPackage package = new ExcelPackage(file)) { ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("物料"); worksheet.Cells[1, 1].Value = "物料编号"; worksheet.Cells[1, 2].Value = "物料名称"; worksheet.Cells[1, 3].Value = "物料类型"; worksheet.Cells[1, 4].Value = "物料规格"; worksheet.Cells[1, 5].Value = "物料单位"; worksheet.Cells[1, 6].Value = "是否为生产物料"; worksheet.Cells[1, 7].Value = "是否有独立编号"; worksheet.Cells[1, 8].Value = "采购主体"; worksheet.Cells[1, 9].Value = "建议采购价"; worksheet.Cells[1, 9].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; worksheet.Cells[1, 10].Value = "是否已发布"; worksheet.Cells[1, 11].Value = "发布时间"; worksheet.Cells[1, 12].Value = "是否启用"; // 自定义属性名称 var properties = list.First().Properties; if (properties?.Any() ?? false) { var col = 13; foreach (var property in properties) { worksheet.Cells[1, col].Value = property.DisplayName; col++; } } int row = 2; foreach (var item in list) { worksheet.Cells[row, 1].Value = item.MateCode; worksheet.Cells[row, 2].Value = item.MateName; worksheet.Cells[row, 3].Value = item.TypeName; worksheet.Cells[row, 4].Value = item.MaterialSpec; worksheet.Cells[row, 5].Value = item.MaterialUnits; worksheet.Cells[row, 6].Value = item.IsSelfBuild ? "是" : "否"; worksheet.Cells[row, 7].Value = item.IdCodeSingle ? "是" : "否"; worksheet.Cells[row, 8].Value = item.TradeName; worksheet.Cells[row, 9].Value = string.Format("{0:#,##0.00}", item.MaterialPrice); worksheet.Cells[row, 9].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; worksheet.Cells[row, 10].Value = (item.AuditState == 1) ? "是" : "否"; worksheet.Cells[row, 11].Value = (item.AuditState == 1) ? string.Format("{0:yyyy-MM-dd HH:mm:ss}", item.AuditTime) : string.Empty; worksheet.Cells[row, 12].Value = item.IsDisable ? "禁用" : "启用"; // 自定义属性 var options = item.Properties; if (options?.Any() ?? false) { var col = 13; foreach (var option in options) { worksheet.Cells[row, col].Value = option.OptionName; col++; } } row++; } worksheet.View.FreezePanes(2, 1); worksheet.Cells.AutoFitColumns(); package.Save(); } string ossEndpoint = "http://oss-cn-hangzhou.aliyuncs.com"; string ossAccessKeyId = "LTAIFAiipRXidbYT"; string ossAccessKeySecret = "62zh9kaAd60NAqMxNPCqVcvxhNxH0H"; string ossBucketName = "basichz"; string ossRootFolder = "productcenter"; string ossAccessUrl = "//oss.lunz.cn/"; var fileManager = new OssFileManager(ossEndpoint, ossAccessKeyId, ossAccessKeySecret, ossBucketName, ossRootFolder, ossAccessUrl); // 上传至阿里云 var uploadResult = fileManager.Upload(file.Name.Trim(), $@"{path}\{file.Name.Trim()}"); // 返回阿里云Url地址 var url = $@"http://basichz.lunz.cn/{uploadResult.Key}"; file.Refresh(); var resource = new ResourceItem { FileName = fileName, FileType = file.Extension, // .xlsx FileLength = Convert.ToInt32(Math.Ceiling(file.Length / 1024.0)), FileURL = url, }; using (var scope = _databaseScopeFactory.CreateWithTransaction()) { await _repository.AddResourceAsync(resource, request.UserId); scope.SaveChanges(); } // 删除文件夹 DirectoryInfo directory = new DirectoryInfo(path); if (directory.Exists) { directory.Delete(true); } } return(ResponseResult.Ok()); }
public async Task <ResponseResult <Response> > Handle( Command request, CancellationToken cancellationToken) { var queryResult = new MaterialTypeExport(); IEnumerable <MaterialPropertyExport> basicProperties; IEnumerable <Lunz.ProductCenter.MService.QueryStack.Models.Trade> trades; using (var scope = _databaseScopeFactory.CreateReadOnly()) { queryResult = await _repository.FindCustomPropertiesByTypeAsync <MaterialTypeExport>(request.TypeId); basicProperties = await _repository.FindBasicPropertiesAsync <MaterialPropertyExport>(); trades = await _repository.FindAllTradePropertiesAsync <QueryStack.Models.Trade>(); } string url = string.Empty; if (queryResult != null) { var createResult = await CreateFolder(); var path = createResult.Item1; var fileName = $"物料模板-{request.TypeName}{request.UserName}-{createResult.Item2}.xlsx"; FileInfo file = new FileInfo($@"{path}\{fileName}"); if (file?.Exists ?? false) { file.Delete(); } using (ExcelPackage package = new ExcelPackage(file)) { // 物料模板sheet ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("物料"); // 物料属性sheet ExcelWorksheet propertysheet = package.Workbook.Worksheets.Add("属性"); // 第一行 worksheet.Cells[1, 1].Value = $"物料类型Id:{queryResult.Id}"; worksheet.Cells[1, 2].Value = $"物料类型:{queryResult.TypeName}"; worksheet.Cells[1, 3].Value = $"物料编码:{request.TypeCode}"; worksheet.Cells[1, 4].Value = $"模板导出时间:{string.Format("{0:yyyy-MM-dd HH:mm:ss}", DateTime.Now)}"; // row 2, col 1, 物料名称列 worksheet.Cells[2, 1].IsRichText = true; ExcelRichText ert = worksheet.Cells[2, 1].RichText.Add("物料名称"); ert = worksheet.Cells[2, 1].RichText.Add("*"); ert.Color = Color.Red; // 从row 3到maxRows, col 1, 物料名称列验证 var minLength = 1; var maxLength = 40; int maxRows = ExcelPackage.MaxRows; var textValidation = worksheet.DataValidations.AddTextLengthValidation($"A3:A{maxRows}"); textValidation.ShowErrorMessage = true; textValidation.ErrorStyle = ExcelDataValidationWarningStyle.stop; textValidation.ErrorTitle = "物料名称"; textValidation.Error = string.Format("物料名称不能为空,且最大长度不能超过{0}个字符以内的汉字、字母、数字、符号的组合", maxLength); textValidation.Formula.Value = minLength; textValidation.Formula2.Value = maxLength; // 属性sheet var optionRow = 2; var optionCol = 1; // 物料sheet, row 2, col 2, col 3, DT0000000015: 物料单位, DT0000000031: 物料规格 if (basicProperties?.Count() == 2) { optionCol = 1; // 物料sheet, 从row 3到maxRows, col 2, col 3, 物料规格列下拉列表, 物料单位列下拉列表 var col = 2; foreach (var property in basicProperties) { worksheet.Cells[2, col].IsRichText = true; ert = worksheet.Cells[2, col].RichText.Add(property.DisplayName); ert = worksheet.Cells[2, col].RichText.Add("*"); ert.Color = Color.Red; var basicPropertiesRange = ExcelRange.GetAddress(3, col, ExcelPackage.MaxRows, col); // 属性值 var options = property.Options; if (options?.Any() ?? false) { var basicPropertiesValidation = worksheet.DataValidations.AddListValidation(basicPropertiesRange); basicPropertiesValidation.ShowErrorMessage = true; basicPropertiesValidation.ErrorStyle = ExcelDataValidationWarningStyle.stop; basicPropertiesValidation.ErrorTitle = "选择基本属性"; basicPropertiesValidation.Error = "请从属性列表中选择一项"; var valueRange = ExcelRange.GetAddress(2, optionCol, options.Count + 1, optionCol, true); basicPropertiesValidation.Formula.ExcelFormula = $@"属性!{valueRange}"; // 属性sheet, 属性名 propertysheet.Cells[1, optionCol].Value = property.DisplayName; optionRow = 2; foreach (var option in options) { // 属性sheet, 所有属性选项值 propertysheet.Cells[optionRow, optionCol].Value = $"{option.OptionId}:{option.OptionName}"; optionRow++; } } optionCol++; col++; } } else { worksheet.Cells[2, 2].IsRichText = true; ert = worksheet.Cells[2, 2].RichText.Add("物料规格"); ert = worksheet.Cells[2, 2].RichText.Add("*"); ert.Color = Color.Red; worksheet.Cells[2, 3].IsRichText = true; ert = worksheet.Cells[2, 3].RichText.Add("物料单位"); ert = worksheet.Cells[2, 3].RichText.Add("*"); ert.Color = Color.Red; } // row 2, col 4, 是否为生产物料列, 【是否为生产物料】选择为“是”时,【是否有独立编码】默认为“否”且不可修改。 worksheet.Cells[2, 4].IsRichText = true; ert = worksheet.Cells[2, 4].RichText.Add("是否为生产物料"); ert = worksheet.Cells[2, 4].RichText.Add("*"); ert.Color = Color.Red; // 是否为生产物料下拉列表 var range = ExcelRange.GetAddress(3, 4, ExcelPackage.MaxRows, 4); var listValidation = worksheet.DataValidations.AddListValidation(range); listValidation.ShowErrorMessage = true; listValidation.ErrorStyle = ExcelDataValidationWarningStyle.stop; listValidation.ErrorTitle = "选择基本属性"; listValidation.Error = "请从属性列表中选择一项"; listValidation.Formula.Values.Add("0:否"); listValidation.Formula.Values.Add("1:是"); // row 2, col 5, 是否有独立编号列 worksheet.Cells[2, 5].IsRichText = true; ert = worksheet.Cells[2, 5].RichText.Add("是否有独立编号"); ert = worksheet.Cells[2, 5].RichText.Add("*"); ert.Color = Color.Red; // 是否有独立编号下拉列表 range = ExcelRange.GetAddress(3, 5, ExcelPackage.MaxRows, 5); listValidation = worksheet.DataValidations.AddListValidation(range); listValidation.ShowErrorMessage = true; listValidation.ErrorStyle = ExcelDataValidationWarningStyle.stop; listValidation.ErrorTitle = "选择基本属性"; listValidation.Error = "请从属性列表中选择一项"; listValidation.Formula.Values.Add("0:否"); listValidation.Formula.Values.Add("1:是"); // row 2, col 6, 采购主体列 StringBuilder tradeValue = new StringBuilder(); tradeValue.Append(Environment.NewLine); tradeValue.Append("(多个采购主体用英文逗号隔开)"); if (trades?.Any() ?? false) { tradeValue.Append(Environment.NewLine); foreach (var trade in trades) { tradeValue.Append($"{trade.Id}:{trade.TradeName},"); } } var value = tradeValue.ToString(); worksheet.Cells[2, 6].IsRichText = true; ert = worksheet.Cells[2, 6].RichText.Add("采购主体"); if (!string.IsNullOrEmpty(request.TypeCode) && request.TypeCode.StartsWith(_tradCode)) { ert = worksheet.Cells[2, 6].RichText.Add("*"); ert.Color = Color.Red; } ert = worksheet.Cells[2, 6].RichText.Add(value.Remove(value.LastIndexOf(','))); ert.Color = Color.Black; worksheet.Cells[2, 6].Style.WrapText = true; // row 2, col 7, 建议采购价列 worksheet.Cells[2, 7].Value = "建议采购价(5位以内的正数,两位小数)"; worksheet.Cells[2, 7].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; // 从row 3到maxRows, col 7, 建议采购价列验证 range = ExcelRange.GetAddress(3, 7, ExcelPackage.MaxRows, 7); var decimalValidation = worksheet.DataValidations.AddDecimalValidation(range); decimalValidation.ErrorStyle = ExcelDataValidationWarningStyle.stop; decimalValidation.ErrorTitle = "建议采购价"; decimalValidation.Error = "建议采购价是5位以内的正数"; decimalValidation.ShowErrorMessage = true; decimalValidation.Operator = ExcelDataValidationOperator.between; decimalValidation.Formula.Value = 0.00; decimalValidation.Formula2.Value = 99999.99; // 属性sheet optionRow = 2; // row 2, 从col 8开始, 自定义属性列 var properties = queryResult.Properties; if (properties?.Any() ?? false) { optionCol = 3; var col = 8; foreach (var property in properties) { // 从row 3开始,添加自定义属性选项值下拉列表 range = ExcelRange.GetAddress(3, col, ExcelPackage.MaxRows, col); // 属性选项值 var options = property.Options; if (options?.Any() ?? false) { listValidation = worksheet.DataValidations.AddListValidation(range); listValidation.ShowErrorMessage = true; listValidation.ErrorTitle = "选择自定义属性"; listValidation.Error = "请从属性列表中选择一项"; var customerOptionRange = ExcelRange.GetAddress(2, optionCol, options.Count + 1, optionCol, true); listValidation.Formula.ExcelFormula = $@"属性!{customerOptionRange}"; // 属性sheet, 属性名 propertysheet.Cells[1, optionCol].Value = $"{property.PropId}:{property.DisplayName}"; optionRow = 2; foreach (var option in options) { // 属性sheet, 所有属性选项值 propertysheet.Cells[optionRow, optionCol].Value = $"{option.OptionId}:{option.OptionName}"; optionRow++; } } // 自定义属性是否必填 if (property.IsNecessary) { worksheet.Cells[2, col].IsRichText = true; ert = worksheet.Cells[2, col].RichText.Add($"{property.PropId}:{property.DisplayName}"); ert = worksheet.Cells[2, col].RichText.Add("*"); ert.Color = Color.Red; if (options?.Any() ?? false) { listValidation.ErrorStyle = ExcelDataValidationWarningStyle.stop; } } else { worksheet.Cells[2, col].Value = $"{property.PropId}:{property.DisplayName}"; if (options?.Any() ?? false) { listValidation.ErrorStyle = ExcelDataValidationWarningStyle.warning; } } optionCol++; col++; } } worksheet.View.FreezePanes(3, 1); worksheet.Cells.AutoFitColumns(); propertysheet.Cells.AutoFitColumns(); worksheet.Column(6).Width = 32; package.Save(); } string ossEndpoint = "http://oss-cn-hangzhou.aliyuncs.com"; string ossAccessKeyId = "LTAIFAiipRXidbYT"; string ossAccessKeySecret = "62zh9kaAd60NAqMxNPCqVcvxhNxH0H"; string ossBucketName = "basichz"; string ossRootFolder = "productcenter"; string ossAccessUrl = "//oss.lunz.cn/"; var fileManager = new OssFileManager(ossEndpoint, ossAccessKeyId, ossAccessKeySecret, ossBucketName, ossRootFolder, ossAccessUrl); // 上传至阿里云 var uploadResult = fileManager.Upload(file.Name.Trim(), $@"{path}\{file.Name.Trim()}"); // 返回阿里云Url地址 url = $@"http://basichz.lunz.cn/{uploadResult.Key}"; // 删除文件夹 DirectoryInfo directory = new DirectoryInfo(path); if (directory.Exists) { directory.Delete(true); } } return(ResponseResult <Response> .Ok(new Response(url))); }