1、所需jar
jxl-2.6.10.jar
jxls-core-1.0-RC-3.jar
jxls-reader-1.0-RC-3.jar
2、 excel修改行宽度封装
SheetColumn.java
package com.tp.soft.common.excel; import jxl.write.WritableSheet; /**
* excel修改行宽度
* @author taop
*
*/
public interface SheetColumn { public abstract void setColumnView(WritableSheet sheet) ;
}
3、excel生成封装
ExcelExportUtils.java
package com.tp.soft.common.excel; import java.util.List;
import java.util.Map; import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse; import jxl.CellView;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.VerticalAlignment;
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 com.sun.xml.internal.messaging.saaj.packaging.mime.internet.MimeUtility; /******************************************************************************
* @Package: [com.tp.soft.common.excel.java]
* @ClassName: [XmlExportUtis]
* @Description: [动态生成XML]
* @Author: [taop]
* @CreateDate: [2014-3-31 下午2:32:51]
* @UpdateUser: [taop(如多次修改保留历史记录，增加修改记录)]
* @UpdateDate: [2014-3-31 下午2:32:51，(如多次修改保留历史记录，增加修改记录)]
* @UpdateRemark: [说明本次修改内容,(如多次修改保留历史记录，增加修改记录)]
* @Version: [v1.0]
*/ public class ExcelExportUtils {
/**
*
* @Title: downLoad
* @Description: TODO(导出EXCEL)
* @param fileName 导出文件名
* @param titles excel标题
* @param dataList excel数据
* @param request
* @param response
* @param sheetColumn void 设置 单元格宽度等信息
* @throws
*/
public static void downLoad(String fileName ,
String[] titles ,
Map<String,List<List<String>>> data ,
HttpServletRequest request ,
HttpServletResponse response , SheetColumn sheetColumn ){
//创建一个EXCEL
WritableWorkbook excel = null ;
try {
//设置类型为下载
response.setContentType("application/x-msdownload");
//获取浏览器信息
String userAgent = request.getHeader("user-agent").toLowerCase();
String rtn ="filename="+fileName+".xls" ;
if (userAgent.indexOf("msie") != -1) { //IE浏览器
rtn = "filename=" + java.net.URLEncoder.encode(fileName,"UTF-8")+".xls" ;
}else if (userAgent.indexOf("opera") != -1) { // Opera浏览器只能采用filename*
rtn = "filename*=UTF-8''" + fileName+".xls";
}else if (userAgent.indexOf("safari") != -1 ) { // Safari浏览器
rtn = "filename=" + new String(fileName.getBytes("UTF-8"),"ISO8859-1")+".xls" ;
}else if (userAgent.indexOf("applewebkit") != -1 ){ // Chrome浏览器
fileName = MimeUtility.encodeText(fileName, "UTF8", "B");
rtn = "filename=\"" + fileName + ".xls\"";
}else if (userAgent.indexOf("mozilla") != -1){ // FireFox浏览器
rtn = "filename=" + new String(fileName.getBytes("UTF-8"),"ISO-8859-1")+".xls";
}
//设置下载文件名
response.setHeader("Content-Disposition","attachment;"+ rtn);
//设置窗口弹出类型
response.setHeader("windows-Target","_blank"); excel = Workbook.createWorkbook(response.getOutputStream());
WritableFont font = new WritableFont(WritableFont.TIMES,14,WritableFont.BOLD);
WritableCellFormat format = new WritableCellFormat();
//设置文字居中
format.setAlignment(Alignment.CENTRE);
//设置文字垂直居中
format.setVerticalAlignment(VerticalAlignment.CENTRE);
//设置自动换行
format.setWrap(true); WritableCellFormat formatT = new WritableCellFormat(font);
//设置文字居中
formatT.setAlignment(Alignment.CENTRE);
//设置文字垂直居中
formatT.setVerticalAlignment(VerticalAlignment.CENTRE);
//设置自动换行
formatT.setWrap(true); int k = 0 ;
for(Map.Entry<String, List<List<String>>> entry : data.entrySet()) {
//创建工作空间
WritableSheet sheet = excel.createSheet(entry.getKey(), k++); //设置宽度
if(sheetColumn!=null){
sheetColumn.setColumnView(sheet);
}else{
//设置自动大小
CellView cellView = new CellView();
cellView.setAutosize(true);
for(int i= 0 ; i<titles.length ; i++){
sheet.setColumnView(i, cellView);
}
} List<List<String>> dataList = entry.getValue() ;
/*
* 添加title
*/
for(int i= 0 ; i<titles.length ; i++){
WritableCell cell= new Label(i , 0 ,titles[i],formatT);
sheet.addCell(cell);
}
/*
* 添加内容
*/
for(int j=0 ; j<dataList.size() ; j++){
List<String> contents = dataList.get(j) ; //一条数据
for(int i=0 ; i<contents.size() ; i++) {
WritableCell cell= new Label(i , j+1 ,contents.get(i),format);
sheet.addCell(cell);
}
}
}
excel.write() ;
} catch (Exception e) {
e.printStackTrace();
}finally{
try{
if(excel!=null) excel.close();
excel = null ;
}catch(Exception e){ }
}
} }
4、读取数据库数据并调用生成excel 下载
/**
* 导出中奖记录
* */
@RequestMapping("/doWinExcel")
public ModelAndView download(BuWinUser buWinUser, int zid,
HttpServletResponse response) throws Exception { List<BuWinUser> winList = winUserSvc
.findWinListByNoPage(buWinUser, zid);
Map<String, List<List<String>>> data = new HashMap<String, List<List<String>>>(); data.put("获奖用户信息", beanToArray(winList)); String[] titles = { "编号", "状态", "兑换码", "openid", "奖项", "昵称", "联系电话",
"性别", "中奖时间", "发奖时间" }; ExcelExportUtils.downLoad("获奖用户信息", titles, data, request, response,
new SheetColumn() {
@Override
public void setColumnView(WritableSheet sheet) {
sheet.setColumnView(0, 15);
sheet.setColumnView(1, 20);
sheet.setColumnView(2, 20);
sheet.setColumnView(3, 40);
sheet.setColumnView(4, 20);
sheet.setColumnView(5, 20);
sheet.setColumnView(6, 30);
sheet.setColumnView(7, 30);
sheet.setColumnView(8, 20);
sheet.setColumnView(9, 20);
}
});
return null; } private List<List<String>> beanToArray(List<BuWinUser> data) {
List<List<String>> dataList = new ArrayList<List<String>>();
for (BuWinUser winUser : data) {
List<String> list = new ArrayList<String>();
list.add(winUser.getWid() + "");
list.add(winUser.getIssend_name());
list.add(winUser.getSn_num());
list.add(winUser.getOpenid());
list.add(winUser.getPlevel_name());
list.add(winUser.getNick_name());
list.add(winUser.getTel());
list.add(winUser.getSex());
list.add(DateUtil.timestampToStr(winUser.getGmt_create(),
"yyyy-MM-dd HH:mm:ss"));
list.add(DateUtil.timestampToStr(winUser.getSend_time(),
"yyyy-MM-dd HH:mm:ss")); dataList.add(list);
}
return dataList;
}