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

Example 1 with HSSFEvaluationWorkbook

use of org.apache.poi.hssf.usermodel.HSSFEvaluationWorkbook 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();
    }
}

Example 2 with HSSFEvaluationWorkbook

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

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

the class TestDec2Bin method createContext.

private OperationEvaluationContext createContext() {
    HSSFWorkbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet();
    Row row = sheet.createRow(0);
    Cell cell = row.createCell(0);
    cell.setCellValue("13.43");
    cell = row.createCell(1);
    cell.setCellValue("8");
    cell = row.createCell(2);
    cell.setCellValue("-8");
    cell = row.createCell(3);
    cell.setCellValue("1");
    HSSFEvaluationWorkbook workbook = HSSFEvaluationWorkbook.create(wb);
    WorkbookEvaluator workbookEvaluator = new WorkbookEvaluator(workbook, new IStabilityClassifier() {

        @Override
        public boolean isCellFinal(int sheetIndex, int rowIndex, int columnIndex) {
            return true;
        }
    }, null);
    OperationEvaluationContext ctx = new OperationEvaluationContext(workbookEvaluator, workbook, 0, 0, 0, null);
    return ctx;
}

Example 4 with HSSFEvaluationWorkbook

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

the class TestBin2Dec method createContext.

private OperationEvaluationContext createContext() {
    HSSFWorkbook wb = new HSSFWorkbook();
    wb.createSheet();
    HSSFEvaluationWorkbook workbook = HSSFEvaluationWorkbook.create(wb);
    WorkbookEvaluator workbookEvaluator = new WorkbookEvaluator(workbook, new IStabilityClassifier() {

        @Override
        public boolean isCellFinal(int sheetIndex, int rowIndex, int columnIndex) {
            return true;
        }
    }, null);
    OperationEvaluationContext ctx = new OperationEvaluationContext(workbookEvaluator, workbook, 0, 0, 0, null);
    return ctx;
}

Example 5 with HSSFEvaluationWorkbook

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

the class TestHex2Dec method createContext.

private OperationEvaluationContext createContext() {
    HSSFWorkbook wb = new HSSFWorkbook();
    wb.createSheet();
    HSSFEvaluationWorkbook workbook = HSSFEvaluationWorkbook.create(wb);
    WorkbookEvaluator workbookEvaluator = new WorkbookEvaluator(workbook, new IStabilityClassifier() {

        @Override
        public boolean isCellFinal(int sheetIndex, int rowIndex, int columnIndex) {
            return true;
        }
    }, null);
    OperationEvaluationContext ctx = new OperationEvaluationContext(workbookEvaluator, workbook, 0, 0, 0, null);
    return ctx;
}