Examples with Table org.apache.poi.ss.usermodel.Table used on opensource projects

Example 1 with Table

use of org.apache.poi.ss.usermodel.Table in project poi by apache.

the class TestTableStyles method testCustomStyle.

@Test
public void testCustomStyle() throws Exception {
    XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("tableStyle.xlsx");
    Table table = wb.getTable("Table1");
    assertNotNull("missing table", table);
    TableStyleInfo style = table.getStyle();
    assertNotNull("Missing table style info", style);
    assertNotNull("Missing table style", style.getStyle());
    assertEquals("Wrong name", "TestTableStyle", style.getName());
    assertEquals("Wrong name", "TestTableStyle", style.getStyle().getName());
    DifferentialStyleProvider firstColumn = style.getStyle().getStyle(TableStyleType.firstColumn);
    assertNotNull("no first column style", firstColumn);
    FontFormatting font = firstColumn.getFontFormatting();
    assertNotNull("no first col font", font);
    assertTrue("wrong first col bold", font.isBold());
    wb.close();
}

Example 2 with Table

use of org.apache.poi.ss.usermodel.Table 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);
}