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

Example 51 with HSSFSheet

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

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

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

Example 54 with HSSFSheet

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

the class TestMirr method testEvaluateInSheet.

public void testEvaluateInSheet() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("Sheet1");
    HSSFRow row = sheet.createRow(0);
    row.createCell(0).setCellValue(-7500d);
    row.createCell(1).setCellValue(3000d);
    row.createCell(2).setCellValue(5000d);
    row.createCell(3).setCellValue(1200d);
    row.createCell(4).setCellValue(4000d);
    row.createCell(5).setCellValue(0.05d);
    row.createCell(6).setCellValue(0.08d);
    HSSFCell cell = row.createCell(7);
    cell.setCellFormula("MIRR(A1:E1, F1, G1)");
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    fe.clearAllCachedResultValues();
    fe.evaluateFormulaCellEnum(cell);
    double res = cell.getNumericCellValue();
    assertEquals(0.18736225093, res, 0.00000001);
}

Example 55 with HSSFSheet

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

the class TestValueRecordsAggregate method testSpuriousSharedFormulaFlag.

/**
	 * Sometimes the 'shared formula' flag (<tt>FormulaRecord.isSharedFormula()</tt>) is set when
	 * there is no corresponding SharedFormulaRecord available. SharedFormulaRecord definitions do
	 * not span multiple sheets.  They are are only defined within a sheet, and thus they do not
	 * have a sheet index field (only row and column range fields).<br/>
	 * So it is important that the code which locates the SharedFormulaRecord for each
	 * FormulaRecord does not allow matches across sheets.</br>
	 *
	 * Prior to bugzilla 44449 (Feb 2008), POI <tt>ValueRecordsAggregate.construct(int, List)</tt>
	 * allowed <tt>SharedFormulaRecord</tt>s to be erroneously used across sheets.  That incorrect
	 * behaviour is shown by this test.<p/>
	 *
	 * <b>Notes on how to produce the test spreadsheet</b>:</p>
	 * The setup for this test (AbnormalSharedFormulaFlag.xls) is rather fragile, insomuchas
	 * re-saving the file (either with Excel or POI) clears the flag.<br/>
	 * <ol>
	 * <li>A new spreadsheet was created in Excel (File | New | Blank Workbook).</li>
	 * <li>Sheet3 was deleted.</li>
	 * <li>Sheet2!A1 formula was set to '="second formula"', and fill-dragged through A1:A8.</li>
	 * <li>Sheet1!A1 formula was set to '="first formula"', and also fill-dragged through A1:A8.</li>
	 * <li>Four rows on Sheet1 "5" through "8" were deleted ('delete rows' alt-E D, not 'clear' Del).</li>
	 * <li>The spreadsheet was saved as AbnormalSharedFormulaFlag.xls.</li>
	 * </ol>
	 * Prior to the row delete action the spreadsheet has two <tt>SharedFormulaRecord</tt>s. One
	 * for each sheet. To expose the bug, the shared formulas have been made to overlap.<br/>
	 * The row delete action (as described here) seems to to delete the
	 * <tt>SharedFormulaRecord</tt> from Sheet1 (but not clear the 'shared formula' flags.<br/>
	 * There are other variations on this theme to create the same effect.
	 *
	 */
@Test
public void testSpuriousSharedFormulaFlag() throws Exception {
    long actualCRC = getFileCRC(HSSFTestDataSamples.openSampleFileStream(ABNORMAL_SHARED_FORMULA_FLAG_TEST_FILE));
    long expectedCRC = 2277445406L;
    if (actualCRC != expectedCRC) {
        System.err.println("Expected crc " + expectedCRC + " but got " + actualCRC);
        throw failUnexpectedTestFileChange();
    }
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook(ABNORMAL_SHARED_FORMULA_FLAG_TEST_FILE);
    // Sheet1
    HSSFSheet s = wb.getSheetAt(0);
    String cellFormula;
    // row "1"
    cellFormula = getFormulaFromFirstCell(s, 0);
    // the problem is not observable in the first row of the shared formula
    assertEquals("Something else wrong with this test case", "\"first formula\"", cellFormula);
    // but the problem is observable in rows 2,3,4
    // row "2"
    cellFormula = getFormulaFromFirstCell(s, 1);
    assertNotEquals("found bug 44449 (Wrong SharedFormulaRecord was used).", "\"second formula\"", cellFormula);
    assertEquals("Something else wrong with this test case", "\"first formula\"", cellFormula);
    wb.close();
}