modeltoexcelimpl.java

J2EE项目开发Excel数据导入导出操作组件源代码（附带说明文档）

package com.javayjm.excel.file.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.math.NumberUtils;
import org.springframework.beans.BeanWrapper;
import org.springframework.beans.BeanWrapperImpl;

import jxl.CellType;
import jxl.Workbook;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.read.biff.BiffException;
import jxl.write.Number;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import com.javayjm.excel.config.ExcelConfigFactory;
import com.javayjm.excel.config.ExcelConfigManager;
import com.javayjm.excel.config.RuturnConfig;
import com.javayjm.excel.config.RuturnPropertyParam;
import com.javayjm.excel.file.ModelToExcel;
import com.javayjm.test.model.DeptModel;

/*＊
 * 公　司：
 * 文件名：ModelToExcelImpl.java
 * 作　者：YJM
 * 版本号：1.0
 * 时　间：2007-11-6下午02:18:13
 */

public class ModelToExcelImpl implements ModelToExcel {
    private List modelList = null;

    private File excelFile = null;

    private OutputStream excelOutputStream = null;
    
    private RuturnConfig excelConfig = null;

    private WritableCellFormat headerFormat;

    private WritableCellFormat titleFormat;

    private WritableCellFormat normolFormat;

    private String header;

    private int intHeight = -1;

    // 默认按配制文件输出，传入的dynamicTitleMap（property,exceltitlename）
    // 只是按javabean对应的属性改变输出标题
    // 如果 isDynamicTitle = true ，按传入的dynamicTitleMap 给出的列输出，只是列显示排序按配制文件设置。
    // isDynamicTitle = true 要验证，dynamicTitleMap size
    // 大于0,且其中设置的属性在javabean中存在的列必须大于0
    private boolean isDynamicTitle = false;

    private Map dynamicTitleMap = new HashMap();
    
    //文件导出时，支持模板导出
    /*
     * isTemplate = true ,按传入的模板文件格式输出，　
     * templateFile　模板文件，
     * startRow　开始输出的行数，
     * paramMap　模板中的参数。map.put("Name","尹景民")如模板中有一个单元格为　　制表人　#Name# ,输出时为：制表人　尹景民
     */
    private boolean isTemplate = false;
    
    private String templateFile = null;
    
    private int startRow = 0;
    
    private Map paramMap = new HashMap();
    
    private int startColumn = 0;
    
    private boolean isInsertRow = true;
    
    
    //支持多Sheet输出
    private String sheetName="Sheet1";
    private int sheetNum=0;

    public ModelToExcelImpl() {

    }

    public ModelToExcelImpl(File excelFile, RuturnConfig excelConfig, List modelList) {
        this.excelOutputStream = null;
        this.excelFile = excelFile;
        this.excelConfig = excelConfig;
        this.modelList = modelList;
    }
    public ModelToExcelImpl(OutputStream excelOutputStream, RuturnConfig excelConfig, List modelList) {
        this.excelFile = null;
        this.excelOutputStream = excelOutputStream;
        this.excelConfig = excelConfig;
        this.modelList = modelList;
    }
    private void initFormat() {
        if (this.headerFormat == null) {
            this.headerFormat = this.getDefaultHeaderFormat();
        }
        if (this.titleFormat == null) {
            this.titleFormat = this.getDefaultTitleFormat();
        }
        if (this.normolFormat == null) {
            this.normolFormat = this.getDefaultNormolFormat();
        }
    }

    public File getExcelfile() {
        initFormat();
        if (isDynamicTitle) {
            return getExcelfileByDynamicTitle(); // 动态传入的表头及列
        } else if(isTemplate){                  //模板方式
            return getExcelfileByTemplate();
        }else{
            return getExcelfileByConfig(); // 配制文件的列，可改变表头
        }
    }
    public File getExcelfileByTemplate(){
        try {
            if (this.excelFile!=null&&!excelFile.exists()) {
                this.excelFile.createNewFile();
            }
            //复制模板内容到导出文件中
            Workbook book;
            WritableWorkbook wbook;
            if(this.excelFile!=null){
                //this.copyFile(this.templateFile, this.excelFile.getAbsolutePath());
                //book = Workbook.getWorkbook(this.excelFile); 
                book = Workbook.getWorkbook(new File(this.templateFile)); 
                wbook = Workbook.createWorkbook(this.excelFile,book);                
            }else{
                //this.copyFile(this.templateFile, this.excelOutputStream);
                book = Workbook.getWorkbook(new File(this.templateFile));                
                wbook = Workbook.createWorkbook(this.excelOutputStream,book);
            }  
            WritableSheet wsheet;
            if(wbook.getSheets().length>this.sheetNum){
                wsheet = wbook.getSheet(this.sheetNum);
            }else{             	
                System.out.println("ERROR:　模板文件中不存在 Sheet(" + this.sheetNum + ")");                
                return null;
            }
                       
            
            int rows = wsheet.getRows();
            int columns = wsheet.getColumns();
            
            //替换模板中 # #　之间的内容。
            for(int i=0;i<rows;i++){
                for(int j=0;j<columns;j++){                    
                    WritableCell wc = wsheet.getWritableCell(j, i);
                    String strCell = wc.getContents().toString();
                    System.out.println("strCell = " + strCell );
                    if(StringUtils.isNotBlank(strCell)&&strCell.indexOf("#")>=0){                        
                        strCell = this.getParamValue(strCell);
                        if(wc.getType() == CellType.LABEL){
                            Label l = (Label)wc;
                            l.setString(strCell);
                        }
                    }
                }
            }
            //行，列重新取值　行取modelList.size()　+ startRow 
            columns = excelConfig.getColumnMap().size();            
            rows = modelList.size() + startRow;
            
            String[] propertyName = new String[columns];
            String[] propertyDataType = new String[columns];
            for (int j = 0; j < columns; j++) {
                RuturnPropertyParam propertyBean = (RuturnPropertyParam) excelConfig.getColumnMap().get(String.valueOf(j + 1));
                // 每列 Model中对应的属性
                propertyName[j] = propertyBean.getName(); 
                propertyDataType[j] = propertyBean.getDataType();
            }
            //判断当前 list 存储的是 from 还是 Map
            
            boolean isMap = false;
            if (modelList.size()>0) {
                Object tmpObj = modelList.get(0);
                if(tmpObj instanceof Map){
                    isMap = true;
                }
            }
            
            for(int i=startRow ;i<rows;i++){
                //每一次操作，都要插入一行
                if(this.isInsertRow){
                    wsheet.insertRow(i);
                }               
                Object modelObj = modelList.get(i - startRow);
                BeanWrapper bw = null;
                if(isMap!=true){   
                     bw= new BeanWrapperImpl(modelObj);
                }
                
                
                for(int j=0;j<columns;j++){                    
                    String strCell = "";
                    
                    Object obj;
                    if(isMap ==true){
                        obj =((Map) modelObj).get(propertyName[j]);
                    }else{
                         obj= bw.getPropertyValue(propertyName[j]);
                    }
                    
                    
                    if (obj != null) {
                        strCell = obj.toString().trim();
                    }
                    
                    Label cellNormol = new Label(this.startColumn + j, i, strCell, this.getNormolFormat());   
                    
                    if(NumberUtils.isNumber(strCell)&&(propertyDataType[j].indexOf("Integer")>=0)){
                        Number ncellNormol;
                        if(strCell.indexOf(".")>=0){                        	
                            ncellNormol = new Number(this.startColumn + j, i, Double.parseDouble(strCell), this.getNormolFormat()); 
                        }else{
                            ncellNormol = new Number(this.startColumn + j, i, Long.parseLong(strCell), this.getNormolFormat());                            
                        }   
                        wsheet.addCell(ncellNormol);
                    }else{
                        wsheet.addCell(cellNormol);
                    }                   
                }
                
                if (intHeight > 0) {
                    wsheet.setRowView(i, intHeight);
                }
            }
            
            book.close();
            wbook.write();
            wbook.close();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (RowsExceededException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (WriteException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (BiffException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return excelFile;
    }
    public File getExcelfileByDynamicTitle() {
        try {
            /*
             * 在支持多页的过程中，对于每个工作薄不能每次都要从新建立　Workbook.createWorkbook(this.excelFile);
             * 要从传进入的文件的基础上进行修改
             * Workbook book = Workbook.getWorkbook(this.excelFile);  
             * wbook = Workbook.createWorkbook(this.excelFile,book); 
             * 但是对于刚刚建立的文件，this.excelFile.createNewFile();