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

Example 46 with CellRangeAddress

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

the class TestHSSFSheet method autoFilter.

@Test
public void autoFilter() throws IOException {
    HSSFWorkbook wb1 = new HSSFWorkbook();
    HSSFSheet sh = wb1.createSheet();
    InternalWorkbook iwb = wb1.getWorkbook();
    InternalSheet ish = sh.getSheet();
    assertNull(iwb.getSpecificBuiltinRecord(NameRecord.BUILTIN_FILTER_DB, 1));
    assertNull(ish.findFirstRecordBySid(AutoFilterInfoRecord.sid));
    CellRangeAddress range = CellRangeAddress.valueOf("A1:B10");
    sh.setAutoFilter(range);
    NameRecord name = iwb.getSpecificBuiltinRecord(NameRecord.BUILTIN_FILTER_DB, 1);
    assertNotNull(name);
    // The built-in name for auto-filter must consist of a single Area3d Ptg.
    Ptg[] ptg = name.getNameDefinition();
    assertEquals("The built-in name for auto-filter must consist of a single Area3d Ptg", 1, ptg.length);
    assertTrue("The built-in name for auto-filter must consist of a single Area3d Ptg", ptg[0] instanceof Area3DPtg);
    Area3DPtg aref = (Area3DPtg) ptg[0];
    assertEquals(range.getFirstColumn(), aref.getFirstColumn());
    assertEquals(range.getFirstRow(), aref.getFirstRow());
    assertEquals(range.getLastColumn(), aref.getLastColumn());
    assertEquals(range.getLastRow(), aref.getLastRow());
    // verify  AutoFilterInfoRecord
    AutoFilterInfoRecord afilter = (AutoFilterInfoRecord) ish.findFirstRecordBySid(AutoFilterInfoRecord.sid);
    assertNotNull(afilter);
    //filter covers two columns
    assertEquals(2, afilter.getNumEntries());
    HSSFPatriarch dr = sh.getDrawingPatriarch();
    assertNotNull(dr);
    HSSFSimpleShape comboBoxShape = (HSSFSimpleShape) dr.getChildren().get(0);
    assertEquals(comboBoxShape.getShapeType(), HSSFSimpleShape.OBJECT_TYPE_COMBO_BOX);
    // ObjRecord will appear after serializetion
    assertNull(ish.findFirstRecordBySid(ObjRecord.sid));
    HSSFWorkbook wb2 = HSSFTestDataSamples.writeOutAndReadBack(wb1);
    wb1.close();
    sh = wb2.getSheetAt(0);
    ish = sh.getSheet();
    ObjRecord objRecord = (ObjRecord) ish.findFirstRecordBySid(ObjRecord.sid);
    List<SubRecord> subRecords = objRecord.getSubRecords();
    assertEquals(3, subRecords.size());
    assertTrue(subRecords.get(0) instanceof CommonObjectDataSubRecord);
    // must be present, see Bug 51481
    assertTrue(subRecords.get(1) instanceof FtCblsSubRecord);
    assertTrue(subRecords.get(2) instanceof LbsDataSubRecord);
    wb2.close();
}

Example 47 with CellRangeAddress

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

the class TestHSSFName method testRepeatingRowsAndColumsNames.

@Test
public void testRepeatingRowsAndColumsNames() throws Exception {
    // First test that setting RR&C for same sheet more than once only creates a
    // single  Print_Titles built-in record
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("FirstSheet");
    // set repeating rows and columns twice for the first sheet
    CellRangeAddress cra = CellRangeAddress.valueOf("A1:A3");
    for (int i = 0; i < 2; i++) {
        sheet.setRepeatingColumns(cra);
        sheet.setRepeatingRows(cra);
        sheet.createFreezePane(0, 3);
    }
    assertEquals(1, wb.getNumberOfNames());
    HSSFName nr1 = wb.getNameAt(0);
    assertEquals("Print_Titles", nr1.getNameName());
    // TODO - full column references not rendering properly, absolute markers not present either
    // assertEquals("FirstSheet!$A:$A,FirstSheet!$1:$3", nr1.getRefersToFormula());
    assertEquals("FirstSheet!A:A,FirstSheet!$A$1:$IV$3", nr1.getRefersToFormula());
    // Save and re-open
    HSSFWorkbook nwb = HSSFTestDataSamples.writeOutAndReadBack(wb);
    wb.close();
    assertEquals(1, nwb.getNumberOfNames());
    nr1 = nwb.getNameAt(0);
    assertEquals("Print_Titles", nr1.getNameName());
    assertEquals("FirstSheet!A:A,FirstSheet!$A$1:$IV$3", nr1.getRefersToFormula());
    // check that setting RR&C on a second sheet causes a new Print_Titles built-in
    // name to be created
    sheet = nwb.createSheet("SecondSheet");
    cra = CellRangeAddress.valueOf("B1:C1");
    sheet.setRepeatingColumns(cra);
    sheet.setRepeatingRows(cra);
    assertEquals(2, nwb.getNumberOfNames());
    HSSFName nr2 = nwb.getNameAt(1);
    assertEquals("Print_Titles", nr2.getNameName());
    assertEquals("SecondSheet!B:C,SecondSheet!$A$1:$IV$1", nr2.getRefersToFormula());
    nwb.close();
}

Example 48 with CellRangeAddress

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

the class TestDataValidation method testGetDataValidationsAny.

@Test
public void testGetDataValidationsAny() throws Exception {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();
    List<HSSFDataValidation> list = sheet.getDataValidations();
    assertEquals(0, list.size());
    DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
    DataValidationConstraint constraint = dataValidationHelper.createNumericConstraint(ValidationType.ANY, OperatorType.IGNORED, null, null);
    CellRangeAddressList addressList = new CellRangeAddressList(1, 2, 3, 4);
    DataValidation validation = dataValidationHelper.createValidation(constraint, addressList);
    validation.setEmptyCellAllowed(true);
    validation.createErrorBox("error-title", "error-text");
    validation.createPromptBox("prompt-title", "prompt-text");
    sheet.addValidationData(validation);
    // <-- works
    list = sheet.getDataValidations();
    assertEquals(1, list.size());
    HSSFDataValidation dv = list.get(0);
    {
        CellRangeAddressList regions = dv.getRegions();
        assertEquals(1, regions.countRanges());
        CellRangeAddress address = regions.getCellRangeAddress(0);
        assertEquals(1, address.getFirstRow());
        assertEquals(2, address.getLastRow());
        assertEquals(3, address.getFirstColumn());
        assertEquals(4, address.getLastColumn());
    }
    assertEquals(true, dv.getEmptyCellAllowed());
    assertEquals(false, dv.getSuppressDropDownArrow());
    assertEquals(true, dv.getShowErrorBox());
    assertEquals("error-title", dv.getErrorBoxTitle());
    assertEquals("error-text", dv.getErrorBoxText());
    assertEquals(true, dv.getShowPromptBox());
    assertEquals("prompt-title", dv.getPromptBoxTitle());
    assertEquals("prompt-text", dv.getPromptBoxText());
    DataValidationConstraint c = dv.getValidationConstraint();
    assertEquals(ValidationType.ANY, c.getValidationType());
    assertEquals(OperatorType.IGNORED, c.getOperator());
    wb.close();
}

Example 49 with CellRangeAddress

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

the class TestHSSFSheet method autoSizeColumn.

@Test
public void autoSizeColumn() throws IOException {
    HSSFWorkbook wb1 = HSSFTestDataSamples.openSampleWorkbook("43902.xls");
    String sheetName = "my sheet";
    HSSFSheet sheet = wb1.getSheet(sheetName);
    // Can't use literal numbers for column sizes, as
    //  will come out with different values on different
    //  machines based on the fonts available.
    // So, we use ranges, which are pretty large, but
    //  thankfully don't overlap!
    int minWithRow1And2 = 6400;
    int maxWithRow1And2 = 7800;
    int minWithRow1Only = 2750;
    int maxWithRow1Only = 3400;
    // autoSize the first column and check its size before the merged region (1,0,1,1) is set:
    // it has to be based on the 2nd row width
    sheet.autoSizeColumn((short) 0);
    assertTrue("Column autosized with only one row: wrong width", sheet.getColumnWidth(0) >= minWithRow1And2);
    assertTrue("Column autosized with only one row: wrong width", sheet.getColumnWidth(0) <= maxWithRow1And2);
    //create a region over the 2nd row and auto size the first column
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 1));
    assertNotNull(sheet.getMergedRegion(0));
    sheet.autoSizeColumn((short) 0);
    HSSFWorkbook wb2 = HSSFTestDataSamples.writeOutAndReadBack(wb1);
    // check that the autoSized column width has ignored the 2nd row
    // because it is included in a merged region (Excel like behavior)
    HSSFSheet sheet2 = wb2.getSheet(sheetName);
    assertTrue(sheet2.getColumnWidth(0) >= minWithRow1Only);
    assertTrue(sheet2.getColumnWidth(0) <= maxWithRow1Only);
    // remove the 2nd row merged region and check that the 2nd row value is used to the autoSizeColumn width
    sheet2.removeMergedRegion(1);
    sheet2.autoSizeColumn((short) 0);
    HSSFWorkbook wb3 = HSSFTestDataSamples.writeOutAndReadBack(wb2);
    HSSFSheet sheet3 = wb3.getSheet(sheetName);
    assertTrue(sheet3.getColumnWidth(0) >= minWithRow1And2);
    assertTrue(sheet3.getColumnWidth(0) <= maxWithRow1And2);
    wb3.close();
    wb2.close();
    wb1.close();
}

Example 50 with CellRangeAddress

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

the class BaseTestConditionalFormatting method testCreateDataBarFormatting.

@Test
public void testCreateDataBarFormatting() throws IOException {
    Workbook wb1 = _testDataProvider.createWorkbook();
    Sheet sheet = wb1.createSheet();
    String colorHex = "FFFFEB84";
    ExtendedColor color = wb1.getCreationHelper().createExtendedColor();
    color.setARGBHex(colorHex);
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(color);
    DataBarFormatting dbFmt = rule1.getDataBarFormatting();
    assertEquals(false, dbFmt.isIconOnly());
    assertEquals(true, dbFmt.isLeftToRight());
    assertEquals(0, dbFmt.getWidthMin());
    assertEquals(100, dbFmt.getWidthMax());
    assertColour(colorHex, dbFmt.getColor());
    dbFmt.getMinThreshold().setRangeType(RangeType.MIN);
    dbFmt.getMaxThreshold().setRangeType(RangeType.MAX);
    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:A5") };
    sheetCF.addConditionalFormatting(regions, rule1);
    // Save, re-load and re-check
    Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb1);
    wb1.close();
    sheet = wb2.getSheetAt(0);
    sheetCF = sheet.getSheetConditionalFormatting();
    assertEquals(1, sheetCF.getNumConditionalFormattings());
    ConditionalFormatting cf = sheetCF.getConditionalFormattingAt(0);
    assertEquals(1, cf.getNumberOfRules());
    rule1 = cf.getRule(0);
    dbFmt = rule1.getDataBarFormatting();
    assertEquals(ConditionType.DATA_BAR, rule1.getConditionType());
    assertEquals(false, dbFmt.isIconOnly());
    assertEquals(true, dbFmt.isLeftToRight());
    assertEquals(0, dbFmt.getWidthMin());
    assertEquals(100, dbFmt.getWidthMax());
    assertColour(colorHex, dbFmt.getColor());
    assertEquals(RangeType.MIN, dbFmt.getMinThreshold().getRangeType());
    assertEquals(RangeType.MAX, dbFmt.getMaxThreshold().getRangeType());
    assertEquals(null, dbFmt.getMinThreshold().getValue());
    assertEquals(null, dbFmt.getMaxThreshold().getValue());
    wb2.close();
}