Examples with CalculationChain org.apache.poi.xssf.model.CalculationChain used on opensource projects

Example 1 with CalculationChain

use of org.apache.poi.xssf.model.CalculationChain in project poi by apache.

the class XSSFRow method shift.

/**
     * update cell references when shifting rows
     *
     * @param n the number of rows to move
     */
protected void shift(int n) {
    int rownum = getRowNum() + n;
    CalculationChain calcChain = _sheet.getWorkbook().getCalculationChain();
    int sheetId = (int) _sheet.sheet.getSheetId();
    String msg = "Row[rownum=" + getRowNum() + "] contains cell(s) included in a multi-cell array formula. " + "You cannot change part of an array.";
    for (Cell c : this) {
        XSSFCell cell = (XSSFCell) c;
        if (cell.isPartOfArrayFormulaGroup()) {
            cell.notifyArrayFormulaChanging(msg);
        }
        //remove the reference in the calculation chain
        if (calcChain != null)
            calcChain.removeItem(sheetId, cell.getReference());
        CTCell ctCell = cell.getCTCell();
        String r = new CellReference(rownum, cell.getColumnIndex()).formatAsString();
        ctCell.setR(r);
    }
    setRowNum(rownum);
}

Example 2 with CalculationChain

use of org.apache.poi.xssf.model.CalculationChain in project poi by apache.

the class TestXSSFBugs method bug49966.

/**
     * Various ways of removing a cell formula should all zap the calcChain
     * entry.
     */
@Test
public void bug49966() throws IOException {
    XSSFWorkbook wb1 = XSSFTestDataSamples.openSampleWorkbook("shared_formulas.xlsx");
    XSSFSheet sheet = wb1.getSheetAt(0);
    XSSFTestDataSamples.writeOutAndReadBack(wb1).close();
    // CalcChain has lots of entries
    CalculationChain cc = wb1.getCalculationChain();
    assertEquals("A2", cc.getCTCalcChain().getCArray(0).getR());
    assertEquals("A3", cc.getCTCalcChain().getCArray(1).getR());
    assertEquals("A4", cc.getCTCalcChain().getCArray(2).getR());
    assertEquals("A5", cc.getCTCalcChain().getCArray(3).getR());
    assertEquals("A6", cc.getCTCalcChain().getCArray(4).getR());
    assertEquals("A7", cc.getCTCalcChain().getCArray(5).getR());
    assertEquals("A8", cc.getCTCalcChain().getCArray(6).getR());
    assertEquals(40, cc.getCTCalcChain().sizeOfCArray());
    XSSFTestDataSamples.writeOutAndReadBack(wb1).close();
    // Try various ways of changing the formulas
    // If it stays a formula, chain entry should remain
    // Otherwise should go
    // stay
    sheet.getRow(1).getCell(0).setCellFormula("A1");
    // go
    sheet.getRow(2).getCell(0).setCellFormula(null);
    // stay
    sheet.getRow(3).getCell(0).setCellType(CellType.FORMULA);
    XSSFTestDataSamples.writeOutAndReadBack(wb1).close();
    // go
    sheet.getRow(4).getCell(0).setCellType(CellType.STRING);
    XSSFTestDataSamples.writeOutAndReadBack(wb1).close();
    validateCells(sheet);
    // go
    sheet.getRow(5).removeCell(sheet.getRow(5).getCell(0));
    validateCells(sheet);
    XSSFTestDataSamples.writeOutAndReadBack(wb1).close();
    // go
    sheet.getRow(6).getCell(0).setCellType(CellType.BLANK);
    XSSFTestDataSamples.writeOutAndReadBack(wb1).close();
    // go
    sheet.getRow(7).getCell(0).setCellValue((String) null);
    XSSFTestDataSamples.writeOutAndReadBack(wb1).close();
    // Save and check
    XSSFWorkbook wb2 = XSSFTestDataSamples.writeOutAndReadBack(wb1);
    wb1.close();
    assertEquals(35, cc.getCTCalcChain().sizeOfCArray());
    cc = wb2.getCalculationChain();
    assertEquals("A2", cc.getCTCalcChain().getCArray(0).getR());
    assertEquals("A4", cc.getCTCalcChain().getCArray(1).getR());
    assertEquals("A9", cc.getCTCalcChain().getCArray(2).getR());
    wb2.close();
}

Example 3 with CalculationChain

use of org.apache.poi.xssf.model.CalculationChain in project poi by apache.

the class TestXSSFSheet method bug49966.

@Test
public void bug49966() throws IOException {
    XSSFWorkbook wb1 = XSSFTestDataSamples.openSampleWorkbook("49966.xlsx");
    CalculationChain calcChain = wb1.getCalculationChain();
    assertNotNull(wb1.getCalculationChain());
    assertEquals(3, calcChain.getCTCalcChain().sizeOfCArray());
    XSSFSheet sheet = wb1.getSheetAt(0);
    XSSFRow row = sheet.getRow(0);
    sheet.removeRow(row);
    assertEquals("XSSFSheet#removeRow did not clear calcChain entries", 0, calcChain.getCTCalcChain().sizeOfCArray());
    //calcChain should be gone
    XSSFWorkbook wb2 = XSSFTestDataSamples.writeOutAndReadBack(wb1);
    wb1.close();
    assertNull(wb2.getCalculationChain());
    wb2.close();
}

Example 4 with CalculationChain

use of org.apache.poi.xssf.model.CalculationChain in project poi by apache.

the class XSSFWorkbook method onDocumentRead.

@Override
protected void onDocumentRead() throws IOException {
    try {
        WorkbookDocument doc = WorkbookDocument.Factory.parse(getPackagePart().getInputStream(), DEFAULT_XML_OPTIONS);
        this.workbook = doc.getWorkbook();
        ThemesTable theme = null;
        Map<String, XSSFSheet> shIdMap = new HashMap<String, XSSFSheet>();
        Map<String, ExternalLinksTable> elIdMap = new HashMap<String, ExternalLinksTable>();
        for (RelationPart rp : getRelationParts()) {
            POIXMLDocumentPart p = rp.getDocumentPart();
            if (p instanceof SharedStringsTable) {
                sharedStringSource = (SharedStringsTable) p;
            } else if (p instanceof StylesTable) {
                stylesSource = (StylesTable) p;
            } else if (p instanceof ThemesTable) {
                theme = (ThemesTable) p;
            } else if (p instanceof CalculationChain) {
                calcChain = (CalculationChain) p;
            } else if (p instanceof MapInfo) {
                mapInfo = (MapInfo) p;
            } else if (p instanceof XSSFSheet) {
                shIdMap.put(rp.getRelationship().getId(), (XSSFSheet) p);
            } else if (p instanceof ExternalLinksTable) {
                elIdMap.put(rp.getRelationship().getId(), (ExternalLinksTable) p);
            }
        }
        boolean packageReadOnly = (getPackage().getPackageAccess() == PackageAccess.READ);
        if (stylesSource == null) {
            // Create Styles if it is missing
            if (packageReadOnly) {
                stylesSource = new StylesTable();
            } else {
                stylesSource = (StylesTable) createRelationship(XSSFRelation.STYLES, XSSFFactory.getInstance());
            }
        }
        stylesSource.setWorkbook(this);
        stylesSource.setTheme(theme);
        if (sharedStringSource == null) {
            // Create SST if it is missing
            if (packageReadOnly) {
                sharedStringSource = new SharedStringsTable();
            } else {
                sharedStringSource = (SharedStringsTable) createRelationship(XSSFRelation.SHARED_STRINGS, XSSFFactory.getInstance());
            }
        }
        // Load individual sheets. The order of sheets is defined by the order
        //  of CTSheet elements in the workbook
        sheets = new ArrayList<XSSFSheet>(shIdMap.size());
        for (CTSheet ctSheet : this.workbook.getSheets().getSheetArray()) {
            parseSheet(shIdMap, ctSheet);
        }
        // Load the external links tables. Their order is defined by the order 
        //  of CTExternalReference elements in the workbook
        externalLinks = new ArrayList<ExternalLinksTable>(elIdMap.size());
        if (this.workbook.isSetExternalReferences()) {
            for (CTExternalReference er : this.workbook.getExternalReferences().getExternalReferenceArray()) {
                ExternalLinksTable el = elIdMap.get(er.getId());
                if (el == null) {
                    logger.log(POILogger.WARN, "ExternalLinksTable with r:id " + er.getId() + " was defined, but didn't exist in package, skipping");
                    continue;
                }
                externalLinks.add(el);
            }
        }
        // Process the named ranges
        reprocessNamedRanges();
    } catch (XmlException e) {
        throw new POIXMLException(e);
    }
}

Example 5 with CalculationChain

use of org.apache.poi.xssf.model.CalculationChain in project poi by apache.

the class TestXSSFBugs method runTest56574.

private void runTest56574(boolean createRow) throws IOException {
    Workbook wb = XSSFTestDataSamples.openSampleWorkbook("56574.xlsx");
    Sheet sheet = wb.getSheet("Func");
    assertNotNull(sheet);
    Map<String, Object[]> data;
    data = new TreeMap<String, Object[]>();
    data.put("1", new Object[] { "ID", "NAME", "LASTNAME" });
    data.put("2", new Object[] { 2, "Amit", "Shukla" });
    data.put("3", new Object[] { 1, "Lokesh", "Gupta" });
    data.put("4", new Object[] { 4, "John", "Adwards" });
    data.put("5", new Object[] { 2, "Brian", "Schultz" });
    int rownum = 1;
    for (Map.Entry<String, Object[]> me : data.entrySet()) {
        final Row row;
        if (createRow) {
            row = sheet.createRow(rownum++);
        } else {
            row = sheet.getRow(rownum++);
        }
        assertNotNull(row);
        int cellnum = 0;
        for (Object obj : me.getValue()) {
            Cell cell = row.getCell(cellnum);
            if (cell == null) {
                cell = row.createCell(cellnum);
            } else {
                if (cell.getCellTypeEnum() == CellType.FORMULA) {
                    cell.setCellFormula(null);
                    cell.getCellStyle().setDataFormat((short) 0);
                }
            }
            if (obj instanceof String) {
                cell.setCellValue((String) obj);
            } else if (obj instanceof Integer) {
                cell.setCellValue((Integer) obj);
            }
            cellnum++;
        }
    }
    XSSFFormulaEvaluator.evaluateAllFormulaCells((XSSFWorkbook) wb);
    wb.getCreationHelper().createFormulaEvaluator().evaluateAll();
    CalculationChain chain = ((XSSFWorkbook) wb).getCalculationChain();
    for (CTCalcCell calc : chain.getCTCalcChain().getCList()) {
        // A2 to A6 should be gone
        assertFalse(calc.getR().equals("A2"));
        assertFalse(calc.getR().equals("A3"));
        assertFalse(calc.getR().equals("A4"));
        assertFalse(calc.getR().equals("A5"));
        assertFalse(calc.getR().equals("A6"));
    }
    Workbook wbBack = XSSFTestDataSamples.writeOutAndReadBack(wb);
    Sheet sheetBack = wbBack.getSheet("Func");
    assertNotNull(sheetBack);
    chain = ((XSSFWorkbook) wbBack).getCalculationChain();
    for (CTCalcCell calc : chain.getCTCalcChain().getCList()) {
        // A2 to A6 should be gone
        assertFalse(calc.getR().equals("A2"));
        assertFalse(calc.getR().equals("A3"));
        assertFalse(calc.getR().equals("A4"));
        assertFalse(calc.getR().equals("A5"));
        assertFalse(calc.getR().equals("A6"));
    }
    wbBack.close();
    wb.close();
}