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("无法获取模型的类");
}
}
}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
