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

Example 11 with AreaReference

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

the class TestRangeEval method confirm.

private static void confirm(String refA, String refB, String expectedAreaRef) {
    ValueEval[] args = { createRefEval(refA), createRefEval(refB) };
    @SuppressWarnings("deprecation") AreaReference ar = new AreaReference(expectedAreaRef);
    ValueEval result = EvalInstances.Range.evaluate(args, 0, (short) 0);
    assertTrue(result instanceof AreaEval);
    AreaEval ae = (AreaEval) result;
    assertEquals(ar.getFirstCell().getRow(), ae.getFirstRow());
    assertEquals(ar.getLastCell().getRow(), ae.getLastRow());
    assertEquals(ar.getFirstCell().getCol(), ae.getFirstColumn());
    assertEquals(ar.getLastCell().getCol(), ae.getLastColumn());
}

Example 12 with AreaReference

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

the class XSSFPivotTable method createSourceReferences.

/**
     * Creates cacheSource and workSheetSource for pivot table and sets the source reference as well assets the location of the pivot table
     * @param position Position for pivot table in sheet
     * @param sourceSheet Sheet where the source will be collected from
     * @param refConfig  an configurator that knows how to configure pivot table references
     */
@Beta
protected void createSourceReferences(CellReference position, Sheet sourceSheet, PivotTableReferenceConfigurator refConfig) {
    //Get cell one to the right and one down from position, add both to AreaReference and set pivot table location.
    AreaReference destination = new AreaReference(position, new CellReference(position.getRow() + 1, position.getCol() + 1));
    CTLocation location;
    if (pivotTableDefinition.getLocation() == null) {
        location = pivotTableDefinition.addNewLocation();
        location.setFirstDataCol(1);
        location.setFirstDataRow(1);
        location.setFirstHeaderRow(1);
    } else {
        location = pivotTableDefinition.getLocation();
    }
    location.setRef(destination.formatAsString());
    pivotTableDefinition.setLocation(location);
    //Set source for the pivot table
    CTPivotCacheDefinition cacheDef = getPivotCacheDefinition().getCTPivotCacheDefinition();
    CTCacheSource cacheSource = cacheDef.addNewCacheSource();
    cacheSource.setType(STSourceType.WORKSHEET);
    CTWorksheetSource worksheetSource = cacheSource.addNewWorksheetSource();
    worksheetSource.setSheet(sourceSheet.getSheetName());
    setDataSheet(sourceSheet);
    refConfig.configureReference(worksheetSource);
    if (worksheetSource.getName() == null && worksheetSource.getRef() == null)
        throw new IllegalArgumentException("Pivot table source area reference or name must be specified.");
}

Example 13 with AreaReference

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

the class TestXSSFBugs method bug51963.

/**
     * Sheet names with a , in them
     */
@Test
public void bug51963() throws IOException {
    XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("51963.xlsx");
    Sheet sheet = wb.getSheetAt(0);
    assertEquals("Abc,1", sheet.getSheetName());
    Name name = wb.getName("Intekon.ProdCodes");
    assertEquals("'Abc,1'!$A$1:$A$2", name.getRefersToFormula());
    AreaReference ref = new AreaReference(name.getRefersToFormula(), SpreadsheetVersion.EXCEL2007);
    assertEquals(0, ref.getFirstCell().getRow());
    assertEquals(0, ref.getFirstCell().getCol());
    assertEquals(1, ref.getLastCell().getRow());
    assertEquals(0, ref.getLastCell().getCol());
    wb.close();
}

Example 14 with AreaReference

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

the class TestXSSFSheet method testCreateTwoPivotTablesInTwoSheets.

@Test
public void testCreateTwoPivotTablesInTwoSheets() throws IOException {
    XSSFWorkbook wb = setupSheet();
    XSSFSheet sheet = wb.getSheetAt(0);
    assertNotNull(wb);
    assertNotNull(sheet);
    XSSFPivotTable pivotTable = sheet.createPivotTable(new AreaReference("A1:B2", SpreadsheetVersion.EXCEL2007), new CellReference("H5"));
    assertNotNull(pivotTable);
    assertTrue(wb.getPivotTables().size() > 0);
    assertNotNull(wb);
    XSSFSheet sheet2 = wb.createSheet();
    XSSFPivotTable pivotTable2 = sheet2.createPivotTable(new AreaReference("A1:B2", SpreadsheetVersion.EXCEL2007), new CellReference("H5"), sheet);
    assertNotNull(pivotTable2);
    assertTrue(wb.getPivotTables().size() > 1);
    wb.close();
}

Example 15 with AreaReference

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

the class TestXSSFSheet method testCreatePivotTable.

@Test
public void testCreatePivotTable() throws IOException {
    XSSFWorkbook wb = setupSheet();
    XSSFSheet sheet = wb.getSheetAt(0);
    assertNotNull(wb);
    assertNotNull(sheet);
    XSSFPivotTable pivotTable = sheet.createPivotTable(new AreaReference("A1:B2", SpreadsheetVersion.EXCEL2007), new CellReference("H5"));
    assertNotNull(pivotTable);
    assertTrue(wb.getPivotTables().size() > 0);
    wb.close();
}