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);
}