diff options
Diffstat (limited to 'src/testcases/org/apache/poi/hssf/usermodel/TestFormulas.java')
| -rw-r--r-- | src/testcases/org/apache/poi/hssf/usermodel/TestFormulas.java | 1055 |
1 files changed, 489 insertions, 566 deletions
diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestFormulas.java b/src/testcases/org/apache/poi/hssf/usermodel/TestFormulas.java index 1eea909b3c..d2aeaf3803 100644 --- a/src/testcases/org/apache/poi/hssf/usermodel/TestFormulas.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestFormulas.java @@ -17,6 +17,7 @@ package org.apache.poi.hssf.usermodel; +import static org.apache.poi.hssf.HSSFTestDataSamples.writeOutAndReadBack; import static org.junit.Assert.assertEquals; import static org.junit.Assert.assertNotNull; import static org.junit.Assert.assertSame; @@ -25,17 +26,19 @@ import static org.junit.Assert.assertTrue; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; -import java.util.Date; +import java.util.Calendar; import org.apache.poi.hssf.HSSFTestDataSamples; import org.apache.poi.hssf.model.HSSFFormulaParser; -import org.apache.poi.ss.util.CellReference; import org.apache.poi.ss.formula.FormulaType; import org.apache.poi.ss.formula.ptg.NamePtg; import org.apache.poi.ss.formula.ptg.Ptg; import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.Name; import org.apache.poi.ss.usermodel.Sheet; +import org.apache.poi.ss.util.CellReference; +import org.apache.poi.util.LocaleUtil; import org.apache.poi.util.TempFile; import org.junit.Test; @@ -50,25 +53,22 @@ public final class TestFormulas { */ @Test public void testBasicAddIntegers() throws IOException { + try (HSSFWorkbook wb1 = new HSSFWorkbook()) { + HSSFSheet s = wb1.createSheet(); - HSSFWorkbook wb1 = new HSSFWorkbook(); - HSSFSheet s = wb1.createSheet(); - HSSFRow r; - HSSFCell c; + //get our minimum values + HSSFRow r = s.createRow(1); + HSSFCell c = r.createCell(1); + c.setCellFormula(1 + "+" + 1); - //get our minimum values - r = s.createRow(1); - c = r.createCell(1); - c.setCellFormula(1 + "+" + 1); + try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { + s = wb2.getSheetAt(0); + r = s.getRow(1); + c = r.getCell(1); - HSSFWorkbook wb2 = HSSFTestDataSamples.writeOutAndReadBack(wb1); - wb1.close(); - s = wb2.getSheetAt(0); - r = s.getRow(1); - c = r.getCell(1); - - assertEquals("Formula is as expected", "1+1", c.getCellFormula()); - wb2.close(); + assertEquals("Formula is as expected", "1+1", c.getCellFormula()); + } + } } /** @@ -164,46 +164,37 @@ public final class TestFormulas { } private static void floatTest(String operator) throws IOException { - HSSFWorkbook wb1 = new HSSFWorkbook(); - HSSFSheet s = wb1.createSheet(); - HSSFRow r; - HSSFCell c; - - //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); + try (HSSFWorkbook wb1 = new HSSFWorkbook()) { + HSSFSheet s = wb1.createSheet(); - for (int y = 1; y < 256 && y > 0; y= (short) (y +2)) { - - c = r.createCell(y); - c.setCellFormula("" + x+"."+y + operator + y +"."+x); + //get our minimum values + HSSFRow r = s.createRow(0); + HSSFCell 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); + } + try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { + floatVerify(operator, wb2); } } - if (s.getLastRowNum() < Short.MAX_VALUE) { - r = s.createRow(0); - c = r.createCell(0); - c.setCellFormula("" + Float.MAX_VALUE + operator + Float.MAX_VALUE); - } - HSSFWorkbook wb2 = HSSFTestDataSamples.writeOutAndReadBack(wb1); - wb1.close(); - - floatVerify(operator, wb2); - wb2.close(); } private static void floatVerify(String operator, HSSFWorkbook wb) { - - HSSFSheet s = wb.getSheetAt(0); + HSSFSheet s = wb.getSheetAt(0); // don't know how to check correct result .. for the moment, we just verify that the file can be read. - for (int x = 1; x < Short.MAX_VALUE && x > 0; x=(short)(x*2)) { HSSFRow r = s.getRow(x); @@ -238,68 +229,44 @@ public final class TestFormulas { } private static void operationRefTest(String operator) throws IOException { - HSSFWorkbook wb1 = new HSSFWorkbook(); - HSSFSheet s = wb1.createSheet(); - HSSFRow r; - HSSFCell c; - - //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; - String ref2; - short refx1; - short refy1; - short refx2; - short refy2; - if (x +50 < Short.MAX_VALUE) { - refx1=(short)(x+50); - refx2=(short)(x+46); - } else { - refx1=(short)(x-4); - refx2=(short)(x-3); + try (HSSFWorkbook wb1 = new HSSFWorkbook()) { + HSSFSheet s = wb1.createSheet(); + + //get our minimum values + HSSFRow r = s.createRow(0); + HSSFCell 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++) { + short refx1 = (short)(x + (x + 50 < Short.MAX_VALUE ? 50 : -4)); + short refx2 = (short)(x + (x + 50 < Short.MAX_VALUE ? 46 : -3)); + short refy1 = (short)(y + (y + 50 < 255 ? 50 : -4)); + short refy2 = (short)(y + (y + 50 < 255 ? 49 : -3)); + + CellReference cr = new CellReference(refx1, refy1, false, false); + String ref = cr.formatAsString(); + cr = new CellReference(refx2, refy2, false, false); + String ref2 = cr.formatAsString(); + + c = r.createCell(y); + c.setCellFormula("" + ref + operator + ref2); } + } - 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"); } - } - //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"); + try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { + operationalRefVerify(operator, wb2); + } } - - HSSFWorkbook wb2 = HSSFTestDataSamples.writeOutAndReadBack(wb1); - wb1.close(); - operationalRefVerify(operator, wb2); - wb2.close(); } /** @@ -308,13 +275,11 @@ public final class TestFormulas { */ private static void operationalRefVerify(String operator, HSSFWorkbook wb) { - HSSFSheet s = wb.getSheetAt(0); - HSSFRow r; - HSSFCell c; + HSSFSheet s = wb.getSheetAt(0); //get our minimum values - r = s.getRow(0); - c = r.getCell(1); + HSSFRow r = s.getRow(0); + HSSFCell c = r.getCell(1); //get our minimum values assertEquals("minval Formula is as expected A2" + operator + "A3 != " + c.getCellFormula(), ("A2" + operator + "A3"), c.getCellFormula()); @@ -324,30 +289,14 @@ public final class TestFormulas { for (int y = 1; y < 256 && y > 0; y++) { - int refx1; - int refy1; - int refx2; - int refy2; - if (x +50 < Short.MAX_VALUE) { - refx1=x+50; - refx2=x+46; - } else { - refx1=x-4; - refx2=x-3; - } - - if (y+50 < 255) { - refy1=y+50; - refy2=y+49; - } else { - refy1=y-4; - refy2=y-3; - } + int refx1 = x + (x+50 < Short.MAX_VALUE ? 50 : -4); + int refx2 = x + (x+50 < Short.MAX_VALUE ? 46 : -3); + int refy1 = y + (y+50 < 255 ? 50 : -4); + int refy2 = y + (y+50 < 255 ? 49 : -3); c = r.getCell(y); CellReference cr= new CellReference(refx1, refy1, false, false); String ref=cr.formatAsString(); - ref=cr.formatAsString(); cr=new CellReference(refx2,refy2, false, false); String ref2=cr.formatAsString(); @@ -369,25 +318,23 @@ public final class TestFormulas { * tests order wrting out == order writing in for a given formula */ private static void orderTest(String formula) throws IOException { - HSSFWorkbook wb1 = new HSSFWorkbook(); - HSSFSheet s = wb1.createSheet(); - HSSFRow r; - HSSFCell c; + try (HSSFWorkbook wb1 = new HSSFWorkbook()) { + HSSFSheet s = wb1.createSheet(); - //get our minimum values - r = s.createRow(0); - c = r.createCell(1); - c.setCellFormula(formula); + //get our minimum values + HSSFRow r = s.createRow(0); + HSSFCell c = r.createCell(1); + c.setCellFormula(formula); - HSSFWorkbook wb2 = HSSFTestDataSamples.writeOutAndReadBack(wb1); - wb1.close(); - s = wb2.getSheetAt(0); + try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { + s = wb2.getSheetAt(0); - //get our minimum values - r = s.getRow(0); - c = r.getCell(1); - assertEquals("minval Formula is as expected", formula, c.getCellFormula()); - wb2.close(); + //get our minimum values + r = s.getRow(0); + c = r.getCell(1); + assertEquals("minval Formula is as expected", formula, c.getCellFormula()); + } + } } /** @@ -396,37 +343,33 @@ public final class TestFormulas { * that they are all how we expect. */ private static void binomialOperator(String operator) throws IOException { - HSSFWorkbook wb1 = new HSSFWorkbook(); - HSSFSheet s = wb1.createSheet(); - HSSFRow r; - HSSFCell c; - - //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); + try (HSSFWorkbook wb1 = new HSSFWorkbook()) { + HSSFSheet s = wb1.createSheet(); - for (int y = 1; y < 256 && y > 0; y++) { + //get our minimum values + HSSFRow r = s.createRow(0); + HSSFCell c = r.createCell(1); + c.setCellFormula(1 + operator + 1); - c = r.createCell(y); - c.setCellFormula("" + x + operator + y); + 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); + //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); + } + try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { + binomialVerify(operator, wb2); + } } - HSSFWorkbook wb2 = HSSFTestDataSamples.writeOutAndReadBack(wb1); - wb1.close(); - binomialVerify(operator, wb2); - wb2.close(); } /** @@ -434,22 +377,18 @@ public final class TestFormulas { * all match what we expect (x operator y) */ private static void binomialVerify(String operator, HSSFWorkbook wb) { - HSSFSheet s = wb.getSheetAt(0); - HSSFRow r; - HSSFCell c; + HSSFSheet s = wb.getSheetAt(0); //get our minimum values - r = s.getRow(0); - c = r.getCell(1); + HSSFRow r = s.getRow(0); + HSSFCell c = r.getCell(1); assertEquals("minval Formula is as expected 1" + operator + "1 != " + c.getCellFormula(), ("1" + operator + "1"), c.getCellFormula()); for (int x = 1; x < Short.MAX_VALUE && x > 0; x=(short)(x*2)) { r = s.getRow(x); for (int y = 1; y < 256 && y > 0; y++) { - c = r.getCell(y); - assertEquals("loop Formula is as expected " + x + operator + y + "!=" + c.getCellFormula(), ("" + x + operator + y), c.getCellFormula()); } } @@ -465,52 +404,41 @@ public final class TestFormulas { * Writes a function then tests to see if its correct */ public static void areaFunctionTest(String function) throws IOException { - - HSSFWorkbook wb1 = new HSSFWorkbook(); - HSSFSheet s = wb1.createSheet(); - HSSFRow r; - HSSFCell c; - - - r = s.createRow(0); - - c = r.createCell(0); - c.setCellFormula(function+"(A2:A3)"); - - HSSFWorkbook wb2 = HSSFTestDataSamples.writeOutAndReadBack(wb1); - wb1.close(); - s = wb2.getSheetAt(0); - r = s.getRow(0); - c = r.getCell(0); - - assertEquals("function =" + function + "(A2:A3)", (function + "(A2:A3)"), (function + "(A2:A3)")); - wb2.close(); + try (HSSFWorkbook wb1 = new HSSFWorkbook()) { + HSSFSheet s = wb1.createSheet(); + HSSFRow r = s.createRow(0); + HSSFCell c = r.createCell(0); + c.setCellFormula(function + "(A2:A3)"); + + try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { + s = wb2.getSheetAt(0); + r = s.getRow(0); + c = r.getCell(0); + + assertEquals("function =" + function + "(A2:A3)", function + "(A2:A3)", c.getCellFormula()); + } + } } /** * Writes a function then tests to see if its correct */ public void refArrayFunctionTest(String function) throws IOException { + try (HSSFWorkbook wb1 = new HSSFWorkbook()) { + HSSFSheet s = wb1.createSheet(); + HSSFRow r = s.createRow(0); - HSSFWorkbook wb1 = new HSSFWorkbook(); - HSSFSheet s = wb1.createSheet(); - HSSFRow r; - HSSFCell c; + HSSFCell c = r.createCell(0); + c.setCellFormula(function + "(A2,A3)"); + try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { + s = wb2.getSheetAt(0); + r = s.getRow(0); + c = r.getCell(0); - r = s.createRow(0); - - c = r.createCell(0); - c.setCellFormula(function+"(A2,A3)"); - - HSSFWorkbook wb2 = HSSFTestDataSamples.writeOutAndReadBack(wb1); - wb1.close(); - s = wb2.getSheetAt(0); - r = s.getRow(0); - c = r.getCell(0); - - assertEquals("function =" + function + "(A2,A3)", (function + "(A2,A3)"), c.getCellFormula()); - wb2.close(); + assertEquals("function =" + function + "(A2,A3)", (function + "(A2,A3)"), c.getCellFormula()); + } + } } @@ -520,379 +448,383 @@ public final class TestFormulas { */ public void refAreaArrayFunctionTest(String function) throws IOException { - HSSFWorkbook wb1 = new HSSFWorkbook(); - HSSFSheet s = wb1.createSheet(); - HSSFRow r; - HSSFCell c; - - - r = s.createRow(0); + try (HSSFWorkbook wb1 = new HSSFWorkbook()) { + HSSFSheet s = wb1.createSheet(); + HSSFRow r = s.createRow(0); + HSSFCell c = r.createCell(0); + c.setCellFormula(function + "(A2:A4,B2:B4)"); + c = r.createCell(1); + c.setCellFormula(function + "($A$2:$A4,B$2:B4)"); - c = r.createCell(0); - c.setCellFormula(function+"(A2:A4,B2:B4)"); - c=r.createCell(1); - c.setCellFormula(function+"($A$2:$A4,B$2:B4)"); + try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { + s = wb2.getSheetAt(0); + r = s.getRow(0); + c = r.getCell(0); - HSSFWorkbook wb2 = HSSFTestDataSamples.writeOutAndReadBack(wb1); - wb1.close(); - s = wb2.getSheetAt(0); - r = s.getRow(0); - c = r.getCell(0); - - assertEquals("function =" + function + "(A2:A4,B2:B4)", (function + "(A2:A4,B2:B4)"), c.getCellFormula()); + assertEquals("function =" + function + "(A2:A4,B2:B4)", (function + "(A2:A4,B2:B4)"), c.getCellFormula()); - c=r.getCell(1); - assertEquals("function =" + function + "($A$2:$A4,B$2:B4)", (function + "($A$2:$A4,B$2:B4)"), c.getCellFormula()); - wb2.close(); + c = r.getCell(1); + assertEquals("function =" + function + "($A$2:$A4,B$2:B4)", (function + "($A$2:$A4,B$2:B4)"), c.getCellFormula()); + } + } } @Test public void testAbsRefs() throws IOException { - HSSFWorkbook wb1 = new HSSFWorkbook(); - HSSFSheet s = wb1.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)"); - - HSSFWorkbook wb2 = HSSFTestDataSamples.writeOutAndReadBack(wb1); - wb1.close(); - s = wb2.getSheetAt(0); - r = s.getRow(0); - c = r.getCell(0); - assertEquals("A3+A2", ("A3+A2"), c.getCellFormula()); - c = r.getCell(1); - assertEquals("$A3+$A2", ("$A3+$A2"), c.getCellFormula()); - c = r.getCell(2); - assertEquals("A$3+A$2", ("A$3+A$2"), c.getCellFormula()); - c = r.getCell(3); - assertEquals("$A$3+$A$2", ("$A$3+$A$2"), c.getCellFormula()); - c = r.getCell(4); - assertEquals("SUM($A$3,$A$2)", ("SUM($A$3,$A$2)"), c.getCellFormula()); - wb2.close(); + try (HSSFWorkbook wb1 = new HSSFWorkbook()) { + HSSFSheet s = wb1.createSheet(); + HSSFRow r = s.createRow(0); + HSSFCell 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)"); + + try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { + s = wb2.getSheetAt(0); + r = s.getRow(0); + c = r.getCell(0); + assertEquals("A3+A2", ("A3+A2"), c.getCellFormula()); + c = r.getCell(1); + assertEquals("$A3+$A2", ("$A3+$A2"), c.getCellFormula()); + c = r.getCell(2); + assertEquals("A$3+A$2", ("A$3+A$2"), c.getCellFormula()); + c = r.getCell(3); + assertEquals("$A$3+$A$2", ("$A$3+$A$2"), c.getCellFormula()); + c = r.getCell(4); + assertEquals("SUM($A$3,$A$2)", ("SUM($A$3,$A$2)"), c.getCellFormula()); + } + } } @Test public void testSheetFunctions() throws IOException { - HSSFWorkbook wb1 = new HSSFWorkbook(); - HSSFSheet s = wb1.createSheet("A"); - HSSFRow r; - HSSFCell c; - r = s.createRow(0); - c = r.createCell(0);c.setCellValue(1); - c = r.createCell(1);c.setCellValue(2); - - s = wb1.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"); - - HSSFWorkbook wb2 = HSSFTestDataSamples.writeOutAndReadBack(wb1); - wb1.close(); - - s = wb2.getSheet("B"); - r = s.getRow(0); - c = r.getCell(0); - assertEquals("expected: AVERAGE(A!A1:B1) got: " + c.getCellFormula(), ("AVERAGE(A!A1:B1)"), c.getCellFormula()); - c = r.getCell(1); - assertEquals("expected: A!A1+A!B1 got: " + c.getCellFormula(), ("A!A1+A!B1"), c.getCellFormula()); - wb2.close(); + try (HSSFWorkbook wb1 = new HSSFWorkbook()) { + HSSFSheet s = wb1.createSheet("A"); + HSSFRow r = s.createRow(0); + HSSFCell c = r.createCell(0); + c.setCellValue(1); + c = r.createCell(1); + c.setCellValue(2); + + s = wb1.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"); + + try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { + s = wb2.getSheet("B"); + r = s.getRow(0); + c = r.getCell(0); + assertEquals("expected: AVERAGE(A!A1:B1) got: " + c.getCellFormula(), ("AVERAGE(A!A1:B1)"), c.getCellFormula()); + c = r.getCell(1); + assertEquals("expected: A!A1+A!B1 got: " + c.getCellFormula(), ("A!A1+A!B1"), c.getCellFormula()); + } + } } @Test public void testRVAoperands() throws IOException { File file = TempFile.createTempFile("testFormulaRVA",".xls"); - FileOutputStream out = new FileOutputStream(file); - 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("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); + try (HSSFWorkbook wb = new HSSFWorkbook()) { + HSSFSheet s = wb.createSheet(); + HSSFRow r = s.createRow(0); + HSSFCell 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()); - wb.close(); + try (FileOutputStream out = new FileOutputStream(file)) { + wb.write(out); + } + assertTrue("file exists", file.exists()); + } } @Test public void testStringFormulas() throws IOException { - HSSFWorkbook wb = new HSSFWorkbook(); - HSSFSheet s = wb.createSheet("A"); - HSSFRow r; - HSSFCell c; - 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).close(); - wb.close(); - - wb = openSample("StringFormulas.xls"); - s = wb.getSheetAt(0); - r = s.getRow(0); - c = r.getCell(0); - assertEquals("UPPER(\"xyz\")", c.getCellFormula()); - wb.close(); + try (HSSFWorkbook wb = new HSSFWorkbook()) { + HSSFSheet s = wb.createSheet("A"); + HSSFRow r = s.createRow(0); + HSSFCell 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 \")"); + + writeOutAndReadBack(wb).close(); + } + + try (HSSFWorkbook wb = openSample("StringFormulas.xls")) { + HSSFSheet s = wb.getSheetAt(0); + HSSFRow r = s.getRow(0); + HSSFCell c = r.getCell(0); + assertEquals("UPPER(\"xyz\")", c.getCellFormula()); + } } @Test public void testLogicalFormulas() throws IOException { - - HSSFWorkbook wb1 = new HSSFWorkbook(); - HSSFSheet s = wb1.createSheet("A"); - HSSFRow r; - HSSFCell c; - r = s.createRow(0); - c=r.createCell(1); c.setCellFormula("IF(A1<A2,B1,B2)"); - - HSSFWorkbook wb2 = HSSFTestDataSamples.writeOutAndReadBack(wb1); - wb1.close(); - s = wb2.getSheetAt(0); - r = s.getRow(0); - c = r.getCell(1); - assertEquals("Formula in cell 1 ","IF(A1<A2,B1,B2)",c.getCellFormula()); - wb2.close(); + try (HSSFWorkbook wb1 = new HSSFWorkbook()) { + HSSFSheet s = wb1.createSheet("A"); + HSSFRow r = s.createRow(0); + HSSFCell c = r.createCell(1); + c.setCellFormula("IF(A1<A2,B1,B2)"); + + try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { + s = wb2.getSheetAt(0); + r = s.getRow(0); + c = r.getCell(1); + assertEquals("Formula in cell 1 ", "IF(A1<A2,B1,B2)", c.getCellFormula()); + } + } } @Test public void testDateFormulas() throws IOException { - HSSFWorkbook wb = new HSSFWorkbook(); - HSSFSheet s = wb.createSheet("testSheet1"); - HSSFRow r; - HSSFCell c; - - 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(), - // DateUtil.getJavaDate(excelDate).getTime()); + try (HSSFWorkbook wb = new HSSFWorkbook()) { + HSSFSheet s = wb.createSheet("testSheet1"); + HSSFRow r = s.createRow(0); + HSSFCell c = r.createCell(0); + + Calendar cal = LocaleUtil.getLocaleCalendar(); + + HSSFCellStyle cellStyle = wb.createCellStyle(); + cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); + c.setCellValue(cal.getTime()); + c.setCellStyle(cellStyle); + + int hour = cal.get(Calendar.HOUR_OF_DAY); + double excelDate = c.getNumericCellValue(); + assertEquals(cal.getTime().getTime(), DateUtil.getJavaDate(excelDate).getTime()); + assertEquals(cal.getTime(), c.getDateCellValue()); + + for (int k = 1; k < 100; k++) { + r = s.createRow(k); + c = r.createCell(0); + c.setCellFormula("A" + (k) + "+1"); + c.setCellStyle(cellStyle); + } - for (int k=1; k < 100; k++) { - r=s.createRow(k); - c=r.createCell(0); - c.setCellFormula("A"+(k)+"+1"); - c.setCellStyle(cellStyle); + writeOutAndReadBack(wb).close(); } - - HSSFTestDataSamples.writeOutAndReadBack(wb).close(); - wb.close(); } @Test public void testIfFormulas() throws IOException { - HSSFWorkbook wb1 = new HSSFWorkbook(); - HSSFSheet s = wb1.createSheet("testSheet1"); - HSSFRow r; - HSSFCell c; - 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\")"); - - HSSFWorkbook wb2 = HSSFTestDataSamples.writeOutAndReadBack(wb1); - wb1.close(); - s = wb2.getSheetAt(0); - r = s.getRow(0); - c = r.getCell(4); - - assertEquals("expected: IF(A1=D1,\"A1\",\"B1\") got " + c.getCellFormula(), ("IF(A1=D1,\"A1\",\"B1\")"), c.getCellFormula()); - wb2.close(); + try (HSSFWorkbook wb1 = new HSSFWorkbook()) { + HSSFSheet s = wb1.createSheet("testSheet1"); + HSSFRow r = s.createRow(0); + HSSFCell 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\")"); + + try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { + s = wb2.getSheetAt(0); + r = s.getRow(0); + c = r.getCell(4); + + assertEquals("expected: IF(A1=D1,\"A1\",\"B1\") got " + c.getCellFormula(), ("IF(A1=D1,\"A1\",\"B1\")"), c.getCellFormula()); + } + } - wb1 = openSample("IfFormulaTest.xls"); - s = wb1.getSheetAt(0); - r = s.getRow(3); - c = r.getCell(0); - assertEquals("expected: IF(A3=A1,\"A1\",\"A2\") got " + c.getCellFormula(), ("IF(A3=A1,\"A1\",\"A2\")"), c.getCellFormula()); - //c = r.getCell((short)1); - //assertTrue("expected: A!A1+A!B1 got: "+c.getCellFormula(), ("A!A1+A!B1").equals(c.getCellFormula())); - wb1.close(); + try (HSSFWorkbook wb1 = openSample("IfFormulaTest.xls")) { + HSSFSheet s = wb1.getSheetAt(0); + HSSFRow r = s.getRow(3); + HSSFCell c = r.getCell(0); + assertEquals("expected: IF(A3=A1,\"A1\",\"A2\") got " + c.getCellFormula(), ("IF(A3=A1,\"A1\",\"A2\")"), c.getCellFormula()); + //c = r.getCell((short)1); + //assertTrue("expected: A!A1+A!B1 got: "+c.getCellFormula(), ("A!A1+A!B1").equals(c.getCellFormula())); + } - wb1 = new HSSFWorkbook(); - s = wb1.createSheet("testSheet1"); - r = null; - c = null; - r = s.createRow(0); - c=r.createCell(0); c.setCellFormula("IF(1=1,0,1)"); + try (HSSFWorkbook wb1 = new HSSFWorkbook()) { + HSSFSheet s = wb1.createSheet("testSheet1"); + HSSFRow r = s.createRow(0); + HSSFCell c = r.createCell(0); + c.setCellFormula("IF(1=1,0,1)"); - HSSFTestDataSamples.writeOutAndReadBack(wb1).close(); - wb1.close(); + writeOutAndReadBack(wb1).close(); + } - wb1 = new HSSFWorkbook(); - s = wb1.createSheet("testSheet1"); - r = null; - c = null; - r = s.createRow(0); - c=r.createCell(0); - c.setCellValue(1); + try (HSSFWorkbook wb1 = new HSSFWorkbook()) { + HSSFSheet s = wb1.createSheet("testSheet1"); + HSSFRow r = s.createRow(0); + HSSFCell c = r.createCell(0); + c.setCellValue(1); - c=r.createCell(1); - c.setCellValue(3); + c = r.createCell(1); + c.setCellValue(3); - HSSFCell formulaCell=r.createCell(3); + HSSFCell formulaCell = r.createCell(3); - r = s.createRow(1); - c=r.createCell(0); - c.setCellValue(3); + r = s.createRow(1); + c = r.createCell(0); + c.setCellValue(3); - c=r.createCell(1); - c.setCellValue(7); + c = r.createCell(1); + c.setCellValue(7); - formulaCell.setCellFormula("IF(A1=B1,AVERAGE(A1:B1),AVERAGE(A2:B2))"); + formulaCell.setCellFormula("IF(A1=B1,AVERAGE(A1:B1),AVERAGE(A2:B2))"); - HSSFTestDataSamples.writeOutAndReadBack(wb1).close(); - wb1.close(); + writeOutAndReadBack(wb1).close(); + } } @Test public void testSumIf() throws IOException { 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(); + try (HSSFWorkbook wb = openSample("sumifformula.xls")) { + HSSFSheet s = wb.getSheetAt(0); + HSSFRow r = s.getRow(0); + HSSFCell c = r.getCell(2); + assertEquals(function, c.getCellFormula()); + } - r = s.createRow(0); - c=r.createCell(0); c.setCellValue(1000); - c=r.createCell(1); c.setCellValue(1); + try (HSSFWorkbook wb = new HSSFWorkbook()) { + HSSFSheet s = wb.createSheet(); + HSSFRow r = s.createRow(0); + HSSFCell 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(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(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(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.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); + r = s.getRow(0); + c = r.createCell(2); + c.setCellFormula(function); - HSSFTestDataSamples.writeOutAndReadBack(wb).close(); - wb.close(); + writeOutAndReadBack(wb).close(); + } } @Test public void testSquareMacro() throws IOException { - HSSFWorkbook w = openSample("SquareMacro.xls"); - - HSSFSheet s0 = w.getSheetAt(0); - HSSFRow[] r = {s0.getRow(0), s0.getRow(1)}; + try (HSSFWorkbook w = openSample("SquareMacro.xls")) { + HSSFSheet s0 = w.getSheetAt(0); + HSSFRow[] r = {s0.getRow(0), s0.getRow(1)}; - HSSFCell a1 = r[0].getCell(0); - assertEquals("square(1)", a1.getCellFormula()); - assertEquals(1d, a1.getNumericCellValue(), 1e-9); + HSSFCell a1 = r[0].getCell(0); + assertEquals("square(1)", a1.getCellFormula()); + assertEquals(1d, a1.getNumericCellValue(), 1e-9); - HSSFCell a2 = r[1].getCell(0); - assertEquals("square(2)", a2.getCellFormula()); - assertEquals(4d, a2.getNumericCellValue(), 1e-9); + HSSFCell a2 = r[1].getCell(0); + assertEquals("square(2)", a2.getCellFormula()); + assertEquals(4d, a2.getNumericCellValue(), 1e-9); - HSSFCell b1 = r[0].getCell(1); - assertEquals("IF(TRUE,square(1))", b1.getCellFormula()); - assertEquals(1d, b1.getNumericCellValue(), 1e-9); + HSSFCell b1 = r[0].getCell(1); + assertEquals("IF(TRUE,square(1))", b1.getCellFormula()); + assertEquals(1d, b1.getNumericCellValue(), 1e-9); - HSSFCell b2 = r[1].getCell(1); - assertEquals("IF(TRUE,square(2))", b2.getCellFormula()); - assertEquals(4d, b2.getNumericCellValue(), 1e-9); + HSSFCell b2 = r[1].getCell(1); + assertEquals("IF(TRUE,square(2))", b2.getCellFormula()); + assertEquals(4d, b2.getNumericCellValue(), 1e-9); - HSSFCell c1 = r[0].getCell(2); - assertEquals("square(square(1))", c1.getCellFormula()); - assertEquals(1d, c1.getNumericCellValue(), 1e-9); + HSSFCell c1 = r[0].getCell(2); + assertEquals("square(square(1))", c1.getCellFormula()); + assertEquals(1d, c1.getNumericCellValue(), 1e-9); - HSSFCell c2 = r[1].getCell(2); - assertEquals("square(square(2))", c2.getCellFormula()); - assertEquals(16d, c2.getNumericCellValue(), 1e-9); + HSSFCell c2 = r[1].getCell(2); + assertEquals("square(square(2))", c2.getCellFormula()); + assertEquals(16d, c2.getNumericCellValue(), 1e-9); - HSSFCell d1 = r[0].getCell(3); - assertEquals("square(one())", d1.getCellFormula()); - assertEquals(1d, d1.getNumericCellValue(), 1e-9); + HSSFCell d1 = r[0].getCell(3); + assertEquals("square(one())", d1.getCellFormula()); + assertEquals(1d, d1.getNumericCellValue(), 1e-9); - HSSFCell d2 = r[1].getCell(3); - assertEquals("square(two())", d2.getCellFormula()); - assertEquals(4d, d2.getNumericCellValue(), 1e-9); - - w.close(); + HSSFCell d2 = r[1].getCell(3); + assertEquals("square(two())", d2.getCellFormula()); + assertEquals(4d, d2.getNumericCellValue(), 1e-9); + } } @Test public void testStringFormulaRead() throws IOException { - HSSFWorkbook w = openSample("StringFormulas.xls"); - HSSFCell c = w.getSheetAt(0).getRow(0).getCell(0); - assertEquals("String Cell value","XYZ",c.getRichStringCellValue().getString()); - w.close(); + try (HSSFWorkbook w = openSample("StringFormulas.xls")) { + HSSFCell c = w.getSheetAt(0).getRow(0).getCell(0); + assertEquals("String Cell value", "XYZ", c.getRichStringCellValue().getString()); + } } /** test for bug 34021*/ @Test - public void testComplexSheetRefs () throws IOException { - try (HSSFWorkbook sb = new HSSFWorkbook()) { - HSSFSheet s1 = sb.createSheet("Sheet a.1"); - HSSFSheet s2 = sb.createSheet("Sheet.A"); + public void testComplexSheetRefs() throws IOException { + try (HSSFWorkbook wb1 = new HSSFWorkbook()) { + HSSFSheet s1 = wb1.createSheet("Sheet a.1"); + HSSFSheet s2 = wb1.createSheet("Sheet.A"); s2.createRow(1).createCell(2).setCellFormula("'Sheet a.1'!A1"); s1.createRow(1).createCell(2).setCellFormula("'Sheet.A'!A1"); - File file = TempFile.createTempFile("testComplexSheetRefs", ".xls"); - try (FileOutputStream stream = new FileOutputStream(file)) { - sb.write(stream); + + try (HSSFWorkbook wb2 = writeOutAndReadBack(wb1)) { + s1 = wb2.getSheet("Sheet a.1"); + assertNotNull(s1); + assertEquals("Sheet.A!A1", s1.getRow(1).getCell(2).getCellFormula()); + s2 = wb2.getSheet("Sheet.A"); + assertNotNull(s2); + assertEquals("'Sheet a.1'!A1", s2.getRow(1).getCell(2).getCellFormula()); } } } @@ -900,26 +832,20 @@ public final class TestFormulas { /** Unknown Ptg 3C*/ @Test public void test27272_1() throws IOException { - HSSFWorkbook wb = openSample("27272_1.xls"); - wb.getSheetAt(0); - assertEquals("Reference for named range ", "Compliance!#REF!",wb.getNameAt(0).getRefersToFormula()); - File outF = TempFile.createTempFile("bug27272_1",".xls"); - try (FileOutputStream stream = new FileOutputStream(outF)) { - wb.write(stream); + try (HSSFWorkbook wb = openSample("27272_1.xls")) { + wb.getSheetAt(0); + assertEquals("Reference for named range ", "Compliance!#REF!", wb.getNameAt(0).getRefersToFormula()); + writeOutAndReadBack(wb).close(); } - wb.close(); } /** Unknown Ptg 3D*/ @Test public void test27272_2() throws IOException { - HSSFWorkbook wb = openSample("27272_2.xls"); - assertEquals("Reference for named range ", "LOAD.POD_HISTORIES!#REF!",wb.getNameAt(0).getRefersToFormula()); - File outF = TempFile.createTempFile("bug27272_2",".xls"); - try (FileOutputStream stream = new FileOutputStream(outF)) { - wb.write(stream); + try (HSSFWorkbook wb = openSample("27272_2.xls")) { + assertEquals("Reference for named range ", "LOAD.POD_HISTORIES!#REF!", wb.getNameAt(0).getRefersToFormula()); + writeOutAndReadBack(wb).close(); } - wb.close(); } /** MissingArgPtg */ @@ -933,13 +859,12 @@ public final class TestFormulas { @Test public void testSharedFormula() throws IOException { - HSSFWorkbook wb = openSample("SharedFormulaTest.xls"); - - assertEquals("A$1*2", wb.getSheetAt(0).getRow(1).getCell(1).toString()); - assertEquals("$A11*2", wb.getSheetAt(0).getRow(11).getCell(1).toString()); - assertEquals("DZ2*2", wb.getSheetAt(0).getRow(1).getCell(128).toString()); - assertEquals("B32770*2", wb.getSheetAt(0).getRow(32768).getCell(1).toString()); - wb.close(); + try (HSSFWorkbook wb = openSample("SharedFormulaTest.xls")) { + assertEquals("A$1*2", wb.getSheetAt(0).getRow(1).getCell(1).toString()); + assertEquals("$A11*2", wb.getSheetAt(0).getRow(11).getCell(1).toString()); + assertEquals("DZ2*2", wb.getSheetAt(0).getRow(1).getCell(128).toString()); + assertEquals("B32770*2", wb.getSheetAt(0).getRow(32768).getCell(1).toString()); + } } /** @@ -947,48 +872,46 @@ public final class TestFormulas { */ @Test public void testSheetLevelFormulas() throws IOException { - HSSFWorkbook wb = new HSSFWorkbook(); - - HSSFRow row; - HSSFSheet sh1 = wb.createSheet("Sheet1"); - HSSFName nm1 = wb.createName(); - nm1.setNameName("sales_1"); - nm1.setSheetIndex(0); - nm1.setRefersToFormula("Sheet1!$A$1"); - row = sh1.createRow(0); - row.createCell(0).setCellValue(3); - row.createCell(1).setCellFormula("sales_1"); - row.createCell(2).setCellFormula("sales_1*2"); - - - HSSFSheet sh2 = wb.createSheet("Sheet2"); - HSSFName nm2 = wb.createName(); - nm2.setNameName("sales_1"); - nm2.setSheetIndex(1); - nm2.setRefersToFormula("Sheet2!$A$1"); - - row = sh2.createRow(0); - row.createCell(0).setCellValue(5); - row.createCell(1).setCellFormula("sales_1"); - row.createCell(2).setCellFormula("sales_1*3"); - - //check that NamePtg refers to the correct NameRecord - Ptg[] ptgs1 = HSSFFormulaParser.parse("sales_1", wb, FormulaType.CELL, 0); - NamePtg nPtg1 = (NamePtg)ptgs1[0]; - assertSame(nm1, wb.getNameAt(nPtg1.getIndex())); - - Ptg[] ptgs2 = HSSFFormulaParser.parse("sales_1", wb, FormulaType.CELL, 1); - NamePtg nPtg2 = (NamePtg)ptgs2[0]; - assertSame(nm2, wb.getNameAt(nPtg2.getIndex())); - - //check that the formula evaluator returns the correct result - HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb); - assertEquals(3.0, evaluator.evaluate(sh1.getRow(0).getCell(1)).getNumberValue(), 0.0); - assertEquals(6.0, evaluator.evaluate(sh1.getRow(0).getCell(2)).getNumberValue(), 0.0); - - assertEquals(5.0, evaluator.evaluate(sh2.getRow(0).getCell(1)).getNumberValue(), 0.0); - assertEquals(15.0, evaluator.evaluate(sh2.getRow(0).getCell(2)).getNumberValue(), 0.0); - wb.close(); + try (HSSFWorkbook wb = new HSSFWorkbook()) { + HSSFSheet sh1 = wb.createSheet("Sheet1"); + HSSFName nm1 = wb.createName(); + nm1.setNameName("sales_1"); + nm1.setSheetIndex(0); + nm1.setRefersToFormula("Sheet1!$A$1"); + HSSFRow row = sh1.createRow(0); + row.createCell(0).setCellValue(3); + row.createCell(1).setCellFormula("sales_1"); + row.createCell(2).setCellFormula("sales_1*2"); + + + HSSFSheet sh2 = wb.createSheet("Sheet2"); + HSSFName nm2 = wb.createName(); + nm2.setNameName("sales_1"); + nm2.setSheetIndex(1); + nm2.setRefersToFormula("Sheet2!$A$1"); + + row = sh2.createRow(0); + row.createCell(0).setCellValue(5); + row.createCell(1).setCellFormula("sales_1"); + row.createCell(2).setCellFormula("sales_1*3"); + + //check that NamePtg refers to the correct NameRecord + Ptg[] ptgs1 = HSSFFormulaParser.parse("sales_1", wb, FormulaType.CELL, 0); + NamePtg nPtg1 = (NamePtg) ptgs1[0]; + assertSame(nm1, wb.getNameAt(nPtg1.getIndex())); + + Ptg[] ptgs2 = HSSFFormulaParser.parse("sales_1", wb, FormulaType.CELL, 1); + NamePtg nPtg2 = (NamePtg) ptgs2[0]; + assertSame(nm2, wb.getNameAt(nPtg2.getIndex())); + + //check that the formula evaluator returns the correct result + HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb); + assertEquals(3.0, evaluator.evaluate(sh1.getRow(0).getCell(1)).getNumberValue(), 0.0); + assertEquals(6.0, evaluator.evaluate(sh1.getRow(0).getCell(2)).getNumberValue(), 0.0); + + assertEquals(5.0, evaluator.evaluate(sh2.getRow(0).getCell(1)).getNumberValue(), 0.0); + assertEquals(15.0, evaluator.evaluate(sh2.getRow(0).getCell(2)).getNumberValue(), 0.0); + } } /** |