Examples with NameRecord org.apache.poi.hssf.record.NameRecord used on opensource projects

Example 11 with NameRecord

use of org.apache.poi.hssf.record.NameRecord 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 12 with NameRecord

use of org.apache.poi.hssf.record.NameRecord in project poi by apache.

the class TestSanityChecker method testCheckRecordOrder.

@Test
public void testCheckRecordOrder() {
    final SanityChecker c = new SanityChecker();
    List<Record> records = new ArrayList<Record>();
    records.add(new BOFRecord());
    records.add(INTERFACEHDR);
    records.add(createBoundSheetRec());
    records.add(EOFRecord.instance);
    CheckRecord[] check = { new CheckRecord(BOFRecord.class, '1'), new CheckRecord(InterfaceHdrRecord.class, '0'), new CheckRecord(BoundSheetRecord.class, 'M'), new CheckRecord(NameRecord.class, '*'), new CheckRecord(EOFRecord.class, '1') };
    // check pass
    c.checkRecordOrder(records, check);
    records.add(2, createBoundSheetRec());
    c.checkRecordOrder(records, check);
    // optional record missing
    records.remove(1);
    c.checkRecordOrder(records, check);
    records.add(3, new NameRecord());
    // optional multiple record occurs more than one time
    records.add(3, new NameRecord());
    c.checkRecordOrder(records, check);
    // check fail
    confirmBadRecordOrder(check, new Record[] { new BOFRecord(), createBoundSheetRec(), INTERFACEHDR, EOFRecord.instance });
    confirmBadRecordOrder(check, new Record[] { new BOFRecord(), INTERFACEHDR, createBoundSheetRec(), INTERFACEHDR, EOFRecord.instance });
    confirmBadRecordOrder(check, new Record[] { new BOFRecord(), createBoundSheetRec(), new NameRecord(), EOFRecord.instance, new NameRecord() });
    confirmBadRecordOrder(check, new Record[] { INTERFACEHDR, createBoundSheetRec(), EOFRecord.instance });
    confirmBadRecordOrder(check, new Record[] { new BOFRecord(), INTERFACEHDR, EOFRecord.instance });
    confirmBadRecordOrder(check, new Record[] { INTERFACEHDR, createBoundSheetRec(), new BOFRecord(), EOFRecord.instance });
    confirmBadRecordOrder(check, new Record[] { new BOFRecord(), createBoundSheetRec(), INTERFACEHDR, EOFRecord.instance });
}

Example 13 with NameRecord

use of org.apache.poi.hssf.record.NameRecord 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 14 with NameRecord

use of org.apache.poi.hssf.record.NameRecord in project poi by apache.

the class TestHSSFWorkbook method findBuiltInNameRecord.

/**
     * Test to make sure that NameRecord.getSheetNumber() is interpreted as a
     * 1-based sheet tab index (not a 1-based extern sheet index)
     */
@Test
public void findBuiltInNameRecord() throws IOException {
    // testRRaC has multiple (3) built-in name records
    // The second print titles name record has getSheetNumber()==4
    HSSFWorkbook wb1 = HSSFTestDataSamples.openSampleWorkbook("testRRaC.xls");
    NameRecord nr;
    assertEquals(3, wb1.getWorkbook().getNumNames());
    nr = wb1.getWorkbook().getNameRecord(2);
    // TODO - render full row and full column refs properly
    // 1:1
    assertEquals("Sheet2!$A$1:$IV$1", HSSFFormulaParser.toFormulaString(wb1, nr.getNameDefinition()));
    try {
        wb1.getSheetAt(3).setRepeatingRows(CellRangeAddress.valueOf("9:12"));
        wb1.getSheetAt(3).setRepeatingColumns(CellRangeAddress.valueOf("E:F"));
    } catch (RuntimeException e) {
        if (e.getMessage().equals("Builtin (7) already exists for sheet (4)")) {
            // there was a problem in the code which locates the existing print titles name record
            fail("Identified bug 45720b");
        }
        wb1.close();
        throw e;
    }
    HSSFWorkbook wb2 = HSSFTestDataSamples.writeOutAndReadBack(wb1);
    wb1.close();
    assertEquals(3, wb2.getWorkbook().getNumNames());
    nr = wb2.getWorkbook().getNameRecord(2);
    // E:F,9:12
    assertEquals("Sheet2!E:F,Sheet2!$A$9:$IV$12", HSSFFormulaParser.toFormulaString(wb2, nr.getNameDefinition()));
    wb2.close();
}

Example 15 with NameRecord

use of org.apache.poi.hssf.record.NameRecord in project poi by apache.

the class TestHSSFFormulaEvaluator method testDefinedNameWithComplexFlag_bug47048.

/**
	 * When evaluating defined names, POI has to decide whether it is capable.  Currently
	 * (May2009) POI only supports simple cell and area refs.<br/>
	 * The sample spreadsheet (bugzilla attachment 23508) had a name flagged as 'complex'
	 * which contained a simple area ref.  It is not clear what the 'complex' flag is used
	 * for but POI should look elsewhere to decide whether it can evaluate the name.
	 */
@Test
public void testDefinedNameWithComplexFlag_bug47048() throws IOException {
    // Mock up a spreadsheet to match the critical details of the sample
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("Input");
    HSSFName definedName = wb.createName();
    definedName.setNameName("Is_Multicar_Vehicle");
    definedName.setRefersToFormula("Input!$B$17:$G$17");
    // Set up some data and the formula
    HSSFRow row17 = sheet.createRow(16);
    row17.createCell(0).setCellValue(25.0);
    row17.createCell(1).setCellValue(1.33);
    row17.createCell(2).setCellValue(4.0);
    HSSFRow row = sheet.createRow(0);
    HSSFCell cellA1 = row.createCell(0);
    cellA1.setCellFormula("SUM(Is_Multicar_Vehicle)");
    // Set the complex flag - POI doesn't usually manipulate this flag
    NameRecord nameRec = TestHSSFName.getNameRecord(definedName);
    // 0x10 -> complex
    nameRec.setOptionFlag((short) 0x10);
    HSSFFormulaEvaluator hsf = new HSSFFormulaEvaluator(wb);
    CellValue value;
    try {
        value = hsf.evaluate(cellA1);
        assertEquals(CellType.NUMERIC, value.getCellTypeEnum());
        assertEquals(5.33, value.getNumberValue(), 0.0);
    } catch (RuntimeException e) {
        if (e.getMessage().equals("Don't now how to evalate name 'Is_Multicar_Vehicle'")) {
            fail("Identified bug 47048a");
        }
        throw e;
    } finally {
        wb.close();
    }
}