public FileDto GetUniversalPSASToExport(GetPSASParamsDto input) { //var psasMain = GetPSASMain(input); var psasMain = GetPsasMainToExport(input); var psasPrice = _ipriceAppService.GetUniversalPrice(input); var psasPayment = GetPaymentByBookCode(input); var paramSchedule = new GetPSASScheduleParamDto() { bookCode = input.bookCode, coCode = "All", unitCode = input.unitCode, unitNo = input.unitNo }; var psasSchedule = GetSchedule(paramSchedule); var dataToExport = new GetUniversalPsasDto() { psasMain = psasMain, psasPayment = psasPayment, psasPrice = psasPrice, psasSchedule = psasSchedule }; return(_printBookingProfileExcelExporter.GenerateExcelBookingProfile(dataToExport)); }
public FileDto GenerateExcelBookingProfile(GetUniversalPsasDto dataPsasDto) { string templateDocument = @"D:\sampah\Book1.xlsx"; string template = _hostingEnvironment.ContentRootPath + @"\wwwroot\Assets\Upload\PSASBookingProfile\BookingProfileTemplate.xlsx"; //string outputDocument = @"D:\sampah\Booking Profile Template hasil 1.xls"; //FileInfo template = new FileInfo(@"D:\sampah\Book1.xlsx"); //FileInfo output = new FileInfo(@"D:\sampah\Booking Profile Template hasil2.xls"); //using (ExcelPackage package = new ExcelPackage(output, template)) //{ // ExcelWorksheet sheet = package.Workbook.Worksheets["Sheet1"]; // sheet.Cells["C7"].Value = ": " + dataPsasDto.psasMain.bookCode; // package.SaveAs(new FileInfo(outputDocument)); //} return(CreateExcelPackageFromTemplate( "Booking Profile - " + dataPsasDto.psasMain.bookCode + " - " + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx", template, excelPackage => { ExcelWorksheet sheet = excelPackage.Workbook.Worksheets["Sheet1"]; var sheetWithData = fillDataSheet(sheet, dataPsasDto); })); }
private ExcelWorksheet fillDataSheet(ExcelWorksheet sheet, GetUniversalPsasDto dataPsasDto) { sheet.Cells["A4"].Value = dataPsasDto.psasMain.projectName + " " + dataPsasDto.psasMain.clusterName + " " + dataPsasDto.psasMain.unitName + " " + dataPsasDto.psasMain.unitNo; sheet.Cells["C7"].Value = ": " + dataPsasDto.psasMain.bookCode; sheet.Cells["C8"].Value = ": " + dataPsasDto.psasMain.bookDate; sheet.Cells["C9"].Value = ": " + dataPsasDto.psasMain.psCode + " / " + dataPsasDto.psasMain.name; sheet.Cells["C10"].Value = ": " + dataPsasDto.psasMain.NPWP; sheet.Cells["C11"].Value = ": " + dataPsasDto.psasMain.memberID + " / " + dataPsasDto.psasMain.memberName; sheet.Cells["C12"].Value = ": " + dataPsasDto.psasMain.membershipType; sheet.Cells["C13"].Value = ": " + dataPsasDto.psasMain.termNo; sheet.Cells["C14"].Value = ": " + dataPsasDto.psasMain.bankName; sheet.Cells["C15"].Value = ": " + dataPsasDto.psasMain.cn; sheet.Cells["C16"].Value = ": " + dataPsasDto.psasMain.sadStatus; sheet.Cells["I7"].Value = ": " + dataPsasDto.psasMain.projectName; sheet.Cells["I8"].Value = ": " + dataPsasDto.psasMain.categoryName; sheet.Cells["I9"].Value = ": " + dataPsasDto.psasMain.clusterName; sheet.Cells["I10"].Value = ": " + dataPsasDto.psasMain.productName; sheet.Cells["I11"].Value = ": " + dataPsasDto.psasMain.detailName; sheet.Cells["I12"].Value = ": " + dataPsasDto.psasMain.ppjb; sheet.Cells["I13"].Value = ": " + dataPsasDto.psasMain.kpu; sheet.Cells["I14"].Value = ": " + dataPsasDto.psasMain.pppu; sheet.Cells["F20"].Value = dataPsasDto.psasPrice.PSASPrice.area.bangunan; sheet.Cells["F21"].Value = dataPsasDto.psasPrice.PSASPrice.grossPrice.bangunan; sheet.Cells["E22"].Value = dataPsasDto.psasPrice.PSASPrice.discount.discount; sheet.Cells["F22"].Value = "(" + dataPsasDto.psasPrice.PSASPrice.discount.bangunan + ")"; sheet.Cells["F23"].Value = dataPsasDto.psasPrice.PSASPrice.netPrice.bangunan; sheet.Cells["K20"].Formula = "F20+H20+J20"; sheet.Cells["K21"].Formula = "F21+H21+J21"; sheet.Cells["K22"].Formula = "F22+H22+J22"; sheet.Cells["K23"].Formula = "F23+H23+J23"; var isFirst = true; var current = 0; var cellRow = 25; foreach (var addDisc in dataPsasDto.psasPrice.PSASPrice.discountA) { current++; if (isFirst) { sheet.Cells["E24"].Value = addDisc.discount; sheet.Cells["F24"].Value = "(" + addDisc.bangunan + ")"; sheet.Cells["G24"].Value = 0; sheet.Cells["H24"].Value = "(" + 0 + ")"; sheet.Cells["I24"].Value = 0; sheet.Cells["J24"].Value = "(" + 0 + ")"; if (current == dataPsasDto.psasPrice.PSASPrice.discountA.Count) { sheet.Cells["K24"].Formula = "F24+H24+J24"; } isFirst = false; } else { if (current == dataPsasDto.psasPrice.PSASPrice.discountA.Count) { sheet.InsertRow(cellRow, 2); sheet.Cells["F" + cellRow].Formula = "F" + (cellRow - 2) + "+F" + (cellRow - 1); sheet.Cells["H" + cellRow].Formula = "H" + (cellRow - 2) + "+H" + (cellRow - 1); sheet.Cells["J" + cellRow].Formula = "J" + (cellRow - 2) + "+J" + (cellRow - 1); sheet.Cells["E" + (cellRow + 1)].Value = addDisc.discount; sheet.Cells["F" + (cellRow + 1)].Value = "(" + addDisc.bangunan + ")"; sheet.Cells["G" + (cellRow + 1)].Value = 0; sheet.Cells["H" + (cellRow + 1)].Value = "(" + 0 + ")"; sheet.Cells["I" + (cellRow + 1)].Value = 0; sheet.Cells["J" + (cellRow + 1)].Value = "(" + 0 + ")"; sheet.Cells["K" + (cellRow + 1)].Formula = "F" + (cellRow + 1) + "+H" + (cellRow + 1) + "+J" + (cellRow + 1); sheet.Cells["E" + (cellRow + 1) + ":K" + (cellRow + 1)].Style.Border.Bottom.Style = ExcelBorderStyle.Thin; cellRow = cellRow + 2; } else { //todo insert row sheet.InsertRow(cellRow, 2); sheet.Cells["E" + (cellRow + 1)].Value = addDisc.discount; sheet.Cells["F" + (cellRow + 1)].Value = "(" + addDisc.bangunan + ")"; sheet.Cells["F" + (cellRow + 2)].Formula = "F" + (cellRow - 1) + "+F" + (cellRow + 1); cellRow = cellRow + 2; } } } sheet.Cells["F" + cellRow].Value = dataPsasDto.psasPrice.PSASPrice.netNetPrice.bangunan; sheet.Cells["F" + (cellRow + 1)].Value = 0; sheet.Cells["F" + (cellRow + 2)].Formula = "F" + (cellRow) + "+F" + (cellRow + 1); sheet.Cells["F" + (cellRow + 3)].Value = dataPsasDto.psasPrice.PSASPrice.VATPrice.bangunan; sheet.Cells["F" + (cellRow + 4)].Value = dataPsasDto.psasPrice.PSASPrice.interest.bangunan; sheet.Cells["F" + (cellRow + 5)].Formula = "F" + (cellRow + 2) + "+F" + (cellRow + 3); sheet.Cells["K" + cellRow].Formula = "F" + (cellRow) + "+H" + (cellRow) + "+J" + (cellRow); sheet.Cells["K" + (cellRow + 1)].Formula = "F" + (cellRow + 1) + "+H" + (cellRow + 1) + "+J" + (cellRow + 1); sheet.Cells["K" + (cellRow + 2)].Formula = "F" + (cellRow + 2) + "+H" + (cellRow + 2) + "+J" + (cellRow + 2); sheet.Cells["K" + (cellRow + 3)].Formula = "F" + (cellRow + 3) + "+H" + (cellRow + 3) + "+J" + (cellRow + 3); sheet.Cells["K" + (cellRow + 4)].Formula = "F" + (cellRow + 4) + "+H" + (cellRow + 4) + "+J" + (cellRow + 4); sheet.Cells["K" + (cellRow + 5)].Formula = "F" + (cellRow + 5) + "+H" + (cellRow + 5) + "+J" + (cellRow + 5); sheet.Cells["E20:E" + (cellRow + 5)].Style.Numberformat.Format = "0%"; sheet.Cells["G20:G" + (cellRow + 5)].Style.Numberformat.Format = "0%"; sheet.Cells["I20:I" + (cellRow + 5)].Style.Numberformat.Format = "0%"; sheet.Cells["F21:F" + (cellRow + 5)].Style.Numberformat.Format = "#,##0.00_);(#,##0.00)"; sheet.Cells["H21:H" + (cellRow + 5)].Style.Numberformat.Format = "#,##0.00"; sheet.Cells["J21:J" + (cellRow + 5)].Style.Numberformat.Format = "#,##0.00"; sheet.Cells["K21:K" + (cellRow + 5)].Style.Numberformat.Format = "#,##0.00"; sheet.Cells["F21:F" + (cellRow + 5)].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; sheet.Cells["H21:H" + (cellRow + 5)].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; sheet.Cells["J21:J" + (cellRow + 5)].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; sheet.Cells["K21:K" + (cellRow + 5)].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; var paymentStart = cellRow + 8; var paymentCount = 0; foreach (var payment in dataPsasDto.psasPayment) { paymentCount++; sheet.InsertRow(paymentStart + paymentCount, 1); sheet.Cells["A" + (paymentStart + paymentCount)].Value = paymentCount; sheet.Cells["B" + (paymentStart + paymentCount)].Value = payment.clearDate; sheet.Cells["C" + (paymentStart + paymentCount)].Value = payment.PMTDate; sheet.Cells["D" + (paymentStart + paymentCount)].Value = payment.transNo; sheet.Cells["F" + (paymentStart + paymentCount)].Value = payment.type.payFor; sheet.Cells["G" + (paymentStart + paymentCount)].Value = payment.type.payType; sheet.Cells["H" + (paymentStart + paymentCount)].Value = payment.type.otherType; sheet.Cells["I" + (paymentStart + paymentCount)].Value = payment.netAmount; sheet.Cells["J" + (paymentStart + paymentCount)].Value = payment.vatAmt; sheet.Cells["K" + (paymentStart + paymentCount)].Value = payment.netAmount + payment.vatAmt; sheet.Cells["L" + (paymentStart + paymentCount)].Value = payment.remarks; sheet.Cells["M" + (paymentStart + paymentCount)].Value = payment.taxFP; } sheet.Cells["B" + (paymentStart + 1) + ":C" + (paymentStart + paymentCount)].Style.Numberformat.Format = "dd/MM/yyyy"; sheet.Cells["I" + (paymentStart + 1) + ":I" + (paymentStart + paymentCount)].Style.Numberformat.Format = "#,##0.00"; sheet.Cells["J" + (paymentStart + 1) + ":J" + (paymentStart + paymentCount)].Style.Numberformat.Format = "#,##0.00"; sheet.Cells["K" + (paymentStart + 1) + ":K" + (paymentStart + paymentCount)].Style.Numberformat.Format = "#,##0.00"; var scheduleStart = paymentStart + paymentCount + 3; var scheduleCount = 0; foreach (var schedule in dataPsasDto.psasSchedule) { scheduleCount++; sheet.InsertRow(scheduleStart + scheduleCount, 1); sheet.Cells["A" + (scheduleStart + scheduleCount)].Value = scheduleCount; sheet.Cells["B" + (scheduleStart + scheduleCount)].Value = schedule.dueDate; sheet.Cells["C" + (scheduleStart + scheduleCount)].Value = schedule.allocCode; sheet.Cells["E" + (scheduleStart + scheduleCount)].Value = schedule.netAmount; sheet.Cells["F" + (scheduleStart + scheduleCount)].Value = schedule.netOutstanding; sheet.Cells["G" + (scheduleStart + scheduleCount)].Value = schedule.VATAmount; sheet.Cells["H" + (scheduleStart + scheduleCount)].Value = schedule.VATOutstanding; sheet.Cells["I" + (scheduleStart + scheduleCount)].Value = schedule.penaltyAge; sheet.Cells["J" + (scheduleStart + scheduleCount)].Value = schedule.penaltyAmount; sheet.Cells["K" + (scheduleStart + scheduleCount)].Value = schedule.totalAmount; sheet.Cells["L" + (scheduleStart + scheduleCount)].Value = schedule.totalOutstanding; } sheet.Cells["B" + (scheduleStart + 1) + ":B" + (scheduleStart + scheduleCount)].Style.Numberformat.Format = "dd/MM/yyyy"; sheet.Cells["E" + (scheduleStart + 1) + ":E" + (scheduleStart + scheduleCount)].Style.Numberformat.Format = "#,##0.00"; sheet.Cells["F" + (scheduleStart + 1) + ":F" + (scheduleStart + scheduleCount)].Style.Numberformat.Format = "#,##0.00"; sheet.Cells["G" + (scheduleStart + 1) + ":G" + (scheduleStart + scheduleCount)].Style.Numberformat.Format = "#,##0.00"; sheet.Cells["H" + (scheduleStart + 1) + ":H" + (scheduleStart + scheduleCount)].Style.Numberformat.Format = "#,##0.00"; sheet.Cells["J" + (scheduleStart + 1) + ":J" + (scheduleStart + scheduleCount)].Style.Numberformat.Format = "#,##0.00"; sheet.Cells["K" + (scheduleStart + 1) + ":K" + (scheduleStart + scheduleCount)].Style.Numberformat.Format = "#,##0.00"; sheet.Cells["L" + (scheduleStart + 1) + ":L" + (scheduleStart + scheduleCount)].Style.Numberformat.Format = "#,##0.00"; var summaryStart = scheduleStart + scheduleCount + 3; //total schedule sheet.Cells["E" + summaryStart].Formula = "SUM(E" + (scheduleStart + 1) + ":E" + (scheduleStart + scheduleCount) + ")"; sheet.Cells["F" + summaryStart].Formula = "SUM(G" + (scheduleStart + 1) + ":G" + (scheduleStart + scheduleCount) + ")"; sheet.Cells["G" + summaryStart].Formula = "SUM(K" + (scheduleStart + 1) + ":K" + (scheduleStart + scheduleCount) + ")"; sheet.Cells["H" + summaryStart].Formula = "SUM(J" + (scheduleStart + 1) + ":J" + (scheduleStart + scheduleCount) + ")"; sheet.Cells["I" + summaryStart].Value = 0; sheet.Cells["J" + summaryStart].Value = 0; //total payment sheet.Cells["E" + (summaryStart + 1)].Formula = "SUM(I" + (paymentStart + 1) + ":I" + (paymentStart + paymentCount) + ")"; sheet.Cells["F" + (summaryStart + 1)].Formula = "SUM(J" + (paymentStart + 1) + ":J" + (paymentStart + paymentCount) + ")"; sheet.Cells["G" + (summaryStart + 1)].Formula = "SUM(K" + (paymentStart + 1) + ":K" + (paymentStart + paymentCount) + ")"; sheet.Cells["H" + (summaryStart + 1)].Value = 0; sheet.Cells["I" + (summaryStart + 1)].Value = 0; sheet.Cells["J" + (summaryStart + 1)].Value = 0; //total outstanding sheet.Cells["E" + (summaryStart + 2)].Formula = "SUM(F" + (scheduleStart + 1) + ":F" + (scheduleStart + paymentCount) + ")"; sheet.Cells["F" + (summaryStart + 2)].Formula = "SUM(H" + (scheduleStart + 1) + ":H" + (scheduleStart + paymentCount) + ")"; sheet.Cells["G" + (summaryStart + 2)].Formula = "SUM(L" + (scheduleStart + 1) + ":L" + (scheduleStart + paymentCount) + ")"; sheet.Cells["H" + (summaryStart + 2)].Value = 0; sheet.Cells["I" + (summaryStart + 2)].Value = 0; sheet.Cells["J" + (summaryStart + 2)].Value = 0; sheet.Cells["E" + summaryStart + ":J" + (summaryStart + 2)].Style.Numberformat.Format = "#,##0.00"; return(sheet); }