/// <summary>
/// Parse an XML sum formula and returns a SumFormulaObject
/// </summary>
/// <remarks>
/// Only sums in the format =SUM(R[-1]C:R[-1]C) is currently match to lock down the system
/// </remarks>
private ExcelSumFormula ParseSumFormula(string formula, int FormulaRow)
{
//this patter will match e.g. =SUM(R[-1]C:R[-1]C) and return 2 groups, one for the first row reference and one for the second, without sign
string pattern = @"=SUM\(R\[-(\d+)\]C:R\[-(\d+)\]C\)";
Regex r = new Regex(pattern, RegexOptions.IgnoreCase);
MatchCollection mc = r.Matches(formula);
//return null if formula doesnt match
if (mc.Count != 1)
{
return(null);
}
//parse FromRow and ToRow
Match m = mc[0]; //We've alread checked that the MatchCollection only contains one match, so this is safe
string sFromRow = m.Groups[1].Value;
int iFromRow = int.Parse(sFromRow);
string sToRow = m.Groups[2].Value;
int iToRow = int.Parse(sToRow);
ExcelSumFormula oExcelSumFormula = new ExcelSumFormula();
oExcelSumFormula.FormulaRow = FormulaRow;
oExcelSumFormula.FromRelativeRow = iFromRow;
oExcelSumFormula.ToRelativeRow = iToRow;
return(oExcelSumFormula);
}
/// <summary>
/// Update sum formulas
/// </summary>
/// <param name="InsertRowNumber"></param>
private void UpdateSumFormulaRange(int InsertRowNumber)
{
//first, find all rows, the loop through all rows to find formulas
//we need to do it this way to know what the row number is
XmlNodeList AllRows;
AllRows = xmlDoc.SelectNodes(".//ss:Row", nsmgr); // gets the cell with the named cell name
for (int RowIndex = 0; RowIndex < AllRows.Count; RowIndex++)
{
//get row
XmlNode CurrentRow = AllRows[RowIndex];
//select cells with formulas
XmlNodeList CellsWithFormulas = CurrentRow.SelectNodes(".//ss:Cell[@ss:Formula]", nsmgr);
//for each formula, check if it contains a relative reference, pattern "=R[-5]C[-4]/R[-10]C10", e.g. R[-5]C[-4]
foreach (XmlNode currentCell in CellsWithFormulas)
{
//get formula
string formula = currentCell.Attributes["ss:Formula"].Value;
int FormulaRow = RowIndex + 1; //RowIndex is zero based, Excel Rows are one-based
ExcelSumFormula oExcelFormula = ParseSumFormula(formula, FormulaRow);
//if formula is a sum formula, updates if necessary
if (oExcelFormula != null)
{
ExcelSumFormula oUpdatedExcelFormula = UpdateSumFormula(oExcelFormula, InsertRowNumber);
currentCell.Attributes["ss:Formula"].Value = oUpdatedExcelFormula.GetFormula();
}
}
}
}
/// <summary>
/// Updates the sum formula range if a new row is added just below the range or inside the range
/// </summary>
/// <param name="oExcelSumFormula"></param>
/// <param name="InsertRow"></param>
/// <returns></returns>
private ExcelSumFormula UpdateSumFormula(ExcelSumFormula oExcelSumFormula, int InsertRow)
{
Boolean ExpandRange = false;
//if new row is inserted just below range, expand range
//Example a. Range is from 5:5, new row inserted at row 5, range exanded to 4:5
//Example a. Range is from 2:3, new row inserted at row 4, range exanded to 2:4
if (InsertRow == oExcelSumFormula.ToAbsoluteRow)
{
ExpandRange = true;
}
if (ExpandRange)
{
oExcelSumFormula.FromRelativeRow = oExcelSumFormula.FromRelativeRow + 1;
}
return(oExcelSumFormula);
}
