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