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)));
}
