xlstransformertest.java

一个java生成自动生成Excel

                        ((SimpleBean)beanWithList.getBeans().get(1)).getName() + " : " + ((SimpleBean)beanWithList.getBeans().get(1)).getDoubleValue() + "!",
                        ((SimpleBean)beanWithList.getBeans().get(2)).getName() + " : " + ((SimpleBean)beanWithList.getBeans().get(2)).getDoubleValue() + "!" });

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

    public void testParallelTablesExport() throws IOException, ParsePropertyException {
        Map beans = new HashMap();
        beans.put("listBean", beanWithList);
        beans.put("bean", simpleBean2);
        InputStream is = new BufferedInputStream(getClass().getResourceAsStream(parallelTablesXLS));
        XLSTransformer transformer = new XLSTransformer();
        HSSFWorkbook resultWorkbook = transformer.transformXLS(is, beans);
        is.close();
        is = new BufferedInputStream(getClass().getResourceAsStream(parallelTablesXLS));
        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", 11, resultSheet.getLastRowNum());

        Map listPropMap = new HashMap();
        listPropMap.put("${listBean.name}", beanWithList.getName());
        listPropMap.put("Name: ${bean.name}", "Name: " + simpleBean2.getName());
        listPropMap.put("${bean.doubleValue}", simpleBean2.getDoubleValue() );
        listPropMap.put("Merged - ${bean.intValue}", "Merged - " + simpleBean2.getIntValue() );
        listPropMap.put("${bean.intValue}", simpleBean2.getIntValue() );

        CellsChecker checker = new CellsChecker(listPropMap);
        checker.checkRows(sourceSheet, resultSheet, 0, 0, 3);

        checker.checkListCells(sourceSheet, 3, resultSheet, 3, (short) 2, names);
        checker.checkListCells(sourceSheet, 3, resultSheet, 3, (short) 3, doubleValues);
        checker.checkListCells(sourceSheet, 3, resultSheet, 3, (short) 5, intValues);

        checker.checkFormulaCell(sourceSheet, 3, resultSheet, 3, (short) 4, "D4+F4");
        checker.checkFormulaCell(sourceSheet, 4, resultSheet, 4, (short) 4, "D5+F5", true);
        checker.checkFormulaCell(sourceSheet, 5, resultSheet, 5, (short) 4, "D6+F6", true);

        checker.checkSection( sourceSheet, resultSheet, 0, 0, (short)0, (short)1, 7, true, true);
        checker.checkSection( sourceSheet, resultSheet, 0, 0, (short)6, (short)7, 14, true, true);

        assertEquals( "Incorrect number of merged regions", 2, resultSheet.getNumMergedRegions() );
        assertTrue( "Merged Region (4,0,4,1) not found", isMergedRegion( resultSheet, new Region(4, (short)0, 4, (short)1) ) );
        assertTrue( "Merged Region (3,6,3,7) not found", isMergedRegion( resultSheet, new Region(3, (short)6, 3, (short)7) ) );

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

    public void testSeveralListsInRowExport() throws IOException, ParsePropertyException {
        Map beans = new HashMap();
        beans.put("list1", listBean1);
        beans.put("list2", listBean2);
        beans.put("bean", simpleBean2);
        beans.put("staticBean", simpleBean1);
        InputStream is = new BufferedInputStream(getClass().getResourceAsStream(severalListsInRowXLS));
        XLSTransformer transformer = new XLSTransformer();
        HSSFWorkbook resultWorkbook = transformer.transformXLS(is, beans);
        is.close();
        is = new BufferedInputStream(getClass().getResourceAsStream(severalListsInRowXLS));
        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", 10, resultSheet.getLastRowNum());

        Map listPropMap = new HashMap();
        listPropMap.put("Name: ${list1.name}", "Name: " + listBean1.getName());
        listPropMap.put("Name: ${list2.name}", "Name: " + listBean2.getName());
        // static tables check
        listPropMap.put("Name: ${bean.name}", "Name: " + simpleBean2.getName());
        listPropMap.put("${bean.doubleValue}", simpleBean2.getDoubleValue() );
        listPropMap.put("Merged - ${bean.intValue}", "Merged - " + simpleBean2.getIntValue() );
        listPropMap.put("${bean.intValue}", simpleBean2.getIntValue() );
        listPropMap.put("Name: ${staticBean.name}", "Name: " + simpleBean1.getName() );
        listPropMap.put("${staticBean.intValue}", simpleBean1.getIntValue() );
        listPropMap.put("${staticBean.doubleValue}", simpleBean1.getDoubleValue() );

        CellsChecker checker = new CellsChecker(listPropMap);
        checker.checkRows(sourceSheet, resultSheet, 0, 0, 3);

        checker.checkSection( sourceSheet, resultSheet, 0, 0, (short)0, (short)1, 7, true, true);
        checker.checkSection( sourceSheet, resultSheet, 0, 0, (short)7, (short)8, 8, true, true);
        checker.checkSection( sourceSheet, resultSheet, 0, 0, (short)13, (short)14, 10, true, true);

        checker.checkListCells(sourceSheet, 3, resultSheet, 3, (short) 2, new String[]{"Name: " + names[0], "Name: " + names[1], "Name: " + names[2]} );
        checker.checkListCells(sourceSheet, 3, resultSheet, 3, (short) 3, new String[]{names[0] + " - " + names[0] + " : " + intValues[0],
                names[1] + " - " + names[1] + " : " + intValues[1],names[2] + " - " + names[2] + " : " + intValues[2] });
        checker.checkListCells(sourceSheet, 3, resultSheet, 3, (short) 5, doubleValues);
        checker.checkListCells(sourceSheet, 3, resultSheet, 3, (short) 12, intValues);
//
        checker.checkFormulaCell(sourceSheet, 3, resultSheet, 3, (short) 6, "F4+M4");
        checker.checkFormulaCell(sourceSheet, 4, resultSheet, 4, (short) 6, "F5+M5", true);
        checker.checkFormulaCell(sourceSheet, 5, resultSheet, 5, (short) 6, "F6+M6", true);

        checker.checkFormulaCell(sourceSheet, 4, resultSheet, 6, (short) 5, "SUM(F4:F6)");

        checker.checkListCells( sourceSheet, 3, resultSheet, 3, (short)10, doubleValues2);
        checker.checkListCells( sourceSheet, 3, resultSheet, 3, (short)11, intValues2);

        checker.checkFormulaCell(sourceSheet, 3, resultSheet, 3, (short) 9, "K4+L4");
        checker.checkFormulaCell(sourceSheet, 4, resultSheet, 4, (short) 9, "K5+L5", true);
        checker.checkFormulaCell(sourceSheet, 5, resultSheet, 5, (short) 9, "K6+L6", true);

        checker.checkFormulaCell(sourceSheet, 4, resultSheet, 10, (short) 9, "SUM(J4:J10)");
        checker.checkFormulaCell(sourceSheet, 4, resultSheet, 10, (short) 11, "SUM(L4:L10)");

        assertEquals( "Incorrect number of merged regions", 6, resultSheet.getNumMergedRegions() );
        assertTrue( "Merged Region (4,0,4,1) not found", isMergedRegion( resultSheet, new Region(4, (short)0, 4, (short)1) ) );
        assertTrue( "Merged Region (3,7,3,8) not found", isMergedRegion( resultSheet, new Region(3, (short)7, 3, (short)8) ) );
        assertTrue( "Merged Region (3,13,3,14) not found", isMergedRegion( resultSheet, new Region(3, (short)13, 3, (short)14) ) );

        assertTrue( "Merged Region (3,3,3,4) not found", isMergedRegion( resultSheet, new Region(3, (short)3, 3, (short)4) ) );
        assertTrue( "Merged Region (4,3,4,4) not found", isMergedRegion( resultSheet, new Region(4, (short)3, 4, (short)4) ) );
        assertTrue( "Merged Region (5,3,5,4) not found", isMergedRegion( resultSheet, new Region(5, (short)3, 5, (short)4) ) );


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

    public void testFixedSizeCollections() throws IOException, ParsePropertyException {
        Map beans = new HashMap();
        beans.put("employee", itEmployees);
        InputStream is = new BufferedInputStream(getClass().getResourceAsStream(fixedSizeListXLS));
        XLSTransformer transformer = new XLSTransformer();
        transformer.markAsFixedSizeCollection( "employee" );
        HSSFWorkbook resultWorkbook = transformer.transformXLS(is, beans);
        is.close();
        is = new BufferedInputStream(getClass().getResourceAsStream(fixedSizeListXLS));
        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(), resultSheet.getLastRowNum());

        Map props = new HashMap();
        CellsChecker checker;
        checker = new CellsChecker(props);
        checker.checkRows(sourceSheet, resultSheet, 0, 0, 2);

        checker.checkFixedListCells( sourceSheet, 2, resultSheet, 2, (short)0, itEmployeeNames);
        checker.checkFixedListCells( sourceSheet, 2, resultSheet, 2, (short)1, itPayments);
        checker.checkFixedListCells( sourceSheet, 2, resultSheet, 2, (short)2, itBonuses);

        is.close();
        saveWorkbook(resultWorkbook, fixedSizeListDestXLS);

    }

    public void testExpressions1() throws IOException, ParsePropertyException {
        Map beans = new HashMap();
        beans.put("bean", simpleBean1);
        beans.put("listBean", beanWithList);
        InputStream is = new BufferedInputStream(getClass().getResourceAsStream(expressions1XLS));
        XLSTransformer transformer = new XLSTransformer();
        HSSFWorkbook resultWorkbook = transformer.transformXLS(is, beans);
        is.close();
        is = new BufferedInputStream(getClass().getResourceAsStream(expressions1XLS));
        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("Name: ${bean.name}", "Name: " + simpleBean1.getName());
        props.put("${bean.other.name} - ${bean.doubleValue*2},${(bean.other.intValue + bean.doubleValue)/0.5}",
                simpleBean1.getOther().getName() +
                " - " + simpleBean1.getDoubleValue().doubleValue()*2 + "," + (simpleBean1.getOther().getIntValue().intValue() + simpleBean1.getDoubleValue().doubleValue())/0.5);
        props.put("${10*bean.doubleValue + 2.55}", new Double(simpleBean1.getDoubleValue().doubleValue() * 10 + 2.55));
        CellsChecker checker = new CellsChecker(props);
        checker.checkRows(sourceSheet, resultSheet, sourceSheet.getFirstRowNum(), resultSheet.getFirstRowNum(), 6);

        Map listPropMap = new HashMap();
        listPropMap.put("[${listBean.beans.name}]", "[" + beanWithList.getName() + "]");
        checker = new CellsChecker(listPropMap);
        checker.checkListCells(sourceSheet, 6, resultSheet, 6, (short) 0,
                new String[]{ "[" +((SimpleBean)beanWithList.getBeans().get(0)).getName() + "]",
                        "[" + ((SimpleBean)beanWithList.getBeans().get(1)).getName() + "]",
                        "[" + ((SimpleBean)beanWithList.getBeans().get(2)).getName() + "]" });
        checker.checkListCells(sourceSheet, 6, resultSheet, 6, (short) 1,
                new String[]{(((SimpleBean)beanWithList.getBeans().get(0)).getDoubleValue().doubleValue()*10.2)/10 + 1.567 + " yeah",
                        (((SimpleBean)beanWithList.getBeans().get(1)).getDoubleValue().doubleValue()*10.2)/10 + 1.567 + " yeah",
                        (((SimpleBean)beanWithList.getBeans().get(2)).getDoubleValue().doubleValue()*10.2)/10 + 1.567 + " yeah" });

        checker.checkListCells(sourceSheet, 6, resultSheet, 6, (short) 2,
                new String[]{((SimpleBean)beanWithList.getBeans().get(0)).getDoubleValue().doubleValue()  + ((SimpleBean)beanWithList.getBeans().get(0)).getIntValue().intValue()*2.1
                        + " - " + (((SimpleBean)beanWithList.getBeans().get(0)).getIntValue().intValue()*(10 + 1.1)),
                        ((SimpleBean)beanWithList.getBeans().get(1)).getDoubleValue().doubleValue()  + ((SimpleBean)beanWithList.getBeans().get(1)).getIntValue().intValue()*2.1
                        + " - " + (((SimpleBean)beanWithList.getBeans().get(1)).getIntValue().intValue()*(10 + 1.1)),
                        ((SimpleBean)beanWithList.getBeans().get(2)).getDoubleValue().doubleValue()  + ((SimpleBean)beanWithList.getBeans().get(2)).getIntValue().intValue()*2.1
                        + " - " + (((SimpleBean)beanWithList.getBeans().get(2)).getIntValue().intValue()*(10 + 1.1))});


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

    public void testIfTag() throws IOException, ParsePropertyException {
        Map beans = new HashMap();

        BeanWithList listBean = new BeanWithList("Main bean", new Double(10.0));
        listBean.addBean(simpleBean1);
        listBean.addBean(simpleBean2);
        listBean.addBean(simpleBean3);

        beans.put("bean", simpleBean1);
        beans.put("listBean", listBean);

        InputStream is = new BufferedInputStream(getClass().getResourceAsStream(iftagXLS));
        XLSTransformer transformer = new XLSTransformer();
        HSSFWorkbook resultWor