A benchmark for simple comparison of previous parser and formula evaluation against XLParser and type system

benchmark-type-system.cs

using System;
using BenchmarkDotNet.Attributes;
using BenchmarkDotNet.Running;
using ClosedXML.Excel;

namespace ClosedXML.Sandbox
{
    [RPlotExporter]
    public class FormulaBenchmark
    {
        private const string SingleCellCalculationFormula = "1 * 2 / (\"7\" + 8 - (1 + 6 * ( 2 * 3)))";
        private const string SingleCellFunctionFormula = "SUM(1,2,3, IF(4 = 4, 5, 6))";
        private readonly XLWorkbook _wb;
        private IXLWorksheet _ws;
        private IXLCell _cellSingleCalc;
        private IXLCell _cellSingleFormula;
        private IXLCell _cellSingleRangeFormula;
        private XLWorkbook _parseWb;
        private XLWorksheet _parseWs;
        private IXLCell _cellDoubleRangeFormula;

        public FormulaBenchmark()
        {
            _wb = new XLWorkbook();
            _ws = _wb.AddWorksheet();
            _cellSingleCalc = _ws.Cell("AA1");
            _cellSingleCalc.FormulaA1 = SingleCellCalculationFormula;
            _ = _cellSingleCalc.Value;

            _cellSingleFormula = _ws.Cell("AA2");
            _cellSingleFormula.FormulaA1 = SingleCellFunctionFormula;
            _ = _cellSingleFormula.Value;

            _ws.Cells("A1:Z100").Value = 1;
            _cellSingleRangeFormula = _ws.Cell("AA3");
            _cellSingleRangeFormula.FormulaA1 = "SUM(A1:Z100)";
            _ = _cellSingleRangeFormula.Value;

            _ws.Cells("A101:Z200").FormulaA1 = "SUM(1,2)";
            _cellDoubleRangeFormula = _ws.Cell("AA4");
            _cellDoubleRangeFormula.FormulaA1 = "SUM(A101:Z200)";
            _ = _cellDoubleRangeFormula.Value;


            _parseWb = new XLWorkbook();
            _parseWs = (XLWorksheet)_parseWb.AddWorksheet("Empty");
            _parseWs.CalcEngine.CacheExpressions = false;
        }

        [Benchmark]
        public object SingleCellCalculationEvaluation()
        {
            var val = _cellSingleCalc.Value;
            return val;
        }

        [Benchmark]
        public object SingleCellFunctionEvaluation()
        {
            var val = _cellSingleFormula.Value;
            return val;
        }

        [Benchmark]
        public object SingleCellRangeEvaluation()
        {
            var val = _cellSingleRangeFormula.Value;
            return val;
        }

        [Benchmark]
        public object DoubleCellRangeEvaluation()
        {
            var val = _cellDoubleRangeFormula.Value;
            return val;
        }

        [Benchmark]
        public object SingleCellCalculationParsing()
        {
            return _parseWs.CalcEngine.Parse(SingleCellCalculationFormula);
        }

        [Benchmark]
        public object SingleCellFunctionParsing()
        {
            return _parseWs.CalcEngine.Parse(SingleCellFunctionFormula);
        }

    }

    internal static class Program
    {
        private static void Main(string[] args)
        {
            var summary = BenchmarkRunner.Run<FormulaBenchmark>();
        }
    }
}