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

Example 1 with AreaReference

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

the class CreateTable method main.

public static void main(String[] args) throws IOException {
    Workbook wb = new XSSFWorkbook();
    XSSFSheet sheet = (XSSFSheet) wb.createSheet();
    //Create 
    XSSFTable table = sheet.createTable();
    table.setDisplayName("Test");
    CTTable cttable = table.getCTTable();
    //Style configurations
    CTTableStyleInfo style = cttable.addNewTableStyleInfo();
    style.setName("TableStyleMedium2");
    style.setShowColumnStripes(false);
    style.setShowRowStripes(true);
    //Set which area the table should be placed in
    AreaReference reference = new AreaReference(new CellReference(0, 0), new CellReference(2, 2));
    cttable.setRef(reference.formatAsString());
    cttable.setId(1);
    cttable.setName("Test");
    cttable.setTotalsRowCount(1);
    CTTableColumns columns = cttable.addNewTableColumns();
    columns.setCount(3);
    CTTableColumn column;
    XSSFRow row;
    XSSFCell cell;
    for (int i = 0; i < 3; i++) {
        //Create column
        column = columns.addNewTableColumn();
        column.setName("Column");
        column.setId(i + 1);
        //Create row
        row = sheet.createRow(i);
        for (int j = 0; j < 3; j++) {
            //Create cell
            cell = row.createCell(j);
            if (i == 0) {
                cell.setCellValue("Column" + j);
            } else {
                cell.setCellValue("0");
            }
        }
    }
    FileOutputStream fileOut = new FileOutputStream("ooxml-table.xlsx");
    wb.write(fileOut);
    fileOut.close();
    wb.close();
}

Example 2 with AreaReference

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

the class XSSFPivotCacheDefinition method getPivotArea.

/**
     * Find the 2D base data area for the pivot table, either from its direct reference or named table/range.
     * @return AreaReference representing the current area defined by the pivot table
     * @throws IllegalArgumentException if the ref attribute is not contiguous or the name attribute is not found.
     */
@Beta
public AreaReference getPivotArea(Workbook wb) throws IllegalArgumentException {
    final CTWorksheetSource wsSource = ctPivotCacheDefinition.getCacheSource().getWorksheetSource();
    final String ref = wsSource.getRef();
    final String name = wsSource.getName();
    if (ref == null && name == null) {
        throw new IllegalArgumentException("Pivot cache must reference an area, named range, or table.");
    }
    // this is the XML format, so tell the reference that.
    if (ref != null) {
        return new AreaReference(ref, SpreadsheetVersion.EXCEL2007);
    }
    assert (name != null);
    // named range or table?
    final Name range = wb.getName(name);
    if (range != null) {
        return new AreaReference(range.getRefersToFormula(), SpreadsheetVersion.EXCEL2007);
    }
    // not a named range, check for a table.
    // do this second, as tables are sheet-specific, but named ranges are not, and may not have a sheet name given.
    final XSSFSheet sheet = (XSSFSheet) wb.getSheet(wsSource.getSheet());
    for (XSSFTable table : sheet.getTables()) {
        // TODO: case-sensitive?
        if (name.equals(table.getName())) {
            return new AreaReference(table.getStartCellReference(), table.getEndCellReference());
        }
    }
    throw new IllegalArgumentException("Name '" + name + "' was not found.");
}

Example 3 with AreaReference

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

the class XSSFPivotTable method getPivotArea.

protected AreaReference getPivotArea() {
    final Workbook wb = getDataSheet().getWorkbook();
    AreaReference pivotArea = getPivotCacheDefinition().getPivotArea(wb);
    return pivotArea;
}

Example 4 with AreaReference

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

the class XSSFPivotTable method addRowLabel.

/**
     * Add a row label using data from the given column.
     * @param columnIndex the index of the source column to be used as row label.
     * {@code columnIndex} is 0-based indexed and relative to the first column in the source.
     */
@Beta
public void addRowLabel(int columnIndex) {
    checkColumnIndex(columnIndex);
    AreaReference pivotArea = getPivotArea();
    final int lastRowIndex = pivotArea.getLastCell().getRow() - pivotArea.getFirstCell().getRow();
    CTPivotFields pivotFields = pivotTableDefinition.getPivotFields();
    CTPivotField pivotField = CTPivotField.Factory.newInstance();
    CTItems items = pivotField.addNewItems();
    pivotField.setAxis(STAxis.AXIS_ROW);
    pivotField.setShowAll(false);
    for (int i = 0; i <= lastRowIndex; i++) {
        items.addNewItem().setT(STItemType.DEFAULT);
    }
    items.setCount(items.sizeOfItemArray());
    pivotFields.setPivotFieldArray(columnIndex, pivotField);
    CTRowFields rowFields;
    if (pivotTableDefinition.getRowFields() != null) {
        rowFields = pivotTableDefinition.getRowFields();
    } else {
        rowFields = pivotTableDefinition.addNewRowFields();
    }
    rowFields.addNewField().setX(columnIndex);
    rowFields.setCount(rowFields.sizeOfFieldArray());
}

Example 5 with AreaReference

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

the class XSSFPivotTable method addDataField.

/**
     * Add data field with data from the given column and specified function.
     * @param function the function to be used on the data
     *      The following functions exists:
     *      Sum, Count, Average, Max, Min, Product, Count numbers, StdDev, StdDevp, Var, Varp
     * @param columnIndex the index of the column to be used as column label.
     * @param valueFieldName the name of pivot table value field
     */
@Beta
private void addDataField(DataConsolidateFunction function, int columnIndex, String valueFieldName) {
    checkColumnIndex(columnIndex);
    AreaReference pivotArea = getPivotArea();
    CTDataFields dataFields;
    if (pivotTableDefinition.getDataFields() != null) {
        dataFields = pivotTableDefinition.getDataFields();
    } else {
        dataFields = pivotTableDefinition.addNewDataFields();
    }
    CTDataField dataField = dataFields.addNewDataField();
    dataField.setSubtotal(STDataConsolidateFunction.Enum.forInt(function.getValue()));
    Cell cell = getDataSheet().getRow(pivotArea.getFirstCell().getRow()).getCell(pivotArea.getFirstCell().getCol() + columnIndex);
    cell.setCellType(CellType.STRING);
    dataField.setName(valueFieldName);
    dataField.setFld(columnIndex);
    dataFields.setCount(dataFields.sizeOfDataFieldArray());
}