Magicodes.IE 按绩效模板生成下载数据 C#

/// <summary>
/// 按绩效模板生成下载数据(新)
/// </summary>
/// <param name="input"></param>
[HttpPost("printexcle1")]
[UnitOfWork]//事务开启工作单元
[ApiExplorerSettings(IgnoreApi = false)]//接口可见性
//[Fact(DisplayName = "Excel模板导出教材订购明细样表")]
public async Task<IActionResult> PrintExcle1(QueryePerformanceAssessorsInput input)
{
    try
    {

    
    var httpQueryData = (await _performanceAssessorsRep.DetachedEntities.FirstOrDefaultAsync(u => u.Id == input.Id)).Adapt<PerformanceAssessorsInput>();
    if (httpQueryData == null) throw Oops.Oh(ErrorCode.D1012);
    string week = httpQueryData.StartTime.Year + "年" + httpQueryData.StartTime.Month + "月第" + (httpQueryData.StartTime.Day / 7 + (httpQueryData.StartTime.Day % 7 == 0 ? 0 : 1)) + "周";
    var prints = await _performanceItemsRep.DetachedEntities.Where(a => a.DbId == input.Id).ProjectToType<ExportPerformanceItemsInput>().ToListAsync();
    ExportPerformanceAssessorsInput assessorsInput = new ExportPerformanceAssessorsInput();
    assessorsInput.DeptName= httpQueryData.DeptName;
    assessorsInput.BelongName = httpQueryData.BelongName;
    assessorsInput.EvaluationWeek = week;
    assessorsInput.AssessmentCycle = httpQueryData.StartTime.ToString("yyyy-MM-dd") + "-" + httpQueryData.EndTime.ToString("yyyy-MM-dd");
    assessorsInput.DateTime = DateTime.Now.ToString("yyyy-MM-dd");
    assessorsInput.SynthesisScore = httpQueryData.SynthesisScore;
    assessorsInput.WorkWeightedSubtotal = httpQueryData.WorkWeightedSubtotal;
    assessorsInput.KPIWeightedSubtotal = httpQueryData.KPIWeightedSubtotal;
    assessorsInput.PerformanceItemsInput = prints.Where(a=>a.Project== "KPI指标（80%）").ToList();
   var prints2 = prints.Where(a => a.Project == "工作能力与态度（20%）").ToList();
    var pmodel = prints2.Where(a => a.Number == 1 && a.DefinitionIndicators == "有很强的责任心，从不推卸责任，积极主动地圆满完成工作").FirstOrDefault();
    if (pmodel != null)
    {
        assessorsInput.ScoreTransaction1 = pmodel.ScoreTransaction;
        assessorsInput.SuperiorRating1 = pmodel.SuperiorRating;
        assessorsInput.ScoreItem1 = pmodel.ScoreItem;
    }
    var pmodel2 = prints2.Where(a => a.Number == 2 && a.DefinitionIndicators == "执行力强，能创造性的开展工作，提前并保质保量完成目标任务").FirstOrDefault();
    if (pmodel2 != null)
    {
        assessorsInput.ScoreTransaction2 = pmodel2.ScoreTransaction;
        assessorsInput.SuperiorRating2 = pmodel2.SuperiorRating;
        assessorsInput.ScoreItem2 = pmodel2.ScoreItem;
    }
    var pmodel3 = prints2.Where(a => a.Number == 3 && a.DefinitionIndicators == "持续地积极努力工作，并以此带动其他人的工作").FirstOrDefault();
    if (pmodel3 != null)
    {
        assessorsInput.ScoreTransaction3 = pmodel3.ScoreTransaction;
        assessorsInput.SuperiorRating3 = pmodel3.SuperiorRating;
        assessorsInput.ScoreItem3 = pmodel3.ScoreItem;
    }
    var pmodel4 = prints2.Where(a => a.Number == 4 && a.DefinitionIndicators == "学习能力很强，善于从不同渠道学习多专业知识，并能很好的吸收，并触类旁通").FirstOrDefault();
    if (pmodel4 != null)
    {
        assessorsInput.ScoreTransaction4 = pmodel4.ScoreTransaction;
        assessorsInput.SuperiorRating4 = pmodel4.SuperiorRating;
        assessorsInput.ScoreItem4 = pmodel4.ScoreItem;
    }
    var pmodel5 = prints.Where(a => a.Number == 1 && a.Project == "调整分数项").FirstOrDefault();
    if (pmodel5 != null)
    {
        assessorsInput.ScoreItem5 = pmodel5.ScoreItem;
    }
    var pmodel6 = prints.Where(a => a.Number == 2 && a.Project == "调整分数项").FirstOrDefault();
    if (pmodel6 != null)
    {
        assessorsInput.ScoreItem6 = pmodel6.ScoreItem;
    }
    string tplPath = "D:\\绩效考核模板\\绩效考核表.xlsx";//模板路径
    Random random = new Random();
    FileInput2 model = new FileInput2();
    model.DbId = input.Id;
    model.Module = httpQueryData.BelongName;
    //var fs11 = System.IO.File.OpenRead(path);
    string extension11 = Path.GetExtension(tplPath);
    var fileInformation = await _ISysFileService.UniversalUpload3(extension11, 0, model, tplPath, week);
    var filePath = fileInformation.Path;//生成服务器路径
    //模板路径
    //var tplPath = Path.Combine(Directory.GetCurrentDirectory(), "TestFiles", "ExportTemplates",  "2020年春季教材订购明细样表.xlsx");
    //创建Excel导出对象
    IExportFileByTemplate exporter = new ExcelExporter();
    //var localTemplatPath = @"D:\\" + httpQueryData.BelongName + week + random.Next(100000, 999999) + "绩效考核表.xlsx";//新生成文件路径以及文件名与格式
    //导出路径
   // var filePath = Path.Combine(Directory.GetCurrentDirectory(), nameof(week) + ".xlsx");
    if (File.Exists(filePath)) File.Delete(filePath);
        //根据模板导出
      var aaa=  await exporter.ExportBytesByTemplate(assessorsInput,  tplPath);
        byte[] buffer = new byte[1024 * 2];
        buffer = aaa.ToArray();
        var memoryStream = new MemoryStream(buffer);
        new FileExtensionContentTypeProvider().TryGetContentType(filePath, out string contentType);
        return await Task.FromResult(new FileStreamResult(memoryStream, contentType)
        {
            FileDownloadName = System.Web.HttpUtility.UrlEncode((httpQueryData.DeptName + httpQueryData.BelongName + week + "绩效考核表.xlsx"))
        });
    }
    catch (Exception ex)
    {

        throw Oops.Oh(ex.Message);
    }
}

模板效果如图：

生成的效果如图：

新更新：存在内存读取不保存在服务器文件

/// <summary>
/// 按绩效模板生成下载数据(新)
/// </summary>
/// <param name="input"></param>
[HttpPost("printexcle1")]
[UnitOfWork]//事务开启工作单元
[ApiExplorerSettings(IgnoreApi = false)]//接口可见性
public async Task<IActionResult> PrintExcle1(QueryePerformanceAssessorsInput input)
{
    try
    {
        var httpQueryData = (await _performanceAssessorsRep.DetachedEntities.FirstOrDefaultAsync(u => u.Id == input.Id)).Adapt<PerformanceAssessorsInput>();
        if (httpQueryData == null) throw Oops.Oh(ErrorCode.D1012);
        string week = httpQueryData.StartTime.Year + "年" + httpQueryData.StartTime.Month + "月第" + (httpQueryData.StartTime.Day / 7 + (httpQueryData.StartTime.Day % 7 == 0 ? 0 : 1)) + "周";
        var prints = await _performanceItemsRep.DetachedEntities.Where(a => a.DbId == input.Id).ProjectToType<ExportPerformanceItemsInput>().ToListAsync();
        ExportPerformanceAssessorsInput assessorsInput = new ExportPerformanceAssessorsInput();
        assessorsInput.DeptName= httpQueryData.DeptName;
        assessorsInput.BelongName = httpQueryData.BelongName;
        assessorsInput.EvaluationWeek = week;
        assessorsInput.AssessmentCycle = httpQueryData.StartTime.ToString("yyyy-MM-dd") + "—" + httpQueryData.EndTime.ToString("yyyy-MM-dd");
        assessorsInput.DateTime = DateTime.Now.ToString("yyyy-MM-dd");
        assessorsInput.SynthesisScore = httpQueryData.SynthesisScore;
        assessorsInput.WorkWeightedSubtotal = httpQueryData.WorkWeightedSubtotal;
        assessorsInput.KPIWeightedSubtotal = httpQueryData.KPIWeightedSubtotal;
        assessorsInput.PerformanceItemsInput = prints.Where(a=>a.Project== "KPI指标（80%）").ToList();
       var prints2 = prints.Where(a => a.Project == "工作能力与态度（20%）").ToList();
        var pmodel = prints2.Where(a => a.Number == 1 && a.DefinitionIndicators == "有很强的责任心，从不推卸责任，积极主动地圆满完成工作").FirstOrDefault();
        if (pmodel != null)
        {
            assessorsInput.ScoreTransaction1 = pmodel.ScoreTransaction;
            assessorsInput.SuperiorRating1 = pmodel.SuperiorRating;
            assessorsInput.ScoreItem1 = pmodel.ScoreItem;
        }
        var pmodel2 = prints2.Where(a => a.Number == 2 && a.DefinitionIndicators == "执行力强，能创造性的开展工作，提前并保质保量完成目标任务").FirstOrDefault();
        if (pmodel2 != null)
        {
            assessorsInput.ScoreTransaction2 = pmodel2.ScoreTransaction;
            assessorsInput.SuperiorRating2 = pmodel2.SuperiorRating;
            assessorsInput.ScoreItem2 = pmodel2.ScoreItem;
        }
        var pmodel3 = prints2.Where(a => a.Number == 3 && a.DefinitionIndicators == "持续地积极努力工作，并以此带动其他人的工作").FirstOrDefault();
        if (pmodel3 != null)
        {
            assessorsInput.ScoreTransaction3 = pmodel3.ScoreTransaction;
            assessorsInput.SuperiorRating3 = pmodel3.SuperiorRating;
            assessorsInput.ScoreItem3 = pmodel3.ScoreItem;
        }
        var pmodel4 = prints2.Where(a => a.Number == 4 && a.DefinitionIndicators == "学习能力很强，善于从不同渠道学习多专业知识，并能很好的吸收，并触类旁通").FirstOrDefault();
        if (pmodel4 != null)
        {
            assessorsInput.ScoreTransaction4 = pmodel4.ScoreTransaction;
            assessorsInput.SuperiorRating4 = pmodel4.SuperiorRating;
            assessorsInput.ScoreItem4 = pmodel4.ScoreItem;
        }
        var pmodel5 = prints.Where(a => a.Number == 1 && a.Project == "调整分数项").FirstOrDefault();
        if (pmodel5 != null)
        {
            assessorsInput.ScoreItem5 = pmodel5.ScoreItem;
        }
        var pmodel6 = prints.Where(a => a.Number == 2 && a.Project == "调整分数项").FirstOrDefault();
        if (pmodel6 != null)
        {
            assessorsInput.ScoreItem6 = pmodel6.ScoreItem;
        }
        string tplPath = "D:\\绩效考核模板\\绩效考核表.xlsx";//模板路径
        Random random = new Random();
        FileInput2 model = new FileInput2();
        model.DbId = input.Id;
        model.Module = httpQueryData.BelongName;
        string extension11 = Path.GetExtension(tplPath);
        var fileInformation = await _ISysFileService.UniversalUpload3(extension11, 0, model, tplPath, week);
        var filePath = fileInformation.Path;//生成服务器路径
        //模板路径
        //var tplPath = Path.Combine(Directory.GetCurrentDirectory(), "TestFiles", "ExportTemplates",  "2020年春季教材订购明细样表.xlsx");
        //创建Excel导出对象
        IExportFileByTemplate exporter = new ExcelExporter();
        //var localTemplatPath = @"D:\\" + httpQueryData.BelongName + week + random.Next(100000, 999999) + "绩效考核表.xlsx";//新生成文件路径以及文件名与格式
        //导出路径
       // var filePath = Path.Combine(Directory.GetCurrentDirectory(), nameof(week) + ".xlsx");
        if (File.Exists(filePath)) File.Delete(filePath);
        //根据模板导出
        byte[] buffer = new byte[1024 * 2];
        var aaa = await exporter.ExportBytesByTemplate(assessorsInput, tplPath);
        buffer = aaa.ToArray();
        using (var ms = new MemoryStream(buffer))
        {
            IWorkbook workbook = new XSSFWorkbook(ms);
            var sheet = workbook.GetSheetAt(0);
            CellRangeAddress cellRangeAddress = new CellRangeAddress(6, 5 + assessorsInput.PerformanceItemsInput.Count(), 0, 0); // 合并行
            sheet.AddMergedRegion(cellRangeAddress);
            //将修改后的execl对象重新写入内存
            using (MemoryStream newStream1 = new MemoryStream())
            {
                workbook.Write(newStream1);
                workbook.Close();
                buffer = newStream1.ToArray();
                ms.Close();
                newStream1.Close();
                 var memoryStream = new MemoryStream(buffer);
                return await Task.FromResult(new FileStreamResult(memoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
                {
                    FileDownloadName = (httpQueryData.DeptName + httpQueryData.BelongName + week + "绩效考核表.xlsx")
                });
            }
        }
    }
    catch (Exception ex)
    {

        throw Oops.Oh(ex.Message);
    }
}