利用jxl实现excel的导出

package com.rbao.east.utils;


import java.io.OutputStream;
import java.util.List;


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


/**
 * excel操作类
 * @author LIUTQ
 *
 */
public class ExcelUtil {


/**
* 创建excel文档并写入outStream输出流中
* @param outStream
* @param mainTitle
* @param titles
* @param contents
*/
public final static void buildExcel(OutputStream outStream,
String mainTitle, String[] titles, List<String[]> contents) {
int beginRow = 0;
try {
/** **********创建工作簿************ */
WritableWorkbook workbook = Workbook.createWorkbook(outStream);


/** **********创建工作表************ */
WritableSheet sheet = workbook.createSheet("Sheet1", 0);


/** **********设置纵横打印（默认为纵打）、打印纸***************** */
jxl.SheetSettings sheetset = sheet.getSettings();
sheetset.setProtected(false);


/** ************设置单元格字体************** */
WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 10);
WritableFont BoldFont = new WritableFont(WritableFont.ARIAL, 10,
WritableFont.BOLD);


/** ************以下设置三种单元格样式************ */
// 用于标题居中
WritableCellFormat wcf_center = new WritableCellFormat(BoldFont);
wcf_center.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
wcf_center.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
wcf_center.setAlignment(Alignment.CENTRE); // 文字水平对齐
wcf_center.setWrap(false); // 文字是否换行


// 用于正文居左
WritableCellFormat wcf_left = new WritableCellFormat(NormalFont);
wcf_left.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
wcf_left.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
wcf_left.setAlignment(Alignment.CENTRE); // 文字水平对齐
wcf_left.setWrap(false); // 文字是否换行


// excel大标题
if (mainTitle != null && !mainTitle.equals("")) {
sheet.mergeCells(0, 0, titles.length - 1, 0);
sheet.addCell(new Label(0, 0, mainTitle, wcf_center));
beginRow = beginRow + 1;
}


/** ***************以下是EXCEL第一行列标题********************* */
if (titles != null && titles.length > 1) {
for (int i = 0; i < titles.length; i++) {
sheet.addCell(new Label(i, beginRow, titles[i], wcf_center));
}
beginRow = beginRow + 1;
}
/** ***************以下是EXCEL正文数据********************* */
for (int i = 0; i < contents.size(); i++) {// row
String[] rowContent = contents.get(i);
for (int j = 0; j < titles.length; j++) { // cell
String content = "";
if (j < rowContent.length) {
content = rowContent[j];
}
sheet.addCell(new Label(j, i + beginRow, content, wcf_left));
}
}
workbook.write();
workbook.close();


} catch (Exception e) {
e.printStackTrace();
}
}

}

@RequestMapping("toExcel")
public void toExcel(HttpServletRequest request, HttpServletResponse response) {
Map<String, String> params = this.getParameters(request);
params.put("numPerPage", "500000");
try {
PageModel pm = accountRechargeService.getAccountRecharge(params);
List<Map<String, String>> ars = pm.getList();



String[] titles = { "序号", "订单号", "用户名称", "真实姓名", "类型", "所属银行",
"充值金额", "费用", "到帐金额", "充值时间","审核时间" ,"状态", "银行返回" };


List<String[]> contents = new ArrayList<String[]>();
for (Map<String, String> map : ars) {
String[] conList = new String[13];
conList[0] = StringUtil.toString(map.get("id"));
conList[1] = StringUtil.toString(map.get("rechargeTradeNo"));
conList[2] = StringUtil.toString(map.get("userAccount"));
conList[3] = StringUtil.toString(map.get("userRealname"));
conList[4] = StringUtil
.toString(AccountRecharge.ALL_RECHAREGE_TYPE.get(map
.get("rechargeType")));
conList[5] = StringUtil.toString(map.get("rechargePayment"));
conList[6] = StringUtil.toString(map.get("rechargeMoney"));
conList[7] = StringUtil.toString(map.get("rechargeFee"));
BigDecimal money = new BigDecimal(StringUtil.toString(map
.get("rechargeMoney")));
BigDecimal fee = new BigDecimal(StringUtil.toString(map
.get("rechargeFee")));
conList[8] = StringUtil.toString(money.subtract(fee));
conList[9] = StringUtil.toString(map.get("rechargeAddtime"));
//modified by hxy 2016-07-27 增加审核时间字段
conList[10] = StringUtil.toString(map.get("verifyTime"));
conList[11] = StringUtil
.toString(AccountRecharge.ALL_RECHAREGE_STATUS.get(map
.get("rechargeStatus")));
if(map.get("rechargeReturn") != null){
conList[12] = StringUtil.toString(map.get("rechargeReturn"));
}
else{
conList[12] = "";
}




contents.add(conList);


}


OutputStream os = response.getOutputStream();
response.reset();// 清空输出流
response.setHeader(
"Content-disposition",
"attachment; filename="
+ new String(("accountRecharge-data" + ".xls")
.getBytes("UTF-8"), "ISO8859-1"));
response.setContentType("application/msexcel");// 定义输出类型
ExcelUtil.buildExcel(os, "每日统计数据", titles, contents);
} catch (Exception e) {
e.printStackTrace();
logger.error("导出excel失败", e);
}


}


}