导出excel

package com.gctx.util;

import java.io.File;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.gctx.framework.logs.Logit;
import com.zr.pojo.aged.cc.ZagccUsers;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

/*****************************************************
*
*@auther
*@since 2013-1-19
****************************************************/
//封装的类
public class ExcelUtility {
private Colour myBlue = Colour.PALE_BLUE;
private Colour myWhite = Colour.WHITE;
private Colour myGreen = Colour.GREEN;

//宋体,20号字,加粗
private WritableFont title1Wf = new WritableFont(WritableFont.createFont("宋体"),20,WritableFont.BOLD);
//宋体,14号字,加粗
private WritableFont title2Wf = new WritableFont(WritableFont.createFont("宋体"),14,WritableFont.BOLD);
//宋体,11号字,加粗
private WritableFont fieldNameWf = new WritableFont(WritableFont.createFont("宋体"),11,WritableFont.BOLD);
//宋体,1号字
private WritableFont dataWf = new WritableFont(WritableFont.createFont("宋体"),11);
//宋体,11号字,加粗,绿色
private WritableFont fieldNameWfGreen = new WritableFont(WritableFont.createFont("宋体"),11,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,myGreen);
//宋体,11号字,加粗,红色
private WritableFont fieldNameWfRed = new WritableFont(WritableFont.createFont("宋体"),11,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.RED);
//宋体,11号字,红色
private WritableFont dataWfRed = new WritableFont(WritableFont.createFont("宋体"),11,WritableFont.NO_BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.RED);

private WritableCellFormat tableTitle1 = new WritableCellFormat(title1Wf);
private WritableCellFormat tableTitle2 = new WritableCellFormat(title2Wf);
private WritableCellFormat tableTitle3 = new WritableCellFormat(title2Wf);
private WritableCellFormat fileNameL = new WritableCellFormat(fieldNameWf);
private WritableCellFormat fileNameM = new WritableCellFormat(fieldNameWf);
private WritableCellFormat fileNameBL = new WritableCellFormat(fieldNameWf);
private WritableCellFormat fileNameBM = new WritableCellFormat(fieldNameWf);
private WritableCellFormat fdataL = new WritableCellFormat(dataWf);
private WritableCellFormat fdataM = new WritableCellFormat(dataWf);
private WritableCellFormat fdataR = new WritableCellFormat(dataWf);

//正常数据
private WritableCellFormat dataL = new WritableCellFormat(dataWf);
private WritableCellFormat dataM = new WritableCellFormat(dataWf);
private WritableCellFormat dataR = new WritableCellFormat(dataWf);
private WritableCellFormat dataBL = new WritableCellFormat(dataWf);
private WritableCellFormat dataBM = new WritableCellFormat(dataWf);
private WritableCellFormat dataBR = new WritableCellFormat(dataWf);
//异常数据
private WritableCellFormat edataM = new WritableCellFormat(dataWfRed);
private WritableCellFormat edataR = new WritableCellFormat(dataWfRed);
private WritableCellFormat edataBM = new WritableCellFormat(dataWfRed);
private WritableCellFormat edataBR = new WritableCellFormat(dataWfRed);

//通过率数据>=90%字体
private WritableCellFormat passGreen = new WritableCellFormat(fieldNameWfGreen);
//通过率数据<90%字体
private WritableCellFormat passRed = new WritableCellFormat(fieldNameWfRed);

public ExcelUtility(){
try {
tableTitle1.setAlignment(Alignment.CENTRE);
tableTitle1.setVerticalAlignment(VerticalAlignment.CENTRE);
tableTitle1.setBorder(Border.TOP, BorderLineStyle.THICK);
tableTitle1.setBorder(Border.BOTTOM, BorderLineStyle.THIN,myBlue);
tableTitle1.setBorder(Border.LEFT, BorderLineStyle.THICK);
tableTitle1.setBorder(Border.RIGHT, BorderLineStyle.THICK);
tableTitle1.setBackground(myBlue);

tableTitle2.setAlignment(Alignment.CENTRE);
tableTitle2.setVerticalAlignment(VerticalAlignment.CENTRE);
tableTitle2.setBorder(Border.BOTTOM, BorderLineStyle.THICK);
tableTitle2.setBorder(Border.LEFT, BorderLineStyle.THICK);
tableTitle2.setBorder(Border.RIGHT, BorderLineStyle.THICK);
tableTitle2.setBackground(myBlue);

tableTitle3.setAlignment(Alignment.CENTRE);
tableTitle3.setVerticalAlignment(VerticalAlignment.CENTRE);
tableTitle3.setBorder(Border.BOTTOM, BorderLineStyle.THICK);
tableTitle3.setBorder(Border.LEFT, BorderLineStyle.THICK);
tableTitle3.setBorder(Border.RIGHT, BorderLineStyle.THICK);

fileNameL.setAlignment(Alignment.CENTRE);
fileNameL.setVerticalAlignment(VerticalAlignment.CENTRE);
fileNameL.setBorder(Border.BOTTOM, BorderLineStyle.THIN);
fileNameL.setBorder(Border.LEFT, BorderLineStyle.THICK);
fileNameL.setBorder(Border.RIGHT, BorderLineStyle.THIN);
fileNameL.setBackground(myBlue);

fileNameM.setAlignment(Alignment.CENTRE);
fileNameM.setVerticalAlignment(VerticalAlignment.CENTRE);
fileNameM.setBorder(Border.BOTTOM, BorderLineStyle.THIN);
fileNameM.setBorder(Border.LEFT, BorderLineStyle.THIN);
fileNameM.setBorder(Border.RIGHT, BorderLineStyle.THIN);
fileNameM.setBackground(myBlue);

fileNameBL.setAlignment(Alignment.CENTRE);
fileNameBL.setVerticalAlignment(VerticalAlignment.CENTRE);
fileNameBL.setBorder(Border.BOTTOM, BorderLineStyle.THICK);
fileNameBL.setBorder(Border.LEFT, BorderLineStyle.THICK);
fileNameBL.setBorder(Border.RIGHT, BorderLineStyle.THIN);
fileNameBL.setBackground(myBlue);

fileNameBM.setAlignment(Alignment.CENTRE);
fileNameBM.setVerticalAlignment(VerticalAlignment.CENTRE);
fileNameBM.setBorder(Border.BOTTOM, BorderLineStyle.THICK);
fileNameBM.setBorder(Border.LEFT, BorderLineStyle.THIN);
fileNameBM.setBorder(Border.RIGHT, BorderLineStyle.THIN);
fileNameBM.setBackground(myBlue);

fdataL.setAlignment(Alignment.CENTRE);
fdataL.setVerticalAlignment(VerticalAlignment.CENTRE);
fdataL.setBorder(Border.BOTTOM, BorderLineStyle.THIN);
fdataL.setBorder(Border.LEFT, BorderLineStyle.THICK);
fdataL.setBorder(Border.RIGHT, BorderLineStyle.THIN);
fdataL.setBackground(myBlue);

fdataM.setAlignment(Alignment.CENTRE);
fdataM.setVerticalAlignment(VerticalAlignment.CENTRE);
fdataM.setBorder(Border.BOTTOM, BorderLineStyle.THIN);
fdataM.setBorder(Border.LEFT, BorderLineStyle.THIN);
fdataM.setBorder(Border.RIGHT, BorderLineStyle.THIN);
fdataM.setBackground(myBlue);

fdataR.setAlignment(Alignment.CENTRE);
fdataR.setVerticalAlignment(VerticalAlignment.CENTRE);
fdataR.setBorder(Border.BOTTOM, BorderLineStyle.THIN);
fdataR.setBorder(Border.LEFT, BorderLineStyle.THIN);
fdataR.setBorder(Border.RIGHT, BorderLineStyle.THICK);
fdataR.setBackground(myBlue);

dataL.setAlignment(Alignment.CENTRE);
dataL.setVerticalAlignment(VerticalAlignment.CENTRE);
dataL.setBorder(Border.BOTTOM, BorderLineStyle.THIN);
dataL.setBorder(Border.LEFT, BorderLineStyle.THICK);
dataL.setBorder(Border.RIGHT, BorderLineStyle.THIN);
dataL.setBackground(myWhite);

dataM.setAlignment(Alignment.CENTRE);
dataM.setVerticalAlignment(VerticalAlignment.CENTRE);
dataM.setBorder(Border.BOTTOM, BorderLineStyle.THIN);
dataM.setBorder(Border.LEFT, BorderLineStyle.THIN);
dataM.setBorder(Border.RIGHT, BorderLineStyle.THIN);
dataM.setBackground(myWhite);

dataR.setAlignment(Alignment.CENTRE);
dataR.setVerticalAlignment(VerticalAlignment.CENTRE);
dataR.setBorder(Border.BOTTOM, BorderLineStyle.THIN);
dataR.setBorder(Border.LEFT, BorderLineStyle.THIN);
dataR.setBorder(Border.RIGHT, BorderLineStyle.THICK);
dataR.setBackground(myWhite);

dataBL.setAlignment(Alignment.CENTRE);
dataBL.setVerticalAlignment(VerticalAlignment.CENTRE);
dataBL.setBorder(Border.BOTTOM, BorderLineStyle.THICK);
dataBL.setBorder(Border.LEFT, BorderLineStyle.THICK);
dataBL.setBorder(Border.RIGHT, BorderLineStyle.THIN);
dataBL.setBackground(myWhite);

dataBM.setAlignment(Alignment.CENTRE);
dataBM.setVerticalAlignment(VerticalAlignment.CENTRE);
dataBM.setBorder(Border.BOTTOM, BorderLineStyle.THICK);
dataBM.setBorder(Border.LEFT, BorderLineStyle.THIN);
dataBM.setBorder(Border.RIGHT, BorderLineStyle.THIN);
dataBM.setBackground(myWhite);

dataBR.setAlignment(Alignment.CENTRE);
dataBR.setVerticalAlignment(VerticalAlignment.CENTRE);
dataBR.setBorder(Border.BOTTOM, BorderLineStyle.THICK);
dataBR.setBorder(Border.LEFT, BorderLineStyle.THIN);
dataBR.setBorder(Border.RIGHT, BorderLineStyle.THICK);
dataBR.setBackground(myWhite);

edataM.setAlignment(Alignment.CENTRE);
edataM.setVerticalAlignment(VerticalAlignment.CENTRE);
edataM.setBorder(Border.BOTTOM, BorderLineStyle.THIN);
edataM.setBorder(Border.LEFT, BorderLineStyle.THIN);
edataM.setBorder(Border.RIGHT, BorderLineStyle.THIN);
edataM.setBackground(myWhite);

edataR.setAlignment(Alignment.CENTRE);
edataR.setVerticalAlignment(VerticalAlignment.CENTRE);
edataR.setBorder(Border.BOTTOM, BorderLineStyle.THIN);
edataR.setBorder(Border.LEFT, BorderLineStyle.THIN);
edataR.setBorder(Border.RIGHT, BorderLineStyle.THICK);
edataR.setBackground(myWhite);

edataBM.setAlignment(Alignment.CENTRE);
edataBM.setVerticalAlignment(VerticalAlignment.CENTRE);
edataBM.setBorder(Border.BOTTOM, BorderLineStyle.THICK);
edataBM.setBorder(Border.LEFT, BorderLineStyle.THIN);
edataBM.setBorder(Border.RIGHT, BorderLineStyle.THIN);
edataBM.setBackground(myWhite);

edataBR.setAlignment(Alignment.CENTRE);
edataBR.setVerticalAlignment(VerticalAlignment.CENTRE);
edataBR.setBorder(Border.BOTTOM, BorderLineStyle.THICK);
edataBR.setBorder(Border.LEFT, BorderLineStyle.THIN);
edataBR.setBorder(Border.RIGHT, BorderLineStyle.THICK);
edataBR.setBackground(myWhite);

passGreen.setAlignment(Alignment.CENTRE);
passGreen.setVerticalAlignment(VerticalAlignment.CENTRE);
passGreen.setBorder(Border.BOTTOM, BorderLineStyle.THICK);
passGreen.setBorder(Border.LEFT, BorderLineStyle.THIN);
passGreen.setBorder(Border.RIGHT, BorderLineStyle.THICK);
passGreen.setBackground(myWhite);

passRed.setAlignment(Alignment.CENTRE);
passRed.setVerticalAlignment(VerticalAlignment.CENTRE);
passRed.setBorder(Border.BOTTOM, BorderLineStyle.THICK);
passRed.setBorder(Border.LEFT, BorderLineStyle.THIN);
passRed.setBorder(Border.RIGHT, BorderLineStyle.THICK);
passRed.setBackground(myWhite);
} catch (WriteException e) {
Logit.errorLog(e.getMessage(), new Throwable(e));
}
}

public File getExcel(String excelName,String reportTitle,String smallTitleName1,String smallTitleName2,Object objUserBean,
Map<Integer, String> nameMap,Map<Integer, Integer> countMap,List<Integer> beanKeyList,String path){
String targetFileName = null;
WritableWorkbook workbook = null;
boolean readWorkBookFlag = true;
String shortName = "控制中心";

File f = null ;
File directory = null;
try {
targetFileName = shortName+"-" + excelName + ".xls";
f = new File(path+"/decorators/resource/"+targetFileName);
WritableSheet sheet = null;
if(f != null && f.exists()){
readWorkBookFlag = false;
return f;
}
directory = f.getParentFile();
if(!directory.exists()){
directory.mkdirs();
}
//创建一个Excel文件
workbook = Workbook.createWorkbook(f);
Map<String,Integer> sheetNameMap = new HashMap<String, Integer>();
String sheetName ="("+ excelName +")";
if(!sheetNameMap.containsKey(sheetName)){
sheetNameMap.put(sheetName,1);
}else{
sheetNameMap.put(sheetName,sheetNameMap.get(sheetName)+1);
sheetName = sheetName+sheetNameMap.get(sheetName);
}
sheet = workbook.createSheet(sheetName, 1);


excelConstructorInit(reportTitle,smallTitleName1,smallTitleName2,sheet);
excelDataInit(objUserBean,nameMap,countMap,beanKeyList,sheet);


workbook.write();
return f;
} catch (Exception e) {
Logit.errorLog(e.getMessage(), new Throwable(e));
}finally{
try {
if(workbook != null && readWorkBookFlag){
workbook.close();
}
} catch (Exception e) {
Logit.errorLog(e.getMessage(), new Throwable(e));
}
}
return null;
}

private void excelConstructorInit(String reportTitle,String smallTitleName1,String smallTitleName2,
WritableSheet sheet)throws Exception {
sheet.setColumnView(1, 26);
sheet.setColumnView(2, 26);
sheet.setColumnView(3, 26);
// sheet.setColumnView(4, 26);
sheet.setRowView(1, 525);
sheet.setRowView(2, 300);
sheet.setRowView(3, 480);
sheet.setRowView(4, 450);
// sheet.setRowView(4, 300);
// sheet.setRowView(5, 300);
// sheet.setRowView(6, 300);
// sheet.setRowView(7, 300);
// sheet.setRowView(8, 300);
// sheet.setRowView(9, 300);
// sheet.setRowView(10, 450);
// sheet.setRowView(11, 300);
Label title = null;
// Label fieldName = null;
Label data = null;
title = new Label(1, 1, "控制中心数据报表", tableTitle1); //Label(列,实际行-1,内容,样式);
sheet.addCell(title);
sheet.mergeCells(1, 1, 3, 1); //mergeCells(第几张表,实际行-1,纵列数,实际行-1);
// title = new Label(1, 2, " by zaghome", tableTitle2);
// sheet.addCell(title);
// sheet.mergeCells(1, 2, 3, 2);
title = new Label(1, 2, reportTitle, tableTitle3);
sheet.addCell(title);
sheet.mergeCells(1, 2, 3, 2);
// title = new Label(1, 10, "Data Ratios", tableTitle3);
// sheet.addCell(title);
// sheet.mergeCells(1, 10, 2, 10);
//
// fieldName = new Label(1, 4, "应用名称", fileNameL);
// sheet.addCell(fieldName);
// fieldName = new Label(1, 5, "测试终端", fileNameL);
// sheet.addCell(fieldName);
// fieldName = new Label(1, 6, "运营商", fileNameL);
// sheet.addCell(fieldName);
// fieldName = new Label(1, 7, "IP地址", fileNameL);
// sheet.addCell(fieldName);
// fieldName = new Label(1, 8, "测试地点", fileNameL);
// sheet.addCell(fieldName);
// fieldName = new Label(1, 9, "执行总次数", fileNameBL);
// sheet.addCell(fieldName);
//
//
// fieldName = new Label(3, 4, "监控内容", fileNameM);
// sheet.addCell(fieldName);
// fieldName = new Label(3, 5, "终端平台版本", fileNameM);
// sheet.addCell(fieldName);
// fieldName = new Label(3, 6, "手机网络环境", fileNameM);
// sheet.addCell(fieldName);
// fieldName = new Label(3, 7, "测试网络", fileNameM);
// sheet.addCell(fieldName);
// fieldName = new Label(3, 8, "监控执行日期", fileNameM);
// sheet.addCell(fieldName);
// fieldName = new Label(3, 9, "成功率", fileNameBM);
// sheet.addCell(fieldName);
//
// fieldName = new Label(2, 4, "test", dataM);
// sheet.addCell(fieldName);
// fieldName = new Label(2, 5, "test", dataM);
// sheet.addCell(fieldName);
// fieldName = new Label(2, 6, "中国移动", dataM);
// sheet.addCell(fieldName);
// fieldName = new Label(2, 7, "test", dataM);
// sheet.addCell(fieldName);
// String address = "test地址";
// fieldName = new Label(2, 8, address , dataM);
// sheet.addCell(fieldName);
//
// fieldName = new Label(4, 4, "test", dataR);
// sheet.addCell(fieldName);
// fieldName = new Label(4, 5, "test", dataR);
// sheet.addCell(fieldName);
// fieldName = new Label(4, 6, "神州行（5元卡）", dataR);
// sheet.addCell(fieldName);
// fieldName = new Label(4, 7, "4M共享(联通）", dataR);
// sheet.addCell(fieldName);
// fieldName = new Label(4, 8,  "test", dataR);
// sheet.addCell(fieldName);

data = new Label(1, 3, "序列号", fdataL);
sheet.addCell(data);
data = new Label(2, 3, smallTitleName1, fdataM);
sheet.addCell(data);
data = new Label(3, 3, smallTitleName2, fdataM);
sheet.addCell(data);
}

private void excelDataInit(Object objUserBean,Map<Integer, String> nameMap,Map<Integer, Integer> countMap,
List<Integer> beanKeyList,WritableSheet sheet)throws Exception{
Label data = null;
int successTotal = 0;
//数据起始行数
int dataRowBegin = 4; //12
int nowDataRow = -1;
// Object[] namekeyarr = nameMap.keySet().toArray();
for (int i = 0; i < beanKeyList.size(); i++) {
nowDataRow = dataRowBegin + i;
sheet.setRowView(nowDataRow, 300);
//控制数据显示（是不是最后一行）
if( i != beanKeyList.size() - 1 ){ //不是最后一行的数据（格式A-下边框线为细）
data = new Label(1,nowDataRow, new Integer(i+1).toString(), dataL);
sheet.addCell(data);
//如果开始时间为异常
data = new Label(2,nowDataRow, nameMap.get(beanKeyList.get(i)), dataM);
sheet.addCell(data);
data = new Label(3,nowDataRow, new Integer(countMap.get(beanKeyList.get(i))).toString(), dataM);
sheet.addCell(data);
successTotal++;
continue;
}else{ //最后一行的数据(格式B-下边框线为粗）
data = new Label(1,nowDataRow, new Integer(i+1).toString(), dataL);
sheet.addCell(data);
data = new Label(2,nowDataRow, nameMap.get(beanKeyList.get(i)), dataM);
sheet.addCell(data);
data = new Label(3,nowDataRow, new Integer(countMap.get(beanKeyList.get(i))).toString(), dataM);
sheet.addCell(data);
successTotal++;

//Label(列,实际行-1,内容,样式);
//mergeCells(第几张表,实际行-1,纵列数,实际行-1);

data = new Label(1,nowDataRow+1, "备注", dataL);
sheet.addCell(data);
data = new Label(2,nowDataRow+1, "操作员： "+((ZagccUsers) objUserBean).getUser_name(), dataM);
sheet.addCell(data);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
data = new Label(3,nowDataRow+1, "导出时间: "+sdf.format(new Date()), dataM);
sheet.addCell(data);
}
}
}

}


//实际调用
/**
* 教育程度报表的导出
* @return
*/
public String exportEduExcel(){
List<ZaghEduLevelOlderCount> eduOlderCounts = iedulevelservice
.getEduLevelOlderCount();
List<ZaghEduLevel> eduLevels = iedulevelservice.getAllEduLevelList();
ZagccUsers userBean=(ZagccUsers) this.getSession().getAttribute("ccuserbean");
String excelName = "教育程度统计老人";
String reportTitle = "教育程度老人数量统计表";
String smallTitleName1 = "教育程度名称";
String smallTitleName2 = "老人数量";
Map<Integer, String> nameMap=new HashMap<Integer, String>();
Map<Integer, Integer> countMap=new HashMap<Integer, Integer>();
List<Integer> beanKey=new ArrayList<Integer>();
if(eduOlderCounts!=null&&eduOlderCounts.size()>0){
for (ZaghEduLevelOlderCount zagheduleveloldercount : eduOlderCounts) {
if(eduLevels!=null&&eduLevels.size()>0){
for (ZaghEduLevel zaghedulevel : eduLevels) {
if(zagheduleveloldercount.getEdu_id()==zaghedulevel.getId()){
nameMap.put(zagheduleveloldercount.getEdu_id(), zaghedulevel.getEdu_level_name());
countMap.put(zagheduleveloldercount.getEdu_id(), zagheduleveloldercount.getEdu_older_count());
beanKey.add(zagheduleveloldercount.getEdu_id());

}
}

}
}

}
ExcelUtility utility=new ExcelUtility();
String path="D:/excel";
File file=utility.getExcel(excelName, reportTitle, smallTitleName1, smallTitleName2, userBean,
nameMap, countMap, beanKey,path);
FileInputStream fis=null;
try {
fis=new FileInputStream(file);
int i=fis.available();
this.getResponse().reset();
this.getResponse().setContentType("APPLICATION/X-DOWNLOAD");
this.getResponse().setHeader("Accept-Ranges", "bytes");
this.getResponse().setHeader("Accept-Length", String.valueOf(i));
this.getResponse().setHeader("Content-Disposition", "attachment;filename="+
new String(file.getName().getBytes("gb2312"),"ISO8859-1"));
byte [] bytes=new byte[4096];
int  length=-1;
while((length=fis.read(bytes))!=-1){
this.getResponse().getOutputStream().write(bytes,0,length);
this.getResponse().flushBuffer();
}

} catch (Exception e) {
Logit.debugLog("getReport send to client is error!");
return null;
}finally {
try {
fis.close();
} catch (IOException e) {
Logit.debugLog("getReport file's stream is null");
}
}
return null;
}