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

← All demos

C# code of the Tables demo

Features/TablesDemo.cs
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.