Examples with CellReference org.apache.poi.ss.util.CellReference used on opensource projects

Example 71 with CellReference

use of org.apache.poi.ss.util.CellReference in project poi by apache.

the class TestSXSSFWorkbook method gzipSheetdataWriter.

@Test
public void gzipSheetdataWriter() throws IOException {
    SXSSFWorkbook wb = new SXSSFWorkbook();
    wb.setCompressTempFiles(true);
    int rowNum = 1000;
    int sheetNum = 5;
    for (int i = 0; i < sheetNum; i++) {
        Sheet sh = wb.createSheet("sheet" + i);
        for (int j = 0; j < rowNum; j++) {
            Row row = sh.createRow(j);
            Cell cell1 = row.createCell(0);
            cell1.setCellValue(new CellReference(cell1).formatAsString());
            Cell cell2 = row.createCell(1);
            cell2.setCellValue(i);
            Cell cell3 = row.createCell(2);
            cell3.setCellValue(j);
        }
    }
    XSSFWorkbook xwb = SXSSFITestDataProvider.instance.writeOutAndReadBack(wb);
    for (int i = 0; i < sheetNum; i++) {
        Sheet sh = xwb.getSheetAt(i);
        assertEquals("sheet" + i, sh.getSheetName());
        for (int j = 0; j < rowNum; j++) {
            Row row = sh.getRow(j);
            assertNotNull("row[" + j + "]", row);
            Cell cell1 = row.getCell(0);
            assertEquals(new CellReference(cell1).formatAsString(), cell1.getStringCellValue());
            Cell cell2 = row.getCell(1);
            assertEquals(i, (int) cell2.getNumericCellValue());
            Cell cell3 = row.getCell(2);
            assertEquals(j, (int) cell3.getNumericCellValue());
        }
    }
    assertTrue(wb.dispose());
    xwb.close();
    wb.close();
}

Example 72 with CellReference

use of org.apache.poi.ss.util.CellReference in project poi by apache.

the class TestLogicalFunction method buildWorkbook.

private void buildWorkbook(Workbook wb) {
    Sheet sh = wb.createSheet();
    Row row1 = sh.createRow(0);
    Row row2 = sh.createRow(1);
    row3 = sh.createRow(2);
    row1.createCell(0, CellType.NUMERIC);
    row1.createCell(1, CellType.NUMERIC);
    row2.createCell(0, CellType.NUMERIC);
    row2.createCell(1, CellType.NUMERIC);
    row3.createCell(0);
    row3.createCell(1);
    CellReference a1 = new CellReference("A1");
    CellReference a2 = new CellReference("A2");
    CellReference b1 = new CellReference("B1");
    CellReference b2 = new CellReference("B2");
    sh.getRow(a1.getRow()).getCell(a1.getCol()).setCellValue(35);
    sh.getRow(a2.getRow()).getCell(a2.getCol()).setCellValue(0);
    sh.getRow(b1.getRow()).getCell(b1.getCol()).setCellFormula("A1/A2");
    sh.getRow(b2.getRow()).getCell(b2.getCol()).setCellFormula("NA()");
    evaluator = wb.getCreationHelper().createFormulaEvaluator();
}

Example 73 with CellReference

use of org.apache.poi.ss.util.CellReference in project poi by apache.

the class FormulaParser method parseStructuredReference.

/**
     * Parses a structured reference, returns it as area reference.
     * Examples:
     * <pre>
     * Table1[col]
     * Table1[[#Totals],[col]]
     * Table1[#Totals]
     * Table1[#All]
     * Table1[#Data]
     * Table1[#Headers]
     * Table1[#Totals]
     * Table1[#This Row]
     * Table1[[#All],[col]]
     * Table1[[#Headers],[col]]
     * Table1[[#Totals],[col]]
     * Table1[[#All],[col1]:[col2]]
     * Table1[[#Data],[col1]:[col2]]
     * Table1[[#Headers],[col1]:[col2]]
     * Table1[[#Totals],[col1]:[col2]]
     * Table1[[#Headers],[#Data],[col2]]
     * Table1[[#This Row], [col1]]
     * Table1[ [col1]:[col2] ]
     * </pre>
     * @param tableName
     * @return Area Reference for the given table
     */
private ParseNode parseStructuredReference(String tableName) {
    if (!(_ssVersion.equals(SpreadsheetVersion.EXCEL2007))) {
        throw new FormulaParseException("Structured references work only on XSSF (Excel 2007+)!");
    }
    Table tbl = _book.getTable(tableName);
    if (tbl == null) {
        throw new FormulaParseException("Illegal table name: '" + tableName + "'");
    }
    String sheetName = tbl.getSheetName();
    int startCol = tbl.getStartColIndex();
    int endCol = tbl.getEndColIndex();
    int startRow = tbl.getStartRowIndex();
    int endRow = tbl.getEndRowIndex();
    // Do NOT return before done reading all the structured reference tokens from the input stream.
    // Throwing exceptions is okay.
    int savePtr0 = _pointer;
    GetChar();
    boolean isTotalsSpec = false;
    boolean isThisRowSpec = false;
    boolean isDataSpec = false;
    boolean isHeadersSpec = false;
    boolean isAllSpec = false;
    // The number of special quantifiers
    int nSpecQuantifiers = 0;
    while (true) {
        int savePtr1 = _pointer;
        String specName = parseAsSpecialQuantifier();
        if (specName == null) {
            resetPointer(savePtr1);
            break;
        }
        if (specName.equals(specAll)) {
            isAllSpec = true;
        } else if (specName.equals(specData)) {
            isDataSpec = true;
        } else if (specName.equals(specHeaders)) {
            isHeadersSpec = true;
        } else if (specName.equals(specThisRow)) {
            isThisRowSpec = true;
        } else if (specName.equals(specTotals)) {
            isTotalsSpec = true;
        } else {
            throw new FormulaParseException("Unknown special quantifier " + specName);
        }
        nSpecQuantifiers++;
        if (look == ',') {
            GetChar();
        } else {
            break;
        }
    }
    boolean isThisRow = false;
    SkipWhite();
    if (look == '@') {
        isThisRow = true;
        GetChar();
    }
    // parse column quantifier
    String startColumnName;
    String endColumnName = null;
    int nColQuantifiers = 0;
    int savePtr1 = _pointer;
    startColumnName = parseAsColumnQuantifier();
    if (startColumnName == null) {
        resetPointer(savePtr1);
    } else {
        nColQuantifiers++;
        if (look == ',') {
            throw new FormulaParseException("The formula " + _formulaString + "is illegal: you should not use ',' with column quantifiers");
        } else if (look == ':') {
            GetChar();
            endColumnName = parseAsColumnQuantifier();
            nColQuantifiers++;
            if (endColumnName == null) {
                throw new FormulaParseException("The formula " + _formulaString + "is illegal: the string after ':' must be column quantifier");
            }
        }
    }
    if (nColQuantifiers == 0 && nSpecQuantifiers == 0) {
        resetPointer(savePtr0);
        savePtr0 = _pointer;
        startColumnName = parseAsColumnQuantifier();
        if (startColumnName != null) {
            nColQuantifiers++;
        } else {
            resetPointer(savePtr0);
            String name = parseAsSpecialQuantifier();
            if (name != null) {
                if (name.equals(specAll)) {
                    isAllSpec = true;
                } else if (name.equals(specData)) {
                    isDataSpec = true;
                } else if (name.equals(specHeaders)) {
                    isHeadersSpec = true;
                } else if (name.equals(specThisRow)) {
                    isThisRowSpec = true;
                } else if (name.equals(specTotals)) {
                    isTotalsSpec = true;
                } else {
                    throw new FormulaParseException("Unknown special quantifier " + name);
                }
                nSpecQuantifiers++;
            } else {
                throw new FormulaParseException("The formula " + _formulaString + " is illegal");
            }
        }
    } else {
        Match(']');
    }
    if (isTotalsSpec && !tbl.isHasTotalsRow()) {
        return new ParseNode(ErrPtg.REF_INVALID);
    }
    if ((isThisRow || isThisRowSpec) && (_rowIndex < startRow || endRow < _rowIndex)) {
        // structured reference is trying to reference a row above or below the table with [#This Row] or [@]
        if (_rowIndex >= 0) {
            return new ParseNode(ErrPtg.VALUE_INVALID);
        } else {
            throw new FormulaParseException("Formula contained [#This Row] or [@] structured reference but this row < 0. " + "Row index must be specified for row-referencing structured references.");
        }
    }
    int actualStartRow = startRow;
    int actualEndRow = endRow;
    int actualStartCol = startCol;
    int actualEndCol = endCol;
    if (nSpecQuantifiers > 0) {
        //Selecting rows
        if (nSpecQuantifiers == 1 && isAllSpec) {
        //do nothing
        } else if (isDataSpec && isHeadersSpec) {
            if (tbl.isHasTotalsRow()) {
                actualEndRow = endRow - 1;
            }
        } else if (isDataSpec && isTotalsSpec) {
            actualStartRow = startRow + 1;
        } else if (nSpecQuantifiers == 1 && isDataSpec) {
            actualStartRow = startRow + 1;
            if (tbl.isHasTotalsRow()) {
                actualEndRow = endRow - 1;
            }
        } else if (nSpecQuantifiers == 1 && isHeadersSpec) {
            actualEndRow = actualStartRow;
        } else if (nSpecQuantifiers == 1 && isTotalsSpec) {
            actualStartRow = actualEndRow;
        } else if ((nSpecQuantifiers == 1 && isThisRowSpec) || isThisRow) {
            //The rowNum is 0 based
            actualStartRow = _rowIndex;
            actualEndRow = _rowIndex;
        } else {
            throw new FormulaParseException("The formula " + _formulaString + " is illegal");
        }
    } else {
        if (isThisRow) {
            // there is a @
            //The rowNum is 0 based
            actualStartRow = _rowIndex;
            actualEndRow = _rowIndex;
        } else {
            // Really no special quantifiers
            actualStartRow++;
            if (tbl.isHasTotalsRow())
                actualEndRow--;
        }
    }
    if (nColQuantifiers == 2) {
        if (startColumnName == null || endColumnName == null) {
            throw new IllegalStateException("Fatal error");
        }
        int startIdx = tbl.findColumnIndex(startColumnName);
        int endIdx = tbl.findColumnIndex(endColumnName);
        if (startIdx == -1 || endIdx == -1) {
            throw new FormulaParseException("One of the columns " + startColumnName + ", " + endColumnName + " doesn't exist in table " + tbl.getName());
        }
        actualStartCol = startCol + startIdx;
        actualEndCol = startCol + endIdx;
    } else if (nColQuantifiers == 1 && !isThisRow) {
        if (startColumnName == null) {
            throw new IllegalStateException("Fatal error");
        }
        int idx = tbl.findColumnIndex(startColumnName);
        if (idx == -1) {
            throw new FormulaParseException("The column " + startColumnName + " doesn't exist in table " + tbl.getName());
        }
        actualStartCol = startCol + idx;
        actualEndCol = actualStartCol;
    }
    CellReference topLeft = new CellReference(actualStartRow, actualStartCol);
    CellReference bottomRight = new CellReference(actualEndRow, actualEndCol);
    SheetIdentifier sheetIden = new SheetIdentifier(null, new NameIdentifier(sheetName, true));
    Ptg ptg = _book.get3DReferencePtg(new AreaReference(topLeft, bottomRight), sheetIden);
    return new ParseNode(ptg);
}

Example 74 with CellReference

use of org.apache.poi.ss.util.CellReference in project poi by apache.

the class LazyAreaEval method toString.

public String toString() {
    CellReference crA = new CellReference(getFirstRow(), getFirstColumn());
    CellReference crB = new CellReference(getLastRow(), getLastColumn());
    return getClass().getName() + "[" + _evaluator.getSheetNameRange() + '!' + crA.formatAsString() + ':' + crB.formatAsString() + "]";
}

Example 75 with CellReference

use of org.apache.poi.ss.util.CellReference in project poi by apache.

the class WorkbookEvaluator method addExceptionInfo.

/**
     * Adds the current cell reference to the exception for easier debugging.
     * Would be nice to get the formula text as well, but that seems to require
     * too much digging around and casting to get the FormulaRenderingWorkbook.
     */
private NotImplementedException addExceptionInfo(NotImplementedException inner, int sheetIndex, int rowIndex, int columnIndex) {
    try {
        String sheetName = _workbook.getSheetName(sheetIndex);
        CellReference cr = new CellReference(sheetName, rowIndex, columnIndex, false, false);
        String msg = "Error evaluating cell " + cr.formatAsString();
        return new NotImplementedException(msg, inner);
    } catch (Exception e) {
        // avoid bombing out during exception handling
        LOG.log(POILogger.ERROR, "Can't add exception info", e);
        // preserve original exception
        return inner;
    }
}