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