testformulas.java

来自「EXCEL read and write」· Java 代码 · 共 888 行 · 第 1/2 页

    }

    /**
     * Writes a function then tests to see if its correct
     */
    public static void areaFunctionTest(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:A3)");

        wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
        s = wb.getSheetAt(0);
        r = s.getRow(0);
        c = r.getCell(0);

        assertTrue("function ="+function+"(A2:A3)",
                    ( (function+"(A2:A3)").equals((function+"(A2:A3)")) )
                  );
    }

    /**
     * Writes a function then tests to see if its correct
     */
    public void refArrayFunctionTest(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,A3)");

        wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
        s = wb.getSheetAt(0);
        r = s.getRow(0);
        c = r.getCell(0);

        assertTrue("function ="+function+"(A2,A3)",
                    ( (function+"(A2,A3)").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()));
    }

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

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

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

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

    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 testSquareMacro() {
        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 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 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 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 d2 = r[1].getCell(3);
        assertEquals("square(two())", d2.getCellFormula());
        assertEquals(4d, d2.getNumericCellValue(), 1e-9);
    }

    public void testStringFormulaRead() {
        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*/
    public void testComplexSheetRefs () throws IOException {
         HSSFWorkbook sb = new HSSFWorkbook();
         HSSFSheet s1 = sb.createSheet("Sheet a.1");
         HSSFSheet s2 = sb.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");
         sb.write(new FileOutputStream(file));
    }

    /** Unknown Ptg 3C*/
    public void test27272_1() throws Exception {
        HSSFWorkbook wb = openSample("27272_1.xls");
        wb.getSheetAt(0);
        assertEquals("Reference for named range ", "Compliance!#REF!",wb.getNameAt(0).getReference());
        File outF = File.createTempFile("bug27272_1",".xls");
        wb.write(new FileOutputStream(outF));
        System.out.println("Open "+outF.getAbsolutePath()+" in Excel");
    }
    /** Unknown Ptg 3D*/
    public void test27272_2() throws Exception {
        HSSFWorkbook wb = openSample("27272_2.xls");
        assertEquals("Reference for named range ", "LOAD.POD_HISTORIES!#REF!",wb.getNameAt(0).getReference());
        File outF = File.createTempFile("bug27272_2",".xls");
        wb.write(new FileOutputStream(outF));
        System.out.println("Open "+outF.getAbsolutePath()+" in Excel");
    }

    /** MissingArgPtg */
    public void testMissingArgPtg() throws Exception {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFCell cell = wb.createSheet("Sheet1").createRow(4).createCell(0);
        cell.setCellFormula("IF(A1=\"A\",1,)");
    }

    public void testSharedFormula() {
        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());
    }
}