jxl 写 excel

package com.jandar.sungovernment.util;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.ParameterizedType;
import java.util.List;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.poi.hssf.record.formula.functions.T;
import org.jfree.util.Log;

import com.jandar.sungovernment.domain.NO_Cqbqh;
import com.jandar.sungovernment.domain.NO_QualityDetect;
import com.jandar.sungovernment.domain.NO_SubscriptionInformation;
/**
 * @author cmtony   2009-7-10
 *
 */
public class Jxl {
   
    /**
     * @param datas 封装着Object[]的列表, 一般是String内容.
     * @param title 每个sheet里的标题.
     * @throws IOException
     */
  int charTitle = 15;// 标题字体大小
  int charNormal = 10;// 标题字体大小

 jxl.write.WritableFont titleFont = new jxl.write.WritableFont(
       WritableFont.createFont("宋体"), charTitle, WritableFont.BOLD);
 jxl.write.WritableFont endFont = new jxl.write.WritableFont(
       WritableFont.createFont("宋体"), charTitle, WritableFont.BOLD,false,jxl.format.UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.RED);
 jxl.write.WritableCellFormat titleFormat = new jxl.write.WritableCellFormat( titleFont);
 jxl.write.WritableCellFormat endFormat = new jxl.write.WritableCellFormat( endFont);
 
 
 
    @SuppressWarnings("unchecked")
 public void writeExcel(OutputStream out, List datas, String[] title,String[] first) throws IOException {
        if(datas == null) {
            throw new IllegalArgumentException("写excel流需要List参数!");
        }
        try {
            WritableWorkbook workbook = Workbook.createWorkbook(out);
            WritableSheet ws = workbook.createSheet("sheet 1", 0);
            int rowNum = 0;    //要写的行
           
            int num = 0;//总的份数
            double ysk = 0.00;//应收款
            double yjk = 0.00;//已交款
           
            if(first !=null){
             Label cell = new Label(2, 0, ""+first[0],titleFormat);
                ws.addCell(cell);
//            putRow(ws, 0, first);//压入标题
               rowNum = 1;
           }
           if(title != null) {
               putRow(ws, 1, title);//压入标题
               rowNum = 2;
           }
            for(int i=0; i<datas.size(); i++, rowNum++) {//写sheet
             NO_SubscriptionInformation info = (NO_SubscriptionInformation) datas.get(i);
             String[] str = {info.getName(),info.getTel(),info.getUmber(),info.getYsMoney(),info.getYjMoney(),info.getZdPeople(),info.getIssuedDate(),info.getSbDate(),info.getAddress(),info.getRemarks()};
             putRow(ws, rowNum, str);    // 压一行到sheet
             num += Integer.valueOf(info.getUmber());
             ysk += Integer.valueOf(info.getYsMoney());
             yjk += Integer.valueOf(info.getYjMoney());
            }
           
            Label cell1 = new Label(0,ws.getRows()+1,"份数总计 :"+ num+"份",endFormat);
            Label cell2 = new Label(3,ws.getRows()+1,"应收款总计: "+ ysk+"元",endFormat);
            Label cell3 = new Label(7,ws.getRows()+1,"已交款总计 :"+ yjk+"元",endFormat);
            ws.addCell(cell1);
            ws.addCell(cell2);
            ws.addCell(cell3);
           
            workbook.write();
            workbook.close();    //一定要关闭, 否则没有保存Excel
          
        } catch (RowsExceededException e) {
           Log.warn("jxl write RowsExceededException: "+e.getMessage());
        } catch (WriteException e) {
         Log.warn("jxl write WriteException: "+e.getMessage());
        } catch (IOException e) {
         Log.warn("jxl write file i/o exception!, cause by: "+e.getMessage());
        }
    }
   
   
    public void writeExcelQuality(OutputStream out, List datas, String[] title,String[] first) throws IOException {
     if(datas == null) {
            throw new IllegalArgumentException("写excel流需要List参数!");
        }
        try {
            WritableWorkbook workbook = Workbook.createWorkbook(out);
            WritableSheet ws = workbook.createSheet("sheet 1", 0);
 //           WritableSheet ws1 = workbook.createSheet("sheet 1", 1);
            int rowNum = 0;    //要写的行
            double cfje = 0.00;
            double jlje = 0.00;
            if(first !=null){
              Label cell = new Label(2, 0, ""+first[0],titleFormat);
                 ws.addCell(cell);
//             putRow(ws, 0, first);//压入标题
                rowNum = 1;
            }
            if(title != null) {
                putRow(ws, 1, title);//压入标题
                rowNum = 2;
            }
            for(int i=0; i<datas.size(); i++, rowNum++) {//写sheet
             NO_QualityDetect info = (NO_QualityDetect) datas.get(i);
             String[] str = {info.getCaption(),info.getDutyPerson(),String.valueOf(info.getPunishMoney()),info.getCheckPerson(),String.valueOf(info.getRewardsMoney()),info.getDate(),info.getOperatePerson(),info.getQualityDetectTime()};
             cfje += info.getPunishMoney();
             jlje += info.getRewardsMoney();
             putRow(ws, rowNum, str);    // 压一行到sheet
            }
            Label cell1 = new Label(1,ws.getRows()+1,"处罚金额总计: "+ cfje+"元",endFormat);
            Label cell2 = new Label(4,ws.getRows()+1,"奖励金额总计 :"+ jlje+"元",endFormat);
            ws.addCell(cell1);
            ws.addCell(cell2);
           
            workbook.write();
            workbook.close();    //一定要关闭, 否则没有保存Excel
          
        } catch (RowsExceededException e) {
         Log.warn("jxl write RowsExceededException: "+e.getMessage());
        } catch (WriteException e) {
         Log.warn("jxl write WriteException: "+e.getMessage());
        } catch (IOException e) {
         Log.warn("jxl write file i/o exception!, cause by: "+e.getMessage());
        }
    }
   
    public void writeExcelCqh(OutputStream out, List datas, String[] title,String[] first) throws IOException {
        if(datas == null) {
            throw new IllegalArgumentException("写excel流需要List参数!");
        }
        try {
            WritableWorkbook workbook = Workbook.createWorkbook(out);
            WritableSheet ws = workbook.createSheet("sheet 1", 0);
 //           WritableSheet ws1 = workbook.createSheet("sheet 1", 1);
            int rowNum = 0;    //要写的行
            if(first !=null){
              Label cell = new Label(2, 0, ""+first[0],titleFormat);
                 ws.addCell(cell);
//             putRow(ws, 0, first);//压入标题
                rowNum = 1;
            }
            if(title != null) {
                putRow(ws, 1, title);//压入标题
                rowNum = 2;
            }
            for(int i=0; i<datas.size(); i++, rowNum++) {//写sheet
             NO_Cqbqh info = (NO_Cqbqh) datas.get(i);
             String[] str = {info.getAttendeeName(),info.getCoverTask(),info.getDiscussSituation(),info.getSignIn(),info.getDate(),info.getEntryPerson(),info.getEntryTime()};
             putRow(ws, rowNum, str);    // 压一行到sheet
            }
            workbook.write();
            workbook.close();    //一定要关闭, 否则没有保存Excel
          
        } catch (RowsExceededException e) {
         Log.warn("jxl write RowsExceededException: "+e.getMessage());
        } catch (WriteException e) {
         Log.warn("jxl write WriteException: "+e.getMessage());
        } catch (IOException e) {
         Log.warn("jxl write file i/o exception!, cause by: "+e.getMessage());
        }
    }
   
   
   

  
   
   
    protected T entity;
   
    protected Class<T> entityClass;
   
    /**
     * 导出excel的通用方法
     * @param out，   输出流
     * @param datas   要输出的数据
     * @param title   主题
     * @param heading  要输出的字段标题
     * @throws IOException
     * @throws WriteException
     * @throws RowsExceededException
     */
    public void commonWriteExcel(OutputStream out, List<T> datas, String[] title,int Trow,int Tcol,String[] heading,int Hrow,int Hcol)
            throws IOException, RowsExceededException, WriteException{
     if(datas == null){
      throw new IllegalArgumentException("写excel流需要List参数!");
     }
     WritableSheet ws = this.createWorkBook(out);
     int rowNum = 0;
     this.writeTitleHeading(rowNum,ws,title, Trow, Tcol, heading);
     
     for (int i = 0; i < datas.size(); i++) {
   T t = datas.get(i);
  }
     
    }
   
    /**
     * 创建excel工作簿的的方法
     * @param out
     * @return
     * @throws IOException
     */
    private WritableSheet createWorkBook(OutputStream out) throws IOException{
      WritableWorkbook workbook = Workbook.createWorkbook(out);
         WritableSheet ws = workbook.createSheet("sheet 1", 0);
         return ws;
    }
   
    /**
     * 往工作簿中写入主题和标题
     * @param ws
     * @param title
     * @param heading
     * @throws WriteException
     * @throws RowsExceededException
     */
    private void writeTitleHeading(int rowNum,WritableSheet ws,String[] title,int Trow,int Tcol,String[] heading) throws RowsExceededException, WriteException{
     if(title == null&& heading != null){
      Label cell = new Label(Trow,Tcol,heading[0],this.titleFormat);
      ws.addCell(cell);
      rowNum = 1;
     }
     if(title != null&& heading != null){
      this.putRow(ws, rowNum, title);
      rowNum += 1;
      Label cell = new Label(Trow,Tcol,heading[0],this.titleFormat);
      ws.addCell(cell);
     }
    }
   
   
    private void putRow(WritableSheet ws, int rowNum, Object[] cells) throws RowsExceededException, WriteException {
        for(int j=0; j<cells.length; j++) {//写一行
            Label cell = new Label(j, rowNum, ""+cells[j]);
            ws.addCell(cell);
        }
    }
   
   
    public Jxl() {
  try {
   entityClass = (Class<T>) ((ParameterizedType) getClass()
     .getGenericSuperclass()).getActualTypeArguments()[0];
  } catch (Exception ex) {
   System.out.println("无法获取模型的类");
  }
 }
   
   
}