xlstransformertest.java

一个java生成自动生成Excel

        beanList.add(simpleBean2);
        beanList.add(simpleBean3);

        listBean1.addBean( simpleBean1 );
        listBean1.addBean( simpleBean2 );
        listBean1.addBean( simpleBean3 );

        Department department = new Department("IT");
        for(int i = 0; i < itEmployeeNames.length; i++){
            Employee employee = new Employee(itEmployeeNames[i], itAges[i], itPayments[i], itBonuses[i]);
            employee.setNotes( generateNotes(employee.getName()) );
            department.addEmployee( employee );
            itEmployees.add( employee );
        }
        itDepartment = department;
        departments.add( department );
        department = new Department("HR");
        for(int i = 0; i < hrEmployeeNames.length; i++){
            department.addEmployee( new Employee(hrEmployeeNames[i], hrAges[i], hrPayments[i], hrBonuses[i]) );
        }
        departments.add( department );
        department = new Department("BA");
        for(int i = 0; i < baEmployeeNames.length; i++){
            department.addEmployee( new Employee(baEmployeeNames[i], baAges[i], baPayments[i], baBonuses[i]) );
        }
        departments.add( department );

        department = new Department("MGR");
        for(int i = 0; i < mgrEmployeeNames.length; i++){
            department.addEmployee( new Employee(mgrEmployeeNames[i], mgrAges[i], mgrPayments[i], mgrBonuses[i]) );
        }
        mgrDepartment = department;


        beanWithList.setBeans(beanList);

        propertyMap.put("${bean.name}", simpleBean1.getName());
        propertyMap.put("${bean.doubleValue}", simpleBean1.getDoubleValue());
        propertyMap.put("${bean.intValue}", simpleBean1.getIntValue());
        propertyMap.put("${bean.dateValue}", simpleBean1.getDateValue());
        propertyMap.put("${bean.other.name}", simpleBean1.getOther().getName());
        propertyMap.put("${bean.other.intValue}", simpleBean1.getOther().getIntValue());
        propertyMap.put("${bean.other.doubleValue}", simpleBean1.getOther().getDoubleValue());
        propertyMap.put("${bean.other.dateValue}", simpleBean1.getOther().getDateValue());

        propertyMap.put("${listBean.name}", beanWithList.getName());
//        propertyMap.put("${listBean.beans.name}", beanWithList.getBeans());

        for (int i = 0; i < amounts.length; i++) {
            int amount = amounts[i];
            amountBeans.add( new SimpleBean( amount ) );
        }

    }

    protected List generateNotes(String name) {
        Random r = new Random( System.currentTimeMillis() );
        int n = 1 + r.nextInt(7);
        List notes = new ArrayList();
        for(int i = 0 ; i < n; i++){
            notes.add("Note " + i + " for " + name);
        }
        return notes;
    }

    Map propertyMap = new HashMap();

    public void testSimpleBeanExport() throws IOException, ParsePropertyException {
        Map beans = new HashMap();
        beans.put("bean", simpleBean1);
        Calendar calendar = Calendar.getInstance();
        calendar.set(2006, 8, 19);
        beans.put("calendar", calendar);
        InputStream is = new BufferedInputStream(getClass().getResourceAsStream(simpleBeanXLS));
        XLSTransformer transformer = new XLSTransformer();
        HSSFWorkbook resultWorkbook = transformer.transformXLS(is, beans);
        is.close();
        is = new BufferedInputStream(getClass().getResourceAsStream(simpleBeanXLS));
        POIFSFileSystem fs = new POIFSFileSystem(is);
        HSSFWorkbook sourceWorkbook = new HSSFWorkbook(fs);

        HSSFSheet sourceSheet = sourceWorkbook.getSheetAt(0);
        HSSFSheet resultSheet = resultWorkbook.getSheetAt(0);
        assertEquals("First Row Numbers differ in source and result sheets", sourceSheet.getFirstRowNum(), resultSheet.getFirstRowNum());
        assertEquals("Last Row Numbers differ in source and result sheets", sourceSheet.getLastRowNum(), resultSheet.getLastRowNum());
        CellsChecker checker = new CellsChecker(propertyMap);
        propertyMap.put("${calendar}", calendar);
        checker.checkRows(sourceSheet, resultSheet, 0, 0, 6);

        is.close();
        saveWorkbook(resultWorkbook, simpeBeanDestXLS);
    }

    public void testBeanWithListExport() throws IOException, ParsePropertyException {
        Map beans = new HashMap();
        beans.put("listBean", beanWithList);
        beans.put("beans", beanWithList.getBeans());
        InputStream is = new BufferedInputStream(getClass().getResourceAsStream(beanWithListXLS));
        XLSTransformer transformer = new XLSTransformer();
        HSSFWorkbook resultWorkbook = transformer.transformXLS(is, beans);
        is.close();
        is = new BufferedInputStream(getClass().getResourceAsStream(beanWithListXLS));
        POIFSFileSystem fs = new POIFSFileSystem(is);
        HSSFWorkbook sourceWorkbook = new HSSFWorkbook(fs);

        HSSFSheet sourceSheet = sourceWorkbook.getSheetAt(0);
        HSSFSheet resultSheet = resultWorkbook.getSheetAt(0);
        assertEquals("First Row Numbers differ in source and result sheets", sourceSheet.getFirstRowNum(), resultSheet.getFirstRowNum());
        assertEquals("Last Row Number is incorrect", sourceSheet.getLastRowNum() + beanWithList.getBeans().size() - 1, resultSheet.getLastRowNum());

        Map listPropMap = new HashMap();
        listPropMap.put("${listBean.name}", beanWithList.getName());
        CellsChecker checker = new CellsChecker(listPropMap);
        checker.checkRows(sourceSheet, resultSheet, 0, 0, 3);
        checker.checkListCells(sourceSheet, 3, resultSheet, 3, (short) 0, names);
        checker.checkListCells(sourceSheet, 3, resultSheet, 3, (short) 1, doubleValues);
        checker.checkListCells(sourceSheet, 3, resultSheet, 3, (short) 2, new Object[]{new Integer(123), new Integer(10234), null});
        checker.checkListCells(sourceSheet, 3, resultSheet, 3, (short) 3, dateValues);
        is.close();
        saveWorkbook(resultWorkbook, beanWithListDestXLS);
    }

    public void testFormulas2() throws IOException {
        Map beans = new HashMap();
        beans.put("departments", departments);

        InputStream is = new BufferedInputStream(getClass().getResourceAsStream(formulas2XLS));
        XLSTransformer transformer = new XLSTransformer();
        HSSFWorkbook resultWorkbook = transformer.transformXLS(is, beans);
        is.close();

        saveWorkbook(resultWorkbook, formulasDestXLS);

    }

    public void testFormulas() throws IOException, ParsePropertyException {
        Map beans = new HashMap();
        beans.put("listBean", beanWithList);
        beans.put("departments", departments);
        beans.put( "t1", amountBeans );
        //todo comment this line to work on #VALUE! formula cell problem
//        simpleBean3.setOther( simpleBean1 );

        InputStream is = new BufferedInputStream(getClass().getResourceAsStream(formulasXLS));
        XLSTransformer transformer = new XLSTransformer();
        HSSFWorkbook resultWorkbook = transformer.transformXLS(is, beans);
        is.close();
        is = new BufferedInputStream(getClass().getResourceAsStream(formulasXLS));
        POIFSFileSystem fs = new POIFSFileSystem(is);
        HSSFWorkbook sourceWorkbook = new HSSFWorkbook(fs);

        HSSFSheet sourceSheet = sourceWorkbook.getSheetAt(0);
        HSSFSheet resultSheet = resultWorkbook.getSheetAt(0);
        assertEquals("First Row Numbers differ in source and result sheets", sourceSheet.getFirstRowNum(), resultSheet.getFirstRowNum());
//        assertEquals("Last Row Number is incorrect", sourceSheet.getLastRowNum() + beanWithList.getBeans().size() - 1, resultSheet.getLastRowNum());

        Map props = new HashMap();
        props.put("${listBean.name}", beanWithList.getName());
        CellsChecker checker = new CellsChecker(props);
        checker.checkRows(sourceSheet, resultSheet, 0, 0, 3);
        checker.checkListCells(sourceSheet, 3, resultSheet, 3, (short) 0, names);
        checker.checkListCells(sourceSheet, 3, resultSheet, 3, (short) 1, doubleValues);
        checker.checkListCells(sourceSheet, 3, resultSheet, 3, (short) 2, new Object[]{new Integer(123), new Integer(10234)});
        checker.checkListCells(sourceSheet, 3, resultSheet, 3, (short) 3, dateValues);
        checker.checkFormulaCell(sourceSheet, 4, resultSheet, 6, (short) 1, "SUM(B4:B6)");
        checker.checkFormulaCell(sourceSheet, 4, resultSheet, 6, (short) 2, "SUM(C4:C6)");
        checker.checkFormulaCell(sourceSheet, 6, resultSheet, 8, (short) 1, "MAX(B7,C7)");

        checker.checkFormulaCell(sourceSheet, 3, resultSheet, 3, (short) 4, "B4+C4");
        checker.checkFormulaCell(sourceSheet, 3, resultSheet, 4, (short) 4, "B5+C5");
        checker.checkFormulaCell(sourceSheet, 3, resultSheet, 5, (short) 4, "B6+C6");

        checker.checkFormulaCell(sourceSheet, 4, resultSheet, 6, (short) 4, "SUM(E4:E6)");

        checker.checkFormulaCell( sourceSheet, 8, resultSheet, 10, (short) 1, "SUM(B4:B6)");
        checker.checkFormulaCell( sourceSheet, 8, resultSheet, 10, (short) 2, "SUM(C4:C6)");
        checker.checkFormulaCell( sourceSheet, 8, resultSheet, 10, (short) 4, "SUM(E4:E6)");
        checker.checkFormulaCell(sourceSheet, 10, resultSheet, 12, (short) 1, "MAX(B7,C7)");

        checker.checkFormulaCell( sourceSheet, 20, resultSheet, 23, (short)1, "SUM(B19:B23)");
        checker.checkFormulaCell( sourceSheet, 20, resultSheet, 32, (short)1, "SUM(B29:B32)");
        checker.checkFormulaCell( sourceSheet, 20, resultSheet, 40, (short)1, "SUM(B38:B40)");

        checker.checkFormulaCell( sourceSheet, 20, resultSheet, 23, (short)3, "SUM(D19:D23)");
        checker.checkFormulaCell( sourceSheet, 20, resultSheet, 32, (short)3, "SUM(D29:D32)");
        checker.checkFormulaCell( sourceSheet, 20, resultSheet, 40, (short)3, "SUM(D38:D40)");

        checker.checkFormulaCell( sourceSheet, 22, resultSheet, 41, (short)1, "SUM(B24,B33,B41)");
        checker.checkFormulaCell( sourceSheet, 22, resultSheet, 41, (short)3, "SUM(D24,D33,D41)");

        checker.checkFormulaCell( sourceSheet, 18, resultSheet, 18, (short)3, "B19*(1+C19)");
        checker.checkFormulaCell( sourceSheet, 18, resultSheet, 22, (short)3, "B23*(1+C23)");
        checker.checkFormulaCell( sourceSheet, 18, resultSheet, 28, (short)3, "B29*(1+C29)");
        checker.checkFormulaCell( sourceSheet, 19, resultSheet, 31, (short)3, "B32*(1+C32)");
        checker.checkFormulaCell( sourceSheet, 19, resultSheet, 37, (short)3, "B38*(1+C38)");
        checker.checkFormulaCell( sourceSheet, 19, resultSheet, 39, (short)3, "B40*(1+C40)");

        checker.checkFormulaCell( sourceSheet, 24, resultSheet, 43, (short)1, "'Sheet 2'!B55");

        sourceSheet = sourceWorkbook.getSheetAt( 1 );
        resultSheet = resultWorkbook.getSheetAt( 1 );
        checker.checkFormulaCell( sourceSheet, 0, resultSheet, 0, (short)1, "SUM(Sheet1!B4:B6)");
        checker.checkFormulaCell( sourceSheet, 0, resultSheet, 0, (short)2, "SUM(Sheet1!C4:C6)");
        checker.checkFormulaCell( sourceSheet, 0, resultSheet, 0, (short)4, "SUM(Sheet1!E4:E6)");
        checker.checkFormulaCell( sourceSheet, 2, resultSheet, 2, (short)1, "MAX(Sheet1!B7,Sheet1!C7)");
        checker.checkFormulaCell( sourceSheet, 4, resultSheet, 4, (short)1, "Sheet1!B13");

        checker.checkFormulaCell( sourceSheet, 15, resultSheet, 24, (short)1, "SUM(B10,B13,B16,B19,B22)");
        checker.checkFormulaCell( sourceSheet, 15, resultSheet, 40, (short)1, "SUM(B29,B32,B35,B38)");
        checker.checkFormulaCell( sourceSheet, 15, resultSheet, 53, (short)1, "SUM(B45,B48,B51)");

        checker.checkFormulaCell( sourceSheet, 18, resultSheet, 55, (short)1, "Sheet1!D24");
        checker.checkFormulaCell( sourceSheet, 19, resultSheet, 56, (short)1, "Sheet1!D33");
        checker.checkFormulaCell( sourceSheet, 20, resultSheet, 57, (short)1, "Sheet1!D41");
        // todo Create checks for "Sheet 3"

        is.close();
        saveWorkbook(resultWorkbook, formulasDestXLS);
    }

    public void testMultipleListRows() throws IOException, ParsePropertyException {

        Map beans = new HashMap();
        beans.put("listBean", beanWithList);
        InputStream is = new BufferedInputStream(getClass().getResourceAsStream(multipleListRowsXLS));