public static async Task <Report4TWModel> GetData4TW(this IRepository <Leaguer> repository, int year)
{
var source = new Report4TWModel();
await repository.LoadStoredProc("spGetData4TW")
.WithSqlParam("@year", year)
.ExecuteStoredProcAsync((result) =>
{
source.Folks = result.ReadNextListOrEmpty <Data4TWModel>().ToList();
source.FemaleFolks = result.ReadNextListOrEmpty <Data4TWModel>().ToList();
source.Religions = result.ReadNextListOrEmpty <Data4TWModel>().ToList();
source.Total = result.ReadNextListOrEmpty <Data4TWModel>().First().Total;
});
return(source);
}
private byte[] Generate4TW(Report4TWModel excelData, int year)
{
//dir path của file template
using var workbook =
new XLWorkbook(Path.GetFullPath(Directory.GetCurrentDirectory() +
"\\Contents\\Templates\\Mau 4-TW.xlsx"));
var worksheet = workbook.GetWorkSheet("Sheet1");
// Start generate content
// date-year
worksheet.Cell(3, 1).Value += $"(Tính đến 31/12/{year})";
// folk
if (excelData.Folks.Any())
{
foreach (var data in excelData.Folks)
{
if (data.Name == "Kinh")
{
worksheet.Cell(8, 3).Value = data.Total;
var percent = data.Total / excelData.Total * 100;
worksheet.Cell(8, 5).Value = Math.Round(percent, 2);
}
}
}
// female folk
if (excelData.FemaleFolks.Any())
{
foreach (var data in excelData.FemaleFolks)
{
if (data.Name == "Kinh")
{
worksheet.Cell(8, 4).Value = data.Total;
}
}
}
// religion
if (excelData.Religions.Any())
{
foreach (var data in excelData.Religions)
{
switch (data.Name)
{
case "Không":
worksheet.Cell(39, 7).Value = "Không";
worksheet.Cell(39, 8).Value = data.Total;
worksheet.Cell(39, 10).Value = Math.Round(data.Total / excelData.Total * 100, 2);
break;
case "Đạo Phật":
worksheet.Cell(32, 8).Value = data.Total;
worksheet.Cell(32, 10).Value = Math.Round(data.Total / excelData.Total * 100, 2);
break;
}
}
}
// signature
worksheet.GenerateMergeCell(41, new MergeCellModel {
From = 7, To = 10, Text = $"Đà Nẵng, ngày 30 tháng 12 năm {year}", IsFontBold = false
});
// wrap text
worksheet.Columns().Style.Alignment.WrapText = true;
//add to stream return to controller
using var stream = new MemoryStream();
workbook.SaveAs(stream);
return(stream.ToArray());
}