EPPlus/FormulaParsing/CalculateExtentions.cs

/*******************************************************************************
 * You may amend and distribute as you like, but don't remove this header!
 *
 * EPPlus provides server-side generation of Excel 2007/2010 spreadsheets.
 * See http://www.codeplex.com/EPPlus for details.
 *
 * Copyright (C) 2011  Jan Källman
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.

 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
 * See the GNU Lesser General Public License for more details.
 *
 * The GNU Lesser General Public License can be viewed at http://www.opensource.org/licenses/lgpl-license.php
 * If you unfamiliar with this license or have questions about it, here is an http://www.gnu.org/licenses/gpl-faq.html
 *
 * All code and executables are provided "as is" with no warranty either express or implied.
 * The author accepts no liability for any damage or loss of business that this product may cause.
 *
 * Code change notes:
 *
 * Author							Change						Date
 * ******************************************************************************
 * Jan Källman                      Added                       2012-03-04
 *******************************************************************************/

using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using OfficeOpenXml.FormulaParsing;
using OfficeOpenXml.FormulaParsing.Excel.Functions;
using OfficeOpenXml.FormulaParsing.Exceptions;
using OfficeOpenXml.FormulaParsing.LexicalAnalysis;

namespace OfficeOpenXml;

public static class CalculationExtension {
  public static void Calculate(this ExcelWorkbook workbook) {
    Calculate(
        workbook,
        new() {
          AllowCirculareReferences = false,
        });
  }

  public static void Calculate(this ExcelWorkbook workbook, ExcelCalculationOption options) {
    Init(workbook);

    var dc = DependencyChainFactory.Create(workbook, options);
    workbook.FormulaParser.InitNewCalc();
    CalcChain(workbook, workbook.FormulaParser, dc);
  }

  public static void Calculate(this ExcelWorksheet worksheet) {
    Calculate(worksheet, new ExcelCalculationOption());
  }

  public static void Calculate(this ExcelWorksheet worksheet, ExcelCalculationOption options) {
    Init(worksheet.Workbook);
    //worksheet.Workbook._formulaParser = null; TODO:Cant reset. Don't work with userdefined or overrided worksheet functions
    var dc = DependencyChainFactory.Create(worksheet, options);

    // Display Calc Chain to determine why formula calculation is taking so long.
    // Uncomment the following line to display the Calc Chain.
    // DisplayCalcChain(worksheet, dc);

    var parser = worksheet.Workbook.FormulaParser;
    parser.InitNewCalc();
    CalcChain(worksheet.Workbook, parser, dc);
  }

  // I added this method in December 2019 to analyze why formula calculation was taking
  // so long during a 'Regenerate' of a Microsoft Excel table.
  private static void DisplayCalcChain(ExcelWorksheet worksheet, DependencyChain dc) {
    // Open Output file
    string outputFolderName = @"C:\\EPPlus";
    Directory.CreateDirectory(outputFolderName);
    DateTime invocationDateTime = DateTime.Now;
    string outputFileName = Path.Combine(
        outputFolderName,
        $"CalcChain_{worksheet.Name}_{invocationDateTime.ToString("yyyyMMdd_HHmmss_fff")}.txt");
    FileStream fileStream = new FileStream(outputFileName, FileMode.CreateNew);
    StreamWriter streamWriter = new StreamWriter(fileStream, Encoding.UTF8, 65536);

    // Display CalcChain
    foreach (int index in dc.CalcOrder) {
      FormulaCell formulaCell = dc.list[index];
      string calcChainValue = "";
      calcChainValue += $"Index {index}, ";
      calcChainValue += $"Worksheet '{formulaCell.ws.Name}', ";
      calcChainValue += $"Row {formulaCell.Row}, ";
      calcChainValue += $"Col {formulaCell.Column}, ";
      calcChainValue += $" Formula '{formulaCell.Formula}'";
      streamWriter.WriteLine(calcChainValue);
    }

    // Close Output file
    streamWriter.Flush();
    streamWriter.Close();
  }

  public static void Calculate(this ExcelRangeBase range) {
    Calculate(range, new());
  }

  public static void Calculate(this ExcelRangeBase range, ExcelCalculationOption options) {
    Init(range._workbook);
    var parser = range._workbook.FormulaParser;
    parser.InitNewCalc();
    var dc = DependencyChainFactory.Create(range, options);
    CalcChain(range._workbook, parser, dc);
  }

  public static object Calculate(this ExcelWorksheet worksheet, string formula) {
    return Calculate(worksheet, formula, new());
  }

  public static object Calculate(
      this ExcelWorksheet worksheet,
      string formula,
      ExcelCalculationOption options) {
    try {
      worksheet.CheckSheetType();
      if (string.IsNullOrEmpty(formula.Trim())) {
        return null;
      }
      Init(worksheet.Workbook);
      var parser = worksheet.Workbook.FormulaParser;
      parser.InitNewCalc();
      if (formula[0] == '=') {
        formula = formula.Substring(1); //Remove any starting equal sign
      }
      var dc = DependencyChainFactory.Create(worksheet, formula, options);
      var f = dc.list[0];
      dc.CalcOrder.RemoveAt(dc.CalcOrder.Count - 1);

      CalcChain(worksheet.Workbook, parser, dc);

      return parser.ParseCell(f.Tokens, worksheet.Name, -1, -1);
    } catch (Exception ex) {
      return new ExcelErrorValueException(ex.Message, ExcelErrorValue.Create(eErrorType.Value));
    }
  }

  private static void CalcChain(ExcelWorkbook wb, FormulaParser parser, DependencyChain dc) {
    foreach (var ix in dc.CalcOrder) {
      var item = dc.list[ix];
      try {
        var ws = wb.Worksheets.GetBySheetId(item.SheetID);
        var v = parser.ParseCell(item.Tokens, ws == null ? "" : ws.Name, item.Row, item.Column);
        SetValue(wb, item, v);
      } catch (FunctionException) {
        // Excel function is not supported by EPPlus
        throw;
      } catch (FormatException) {
        throw;
      } catch (Exception) {
        var error = ExcelErrorValue.Parse(ExcelErrorValue.Values.Value);
        SetValue(wb, item, error);
      }
    }
  }

  private static void Init(ExcelWorkbook workbook) {
    workbook._formulaTokens = new();
    ;
    foreach (var ws in workbook.Worksheets) {
      if (!(ws is ExcelChartsheet)) {
        ws._formulaTokens = new();
      }
    }
  }

  private static void SetValue(ExcelWorkbook workbook, FormulaCell item, object v) {
    if (item.Column == 0) {
      if (item.SheetID <= 0) {
        workbook.Names[item.Row].NameValue = v;
      } else {
        var sh = workbook.Worksheets.GetBySheetId(item.SheetID);
        sh.Names[item.Row].NameValue = v;
      }
    } else {
      var sheet = workbook.Worksheets.GetBySheetId(item.SheetID);
      sheet._values.SetValue(item.Row, item.Column, v);
    }
  }
}