xlstransformertest.java

一个java生成自动生成Excel

        XLSTransformer transformer = new XLSTransformer();
        HSSFWorkbook resultWorkbook = transformer.transformXLS(is, beans);
        is.close();
        is = new BufferedInputStream(getClass().getResourceAsStream(multipleListRowsXLS));
        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) * 4, resultSheet.getLastRowNum());

        Map props = new HashMap();
        props.put("${listBean.beans.name}//:3", names[0]);
        props.put("${listBean.beans.doubleValue}", doubleValues[0]);
        props.put("${listBean.beans.other.intValue}", simpleBean1.getOther().getIntValue());
        props.put("${listBean.beans.dateValue}", dateValues[0]);
        props.put("//listBean.beans", "");
        props.put("Int Value://listBean.beans", "Int Value:");
        CellsChecker checker = new CellsChecker(props);
        checker.checkRows(sourceSheet, resultSheet, 3, 3, 4);

        props.clear();
        props.put("${listBean.beans.name}//:3", names[1]);
        props.put("${listBean.beans.doubleValue}", doubleValues[1]);
        props.put("${listBean.beans.other.intValue}", simpleBean2.getOther().getIntValue());
        props.put("${listBean.beans.dateValue}", dateValues[1]);
        props.put("//listBean.beans", "");
        props.put("Int Value://listBean.beans", "Int Value:");
        checker = new CellsChecker(props);

        checker.checkRows(sourceSheet, resultSheet, 3, 7, 4);

        props.clear();
        props.put("${listBean.beans.name}//:3", names[2]);
        props.put("${listBean.beans.doubleValue}", doubleValues[2]);
        props.put("${listBean.beans.other.intValue}", "");
        props.put("${listBean.beans.dateValue}", dateValues[2]);
        props.put("//listBean.beans", "");
        props.put("Int Value://listBean.beans", "Int Value:");
        checker = new CellsChecker(props);
        checker.checkRows(sourceSheet, resultSheet, 3, 11, 4);

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

    public void testMergedMultipleListRows() throws IOException, ParsePropertyException {

        Map beans = new HashMap();
        beans.put("listBean", beanWithList);
        InputStream is = new BufferedInputStream(getClass().getResourceAsStream(mergeMultipleListRowsXLS));
        XLSTransformer transformer = new XLSTransformer();
        HSSFWorkbook resultWorkbook = transformer.transformXLS(is, beans);
        is.close();
        is = new BufferedInputStream(getClass().getResourceAsStream(mergeMultipleListRowsXLS));
        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) * 4, resultSheet.getLastRowNum());

        Map props = new HashMap();
        props.put("${listBean.beans.name}//:3", names[0]);
        props.put("${listBean.beans.doubleValue}", doubleValues[0]);
        props.put("${listBean.beans.dateValue}", dateValues[0]);
        CellsChecker checker = new CellsChecker(props);
        checker.checkRows(sourceSheet, resultSheet, 3, 3, 4);

        props.clear();
        props.put("${listBean.beans.name}//:3", names[1]);
        props.put("${listBean.beans.doubleValue}", doubleValues[1]);
        props.put("${listBean.beans.dateValue}", dateValues[1]);
        checker = new CellsChecker(props);
        checker.checkRows(sourceSheet, resultSheet, 3, 7, 4);

        props.clear();
        props.put("${listBean.beans.name}//:3", names[2]);
        props.put("${listBean.beans.doubleValue}", doubleValues[2]);
        props.put("${listBean.beans.dateValue}", dateValues[2]);
        checker = new CellsChecker(props);
        checker.checkRows(sourceSheet, resultSheet, 3, 11, 4);

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

        assertTrue( "Merged Region not found", isMergedRegion( resultSheet, new Region(4, (short)1, 4, (short)2) ) );
        assertTrue( "Merged Region not found", isMergedRegion( resultSheet, new Region(8, (short)1, 8, (short)2) ) );
        assertTrue( "Merged Region not found", isMergedRegion( resultSheet, new Region(12, (short)1, 12, (short)2) ) );

        assertTrue( "Merged Region not found", isMergedRegion( resultSheet, new Region(5, (short)1, 6, (short)2) ) );
        assertTrue( "Merged Region not found", isMergedRegion( resultSheet, new Region(9, (short)1, 10, (short)2) ) );
        assertTrue( "Merged Region not found", isMergedRegion( resultSheet, new Region(13, (short)1, 14, (short)2) ) );

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

    public void testGrouping1() throws IOException, ParsePropertyException {
        BeanWithList beanWithList2 = new BeanWithList("2nd bean with list", new Double(22.22));
        List beans2 = new ArrayList();
        beans2.add(new SimpleBean("bean 21", new Double(21.21), new Integer(21), new Date()));
        beans2.add(new SimpleBean("bean 22", new Double(22.22), new Integer(22), new Date()));
        beanWithList2.setBeans(beans2);
        BeanWithList beanWithList3 = new BeanWithList("3d bean with list", new Double(333.333));
        List beans3 = new ArrayList();
        beans3.add(new SimpleBean("bean 31", new Double(31.31), new Integer(31), new Date()));
        beans3.add(new SimpleBean("bean 32", new Double(32.32), new Integer(32), new Date()));
        beanWithList3.setBeans(beans3);
        List mainList = new ArrayList();
        mainList.add(beanWithList2);
        mainList.add(beanWithList3);
        BeanWithList bean = new BeanWithList("Root", new Double(1111.1111));
        bean.setBeans(mainList);
        Map beans = new HashMap();
        beans.put("mainBean", bean);
        InputStream is = new BufferedInputStream(getClass().getResourceAsStream(grouping1XLS));
        XLSTransformer transformer = new XLSTransformer();
        HSSFWorkbook resultWorkbook = transformer.transformXLS(is, beans);
        is.close();
        is = new BufferedInputStream(getClass().getResourceAsStream(grouping1XLS));
        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() + 6, resultSheet.getLastRowNum());

        Map props = new HashMap();
        props.put("${mainBean.beans.name}//:3", "2nd bean with list");
        props.put("${mainBean.beans.beans.name}", "bean 21");
        props.put("${mainBean.beans.beans.doubleValue}", new Double(21.21));
        props.put("${mainBean.name}", bean.getName());
        props.put("Name://mainBean.beans", "Name:");
        props.put("${mainBean.name}//mainBean.beans.beans", bean.getName());
        CellsChecker checker = new CellsChecker(props);
        checker.checkRows(sourceSheet, resultSheet, 1, 1, 3);
        props.clear();
        props.put("${mainBean.beans.beans.name}", "bean 22");
        props.put("${mainBean.beans.beans.doubleValue}", new Double(22.22));
        props.put("${mainBean.name}", bean.getName());
        props.put("Name://mainBean.beans", "Name:");
        props.put("${mainBean.name}//mainBean.beans.beans", bean.getName());
        checker = new CellsChecker(props);
        checker.checkRows(sourceSheet, resultSheet, 3, 4, 1);
        checker.checkRows(sourceSheet, resultSheet, 4, 5, 1);
        props.clear();
        props.put("${mainBean.beans.name}//:3", "3d bean with list");
        props.put("${mainBean.beans.beans.name}", "bean 31");
        props.put("${mainBean.beans.beans.doubleValue}", new Double(31.31));
        props.put("${mainBean.name}", bean.getName());
        props.put("Name://mainBean.beans", "Name:");
        props.put("${mainBean.name}//mainBean.beans.beans", bean.getName());
        checker = new CellsChecker(props);
        checker.checkRows(sourceSheet, resultSheet, 1, 6, 3);
        props.clear();
        props.put("${mainBean.beans.beans.name}", "bean 32");
        props.put("${mainBean.beans.beans.doubleValue}", new Double(32.32));
        props.put("${mainBean.name}", bean.getName());
        props.put("Name://mainBean.beans", "Name:");
        props.put("${mainBean.name}//mainBean.beans.beans", bean.getName());
        checker = new CellsChecker(props);
        checker.checkRows(sourceSheet, resultSheet, 3, 9, 1);
        checker.checkRows(sourceSheet, resultSheet, 4, 10, 1);

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

    public void testMergeCellsList() throws IOException, ParsePropertyException {
        Map beans = new HashMap();
        beans.put("listBean", beanWithList);
        InputStream is = new BufferedInputStream(getClass().getResourceAsStream(mergeCellsListXLS));
        XLSTransformer transformer = new XLSTransformer();
        HSSFWorkbook resultWorkbook = transformer.transformXLS(is, beans);
        is.close();
        is = new BufferedInputStream(getClass().getResourceAsStream(mergeCellsListXLS));
        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, intValues);
        checker.checkListCells(sourceSheet, 3, resultSheet, 3, (short) 3, doubleValues);
        assertEquals( "Incorrect number of merged regions", 3, resultSheet.getNumMergedRegions() );
        assertTrue( "Merged Region (3,1,3,2) not found", isMergedRegion( resultSheet, new Region(3, (short)1, 3, (short)2) ) );
        assertTrue( "Merged Region (4,1,4,2) not found", isMergedRegion( resultSheet, new Region(4, (short)1, 4, (short)2) ) );
        assertTrue( "Merged Region (5,1,5,2) not found", isMergedRegion( resultSheet, new Region(5, (short)1, 5, (short)2) ) );

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

    protected static boolean isMergedRegion(HSSFSheet sheet, Region region){
        for(int i = 0; i < sheet.getNumMergedRegions(); i++){
            Region mgdRegion = sheet.getMergedRegionAt( i );
            if( mgdRegion.equals( region ) ){
                return true;
            }
//            log.info("(" + mgdRegion.getRowFrom() + ", " + mgdRegion.getColumnFrom() + ", " + mgdRegion.getRowTo() + ", " + mgdRegion.getColumnTo() + ")");
        }
        return false;
    }

    public void testGrouping2() throws IOException, ParsePropertyException {
        BeanWithList beanWithList2 = new BeanWithList("2nd bean with list", new Double(22.22));
        List beans2 = new ArrayList();
        beans2.add(new SimpleBean("bean 21", new Double(21.21), new Integer(21), new Date()));
        beans2.add(new SimpleBean("bean 22", new Double(22.22), new Integer(22), new Date()));
        beanWithList2.setBeans(beans2);
        BeanWithList beanWithList3 = new BeanWithList("3d bean with list", new Double(333.333));
        List beans3 = new ArrayList();