/// <summary> /// Gets Excel file for invoice using Syncfusion.XlsIO /// </summary> /// <param name="invoiceId">invoice Id for which file should be generated</param> /// <returns>Excel file for invoice</returns> public FileStreamResult GetInvoiceExcel(string invoiceId) { var invoice = InvoiceManager.GetInvoiceViewModelById(invoiceId); invoice.InvoiceLines = InvoiceManager.GetInvoiceLineViewModels(invoiceId).OrderBy(x => x.LineNumber); //Step 1 : Instantiate the spreadsheet creation engine. ExcelEngine excelEngine = new ExcelEngine(); //Step 2 : Instantiate the excel application object. IApplication application = excelEngine.Excel; // Creating new workbook IWorkbook workbook = application.Workbooks.Create(1); IWorksheet sheet = workbook.Worksheets[0]; sheet.Name = $"Faktura_{invoice.InvoiceNumber.Replace("/", "_")}"; sheet.Name = sheet.Name.Length > 31 ? sheet.Name.Substring(0, 31) : sheet.Name; #region Generate Excel sheet.Range["A2"].ColumnWidth = 15; sheet.Range["B2"].ColumnWidth = 15; sheet.Range["C2"].ColumnWidth = 25; sheet.Range["D2"].ColumnWidth = 15; sheet.Range["E2"].ColumnWidth = 15; sheet.Range["F2"].ColumnWidth = 15; sheet.Range["G2"].ColumnWidth = 15; sheet.Range["H2"].ColumnWidth = 15; sheet.Range["I2"].ColumnWidth = 15; sheet.Range["J2"].ColumnWidth = 15; sheet.Range["K2"].ColumnWidth = 15; sheet.Range["L2"].ColumnWidth = 15; sheet.Range["M2"].ColumnWidth = 15; sheet.Range["N2"].ColumnWidth = 15; sheet.Range["O2"].ColumnWidth = 15; sheet.Range["A2:O2"].Merge(true); sheet.Range["A2:O2"].BorderAround(ExcelLineStyle.Thick, Color.FromArgb(47, 117, 181)); sheet.Range["A2:O2"].CellStyle.Color = Color.FromArgb(242, 242, 242); //Invoice Header sheet.Range["A2"].Text = invoice.InvoiceNumber; sheet.Range["A2"].CellStyle.Font.FontName = "Verdana"; sheet.Range["A2"].CellStyle.Font.Bold = true; sheet.Range["A2"].CellStyle.Font.Size = 28; sheet.Range["A2"].CellStyle.Font.RGBColor = Color.FromArgb(0, 0, 112, 192); sheet.Range["A2"].HorizontalAlignment = ExcelHAlign.HAlignCenter; sheet.Range["A5:B5"].Merge(true); sheet.Range["A5"].Text = "Numer referencyjny"; sheet.Range["C5"].Text = invoice.ReferenceNumber; sheet.Range["A6:B6"].Merge(true); sheet.Range["A6"].Text = "Klient"; sheet.Range["C6"].Text = invoice.Customer.Name; sheet.Range["A7:B7"].Merge(true); sheet.Range["A7"].Text = "Domyślny budżet"; sheet.Range["C7"].Text = invoice.Budget.BudgetNumber; sheet.Range["A8:B8"].Merge(true); sheet.Range["A8"].Text = "Domyślna waluta"; sheet.Range["C8"].Text = invoice.Currency; sheet.Range["A9:B9"].Merge(true); sheet.Range["A9"].Text = "Metoda płatności"; sheet.Range["C9"].Text = invoice.PaymentMethod; sheet.Range["C11:C13"].NumberFormat = "yyyy/mm/dd"; sheet.Range["A11:B11"].Merge(true); sheet.Range["A11"].Text = "Data płatności"; sheet.Range["C11"].DateTime = invoice.PaymentDueDate; sheet.Range["A12:B12"].Merge(true); sheet.Range["A12"].Text = "Data wydania"; sheet.Range["C12"].DateTime = invoice.IssueDate; sheet.Range["A13:B13"].Merge(true); sheet.Range["A13"].Text = "Data przyjęcia"; sheet.Range["C13"].DateTime = invoice.ReceiveDate; sheet.Range["A5:C13"].CellStyle.Font.FontName = "Verdana"; sheet.Range["A5:C13"].CellStyle.Font.Bold = true; sheet.Range["A5:C13"].CellStyle.Font.Size = 11; sheet.Range["A5:C13"].CellStyle.Font.RGBColor = Color.FromArgb(0, 128, 128, 128); sheet.Range["A5:C13"].HorizontalAlignment = ExcelHAlign.HAlignLeft; sheet.Range["B5:C13"].CellStyle.Font.RGBColor = Color.FromArgb(0, 174, 170, 170); sheet.Range["B5:C13"].HorizontalAlignment = ExcelHAlign.HAlignRight; //Invoice lines header var invoiceLineHdgIndex = 15; sheet.Range[$"A{invoiceLineHdgIndex}"].Text = "Numer linii"; sheet.Range[$"B{invoiceLineHdgIndex}"].Text = "Kod pozycji"; sheet.Range[$"C{invoiceLineHdgIndex}"].Text = "Opis"; sheet.Range[$"D{invoiceLineHdgIndex}"].Text = "Ilość"; sheet.Range[$"E{invoiceLineHdgIndex}"].Text = "Cena jedn."; sheet.Range[$"F{invoiceLineHdgIndex}"].Text = "Waluta"; sheet.Range[$"G{invoiceLineHdgIndex}"].Text = "Kurs waluty"; sheet.Range[$"H{invoiceLineHdgIndex}"].Text = "Stawka Vat(%)"; sheet.Range[$"I{invoiceLineHdgIndex}"].Text = "Netto (waluta)"; sheet.Range[$"J{invoiceLineHdgIndex}"].Text = "VAT (waluta)"; sheet.Range[$"K{invoiceLineHdgIndex}"].Text = "Brutto (waluta)"; sheet.Range[$"L{invoiceLineHdgIndex}"].Text = "Netto (PLN)"; sheet.Range[$"M{invoiceLineHdgIndex}"].Text = "VAT (PLN)"; sheet.Range[$"N{invoiceLineHdgIndex}"].Text = "Brutto (PLN)"; sheet.Range[$"O{invoiceLineHdgIndex}"].Text = "Kod budżetu"; sheet.Range[$"A{invoiceLineHdgIndex}"].RowHeight = 30; sheet.Range[$"A{invoiceLineHdgIndex}:O{invoiceLineHdgIndex}"].WrapText = true; sheet.Range[$"A{invoiceLineHdgIndex}:O{invoiceLineHdgIndex}"].BorderInside(ExcelLineStyle.Thin, ExcelKnownColors.White); sheet.Range[$"A{invoiceLineHdgIndex}:O{invoiceLineHdgIndex}"].CellStyle.Font.Bold = true; sheet.Range[$"A{invoiceLineHdgIndex}:O{invoiceLineHdgIndex}"].CellStyle.Font.Color = ExcelKnownColors.White; sheet.Range[$"A{invoiceLineHdgIndex}:O{invoiceLineHdgIndex}"].CellStyle.Color = Color.FromArgb(47, 117, 181); sheet.Range[$"A{invoiceLineHdgIndex}:O{invoiceLineHdgIndex}"].HorizontalAlignment = ExcelHAlign.HAlignCenter; sheet.Range[$"A{invoiceLineHdgIndex}:O{invoiceLineHdgIndex}"].VerticalAlignment = ExcelVAlign.VAlignCenter; //Invoice lines details int i = 1; foreach (var line in invoice.InvoiceLines) { if (i % 2 == 0) { sheet.Range[$"A{invoiceLineHdgIndex + i}:O{invoiceLineHdgIndex + i}"].CellStyle.Color = Color.FromArgb(230, 230, 230); } sheet.Range[$"A{invoiceLineHdgIndex + i}"].Number = line.LineNumber; sheet.Range[$"B{invoiceLineHdgIndex + i}"].Text = line.ItemName; sheet.Range[$"C{invoiceLineHdgIndex + i}"].Text = line.Description; sheet.Range[$"D{invoiceLineHdgIndex + i}"].Number = line.Quantity; sheet.Range[$"E{invoiceLineHdgIndex + i}"].Number = (double)line.Price; sheet.Range[$"E{invoiceLineHdgIndex + i}"].NumberFormat = "#,###0.00"; sheet.Range[$"F{invoiceLineHdgIndex + i}"].Text = line.Currency; sheet.Range[$"G{invoiceLineHdgIndex + i}"].Number = (double)line.CurrencyRate; sheet.Range[$"G{invoiceLineHdgIndex + i}"].NumberFormat = "#,###0.0000"; sheet.Range[$"H{invoiceLineHdgIndex + i}"].Number = (double)line.TaxRate; sheet.Range[$"I{invoiceLineHdgIndex + i}"].Number = (double)line.Netto; sheet.Range[$"J{invoiceLineHdgIndex + i}"].Number = (double)line.Tax; sheet.Range[$"K{invoiceLineHdgIndex + i}"].Number = (double)line.Gross; sheet.Range[$"L{invoiceLineHdgIndex + i}"].Number = (double)line.BaseNetto; sheet.Range[$"M{invoiceLineHdgIndex + i}"].Number = (double)line.BaseTax; sheet.Range[$"N{invoiceLineHdgIndex + i}"].Number = (double)line.BaseGross; sheet.Range[$"I{invoiceLineHdgIndex + i}:N{15 + i}"].NumberFormat = "#,###0.00"; sheet.Range[$"O{invoiceLineHdgIndex + i}"].Text = line.Budget.BudgetNumber; i++; } sheet.Range[$"A{invoiceLineHdgIndex + i - 1}:O{invoiceLineHdgIndex + i - 1}"].CellStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; sheet.Range[$"A15:O{invoiceLineHdgIndex + i - 1}"].CellStyle.Font.FontName = "Verdana"; sheet.Range[$"A15:O{invoiceLineHdgIndex + i - 1}"].CellStyle.Font.Size = 11; sheet.InsertColumn(1, 1, ExcelInsertOptions.FormatDefault); sheet.IsGridLinesVisible = false; #endregion workbook.Version = ExcelVersion.Excel2013; var contentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; var fileName = $"{invoice.InvoiceNumber.Replace("/", "_")}_{DateHelper.GetCurrentDatetime():yyyyMMdd}.xlsx"; MemoryStream ms = new MemoryStream(); workbook.SaveAs(ms); ms.Position = 0; FileStreamResult fileStreamResult = new FileStreamResult(ms, contentType); fileStreamResult.FileDownloadName = fileName; return(fileStreamResult); }
/// <summary> /// Gets PDF file for invoice using Syncfusion.Pdf /// </summary> /// <param name="invoiceId">invoice Id for which file should be generated</param> /// <returns>PDF file for invoice</returns> public FileStreamResult GetInvoicePdf(string invoiceId) { //Declaring colors var black = Color.FromArgb(255, 0, 0, 0); var white = Color.FromArgb(255, 255, 255, 255); var lightGray = Color.FromArgb(255, 220, 220, 220); var model = InvoiceManager.GetInvoiceViewModelById(invoiceId); model.InvoiceLines = InvoiceManager.GetInvoiceLineViewModels(invoiceId); var invoiceNo = model.InvoiceNumber; var invoiceHdrText = $"Faktura VAT: {invoiceNo}"; var customer = model.Customer; var invoiceLines = model.InvoiceLines; var lightGrayBrush = new PdfSolidBrush(lightGray); //Creating new PDF document instance PdfDocument document = new PdfDocument(); //Setting margin document.PageSettings.Margins.All = 20; //Adding a new page PdfPage page = document.Pages.Add(); PdfGraphics g = page.Graphics; //Load the custom font as stream Stream fontStream = System.IO.File.OpenRead("wwwroot/fonts/Roboto-Regular.ttf"); //Create a new PDF true type font. PdfTrueTypeFont customFont8 = new PdfTrueTypeFont(fontStream, 8, PdfFontStyle.Regular); PdfTrueTypeFont customFont8Bold = new PdfTrueTypeFont(fontStream, 8, PdfFontStyle.Bold); PdfTrueTypeFont customFont10 = new PdfTrueTypeFont(fontStream, 10, PdfFontStyle.Regular); PdfTrueTypeFont customFont12 = new PdfTrueTypeFont(fontStream, 12, PdfFontStyle.Regular); PdfTrueTypeFont customFont12Bold = new PdfTrueTypeFont(fontStream, 12, PdfFontStyle.Bold); PdfTrueTypeFont customFont30 = new PdfTrueTypeFont(fontStream, 30, PdfFontStyle.Regular); //Creating font instances PdfFont headerFont = customFont30; //PdfFont standardText12 = new PdfStandardFont(PdfFontFamily.Helvetica, 12); PdfFont standardText12 = customFont12; PdfFont standardText12Bold = customFont12Bold; PdfFont standardText10 = customFont10; PdfFont standardText8 = customFont8; PdfFont standardText8Bold = customFont8Bold; //Set page size document.PageSettings.Size = PdfPageSize.A4; //Set page orientation document.PageSettings.Orientation = PdfPageOrientation.Landscape; //pen var pen = new PdfPen(Color.Black) { Width = 0.5F }; //Invoice number printing var invoiceNoRectangle = new Syncfusion.Drawing.RectangleF(180, 30, 200, 100); g.DrawRectangle(lightGrayBrush, invoiceNoRectangle); var result = BodyContent(invoiceHdrText, headerFont, black, PdfTextAlignment.Center, PdfVerticalAlignment.Middle, page, invoiceNoRectangle); //Customer data printing var customerDataHeaderRectangle = new Syncfusion.Drawing.RectangleF(10, 200, 200, 15); var customerDataContentRectangle = new Syncfusion.Drawing.RectangleF(10, 215, 200, 100); g.DrawRectangle(lightGrayBrush, customerDataHeaderRectangle); result = BodyContent("Klient", standardText12Bold, black, PdfTextAlignment.Center, PdfVerticalAlignment.Middle, page, customerDataHeaderRectangle); g.DrawRectangle(pen, new PdfSolidBrush(white), customerDataContentRectangle); var rect = new RectangleF(12, 215, 200, 25); result = BodyContent(customer.Name, standardText10, black, PdfTextAlignment.Left, PdfVerticalAlignment.Top, page, rect); rect = new RectangleF(12, result.Bounds.Bottom + 5, 200, 25); result = BodyContent($"{customer.Street}/{customer.BuildingNumber}", standardText10, black, PdfTextAlignment.Left, PdfVerticalAlignment.Top, page, rect); rect = new RectangleF(12, result.Bounds.Bottom, 200, 25); result = BodyContent($"{customer.PostalCode} {customer.City}", standardText10, black, PdfTextAlignment.Left, PdfVerticalAlignment.Top, page, rect); //Dates data printing var datesX = page.Graphics.ClientSize.Width - customerDataHeaderRectangle.Width - customerDataHeaderRectangle.X; var datesY = customerDataHeaderRectangle.Y; var datesWidth = customerDataHeaderRectangle.Width; var datesHeight = customerDataHeaderRectangle.Height; //issue date var issueDateHeaderRectangle = new Syncfusion.Drawing.RectangleF(datesX, datesY, datesWidth, datesHeight); var issueDateDataContentRectangle = new Syncfusion.Drawing.RectangleF(datesX, datesY + datesHeight, datesWidth, 20); g.DrawRectangle(lightGrayBrush, issueDateHeaderRectangle); BodyContent("Data wydania", standardText12Bold, black, PdfTextAlignment.Center, PdfVerticalAlignment.Middle, page, issueDateHeaderRectangle); g.DrawRectangle(pen, new PdfSolidBrush(white), issueDateDataContentRectangle); result = BodyContent("2018-10-04", standardText10, black, PdfTextAlignment.Center, PdfVerticalAlignment.Middle, page, issueDateDataContentRectangle); //receipt date var receiptDateHeaderRectangle = new Syncfusion.Drawing.RectangleF(datesX, result.Bounds.Bottom + 9, datesWidth, datesHeight); var receiptDateDataContentRectangle = new Syncfusion.Drawing.RectangleF(datesX, result.Bounds.Bottom + 9 + datesHeight, datesWidth, 20); g.DrawRectangle(lightGrayBrush, receiptDateHeaderRectangle); BodyContent("Data przyjęcia", standardText12Bold, black, PdfTextAlignment.Center, PdfVerticalAlignment.Middle, page, receiptDateHeaderRectangle); g.DrawRectangle(pen, new PdfSolidBrush(white), receiptDateDataContentRectangle); result = BodyContent("2018-10-04", standardText10, black, PdfTextAlignment.Center, PdfVerticalAlignment.Middle, page, receiptDateDataContentRectangle); //receipt date var paymentDateHeaderRectangle = new Syncfusion.Drawing.RectangleF(datesX, result.Bounds.Bottom + 9, datesWidth, datesHeight); var paymentDateDataContentRectangle = new Syncfusion.Drawing.RectangleF(datesX, result.Bounds.Bottom + 9 + datesHeight, datesWidth, 20); g.DrawRectangle(lightGrayBrush, paymentDateHeaderRectangle); BodyContent("Data płatności", standardText12Bold, black, PdfTextAlignment.Center, PdfVerticalAlignment.Middle, page, paymentDateHeaderRectangle); g.DrawRectangle(pen, new PdfSolidBrush(white), paymentDateDataContentRectangle); result = BodyContent("2018-10-04", standardText10, black, PdfTextAlignment.Center, PdfVerticalAlignment.Middle, page, paymentDateDataContentRectangle); //adding grid for invoice lines PdfGrid grid = new PdfGrid(); grid.Style.AllowHorizontalOverflow = false; //Set Data source for invoice lines if (invoiceLines.Count() == 0) { invoiceLines = new List <InvoiceLineViewModel>() { new InvoiceLineViewModel() }; } grid.DataSource = invoiceLines.Select(x => new { ItemName = x.ItemName, Description = x.Description, Quantity = x.Quantity, Price = x.Price.ToString("N"), TaxRate = x.TaxRate.ToString("##"), NetValue = x.BaseNetto.ToString("N"), TaxValue = x.BaseTax.ToString("N"), GrossValue = x.BaseGross.ToString("N") }); //grid styling var styleName = "GridTable4"; PdfGridBuiltinStyleSettings setting = new PdfGridBuiltinStyleSettings(); setting.ApplyStyleForHeaderRow = false; setting.ApplyStyleForBandedRows = false; setting.ApplyStyleForBandedColumns = false; setting.ApplyStyleForFirstColumn = false; setting.ApplyStyleForLastColumn = false; setting.ApplyStyleForLastRow = false; var gridHeaderStyle = new PdfGridRowStyle() { BackgroundBrush = new PdfSolidBrush(lightGray), Font = standardText8Bold }; //Set layout properties PdfLayoutFormat format = new PdfLayoutFormat(); format.Break = PdfLayoutBreakType.FitElement; format.Layout = PdfLayoutType.Paginate; PdfGridBuiltinStyle style = (PdfGridBuiltinStyle)Enum.Parse(typeof(PdfGridBuiltinStyle), styleName); grid.ApplyBuiltinStyle(style, setting); grid.Style.Font = standardText8; grid.Style.CellPadding.All = 2; grid.Style.AllowHorizontalOverflow = false; //set header texts PdfGridRow pdfGridHeader = grid.Headers[0]; pdfGridHeader.Style = gridHeaderStyle; pdfGridHeader.Cells[0].Value = "Kod pozycji"; pdfGridHeader.Cells[1].Value = "Opis"; pdfGridHeader.Cells[2].Value = "Ilość"; pdfGridHeader.Cells[3].Value = "Cena"; pdfGridHeader.Cells[4].Value = "VAT(%)"; pdfGridHeader.Cells[5].Value = "Netto"; pdfGridHeader.Cells[6].Value = "VAT"; pdfGridHeader.Cells[7].Value = "Brutto"; //grid location var gridStartLocation = new PointF(0, result.Bounds.Bottom + 50); //Draw table result = grid.Draw(page, gridStartLocation, format); var summaryByTaxRate = invoiceLines .GroupBy(x => x.TaxRate) .Select(x => new { TaxRate = x.Key.ToString("##"), NetValue = x.Sum(y => y.BaseNetto).ToString("N"), TaxValue = x.Sum(y => y.BaseTax).ToString("N"), GrossValue = x.Sum(y => y.BaseGross).ToString("N") }); //adding summary grid PdfGrid summaryGrid = new PdfGrid(); summaryGrid.Style.AllowHorizontalOverflow = false; //Set Data source summaryGrid.DataSource = summaryByTaxRate; summaryGrid.ApplyBuiltinStyle(style, setting); summaryGrid.Style.Font = standardText8; summaryGrid.Style.CellPadding.All = 2; summaryGrid.Style.AllowHorizontalOverflow = false; //set header texts PdfGridRow pdfSummaryGridHeader = summaryGrid.Headers[0]; pdfSummaryGridHeader.Style = gridHeaderStyle; pdfSummaryGridHeader.Cells[0].Value = "według stawki VAT"; pdfSummaryGridHeader.Cells[1].Value = "wartość netto"; pdfSummaryGridHeader.Cells[2].Value = "wartość VAT"; pdfSummaryGridHeader.Cells[3].Value = "wartość brutto"; summaryGrid.Columns[0].Width = 80; summaryGrid.Columns[1].Width = 60; summaryGrid.Columns[2].Width = 60; summaryGrid.Columns[3].Width = 60; //create summary total GridRow var summaryTotalNetto = invoiceLines.Sum(x => x.BaseNetto); var summaryTotalTax = invoiceLines.Sum(x => x.BaseTax); var summaryTotalGross = invoiceLines.Sum(x => x.BaseGross); var summaryTotalRow = new PdfGridRow(summaryGrid); summaryGrid.Rows.Add(summaryTotalRow); summaryTotalRow.Cells[0].Value = "Razem:"; summaryTotalRow.Cells[0].Style.Font = customFont8Bold; summaryTotalRow.Cells[1].Value = summaryTotalNetto.ToString("N"); summaryTotalRow.Cells[2].Value = summaryTotalTax.ToString("N"); summaryTotalRow.Cells[3].Value = summaryTotalGross.ToString("N"); float columnsWidth = 0; foreach (PdfGridColumn column in summaryGrid.Columns) { columnsWidth += column.Width; } //Summary grid location var summaryGridStartLocation = new PointF(page.GetClientSize().Width - columnsWidth, result.Bounds.Bottom + 20); //Draw table result = summaryGrid.Draw(page, summaryGridStartLocation, format); //Saving the PDF to the MemoryStreamcolumnsWidth MemoryStream ms = new MemoryStream(); document.Save(ms); //If the position is not set to '0' then the PDF will be empty. ms.Position = 0; var contentType = "application/pdf"; var fileName = $"{invoiceNo.Replace("/", "_")}_{DateHelper.GetCurrentDatetime():yyyyMMdd}.pdf"; //Download the PDF document in the browser. FileStreamResult fileStreamResult = new FileStreamResult(ms, contentType); fileStreamResult.FileDownloadName = fileName; return(fileStreamResult); }