Add 'DisplayCalcChain' method to debug why the computation of the Calc Chain was taking so long during a 'regenerate'.
diff --git a/EPPlus/FormulaParsing/CalculateExtentions.cs b/EPPlus/FormulaParsing/CalculateExtentions.cs
index 1ec584a..22637ac 100644
--- a/EPPlus/FormulaParsing/CalculateExtentions.cs
+++ b/EPPlus/FormulaParsing/CalculateExtentions.cs
@@ -37,6 +37,7 @@
using System.Text;
using OfficeOpenXml.FormulaParsing;
using OfficeOpenXml.FormulaParsing.Exceptions;
+using System.IO;
namespace OfficeOpenXml
{
public static class CalculationExtension
@@ -88,6 +89,11 @@
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();
if (parser.Logger != null)
@@ -97,6 +103,37 @@
}
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 System.IO.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 ExcelCalculationOption());
