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

Example 91 with HSSFWorkbook

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

the class TestTime method setUp.

@Before
public void setUp() {
    wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("new sheet");
    style = wb.createCellStyle();
    HSSFDataFormat fmt = wb.createDataFormat();
    style.setDataFormat(fmt.getFormat("hh:mm:ss"));
    cell11 = sheet.createRow(0).createCell(0);
    form = new HSSFDataFormatter();
    evaluator = new HSSFFormulaEvaluator(wb);
}

Example 92 with HSSFWorkbook

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

the class TestSubtotal method testMin.

@Test
public void testMin() throws IOException {
    Workbook wb = new HSSFWorkbook();
    FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
    Sheet sh = wb.createSheet();
    Cell a1 = sh.createRow(1).createCell(1);
    a1.setCellValue(1);
    Cell a2 = sh.createRow(2).createCell(1);
    a2.setCellValue(3);
    Cell a3 = sh.createRow(3).createCell(1);
    a3.setCellFormula("SUBTOTAL(5,B2:B3)");
    Cell a4 = sh.createRow(4).createCell(1);
    a4.setCellValue(1);
    Cell a5 = sh.createRow(5).createCell(1);
    a5.setCellValue(7);
    Cell a6 = sh.createRow(6).createCell(1);
    a6.setCellFormula("SUBTOTAL(5,B2:B6)*2 + 2");
    Cell a7 = sh.createRow(7).createCell(1);
    a7.setCellFormula("SUBTOTAL(5,B2:B7)");
    Cell a8 = sh.createRow(8).createCell(1);
    a8.setCellFormula("SUBTOTAL(5,B2,B3,B4,B5,B6,B7,B8)");
    fe.evaluateAll();
    assertEquals(1.0, a3.getNumericCellValue(), 0);
    assertEquals(4.0, a6.getNumericCellValue(), 0);
    assertEquals(1.0, a7.getNumericCellValue(), 0);
    assertEquals(1.0, a8.getNumericCellValue(), 0);
    wb.close();
}

Example 93 with HSSFWorkbook

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

the class TestRank method testFromFile.

public void testFromFile() {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("rank.xls");
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    HSSFSheet example1 = wb.getSheet("Example 1");
    HSSFCell ex1cell1 = example1.getRow(7).getCell(0);
    assertEquals(3.0, fe.evaluate(ex1cell1).getNumberValue());
    HSSFCell ex1cell2 = example1.getRow(8).getCell(0);
    assertEquals(5.0, fe.evaluate(ex1cell2).getNumberValue());
    HSSFSheet example2 = wb.getSheet("Example 2");
    for (int rownum = 1; rownum <= 10; rownum++) {
        HSSFCell cell = example2.getRow(rownum).getCell(2);
        //cached formula result
        double cachedResult = cell.getNumericCellValue();
        assertEquals(cachedResult, fe.evaluate(cell).getNumberValue());
    }
    HSSFSheet example3 = wb.getSheet("Example 3");
    for (int rownum = 1; rownum <= 10; rownum++) {
        HSSFCell cellD = example3.getRow(rownum).getCell(3);
        //cached formula result
        double cachedResultD = cellD.getNumericCellValue();
        assertEquals(new CellReference(cellD).formatAsString(), cachedResultD, fe.evaluate(cellD).getNumberValue());
        HSSFCell cellE = example3.getRow(rownum).getCell(4);
        //cached formula result
        double cachedResultE = cellE.getNumericCellValue();
        assertEquals(new CellReference(cellE).formatAsString(), cachedResultE, fe.evaluate(cellE).getNumberValue());
        HSSFCell cellF = example3.getRow(rownum).getCell(5);
        //cached formula result
        double cachedResultF = cellF.getNumericCellValue();
        assertEquals(new CellReference(cellF).formatAsString(), cachedResultF, fe.evaluate(cellF).getNumberValue());
    }
}

Example 94 with HSSFWorkbook

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

the class TestExternalNameReference method testEvaluate.

public void testEvaluate() throws Exception {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("XRefCalc.xls");
    HSSFWorkbook wb2 = HSSFTestDataSamples.openSampleWorkbook("XRefCalcData.xls");
    CellReference cellRef = new CellReference(wb.getName("QUANT").getRefersToFormula());
    HSSFCell cell = wb.getSheet(cellRef.getSheetName()).getRow(cellRef.getRow()).getCell((int) cellRef.getCol());
    cell.setCellValue(NEW_QUANT);
    cell = wb2.getSheet("CostSheet").getRow(1).getCell(1);
    cell.setCellValue(NEW_PART_COST);
    HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
    HSSFFormulaEvaluator evaluatorCost = new HSSFFormulaEvaluator(wb2);
    String[] bookNames = { "XRefCalc.xls", "XRefCalcData.xls" };
    HSSFFormulaEvaluator[] evaluators = { evaluator, evaluatorCost };
    HSSFFormulaEvaluator.setupEnvironment(bookNames, evaluators);
    cellRef = new CellReference(wb.getName("UNITCOST").getRefersToFormula());
    HSSFCell uccell = wb.getSheet(cellRef.getSheetName()).getRow(cellRef.getRow()).getCell((int) cellRef.getCol());
    cellRef = new CellReference(wb.getName("COST").getRefersToFormula());
    HSSFCell ccell = wb.getSheet(cellRef.getSheetName()).getRow(cellRef.getRow()).getCell((int) cellRef.getCol());
    cellRef = new CellReference(wb.getName("TOTALCOST").getRefersToFormula());
    HSSFCell tccell = wb.getSheet(cellRef.getSheetName()).getRow(cellRef.getRow()).getCell((int) cellRef.getCol());
    evaluator.evaluateFormulaCellEnum(uccell);
    evaluator.evaluateFormulaCellEnum(ccell);
    evaluator.evaluateFormulaCellEnum(tccell);
    assertEquals(NEW_PART_COST, uccell.getNumericCellValue());
    assertEquals(NEW_PART_COST * NEW_QUANT, ccell.getNumericCellValue());
    assertEquals(NEW_PART_COST * NEW_QUANT * MARKUP_COST_2, tccell.getNumericCellValue());
}

Example 95 with HSSFWorkbook

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

the class TestPercentPtg method testReading.

/**
     * Tests reading a file containing this ptg.
     */
public void testReading() {
    HSSFWorkbook workbook = loadWorkbook("PercentPtg.xls");
    HSSFSheet sheet = workbook.getSheetAt(0);
    assertEquals("Wrong numeric value for original number", 53000.0, sheet.getRow(0).getCell(0).getNumericCellValue(), 0.0);
    assertEquals("Wrong numeric value for percent formula result", 5300.0, sheet.getRow(1).getCell(0).getNumericCellValue(), 0.0);
    assertEquals("Wrong formula string for percent formula", "A1*10%", sheet.getRow(1).getCell(0).getCellFormula());
}