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