@Test public void testRangeFromFormula() { Cell cellWithFormula = new Cell(); CTCellFormula value = new CTCellFormula(); value.setValue("A1*C1"); cellWithFormula.setF(value); Set<Range> ranges = Range.fromCellFormula("", cellWithFormula); System.out.println(ranges); Assert.assertEquals(2, ranges.size()); Assert.assertTrue(ranges.contains(Range.fromRange("", "A1:A1"))); Assert.assertTrue(ranges.contains(Range.fromRange("", "C1:C1"))); value.setValue("SUM(A1:C1)"); ranges = Range.fromCellFormula("", cellWithFormula); System.out.println(ranges); System.out.println(ranges); Assert.assertEquals(1, ranges.size()); Assert.assertTrue(ranges.contains(Range.fromRange("", "A1:C1"))); } }
private Cell cellWithFormula(String formula) { Cell cellWithFormula = new Cell(); CTCellFormula ctCellFormula = new CTCellFormula(); ctCellFormula.setValue(formula); cellWithFormula.setF(ctCellFormula); return cellWithFormula; } }
protected void updateFormula(Cell cellWithFormula, Range originalFormulaRange, Range formulaRange, CTCalcChain calculationChain, int formulaCount) { CTCellFormula formula = cellWithFormula.getF(); formula.setValue(formula.getValue().replace(originalFormulaRange.toRange(), formulaRange.toRange())); if (originalFormulaRange.isOneCellRange() && formulaRange.isOneCellRange()) { //here we check that there are no alpha-numeric symbols around the single reference String pattern = "(?<!\\w+)" + originalFormulaRange.toFirstCellReference() + "(?!\\w+)"; formula.setValue(formula.getValue().replaceAll(pattern, formulaRange.toFirstCellReference())); } if (calculationChain != null) { CTCalcCell calcCell = new CTCalcCell(); calcCell.setR(cellWithFormula.getR()); String sheetName = originalFormulaRange.getSheet(); Sheets sheets = template.getWorkbook().getSheets(); if (sheets != null && sheets.getSheet() != null) { for (Sheet sheet : sheets.getSheet()) { if (Objects.equals(sheet.getName(), sheetName)) { calcCell.setI((int) sheet.getSheetId()); break; } } } calculationChain.getC().add(calcCell); } }
protected void updateFormula(Cell cellWithFormula, Range originalFormulaRange, Range formulaRange, CTCalcChain calculationChain, int formulaCount) { CTCellFormula formula = cellWithFormula.getF(); formula.setValue(formula.getValue().replace(originalFormulaRange.toRange(), formulaRange.toRange())); if (originalFormulaRange.isOneCellRange() && formulaRange.isOneCellRange()) { //here we check that there are no alpha-numeric symbols around the single reference String pattern = "(?<!\\w+)" + originalFormulaRange.toFirstCellReference() + "(?!\\w+)"; formula.setValue(formula.getValue().replaceAll(pattern, formulaRange.toFirstCellReference())); } if (calculationChain != null) { CTCalcCell calcCell = new CTCalcCell(); calcCell.setR(cellWithFormula.getR()); String sheetName = originalFormulaRange.getSheet(); Sheets sheets = template.getWorkbook().getSheets(); if (sheets != null && sheets.getSheet() != null) { for (Sheet sheet : sheets.getSheet()) { if (Objects.equals(sheet.getName(), sheetName)) { calcCell.setI((int) sheet.getSheetId()); break; } } } calculationChain.getC().add(calcCell); } }
String newFormula = insertBandDataToString(cellWithWithBand.bandData, oldFormula); if (!oldFormula.equals(newFormula)) { cellWithFormula.getF().setValue(newFormula);
String newFormula = insertBandDataToString(cellWithWithBand.bandData, oldFormula); if (!oldFormula.equals(newFormula)) { cellWithFormula.getF().setValue(newFormula);
String newFormula = insertBandDataToString(cellWithWithBand.bandData, oldFormula); if (!oldFormula.equals(newFormula)) { cellWithFormula.getF().setValue(newFormula);
String newFormula = insertBandDataToString(cellWithWithBand.bandData, oldFormula); if (!oldFormula.equals(newFormula)) { cellWithFormula.getF().setValue(newFormula);