/** * Updates named ranges due to moving of cells */ public void UpdateNamesAfterCellShift(FormulaShifter shifter) { for (int i = 0; i < NumNames; ++i) { NameRecord nr = GetNameRecord(i); Ptg[] ptgs = nr.NameDefinition; if (shifter.AdjustFormula(ptgs, nr.SheetNumber)) { nr.NameDefinition = ptgs; } } }
public void UpdateFormulasAfterRowShift(FormulaShifter shifter, int currentExternSheetIndex) { for (int i = 0; i < records.Length; i++) { CellValueRecordInterface[] rowCells = records[i]; if (rowCells == null) { continue; } for (int j = 0; j < rowCells.Length; j++) { CellValueRecordInterface cell = rowCells[j]; if (cell is FormulaRecordAggregate) { FormulaRecord fr = ((FormulaRecordAggregate)cell).FormulaRecord; Ptg[] ptgs = fr.ParsedExpression; // needs clone() inside this getter? if (shifter.AdjustFormula(ptgs, currentExternSheetIndex)) { fr.ParsedExpression = (ptgs); } } } } }
/** * Updates formulas in cells and conditional formats due to moving of cells * @param externSheetIndex the externSheet index of this sheet */ public void UpdateFormulasAfterCellShift(FormulaShifter shifter, int externSheetIndex) { RowsAggregate.UpdateFormulasAfterRowShift(shifter, externSheetIndex); if (condFormatting != null) { ConditionalFormattingTable.UpdateFormulasAfterCellShift(shifter, externSheetIndex); } // TODO - adjust data validations }
public void UpdateFormulasAfterRowShift(FormulaShifter formulaShifter, int currentExternSheetIndex) { _valuesAgg.UpdateFormulasAfterRowShift(formulaShifter, currentExternSheetIndex); }
public void UpdateConditionalFormatting(FormulaShifter Shifter) { IWorkbook wb = sheet.Workbook; int sheetIndex = wb.GetSheetIndex(sheet); XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb); CT_Worksheet ctWorksheet = sheet.GetCTWorksheet(); List<CT_ConditionalFormatting> conditionalFormattingArray = ctWorksheet.conditionalFormatting; // iterate backwards due to possible calls to ctWorksheet.removeConditionalFormatting(j) for (int j = conditionalFormattingArray.Count - 1; j >= 0; j--) { CT_ConditionalFormatting cf = conditionalFormattingArray[j]; List<CellRangeAddress> cellRanges = new List<CellRangeAddress>(); String[] regions = cf.sqref.ToString().Split(new char[] { ' ' }); for (int i = 0; i < regions.Length; i++) { cellRanges.Add(CellRangeAddress.ValueOf(regions[i])); } bool Changed = false; List<CellRangeAddress> temp = new List<CellRangeAddress>(); for (int i = 0; i < cellRanges.Count; i++) { CellRangeAddress craOld = cellRanges[i]; CellRangeAddress craNew = ShiftRange(Shifter, craOld, sheetIndex); if (craNew == null) { Changed = true; continue; } temp.Add(craNew); if (craNew != craOld) { Changed = true; } } if (Changed) { int nRanges = temp.Count; if (nRanges == 0) { conditionalFormattingArray.RemoveAt(j); continue; } string refs = string.Empty; foreach (CellRangeAddress a in temp) { if (refs.Length == 0) refs = a.FormatAsString(); else refs += " " + a.FormatAsString(); } cf.sqref = refs; } foreach (CT_CfRule cfRule in cf.cfRule) { List<String> formulas = cfRule.formula; for (int i = 0; i < formulas.Count; i++) { String formula = formulas[i]; Ptg[] ptgs = FormulaParser.Parse(formula, fpb, FormulaType.Cell, sheetIndex); if (Shifter.AdjustFormula(ptgs, sheetIndex)) { String ShiftedFmla = FormulaRenderer.ToFormulaString(fpb, ptgs); formulas[i] = ShiftedFmla; } } } } }
private static CellRangeAddress ShiftRange(FormulaShifter Shifter, CellRangeAddress cra, int currentExternSheetIx) { // FormulaShifter works well in terms of Ptgs - so convert CellRangeAddress to AreaPtg (and back) here AreaPtg aptg = new AreaPtg(cra.FirstRow, cra.LastRow, cra.FirstColumn, cra.LastColumn, false, false, false, false); Ptg[] ptgs = { aptg, }; if (!Shifter.AdjustFormula(ptgs, currentExternSheetIx)) { return cra; } Ptg ptg0 = ptgs[0]; if (ptg0 is AreaPtg) { AreaPtg bptg = (AreaPtg)ptg0; return new CellRangeAddress(bptg.FirstRow, bptg.LastRow, bptg.FirstColumn, bptg.LastColumn); } if (ptg0 is AreaErrPtg) { return null; } throw new InvalidOperationException("Unexpected Shifted ptg class (" + ptg0.GetType().Name + ")"); }
private void updateRowFormulas(XSSFRow row, FormulaShifter Shifter) { foreach (ICell c in row) { XSSFCell cell = (XSSFCell)c; CT_Cell ctCell = cell.GetCTCell(); if (ctCell.IsSetF()) { CT_CellFormula f = ctCell.f; String formula = f.Value; if (formula.Length > 0) { String ShiftedFormula = ShiftFormula(row, formula, Shifter); if (ShiftedFormula != null) { f.Value = (ShiftedFormula); if (f.t == ST_CellFormulaType.shared) { int si = (int)f.si; CT_CellFormula sf = ((XSSFSheet)row.Sheet).GetSharedFormula(si); sf.Value = (ShiftedFormula); } } } if (f.isSetRef()) { //Range of cells which the formula applies to. String ref1 = f.@ref; String ShiftedRef = ShiftFormula(row, ref1, Shifter); if (ShiftedRef != null) f.@ref = ShiftedRef; } } } }
/** * Shift a formula using the supplied FormulaShifter * * @param row the row of the cell this formula belongs to. Used to get a reference to the parent workbook. * @param formula the formula to shift * @param Shifter the FormulaShifter object that operates on the Parsed formula tokens * @return the Shifted formula if the formula was Changed, * <code>null</code> if the formula wasn't modified */ private static String ShiftFormula(XSSFRow row, String formula, FormulaShifter Shifter) { ISheet sheet = row.Sheet; IWorkbook wb = sheet.Workbook; int sheetIndex = wb.GetSheetIndex(sheet); XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb); try { Ptg[] ptgs = FormulaParser.Parse(formula, fpb, FormulaType.Cell, sheetIndex); String ShiftedFmla = null; if (Shifter.AdjustFormula(ptgs, sheetIndex)) { ShiftedFmla = FormulaRenderer.ToFormulaString(fpb, ptgs); } return ShiftedFmla; } catch (FormulaParseException fpe) { // Log, but don't change, rather than breaking Console.WriteLine("Error shifting formula on row {0}, {1}", row.RowNum, fpe); return formula; } }
/** * Update formulas. */ public void UpdateFormulas(FormulaShifter shifter) { //update formulas on the parent sheet UpdateSheetFormulas(sheet, shifter); //update formulas on other sheets IWorkbook wb = sheet.Workbook; foreach (XSSFSheet sh in wb) { if (sheet == sh) continue; UpdateSheetFormulas(sh, shifter); } }
private void UpdateSheetFormulas(XSSFSheet sh, FormulaShifter Shifter) { foreach (IRow r in sh) { XSSFRow row = (XSSFRow)r; updateRowFormulas(row, Shifter); } }
/** * Updated named ranges */ public void UpdateNamedRanges(FormulaShifter shifter) { IWorkbook wb = sheet.Workbook; XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb); for (int i = 0; i < wb.NumberOfNames; i++) { IName name = wb.GetNameAt(i); String formula = name.RefersToFormula; int sheetIndex = name.SheetIndex; Ptg[] ptgs = FormulaParser.Parse(formula, fpb, FormulaType.NamedRange, sheetIndex); if (shifter.AdjustFormula(ptgs, sheetIndex)) { String shiftedFmla = FormulaRenderer.ToFormulaString(fpb, ptgs); name.RefersToFormula = shiftedFmla; } } }
/** * @return <c>false</c> if this whole {@link CFHeaderRecord} / {@link CFRuleRecord}s should be deleted */ public bool UpdateFormulasAfterCellShift(FormulaShifter shifter, int currentExternSheetIx) { CellRangeAddress[] cellRanges = header.CellRanges; bool changed = false; List<CellRangeAddress> temp = new List<CellRangeAddress>(); for (int i = 0; i < cellRanges.Length; i++) { CellRangeAddress craOld = cellRanges[i]; CellRangeAddress craNew = ShiftRange(shifter, craOld, currentExternSheetIx); if (craNew == null) { changed = true; continue; } temp.Add(craNew); if (craNew != craOld) { changed = true; } } if (changed) { int nRanges = temp.Count; if (nRanges == 0) { return false; } CellRangeAddress[] newRanges = new CellRangeAddress[nRanges]; newRanges = temp.ToArray(); header.CellRanges = (newRanges); } for (int i = 0; i < rules.Count; i++) { CFRuleRecord rule = rules[i]; Ptg[] ptgs; ptgs = rule.ParsedExpression1; if (ptgs != null && shifter.AdjustFormula(ptgs, currentExternSheetIx)) { rule.ParsedExpression1 = (ptgs); } ptgs = rule.ParsedExpression2; if (ptgs != null && shifter.AdjustFormula(ptgs, currentExternSheetIx)) { rule.ParsedExpression2 = (ptgs); } } return true; }
/** * Shift a formula using the supplied FormulaShifter * * @param row the row of the cell this formula belongs to. Used to get a reference to the parent workbook. * @param formula the formula to shift * @param Shifter the FormulaShifter object that operates on the Parsed formula tokens * @return the Shifted formula if the formula was Changed, * <code>null</code> if the formula wasn't modified */ private static String ShiftFormula(XSSFRow row, String formula, FormulaShifter Shifter) { ISheet sheet = row.Sheet; IWorkbook wb = sheet.Workbook; int sheetIndex = wb.GetSheetIndex(sheet); XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb); Ptg[] ptgs = FormulaParser.Parse(formula, fpb, FormulaType.Cell, sheetIndex); String ShiftedFmla = null; if (Shifter.AdjustFormula(ptgs, sheetIndex)) { ShiftedFmla = FormulaRenderer.ToFormulaString(fpb, ptgs); } return ShiftedFmla; }
public void UpdateConditionalFormatting(FormulaShifter Shifter) { IWorkbook wb = sheet.Workbook; int sheetIndex = wb.GetSheetIndex(sheet); XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb); List<CT_ConditionalFormatting> cfList = sheet.GetCTWorksheet().conditionalFormatting; for(int j = 0; cfList!=null&&j< cfList.Count; j++){ CT_ConditionalFormatting cf = cfList[j]; List<CellRangeAddress> cellRanges = new List<CellRangeAddress>(); foreach (Object stRef in cf.sqref) { String[] regions = stRef.ToString().Split(new char[]{' '}); for (int i = 0; i < regions.Length; i++) { cellRanges.Add(CellRangeAddress.ValueOf(regions[i])); } } bool Changed = false; List<CellRangeAddress> temp = new List<CellRangeAddress>(); for (int i = 0; i < cellRanges.Count; i++) { CellRangeAddress craOld = cellRanges[i]; CellRangeAddress craNew = ShiftRange(Shifter, craOld, sheetIndex); if (craNew == null) { Changed = true; continue; } temp.Add(craNew); if (craNew != craOld) { Changed = true; } } if (Changed) { int nRanges = temp.Count; if (nRanges == 0) { cfList.RemoveAt(j); continue; } List<String> refs = new List<String>(); foreach(CellRangeAddress a in temp) refs.Add(a.FormatAsString()); cf.sqref=(refs); } foreach(CT_CfRule cfRule in cf.cfRule){ List<String> formulas = cfRule.formula; for (int i = 0; i < formulas.Count; i++) { String formula = formulas[i]; Ptg[] ptgs = FormulaParser.Parse(formula, fpb, FormulaType.CELL, sheetIndex); if (Shifter.AdjustFormula(ptgs, sheetIndex)) { String ShiftedFmla = FormulaRenderer.ToFormulaString(fpb, ptgs); formulas[i] = ShiftedFmla; } } } } }
public void UpdateFormulasAfterCellShift(FormulaShifter shifter, int externSheetIndex) { for (int i = 0; i < _cfHeaders.Count; i++) { CFRecordsAggregate subAgg = (CFRecordsAggregate)_cfHeaders[i]; bool shouldKeep = subAgg.UpdateFormulasAfterCellShift(shifter, externSheetIndex); if (!shouldKeep) { _cfHeaders.RemoveAt(i); i--; } } }