0001: /* ====================================================================
0002: Licensed to the Apache Software Foundation (ASF) under one or more
0003: contributor license agreements. See the NOTICE file distributed with
0004: this work for additional information regarding copyright ownership.
0005: The ASF licenses this file to You under the Apache License, Version 2.0
0006: (the "License"); you may not use this file except in compliance with
0007: the License. You may obtain a copy of the License at
0008:
0009: http://www.apache.org/licenses/LICENSE-2.0
0010:
0011: Unless required by applicable law or agreed to in writing, software
0012: distributed under the License is distributed on an "AS IS" BASIS,
0013: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0014: See the License for the specific language governing permissions and
0015: limitations under the License.
0016: ==================================================================== */
0017:
0018: package org.apache.poi.hssf.usermodel;
0019:
0020: import java.io.File;
0021: import java.io.FileInputStream;
0022: import java.io.FileOutputStream;
0023: import java.io.IOException;
0024: import java.util.Date;
0025:
0026: import junit.framework.TestCase;
0027:
0028: import org.apache.poi.hssf.util.CellReference;
0029: import org.apache.poi.util.TempFile;
0030:
0031: /**
0032: * @author Andrew C. Oliver (acoliver at apache dot org)
0033: * @author Avik Sengupta
0034: */
0035:
0036: public class TestFormulas extends TestCase {
0037: public TestFormulas(String s) {
0038: super (s);
0039: }
0040:
0041: /**
0042: * Add 1+1 -- WHoohoo!
0043: */
0044:
0045: public void testBasicAddIntegers() throws Exception {
0046:
0047: short rownum = 0;
0048: File file = TempFile.createTempFile("testFormula", ".xls");
0049: FileOutputStream out = new FileOutputStream(file);
0050: HSSFWorkbook wb = new HSSFWorkbook();
0051: HSSFSheet s = wb.createSheet();
0052: HSSFRow r = null;
0053: HSSFCell c = null;
0054:
0055: //get our minimum values
0056: r = s.createRow((short) 1);
0057: c = r.createCell((short) 1);
0058: c.setCellFormula(1 + "+" + 1);
0059:
0060: wb.write(out);
0061: out.close();
0062:
0063: FileInputStream in = new FileInputStream(file);
0064: wb = new HSSFWorkbook(in);
0065: s = wb.getSheetAt(0);
0066: r = s.getRow((short) 1);
0067: c = r.getCell((short) 1);
0068:
0069: assertTrue("Formula is as expected", ("1+1".equals(c
0070: .getCellFormula())));
0071: in.close();
0072: }
0073:
0074: /**
0075: * Add various integers
0076: */
0077:
0078: public void testAddIntegers() throws Exception {
0079: binomialOperator("+");
0080: }
0081:
0082: /**
0083: * Multiply various integers
0084: */
0085:
0086: public void testMultplyIntegers() throws Exception {
0087: binomialOperator("*");
0088: }
0089:
0090: /**
0091: * Subtract various integers
0092: */
0093: public void testSubtractIntegers() throws Exception {
0094: binomialOperator("-");
0095: }
0096:
0097: /**
0098: * Subtract various integers
0099: */
0100: public void testDivideIntegers() throws Exception {
0101: binomialOperator("/");
0102: }
0103:
0104: /**
0105: * Exponentialize various integers;
0106: */
0107: public void testPowerIntegers() throws Exception {
0108: binomialOperator("^");
0109: }
0110:
0111: /**
0112: * Concatinate two numbers 1&2 = 12
0113: */
0114: public void testConcatIntegers() throws Exception {
0115: binomialOperator("&");
0116: }
0117:
0118: /**
0119: * tests 1*2+3*4
0120: */
0121: public void testOrderOfOperationsMultiply() throws Exception {
0122: orderTest("1*2+3*4");
0123: }
0124:
0125: /**
0126: * tests 1*2+3^4
0127: */
0128: public void testOrderOfOperationsPower() throws Exception {
0129: orderTest("1*2+3^4");
0130: }
0131:
0132: /**
0133: * Tests that parenthesis are obeyed
0134: */
0135: public void testParenthesis() throws Exception {
0136: orderTest("(1*3)+2+(1+2)*(3^4)^5");
0137: }
0138:
0139: public void testReferencesOpr() throws Exception {
0140: String[] operation = new String[] { "+", "-", "*", "/", "^",
0141: "&" };
0142: for (int k = 0; k < operation.length; k++) {
0143: operationRefTest(operation[k]);
0144: }
0145: }
0146:
0147: /**
0148: * Tests creating a file with floating point in a formula.
0149: *
0150: */
0151: public void testFloat() throws Exception {
0152: floatTest("*");
0153: floatTest("/");
0154: }
0155:
0156: private void floatTest(String operator) throws Exception {
0157: short rownum = 0;
0158: File file = TempFile.createTempFile("testFormulaFloat", ".xls");
0159: FileOutputStream out = new FileOutputStream(file);
0160: HSSFWorkbook wb = new HSSFWorkbook();
0161: HSSFSheet s = wb.createSheet();
0162: HSSFRow r = null;
0163: HSSFCell c = null;
0164:
0165: //get our minimum values
0166:
0167: r = s.createRow((short) 0);
0168: c = r.createCell((short) 1);
0169: c.setCellFormula("" + Float.MIN_VALUE + operator
0170: + Float.MIN_VALUE);
0171:
0172: for (short x = 1; x < Short.MAX_VALUE && x > 0; x = (short) (x * 2)) {
0173: r = s.createRow((short) x);
0174:
0175: for (short y = 1; y < 256 && y > 0; y = (short) (y + 2)) {
0176:
0177: c = r.createCell((short) y);
0178: c.setCellFormula("" + x + "." + y + operator + y + "."
0179: + x);
0180:
0181: }
0182: }
0183: if (s.getLastRowNum() < Short.MAX_VALUE) {
0184: r = s.createRow((short) 0);
0185: c = r.createCell((short) 0);
0186: c.setCellFormula("" + Float.MAX_VALUE + operator
0187: + Float.MAX_VALUE);
0188: }
0189: wb.write(out);
0190: out.close();
0191: assertTrue("file exists", file.exists());
0192: out = null;
0193: wb = null; //otherwise we get out of memory error!
0194: floatVerify(operator, file);
0195:
0196: }
0197:
0198: private void floatVerify(String operator, File file)
0199: throws Exception {
0200: short rownum = 0;
0201:
0202: FileInputStream in = new FileInputStream(file);
0203: HSSFWorkbook wb = new HSSFWorkbook(in);
0204: HSSFSheet s = wb.getSheetAt(0);
0205: HSSFRow r = null;
0206: HSSFCell c = null;
0207:
0208: // dont know how to check correct result .. for the moment, we just verify that the file can be read.
0209:
0210: for (short x = 1; x < Short.MAX_VALUE && x > 0; x = (short) (x * 2)) {
0211: r = s.getRow((short) x);
0212:
0213: for (short y = 1; y < 256 && y > 0; y = (short) (y + 2)) {
0214:
0215: c = r.getCell((short) y);
0216: assertTrue("got a formula", c.getCellFormula() != null);
0217:
0218: assertTrue("loop Formula is as expected " + x + "." + y
0219: + operator + y + "." + x + "!="
0220: + c.getCellFormula(), (("" + x + "." + y
0221: + operator + y + "." + x).equals(c
0222: .getCellFormula())));
0223:
0224: }
0225: }
0226:
0227: in.close();
0228: assertTrue("file exists", file.exists());
0229: }
0230:
0231: public void testAreaSum() throws Exception {
0232: areaFunctionTest("SUM");
0233: }
0234:
0235: public void testAreaAverage() throws Exception {
0236: areaFunctionTest("AVERAGE");
0237: }
0238:
0239: public void testRefArraySum() throws Exception {
0240: refArrayFunctionTest("SUM");
0241: }
0242:
0243: public void testAreaArraySum() throws Exception {
0244: refAreaArrayFunctionTest("SUM");
0245: }
0246:
0247: private void operationRefTest(String operator) throws Exception {
0248: File file = TempFile.createTempFile("testFormula", ".xls");
0249: FileOutputStream out = new FileOutputStream(file);
0250: HSSFWorkbook wb = new HSSFWorkbook();
0251: HSSFSheet s = wb.createSheet();
0252: HSSFRow r = null;
0253: HSSFCell c = null;
0254:
0255: //get our minimum values
0256: r = s.createRow((short) 0);
0257: c = r.createCell((short) 1);
0258: c.setCellFormula("A2" + operator + "A3");
0259:
0260: for (short x = 1; x < Short.MAX_VALUE && x > 0; x = (short) (x * 2)) {
0261: r = s.createRow((short) x);
0262:
0263: for (short y = 1; y < 256 && y > 0; y++) {
0264:
0265: String ref = null;
0266: String ref2 = null;
0267: short refx1 = 0;
0268: short refy1 = 0;
0269: short refx2 = 0;
0270: short refy2 = 0;
0271: if (x + 50 < Short.MAX_VALUE) {
0272: refx1 = (short) (x + 50);
0273: refx2 = (short) (x + 46);
0274: } else {
0275: refx1 = (short) (x - 4);
0276: refx2 = (short) (x - 3);
0277: }
0278:
0279: if (y + 50 < 255) {
0280: refy1 = (short) (y + 50);
0281: refy2 = (short) (y + 49);
0282: } else {
0283: refy1 = (short) (y - 4);
0284: refy2 = (short) (y - 3);
0285: }
0286:
0287: c = r.getCell((short) y);
0288: CellReference cr = new CellReference(refx1, refy1);
0289: ref = cr.toString();
0290: cr = new CellReference(refx2, refy2);
0291: ref2 = cr.toString();
0292:
0293: c = r.createCell((short) y);
0294: c.setCellFormula("" + ref + operator + ref2);
0295:
0296: }
0297: }
0298:
0299: //make sure we do the maximum value of the Int operator
0300: if (s.getLastRowNum() < Short.MAX_VALUE) {
0301: r = s.createRow((short) 0);
0302: c = r.createCell((short) 0);
0303: c.setCellFormula("" + "B1" + operator + "IV255");
0304: }
0305:
0306: wb.write(out);
0307: out.close();
0308: assertTrue("file exists", file.exists());
0309: operationalRefVerify(operator, file);
0310: }
0311:
0312: /**
0313: * Opens the sheet we wrote out by binomialOperator and makes sure the formulas
0314: * all match what we expect (x operator y)
0315: */
0316: private void operationalRefVerify(String operator, File file)
0317: throws Exception {
0318: short rownum = 0;
0319:
0320: FileInputStream in = new FileInputStream(file);
0321: HSSFWorkbook wb = new HSSFWorkbook(in);
0322: HSSFSheet s = wb.getSheetAt(0);
0323: HSSFRow r = null;
0324: HSSFCell c = null;
0325:
0326: //get our minimum values
0327: r = s.getRow((short) 0);
0328: c = r.getCell((short) 1);
0329: //get our minimum values
0330: assertTrue("minval Formula is as expected A2" + operator
0331: + "A3 != " + c.getCellFormula(),
0332: (("A2" + operator + "A3").equals(c.getCellFormula())));
0333:
0334: for (short x = 1; x < Short.MAX_VALUE && x > 0; x = (short) (x * 2)) {
0335: r = s.getRow((short) x);
0336:
0337: for (short y = 1; y < 256 && y > 0; y++) {
0338:
0339: String ref = null;
0340: String ref2 = null;
0341: short refx1 = 0;
0342: short refy1 = 0;
0343: short refx2 = 0;
0344: short refy2 = 0;
0345: if (x + 50 < Short.MAX_VALUE) {
0346: refx1 = (short) (x + 50);
0347: refx2 = (short) (x + 46);
0348: } else {
0349: refx1 = (short) (x - 4);
0350: refx2 = (short) (x - 3);
0351: }
0352:
0353: if (y + 50 < 255) {
0354: refy1 = (short) (y + 50);
0355: refy2 = (short) (y + 49);
0356: } else {
0357: refy1 = (short) (y - 4);
0358: refy2 = (short) (y - 3);
0359: }
0360:
0361: c = r.getCell((short) y);
0362: CellReference cr = new CellReference(refx1, refy1);
0363: ref = cr.toString();
0364: cr = new CellReference(refx2, refy2);
0365: ref2 = cr.toString();
0366:
0367: assertTrue("loop Formula is as expected " + ref
0368: + operator + ref2 + "!=" + c.getCellFormula(),
0369: (("" + ref + operator + ref2).equals(c
0370: .getCellFormula())));
0371:
0372: }
0373: }
0374:
0375: //test our maximum values
0376: r = s.getRow((short) 0);
0377: c = r.getCell((short) 0);
0378:
0379: assertTrue(
0380: "maxval Formula is as expected",
0381: (("B1" + operator + "IV255").equals(c.getCellFormula())));
0382:
0383: in.close();
0384: assertTrue("file exists", file.exists());
0385: }
0386:
0387: /**
0388: * tests order wrting out == order writing in for a given formula
0389: */
0390: private void orderTest(String formula) throws Exception {
0391: File file = TempFile.createTempFile("testFormula", ".xls");
0392: FileOutputStream out = new FileOutputStream(file);
0393: HSSFWorkbook wb = new HSSFWorkbook();
0394: HSSFSheet s = wb.createSheet();
0395: HSSFRow r = null;
0396: HSSFCell c = null;
0397:
0398: //get our minimum values
0399: r = s.createRow((short) 0);
0400: c = r.createCell((short) 1);
0401: c.setCellFormula(formula);
0402:
0403: wb.write(out);
0404: out.close();
0405: assertTrue("file exists", file.exists());
0406:
0407: FileInputStream in = new FileInputStream(file);
0408: wb = new HSSFWorkbook(in);
0409: s = wb.getSheetAt(0);
0410:
0411: //get our minimum values
0412: r = s.getRow((short) 0);
0413: c = r.getCell((short) 1);
0414: assertTrue("minval Formula is as expected", formula.equals(c
0415: .getCellFormula()));
0416:
0417: in.close();
0418: }
0419:
0420: /**
0421: * All multi-binomial operator tests use this to create a worksheet with a
0422: * huge set of x operator y formulas. Next we call binomialVerify and verify
0423: * that they are all how we expect.
0424: */
0425: private void binomialOperator(String operator) throws Exception {
0426: short rownum = 0;
0427: File file = TempFile.createTempFile("testFormula", ".xls");
0428: FileOutputStream out = new FileOutputStream(file);
0429: HSSFWorkbook wb = new HSSFWorkbook();
0430: HSSFSheet s = wb.createSheet();
0431: HSSFRow r = null;
0432: HSSFCell c = null;
0433:
0434: //get our minimum values
0435: r = s.createRow((short) 0);
0436: c = r.createCell((short) 1);
0437: c.setCellFormula(1 + operator + 1);
0438:
0439: for (short x = 1; x < Short.MAX_VALUE && x > 0; x = (short) (x * 2)) {
0440: r = s.createRow((short) x);
0441:
0442: for (short y = 1; y < 256 && y > 0; y++) {
0443:
0444: c = r.createCell((short) y);
0445: c.setCellFormula("" + x + operator + y);
0446:
0447: }
0448: }
0449:
0450: //make sure we do the maximum value of the Int operator
0451: if (s.getLastRowNum() < Short.MAX_VALUE) {
0452: r = s.createRow((short) 0);
0453: c = r.createCell((short) 0);
0454: c.setCellFormula("" + Short.MAX_VALUE + operator
0455: + Short.MAX_VALUE);
0456: }
0457:
0458: wb.write(out);
0459: out.close();
0460: assertTrue("file exists", file.exists());
0461:
0462: binomialVerify(operator, file);
0463: }
0464:
0465: /**
0466: * Opens the sheet we wrote out by binomialOperator and makes sure the formulas
0467: * all match what we expect (x operator y)
0468: */
0469: private void binomialVerify(String operator, File file)
0470: throws Exception {
0471: short rownum = 0;
0472:
0473: FileInputStream in = new FileInputStream(file);
0474: HSSFWorkbook wb = new HSSFWorkbook(in);
0475: HSSFSheet s = wb.getSheetAt(0);
0476: HSSFRow r = null;
0477: HSSFCell c = null;
0478:
0479: //get our minimum values
0480: r = s.getRow((short) 0);
0481: c = r.getCell((short) 1);
0482: assertTrue("minval Formula is as expected 1" + operator
0483: + "1 != " + c.getCellFormula(), (("1" + operator + "1")
0484: .equals(c.getCellFormula())));
0485:
0486: for (short x = 1; x < Short.MAX_VALUE && x > 0; x = (short) (x * 2)) {
0487: r = s.getRow((short) x);
0488:
0489: for (short y = 1; y < 256 && y > 0; y++) {
0490:
0491: c = r.getCell((short) y);
0492:
0493: assertTrue("loop Formula is as expected " + x
0494: + operator + y + "!=" + c.getCellFormula(),
0495: (("" + x + operator + y).equals(c
0496: .getCellFormula())));
0497:
0498: }
0499: }
0500:
0501: //test our maximum values
0502: r = s.getRow((short) 0);
0503: c = r.getCell((short) 0);
0504:
0505: assertTrue("maxval Formula is as expected", ((""
0506: + Short.MAX_VALUE + operator + Short.MAX_VALUE)
0507: .equals(c.getCellFormula())));
0508:
0509: in.close();
0510: assertTrue("file exists", file.exists());
0511: }
0512:
0513: /**
0514: * Writes a function then tests to see if its correct
0515: *
0516: */
0517: public void areaFunctionTest(String function) throws Exception {
0518:
0519: short rownum = 0;
0520: File file = TempFile.createTempFile("testFormulaAreaFunction"
0521: + function, ".xls");
0522: FileOutputStream out = new FileOutputStream(file);
0523: HSSFWorkbook wb = new HSSFWorkbook();
0524: HSSFSheet s = wb.createSheet();
0525: HSSFRow r = null;
0526: HSSFCell c = null;
0527:
0528: r = s.createRow((short) 0);
0529:
0530: c = r.createCell((short) 0);
0531: c.setCellFormula(function + "(A2:A3)");
0532:
0533: wb.write(out);
0534: out.close();
0535: assertTrue("file exists", file.exists());
0536:
0537: FileInputStream in = new FileInputStream(file);
0538: wb = new HSSFWorkbook(in);
0539: s = wb.getSheetAt(0);
0540: r = s.getRow(0);
0541: c = r.getCell((short) 0);
0542:
0543: assertTrue("function =" + function + "(A2:A3)",
0544: ((function + "(A2:A3)").equals((function + "(A2:A3)"))));
0545: in.close();
0546: }
0547:
0548: /**
0549: * Writes a function then tests to see if its correct
0550: *
0551: */
0552: public void refArrayFunctionTest(String function) throws Exception {
0553:
0554: short rownum = 0;
0555: File file = TempFile.createTempFile("testFormulaArrayFunction"
0556: + function, ".xls");
0557: FileOutputStream out = new FileOutputStream(file);
0558: HSSFWorkbook wb = new HSSFWorkbook();
0559: HSSFSheet s = wb.createSheet();
0560: HSSFRow r = null;
0561: HSSFCell c = null;
0562:
0563: r = s.createRow((short) 0);
0564:
0565: c = r.createCell((short) 0);
0566: c.setCellFormula(function + "(A2,A3)");
0567:
0568: wb.write(out);
0569: out.close();
0570: assertTrue("file exists", file.exists());
0571:
0572: FileInputStream in = new FileInputStream(file);
0573: wb = new HSSFWorkbook(in);
0574: s = wb.getSheetAt(0);
0575: r = s.getRow(0);
0576: c = r.getCell((short) 0);
0577:
0578: assertTrue("function =" + function + "(A2,A3)",
0579: ((function + "(A2,A3)").equals(c.getCellFormula())));
0580: in.close();
0581: }
0582:
0583: /**
0584: * Writes a function then tests to see if its correct
0585: *
0586: */
0587: public void refAreaArrayFunctionTest(String function)
0588: throws Exception {
0589:
0590: short rownum = 0;
0591: File file = TempFile.createTempFile(
0592: "testFormulaAreaArrayFunction" + function, ".xls");
0593: FileOutputStream out = new FileOutputStream(file);
0594: HSSFWorkbook wb = new HSSFWorkbook();
0595: HSSFSheet s = wb.createSheet();
0596: HSSFRow r = null;
0597: HSSFCell c = null;
0598:
0599: r = s.createRow((short) 0);
0600:
0601: c = r.createCell((short) 0);
0602: c.setCellFormula(function + "(A2:A4,B2:B4)");
0603: c = r.createCell((short) 1);
0604: c.setCellFormula(function + "($A$2:$A4,B$2:B4)");
0605:
0606: wb.write(out);
0607: out.close();
0608: assertTrue("file exists", file.exists());
0609:
0610: FileInputStream in = new FileInputStream(file);
0611: wb = new HSSFWorkbook(in);
0612: s = wb.getSheetAt(0);
0613: r = s.getRow(0);
0614: c = r.getCell((short) 0);
0615:
0616: assertTrue("function =" + function + "(A2:A4,B2:B4)",
0617: ((function + "(A2:A4,B2:B4)")
0618: .equals(c.getCellFormula())));
0619:
0620: c = r.getCell((short) 1);
0621: assertTrue("function =" + function + "($A$2:$A4,B$2:B4)",
0622: ((function + "($A$2:$A4,B$2:B4)").equals(c
0623: .getCellFormula())));
0624: in.close();
0625: }
0626:
0627: public void testAbsRefs() throws Exception {
0628: File file = TempFile
0629: .createTempFile("testFormulaAbsRef", ".xls");
0630: FileOutputStream out = new FileOutputStream(file);
0631: HSSFWorkbook wb = new HSSFWorkbook();
0632: HSSFSheet s = wb.createSheet();
0633: HSSFRow r = null;
0634: HSSFCell c = null;
0635:
0636: r = s.createRow((short) 0);
0637:
0638: c = r.createCell((short) 0);
0639: c.setCellFormula("A3+A2");
0640: c = r.createCell((short) 1);
0641: c.setCellFormula("$A3+$A2");
0642: c = r.createCell((short) 2);
0643: c.setCellFormula("A$3+A$2");
0644: c = r.createCell((short) 3);
0645: c.setCellFormula("$A$3+$A$2");
0646: c = r.createCell((short) 4);
0647: c.setCellFormula("SUM($A$3,$A$2)");
0648:
0649: wb.write(out);
0650: out.close();
0651: assertTrue("file exists", file.exists());
0652:
0653: FileInputStream in = new FileInputStream(file);
0654: wb = new HSSFWorkbook(in);
0655: s = wb.getSheetAt(0);
0656: r = s.getRow(0);
0657: c = r.getCell((short) 0);
0658: assertTrue("A3+A2", ("A3+A2").equals(c.getCellFormula()));
0659: c = r.getCell((short) 1);
0660: assertTrue("$A3+$A2", ("$A3+$A2").equals(c.getCellFormula()));
0661: c = r.getCell((short) 2);
0662: assertTrue("A$3+A$2", ("A$3+A$2").equals(c.getCellFormula()));
0663: c = r.getCell((short) 3);
0664: assertTrue("$A$3+$A$2", ("$A$3+$A$2")
0665: .equals(c.getCellFormula()));
0666: c = r.getCell((short) 4);
0667: assertTrue("SUM($A$3,$A$2)", ("SUM($A$3,$A$2)").equals(c
0668: .getCellFormula()));
0669: in.close();
0670: }
0671:
0672: public void testSheetFunctions() throws IOException {
0673: String filename = System.getProperty("HSSF.testdata.path");
0674:
0675: File file = TempFile.createTempFile("testSheetFormula", ".xls");
0676: FileOutputStream out = new FileOutputStream(file);
0677: HSSFWorkbook wb = new HSSFWorkbook();
0678: HSSFSheet s = wb.createSheet("A");
0679: HSSFRow r = null;
0680: HSSFCell c = null;
0681: r = s.createRow((short) 0);
0682: c = r.createCell((short) 0);
0683: c.setCellValue(1);
0684: c = r.createCell((short) 1);
0685: c.setCellValue(2);
0686:
0687: s = wb.createSheet("B");
0688: r = s.createRow((short) 0);
0689: c = r.createCell((short) 0);
0690: c.setCellFormula("AVERAGE(A!A1:B1)");
0691: c = r.createCell((short) 1);
0692: c.setCellFormula("A!A1+A!B1");
0693: c = r.createCell((short) 2);
0694: c.setCellFormula("A!$A$1+A!$B1");
0695: wb.write(out);
0696: out.close();
0697:
0698: assertTrue("file exists", file.exists());
0699:
0700: FileInputStream in = new FileInputStream(file);
0701: wb = new HSSFWorkbook(in);
0702: s = wb.getSheet("B");
0703: r = s.getRow(0);
0704: c = r.getCell((short) 0);
0705: assertTrue("expected: AVERAGE(A!A1:B1) got: "
0706: + c.getCellFormula(), ("AVERAGE(A!A1:B1)").equals(c
0707: .getCellFormula()));
0708: c = r.getCell((short) 1);
0709: assertTrue("expected: A!A1+A!B1 got: " + c.getCellFormula(),
0710: ("A!A1+A!B1").equals(c.getCellFormula()));
0711: in.close();
0712: }
0713:
0714: public void testRVAoperands() throws Exception {
0715: File file = TempFile.createTempFile("testFormulaRVA", ".xls");
0716: FileOutputStream out = new FileOutputStream(file);
0717: HSSFWorkbook wb = new HSSFWorkbook();
0718: HSSFSheet s = wb.createSheet();
0719: HSSFRow r = null;
0720: HSSFCell c = null;
0721:
0722: r = s.createRow((short) 0);
0723:
0724: c = r.createCell((short) 0);
0725: c.setCellFormula("A3+A2");
0726: c = r.createCell((short) 1);
0727: c.setCellFormula("AVERAGE(A3,A2)");
0728: c = r.createCell((short) 2);
0729: c.setCellFormula("ROW(A3)");
0730: c = r.createCell((short) 3);
0731: c.setCellFormula("AVERAGE(A2:A3)");
0732: c = r.createCell((short) 4);
0733: c.setCellFormula("POWER(A2,A3)");
0734: c = r.createCell((short) 5);
0735: c.setCellFormula("SIN(A2)");
0736:
0737: c = r.createCell((short) 6);
0738: c.setCellFormula("SUM(A2:A3)");
0739:
0740: c = r.createCell((short) 7);
0741: c.setCellFormula("SUM(A2,A3)");
0742:
0743: r = s.createRow((short) 1);
0744: c = r.createCell((short) 0);
0745: c.setCellValue(2.0);
0746: r = s.createRow((short) 2);
0747: c = r.createCell((short) 0);
0748: c.setCellValue(3.0);
0749:
0750: wb.write(out);
0751: out.close();
0752: assertTrue("file exists", file.exists());
0753: }
0754:
0755: public void testStringFormulas() throws IOException {
0756: String readFilename = System.getProperty("HSSF.testdata.path");
0757:
0758: File file = TempFile
0759: .createTempFile("testStringFormula", ".xls");
0760: FileOutputStream out = new FileOutputStream(file);
0761: HSSFWorkbook wb = new HSSFWorkbook();
0762: HSSFSheet s = wb.createSheet("A");
0763: HSSFRow r = null;
0764: HSSFCell c = null;
0765: r = s.createRow((short) 0);
0766: c = r.createCell((short) 1);
0767: c.setCellFormula("UPPER(\"abc\")");
0768: c = r.createCell((short) 2);
0769: c.setCellFormula("LOWER(\"ABC\")");
0770: c = r.createCell((short) 3);
0771: c.setCellFormula("CONCATENATE(\" my \",\" name \")");
0772:
0773: wb.write(out);
0774: out.close();
0775:
0776: assertTrue("file exists", file.exists());
0777:
0778: FileInputStream in = new FileInputStream(readFilename
0779: + File.separator + "StringFormulas.xls");
0780: wb = new HSSFWorkbook(in);
0781: s = wb.getSheetAt(0);
0782: r = s.getRow(0);
0783: c = r.getCell((short) 0);
0784: assertTrue(
0785: "expected: UPPER(\"xyz\") got " + c.getCellFormula(),
0786: ("UPPER(\"xyz\")").equals(c.getCellFormula()));
0787: //c = r.getCell((short)1);
0788: //assertTrue("expected: A!A1+A!B1 got: "+c.getCellFormula(), ("A!A1+A!B1").equals(c.getCellFormula()));
0789: in.close();
0790: }
0791:
0792: public void testLogicalFormulas() throws IOException {
0793:
0794: File file = TempFile.createTempFile("testLogicalFormula",
0795: ".xls");
0796: FileOutputStream out = new FileOutputStream(file);
0797: HSSFWorkbook wb = new HSSFWorkbook();
0798: HSSFSheet s = wb.createSheet("A");
0799: HSSFRow r = null;
0800: HSSFCell c = null;
0801: r = s.createRow((short) 0);
0802: c = r.createCell((short) 1);
0803: c.setCellFormula("IF(A1<A2,B1,B2)");
0804:
0805: wb.write(out);
0806: out.close();
0807:
0808: assertTrue("file exists", file.exists());
0809:
0810: FileInputStream in = new FileInputStream(file);
0811: wb = new HSSFWorkbook(in);
0812: s = wb.getSheetAt(0);
0813: r = s.getRow(0);
0814: c = r.getCell((short) 1);
0815: assertEquals("Formula in cell 1 ", "IF(A1<A2,B1,B2)", c
0816: .getCellFormula());
0817: in.close();
0818: }
0819:
0820: public void testDateFormulas() throws IOException {
0821: String readFilename = System.getProperty("HSSF.testdata.path");
0822:
0823: File file = TempFile.createTempFile("testDateFormula", ".xls");
0824: FileOutputStream out = new FileOutputStream(file);
0825: HSSFWorkbook wb = new HSSFWorkbook();
0826: HSSFSheet s = wb.createSheet("testSheet1");
0827: HSSFRow r = null;
0828: HSSFCell c = null;
0829:
0830: r = s.createRow((short) 0);
0831: c = r.createCell((short) 0);
0832:
0833: HSSFCellStyle cellStyle = wb.createCellStyle();
0834: cellStyle.setDataFormat(HSSFDataFormat
0835: .getBuiltinFormat("m/d/yy h:mm"));
0836: c.setCellValue(new Date());
0837: c.setCellStyle(cellStyle);
0838:
0839: // assertEquals("Checking hour = " + hour, date.getTime().getTime(),
0840: // HSSFDateUtil.getJavaDate(excelDate).getTime());
0841:
0842: for (int k = 1; k < 100; k++) {
0843: r = s.createRow((short) k);
0844: c = r.createCell((short) 0);
0845: c.setCellFormula("A" + (k) + "+1");
0846: c.setCellStyle(cellStyle);
0847: }
0848:
0849: wb.write(out);
0850: out.close();
0851:
0852: assertTrue("file exists", file.exists());
0853:
0854: }
0855:
0856: public void testIfFormulas() throws IOException {
0857: String readFilename = System.getProperty("HSSF.testdata.path");
0858:
0859: File file = TempFile.createTempFile("testIfFormula", ".xls");
0860: FileOutputStream out = new FileOutputStream(file);
0861: HSSFWorkbook wb = new HSSFWorkbook();
0862: HSSFSheet s = wb.createSheet("testSheet1");
0863: HSSFRow r = null;
0864: HSSFCell c = null;
0865: r = s.createRow((short) 0);
0866: c = r.createCell((short) 1);
0867: c.setCellValue(1);
0868: c = r.createCell((short) 2);
0869: c.setCellValue(2);
0870: c = r.createCell((short) 3);
0871: c.setCellFormula("MAX(A1:B1)");
0872: c = r.createCell((short) 4);
0873: c.setCellFormula("IF(A1=D1,\"A1\",\"B1\")");
0874:
0875: wb.write(out);
0876: out.close();
0877:
0878: assertTrue("file exists", file.exists());
0879:
0880: FileInputStream in = new FileInputStream(file);
0881: wb = new HSSFWorkbook(in);
0882: s = wb.getSheetAt(0);
0883: r = s.getRow(0);
0884: c = r.getCell((short) 4);
0885:
0886: assertTrue("expected: IF(A1=D1,\"A1\",\"B1\") got "
0887: + c.getCellFormula(), ("IF(A1=D1,\"A1\",\"B1\")")
0888: .equals(c.getCellFormula()));
0889: in.close();
0890:
0891: in = new FileInputStream(readFilename + File.separator
0892: + "IfFormulaTest.xls");
0893: wb = new HSSFWorkbook(in);
0894: s = wb.getSheetAt(0);
0895: r = s.getRow(3);
0896: c = r.getCell((short) 0);
0897: assertTrue("expected: IF(A3=A1,\"A1\",\"A2\") got "
0898: + c.getCellFormula(), ("IF(A3=A1,\"A1\",\"A2\")")
0899: .equals(c.getCellFormula()));
0900: //c = r.getCell((short)1);
0901: //assertTrue("expected: A!A1+A!B1 got: "+c.getCellFormula(), ("A!A1+A!B1").equals(c.getCellFormula()));
0902: in.close();
0903:
0904: File simpleIf = TempFile.createTempFile(
0905: "testSimpleIfFormulaWrite", ".xls");
0906: out = new FileOutputStream(simpleIf);
0907: wb = new HSSFWorkbook();
0908: s = wb.createSheet("testSheet1");
0909: r = null;
0910: c = null;
0911: r = s.createRow((short) 0);
0912: c = r.createCell((short) 0);
0913: c.setCellFormula("IF(1=1,0,1)");
0914:
0915: wb.write(out);
0916: out.close();
0917: assertTrue("file exists", simpleIf.exists());
0918:
0919: assertTrue("length of simpleIf file is zero", (simpleIf
0920: .length() > 0));
0921:
0922: File nestedIf = TempFile.createTempFile("testNestedIfFormula",
0923: ".xls");
0924: out = new FileOutputStream(nestedIf);
0925: wb = new HSSFWorkbook();
0926: s = wb.createSheet("testSheet1");
0927: r = null;
0928: c = null;
0929: r = s.createRow((short) 0);
0930: c = r.createCell((short) 0);
0931: c.setCellValue(1);
0932:
0933: c = r.createCell((short) 1);
0934: c.setCellValue(3);
0935:
0936: HSSFCell formulaCell = r.createCell((short) 3);
0937:
0938: r = s.createRow((short) 1);
0939: c = r.createCell((short) 0);
0940: c.setCellValue(3);
0941:
0942: c = r.createCell((short) 1);
0943: c.setCellValue(7);
0944:
0945: formulaCell
0946: .setCellFormula("IF(A1=B1,AVERAGE(A1:B1),AVERAGE(A2:B2))");
0947:
0948: wb.write(out);
0949: out.close();
0950: assertTrue("file exists", nestedIf.exists());
0951:
0952: assertTrue("length of nestedIf file is zero", (nestedIf
0953: .length() > 0));
0954: }
0955:
0956: public void testSumIf() throws IOException {
0957: String readFilename = System.getProperty("HSSF.testdata.path");
0958: String function = "SUMIF(A1:A5,\">4000\",B1:B5)";
0959:
0960: File inFile = new File(readFilename + "/sumifformula.xls");
0961: FileInputStream in = new FileInputStream(inFile);
0962: HSSFWorkbook wb = new HSSFWorkbook(in);
0963: in.close();
0964:
0965: HSSFSheet s = wb.getSheetAt(0);
0966: HSSFRow r = s.getRow(0);
0967: HSSFCell c = r.getCell((short) 2);
0968: assertEquals(function, c.getCellFormula());
0969:
0970: File file = TempFile.createTempFile("testSumIfFormula", ".xls");
0971: FileOutputStream out = new FileOutputStream(file);
0972: wb = new HSSFWorkbook();
0973: s = wb.createSheet();
0974:
0975: r = s.createRow((short) 0);
0976: c = r.createCell((short) 0);
0977: c.setCellValue((double) 1000);
0978: c = r.createCell((short) 1);
0979: c.setCellValue((double) 1);
0980:
0981: r = s.createRow((short) 1);
0982: c = r.createCell((short) 0);
0983: c.setCellValue((double) 2000);
0984: c = r.createCell((short) 1);
0985: c.setCellValue((double) 2);
0986:
0987: r = s.createRow((short) 2);
0988: c = r.createCell((short) 0);
0989: c.setCellValue((double) 3000);
0990: c = r.createCell((short) 1);
0991: c.setCellValue((double) 3);
0992:
0993: r = s.createRow((short) 3);
0994: c = r.createCell((short) 0);
0995: c.setCellValue((double) 4000);
0996: c = r.createCell((short) 1);
0997: c.setCellValue((double) 4);
0998:
0999: r = s.createRow((short) 4);
1000: c = r.createCell((short) 0);
1001: c.setCellValue((double) 5000);
1002: c = r.createCell((short) 1);
1003: c.setCellValue((double) 5);
1004:
1005: r = s.getRow(0);
1006: c = r.createCell((short) 2);
1007: c.setCellFormula(function);
1008:
1009: wb.write(out);
1010: out.close();
1011:
1012: assertTrue("sumif file doesnt exists", (file.exists()));
1013: assertTrue("sumif == 0 bytes", file.length() > 0);
1014: }
1015:
1016: public void testSquareMacro() throws IOException {
1017: File dir = new File(System.getProperty("HSSF.testdata.path"));
1018: File xls = new File(dir, "SquareMacro.xls");
1019: FileInputStream in = new FileInputStream(xls);
1020: HSSFWorkbook w;
1021: try {
1022: w = new HSSFWorkbook(in);
1023: } finally {
1024: in.close();
1025: }
1026: HSSFSheet s0 = w.getSheetAt(0);
1027: HSSFRow[] r = { s0.getRow(0), s0.getRow(1) };
1028:
1029: HSSFCell a1 = r[0].getCell((short) 0);
1030: assertEquals("square(1)", a1.getCellFormula());
1031: assertEquals(1d, a1.getNumericCellValue(), 1e-9);
1032:
1033: HSSFCell a2 = r[1].getCell((short) 0);
1034: assertEquals("square(2)", a2.getCellFormula());
1035: assertEquals(4d, a2.getNumericCellValue(), 1e-9);
1036:
1037: HSSFCell b1 = r[0].getCell((short) 1);
1038: assertEquals("IF(TRUE,square(1))", b1.getCellFormula());
1039: assertEquals(1d, b1.getNumericCellValue(), 1e-9);
1040:
1041: HSSFCell b2 = r[1].getCell((short) 1);
1042: assertEquals("IF(TRUE,square(2))", b2.getCellFormula());
1043: assertEquals(4d, b2.getNumericCellValue(), 1e-9);
1044:
1045: HSSFCell c1 = r[0].getCell((short) 2);
1046: assertEquals("square(square(1))", c1.getCellFormula());
1047: assertEquals(1d, c1.getNumericCellValue(), 1e-9);
1048:
1049: HSSFCell c2 = r[1].getCell((short) 2);
1050: assertEquals("square(square(2))", c2.getCellFormula());
1051: assertEquals(16d, c2.getNumericCellValue(), 1e-9);
1052:
1053: HSSFCell d1 = r[0].getCell((short) 3);
1054: assertEquals("square(one())", d1.getCellFormula());
1055: assertEquals(1d, d1.getNumericCellValue(), 1e-9);
1056:
1057: HSSFCell d2 = r[1].getCell((short) 3);
1058: assertEquals("square(two())", d2.getCellFormula());
1059: assertEquals(4d, d2.getNumericCellValue(), 1e-9);
1060: }
1061:
1062: public void testStringFormulaRead() throws IOException {
1063: File dir = new File(System.getProperty("HSSF.testdata.path"));
1064: File xls = new File(dir, "StringFormulas.xls");
1065: FileInputStream in = new FileInputStream(xls);
1066: HSSFWorkbook w;
1067: try {
1068: w = new HSSFWorkbook(in);
1069: } finally {
1070: in.close();
1071: }
1072: HSSFCell c = w.getSheetAt(0).getRow(0).getCell((short) 0);
1073: assertEquals("String Cell value", "XYZ", c.getStringCellValue());
1074: }
1075:
1076: /** test for bug 34021*/
1077: public void testComplexSheetRefs() throws IOException {
1078: HSSFWorkbook sb = new HSSFWorkbook();
1079: HSSFSheet s1 = sb.createSheet("Sheet a.1");
1080: HSSFSheet s2 = sb.createSheet("Sheet.A");
1081: s2.createRow(1).createCell((short) 2).setCellFormula(
1082: "'Sheet a.1'!A1");
1083: s1.createRow(1).createCell((short) 2).setCellFormula(
1084: "'Sheet.A'!A1");
1085: File file = TempFile.createTempFile("testComplexSheetRefs",
1086: ".xls");
1087: sb.write(new FileOutputStream(file));
1088: }
1089:
1090: /*Unknown Ptg 3C*/
1091: public void test27272_1() throws Exception {
1092: String readFilename = System.getProperty("HSSF.testdata.path");
1093: File inFile = new File(readFilename + "/27272_1.xls");
1094: FileInputStream in = new FileInputStream(inFile);
1095: HSSFWorkbook wb = new HSSFWorkbook(in);
1096: wb.getSheetAt(0);
1097: assertEquals("Reference for named range ", "#REF!", wb
1098: .getNameAt(0).getReference());
1099: File outF = File.createTempFile("bug27272_1", ".xls");
1100: wb.write(new FileOutputStream(outF));
1101: System.out.println("Open " + outF.getAbsolutePath()
1102: + " in Excel");
1103: }
1104:
1105: /*Unknown Ptg 3D*/
1106: public void test27272_2() throws Exception {
1107: String readFilename = System.getProperty("HSSF.testdata.path");
1108: File inFile = new File(readFilename + "/27272_2.xls");
1109: FileInputStream in = new FileInputStream(inFile);
1110: HSSFWorkbook wb = new HSSFWorkbook(in);
1111: assertEquals("Reference for named range ", "#REF!", wb
1112: .getNameAt(0).getReference());
1113: File outF = File.createTempFile("bug27272_2", ".xls");
1114: wb.write(new FileOutputStream(outF));
1115: System.out.println("Open " + outF.getAbsolutePath()
1116: + " in Excel");
1117: }
1118:
1119: /* MissingArgPtg */
1120: public void testMissingArgPtg() throws Exception {
1121: HSSFWorkbook wb = new HSSFWorkbook();
1122: HSSFCell cell = wb.createSheet("Sheet1").createRow(4)
1123: .createCell((short) 0);
1124: cell.setCellFormula("IF(A1=\"A\",1,)");
1125: }
1126:
1127: public static void main(String[] args) {
1128: System.out
1129: .println("Testing org.apache.poi.hssf.usermodel.TestFormulas");
1130: junit.textui.TestRunner.run(TestFormulas.class);
1131: }
1132:
1133: }
|