JAVA 操作Excel工具类

　　

　　Bean转Excel对象

　　

/*
 * 文件名：BeanToExcel.java
 */

import java.util.ArrayList;
import java.util.List;

import jxl.Sheet;
import jxl.write.WritableCell;
import jxl.write.WritableSheet;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;



/**
 * TODO 添加类的一句话简单描述。
 */
public class BeanToExcel<T> {
    
    private WritableSheet wrightsheet;
    private Sheet readSheet;
    private List<T> beanList;
    private Mapper<T> mapper;
    private List<WritableCell> needChangeCells = new ArrayList<WritableCell>();
    
    public BeanToExcel(WritableSheet sheet, List<T> beanList, Mapper<T> mapper){
        this.wrightsheet = sheet;
        this.beanList = beanList;
        this.mapper = mapper;
    }
    
    public BeanToExcel(Sheet sheet, Mapper<T> mapper){
        this.readSheet = sheet;        
        this.mapper = mapper;
        this.beanList = new ArrayList<T>();
    }
    
    public void writeExcel() throws Exception{
        try {
            write();
        } catch (RowsExceededException e) {
            DEBUGGER.error("Failed to writeExcel", e);
            throw e;
        } catch (WriteException e) {
            DEBUGGER.error("Failed to writeExcel", e);
            throw e;
        }
    }
    
    public List<T> readBeans(){
        for(int i=1; i<readSheet.getRows(); i++){
            try {
                T bean = mapper.toBean(readSheet.getRow(i));
                if (bean instanceof ExcelInfo) {
                    ((ExcelInfo) bean).setRow(i);
                    ((ExcelInfo) bean).setReadSheet(readSheet);
                }
                if (bean != null){
                    beanList.add(bean);
                }
            } catch (ExcelException e) {
                needChangeCells.addAll(e.getCellList());
            }
        }
        return beanList;
    }
    
    protected void write() throws RowsExceededException, WriteException{
        for(int i = 0 ; i < beanList.size(); i++){
            T bean = beanList.get(i);
            List<WritableCell> line = mapper.toRow(i+1, bean);
            for(WritableCell cell : line){
                wrightsheet.addCell(cell);
            }
        }
    }

    public List<WritableCell> getNeedChangeCells() {
        return needChangeCells;
    }
    
}

　　

　　Excel.java

　　

/*
 * 文件名：ExcelInfo.java
 */

import jxl.Sheet;

/**
 * ExcleInfo
 */
public class ExcelInfo
{
    
    private int row;
    
    private Sheet readSheet;
    
    public int getRow()
    {
        return row;
    }
    
    public void setRow(int row)
    {
        this.row = row;
    }
    
    public Sheet getReadSheet()
    {
        return readSheet;
    }
    
    public void setReadSheet(Sheet readSheet)
    {
        this.readSheet = readSheet;
    }
}

　　ExcelException.java

　　

/*
 * 文件名：ExcelException.java
 */

import java.util.List;

import jxl.write.WritableCell;

/**
 * TODO 添加类的一句话简单描述。
 */
public class ExcelException extends RuntimeException {

    private static final long serialVersionUID = -3113079946804687851L;
    
    public static ExcelException DEFAULT = new ExcelException("未知异常");
    
    private String msg;
    private List<WritableCell> cellList; 

    public ExcelException(String msg) {
        this.msg = msg;
    }
    
    public ExcelException(String msg, List<WritableCell> cellList) {
        this.msg = msg;
        this.cellList = cellList;
    }

    public ExcelException(String msg, StackTraceElement[] e, List<WritableCell> cellList) {
        this.msg = msg;
        this.cellList = cellList;
        this.setStackTrace(e);
    }

    @Override
    public String getMessage() {
        return this.msg;
    }

    public List<WritableCell> getCellList() {
        return cellList;
    }

}

　　Mapper.java

　　

/*
 * 文件名：Mapper.java
 */

import java.util.List;

import jxl.Cell;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WriteException;


/**
 * TODO 添加类的一句话简单描述。
 */
public abstract class Mapper<T> {
    
   22     
    public abstract List<WritableCell> toRow(int row,T t);
    
    public abstract T toBean(Cell[] rowCells) throws ExcelException;
    
    public WritableCell createCell(int column, int row,  String content){
        Label label = new Label(column, row, content);
        return label;
    }
    
    public WritableCell createErrorCell(int column, int row, String content){
        Label label = new Label(column, row, content);
        WritableCellFormat cellFormat = new WritableCellFormat();
        try {
            cellFormat.setBackground(Colour.YELLOW);
            label.setCellFormat(cellFormat);
        } catch (WriteException e) {
            DEBUGGER.error("Failed to createErrorCell", e);
        }
        return label;
    }47 }