Examples with XSSFEvaluationWorkbook org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook used on opensource projects

Example 6 with XSSFEvaluationWorkbook

use of org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook in project poi by apache.

the class XSSFRowShifter method updateConditionalFormatting.

public void updateConditionalFormatting(FormulaShifter shifter) {
    XSSFSheet xsheet = (XSSFSheet) sheet;
    XSSFWorkbook wb = xsheet.getWorkbook();
    int sheetIndex = wb.getSheetIndex(sheet);
    //don't care, structured references not allowed in conditional formatting
    final int rowIndex = -1;
    XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb);
    CTWorksheet ctWorksheet = xsheet.getCTWorksheet();
    CTConditionalFormatting[] conditionalFormattingArray = ctWorksheet.getConditionalFormattingArray();
    // iterate backwards due to possible calls to ctWorksheet.removeConditionalFormatting(j)
    for (int j = conditionalFormattingArray.length - 1; j >= 0; j--) {
        CTConditionalFormatting cf = conditionalFormattingArray[j];
        ArrayList<CellRangeAddress> cellRanges = new ArrayList<CellRangeAddress>();
        for (Object stRef : cf.getSqref()) {
            String[] regions = stRef.toString().split(" ");
            for (String region : regions) {
                cellRanges.add(CellRangeAddress.valueOf(region));
            }
        }
        boolean changed = false;
        List<CellRangeAddress> temp = new ArrayList<CellRangeAddress>();
        for (CellRangeAddress craOld : cellRanges) {
            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.size();
            if (nRanges == 0) {
                ctWorksheet.removeConditionalFormatting(j);
                continue;
            }
            List<String> refs = new ArrayList<String>();
            for (CellRangeAddress a : temp) refs.add(a.formatAsString());
            cf.setSqref(refs);
        }
        for (CTCfRule cfRule : cf.getCfRuleArray()) {
            String[] formulaArray = cfRule.getFormulaArray();
            for (int i = 0; i < formulaArray.length; i++) {
                String formula = formulaArray[i];
                Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.CELL, sheetIndex, rowIndex);
                if (shifter.adjustFormula(ptgs, sheetIndex)) {
                    String shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs);
                    cfRule.setFormulaArray(i, shiftedFmla);
                }
            }
        }
    }
}

Example 7 with XSSFEvaluationWorkbook

use of org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook in project poi by apache.

the class TestFormulaParser method testParseExternalReferencesWithUnquotedSheetName.

// trivial case for bug 60219: FormulaParser can't parse external references when sheet name is quoted
@Test
public void testParseExternalReferencesWithUnquotedSheetName() throws Exception {
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFEvaluationWorkbook fpwb = XSSFEvaluationWorkbook.create(wb);
    Ptg[] ptgs = FormulaParser.parse("[1]Sheet1!A1", fpwb, FormulaType.CELL, -1);
    // org.apache.poi.ss.formula.ptg.Ref3DPxg [ [workbook=1] sheet=Sheet 1 ! A1]
    assertEquals("Ptgs length", 1, ptgs.length);
    assertTrue("Ptg class", ptgs[0] instanceof Ref3DPxg);
    Ref3DPxg pxg = (Ref3DPxg) ptgs[0];
    assertEquals("External workbook number", 1, pxg.getExternalWorkbookNumber());
    assertEquals("Sheet name", "Sheet1", pxg.getSheetName());
    assertEquals("Row", 0, pxg.getRow());
    assertEquals("Column", 0, pxg.getColumn());
    wb.close();
}