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