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

Example 36 with HSSFWorkbook

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

the class TestFormulaParserEval method testEvaluateFormulaWithRowBeyond32768_Bug44539.

public void testEvaluateFormulaWithRowBeyond32768_Bug44539() {
    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)");
    // put some values in the cells to make the evaluation more interesting
    sheet.createRow(32768).createCell(0).setCellValue(31);
    sheet.createRow(32769).createCell(0).setCellValue(11);
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    CellValue result;
    try {
        result = fe.evaluate(cell);
    } catch (FormulaParseException e) {
        if (!e.getMessage().equals("Found reference to named range \"A\", but that named range wasn't defined!")) {
            throw new AssertionFailedError("Identifed bug 44539");
        }
        throw e;
    }
    assertEquals(CellType.NUMERIC, result.getCellTypeEnum());
    assertEquals(42.0, result.getNumberValue(), 0.0);
}

Example 37 with HSSFWorkbook

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

the class TestFormulaParser method testExponentialInSheet.

@Test
public void testExponentialInSheet() throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    wb.createSheet("Cash_Flow");
    HSSFSheet sheet = wb.createSheet("Test");
    HSSFRow row = sheet.createRow(0);
    HSSFCell cell = row.createCell(0);
    String formula;
    cell.setCellFormula("1.3E21/3");
    formula = cell.getCellFormula();
    assertEquals("Exponential formula string", "1.3E+21/3", formula);
    cell.setCellFormula("-1.3E21/3");
    formula = cell.getCellFormula();
    assertEquals("Exponential formula string", "-1.3E+21/3", formula);
    cell.setCellFormula("1322E21/3");
    formula = cell.getCellFormula();
    assertEquals("Exponential formula string", "1.322E+24/3", formula);
    cell.setCellFormula("-1322E21/3");
    formula = cell.getCellFormula();
    assertEquals("Exponential formula string", "-1.322E+24/3", formula);
    cell.setCellFormula("1.3E1/3");
    formula = cell.getCellFormula();
    assertEquals("Exponential formula string", "13/3", formula);
    cell.setCellFormula("-1.3E1/3");
    formula = cell.getCellFormula();
    assertEquals("Exponential formula string", "-13/3", formula);
    cell.setCellFormula("1.3E-4/3");
    formula = cell.getCellFormula();
    assertEquals("Exponential formula string", "0.00013/3", formula);
    cell.setCellFormula("-1.3E-4/3");
    formula = cell.getCellFormula();
    assertEquals("Exponential formula string", "-0.00013/3", formula);
    cell.setCellFormula("13E-15/3");
    formula = cell.getCellFormula();
    assertEquals("Exponential formula string", "0.000000000000013/3", formula);
    cell.setCellFormula("-13E-15/3");
    formula = cell.getCellFormula();
    assertEquals("Exponential formula string", "-0.000000000000013/3", formula);
    cell.setCellFormula("1.3E3/3");
    formula = cell.getCellFormula();
    assertEquals("Exponential formula string", "1300/3", formula);
    cell.setCellFormula("-1.3E3/3");
    formula = cell.getCellFormula();
    assertEquals("Exponential formula string", "-1300/3", formula);
    cell.setCellFormula("1300000000000000/3");
    formula = cell.getCellFormula();
    assertEquals("Exponential formula string", "1300000000000000/3", formula);
    cell.setCellFormula("-1300000000000000/3");
    formula = cell.getCellFormula();
    assertEquals("Exponential formula string", "-1300000000000000/3", formula);
    cell.setCellFormula("-10E-1/3.1E2*4E3/3E4");
    formula = cell.getCellFormula();
    assertEquals("Exponential formula string", "-1/310*4000/30000", formula);
    wb.close();
}

Example 38 with HSSFWorkbook

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

the class TestFormulaParser method test57196_Formula.

@Test
public void test57196_Formula() throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    Ptg[] ptgs = HSSFFormulaParser.parse("DEC2HEX(HEX2DEC(O8)-O2+D2)", wb, FormulaType.CELL, -1);
    assertNotNull("Ptg array should not be null", ptgs);
    confirmTokenClasses(ptgs, // ??
    NameXPtg.class, // ??
    NameXPtg.class, // O8
    RefPtg.class, // HEX2DEC
    FuncVarPtg.class, // O2
    RefPtg.class, SubtractPtg.class, // D2
    RefPtg.class, AddPtg.class, // DEC2HEX
    FuncVarPtg.class);
    RefPtg o8 = (RefPtg) ptgs[2];
    FuncVarPtg hex2Dec = (FuncVarPtg) ptgs[3];
    RefPtg o2 = (RefPtg) ptgs[4];
    RefPtg d2 = (RefPtg) ptgs[6];
    FuncVarPtg dec2Hex = (FuncVarPtg) ptgs[8];
    assertEquals("O8", o8.toFormulaString());
    assertEquals(255, hex2Dec.getFunctionIndex());
    //assertEquals("", hex2Dec.toString());
    assertEquals("O2", o2.toFormulaString());
    assertEquals("D2", d2.toFormulaString());
    assertEquals(255, dec2Hex.getFunctionIndex());
    wb.close();
}

Example 39 with HSSFWorkbook

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

the class TestFormulaParser method testToFormulaStringZeroArgFunction.

/**
     * Test for bug observable at svn revision 618865 (5-Feb-2008)<br/>
     * a formula consisting of a single no-arg function got rendered without the function braces
     */
@Test
public void testToFormulaStringZeroArgFunction() throws IOException {
    HSSFWorkbook book = new HSSFWorkbook();
    Ptg[] ptgs = { FuncPtg.create(10) };
    assertEquals("NA()", HSSFFormulaParser.toFormulaString(book, ptgs));
    book.close();
}

Example 40 with HSSFWorkbook

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

the class TestFormulaParser method testMacroFunction.

@Test
public void testMacroFunction() throws IOException {
    // testNames.xls contains a VB function called 'myFunc'
    final String testFile = "testNames.xls";
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook(testFile);
    try {
        HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.create(wb);
        //Expected ptg stack: [NamePtg(myFunc), StringPtg(arg), (additional operands go here...), FunctionPtg(myFunc)]
        Ptg[] ptg = FormulaParser.parse("myFunc(\"arg\")", book, FormulaType.CELL, -1);
        assertEquals(3, ptg.length);
        // the name gets encoded as the first operand on the stack
        NamePtg tname = (NamePtg) ptg[0];
        assertEquals("myFunc", tname.toFormulaString(book));
        // the function's arguments are pushed onto the stack from left-to-right as OperandPtgs
        StringPtg arg = (StringPtg) ptg[1];
        assertEquals("arg", arg.getValue());
        // The external FunctionPtg is the last Ptg added to the stack
        // During formula evaluation, this Ptg pops off the the appropriate number of
        // arguments (getNumberOfOperands()) and pushes the result on the stack
        //FuncVarPtg
        AbstractFunctionPtg tfunc = (AbstractFunctionPtg) ptg[2];
        assertTrue(tfunc.isExternalFunction());
        // confirm formula parsing is case-insensitive
        FormulaParser.parse("mYfUnC(\"arg\")", book, FormulaType.CELL, -1);
        // confirm formula parsing doesn't care about argument count or type
        // this should only throw an error when evaluating the formula.
        FormulaParser.parse("myFunc()", book, FormulaType.CELL, -1);
        FormulaParser.parse("myFunc(\"arg\", 0, TRUE)", book, FormulaType.CELL, -1);
        // A completely unknown formula name (not saved in workbook) should still be parseable and renderable
        // but will throw an NotImplementedFunctionException or return a #NAME? error value if evaluated.
        FormulaParser.parse("yourFunc(\"arg\")", book, FormulaType.CELL, -1);
        // Verify that myFunc and yourFunc were successfully added to Workbook names
        HSSFWorkbook wb2 = HSSFTestDataSamples.writeOutAndReadBack(wb);
        try {
            // HSSFWorkbook/EXCEL97-specific side-effects user-defined function names must be added to Workbook's defined names in order to be saved.
            assertNotNull(wb2.getName("myFunc"));
            assertEqualsIgnoreCase("myFunc", wb2.getName("myFunc").getNameName());
            assertNotNull(wb2.getName("yourFunc"));
            assertEqualsIgnoreCase("yourFunc", wb2.getName("yourFunc").getNameName());
        // Manually check to make sure file isn't corrupted
        // TODO: develop a process for occasionally manually reviewing workbooks
        // to verify workbooks are not corrupted
        /*
                final File fileIn = HSSFTestDataSamples.getSampleFile(testFile);
                final File reSavedFile = new File(fileIn.getParentFile(), fileIn.getName().replace(".xls", "-saved.xls"));
                FileOutputStream fos = new FileOutputStream(reSavedFile);
                wb2.write(fos);
                fos.close();
                */
        } finally {
            wb2.close();
        }
    } finally {
        wb.close();
    }
}