public void testFullColumnRefs() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("Sheet1");
HSSFRow row = sheet.createRow(0);
HSSFCell cell0 = row.createCell(0);
cell0.setCellFormula("sum(D:D)");
HSSFCell cell1 = row.createCell(1);
cell1.setCellFormula("sum(D:E)");
// some values in column D
setValue(sheet, 1, 3, 5.0);
setValue(sheet, 2, 3, 6.0);
setValue(sheet, 5, 3, 7.0);
setValue(sheet, 50, 3, 8.0);
// some values in column E
setValue(sheet, 1, 4, 9.0);
setValue(sheet, 2, 4, 10.0);
setValue(sheet, 30000, 4, 11.0);
// some other values
setValue(sheet, 1, 2, 100.0);
setValue(sheet, 2, 5, 100.0);
setValue(sheet, 3, 6, 100.0);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
assertEquals(26.0, fe.evaluate(cell0).getNumberValue(), 0.0);
assertEquals(56.0, fe.evaluate(cell1).getNumberValue(), 0.0);
}
private static void floatTest(String operator) {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet();
HSSFRow r = null;
HSSFCell c = null;
// get our minimum values
r = s.createRow(0);
c = r.createCell(1);
c.setCellFormula("" + Float.MIN_VALUE + operator + Float.MIN_VALUE);
for (int x = 1; x < Short.MAX_VALUE && x > 0; x = (short) (x * 2)) {
r = s.createRow(x);
for (int y = 1; y < 256 && y > 0; y = (short) (y + 2)) {
c = r.createCell(y);
c.setCellFormula("" + x + "." + y + operator + y + "." + x);
}
}
if (s.getLastRowNum() < Short.MAX_VALUE) {
r = s.createRow(0);
c = r.createCell(0);
c.setCellFormula("" + Float.MAX_VALUE + operator + Float.MAX_VALUE);
}
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
floatVerify(operator, wb);
}
/**
* All multi-binomial operator tests use this to create a worksheet with a huge set of x operator
* y formulas. Next we call binomialVerify and verify that they are all how we expect.
*/
private static void binomialOperator(String operator) {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet();
HSSFRow r = null;
HSSFCell c = null;
// get our minimum values
r = s.createRow(0);
c = r.createCell(1);
c.setCellFormula(1 + operator + 1);
for (int x = 1; x < Short.MAX_VALUE && x > 0; x = (short) (x * 2)) {
r = s.createRow(x);
for (int y = 1; y < 256 && y > 0; y++) {
c = r.createCell(y);
c.setCellFormula("" + x + operator + y);
}
}
// make sure we do the maximum value of the Int operator
if (s.getLastRowNum() < Short.MAX_VALUE) {
r = s.getRow(0);
c = r.createCell(0);
c.setCellFormula("" + Short.MAX_VALUE + operator + Short.MAX_VALUE);
}
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
binomialVerify(operator, wb);
}
public void testSheetFunctions() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet("A");
HSSFRow r = null;
HSSFCell c = null;
r = s.createRow(0);
c = r.createCell(0);
c.setCellValue(1);
c = r.createCell(1);
c.setCellValue(2);
s = wb.createSheet("B");
r = s.createRow(0);
c = r.createCell(0);
c.setCellFormula("AVERAGE(A!A1:B1)");
c = r.createCell(1);
c.setCellFormula("A!A1+A!B1");
c = r.createCell(2);
c.setCellFormula("A!$A$1+A!$B1");
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
s = wb.getSheet("B");
r = s.getRow(0);
c = r.getCell(0);
assertTrue(
"expected: AVERAGE(A!A1:B1) got: " + c.getCellFormula(),
("AVERAGE(A!A1:B1)").equals(c.getCellFormula()));
c = r.getCell(1);
assertTrue(
"expected: A!A1+A!B1 got: " + c.getCellFormula(), ("A!A1+A!B1").equals(c.getCellFormula()));
}
/** Writes a function then tests to see if its correct */
public void refAreaArrayFunctionTest(String function) {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet();
HSSFRow r = null;
HSSFCell c = null;
r = s.createRow(0);
c = r.createCell(0);
c.setCellFormula(function + "(A2:A4,B2:B4)");
c = r.createCell(1);
c.setCellFormula(function + "($A$2:$A4,B$2:B4)");
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
s = wb.getSheetAt(0);
r = s.getRow(0);
c = r.getCell(0);
assertTrue(
"function =" + function + "(A2:A4,B2:B4)",
((function + "(A2:A4,B2:B4)").equals(c.getCellFormula())));
c = r.getCell(1);
assertTrue(
"function =" + function + "($A$2:$A4,B$2:B4)",
((function + "($A$2:$A4,B$2:B4)").equals(c.getCellFormula())));
}
public void testAbsRefs() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet();
HSSFRow r;
HSSFCell c;
r = s.createRow(0);
c = r.createCell(0);
c.setCellFormula("A3+A2");
c = r.createCell(1);
c.setCellFormula("$A3+$A2");
c = r.createCell(2);
c.setCellFormula("A$3+A$2");
c = r.createCell(3);
c.setCellFormula("$A$3+$A$2");
c = r.createCell(4);
c.setCellFormula("SUM($A$3,$A$2)");
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
s = wb.getSheetAt(0);
r = s.getRow(0);
c = r.getCell(0);
assertTrue("A3+A2", ("A3+A2").equals(c.getCellFormula()));
c = r.getCell(1);
assertTrue("$A3+$A2", ("$A3+$A2").equals(c.getCellFormula()));
c = r.getCell(2);
assertTrue("A$3+A$2", ("A$3+A$2").equals(c.getCellFormula()));
c = r.getCell(3);
assertTrue("$A$3+$A$2", ("$A$3+$A$2").equals(c.getCellFormula()));
c = r.getCell(4);
assertTrue("SUM($A$3,$A$2)", ("SUM($A$3,$A$2)").equals(c.getCellFormula()));
}
private static void operationRefTest(String operator) {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet();
HSSFRow r = null;
HSSFCell c = null;
// get our minimum values
r = s.createRow(0);
c = r.createCell(1);
c.setCellFormula("A2" + operator + "A3");
for (int x = 1; x < Short.MAX_VALUE && x > 0; x = (short) (x * 2)) {
r = s.createRow(x);
for (int y = 1; y < 256 && y > 0; y++) {
String ref = null;
String ref2 = null;
short refx1 = 0;
short refy1 = 0;
short refx2 = 0;
short refy2 = 0;
if (x + 50 < Short.MAX_VALUE) {
refx1 = (short) (x + 50);
refx2 = (short) (x + 46);
} else {
refx1 = (short) (x - 4);
refx2 = (short) (x - 3);
}
if (y + 50 < 255) {
refy1 = (short) (y + 50);
refy2 = (short) (y + 49);
} else {
refy1 = (short) (y - 4);
refy2 = (short) (y - 3);
}
c = r.getCell(y);
CellReference cr = new CellReference(refx1, refy1, false, false);
ref = cr.formatAsString();
cr = new CellReference(refx2, refy2, false, false);
ref2 = cr.formatAsString();
c = r.createCell(y);
c.setCellFormula("" + ref + operator + ref2);
}
}
// make sure we do the maximum value of the Int operator
if (s.getLastRowNum() < Short.MAX_VALUE) {
r = s.getRow(0);
c = r.createCell(0);
c.setCellFormula("" + "B1" + operator + "IV255");
}
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
operationalRefVerify(operator, wb);
}
public void testDateFormulas() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet("testSheet1");
HSSFRow r = null;
HSSFCell c = null;
r = s.createRow(0);
c = r.createCell(0);
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
c.setCellValue(new Date());
c.setCellStyle(cellStyle);
// assertEquals("Checking hour = " + hour, date.getTime().getTime(),
// HSSFDateUtil.getJavaDate(excelDate).getTime());
for (int k = 1; k < 100; k++) {
r = s.createRow(k);
c = r.createCell(0);
c.setCellFormula("A" + (k) + "+1");
c.setCellStyle(cellStyle);
}
HSSFTestDataSamples.writeOutAndReadBack(wb);
}
public void testStringFormulas() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet("A");
HSSFRow r = null;
HSSFCell c = null;
r = s.createRow(0);
c = r.createCell(1);
c.setCellFormula("UPPER(\"abc\")");
c = r.createCell(2);
c.setCellFormula("LOWER(\"ABC\")");
c = r.createCell(3);
c.setCellFormula("CONCATENATE(\" my \",\" name \")");
HSSFTestDataSamples.writeOutAndReadBack(wb);
wb = openSample("StringFormulas.xls");
s = wb.getSheetAt(0);
r = s.getRow(0);
c = r.getCell(0);
assertEquals("UPPER(\"xyz\")", c.getCellFormula());
}
public void testSumIf() {
String function = "SUMIF(A1:A5,\">4000\",B1:B5)";
HSSFWorkbook wb = openSample("sumifformula.xls");
HSSFSheet s = wb.getSheetAt(0);
HSSFRow r = s.getRow(0);
HSSFCell c = r.getCell(2);
assertEquals(function, c.getCellFormula());
wb = new HSSFWorkbook();
s = wb.createSheet();
r = s.createRow(0);
c = r.createCell(0);
c.setCellValue(1000);
c = r.createCell(1);
c.setCellValue(1);
r = s.createRow(1);
c = r.createCell(0);
c.setCellValue(2000);
c = r.createCell(1);
c.setCellValue(2);
r = s.createRow(2);
c = r.createCell(0);
c.setCellValue(3000);
c = r.createCell(1);
c.setCellValue(3);
r = s.createRow(3);
c = r.createCell(0);
c.setCellValue(4000);
c = r.createCell(1);
c.setCellValue(4);
r = s.createRow(4);
c = r.createCell(0);
c.setCellValue(5000);
c = r.createCell(1);
c.setCellValue(5);
r = s.getRow(0);
c = r.createCell(2);
c.setCellFormula(function);
HSSFTestDataSamples.writeOutAndReadBack(wb);
}
public void testLogicalFormulas() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet("A");
HSSFRow r = null;
HSSFCell c = null;
r = s.createRow(0);
c = r.createCell(1);
c.setCellFormula("IF(A1<A2,B1,B2)");
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
s = wb.getSheetAt(0);
r = s.getRow(0);
c = r.getCell(1);
assertEquals("Formula in cell 1 ", "IF(A1<A2,B1,B2)", c.getCellFormula());
}
/** Add 1+1 -- WHoohoo! */
public void testBasicAddIntegers() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet();
HSSFRow r = null;
HSSFCell c = null;
// get our minimum values
r = s.createRow(1);
c = r.createCell(1);
c.setCellFormula(1 + "+" + 1);
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
s = wb.getSheetAt(0);
r = s.getRow(1);
c = r.getCell(1);
assertTrue("Formula is as expected", ("1+1".equals(c.getCellFormula())));
}
/** tests order wrting out == order writing in for a given formula */
private static void orderTest(String formula) {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet();
HSSFRow r = null;
HSSFCell c = null;
// get our minimum values
r = s.createRow(0);
c = r.createCell(1);
c.setCellFormula(formula);
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
s = wb.getSheetAt(0);
// get our minimum values
r = s.getRow(0);
c = r.getCell(1);
assertTrue("minval Formula is as expected", formula.equals(c.getCellFormula()));
}
public void testRVAoperands() throws Exception {
File file = TempFile.createTempFile("testFormulaRVA", ".xls");
FileOutputStream out = new FileOutputStream(file);
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet();
HSSFRow r = null;
HSSFCell c = null;
r = s.createRow(0);
c = r.createCell(0);
c.setCellFormula("A3+A2");
c = r.createCell(1);
c.setCellFormula("AVERAGE(A3,A2)");
c = r.createCell(2);
c.setCellFormula("ROW(A3)");
c = r.createCell(3);
c.setCellFormula("AVERAGE(A2:A3)");
c = r.createCell(4);
c.setCellFormula("POWER(A2,A3)");
c = r.createCell(5);
c.setCellFormula("SIN(A2)");
c = r.createCell(6);
c.setCellFormula("SUM(A2:A3)");
c = r.createCell(7);
c.setCellFormula("SUM(A2,A3)");
r = s.createRow(1);
c = r.createCell(0);
c.setCellValue(2.0);
r = s.createRow(2);
c = r.createCell(0);
c.setCellValue(3.0);
wb.write(out);
out.close();
assertTrue("file exists", file.exists());
}
/** Test for bug due to attempt to convert a cached formula error result to a boolean */
public void testUpdateCachedFormulaResultFromErrorToNumber_bug46479() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("Sheet1");
HSSFRow row = sheet.createRow(0);
HSSFCell cellA1 = row.createCell(0);
HSSFCell cellB1 = row.createCell(1);
cellB1.setCellFormula("A1+1");
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
cellA1.setCellErrorValue((byte) HSSFErrorConstants.ERROR_NAME);
fe.evaluateFormulaCell(cellB1);
cellA1.setCellValue(2.5);
fe.notifyUpdateCell(cellA1);
try {
fe.evaluateInCell(cellB1);
} catch (IllegalStateException e) {
if (e.getMessage().equals("Cannot get a numeric value from a error formula cell")) {
throw new AssertionFailedError("Identified bug 46479a");
}
}
assertEquals(3.5, cellB1.getNumericCellValue(), 0.0);
}
/**
* When evaluating defined names, POI has to decide whether it is capable. Currently (May2009) POI
* only supports simple cell and area refs.<br>
* The sample spreadsheet (bugzilla attachment 23508) had a name flagged as 'complex' which
* contained a simple area ref. It is not clear what the 'complex' flag is used for but POI should
* look elsewhere to decide whether it can evaluate the name.
*/
public void testDefinedNameWithComplexFlag_bug47048() {
// Mock up a spreadsheet to match the critical details of the sample
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("Input");
HSSFName definedName = wb.createName();
definedName.setNameName("Is_Multicar_Vehicle");
definedName.setRefersToFormula("Input!$B$17:$G$17");
// Set up some data and the formula
HSSFRow row17 = sheet.createRow(16);
row17.createCell(0).setCellValue(25.0);
row17.createCell(1).setCellValue(1.33);
row17.createCell(2).setCellValue(4.0);
HSSFRow row = sheet.createRow(0);
HSSFCell cellA1 = row.createCell(0);
cellA1.setCellFormula("SUM(Is_Multicar_Vehicle)");
// Set the complex flag - POI doesn't usually manipulate this flag
NameRecord nameRec = TestHSSFName.getNameRecord(definedName);
nameRec.setOptionFlag((short) 0x10); // 0x10 -> complex
HSSFFormulaEvaluator hsf = new HSSFFormulaEvaluator(wb);
CellValue value;
try {
value = hsf.evaluate(cellA1);
} catch (RuntimeException e) {
if (e.getMessage().equals("Don't now how to evalate name 'Is_Multicar_Vehicle'")) {
throw new AssertionFailedError("Identified bug 47048a");
}
throw e;
}
assertEquals(Cell.CELL_TYPE_NUMERIC, value.getCellType());
assertEquals(5.33, value.getNumberValue(), 0.0);
}
/** MissingArgPtg */
public void testMissingArgPtg() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFCell cell = wb.createSheet("Sheet1").createRow(4).createCell(0);
cell.setCellFormula("IF(A1=\"A\",1,)");
}
public void testIfFormulas() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet("testSheet1");
HSSFRow r = null;
HSSFCell c = null;
r = s.createRow(0);
c = r.createCell(1);
c.setCellValue(1);
c = r.createCell(2);
c.setCellValue(2);
c = r.createCell(3);
c.setCellFormula("MAX(A1:B1)");
c = r.createCell(4);
c.setCellFormula("IF(A1=D1,\"A1\",\"B1\")");
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
s = wb.getSheetAt(0);
r = s.getRow(0);
c = r.getCell(4);
assertTrue(
"expected: IF(A1=D1,\"A1\",\"B1\") got " + c.getCellFormula(),
("IF(A1=D1,\"A1\",\"B1\")").equals(c.getCellFormula()));
wb = openSample("IfFormulaTest.xls");
s = wb.getSheetAt(0);
r = s.getRow(3);
c = r.getCell(0);
assertTrue(
"expected: IF(A3=A1,\"A1\",\"A2\") got " + c.getCellFormula(),
("IF(A3=A1,\"A1\",\"A2\")").equals(c.getCellFormula()));
// c = r.getCell((short)1);
// assertTrue("expected: A!A1+A!B1 got: "+c.getCellFormula(),
// ("A!A1+A!B1").equals(c.getCellFormula()));
wb = new HSSFWorkbook();
s = wb.createSheet("testSheet1");
r = null;
c = null;
r = s.createRow(0);
c = r.createCell(0);
c.setCellFormula("IF(1=1,0,1)");
HSSFTestDataSamples.writeOutAndReadBack(wb);
wb = new HSSFWorkbook();
s = wb.createSheet("testSheet1");
r = null;
c = null;
r = s.createRow(0);
c = r.createCell(0);
c.setCellValue(1);
c = r.createCell(1);
c.setCellValue(3);
HSSFCell formulaCell = r.createCell(3);
r = s.createRow(1);
c = r.createCell(0);
c.setCellValue(3);
c = r.createCell(1);
c.setCellValue(7);
formulaCell.setCellFormula("IF(A1=B1,AVERAGE(A1:B1),AVERAGE(A2:B2))");
HSSFTestDataSamples.writeOutAndReadBack(wb);
}
