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

Example 6 with AreaReference

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

the class XSSFPivotTable method createDefaultDataColumns.

@Beta
protected void createDefaultDataColumns() {
    CTPivotFields pivotFields;
    if (pivotTableDefinition.getPivotFields() != null) {
        pivotFields = pivotTableDefinition.getPivotFields();
    } else {
        pivotFields = pivotTableDefinition.addNewPivotFields();
    }
    AreaReference sourceArea = getPivotArea();
    int firstColumn = sourceArea.getFirstCell().getCol();
    int lastColumn = sourceArea.getLastCell().getCol();
    CTPivotField pivotField;
    for (int i = firstColumn; i <= lastColumn; i++) {
        pivotField = pivotFields.addNewPivotField();
        pivotField.setDataField(false);
        pivotField.setShowAll(false);
    }
    pivotFields.setCount(pivotFields.sizeOfPivotFieldArray());
}

Example 7 with AreaReference

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

the class XSSFName method getSheetName.

/**
     * Get the sheets name which this named range is referenced to
     *
     * @return sheet name, which this named range referred to.
     * Empty string if the referenced sheet name weas not found.
     */
public String getSheetName() {
    if (_ctName.isSetLocalSheetId()) {
        // Given as explicit sheet id
        int sheetId = (int) _ctName.getLocalSheetId();
        return _workbook.getSheetName(sheetId);
    }
    String ref = getRefersToFormula();
    AreaReference areaRef = new AreaReference(ref);
    return areaRef.getFirstCell().getSheetName();
}

Example 8 with AreaReference

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

the class BaseTestXSSFPivotTable method testPivotTableSheetNamesAreCaseInsensitive.

@Test
public void testPivotTableSheetNamesAreCaseInsensitive() {
    wb.setSheetName(0, "original");
    wb.setSheetName(1, "offset");
    XSSFSheet original = wb.getSheet("OriginaL");
    XSSFSheet offset = wb.getSheet("OffseT");
    // assume sheets are accessible via case-insensitive name
    assertNotNull(original);
    assertNotNull(offset);
    AreaReference source = new AreaReference("ORIGinal!A1:C2", _testDataProvider.getSpreadsheetVersion());
    // create a pivot table on the same sheet, case insensitive
    original.createPivotTable(source, new CellReference("W1"));
    // create a pivot table on a different sheet, case insensitive
    offset.createPivotTable(source, new CellReference("W1"));
}

Example 9 with AreaReference

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

the class TestAreaReference method testDiscontinousReference.

public void testDiscontinousReference() throws Exception {
    InputStream is = HSSFTestDataSamples.openSampleFileStream("44167.xls");
    HSSFWorkbook wb = new HSSFWorkbook(is);
    InternalWorkbook workbook = TestHSSFWorkbook.getInternalWorkbook(wb);
    HSSFEvaluationWorkbook eb = HSSFEvaluationWorkbook.create(wb);
    assertEquals(1, wb.getNumberOfNames());
    String sheetName = "Tabelle1";
    String rawRefA = "$C$10:$C$14";
    String rawRefB = "$C$16:$C$18";
    String refA = sheetName + "!" + rawRefA;
    String refB = sheetName + "!" + rawRefB;
    String ref = refA + "," + refB;
    // Check the low level record
    NameRecord nr = workbook.getNameRecord(0);
    assertNotNull(nr);
    assertEquals("test", nr.getNameText());
    Ptg[] def = nr.getNameDefinition();
    assertEquals(4, def.length);
    MemFuncPtg ptgA = (MemFuncPtg) def[0];
    Area3DPtg ptgB = (Area3DPtg) def[1];
    Area3DPtg ptgC = (Area3DPtg) def[2];
    UnionPtg ptgD = (UnionPtg) def[3];
    assertEquals("", ptgA.toFormulaString());
    assertEquals(refA, ptgB.toFormulaString(eb));
    assertEquals(refB, ptgC.toFormulaString(eb));
    assertEquals(",", ptgD.toFormulaString());
    assertEquals(ref, HSSFFormulaParser.toFormulaString(wb, nr.getNameDefinition()));
    // Check the high level definition
    int idx = wb.getNameIndex("test");
    assertEquals(0, idx);
    HSSFName aNamedCell = wb.getNameAt(idx);
    // Should have 2 references
    assertEquals(ref, aNamedCell.getRefersToFormula());
    // Check the parsing of the reference into cells
    assertFalse(AreaReference.isContiguous(aNamedCell.getRefersToFormula()));
    AreaReference[] arefs = AreaReference.generateContiguous(aNamedCell.getRefersToFormula());
    assertEquals(2, arefs.length);
    assertEquals(refA, arefs[0].formatAsString());
    assertEquals(refB, arefs[1].formatAsString());
    for (AreaReference ar : arefs) {
        confirmResolveCellRef(wb, ar.getFirstCell());
        confirmResolveCellRef(wb, ar.getLastCell());
    }
}

Example 10 with AreaReference

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

the class TestAreaReference method testSpecialSheetNames.

public void testSpecialSheetNames() {
    AreaReference ar;
    ar = new AreaReference("'Sheet A'!A1:A1");
    confirmAreaSheetName(ar, "Sheet A", "'Sheet A'!A1");
    ar = new AreaReference("'Hey! Look Here!'!A1:A1");
    confirmAreaSheetName(ar, "Hey! Look Here!", "'Hey! Look Here!'!A1");
    ar = new AreaReference("'O''Toole'!A1:B2");
    confirmAreaSheetName(ar, "O'Toole", "'O''Toole'!A1:B2");
    ar = new AreaReference("'one:many'!A1:B2");
    confirmAreaSheetName(ar, "one:many", "'one:many'!A1:B2");
}