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

← All demos

C# code of the Formulas demo

Features/FormulasDemo.cs
using System;
using System.Drawing;
using System.Globalization;
using System.IO;
using ExpertXls.ExcelLib;

namespace ExcelLibraryDemos
{
    /// <summary>
    /// Adds formulas and array formulas to a worksheet, uses named ranges in formulas and calculates the formula
    /// results in the library, so that the values are in the file before Excel opens it.
    /// </summary>
    public static class FormulasDemo
    {
        /// <summary>The name of the generated file, without extension.</summary>
        public const string OutputName = "Formulas";

        /// <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 = "Formulas";
            workbook.DocumentProperties.Comments = "Created with ExpertXls Excel Library for .NET";

            // The formulas are parsed with these separators and number format. Set them before the first formula:
            // the defaults depend on the culture of the thread, the values below make the code culture independent.
            workbook.FormulaParamsSeparator = ',';
            workbook.FormulaRowsSeparator = ';';
            workbook.FormulaNumberFormat = NumberFormatInfo.InvariantInfo;

            Image logo = Image.FromFile(Path.Combine(inputFolder, "Images", "logo.jpg"));
            try
            {
                ExcelCellStyle titleStyle = DemoLayout.AddTitleStyle(workbook);
                ExcelCellStyle sectionStyle = DemoLayout.AddSectionStyle(workbook);

                ExcelCellStyle resultStyle = workbook.Styles.AddStyle("FormulaResultStyle");
                resultStyle.Font.Bold = true;
                resultStyle.Fill.FillType = ExcelCellFillType.SolidFill;
                resultStyle.Fill.SolidFillOptions.BackColor = Color.FromArgb(255, 255, 204);
                resultStyle.Borders.LineStyle = ExcelCellLineStyle.Thin;

                ExcelWorksheet worksheet = workbook.Worksheets[0];
                worksheet.Name = "Formulas";
                DemoLayout.SetupPage(worksheet, logo, ExcelPageOrientation.Portrait);
                DemoLayout.WriteTitle(worksheet, "A2:G3", "Adding Formulas to an Excel Worksheet", titleStyle);
                worksheet["A1"].ColumnWidthInChars = 40;

                // ORDINARY FORMULAS
                DemoLayout.WriteSection(worksheet, "A5:G5", "Formulas", sectionStyle);
                worksheet["A7"].Value = "Formula data:";
                worksheet["A7"].Style.Font.Bold = true;
                double[] data = { 100.5, 50.5, 89, 15.75, 44.25 };
                for (int i = 0; i < data.Length; i++)
                    worksheet[7, 3 + i].Value = data[i];

                AddFormula(worksheet, 9, "=SUM(C7:G7)", "The sum of the numbers in C7:G7", resultStyle);
                AddFormula(worksheet, 11, "=AVERAGE(C7:G7)", "The average of the numbers in C7:G7", resultStyle);
                AddFormula(worksheet, 13, "=MIN(C7:G7)", "The smallest number in C7:G7", resultStyle);
                AddFormula(worksheet, 15, "=C7+100.45", "A constant added to C7", resultStyle);
                AddFormula(worksheet, 17, "=IF(C7>E7,C7,E7)", "The larger of C7 and E7, with the IF function", resultStyle);
                AddFormula(worksheet, 19, "=ROUND(SUM(C7:G7)/COUNT(C7:G7),1)", "Nested functions", resultStyle);

                // ARRAY FORMULAS
                DemoLayout.WriteSection(worksheet, "A22:G22", "Array formulas", sectionStyle);
                worksheet["A24"].Value = "Formula data:";
                worksheet["A24"].Style.Font.Bold = true;
                // an array constant fills the cells of the range, here C24:G24
                worksheet["C24:G24"].FormulaArray = "{10,-5,2,21,-20}";
                worksheet["A24"].AddComment("Initialized with the array formula {10,-5,2,21,-20}");

                worksheet["A26"].Text = "AVERAGE(IF(C24:G24>0,C24:G24,FALSE))";
                worksheet["A26"].Style.Font.Color = Color.DarkBlue;
                worksheet["A26"].AddComment("The average of the positive numbers in C24:G24");
                worksheet["C26"].FormulaArray = "=AVERAGE(IF(C24:G24>0,C24:G24,FALSE))";
                worksheet["C26"].Style = resultStyle;

                // named ranges in a formula: the result of the array formula fills the five cells of C28:G28
                worksheet.NamedRanges.AddNamedRange(worksheet["C24:G24"], "FirstRange");
                worksheet.NamedRanges.AddNamedRange(worksheet["C7:G7"], "SecondRange");
                worksheet["A28"].Text = "FirstRange+SecondRange";
                worksheet["A28"].Style.Font.Color = Color.DarkBlue;
                worksheet["A28"].AddComment("Adds the values of C24:G24 and C7:G7, cell by cell");
                worksheet["C28:G28"].FormulaArray = "=FirstRange+SecondRange";
                worksheet["C28:G28"].Style = resultStyle;

                // CALCULATE: the formula results are stored in the file, so that applications which do not calculate
                // formulas (and the code below) can read them
                workbook.UpdateFormulaResult();
                worksheet["A30"].Value = "The sum calculated by the library:";
                worksheet["A30"].Style.Font.Bold = true;
                worksheet["C30"].Value = worksheet["C9"].FormulaResultNumberValue;

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

        // writes the formula text (without the = sign, so that it stays a text) and a comment in column A and the
        // formula in column C
        private static void AddFormula(ExcelWorksheet worksheet, int row, string formula, string comment, ExcelCellStyle resultStyle)
        {
            worksheet[row, 1].Text = formula.Substring(1);
            worksheet[row, 1].Style.Font.Color = Color.DarkBlue;
            worksheet[row, 1].AddComment(comment);
            worksheet[row, 3].Formula = formula;
            worksheet[row, 3].Style = resultStyle;
        }
    }
}

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.