xlsreadertest.java

一个java生成自动生成Excel

package net.sf.jxls.reader;

import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import junit.framework.TestCase;
import net.sf.jxls.reader.sample.Department;
import net.sf.jxls.reader.sample.Employee;

/**
 * @author Leonid Vysochyn
 */
public class XLSReaderTest extends TestCase {
    public static final String dataXLS = "/templates/departmentdata.xls";

    public void testRead() throws IOException {
        InputStream inputXLS = new BufferedInputStream(getClass().getResourceAsStream(dataXLS));

        Department itDepartment = new Department();
        Department hrDepartment = new Department();
        Map beans = new HashMap();
        beans.put("itDepartment", itDepartment);
        beans.put("hrDepartment", hrDepartment);
        // Create Sheet1 Reader
        List chiefMappings = new ArrayList();
        chiefMappings.add( new BeanCellMapping(0, (short) 1, "itDepartment", "name") );
        chiefMappings.add( new BeanCellMapping(3, (short) 0, "itDepartment", "chief.name") );
        chiefMappings.add( new BeanCellMapping(3, (short) 1, "itDepartment", "chief.age") );
        chiefMappings.add( new BeanCellMapping(3, (short) 3, "itDepartment", "chief.payment") );
        chiefMappings.add( new BeanCellMapping("E4", "itDepartment", "chief.bonus") );
        XLSBlockReader chiefReader = new SimpleBlockReaderImpl(0, 6, chiefMappings);
        List employeeMappings = new ArrayList();
        employeeMappings.add( new BeanCellMapping(7, (short) 0, "employee", "name") );
        employeeMappings.add( new BeanCellMapping(7, (short) 1, "employee", "age") );
        employeeMappings.add( new BeanCellMapping(7, (short) 3, "employee", "payment") );
        employeeMappings.add( new BeanCellMapping(7, (short) 4, "employee", "bonus") );
        XLSBlockReader employeeReader = new SimpleBlockReaderImpl(7, 7, employeeMappings);
        XLSLoopBlockReader employeesReader = new XLSForEachBlockReaderImpl(7, 7, "itDepartment.staff", "employee", Employee.class);
        employeesReader.addBlockReader( employeeReader );
        SectionCheck loopBreakCheck = getLoopBreakCheck();
        employeesReader.setLoopBreakCondition( loopBreakCheck );
        XLSSheetReader sheet1Reader = new XLSSheetReaderImpl();
        sheet1Reader.addBlockReader( chiefReader );
        sheet1Reader.addBlockReader( employeesReader );
        // Create Sheet2 Reader
        XLSSheetReader sheet2Reader = new XLSSheetReaderImpl();
        employeeMappings = new ArrayList();
        employeeMappings.add( new BeanCellMapping(2, (short) 0, "employee", "name") );
        employeeMappings.add( new BeanCellMapping(2, (short) 1, "employee", "age") );
        employeeMappings.add( new BeanCellMapping(2, (short) 2, "employee", "payment") );
        employeeMappings.add( new BeanCellMapping(2, (short) 3, "employee", "bonus") );
        XLSBlockReader sheet2EmployeeReader = new SimpleBlockReaderImpl(2, 2, employeeMappings);
        XLSLoopBlockReader sheet2EmployeesReader = new XLSForEachBlockReaderImpl(2, 2, "hrDepartment.staff", "employee", Employee.class);
        sheet2EmployeesReader.addBlockReader( sheet2EmployeeReader );
        sheet2EmployeesReader.setLoopBreakCondition( getLoopBreakCheck() );
        chiefMappings = new ArrayList();
        chiefMappings.add( new BeanCellMapping(7, (short)0, "hrDepartment", "chief.name"));
        chiefMappings.add( new BeanCellMapping(7, (short)1, "hrDepartment", "chief.age"));
        chiefMappings.add( new BeanCellMapping(7, (short)2, "hrDepartment", "chief.payment"));
        chiefMappings.add( new BeanCellMapping(7, (short)3, "hrDepartment", "chief.bonus"));
        XLSBlockReader hrChiefReader = new SimpleBlockReaderImpl(3, 7, chiefMappings);
        sheet2Reader.addBlockReader( new SimpleBlockReaderImpl(0, 1, new ArrayList()));
        sheet2Reader.addBlockReader( sheet2EmployeesReader );
        sheet2Reader.addBlockReader( hrChiefReader );
        // create main reader
        XLSReader mainReader = new XLSReaderImpl();
        mainReader.addSheetReader("Sheet1", sheet1Reader);
        mainReader.addSheetReader("Sheet2", sheet2Reader);
        mainReader.read( inputXLS, beans);
        inputXLS.close();
        // check sheet1 data
        assertEquals( "IT", itDepartment.getName() );
        assertEquals( "Maxim", itDepartment.getChief().getName() );
        assertEquals( new Integer(30), itDepartment.getChief().getAge() );
        assertEquals( new Double( 3000.0), itDepartment.getChief().getPayment() );
        assertEquals( new Double(0.25), itDepartment.getChief().getBonus() );
        assertEquals( 4, itDepartment.getStaff().size() );
        Employee employee = (Employee) itDepartment.getStaff().get(0);
        checkEmployee( employee, "Oleg", new Integer(32), new Double(2000.0), new Double(0.20) );
        employee = (Employee) itDepartment.getStaff().get(1);
        checkEmployee( employee, "Yuri", new Integer(29), new Double(1800.0), new Double(0.15) );
        employee = (Employee) itDepartment.getStaff().get(2);
        checkEmployee( employee, "Leonid", new Integer(30), new Double(1700.0), new Double(0.20) );
        employee = (Employee) itDepartment.getStaff().get(3);
        checkEmployee( employee, "Alex", new Integer(28), new Double(1600.0), new Double(0.20) );
        // check sheet2 data
        checkEmployee( hrDepartment.getChief(), "Betsy", new Integer(37), new Double(2200.0), new Double(0.3) );
        assertEquals(4, hrDepartment.getStaff().size() );
        employee = (Employee) hrDepartment.getStaff().get(0);
        checkEmployee( employee, "Olga", new Integer(26), new Double(1400.0), new Double(0.20) );
        employee = (Employee) hrDepartment.getStaff().get(1);
        checkEmployee( employee, "Helen", new Integer(30), new Double(2100.0), new Double(0.10) );
        employee = (Employee) hrDepartment.getStaff().get(2);
        checkEmployee( employee, "Keith", new Integer(24), new Double(1800.0), new Double(0.15) );
        employee = (Employee) hrDepartment.getStaff().get(3);
        checkEmployee( employee, "Cat", new Integer(34), new Double(1900.0), new Double(0.15) );
    }



    private void checkEmployee(Employee employee, String name, Integer age, Double payment, Double bonus){
        assertNotNull( employee );
        assertEquals( name, employee.getName() );
        assertEquals( age, employee.getAge() );
        assertEquals( payment, employee.getPayment() );
        assertEquals( bonus, employee.getBonus() );
    }


    private SectionCheck getLoopBreakCheck() {
        OffsetRowCheck rowCheck = new OffsetRowCheckImpl( 0 );
        rowCheck.addCellCheck( new OffsetCellCheckImpl((short) 0, "Employee Payment Totals:") );
        SectionCheck sectionCheck = new SimpleSectionCheck();
        sectionCheck.addRowCheck( rowCheck );
        return sectionCheck;
    }


}