internal void RecalcCellReferences(SheetChange sheetChange) { foreach (var w in this.Workbook.Worksheets) { w.RecalcCellReferences(sheetChange); } }
public void RecalcCellReferences(SheetChange sheetChange) { foreach (var i in this.Cells()) { var c = i.Value; if (c.Formula != null) { c.Formula.Text = ExcelFormula.TranslateForSheetChange(c.Formula.Text, sheetChange, _wsheet.Name); c.Formula.R1 = ExcelRange.TranslateForSheetChange(c.Formula.R1, sheetChange, _wsheet.Name); c.Formula.R2 = ExcelRange.TranslateForSheetChange(c.Formula.R2, sheetChange, _wsheet.Name); c.Formula.Reference = ExcelRange.TranslateForSheetChange(c.Formula.Reference, sheetChange, _wsheet.Name); } } // Adjust conditional formatting List <ConditionalFormatting> cfToRemoveList = new List <ConditionalFormatting>(); foreach (var cf in this.GetElements <ConditionalFormatting>()) { bool removeCf = false; List <StringValue> lst = new List <StringValue>(); foreach (var sqrefItem in cf.SequenceOfReferences.Items) { string newRef = ExcelRange.TranslateForSheetChange(sqrefItem.Value, sheetChange, _wsheet.Name); if (!newRef.StartsWith("#")) // no error { lst.Add(new StringValue(newRef)); } else { cfToRemoveList.Add(cf); removeCf = true; break; } } if (removeCf) { break; } cf.SequenceOfReferences = new ListValue <StringValue>(lst); foreach (var f in cf.Descendants <Formula>()) { f.Text = ExcelFormula.TranslateForSheetChange(f.Text, sheetChange, _wsheet.Name); } } foreach (ConditionalFormatting cf in cfToRemoveList) { this.RemoveElement(cf); } }
/// <summary> /// Translate a fornula due to a sheet change, e.g. insertion of rows. /// </summary> /// <param name="formula">Formula, e.g. SUM(A1,Sheet2!B2)</param> /// <param name="sheetChange">Details of change</param> /// <param name="currentSheetName">The sheet where the range is, to determine if this range is affected. If sheetChange.SheetName is null and currentSheetName is null, translation is always applied.</param> /// <param name="currentSheetName">The sheet where the range is, to determine if this range is affected. If sheetChange.SheetName is null and currentSheetName is null, translation is always applied.</param> /// <returns></returns> public static string TranslateForSheetChange(string formula, SheetChange sheetChange, string currentSheetName) { if (formula == null) { return(null); } ParseTree tree = ExcelFormula.Parse(formula); StringBuilder rebuilt = new StringBuilder(); if (tree.Errors.Count > 0) { throw new ArgumentException("Error in parsing formula"); } BuildTranslated(rebuilt, tree, n => TranslateRangeParseNodeForSheetChange(n, sheetChange, currentSheetName)); return(rebuilt.ToString()); }
public void PushRows(uint rowStart, int qty) { if (qty < 1) { throw new ArgumentException("Quantity cannot be less than 0"); } if (qty == 0) { return; } _sheetCache.InsertOrDeleteRows(rowStart, qty, false); SheetChange sheetChange = new SheetChange() { SheetName = this.Name, RowStart = rowStart, RowDelta = qty }; this.Document.RecalcCellReferences(sheetChange); }
public void InsertColumns(uint colStart, int qty) { if (qty < 1) { throw new ArgumentException("Quantity cannot be less than 0"); } if (qty == 0) { return; } _sheetCache.InsertOrDeleteColumns(colStart, qty, true); SheetChange sheetChange = new SheetChange() { SheetName = this.Name, ColumnStart = colStart, ColumnDelta = qty }; this.Document.RecalcCellReferences(sheetChange); }
/// <summary> /// Translate a range due to a sheet change, e.g. insertion of rows. /// </summary> /// <param name="range">Range, e.g. A1, Sheet1!A1</param> /// <param name="sheetChange">Details of change</param> /// <param name="currentSheetName">The sheet where the range is, to determine if this range is affected. If sheetChange.SheetName is null and currentSheetName is null, translation is always applied.</param> /// <returns></returns> public static string TranslateForSheetChange(string range, SheetChange sheetChange, string currentSheetName) { if (range == null) { return(null); } RangeComponents er = ExcelRange.Parse(range); if ((er.SheetName == "" && currentSheetName == sheetChange.SheetName) || er.SheetName == sheetChange.SheetName) { return(TranslateInternal(er, sheetChange.RowStart, sheetChange.ColumnStart, sheetChange.RowDelta, sheetChange.ColumnDelta, false // Don't allow absolute refs i.e. $ to affect translate )); } else { return(range); } }
private static string TranslateRangeParseNodeForSheetChange(ParseNode rangeNode, SheetChange sheetChange, string currentSheetName) { string range = ""; foreach (ParseNode sub in rangeNode.Nodes) { range += sub.Token.Text; } return(ExcelRange.TranslateForSheetChange(range, sheetChange, currentSheetName)); }
internal void RecalcCellReferences(SheetChange sheetChange) { _sheetCache.RecalcCellReferences(sheetChange); }