testformulas.java

Office格式转换代码

            HSSFRow          r      = null;
            HSSFCell         c      = null;
            r = s.createRow((short)0);
            c = r.createCell((short)0);c.setCellValue(1);
            c = r.createCell((short)1);c.setCellValue(2);
            
            s      = wb.createSheet("B");
            r = s.createRow((short)0);
            c=r.createCell((short)0); c.setCellFormula("AVERAGE(A!A1:B1)");
            c=r.createCell((short)1); c.setCellFormula("A!A1+A!B1");
            c=r.createCell((short)2); c.setCellFormula("A!$A$1+A!$B1");
            wb.write(out);
            out.close();
            
             assertTrue("file exists",file.exists());
            
            FileInputStream in = new FileInputStream(file);
            wb = new HSSFWorkbook(in);
            s = wb.getSheet("B");
            r = s.getRow(0);
            c = r.getCell((short)0);
            assertTrue("expected: AVERAGE(A!A1:B1) got: "+c.getCellFormula(), ("AVERAGE(A!A1:B1)").equals(c.getCellFormula()));
            c = r.getCell((short)1);
            assertTrue("expected: A!A1+A!B1 got: "+c.getCellFormula(), ("A!A1+A!B1").equals(c.getCellFormula()));
            in.close();
    }
    
    public void testRVAoperands() throws Exception {
         File file = File.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((short) 0);

            c = r.createCell((short) 0);
            c.setCellFormula("A3+A2");
            c=r.createCell( (short) 1);
            c.setCellFormula("AVERAGE(A3,A2)");
            c=r.createCell( (short) 2);
            c.setCellFormula("ROW(A3)");
            c=r.createCell( (short) 3);
            c.setCellFormula("AVERAGE(A2:A3)");
            c=r.createCell( (short) 4);
            c.setCellFormula("POWER(A2,A3)");
            c=r.createCell( (short) 5);
            c.setCellFormula("SIN(A2)");
            
            c=r.createCell( (short) 6);
            c.setCellFormula("SUM(A2:A3)");
            
            c=r.createCell( (short) 7);
            c.setCellFormula("SUM(A2,A3)");
            
            r = s.createRow((short) 1);c=r.createCell( (short) 0); c.setCellValue(2.0);
             r = s.createRow((short) 2);c=r.createCell( (short) 0); c.setCellValue(3.0);
            
            wb.write(out);
            out.close();
            assertTrue("file exists",file.exists());
    }
    
    public void testStringFormulas()
        throws IOException
    {
        String readFilename = System.getProperty("HSSF.testdata.path");

            File file = File.createTempFile("testStringFormula",".xls");
            FileOutputStream out    = new FileOutputStream(file);
            HSSFWorkbook     wb     = new HSSFWorkbook();
            HSSFSheet        s      = wb.createSheet("A");
            HSSFRow          r      = null;
            HSSFCell         c      = null;
            r = s.createRow((short)0);
            c=r.createCell((short)1); c.setCellFormula("UPPER(\"abc\")");
            c=r.createCell((short)2); c.setCellFormula("LOWER(\"ABC\")");
            c=r.createCell((short)3); c.setCellFormula("CONCATENATE(\" my \",\" name \")");
            
            wb.write(out);
            out.close();
            
             assertTrue("file exists",file.exists());
            
            FileInputStream in = new FileInputStream(readFilename+File.separator+"StringFormulas.xls");
            wb = new HSSFWorkbook(in);
            s = wb.getSheetAt(0);
            r = s.getRow(0);
            c = r.getCell((short)0);
            assertTrue("expected: UPPER(\"xyz\") got "+c.getCellFormula(), ("UPPER(\"xyz\")").equals(c.getCellFormula()));
            //c = r.getCell((short)1);
            //assertTrue("expected: A!A1+A!B1 got: "+c.getCellFormula(), ("A!A1+A!B1").equals(c.getCellFormula()));
            in.close(); 
    }
    
    
    
    public void testLogicalFormulas()
        throws IOException
    {

            File file = File.createTempFile("testLogicalFormula",".xls");
            FileOutputStream out    = new FileOutputStream(file);
            HSSFWorkbook     wb     = new HSSFWorkbook();
            HSSFSheet        s      = wb.createSheet("A");
            HSSFRow          r      = null;
            HSSFCell         c      = null;
            r = s.createRow((short)0);
            c=r.createCell((short)1); c.setCellFormula("IF(A1<A2,B1,B2)");

            
            wb.write(out);
            out.close();
            
             assertTrue("file exists",file.exists());
            
            FileInputStream in = new FileInputStream(file);
            wb = new HSSFWorkbook(in);
            s = wb.getSheetAt(0);
            r = s.getRow(0);
            c = r.getCell((short)1);
            assertEquals("Formula in cell 1 ","IF(A1<A2,B1,B2)",c.getCellFormula());
            in.close(); 
    }
    
    public void testDateFormulas()
        throws IOException
    {
        String readFilename = System.getProperty("HSSF.testdata.path");

            File file = File.createTempFile("testDateFormula",".xls");
            FileOutputStream out    = new FileOutputStream(file);
            HSSFWorkbook     wb     = new HSSFWorkbook();
            HSSFSheet        s      = wb.createSheet("Sheet1");
            HSSFRow          r      = null;
            HSSFCell         c      = null;

            r = s.createRow(  (short)0 );
            c = r.createCell( (short)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((short)k);
              c=r.createCell((short)0); 
              c.setCellFormula("A"+(k)+"+1");
              c.setCellStyle(cellStyle);
            }

            wb.write(out);
            out.close();
            
            assertTrue("file exists",file.exists());
            
    }

    
    public void testIfFormulas()
        throws IOException
    {
        String readFilename = System.getProperty("HSSF.testdata.path");

            File file = File.createTempFile("testIfFormula",".xls");
            FileOutputStream out    = new FileOutputStream(file);
            HSSFWorkbook     wb     = new HSSFWorkbook();
            HSSFSheet        s      = wb.createSheet("Sheet1");
            HSSFRow          r      = null;
            HSSFCell         c      = null;
            r = s.createRow((short)0);
            c=r.createCell((short)1); c.setCellValue(1);
            c=r.createCell((short)2); c.setCellValue(2);
            c=r.createCell((short)3); c.setCellFormula("MAX(A1:B1)");
            c=r.createCell((short)4); c.setCellFormula("IF(A1=D1,\"A1\",\"B1\")");
            
            wb.write(out);
            out.close();
            
            assertTrue("file exists",file.exists());
            
            FileInputStream in = new FileInputStream(file);
            wb = new HSSFWorkbook(in);
            s = wb.getSheetAt(0);
            r = s.getRow(0);
            c = r.getCell((short)4);
            
            assertTrue("expected: IF(A1=D1,\"A1\",\"B1\") got "+c.getCellFormula(), ("IF(A1=D1,\"A1\",\"B1\")").equals(c.getCellFormula()));            
            in.close();
            
            
            in = new FileInputStream(readFilename+File.separator+"IfFormulaTest.xls");
            wb = new HSSFWorkbook(in);
            s = wb.getSheetAt(0);
            r = s.getRow(3);
            c = r.getCell((short)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()));
            in.close();
            
		File simpleIf = File.createTempFile("testSimpleIfFormulaWrite",".xls");
		out    = new FileOutputStream(simpleIf);
		wb     = new HSSFWorkbook();
		s      = wb.createSheet("Sheet1");
		r      = null;
		c      = null;
		r = s.createRow((short)0);
		c=r.createCell((short)0); c.setCellFormula("IF(1=1,0,1)");
            
		wb.write(out);
		out.close();
		assertTrue("file exists", simpleIf.exists());
			
		assertTrue("length of simpleIf file is zero", (simpleIf.length()>0));
			
		File nestedIf = File.createTempFile("testNestedIfFormula",".xls");
		out    = new FileOutputStream(nestedIf);
		wb     = new HSSFWorkbook();
		s      = wb.createSheet("Sheet1");
		r      = null;
		c      = null;
		r = s.createRow((short)0);
		c=r.createCell((short)0);
		c.setCellValue(1);

		c=r.createCell((short)1);
		c.setCellValue(3);

			
		HSSFCell formulaCell=r.createCell((short)3); 

		r = s.createRow((short)1);
		c=r.createCell((short)0);
		c.setCellValue(3);

		c=r.createCell((short)1);
		c.setCellValue(7);

		formulaCell.setCellFormula("IF(A1=B1,AVERAGE(A1:B1),AVERAGE(A2:B2))");

            
		wb.write(out);
		out.close();
		assertTrue("file exists", nestedIf.exists());
			
		assertTrue("length of nestedIf file is zero", (nestedIf.length()>0));             
    }

	public void testSumIf()
		throws IOException
	{
		String readFilename = System.getProperty("HSSF.testdata.path");		
		String function ="SUMIF(A1:A5,\">4000\",B1:B5)";
		
		File inFile = new File(readFilename+"/sumifformula.xls");
		FileInputStream in = new FileInputStream(inFile);
		HSSFWorkbook wb = new HSSFWorkbook(in);
		in.close();
		
		HSSFSheet        s      = wb.getSheetAt(0);
		HSSFRow          r      = s.getRow(0);
		HSSFCell         c      = r.getCell((short)2);
		assertEquals(function, c.getCellFormula());
		

		File file = File.createTempFile("testSumIfFormula",".xls");
		FileOutputStream out    = new FileOutputStream(file);
		wb     = new HSSFWorkbook();
		s      = wb.createSheet();
		
		r = s.createRow((short)0);
		c=r.createCell((short)0); c.setCellValue((double)1000);
		c=r.createCell((short)1); c.setCellValue((double)1);
		
		
		r = s.createRow((short)1);
		c=r.createCell((short)0); c.setCellValue((double)2000);
		c=r.createCell((short)1); c.setCellValue((double)2);

		r = s.createRow((short)2);
		c=r.createCell((short)0); c.setCellValue((double)3000);
		c=r.createCell((short)1); c.setCellValue((double)3);

		r = s.createRow((short)3);
		c=r.createCell((short)0); c.setCellValue((double)4000);
		c=r.createCell((short)1); c.setCellValue((double)4);

		r = s.createRow((short)4);
		c=r.createCell((short)0); c.setCellValue((double)5000);
		c=r.createCell((short)1); c.setCellValue((double)5);
		
		r = s.getRow(0);
		c=r.createCell((short)2); c.setCellFormula(function);
			
		wb.write(out);				
		out.close();
		
		assertTrue("sumif file doesnt exists", (file.exists()));
		assertTrue("sumif == 0 bytes", file.length() > 0);
	}
    
    public void testSquareMacro() throws IOException {
        File dir = new File(System.getProperty("HSSF.testdata.path"));
        File xls = new File(dir, "SquareMacro.xls");
        FileInputStream in = new FileInputStream(xls);
        HSSFWorkbook w;
        try {
            w = new HSSFWorkbook(in);
        } finally {
            in.close();
        }
        HSSFSheet s0 = w.getSheetAt(0);
        HSSFRow[] r = {s0.getRow(0), s0.getRow(1)};

        HSSFCell a1 = r[0].getCell((short) 0);
        assertEquals("square(1)", a1.getCellFormula());
        assertEquals(1d, a1.getNumericCellValue(), 1e-9);

        HSSFCell a2 = r[1].getCell((short) 0);
        assertEquals("square(2)", a2.getCellFormula());
        assertEquals(4d, a2.getNumericCellValue(), 1e-9);

        HSSFCell b1 = r[0].getCell((short) 1);
        assertEquals("IF(TRUE,square(1))", b1.getCellFormula());
        assertEquals(1d, b1.getNumericCellValue(), 1e-9);

        HSSFCell b2 = r[1].getCell((short) 1);
        assertEquals("IF(TRUE,square(2))", b2.getCellFormula());
        assertEquals(4d, b2.getNumericCellValue(), 1e-9);

        HSSFCell c1 = r[0].getCell((short) 2);
        assertEquals("square(square(1))", c1.getCellFormula());
        assertEquals(1d, c1.getNumericCellValue(), 1e-9);

        HSSFCell c2 = r[1].getCell((short) 2);
        assertEquals("square(square(2))", c2.getCellFormula());
        assertEquals(16d, c2.getNumericCellValue(), 1e-9);

        HSSFCell d1 = r[0].getCell((short) 3);
        assertEquals("square(one())", d1.getCellFormula());
        assertEquals(1d, d1.getNumericCellValue(), 1e-9);

        HSSFCell d2 = r[1].getCell((short) 3);
        assertEquals("square(two())", d2.getCellFormula());
        assertEquals(4d, d2.getNumericCellValue(), 1e-9);
    }

    public void testStringFormulaRead() throws IOException {
        File dir = new File(System.getProperty("HSSF.testdata.path"));
        File xls = new File(dir, "StringFormulas.xls");
        FileInputStream in = new FileInputStream(xls);
        HSSFWorkbook w;
        try {
            w = new HSSFWorkbook(in);
        } finally {
            in.close();
        }
        HSSFCell c = w.getSheetAt(0).getRow(0).getCell((short)0);
        assertEquals("String Cell value","XYZ",c.getStringCellValue());
    }
    
    public static void main(String [] args) {
        System.out
        .println("Testing org.apache.poi.hssf.usermodel.TestFormulas");
        junit.textui.TestRunner.run(TestFormulas.class);
    }
    
}