use of org.apache.poi.hssf.usermodel.HSSFCell in project poi by apache.
the class BaseTestFunctionsFromSpreadsheet method getCellTextValue.
/**
* @return <code>null</code> if cell is missing, empty or blank
*/
private static String getCellTextValue(HSSFRow r, int colIndex, String columnName) {
if (r == null) {
return null;
}
HSSFCell cell = r.getCell(colIndex);
if (cell == null) {
return null;
}
if (cell.getCellTypeEnum() == CellType.BLANK) {
return null;
}
if (cell.getCellTypeEnum() == CellType.STRING) {
return cell.getRichStringCellValue().getString();
}
fail("Bad cell type for '" + columnName + "' column: (" + cell.getCellTypeEnum() + ") row (" + (r.getRowNum() + 1) + ")");
return "";
}
use of org.apache.poi.hssf.usermodel.HSSFCell in project poi by apache.
the class TestYearFracCalculatorFromSpreadsheet method testAll.
@Test
public void testAll() throws Exception {
HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("yearfracExamples.xls");
HSSFSheet sheet = wb.getSheetAt(0);
HSSFFormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator(wb);
int nSuccess = 0;
Iterator<Row> rowIterator = sheet.rowIterator();
while (rowIterator.hasNext()) {
HSSFRow row = (HSSFRow) rowIterator.next();
HSSFCell cell = row.getCell(SS.YEARFRAC_FORMULA_COLUMN);
if (cell == null || cell.getCellTypeEnum() != CellType.FORMULA) {
continue;
}
processRow(row, cell, formulaEvaluator);
nSuccess++;
}
assertTrue("No test sample cases found", nSuccess > 0);
wb.close();
}
use of org.apache.poi.hssf.usermodel.HSSFCell in project poi by apache.
the class TestCountFuncs method testCountifExamples.
/**
* Two COUNTIF examples taken from
* http://office.microsoft.com/en-us/excel-help/countif-function-HP010069840.aspx?CTT=5&origin=HA010277524
*/
public void testCountifExamples() {
HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("countifExamples.xls");
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
HSSFSheet sheet1 = wb.getSheet("MSDN Example 1");
for (int rowIx = 7; rowIx <= 12; rowIx++) {
HSSFRow row = sheet1.getRow(rowIx - 1);
// cell containing a formula with COUNTIF
HSSFCell cellA = row.getCell(0);
assertEquals(CellType.FORMULA, cellA.getCellTypeEnum());
// cell with a reference value
HSSFCell cellC = row.getCell(2);
assertEquals(CellType.NUMERIC, cellC.getCellTypeEnum());
CellValue cv = fe.evaluate(cellA);
double actualValue = cv.getNumberValue();
double expectedValue = cellC.getNumericCellValue();
assertEquals("Problem with a formula at " + new CellReference(cellA).formatAsString() + ": " + cellA.getCellFormula() + " :" + "Expected = (" + expectedValue + ") Actual=(" + actualValue + ") ", expectedValue, actualValue, 0.0001);
}
HSSFSheet sheet2 = wb.getSheet("MSDN Example 2");
for (int rowIx = 9; rowIx <= 14; rowIx++) {
HSSFRow row = sheet2.getRow(rowIx - 1);
// cell containing a formula with COUNTIF
HSSFCell cellA = row.getCell(0);
assertEquals(CellType.FORMULA, cellA.getCellTypeEnum());
// cell with a reference value
HSSFCell cellC = row.getCell(2);
assertEquals(CellType.NUMERIC, cellC.getCellTypeEnum());
CellValue cv = fe.evaluate(cellA);
double actualValue = cv.getNumberValue();
double expectedValue = cellC.getNumericCellValue();
assertEquals("Problem with a formula at " + new CellReference(cellA).formatAsString() + "[" + cellA.getCellFormula() + "]: " + "Expected = (" + expectedValue + ") Actual=(" + actualValue + ") ", expectedValue, actualValue, 0.0001);
}
}
use of org.apache.poi.hssf.usermodel.HSSFCell in project poi by apache.
the class TestCountFuncs method testCountifBug51498.
/**
* Bug #51498 - Check that CountIf behaves correctly for GTE, LTE
* and NEQ cases
*/
public void testCountifBug51498() throws Exception {
final int REF_COL = 4;
final int EVAL_COL = 3;
HSSFWorkbook workbook = HSSFTestDataSamples.openSampleWorkbook("51498.xls");
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
HSSFSheet sheet = workbook.getSheetAt(0);
// numeric criteria
for (int i = 0; i < 8; i++) {
CellValue expected = evaluator.evaluate(sheet.getRow(i).getCell(REF_COL));
CellValue actual = evaluator.evaluate(sheet.getRow(i).getCell(EVAL_COL));
assertEquals(expected.formatAsString(), actual.formatAsString());
}
// boolean criteria
for (int i = 0; i < 8; i++) {
HSSFCell cellFmla = sheet.getRow(i).getCell(8);
HSSFCell cellRef = sheet.getRow(i).getCell(9);
double expectedValue = cellRef.getNumericCellValue();
double actualValue = evaluator.evaluate(cellFmla).getNumberValue();
assertEquals("Problem with a formula at " + new CellReference(cellFmla).formatAsString() + "[" + cellFmla.getCellFormula() + "] ", expectedValue, actualValue, 0.0001);
}
// string criteria
for (int i = 1; i < 9; i++) {
HSSFCell cellFmla = sheet.getRow(i).getCell(13);
HSSFCell cellRef = sheet.getRow(i).getCell(14);
double expectedValue = cellRef.getNumericCellValue();
double actualValue = evaluator.evaluate(cellFmla).getNumberValue();
assertEquals("Problem with a formula at " + new CellReference(cellFmla).formatAsString() + "[" + cellFmla.getCellFormula() + "] ", expectedValue, actualValue, 0.0001);
}
}
use of org.apache.poi.hssf.usermodel.HSSFCell in project poi by apache.
the class TestCountFuncs method testCountFunctionFromSpreadsheet.
private static void testCountFunctionFromSpreadsheet(String FILE_NAME, int START_ROW_IX, int COL_IX_ACTUAL, int COL_IX_EXPECTED, String functionName) {
int failureCount = 0;
HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook(FILE_NAME);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
int maxRow = sheet.getLastRowNum();
for (int rowIx = START_ROW_IX; rowIx < maxRow; rowIx++) {
HSSFRow row = sheet.getRow(rowIx);
if (row == null) {
continue;
}
HSSFCell cell = row.getCell(COL_IX_ACTUAL);
CellValue cv = fe.evaluate(cell);
double actualValue = cv.getNumberValue();
double expectedValue = row.getCell(COL_IX_EXPECTED).getNumericCellValue();
if (actualValue != expectedValue) {
System.err.println("Problem with test case on row " + (rowIx + 1) + " " + "Expected = (" + expectedValue + ") Actual=(" + actualValue + ") ");
failureCount++;
}
}
if (failureCount > 0) {
throw new AssertionFailedError(failureCount + " " + functionName + " evaluations failed. See stderr for more details");
}
}
Aggregations