C# code of the Invoice demo
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.