C# code of the Tables demo
using System;
using System.Drawing;
using System.IO;
using ExpertXls.ExcelLib;
namespace ExcelLibraryDemos
{
/// <summary>
/// Turns a sales table into an Excel table: a built-in table style with banded rows, filter buttons in the header
/// row and a totals row with a sum and an average calculated by Excel. Excel tables are available in the Excel
/// 2007+ format; the Excel 97-2003 workbook gets the same data with an AutoFilter instead.
/// </summary>
public static class TablesDemo
{
/// <summary>The name of the generated file, without extension.</summary>
public const string OutputName = "Tables";
/// <summary>
/// Creates the workbook in the given format and saves it to the output stream.
/// </summary>
/// <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(ExcelWorkbookFormat format, string inputFolder, Stream output)
{
ExcelWorkbook workbook = new ExcelWorkbook(format);
// workbook.LicenseKey = "your license key";
workbook.DocumentProperties.Subject = "Excel tables";
workbook.DocumentProperties.Comments = "Created with ExpertXls Excel Library for .NET";
Image logo = Image.FromFile(Path.Combine(inputFolder, "Images", "logo.jpg"));
try
{
ExcelCellStyle titleStyle = DemoLayout.AddTitleStyle(workbook);
ExcelCellStyle sectionStyle = DemoLayout.AddSectionStyle(workbook);
ExcelWorksheet worksheet = workbook.Worksheets[0];
worksheet.Name = "Excel Table";
DemoLayout.SetupPage(worksheet, logo, ExcelPageOrientation.Landscape);
DemoLayout.WriteTitle(worksheet, "A2:G3", "Creating an Excel Table", titleStyle);
ExcelRange salesRange = DemoLayout.WriteSalesTable(worksheet, 6, 1);
if (format == ExcelWorkbookFormat.Xlsx_2007)
{
DemoLayout.WriteSection(worksheet, "A5:G5", "An Excel table with the Medium 9 style and a totals row. Its name, Sales, can be used in formulas.", sectionStyle);
// the first row of the range is the header row: its cells give the names of the table columns
ExcelTable table = worksheet.Tables.Add("Sales", salesRange);
table.Style = ExcelTableStyle.Medium9;
table.ShowBandedRows = true;
table.ShowFirstColumn = true;
// the totals row is added below the data; Excel calculates the SUBTOTAL formulas
table.ShowTotalsRow = true;
table.Columns["Product"].TotalsRowLabel = "Total";
table.Columns["Region"].TotalsCalculation = ExcelTableTotalsCalculation.Count;
for (int i = 2; i < table.Columns.Count - 1; i++)
table.Columns[i].TotalsCalculation = ExcelTableTotalsCalculation.Sum;
table.Columns["Total"].TotalsCalculation = ExcelTableTotalsCalculation.Average;
// a formula with a structured reference to the table
worksheet["A22"].Text = "Largest total:";
worksheet["A22"].Style.Font.Bold = true;
worksheet["B22"].Formula = "=MAX(Sales[Total])";
worksheet["B22"].Style.Number.NumberFormatString = "#,##0";
}
else
{
DemoLayout.WriteSection(worksheet, "A5:G5", "Excel tables need the Excel 2007+ (.xlsx) format. This workbook has an AutoFilter instead.", sectionStyle);
salesRange.AddAutoFilter();
}
workbook.Save(output);
}
finally
{
workbook.Close();
logo.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.