Examples with HSSFName org.apache.poi.hssf.usermodel.HSSFName used on opensource projects

Example 1 with HSSFName

use of org.apache.poi.hssf.usermodel.HSSFName in project poi by apache.

the class TestFormulaParser method testParseComplexName.

/**
     * In bug 47078, POI had trouble evaluating a defined name flagged as 'complex'.
     * POI should also be able to parse such defined names.
     */
@Test
public void testParseComplexName() throws IOException {
    // Mock up a spreadsheet to match the critical details of the sample
    HSSFWorkbook wb = new HSSFWorkbook();
    wb.createSheet("Sheet1");
    HSSFName definedName = wb.createName();
    definedName.setNameName("foo");
    definedName.setRefersToFormula("Sheet1!B2");
    // Set the complex flag - POI doesn't usually manipulate this flag
    NameRecord nameRec = TestHSSFName.getNameRecord(definedName);
    // 0x10 -> complex
    nameRec.setOptionFlag((short) 0x10);
    Ptg[] result;
    try {
        result = HSSFFormulaParser.parse("1+foo", wb);
    } catch (FormulaParseException e) {
        if (e.getMessage().equals("Specified name 'foo' is not a range as expected.")) {
            fail("Identified bug 47078c");
        }
        wb.close();
        throw e;
    }
    confirmTokenClasses(result, IntPtg.class, NamePtg.class, AddPtg.class);
    wb.close();
}

Example 2 with HSSFName

use of org.apache.poi.hssf.usermodel.HSSFName in project poi by apache.

the class TestFormulaParser method testNamesWithUnderscore.

/** bug 49725, defined names with underscore */
@Test
public void testNamesWithUnderscore() throws IOException {
    //or new XSSFWorkbook();
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("NamesWithUnderscore");
    HSSFName nm;
    nm = wb.createName();
    nm.setNameName("DA6_LEO_WBS_Number");
    nm.setRefersToFormula("33");
    nm = wb.createName();
    nm.setNameName("DA6_LEO_WBS_Name");
    nm.setRefersToFormula("33");
    nm = wb.createName();
    nm.setNameName("A1_");
    nm.setRefersToFormula("22");
    nm = wb.createName();
    nm.setNameName("_A1");
    nm.setRefersToFormula("11");
    nm = wb.createName();
    nm.setNameName("A_1");
    nm.setRefersToFormula("44");
    nm = wb.createName();
    nm.setNameName("A_1_");
    nm.setRefersToFormula("44");
    HSSFRow row = sheet.createRow(0);
    HSSFCell cell = row.createCell(0);
    cell.setCellFormula("DA6_LEO_WBS_Number*2");
    assertEquals("DA6_LEO_WBS_Number*2", cell.getCellFormula());
    cell.setCellFormula("(A1_*_A1+A_1)/A_1_");
    assertEquals("(A1_*_A1+A_1)/A_1_", cell.getCellFormula());
    cell.setCellFormula("INDEX(DA6_LEO_WBS_Name,MATCH($A3,DA6_LEO_WBS_Number,0))");
    assertEquals("INDEX(DA6_LEO_WBS_Name,MATCH($A3,DA6_LEO_WBS_Number,0))", cell.getCellFormula());
    wb.close();
}

Example 3 with HSSFName

use of org.apache.poi.hssf.usermodel.HSSFName in project poi by apache.

the class TestNameRecord method testFormulaGeneral.

public void testFormulaGeneral() throws IOException {
    // perhaps this testcase belongs on TestHSSFName
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFName name = wb.createName();
    wb.createSheet("Sheet1");
    name.setNameName("test");
    name.setRefersToFormula("Sheet1!A1+Sheet1!A2");
    assertEquals("Sheet1!A1+Sheet1!A2", name.getRefersToFormula());
    name.setRefersToFormula("5*6");
    assertEquals("5*6", name.getRefersToFormula());
    wb.close();
}

Example 4 with HSSFName

use of org.apache.poi.hssf.usermodel.HSSFName 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 5 with HSSFName

use of org.apache.poi.hssf.usermodel.HSSFName in project poi by apache.

the class TestIndirect method createWBA.

private static HSSFWorkbook createWBA() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet1 = wb.createSheet("Sheet1");
    HSSFSheet sheet2 = wb.createSheet("Sheet2");
    HSSFSheet sheet3 = wb.createSheet("John's sales");
    createDataRow(sheet1, 0, 11, 12, 13, 14);
    createDataRow(sheet1, 1, 21, 22, 23, 24);
    createDataRow(sheet1, 2, 31, 32, 33, 34);
    createDataRow(sheet2, 0, 50, 55, 60, 65);
    createDataRow(sheet2, 1, 51, 56, 61, 66);
    createDataRow(sheet2, 2, 52, 57, 62, 67);
    createDataRow(sheet3, 0, 30, 31, 32);
    createDataRow(sheet3, 1, 33, 34, 35);
    HSSFName name1 = wb.createName();
    name1.setNameName("sales1");
    name1.setRefersToFormula("Sheet1!A1:D1");
    HSSFName name2 = wb.createName();
    name2.setNameName("sales2");
    name2.setRefersToFormula("Sheet2!B1:C3");
    HSSFRow row = sheet1.createRow(3);
    //A4
    row.createCell(0).setCellValue("sales1");
    //B4
    row.createCell(1).setCellValue("sales2");
    return wb;
}