public static async Task <Report5BTCModel> GetData5BTC(this IRepository <Leaguer> repository, int year) { var source = new Report5BTCModel(); await repository.LoadStoredProc("spGetData5BTC") .WithSqlParam("@year", year) .ExecuteStoredProcAsync((result) => { source.Units = result.ReadNextListOrEmpty <UnitModel>().ToList(); source.Leaguers = result.ReadNextListOrEmpty <Leaguer5BtcModel>().ToList(); }); return(source); }
private byte[] Generate5BTC(Report5BTCModel excelData, int year) { //dir path của file template using var workbook = new XLWorkbook(Path.GetFullPath(Directory.GetCurrentDirectory() + "\\Contents\\Templates\\Mau 5B-TC.xlsx")); var worksheet = workbook.GetWorkSheet("Sheet1"); // declare default variable const int totalColumns = 12; // starting row var currentRow = 7; // // Start generate content // date-year worksheet.Cell(2, 6).Value += $"Đà Nẵng, ngày 30 tháng 12 năm {year}"; worksheet.Cell(3, 1).Value += $"Năm {year}"; // detail var index = 1; foreach (var unit in excelData.Units) { // unit currentRow++; var rowData = new DropDownModel <string> { Key = unit.IdentifyNumber, Value = unit.Name }; worksheet.GenerateRowAndStyling(currentRow, totalColumns, true); worksheet.PrintCellsValue(rowData, currentRow, totalColumns, printedColumn: 1); // leaguers of unit foreach (var leaguer in excelData.Leaguers.Where(_ => _.UnitId == unit.Id)) { currentRow++; worksheet.GenerateRowAndStyling(currentRow, totalColumns); // index worksheet.Cell(currentRow, 1).Value = index; worksheet.Cell(currentRow, 2).Value = leaguer.Name; if (leaguer.RatingId == AppLeaguerRating.Best.ToInt()) { worksheet.Cell(currentRow, leaguer.RatingId + 2).Value = "X"; } if (leaguer.RatingId == AppLeaguerRating.Well.ToInt()) { worksheet.Cell(currentRow, leaguer.RatingId + 3).Value = "X"; } if (leaguer.RatingId == AppLeaguerRating.Done.ToInt()) { worksheet.Cell(currentRow, leaguer.RatingId + 4).Value = "X"; } if (leaguer.RatingId == AppLeaguerRating.NotDone.ToInt()) { worksheet.Cell(currentRow, leaguer.RatingId + 7).Value = "X"; } if (leaguer.RatingId == AppLeaguerRating.Temp.ToInt()) { worksheet.Cell(currentRow, leaguer.RatingId + 11).Value = "X"; } index++; } } // total currentRow++; worksheet.GenerateRowAndStyling(currentRow, totalColumns); worksheet.Cell(currentRow, 3).FormulaA1 = $"=COUNTIF(C9:C{currentRow},'X')"; worksheet.Cell(currentRow, 4).FormulaA1 = $"=COUNTIF(D9:D{currentRow},'X')"; worksheet.Cell(currentRow, 5).FormulaA1 = $"=COUNTIF(E9:E{currentRow},'X')"; worksheet.Cell(currentRow, 6).FormulaA1 = $"=COUNTIF(F9:F{currentRow},'X')"; worksheet.Cell(currentRow, 7).FormulaA1 = $"=COUNTIF(G9:G{currentRow},'X')"; worksheet.Cell(currentRow, 8).FormulaA1 = $"=COUNTIF(H9:H{currentRow},'X')"; worksheet.Cell(currentRow, 9).FormulaA1 = $"=COUNTIF(I9:I{currentRow},'X')"; worksheet.Cell(currentRow, 10).FormulaA1 = $"=COUNTIF(J9:J{currentRow},'X')"; worksheet.Cell(currentRow, 11).FormulaA1 = $"=COUNTIF(K9:K{currentRow},'X')"; worksheet.Cell(currentRow, 12).FormulaA1 = $"=COUNTIF(L9:L{currentRow},'X')"; // signature worksheet.GenerateMergeCell(currentRow + 2, new MergeCellModel { From = 1, To = 2, Text = "NGƯỜI LẬP BIỂU", IsFontBold = true }); worksheet.GenerateMergeCell(currentRow + 2, new MergeCellModel { From = 6, To = 11, Text = "T/M ĐẢNG ỦY", IsFontBold = true }); // 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()); }