ExpertXls Excel Library Blazor demo · .NET 10 and .NET 11

← All demos

C# code of the Invoice demo

Features/InvoiceDemo.cs
using System;
using System.Collections.Generic;
using System.Drawing;
using System.IO;
using ExpertXls.ExcelLib;

namespace ExcelLibraryDemos
{
    /// <summary>The customer of an invoice.</summary>
    [Serializable]
    public class InvoiceCustomer
    {
        public string Name { get; set; }
        public string Address1 { get; set; }
        public string Address2 { get; set; }
        public string Phone { get; set; }
        public string Email { get; set; }
    }

    /// <summary>An item of an invoice.</summary>
    [Serializable]
    public class InvoiceItem
    {
        public InvoiceItem(string productCode, string productName, string productDescription, double unitPrice, int quantity)
        {
            ProductCode = productCode;
            ProductName = productName;
            ProductDescription = productDescription;
            UnitPrice = unitPrice;
            Quantity = quantity;
        }

        public string ProductCode { get; set; }
        public string ProductName { get; set; }
        public string ProductDescription { get; set; }
        public double UnitPrice { get; set; }
        public int Quantity { get; set; }
        public double ItemPrice { get { return UnitPrice * Quantity; } }
    }

    /// <summary>An invoice: a customer and a list of items.</summary>
    [Serializable]
    public class Invoice
    {
        public Invoice()
        {
            Customer = new InvoiceCustomer();
            Items = new List<InvoiceItem>();
            InvoiceDate = DateTime.Today;
        }

        public string Number { get; set; }
        public DateTime InvoiceDate { get; set; }
        public InvoiceCustomer Customer { get; set; }
        public List<InvoiceItem> Items { get; private set; }

        /// <summary>An invoice with sample data, used by the samples that do not have a form to fill.</summary>
        public static Invoice CreateSample()
        {
            Invoice invoice = new Invoice();
            invoice.Number = "30462358";
            invoice.Customer.Name = "My Company Inc.";
            invoice.Customer.Address1 = "12345 My Street";
            invoice.Customer.Address2 = "Anytown, CA 00000";
            invoice.Customer.Phone = "Tel: (310) 555-0100";
            invoice.Customer.Email = "customer@example.com";
            invoice.Items.Add(new InvoiceItem("101", "HTML to PDF Converter", "Developer license", 550, 1));
            invoice.Items.Add(new InvoiceItem("205", "PDF Merge", "Developer license", 150, 2));
            invoice.Items.Add(new InvoiceItem("310", "Excel Library", "Developer license", 350, 1));
            invoice.Items.Add(new InvoiceItem("402", "Support", "One year of priority support", 99.5, 3));
            return invoice;
        }
    }

    /// <summary>
    /// Creates an invoice workbook from data: customer details, a table of items whose prices and total are Excel
    /// formulas, an e-mail hyperlink, the company logo and a 3D pie chart of the invoice structure.
    /// </summary>
    public static class InvoiceDemo
    {
        /// <summary>The name of the generated file, without extension.</summary>
        public const string OutputName = "Invoice";

        /// <summary>
        /// Creates the invoice workbook in the given format and saves it to the output stream.
        /// </summary>
        /// <param name="invoice">The invoice data.</param>
        /// <param name="format">Excel 97-2003 (.xls) or Excel 2007+ (.xlsx).</param>
        /// <param name="inputFolder">The folder that contains the Images folder of the sample.</param>
        /// <param name="output">The stream that receives the workbook.</param>
        public static void CreateWorkbook(Invoice invoice, ExcelWorkbookFormat format, string inputFolder, Stream output)
        {
            string imagesFolder = Path.Combine(inputFolder, "Images");

            ExcelWorkbook workbook = new ExcelWorkbook(format);
            // workbook.LicenseKey = "your license key";
            workbook.DocumentProperties.Title = "Invoice " + invoice.Number;
            workbook.DocumentProperties.Subject = "Invoice created from data";
            workbook.DocumentProperties.Company = "My Company Inc.";
            workbook.DocumentProperties.Comments = "Created with ExpertXls Excel Library for .NET";

            Image logo = Image.FromFile(Path.Combine(imagesFolder, "logo.jpg"));
            Image companyLogo = Image.FromFile(Path.Combine(imagesFolder, "company_logo.png"));
            try
            {
                ExcelCellStyle titleStyle = DemoLayout.AddTitleStyle(workbook);

                ExcelCellStyle infoStyle = workbook.Styles.AddStyle("InvoiceInfoStyle");
                infoStyle.Font.Size = 12;
                infoStyle.Font.Bold = true;
                infoStyle.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;

                ExcelCellStyle itemStyle = workbook.Styles.AddStyle("InvoiceItemStyle");
                itemStyle.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Center;
                itemStyle.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
                itemStyle.Borders.LineStyle = ExcelCellLineStyle.Thin;
                itemStyle.Borders.Color = Color.Green;
                if (format == ExcelWorkbookFormat.Xlsx_2007)
                {
                    itemStyle.Fill.FillType = ExcelCellFillType.GradientFill;
                    itemStyle.Fill.GradientFillOptions.Color1 = Color.LightGreen;
                    itemStyle.Fill.GradientFillOptions.Color2 = Color.White;
                    itemStyle.Fill.GradientFillOptions.Direction = ExcelGradientDirection.DiagonlDown;
                }
                else
                {
                    itemStyle.Fill.FillType = ExcelCellFillType.PatternFill;
                    itemStyle.Fill.PatternFillOptions.PatternType = ExcelCellFillPatternType.Gray_6_25_Percent;
                    itemStyle.Fill.PatternFillOptions.BackColor = Color.White;
                    itemStyle.Fill.PatternFillOptions.PatternColor = Color.Green;
                }

                ExcelWorksheet worksheet = workbook.Worksheets[0];
                worksheet.Name = "Invoice";
                DemoLayout.SetupPage(worksheet, logo, ExcelPageOrientation.Portrait);
                DemoLayout.WriteTitle(worksheet, "A2:G3", "Invoice", titleStyle);

                // the company logo, with its top left corner in A5
                ExcelPicture companyLogoPicture = worksheet.Pictures.AddPicture(1, 5, companyLogo);

                // CUSTOMER AND INVOICE DETAILS
                int row = companyLogoPicture.BottomRowIndex + 2;
                string[] customerLines = { invoice.Customer.Name, invoice.Customer.Address1, invoice.Customer.Address2,
                    invoice.Customer.Phone };
                foreach (string line in customerLines)
                {
                    if (String.IsNullOrEmpty(line))
                        continue;
                    worksheet[row, 1, row, 3].Merge();
                    worksheet[row, 1].Value = line;
                    worksheet[row, 1, row, 3].Style = infoStyle;
                    row++;
                }
                if (!String.IsNullOrEmpty(invoice.Customer.Email))
                {
                    ExcelRange emailRange = worksheet[row, 1, row, 3];
                    emailRange.Merge();
                    ExcelHyperlink emailLink = worksheet.Hyperlinks.AddHyperlink(ExcelHyperlinkType.Url, emailRange,
                        "mailto:" + invoice.Customer.Email);
                    emailLink.Text = invoice.Customer.Email;
                    emailLink.ToolTip = "Send an e-mail to " + invoice.Customer.Email;
                    row++;
                }

                int detailsRow = companyLogoPicture.BottomRowIndex + 2;
                worksheet[detailsRow, 5].Value = "Invoice date:";
                worksheet[detailsRow, 6].Value = invoice.InvoiceDate;
                worksheet[detailsRow, 6].Style.Number.NumberFormatString = "mmmm d, yyyy";
                worksheet[detailsRow + 1, 5].Value = "Invoice number:";
                worksheet[detailsRow + 1, 6].Text = invoice.Number;
                worksheet[detailsRow, 5, detailsRow + 1, 6].Style.Font.Bold = true;
                worksheet[detailsRow, 6, detailsRow + 1, 6].Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Right;

                // ITEMS TABLE
                int headerRow = row + 2;
                string[] headers = { "Code", "Product", "Description", "Unit price", "Quantity", "Price" };
                for (int c = 0; c < headers.Length; c++)
                    worksheet[headerRow, 2 + c].Text = headers[c];
                ExcelRange headerRange = worksheet[headerRow, 2, headerRow, 7];
                headerRange.RowHeightInPoints = 21;
                headerRange.Style.Font.Bold = true;
                headerRange.Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Center;
                headerRange.Style.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
                headerRange.Style.Fill.FillType = ExcelCellFillType.SolidFill;
                headerRange.Style.Fill.SolidFillOptions.BackColor = Color.FromArgb(255, 204, 153);

                int firstItemRow = headerRow + 1;
                int lastItemRow = headerRow + invoice.Items.Count;
                for (int i = 0; i < invoice.Items.Count; i++)
                {
                    InvoiceItem item = invoice.Items[i];
                    int itemRow = firstItemRow + i;
                    worksheet[itemRow, 2].Text = item.ProductCode;
                    worksheet[itemRow, 3].Text = item.ProductName;
                    worksheet[itemRow, 4].Text = item.ProductDescription;
                    worksheet[itemRow, 5].Value = item.UnitPrice;
                    worksheet[itemRow, 6].Value = item.Quantity;
                    // the price is a formula, so that the workbook recalculates when the quantity changes
                    worksheet[itemRow, 7].Formula = String.Format("=E{0}*F{0}", itemRow);
                    worksheet[itemRow, 2, itemRow, 7].Style = itemStyle;
                }
                if (invoice.Items.Count > 0)
                {
                    worksheet[firstItemRow, 5, lastItemRow, 5].Style.Number.NumberFormatString = "#,##0.00";
                    worksheet[firstItemRow, 7, lastItemRow, 7].Style.Number.NumberFormatString = "#,##0.00";
                }
                worksheet[headerRow, 2, Math.Max(lastItemRow, headerRow), 7].AutofitColumns();

                // TOTAL
                int totalRow = Math.Max(lastItemRow, headerRow) + 2;
                worksheet[totalRow, 1, totalRow, 6].Merge();
                worksheet[totalRow, 1].Text = "Total:";
                worksheet[totalRow, 1, totalRow, 6].Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Right;
                worksheet[totalRow, 7].Formula = invoice.Items.Count > 0
                    ? String.Format("=SUM(G{0}:G{1})", firstItemRow, lastItemRow) : "=0";
                worksheet[totalRow, 7].Style.Number.NumberFormatString = "#,##0.00";
                ExcelRange totalRange = worksheet[totalRow, 1, totalRow, 7];
                totalRange.Style.Font.Bold = true;
                totalRange.Style.Font.Size = 12;
                totalRange.Style.Font.Color = Color.DarkBlue;
                // calculate the formulas, so that the values are in the file for any reader
                workbook.UpdateFormulaResult();

                // A 3D PIE CHART OF THE INVOICE STRUCTURE
                if (invoice.Items.Count > 0)
                {
                    ExcelChart chart = worksheet.Charts.AddChart(ExcelChartType.Pie3D, 1, totalRow + 3, 6, totalRow + 23);
                    chart.Title.Text = "Invoice Structure by Items";
                    chart.Title.Font.Size = 12;
                    chart.Title.Font.Color = Color.DarkBlue;
                    ExcelChartSeries series = chart.Series.AddSeries("Invoice Items");
                    series.ChartType = ExcelChartType.Pie3D;
                    series.CategoryNamesRange = worksheet[firstItemRow, 3, lastItemRow, 3];
                    series.ValuesRange = worksheet[firstItemRow, 7, lastItemRow, 7];
                    series.DataPoints.All.Label.ContainsValue = true;
                    chart.Rotation3D.XRotation = 30;
                    chart.Rotation3D.YRotation = 25;
                    chart.Legend.Position = ExcelChartLegendPosition.Right;
                    chart.Legend.Font.Size = 8;
                }

                workbook.Save(output);
            }
            finally
            {
                workbook.Close();
                logo.Dispose();
                companyLogo.Dispose();
            }
        }
    }
}

Create, edit and read Excel workbooks in a Blazor application: the workbooks are created on the server and downloaded by the browser. The code of each workbook is in the Features folder.