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);
}
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());
}
}
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());
}
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);
}
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();
}
Aggregations