xlstransformertest.java

一个java生成自动生成Excel

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

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

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

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

        HSSFSheet sourceSheet = sourceWorkbook.getSheetAt(0);
        HSSFSheet resultSheet = resultWorkbook.getSheetAt(0);

        Map props = new HashMap();
        props.put("${departments.name}//:4", "IT");
        props.put("Department//departments", "Department");
        CellsChecker checker = new CellsChecker(props);
        checker.checkRows(sourceSheet, resultSheet, 0, 0, 3);
        checker.checkListCells(sourceSheet, 3, resultSheet, 3, (short) 0, itEmployeeNames);
        checker.checkListCells(sourceSheet, 3, resultSheet, 3, (short) 1, itPayments);
        checker.checkListCells(sourceSheet, 3, resultSheet, 3, (short) 2, itBonuses);
        checker.checkFormulaCell( sourceSheet, 3, resultSheet, 3, (short)3, "B4*(1+C4)");
        checker.checkFormulaCell( sourceSheet, 3, resultSheet, 4, (short)3, "B5*(1+C5)");
        checker.checkFormulaCell( sourceSheet, 3, resultSheet, 7, (short)3, "B8*(1+C8)");
//        checker.checkFormulaCell( sourceSheet, 4, resultSheet, 8, (short)1, "SUM(B4:B8)");
//        checker.checkFormulaCell( sourceSheet, 4, resultSheet, 6, (short)3, "SUM(D4:D8)");
        props.clear();
        props.put("${departments.name}//:4", "HR");
        props.put("Department//departments", "Department");
        checker = new CellsChecker(props);
        checker.checkRows(sourceSheet, resultSheet, 0, 9, 3);
        props.clear();
        checker.checkListCells(sourceSheet, 3, resultSheet, 12, (short) 0, hrEmployeeNames);
        checker.checkListCells(sourceSheet, 3, resultSheet, 12, (short) 1, hrPayments);
        checker.checkListCells(sourceSheet, 3, resultSheet, 12, (short) 2, hrBonuses);
        checker.checkFormulaCell( sourceSheet, 3, resultSheet, 12, (short)3, "B13*(1+C13)");
        checker.checkFormulaCell( sourceSheet, 3, resultSheet, 13, (short)3, "B14*(1+C14)");
        checker.checkFormulaCell( sourceSheet, 3, resultSheet, 15, (short)3, "B16*(1+C16)");
//        checker.checkFormulaCell( sourceSheet, 4, resultSheet, 16, (short)1, "SUM(B13:B16)");
//        checker.checkFormulaCell( sourceSheet, 4, resultSheet, 16, (short)3, "SUM(D13:D16)");
        props.clear();
        props.put("${departments.name}//:4", "BA");
        props.put("Department//departments", "Department");
        checker = new CellsChecker(props);
        checker.checkRows(sourceSheet, resultSheet, 0, 17, 3);
        props.clear();
        checker.checkListCells(sourceSheet, 3, resultSheet, 20, (short) 0, baEmployeeNames);
        checker.checkListCells(sourceSheet, 3, resultSheet, 20, (short) 1, baPayments);
        checker.checkListCells(sourceSheet, 3, resultSheet, 20, (short) 2, baBonuses);
        checker.checkFormulaCell( sourceSheet, 3, resultSheet, 20, (short)3, "B21*(1+C21)");
        checker.checkFormulaCell( sourceSheet, 3, resultSheet, 21, (short)3, "B22*(1+C22)");
        checker.checkFormulaCell( sourceSheet, 3, resultSheet, 22, (short)3, "B23*(1+C23)");
        checker.checkFormulaCell( sourceSheet, 4, resultSheet, 23, (short)1, "SUM(B21:B23)");
        checker.checkFormulaCell( sourceSheet, 4, resultSheet, 23, (short)3, "SUM(D21:D23)");

        saveWorkbook(resultWorkbook, grouping3DestXLS);
    }

    public void testGroupingFormulas() 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(groupingFormulasXLS));
        XLSTransformer transformer = new XLSTransformer();
        HSSFWorkbook resultWorkbook = transformer.transformXLS(is, beans);
        is.close();
        is = new BufferedInputStream(getClass().getResourceAsStream(groupingFormulasXLS));
        POIFSFileSystem fs = new POIFSFileSystem(is);
        HSSFWorkbook sourceWorkbook = new HSSFWorkbook(fs);
        is.close();
//        HSSFWorkbook resultWorkbook = new HSSFWorkbook( new POIFSFileSystem( new BufferedInputStream(getClass().getResourceAsStream(groupingFormulasDestXLS))));
        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}//mainBean.beans.beans", bean.getName());
        props.put("Name://mainBean.beans", "Name:");
        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}//mainBean.beans.beans", bean.getName());
        props.put("Name://mainBean.beans", "Name:");
        checker = new CellsChecker(props);
        checker.checkRows(sourceSheet, resultSheet, 3, 4, 1);
//        Todo: next check requires investigation
//        Next check currently fails. It seems POI does not get the value of this formula cell correctly.
//        It returns "SUM(B9:B10)" instead of "SUM(B4:B5)". But in the output XLS file the formula is correct.
//        checker.checkFormulaCell(sourceSheet, 4, resultSheet, 5, (short)1, "SUM(B4:B5)");
        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}//mainBean.beans.beans", bean.getName());
        props.put("Name://mainBean.beans", "Name:");
        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}//mainBean.beans.beans", bean.getName());
        props.put("Name://mainBean.beans", "Name:");
        checker = new CellsChecker(props);
        checker.checkRows(sourceSheet, resultSheet, 3, 9, 1);
        checker.checkFormulaCell(sourceSheet, 4, resultSheet, 10, (short) 1, "SUM(B9:B10)");

        saveWorkbook(resultWorkbook, groupingFormulasDestXLS);
    }

    public void testSeveralPropertiesInCell() throws IOException, ParsePropertyException {
        Map beans = new HashMap();
        beans.put("bean", simpleBean1);
        beans.put("listBean", beanWithList);
        InputStream is = new BufferedInputStream(getClass().getResourceAsStream(severalPropertiesInCellXLS));
        XLSTransformer transformer = new XLSTransformer();
        HSSFWorkbook resultWorkbook = transformer.transformXLS(is, beans);
        is.close();
        is = new BufferedInputStream(getClass().getResourceAsStream(severalPropertiesInCellXLS));
        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},${bean.other.intValue}", simpleBean1.getOther().getName() +
                " - " + simpleBean1.getDoubleValue() + "," + simpleBean1.getOther().getIntValue());
        props.put("${bean.dateValue}", simpleBean1.getDateValue());
        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() + " yeah",
                        ((SimpleBean)beanWithList.getBeans().get(1)).getDoubleValue() + " yeah",
                        ((SimpleBean)beanWithList.getBeans().get(2)).getDoubleValue() + " yeah" });

        checker.checkListCells(sourceSheet, 6, resultSheet, 6, (short) 2,
                new String[]{((SimpleBean)beanWithList.getBeans().get(0)).getName() + " : " + ((SimpleBean)beanWithList.getBeans().get(0)).getDoubleValue() + "!",