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

Example 31 with HSSFWorkbook

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

the class TestFormulaParser method confirmArgCountMsg.

private static void confirmArgCountMsg(String formula, String expectedMessage) throws IOException {
    HSSFWorkbook book = new HSSFWorkbook();
    try {
        HSSFFormulaParser.parse(formula, book);
        fail("Didn't get parse exception as expected");
    } catch (FormulaParseException e) {
        confirmParseException(e, expectedMessage);
    }
    book.close();
}

Example 32 with HSSFWorkbook

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

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

the class TestFormulaParser method testZeroRowRefs.

/**
     * Zero is not a valid row number so cell references like 'A0' are not valid.
     * Actually, they should be treated like defined names.
     * <br/>
     * In addition, leading zeros (on the row component) should be removed from cell
     * references during parsing.
     */
@Test
public void testZeroRowRefs() throws IOException {
    // bad because zero is not a valid row number
    String badCellRef = "B0";
    // this should get parsed as "B1"
    String leadingZeroCellRef = "B000001";
    HSSFWorkbook wb = new HSSFWorkbook();
    try {
        HSSFFormulaParser.parse(badCellRef, wb);
        fail("Identified bug 47312b - Shouldn't be able to parse cell ref '" + badCellRef + "'.");
    } catch (FormulaParseException e) {
        // expected during successful test
        confirmParseException(e, "Specified named range '" + badCellRef + "' does not exist in the current workbook.");
    }
    Ptg[] ptgs;
    try {
        ptgs = HSSFFormulaParser.parse(leadingZeroCellRef, wb);
        assertEquals("B1", ((RefPtg) ptgs[0]).toFormulaString());
    } catch (FormulaParseException e) {
        confirmParseException(e, "Specified named range '" + leadingZeroCellRef + "' does not exist in the current workbook.");
        // close but no cigar
        fail("Identified bug 47312c - '" + leadingZeroCellRef + "' should parse as 'B1'.");
    }
    // create a defined name called 'B0' and try again
    Name n = wb.createName();
    n.setNameName("B0");
    n.setRefersToFormula("1+1");
    ptgs = HSSFFormulaParser.parse("B0", wb);
    confirmTokenClasses(ptgs, NamePtg.class);
    wb.close();
}

Example 34 with HSSFWorkbook

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

the class TestFormulaParser method testParseAbnormalSheetNamesAndRanges_bug42448.

/**
     * See the related/similar test: {@link BaseTestBugzillaIssues#bug42448()}
     */
@Test
public void testParseAbnormalSheetNamesAndRanges_bug42448() throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    wb.createSheet("A");
    try {
        HSSFFormulaParser.parse("SUM(A!C7:A!C67)", wb);
    } catch (StringIndexOutOfBoundsException e) {
        fail("Identified bug 42448");
    }
    // the exact example from the bugzilla description:
    HSSFFormulaParser.parse("SUMPRODUCT(A!C7:A!C67, B8:B68) / B69", wb);
    wb.close();
}

Example 35 with HSSFWorkbook

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

the class TestFormulaParser method testSetFormulaWithRowBeyond32768_Bug44539.

@Test
public void testSetFormulaWithRowBeyond32768_Bug44539() throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();
    wb.setSheetName(0, "Sheet1");
    HSSFRow row = sheet.createRow(0);
    HSSFCell cell = row.createCell(0);
    cell.setCellFormula("SUM(A32769:A32770)");
    if ("SUM(A-32767:A-32766)".equals(cell.getCellFormula())) {
        fail("Identified bug 44539");
    }
    assertEquals("SUM(A32769:A32770)", cell.getCellFormula());
    wb.close();
}