diff --git a/ClosedXML/Excel/CalcEngine/CellRangeReference.cs b/ClosedXML/Excel/CalcEngine/CellRangeReference.cs new file mode 100644 index 0000000..1b26891 --- /dev/null +++ b/ClosedXML/Excel/CalcEngine/CellRangeReference.cs @@ -0,0 +1,60 @@ +using System; +using System.Collections; + +namespace ClosedXML.Excel.CalcEngine +{ + internal class CellRangeReference : IValueObject, IEnumerable + { + private IXLRange _range; + private XLCalcEngine _ce; + + public CellRangeReference(IXLRange range, XLCalcEngine ce) + { + _range = range; + _ce = ce; + } + + public IXLRange Range { get { return _range; } } + + // ** IValueObject + public object GetValue() + { + return GetValue(_range.FirstCell()); + } + + // ** IEnumerable + public IEnumerator GetEnumerator() + { + var maxRow = Math.Min(_range.RangeAddress.LastAddress.RowNumber, _range.Worksheet.LastCellUsed().Address.RowNumber); + var maxCol = Math.Min(_range.RangeAddress.LastAddress.ColumnNumber, _range.Worksheet.LastCellUsed().Address.ColumnNumber); + using (var trimmedRange = (XLRangeBase)_range.Worksheet.Range(_range.FirstCell().Address, new XLAddress(maxRow, maxCol, false, false))) + return trimmedRange.CellValues().GetEnumerator(); + } + + private Boolean _evaluating; + + // ** implementation + private object GetValue(IXLCell cell) + { + if (_evaluating || (cell as XLCell).IsEvaluating) + { + throw new InvalidOperationException($"Circular Reference occured during evaluation. Cell: {cell.Address.ToString(XLReferenceStyle.Default, true)}"); + } + try + { + _evaluating = true; + var f = cell.FormulaA1; + if (String.IsNullOrWhiteSpace(f)) + return cell.Value; + else + { + return (cell as XLCell).Evaluate(); + } + } + finally + { + _evaluating = false; + } + } + } +} diff --git a/ClosedXML/Excel/CalcEngine/XLAddressComparer.cs b/ClosedXML/Excel/CalcEngine/XLAddressComparer.cs new file mode 100644 index 0000000..ca2c1c6 --- /dev/null +++ b/ClosedXML/Excel/CalcEngine/XLAddressComparer.cs @@ -0,0 +1,38 @@ +using System; +using System.Collections.Generic; + +namespace ClosedXML.Excel.CalcEngine +{ + internal class XLAddressComparer : IEqualityComparer + { + private readonly bool _ignoreFixed; + + public XLAddressComparer(bool ignoreFixed) + { + _ignoreFixed = ignoreFixed; + } + + public bool Equals(IXLAddress x, IXLAddress y) + { + return (x == null && y == null) || + (x != null && y != null && + string.Equals(x.Worksheet.Name, y.Worksheet.Name, StringComparison.InvariantCultureIgnoreCase) && + x.ColumnNumber == y.ColumnNumber && + x.RowNumber == y.RowNumber && + (_ignoreFixed || x.FixedColumn == y.FixedColumn && + x.FixedRow == y.FixedRow)); + } + + public int GetHashCode(IXLAddress obj) + { + return new + { + WorksheetName = obj.Worksheet.Name.ToUpperInvariant(), + obj.ColumnNumber, + obj.RowNumber, + FixedColumn = (_ignoreFixed ? false : obj.FixedColumn), + FixedRow = (_ignoreFixed ? false : obj.FixedRow) + }.GetHashCode(); + } + } +} diff --git a/ClosedXML/Excel/CalcEngine/XLCalcEngine.cs b/ClosedXML/Excel/CalcEngine/XLCalcEngine.cs index 4c161f9..ba5779d 100644 --- a/ClosedXML/Excel/CalcEngine/XLCalcEngine.cs +++ b/ClosedXML/Excel/CalcEngine/XLCalcEngine.cs @@ -1,5 +1,5 @@ using System; -using System.Collections; +using System.Collections.Generic; using System.Linq; namespace ClosedXML.Excel.CalcEngine @@ -23,6 +23,48 @@ _ws = ws; } + private IList _cellRanges; + + /// + /// Get a collection of cell ranges included into the expression. Order is not preserved. + /// + /// Formula to parse. + /// Collection of ranges included into the expression. + public IEnumerable GetPrecedentRanges(string expression) + { + _cellRanges = new List(); + Parse(expression); + var ranges = _cellRanges; + _cellRanges = null; + var visitedRanges = new HashSet(new XLRangeAddressComparer(true)); + foreach (var range in ranges) + { + if (!visitedRanges.Contains(range.RangeAddress)) + { + visitedRanges.Add(range.RangeAddress); + yield return range; + } + } + } + + public IEnumerable GetPrecedentCells(string expression) + { + if (!String.IsNullOrWhiteSpace(expression)) + { + var ranges = GetPrecedentRanges(expression); + var visitedCells = new HashSet(new XLAddressComparer(true)); + var cells = ranges.SelectMany(range => range.Cells()).Distinct(); + foreach (var cell in cells) + { + if (!visitedCells.Contains(cell.Address)) + { + visitedCells.Add(cell.Address); + yield return cell; + } + } + } + } + public override object GetExternalObject(string identifier) { if (identifier.Contains("!") && _wb != null) @@ -39,7 +81,7 @@ .Distinct(); if (!referencedSheetNames.Any()) - return new CellRangeReference(_ws.Range(identifier), this); + return GetCellRangeReference(_ws.Range(identifier)); else if (referencedSheetNames.Count() > 1) throw new ArgumentOutOfRangeException(referencedSheetNames.Last(), "Cross worksheet references may references no more than 1 other worksheet"); else @@ -49,66 +91,20 @@ identifier = identifier.ToLower().Replace(string.Format("{0}!", worksheet.Name.ToLower()), ""); - return new CellRangeReference(worksheet.Range(identifier), this); + return GetCellRangeReference(worksheet.Range(identifier)); } } else if (_ws != null) - return new CellRangeReference(_ws.Range(identifier), this); + return GetCellRangeReference(_ws.Range(identifier)); else return identifier; } - } - internal class CellRangeReference : IValueObject, IEnumerable - { - private IXLRange _range; - private XLCalcEngine _ce; - - public CellRangeReference(IXLRange range, XLCalcEngine ce) + private CellRangeReference GetCellRangeReference(IXLRange range) { - _range = range; - _ce = ce; - } - - public IXLRange Range { get { return _range; } } - - // ** IValueObject - public object GetValue() - { - return GetValue(_range.FirstCell()); - } - - // ** IEnumerable - public IEnumerator GetEnumerator() - { - var maxRow = Math.Min(_range.RangeAddress.LastAddress.RowNumber, _range.Worksheet.LastCellUsed().Address.RowNumber); - var maxCol = Math.Min(_range.RangeAddress.LastAddress.ColumnNumber, _range.Worksheet.LastCellUsed().Address.ColumnNumber); - using (var trimmedRange = (XLRangeBase)_range.Worksheet.Range(_range.FirstCell().Address, new XLAddress(maxRow, maxCol, false, false))) - return trimmedRange.CellValues().GetEnumerator(); - } - - private Boolean _evaluating; - - // ** implementation - private object GetValue(IXLCell cell) - { - if (_evaluating || (cell as XLCell).IsEvaluating) - { - throw new InvalidOperationException($"Circular Reference occured during evaluation. Cell: {cell.Address.ToString(XLReferenceStyle.Default, true)}"); - } - try - { - _evaluating = true; - var f = cell.FormulaA1; - if (String.IsNullOrWhiteSpace(f)) - return cell.Value; - else - return new XLCalcEngine(cell.Worksheet).Evaluate(f); - } - finally - { - _evaluating = false; - } + var res = new CellRangeReference(range, this); + _cellRanges?.Add(res.Range); + return res; } } } diff --git a/ClosedXML/Excel/CalcEngine/XLRangeAddressComparer.cs b/ClosedXML/Excel/CalcEngine/XLRangeAddressComparer.cs new file mode 100644 index 0000000..a6f7594 --- /dev/null +++ b/ClosedXML/Excel/CalcEngine/XLRangeAddressComparer.cs @@ -0,0 +1,31 @@ +using System.Collections.Generic; + +namespace ClosedXML.Excel.CalcEngine +{ + internal class XLRangeAddressComparer : IEqualityComparer + { + private readonly XLAddressComparer _addressComparer; + + public XLRangeAddressComparer(bool ignoreFixed) + { + _addressComparer = new XLAddressComparer(ignoreFixed); + } + + public bool Equals(IXLRangeAddress x, IXLRangeAddress y) + { + return (x == null && y == null) || + (x != null && y != null && + _addressComparer.Equals(x.FirstAddress, y.FirstAddress) && + _addressComparer.Equals(x.LastAddress, y.LastAddress)); + } + + public int GetHashCode(IXLRangeAddress obj) + { + return new + { + FirstHash = _addressComparer.GetHashCode(obj.FirstAddress), + LastHash = _addressComparer.GetHashCode(obj.LastAddress), + }.GetHashCode(); + } + } +} diff --git a/ClosedXML/Excel/Cells/IXLCell.cs b/ClosedXML/Excel/Cells/IXLCell.cs index 274a50f..5fa8c8e 100644 --- a/ClosedXML/Excel/Cells/IXLCell.cs +++ b/ClosedXML/Excel/Cells/IXLCell.cs @@ -310,8 +310,29 @@ IXLCell CopyTo(String target); + /// + /// Textual representation of cell calculated value (as it is saved to a workbook or read from it) + /// + [Obsolete("Use CachedValue instead")] String ValueCached { get; } + /// + /// Calculated value of cell formula. Is used for decreasing number of computations perfromed. + /// May hold invalid value when flag is True. + /// + Object CachedValue { get; } + + /// + /// Flag indicating that previously calculated cell value may be not valid anymore and has to be re-evaluated. + /// + Boolean NeedsRecalculation { get; } + + /// + /// Invalidate so the formula will be re-evaluated next time is accessed. + /// If cell does not contain formula nothing happens. + /// + void InvalidateFormula(); + IXLRichText RichText { get; } Boolean HasRichText { get; } IXLComment Comment { get; } diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs index e3ec9ae..a164d30 100644 --- a/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/Excel/Cells/XLCell.cs @@ -361,92 +361,143 @@ /// internal bool IsEvaluating { get; private set; } + /// + /// Calculate a value of the specified formula. + /// + /// Cell formula to evaluate. + /// Null if formula is empty or null, calculated value otherwise. + private object RecalculateFormula(string fA1) + { + if (string.IsNullOrEmpty(fA1)) + return null; + + if (IsEvaluating) + throw new InvalidOperationException("Circular Reference"); + + if (fA1[0] == '{') + fA1 = fA1.Substring(1, fA1.Length - 2); + + string sName; + string cAddress; + if (fA1.Contains('!')) + { + sName = fA1.Substring(0, fA1.IndexOf('!')); + if (sName[0] == '\'') + sName = sName.Substring(1, sName.Length - 2); + + cAddress = fA1.Substring(fA1.IndexOf('!') + 1); + } + else + { + sName = Worksheet.Name; + cAddress = fA1; + } + + if (_worksheet.Workbook.WorksheetsInternal.Any( + w => String.Compare(w.Name, sName, true) == 0) + && XLHelper.IsValidA1Address(cAddress) + ) + { + var referenceCell = _worksheet.Workbook.Worksheet(sName).Cell(cAddress); + if (referenceCell.IsEmpty(false)) + return 0; + else + return referenceCell.Value; + } + + object retVal; + try + { + IsEvaluating = true; + + if (_worksheet + .Workbook + .WorksheetsInternal + .Any(w => String.Compare(w.Name, sName, true) == 0) + && XLHelper.IsValidA1Address(cAddress)) + { + var referenceCell = _worksheet.Workbook.Worksheet(sName).Cell(cAddress); + if (referenceCell.IsEmpty(false)) + return 0; + else + return referenceCell.Value; + } + + retVal = Worksheet.Evaluate(fA1); + } + finally + { + IsEvaluating = false; + } + + var retValEnumerable = retVal as IEnumerable; + if (retValEnumerable != null && !(retVal is String)) + return retValEnumerable.Cast().First(); + + return retVal; + } + + public void InvalidateFormula() + { + NeedsRecalculation = true; + Worksheet.Workbook.InvalidateFormulas(); + ModifiedAtVersion = Worksheet.Workbook.RecalculationCounter; + } + + /// + /// Perform an evaluation of cell formula. If cell does not contain formula nothing happens, if cell does not need + /// recalculation ( is False) nothing happens either, unless flag is specified. + /// Otherwise recalculation is perfomed, result value is preserved in and returned. + /// + /// Flag indicating whether a recalculation must be performed even is cell does not need it. + /// Null if cell does not contain a formula. Calculated value otherwise. + public object Evaluate(bool force = false) + { + if (force || NeedsRecalculation) + { + CachedValue = RecalculateFormula(FormulaA1); + EvaluatedAtVersion = Worksheet.Workbook.RecalculationCounter; + NeedsRecalculation = false; + } + return CachedValue; + } + + private object StringToCellDataType(string cellValue) + { + if (_dataType == XLDataType.Boolean) + return cellValue != "0"; + + if (_dataType == XLDataType.DateTime + && Double.TryParse(cellValue, XLHelper.NumberStyle, XLHelper.ParseCulture, out Double d) + && d.IsValidOADateNumber()) + + return DateTime.FromOADate(d); + + if (_dataType == XLDataType.Number + && double.TryParse(cellValue, XLHelper.NumberStyle, XLHelper.ParseCulture, out Double n)) + + return n; + + if (_dataType == XLDataType.TimeSpan + && TimeSpan.TryParse(cellValue, out TimeSpan t)) + return t; + + return cellValue; + } + public object Value { get { - var fA1 = FormulaA1; - if (!String.IsNullOrWhiteSpace(fA1)) + if (!String.IsNullOrWhiteSpace(_formulaA1) || + !String.IsNullOrEmpty(_formulaR1C1)) { - if (IsEvaluating) - throw new InvalidOperationException("Circular Reference"); - - if (fA1[0] == '{') - fA1 = fA1.Substring(1, fA1.Length - 2); - - string sName; - string cAddress; - if (fA1.Contains('!')) - { - sName = fA1.Substring(0, fA1.IndexOf('!')); - if (sName[0] == '\'') - sName = sName.Substring(1, sName.Length - 2); - - cAddress = fA1.Substring(fA1.IndexOf('!') + 1); - } - else - { - sName = Worksheet.Name; - cAddress = fA1; - } - - object retVal; - try - { - IsEvaluating = true; - - if (_worksheet - .Workbook - .WorksheetsInternal - .Any(w => String.Compare(w.Name, sName, true) == 0) - && XLHelper.IsValidA1Address(cAddress)) - { - var referenceCell = _worksheet.Workbook.Worksheet(sName).Cell(cAddress); - if (referenceCell.IsEmpty(false)) - return 0; - else - return referenceCell.Value; - } - - retVal = Worksheet.Evaluate(fA1); - } - finally - { - IsEvaluating = false; - } - - var retValEnumerable = retVal as IEnumerable; - - if (retValEnumerable != null && !(retVal is String)) - return retValEnumerable.Cast().First(); - - return retVal; + return Evaluate(); } var cellValue = HasRichText ? _richText.ToString() : _cellValue; - - if (_dataType == XLDataType.Boolean) - return cellValue != "0"; - - if (_dataType == XLDataType.DateTime - && Double.TryParse(cellValue, XLHelper.NumberStyle, XLHelper.ParseCulture, out Double d) - && d.IsValidOADateNumber()) - - return DateTime.FromOADate(d); - - if (_dataType == XLDataType.Number - && double.TryParse(cellValue, XLHelper.NumberStyle, XLHelper.ParseCulture, out Double n)) - - return n; - - if (_dataType == XLDataType.TimeSpan - && TimeSpan.TryParse(cellValue, out TimeSpan t)) - - return t; - - return cellValue; + return StringToCellDataType(cellValue); } - set { FormulaA1 = String.Empty; @@ -1087,6 +1138,8 @@ set { + InvalidateFormula(); + _formulaA1 = String.IsNullOrWhiteSpace(value) ? null : value; _formulaR1C1 = null; @@ -1105,6 +1158,8 @@ set { + InvalidateFormula(); + _formulaR1C1 = String.IsNullOrWhiteSpace(value) ? null : value; _formulaA1 = null; @@ -1185,6 +1240,64 @@ return this; } + private bool _recalculationNeededLastValue; + + /// + /// Flag indicating that previously calculated cell value may be not valid anymore and has to be re-evaluated. + /// + public bool NeedsRecalculation + { + get + { + if (String.IsNullOrWhiteSpace(_formulaA1) && String.IsNullOrEmpty(_formulaR1C1)) + return false; + + if (NeedsRecalculationEvaluatedAtVersion == Worksheet.Workbook.RecalculationCounter) + return _recalculationNeededLastValue; + + bool res = EvaluatedAtVersion < ModifiedAtVersion || // the cell itself was modified + GetAffectingCells().Any(cell => cell.ModifiedAtVersion > EvaluatedAtVersion || // the affecting cell was modified after this one was evaluated + cell.EvaluatedAtVersion > EvaluatedAtVersion || // the affecting cell was evaluated after this one (normally this should not happen) + cell.NeedsRecalculation); // the affecting cell needs recalculation (recursion to walk through dependencies) + + NeedsRecalculation = res; + return res; + } + private set + { + _recalculationNeededLastValue = value; + NeedsRecalculationEvaluatedAtVersion = Worksheet.Workbook.RecalculationCounter; + } + } + + private IEnumerable GetAffectingCells() + { + return Worksheet.CalcEngine.GetPrecedentCells(_formulaA1).Cast(); + } + + /// + /// The value of that workbook had at the moment of cell last modification. + /// If this value is greater than then cell needs re-evaluation, as well as all dependent cells do. + /// + private long ModifiedAtVersion { get; set; } + + /// + /// The value of that workbook had at the moment of cell formula evaluation. + /// If this value equals to it indicates that stores + /// correct value and no re-evaluation has to be performed. + /// + private long EvaluatedAtVersion { get; set; } + + /// + /// The value of that workbook had at the moment of determining whether the cell + /// needs re-evaluation (due to it has been edited or some of the affecting cells has). If thie value equals to + /// it indicates that stores correct value and no check has to be performed. + /// + private long NeedsRecalculationEvaluatedAtVersion { get; set; } + + public object CachedValue { get; private set; } + + [Obsolete("Use CachedValue instead")] public string ValueCached { get; internal set; } public IXLRichText RichText @@ -2248,7 +2361,7 @@ internal void ShiftFormulaRows(XLRange shiftedRange, int rowsShifted) { - _formulaA1 = ShiftFormulaRows(FormulaA1, Worksheet, shiftedRange, rowsShifted); + FormulaA1 = ShiftFormulaRows(FormulaA1, Worksheet, shiftedRange, rowsShifted); } internal static String ShiftFormulaRows(String formulaA1, XLWorksheet worksheetInAction, XLRange shiftedRange, @@ -2397,7 +2510,7 @@ internal void ShiftFormulaColumns(XLRange shiftedRange, int columnsShifted) { - _formulaA1 = ShiftFormulaColumns(FormulaA1, Worksheet, shiftedRange, columnsShifted); + FormulaA1 = ShiftFormulaColumns(FormulaA1, Worksheet, shiftedRange, columnsShifted); } internal static String ShiftFormulaColumns(String formulaA1, XLWorksheet worksheetInAction, XLRange shiftedRange, diff --git a/ClosedXML/Excel/IXLWorkbook.cs b/ClosedXML/Excel/IXLWorkbook.cs index 702c490..4a4904b 100644 --- a/ClosedXML/Excel/IXLWorkbook.cs +++ b/ClosedXML/Excel/IXLWorkbook.cs @@ -140,6 +140,11 @@ Object Evaluate(String expression); + /// + /// Force recalculation of all cell formulas. + /// + void RecalculateAllFormulas(); + IXLCells FindCells(Func predicate); IXLColumns FindColumns(Func predicate); diff --git a/ClosedXML/Excel/IXLWorksheet.cs b/ClosedXML/Excel/IXLWorksheet.cs index b5aecd7..d761576 100644 --- a/ClosedXML/Excel/IXLWorksheet.cs +++ b/ClosedXML/Excel/IXLWorksheet.cs @@ -430,6 +430,11 @@ Object Evaluate(String expression); + /// + /// Force recalculation of all cell formulas. + /// + void RecalculateAllFormulas(); + String Author { get; set; } IXLPictures Pictures { get; } diff --git a/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/Excel/XLWorkbook.cs index 4d5e1de..6fbe11c 100644 --- a/ClosedXML/Excel/XLWorkbook.cs +++ b/ClosedXML/Excel/XLWorkbook.cs @@ -119,7 +119,22 @@ public XLEventTracking EventTracking { get; set; } - #region Nested Type: XLLoadSource + /// + /// Counter increasing at workbook data change. Serves to determine if the cell formula + /// has to be recalculated. + /// + internal long RecalculationCounter { get; private set; } + + /// + /// Notify that workbook data has been changed which means that cached formula values + /// need to be re-evaluated. + /// + internal void InvalidateFormulas() + { + RecalculationCounter++; + } + + #region Nested Type : XLLoadSource private enum XLLoadSource { @@ -826,6 +841,15 @@ return CalcEngine.Evaluate(expression); } + /// + /// Force recalculation of all cell formulas. + /// + public void RecalculateAllFormulas() + { + InvalidateFormulas(); + Worksheets.ForEach(sheet => sheet.RecalculateAllFormulas()); + } + private static XLCalcEngine _calcEngineExpr; private SpreadsheetDocumentType _spreadsheetDocumentType; diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index 5c694a8..2934e39 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -4863,7 +4863,20 @@ cell.CellFormula.Text = formula; } - cell.CellValue = null; + if (!evaluateFormulae || xlCell.CachedValue == null || xlCell.NeedsRecalculation) + cell.CellValue = null; + else + { + string valueCalculated; + if (xlCell.CachedValue is int) + valueCalculated = ((int)xlCell.CachedValue).ToInvariantString(); + else if (xlCell.CachedValue is double) + valueCalculated = ((double)xlCell.CachedValue).ToInvariantString(); + else + valueCalculated = xlCell.CachedValue.ToString(); + + cell.CellValue = new CellValue(valueCalculated); + } } else if (tableTotalCells.Contains(xlCell.Address)) { diff --git a/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/Excel/XLWorksheet.cs index 2f02040..e35f2e4 100644 --- a/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/Excel/XLWorksheet.cs @@ -1463,7 +1463,7 @@ private XLCalcEngine _calcEngine; - private XLCalcEngine CalcEngine + internal XLCalcEngine CalcEngine { get { return _calcEngine ?? (_calcEngine = new XLCalcEngine(this)); } } @@ -1473,6 +1473,14 @@ return CalcEngine.Evaluate(expression); } + /// + /// Force recalculation of all cell formulas. + /// + public void RecalculateAllFormulas() + { + CellsUsed().Cast().ForEach(cell => cell.Evaluate(true)); + } + public String Author { get; set; } public override string ToString() diff --git a/ClosedXML/Excel/XLWorksheets.cs b/ClosedXML/Excel/XLWorksheets.cs index 14c22bd..0fd1528 100644 --- a/ClosedXML/Excel/XLWorksheets.cs +++ b/ClosedXML/Excel/XLWorksheets.cs @@ -133,6 +133,7 @@ _worksheets.RemoveAll(w => w.Position == position); _worksheets.Values.Where(w => w.Position > position).ForEach(w => w._position -= 1); _workbook.UnsupportedSheets.Where(w => w.Position > position).ForEach(w => w.Position -= 1); + _workbook.InvalidateFormulas(); } IEnumerator IEnumerable.GetEnumerator() diff --git a/ClosedXML_Sandbox/PerformanceRunner.cs b/ClosedXML_Sandbox/PerformanceRunner.cs index d64e02b..84f30b6 100644 --- a/ClosedXML_Sandbox/PerformanceRunner.cs +++ b/ClosedXML_Sandbox/PerformanceRunner.cs @@ -135,5 +135,35 @@ return row; } + + public static void PerformHeavyCalculation() + { + int rows = 200; + int columns = 200; + using (var wb = new XLWorkbook()) + { + var sheet = wb.Worksheets.Add("TestSheet"); + var lastColumnLetter = sheet.Column(columns).ColumnLetter(); + for (int i = 1; i <= rows; i++) + { + for (int j = 1; j <= columns; j++) + { + if (i == 1) + { + sheet.Cell(i, j).FormulaA1 = string.Format("=ROUND({0}*SIN({0}),2)", j); + } + else + { + sheet.Cell(i, j).FormulaA1 = string.Format("=SUM({0}$1:{0}{1})/SUM($A{1}:${2}{1})", + sheet.Column(j).ColumnLetter(), i - 1, lastColumnLetter); // i.e. for K8 there will be =SUM(K$1:K7)/SUM($A7:$GR7) + } + } + } + + var cells = sheet.CellsUsed(); + var sum1 = cells.Sum(cell => (double)cell.Value); + Console.WriteLine("Total sum: {0:N2}", sum1); + } + } } } diff --git a/ClosedXML_Sandbox/Program.cs b/ClosedXML_Sandbox/Program.cs index 1f3f681..704ab40 100644 --- a/ClosedXML_Sandbox/Program.cs +++ b/ClosedXML_Sandbox/Program.cs @@ -16,6 +16,10 @@ Console.WriteLine("Running {0}", nameof(PerformanceRunner.RunInsertTable)); PerformanceRunner.TimeAction(PerformanceRunner.RunInsertTable); Console.WriteLine(); + + Console.WriteLine("Running {0}", nameof(PerformanceRunner.PerformHeavyCalculation)); + PerformanceRunner.TimeAction(PerformanceRunner.PerformHeavyCalculation); + Console.WriteLine(); #endif Console.WriteLine("Press any key to continue"); diff --git a/ClosedXML_Tests/Excel/CalcEngine/FormulaCachingTests.cs b/ClosedXML_Tests/Excel/CalcEngine/FormulaCachingTests.cs new file mode 100644 index 0000000..5ac60f4 --- /dev/null +++ b/ClosedXML_Tests/Excel/CalcEngine/FormulaCachingTests.cs @@ -0,0 +1,320 @@ +using ClosedXML.Excel; +using NUnit.Framework; +using System; +using System.Linq; + +namespace ClosedXML_Tests.Excel.CalcEngine +{ + [TestFixture] + public class FormulaCachingTests + { + [Test] + public void NewWorkbookDoesNotNeedRecalculation() + { + using (var wb = new XLWorkbook()) + { + var sheet = wb.Worksheets.Add("TestSheet"); + var cell = sheet.Cell(1, 1); + + Assert.AreEqual(0, wb.RecalculationCounter); + Assert.IsFalse(cell.NeedsRecalculation); + } + } + + [Test] + public void EditCellCausesCounterIncreasing() + { + using (var wb = new XLWorkbook()) + { + var sheet = wb.Worksheets.Add("TestSheet"); + var cell = sheet.Cell(1, 1); + cell.Value = "1234567"; + + Assert.Greater(wb.RecalculationCounter, 0); + } + } + + [Test] + public void StaticCellDoesNotNeedRecalculation() + { + using (var wb = new XLWorkbook()) + { + var sheet = wb.Worksheets.Add("TestSheet"); + var cell = sheet.Cell(1, 1); + cell.Value = "1234567"; + + Assert.IsFalse(cell.NeedsRecalculation); + } + } + + [Test] + public void EditCellInvalidatesDependentCells() + { + using (var wb = new XLWorkbook()) + { + var sheet = wb.Worksheets.Add("TestSheet"); + var cell = sheet.Cell(1, 1); + var dependentCell = sheet.Cell(2, 1); + dependentCell.FormulaA1 = "=A1"; + var _ = dependentCell.Value; + + cell.Value = "1234567"; + + Assert.IsTrue(dependentCell.NeedsRecalculation); + } + } + + + [Test] + public void EditFormulaA1InvalidatesDependentCells() + { + using (var wb = new XLWorkbook()) + { + var sheet = wb.Worksheets.Add("TestSheet"); + var a1 = sheet.Cell("A1"); + var a2 = sheet.Cell("A2"); + var a3 = sheet.Cell("A3"); + var a4 = sheet.Cell("A4"); + a2.FormulaA1 = "=A1*10"; + a3.FormulaA1 = "=A2*10"; + a4.FormulaA1 = "=SUM(A1:A3)"; + a1.Value = 15; + + var res1 = a4.Value; + a2.FormulaA1 = "=A1*20"; + var res2 = a4.Value; + + Assert.AreEqual(15 + 150 + 1500, res1); + Assert.AreEqual(15 + 300 + 3000, res2); + } + } + + + [Test] + public void EditFormulaR1C1InvalidatesDependentCells() + { + using (var wb = new XLWorkbook()) + { + var sheet = wb.Worksheets.Add("TestSheet"); + var a1 = sheet.Cell("A1"); + var a2 = sheet.Cell("A2"); + var a3 = sheet.Cell("A3"); + var a4 = sheet.Cell("A4"); + a2.FormulaA1 = "=A1*10"; + a3.FormulaA1 = "=A2*10"; + a4.FormulaA1 = "=SUM(A1:A3)"; + a1.Value = 15; + + var res1 = a4.Value; + a2.FormulaR1C1 = "=R[-1]C*2"; + var res2 = a4.Value; + + Assert.AreEqual(15 + 150 + 1500, res1); + Assert.AreEqual(15 + 30 + 300, res2); + } + } + [Test] + public void InsertRowInvalidatesValues() + { + using (var wb = new XLWorkbook()) + { + var sheet = wb.Worksheets.Add("TestSheet"); + var a4 = sheet.Cell("A4"); + a4.FormulaA1 = "=COUNTBLANK(A1:A3)"; + + var res1 = a4.Value; + sheet.Row(2).InsertRowsAbove(2); + var res2 = a4.Value; + + Assert.AreEqual(3, res1); + Assert.AreEqual(5, res2); + } + } + + + [Test] + public void DeleteRowInvalidatesValues() + { + using (var wb = new XLWorkbook()) + { + var sheet = wb.Worksheets.Add("TestSheet"); + var a4 = sheet.Cell("A4"); + a4.FormulaA1 = "=COUNTBLANK(A1:A3)"; + + var res1 = a4.Value; + sheet.Row(2).Delete(); + var res2 = a4.Value; + + Assert.AreEqual(3, res1); + Assert.AreEqual(2, res2); + } + } + + [Test] + public void ChainedCalculationPreservesIntermediateValues() + { + using (var wb = new XLWorkbook()) + { + var sheet = wb.Worksheets.Add("TestSheet"); + var a1 = sheet.Cell("A1"); + var a2 = sheet.Cell("A2"); + var a3 = sheet.Cell("A3"); + var a4 = sheet.Cell("A4"); + a2.FormulaA1 = "=A1*10"; + a3.FormulaA1 = "=A2*10"; + a4.FormulaA1 = "=SUM(A1:A3)"; + + a1.Value = 15; + var res = a4.Value; + + Assert.AreEqual(15 + 150 + 1500, res); + Assert.IsFalse(a4.NeedsRecalculation); + Assert.IsFalse(a3.NeedsRecalculation); + Assert.IsFalse(a2.NeedsRecalculation); + Assert.AreEqual(150, a2.CachedValue); + Assert.AreEqual(1500, a3.CachedValue); + Assert.AreEqual(15 + 150 + 1500, a4.CachedValue); + } + } + + [Test] + public void EditingAffectsDependentCells() + { + using (var wb = new XLWorkbook()) + { + var sheet = wb.Worksheets.Add("TestSheet"); + var a1 = sheet.Cell("A1"); + var a2 = sheet.Cell("A2"); + var a3 = sheet.Cell("A3"); + var a4 = sheet.Cell("A4"); + a2.FormulaA1 = "=A1*10"; + a3.FormulaA1 = "=A2*10"; + a4.FormulaA1 = "=SUM(A1:A3)"; + a1.Value = 15; + + var res1 = a4.Value; + a1.Value = 20; + var res2 = a4.Value; + + Assert.AreEqual(15 + 150 + 1500, res1); + Assert.AreEqual(20 + 200 + 2000, res2); + } + } + + + [Test] + [TestCase("C4", new string[] {"C5"})] + [TestCase("D4", new string[] { })] + [TestCase("A1", new string[] {"A2", "A3", "A4", "C1", "C2", "C3", "C5" })] + [TestCase("B2", new string[] {"B3", "B4", "C2", "C3", "C5" })] + [TestCase("C2", new string[] {"C5" })] + public void EditingDoesNotAffectNonDependingCells(string changedCell, string[] affectedCells) + { + using (var wb = new XLWorkbook()) + { + var sheet = wb.Worksheets.Add("TestSheet"); + sheet.Cell("A2").FormulaA1 = "A1+1"; + sheet.Cell("A3").FormulaA1 = "SUM(A1:A2)"; + sheet.Cell("A4").FormulaA1 = "SUM(A1:A3)"; + sheet.Cell("B2").FormulaA1 = "B1+1"; + sheet.Cell("B3").FormulaA1 = "SUM(B1:B2)"; + sheet.Cell("B4").FormulaA1 = "SUM(B1:B3)"; + sheet.Cell("C1").FormulaA1 = "SUM(A1:B1)"; + sheet.Cell("C2").FormulaA1 = "SUM(A2:B2)"; + sheet.Cell("C3").FormulaA1 = "SUM(A3:B3)"; + sheet.Cell("C5").FormulaA1 = "SUM($A$1:$C$4)"; + sheet.RecalculateAllFormulas(); + var allCells = sheet.CellsUsed(); + + sheet.Cell(changedCell).Value = 100; + var modifiedCells = allCells.Where(cell => cell.NeedsRecalculation); + + Assert.AreEqual(affectedCells.Length, modifiedCells.Count()); + foreach (var cellAddress in affectedCells) + { + Assert.IsTrue(modifiedCells.Any(cell => cell.Address.ToString() == cellAddress), + string.Format("Cell {0} is expected to need recalculation, but it does not", cellAddress)); + } + } + } + + [Test] + public void CircularReferenceFailsCalculating() + { + using (var wb = new XLWorkbook()) + { + var sheet = wb.Worksheets.Add("TestSheet"); + var a1 = sheet.Cell("A1"); + var a2 = sheet.Cell("A2"); + var a3 = sheet.Cell("A3"); + var a4 = sheet.Cell("A4"); + + a2.FormulaA1 = "=A1*10"; + a3.FormulaA1 = "=A2*10"; + a4.FormulaA1 = "=A3*10"; + a1.FormulaA1 = "A2+A3+A4"; + + var getValueA1 = new TestDelegate(() => { var v = a1.Value; }); + var getValueA2 = new TestDelegate(() => { var v = a2.Value; }); + var getValueA3 = new TestDelegate(() => { var v = a3.Value; }); + var getValueA4 = new TestDelegate(() => { var v = a4.Value; }); + + Assert.Throws(typeof(InvalidOperationException), getValueA1); + Assert.Throws(typeof(InvalidOperationException), getValueA2); + Assert.Throws(typeof(InvalidOperationException), getValueA3); + Assert.Throws(typeof(InvalidOperationException), getValueA4); + } + } + + + [Test] + public void CircularReferenceRecalculationNeededDoesNotFail() + { + using (var wb = new XLWorkbook()) + { + var sheet = wb.Worksheets.Add("TestSheet"); + var a1 = sheet.Cell("A1"); + var a2 = sheet.Cell("A2"); + var a3 = sheet.Cell("A3"); + var a4 = sheet.Cell("A4"); + + a2.FormulaA1 = "=A1*10"; + a3.FormulaA1 = "=A2*10"; + a4.FormulaA1 = "=A3*10"; + var _ = a4.Value; + a1.FormulaA1 = "=SUM(A2:A4)"; + + var recalcNeededA1 = a1.NeedsRecalculation; + var recalcNeededA2 = a2.NeedsRecalculation; + var recalcNeededA3 = a3.NeedsRecalculation; + var recalcNeededA4 = a4.NeedsRecalculation; + + Assert.IsTrue(recalcNeededA1); + Assert.IsTrue(recalcNeededA2); + Assert.IsTrue(recalcNeededA3); + Assert.IsTrue(recalcNeededA4); + } + } + + [Test] + public void DeleteWorksheetInvalidatesValues() + { + using (var wb = new XLWorkbook()) + { + var sheet1 = wb.Worksheets.Add("Sheet1"); + var sheet2 = wb.Worksheets.Add("Sheet2"); + var sheet1_a1 = sheet1.Cell("A1"); + var sheet2_a1 = sheet2.Cell("A1"); + sheet1_a1.FormulaA1 = "Sheet2!A1"; + sheet2_a1.Value = "TestValue"; + + var val1 = sheet1_a1.Value; + sheet2.Delete(); + var getValue = new TestDelegate(() => { var val2 = sheet1_a1.Value; }); + + Assert.AreEqual("TestValue", val1.ToString()); + Assert.Throws(typeof(ArgumentOutOfRangeException), getValue); + } + } + } +} diff --git a/ClosedXML_Tests/Excel/CalcEngine/PrecedentCellsTests.cs b/ClosedXML_Tests/Excel/CalcEngine/PrecedentCellsTests.cs new file mode 100644 index 0000000..197033d --- /dev/null +++ b/ClosedXML_Tests/Excel/CalcEngine/PrecedentCellsTests.cs @@ -0,0 +1,86 @@ +using ClosedXML.Excel; +using NUnit.Framework; +using System.IO; +using System.Linq; + +namespace ClosedXML_Tests.Excel.CalcEngine +{ + [TestFixture] + public class PrecedentCellsTests + { + [Test] + public void GetPrecedentRangesPreventsDuplication() + { + using (var ms = new MemoryStream()) + { + using (XLWorkbook wb = new XLWorkbook()) + { + var sheet1 = wb.AddWorksheet("Sheet1") as XLWorksheet; + var sheet2 = wb.AddWorksheet("Sheet2"); + var formula = "=MAX(A2:E2)/COUNTBLANK(A2:E2)*MAX(B1:C3)+SUM(Sheet2!B1:C3)+SUM($A$2:$E$2)+A2+B$2+$C$2"; + + var ranges = sheet1.CalcEngine.GetPrecedentRanges(formula); + + Assert.AreEqual(6, ranges.Count()); + Assert.IsTrue(ranges.Any(r => r.RangeAddress.Worksheet.Name == "Sheet1" && r.RangeAddress.ToString() == "A2:E2")); + Assert.IsTrue(ranges.Any(r => r.RangeAddress.Worksheet.Name == "Sheet1" && r.RangeAddress.ToString() == "B1:C3")); + Assert.IsTrue(ranges.Any(r => r.RangeAddress.Worksheet.Name == "Sheet2" && r.RangeAddress.ToString() == "B1:C3")); + Assert.IsTrue(ranges.Any(r => r.RangeAddress.Worksheet.Name == "Sheet1" && r.RangeAddress.ToString() == "A2:A2")); + Assert.IsTrue(ranges.Any(r => r.RangeAddress.Worksheet.Name == "Sheet1" && r.RangeAddress.ToString() == "B$2:B$2")); + Assert.IsTrue(ranges.Any(r => r.RangeAddress.Worksheet.Name == "Sheet1" && r.RangeAddress.ToString() == "$C$2:$C$2")); + } + } + } + + [Test] + public void GetPrecedentRangesDealsWithNamedRanges() + { + using (var ms = new MemoryStream()) + { + using (XLWorkbook wb = new XLWorkbook()) + { + var sheet1 = wb.AddWorksheet("Sheet1") as XLWorksheet; + sheet1.NamedRanges.Add("NAMED_RANGE", sheet1.Range("A2:B3")); + var formula = "=SUM(NAMED_RANGE)"; + + var ranges = sheet1.CalcEngine.GetPrecedentRanges(formula); + + Assert.AreEqual(1, ranges.Count()); + Assert.AreEqual("$A$2:$B$3", ranges.First().RangeAddress.ToString()); + } + } + } + + [Test] + public void GetPrecedentCells() + { + using (var ms = new MemoryStream()) + { + using (XLWorkbook wb = new XLWorkbook()) + { + var sheet1 = wb.AddWorksheet("Sheet1") as XLWorksheet; + var sheet2 = wb.AddWorksheet("Sheet2"); + var formula = "=MAX(A2:E2)/COUNTBLANK(A2:E2)*MAX(B1:C3)+SUM(Sheet2!B1:C3)+SUM($A$2:$E$2)+A2+B$2+$C$2"; + var expectedAtSheet1 = new string[] + { "A2", "B2", "C2", "D2", "E2", "B1", "C1", "B3", "C3" }; + var expectedAtSheet2 = new string[] + { "B1", "C1", "B2", "C2", "B3", "C3" }; + + var cells = sheet1.CalcEngine.GetPrecedentCells(formula); + + Assert.AreEqual(15, cells.Count()); + foreach (var address in expectedAtSheet1) + { + Assert.IsTrue(cells.Any(cell => cell.Address.Worksheet.Name == sheet1.Name && cell.Address.ToString() == address), + string.Format("Address {0}!{1} is not presented", sheet1.Name, address)); + } + foreach (var address in expectedAtSheet2) + { + Assert.IsTrue(cells.Any(cell => cell.Address.Worksheet.Name == sheet2.Name && cell.Address.ToString() == address), + string.Format("Address {0}!{1} is not presented", sheet2.Name, address)); + } + } + } + } + } +} diff --git a/ClosedXML_Tests/Excel/Loading/LoadingTests.cs b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs index d62b9f2..16ec9c6 100644 --- a/ClosedXML_Tests/Excel/Loading/LoadingTests.cs +++ b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs @@ -312,5 +312,38 @@ } } } + + [Test] + [TestCase("A1*10", "1230")] + [TestCase("A1/10", "12.3")] + [TestCase("A1&\" cells\"", "123 cells")] + [TestCase("A1&\"000\"", "123000")] + [TestCase("ISNUMBER(A1)", "True")] + [TestCase("ISBLANK(A1)", "False")] + [TestCase("DATE(2018,1,28)", "43128")] + public void LoadFormulaCachedValue(string formula, object expectedValue) + { + using (var ms = new MemoryStream()) + { + using (XLWorkbook book1 = new XLWorkbook()) + { + var sheet = book1.AddWorksheet("sheet1"); + sheet.Cell("A1").Value = 123; + sheet.Cell("A2").FormulaA1 = formula; + var options = new SaveOptions { EvaluateFormulasBeforeSaving = true }; + + book1.SaveAs(ms, options); + } + ms.Position = 0; + + using (XLWorkbook book2 = new XLWorkbook(ms)) + { + var ws = book2.Worksheet(1); + var storedValueA2 = ws.Cell("A2").ValueCached; + Assert.IsTrue(ws.Cell("A2").NeedsRecalculation); + Assert.AreEqual(expectedValue, storedValueA2); + } + } + } } } diff --git a/ClosedXML_Tests/Excel/Saving/SavingTests.cs b/ClosedXML_Tests/Excel/Saving/SavingTests.cs index e0fa221..c617d41 100644 --- a/ClosedXML_Tests/Excel/Saving/SavingTests.cs +++ b/ClosedXML_Tests/Excel/Saving/SavingTests.cs @@ -119,6 +119,59 @@ } [Test] + public void NotSaveCachedValueWhenFlagIsFalse() + { + using (var ms = new MemoryStream()) + { + using (XLWorkbook book1 = new XLWorkbook()) + { + var sheet = book1.AddWorksheet("sheet1"); + sheet.Cell("A1").Value = 123; + sheet.Cell("A2").FormulaA1 = "A1*10"; + book1.RecalculateAllFormulas(); + var options = new SaveOptions { EvaluateFormulasBeforeSaving = false }; + + book1.SaveAs(ms, options); + } + ms.Position = 0; + + using (XLWorkbook book2 = new XLWorkbook(ms)) + { + var ws = book2.Worksheet(1); + var storedValue = ws.Cell("A2").ValueCached; + + Assert.IsNull(storedValue); + } + } + } + + [Test] + public void SaveCachedValueWhenFlagIsTrue() + { + using (var ms = new MemoryStream()) + { + using (XLWorkbook book1 = new XLWorkbook()) + { + var sheet = book1.AddWorksheet("sheet1"); + sheet.Cell("A1").Value = 123; + sheet.Cell("A2").FormulaA1 = "A1*10"; + var options = new SaveOptions { EvaluateFormulasBeforeSaving = true }; + + book1.SaveAs(ms, options); + } + ms.Position = 0; + + using (XLWorkbook book2 = new XLWorkbook(ms)) + { + var ws = book2.Worksheet(1); + var storedValue = ws.Cell("A2").ValueCached; + + Assert.AreEqual("1230", storedValue); + } + } + } + + [Test] public void CanSaveAsCopyReadOnlyFile() { using (var original = new TemporaryFile())