xlstransformertest.java

一个java生成自动生成Excel

package net.sf.jxls;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;

import junit.framework.TestCase;
import net.sf.jxls.bean.Bean;
import net.sf.jxls.bean.BeanWithList;
import net.sf.jxls.bean.Column;
import net.sf.jxls.bean.Department;
import net.sf.jxls.bean.Employee;
import net.sf.jxls.bean.Item;
import net.sf.jxls.bean.MyBean;
import net.sf.jxls.bean.SimpleBean;
import net.sf.jxls.exception.ParsePropertyException;
import net.sf.jxls.transformer.Configuration;
import net.sf.jxls.transformer.XLSTransformer;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

/**
 * @author Leonid Vysochyn
 */
public class XLSTransformerTest extends TestCase {
    protected final Log log = LogFactory.getLog(getClass());

    public static final String simpleBeanXLS = "/templates/simplebean.xls";
    public static final String simpeBeanDestXLS = "target/simplebean_output.xls";

    public static final String hideSheetsXLS = "/templates/hidesheets.xls";
    public static final String hideSheetsDestXLS = "target/hidesheets_output.xls";

    public static final String beanWithListXLS = "/templates/beanwithlist.xls";
    public static final String beanWithListDestXLS = "target/beanwithlist_output.xls";

    public static final String emptyBeansXLS = "/templates/beanwithlist.xls";
    public static final String emptyBeansDestXLS = "target/emptybeans_output.xls";

    public static final String formulasXLS = "/templates/formulas.xls";
    public static final String formulasDestXLS = "target/formulas_output.xls";
    public static final String formulas2XLS = "/templates/formulas2.xls";
    public static final String formulas2DestXLS = "target/formulas2_output.xls";

    public static final String multipleListRowsXLS = "/templates/multiplelistrows.xls";
    public static final String multipleListRowsDestXLS = "target/multiplelistrows_output.xls";

    public static final String grouping1XLS = "/templates/grouping1.xls";
    public static final String grouping1DestXLS = "target/grouping1_output.xls";

    public static final String groupingFormulasXLS = "/templates/groupingformulas.xls";
    public static final String groupingFormulasDestXLS = "target/groupingformulas_output.xls";

    public static final String grouping2XLS = "/templates/grouping2.xls";
    public static final String grouping2DestXLS = "target/grouping2_output.xls";

    public static final String grouping3XLS = "/templates/grouping3.xls";
    public static final String grouping3DestXLS = "target/grouping3_output.xls";

    public static final String mergeCellsListXLS = "/templates/mergecellslist.xls";
    public static final String mergeCellsListDestXLS = "target/mergecellslist_output.xls";

    public static final String mergeMultipleListRowsXLS = "/templates/mergemultiplelistrows.xls";
    public static final String mergeMultipleListRowsDestXLS = "target/mergemultiplelistrows_output.xls";

    public static final String severalPropertiesInCellXLS = "/templates/severalpropertiesincell.xls";
    public static final String severalPropertiesInCellDestXLS = "target/severalpropertiesincell_output.xls";

    public static final String parallelTablesXLS = "/templates/paralleltables.xls";
    public static final String parallelTablesDestXLS = "target/paralleltables_output.xls";

    public static final String severalListsInRowXLS = "/templates/severallistsinrow.xls";
    public static final String severalListsInRowDestXLS = "target/severallistsinrow_output.xls";

    public static final String fixedSizeListXLS = "/templates/fixedsizelist.xls";
    public static final String fixedSizeListDestXLS = "target/fixedsizelist_output.xls";

    public static final String expressions1XLS = "/templates/expressions1.xls";
    public static final String expressions1DestXLS = "target/expressions1_output.xls";

    public static final String iftagXLS = "/templates/iftag.xls";
    public static final String iftagDestXLS = "target/iftag_output.xls";

    public static final String forifTag2XLS = "/templates/foriftag2.xls";
    public static final String forifTag2DestXLS = "target/foriftag2_output.xls";

    public static final String poiobjectsXLS = "/templates/poiobjects.xls";
    public static final String poiobjectsDestXLS = "target/poiobjects_output.xls";

    public static final String forifTag3XLS = "/templates/foriftag3.xls";
    public static final String forifTag3DestXLS = "target/foriftag3_output.xls";
    
    public static final String forifTag3OutTagXLS = "/templates/foriftag3OutTag.xls";
    public static final String forifTag3OutTagDestXLS = "target/foriftag3OutTag_output.xls";

    public static final String forifTagMergeXLS = "/templates/foriftagmerge.xls";
    public static final String forifTagMergeDestXLS = "target/foriftagmerge_output.xls";

    public static final String employeeNotesXLS = "/templates/employeeNotes.xls";
    public static final String employeeNotesDestXLS = "target/employeeNotes_output.xls";
    public static final String employeeNotesRusDestXLS = "target/employeeNotesRus_output.xls";

    public static final String varStatusXLS = "/templates/varstatus.xls";
    public static final String varStatusDestXLS = "/target/varstatus_output.xls";

    public static final String forifTagOneRowXLS = "/templates/foriftagOneRow.xls";
    public static final String forifTagOneRowDestXLS = "target/foriftagOneRow_output.xls";

    public static final String dynamicColumnsXLS = "/templates/dynamicColumns.xls";
    public static final String dynamicColumnsDestXLS = "target/dynamicColumns_output.xls";

    public static final String forifTagOneRow2XLS = "/templates/foriftagOneRow2.xls";
    public static final String forifTagOneRowDest2XLS = "target/foriftagOneRow2_output.xls";

    public static final String multipleSheetListXLS = "/templates/multipleSheetList.xls";
    public static final String multipleSheetListDestXLS = "target/multipleSheetList_output.xls";

    public static final String multipleSheetList2XLS = "/templates/multipleSheetList2.xls";
    public static final String multipleSheetList2DestXLS = "target/multipleSheetList2_output.xls";

    public static final String multiTabXLS = "/templates/multi-tab-template.xls";
    public static final String multiTabDestXLS = "target/multi-tab_output.xls";

    public static final String groupTagXLS = "/templates/groupTag.xls";
    public static final String groupTagDestXLS = "target/groupTag_output.xls";

    public static final String jexlXLS = "/templates/jexl.xls";
    public static final String jexlDestXLS = "target/jexl_output.xls";

    public static final String forGroupByXLS = "/templates/forgroup.xls";
    public static final String forGroupByDestXLS = "target/forgroup_output.xls";

    public static final String outlineXLS = "/templates/outline.xls";
    public static final String outlineDestXLS = "target/outline_output.xls";


    SimpleBean simpleBean1;
    SimpleBean simpleBean2;
    SimpleBean simpleBean3;
    BeanWithList beanWithList;
    List beanList = new ArrayList();
    List itEmployees = new ArrayList();
    BeanWithList listBean1 = new BeanWithList("List bean 1");
    BeanWithList listBean2 = new BeanWithList("List bean 2");

    Object[] names = new Object[]{"Bean 1", "Bean 2", "Bean 3"};
    Object[] doubleValues = new Object[]{new Double(100.34567), new Double(555.3), new Double(777.569)};
    Object[] intValues = new Object[]{new Integer(10), new Integer(123), new Integer(10234)};
    Object[] dateValues = new Object[]{new Date(), null, new Date()};

    Object[] names2 = new Object[]{"Bean 1", "Bean 2", "Bean 3", "Bean 4", "Bean 5", "Bean 6", "Bean 7"};
    Object[] doubleValues2 = new Object[]{new Double(111.222), new Double(222.333), new Double(333.444),
            new Double(444.555), new Double(555.666), new Double(666.777), new Double(777.888)};
    Object[] intValues2 = new Object[]{new Integer(11), new Integer(12), new Integer(13), new Integer(14), new Integer(15),
            new Integer(16), new Integer(17)};


    String[] itEmployeeNames = new String[] {"Elsa", "Oleg", "Neil", "Maria", "John"};
    String[] hrEmployeeNames = new String[] {"Olga", "Helen", "Keith", "Cat"};
    String[] baEmployeeNames = new String[] {"Denise", "LeAnn", "Natali"};
    String[] mgrEmployeeNames = new String[] {"Sean", "John", "Joerg"};

    Double[] itPayments = new Double[] {new Double(1500), new Double(2300), new Double(2500), new Double(1700), new Double(2800)};
    Double[] hrPayments = new Double[] {new Double(1400), new Double(2100), new Double(1800), new Double(1900)};
    Double[] baPayments = new Double[] {new Double(2400), new Double(2200), new Double(2600)};
    Double[] mgrPayments = new Double[] {null, new Double(6000), null};
    Double[] itBonuses = new Double[] {new Double(0.15), new Double(0.25), new Double(0.00), new Double(0.15), new Double(0.20)};
    Double[] hrBonuses = new Double[] {new Double(0.20), new Double(0.10), new Double(0.15), new Double(0.15)};
    Double[] baBonuses = new Double[] {new Double(0.20), new Double(0.15), new Double(0.10)};
    Double[] mgrBonuses = new Double[] {new Double(0.20), null, new Double(0.20)};
    Integer[] itAges = new Integer[] {new Integer(34), new Integer(30), new Integer(25), new Integer(25), new Integer(35)};
    Integer[] hrAges = new Integer[] {new Integer(26), new Integer(28), new Integer(26), new Integer(26)};
    Integer[] baAges = new Integer[] {new Integer(30), new Integer(30), new Integer(30)};
    Integer[] mgrAges = new Integer[] {null, new Integer(35), null};
    List departments = new ArrayList();
    Department mgrDepartment, itDepartment;

    int[] amounts = {1, 2, 4, 6, 7, 8, 9, 10, 11, 13, 15, 18, 20, 21, 22};
    List amountBeans = new ArrayList();

    public XLSTransformerTest() {
    }

    public XLSTransformerTest(String s) {
        super(s);
    }

    protected void setUp() throws Exception {
        super.setUp();
        simpleBean1 = new SimpleBean(names[0].toString(), (Double) doubleValues[0], (Integer) intValues[0], (Date) dateValues[0]);
        simpleBean2 = new SimpleBean(names[1].toString(), (Double) doubleValues[1], (Integer) intValues[1], (Date) dateValues[1]);
        simpleBean3 = new SimpleBean(names[2].toString(), (Double) doubleValues[2], (Integer) intValues[2], (Date) dateValues[2]);

        listBean2.addBean( new SimpleBean(names2[0].toString(), (Double) doubleValues2[0], (Integer) intValues2[0]) );
        listBean2.addBean( new SimpleBean(names2[1].toString(), (Double) doubleValues2[1], (Integer) intValues2[1]) );
        listBean2.addBean( new SimpleBean(names2[2].toString(), (Double) doubleValues2[2], (Integer) intValues2[2]) );
        listBean2.addBean( new SimpleBean(names2[3].toString(), (Double) doubleValues2[3], (Integer) intValues2[3]) );
        listBean2.addBean( new SimpleBean(names2[4].toString(), (Double) doubleValues2[4], (Integer) intValues2[4]) );
        listBean2.addBean( new SimpleBean(names2[5].toString(), (Double) doubleValues2[5], (Integer) intValues2[5]) );
        listBean2.addBean( new SimpleBean(names2[6].toString(), (Double) doubleValues2[6], (Integer) intValues2[6]) );

        simpleBean1.setOther(simpleBean2);
        simpleBean2.setOther(simpleBean3);
//        simpleBean3.setOther( simpleBean1 );

        beanWithList = new BeanWithList("Bean With List", new Double(1976.1202));


        beanList.add(simpleBean1);