/** * Return a formula for the cell, for example, <code>SUM(C4:E4)</code> * * @return a formula for the cell * @throws IllegalStateException if the cell type returned by {@link #getCellType()} is not CELL_TYPE_FORMULA */ public String getCellFormula(Cell _cell) { int cellType = CellUtils.getCellType(_cell); if(cellType != CellUtils.CELL_TYPE_FORMULA) throw CellUtils.typeMismatch(CellUtils.CELL_TYPE_FORMULA, cellType, false); CTCellFormula f = _cell.getF(); // if (isPartOfArrayFormulaGroup() && f == null) { // Cell cell = getSheet().getFirstCellInArrayFormula(this); // return cell.getCellFormula(); // } if (f==null) { throw new RuntimeException("TODO: handle isPartOfArrayFormulaGroup()"); } if (f.getT() == STCellFormulaType.SHARED) { //return convertSharedFormula((int)f.getSi()); throw new RuntimeException("TODO: convertSharedFormula"); } return f.getValue(); }
public static Set<Range> fromCellFormula(String sheet, Cell cellWithFormula) { Matcher matcher = Range.STRICT_RANGE_PATTERN.matcher(cellWithFormula.getF().getValue()); Set<Range> ranges = new HashSet<Range>(); while (matcher.find()) { String rangeStr = matcher.group(); Range formulaRange = Range.fromRange(sheet, rangeStr); ranges.add(formulaRange); } matcher = Range.NOT_STRICT_RANGE_PATTERN.matcher(cellWithFormula.getF().getValue()); while (matcher.find()) { String rangeStr = matcher.group(); Range formulaRange = Range.fromRange(sheet, rangeStr); ranges.add(formulaRange); } return ranges; }
public static Set<Range> fromCellFormula(String sheet, Cell cellWithFormula) { Matcher matcher = Range.STRICT_RANGE_PATTERN.matcher(cellWithFormula.getF().getValue()); Set<Range> ranges = new HashSet<Range>(); while (matcher.find()) { String rangeStr = matcher.group(); Range formulaRange = Range.fromRange(sheet, rangeStr); ranges.add(formulaRange); } matcher = Range.NOT_STRICT_RANGE_PATTERN.matcher(cellWithFormula.getF().getValue()); while (matcher.find()) { String rangeStr = matcher.group(); Range formulaRange = Range.fromRange(sheet, rangeStr); ranges.add(formulaRange); } return ranges; }
/** * Return a formula for the cell, for example, <code>SUM(C4:E4)</code> * * @return a formula for the cell * @throws IllegalStateException if the cell type returned by {@link #getCellType()} is not CELL_TYPE_FORMULA */ public String getCellFormula(Cell _cell) { int cellType = CellUtils.getCellType(_cell); if(cellType != CellUtils.CELL_TYPE_FORMULA) throw CellUtils.typeMismatch(CellUtils.CELL_TYPE_FORMULA, cellType, false); CTCellFormula f = _cell.getF(); // if (isPartOfArrayFormulaGroup() && f == null) { // Cell cell = getSheet().getFirstCellInArrayFormula(this); // return cell.getCellFormula(); // } if (f==null) { throw new RuntimeException("TODO: handle isPartOfArrayFormulaGroup()"); } if (f.getT() == STCellFormulaType.SHARED) { //return convertSharedFormula((int)f.getSi()); throw new RuntimeException("TODO: convertSharedFormula"); } return f.getValue(); }
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); } }
protected void processOuterFormulas(int formulaCount, CTCalcChain calculationChain) { for (CellWithBand cellWithWithBand : outerFormulas) { Cell cellWithFormula = cellWithWithBand.cell; String oldFormula = cellWithFormula.getF().getValue(); String newFormula = insertBandDataToString(cellWithWithBand.bandData, oldFormula); if (!oldFormula.equals(newFormula)) {
protected void processOuterFormulas(int formulaCount, CTCalcChain calculationChain) { for (CellWithBand cellWithWithBand : outerFormulas) { Cell cellWithFormula = cellWithWithBand.cell; String oldFormula = cellWithFormula.getF().getValue(); String newFormula = insertBandDataToString(cellWithWithBand.bandData, oldFormula); if (!oldFormula.equals(newFormula)) {
String oldFormula = cellWithFormula.getF().getValue(); String newFormula = insertBandDataToString(cellWithWithBand.bandData, oldFormula); if (!oldFormula.equals(newFormula)) {
String oldFormula = cellWithFormula.getF().getValue(); String newFormula = insertBandDataToString(cellWithWithBand.bandData, oldFormula); if (!oldFormula.equals(newFormula)) {
@Test public void testFormulaShifts() throws Exception { XlsxFormatter xlsxFormatter = new XlsxFormatter( new FormatterFactoryInput("xls", new BandData(""), reportTemplate, null)); Cell cellWithFormula = cellWithFormula("SUM(A9:A9)/B9"); xlsxFormatter.updateFormula(cellWithFormula, Range.fromRange("Sheet", "A9:A9"), Range.fromRange("Sheet", "A90:A90"), null, 0); xlsxFormatter.updateFormula(cellWithFormula, Range.fromRange("Sheet", "B9:B9"), Range.fromRange("Sheet", "B90:B90"), null, 0); Assert.assertEquals("SUM(A90:A90)/B90", cellWithFormula.getF().getValue()); cellWithFormula = cellWithFormula("SUM(A9:B9)"); xlsxFormatter.updateFormula(cellWithFormula, Range.fromRange("Sheet", "A9:B9"), Range.fromRange("Sheet", "A90:B90"), null, 0); Assert.assertEquals("SUM(A90:B90)", cellWithFormula.getF().getValue()); cellWithFormula = cellWithFormula("A9*SUM(A9:A9)"); xlsxFormatter.updateFormula(cellWithFormula, Range.fromRange("Sheet", "A9:A9"), Range.fromRange("Sheet", "A90:A90"), null, 0); Assert.assertEquals("A90*SUM(A90:A90)", cellWithFormula.getF().getValue()); cellWithFormula = cellWithFormula("CA9*SUM(A9:A9)"); xlsxFormatter.updateFormula(cellWithFormula, Range.fromRange("Sheet", "A9:A9"), Range.fromRange("Sheet", "A90:A90"), null, 0); Assert.assertEquals("CA9*SUM(A90:A90)", cellWithFormula.getF().getValue()); }