对excel的导出，使用jxt
使用spring的annotation模式
前端 jsp：
function extractReport(){
$("#extractform").submit();
}
<form class="" id="extractform" action="${ctx}/report_detail/extractExport.action" method="post">
<input name="" type="button" style="width:240px" id="exprot" οnclick="extractReport()" value="提现,送彩,购彩,剩余彩票明细导出">
</form>
</div>
<div class="websearch">
<span style=""><font color="red">${msg}</font></span>
</div>
在controller层：
//提现报表的导出
@RequestMapping("extractExport")
public String extractExport(ModelMap m,String info,HttpServletResponse response,HttpServletRequest request){
response.reset();
InputStream input = null;
OutputStream ouput = null;
String fileName="";
try{
//获取数据库中要导出的数据
List<String[]> reportExtractCatchs=reportDetailService.findAllExtract();
List<String[]> reportLotterys=reportDetailService.findLotterys();
if(reportExtractCatchs==null&&reportLotterys==null||reportExtractCatchs.size()==0){//没有可导出的数据
m.put("msg", "没有可导出的数据");
m.put("result",false);
return BaseUtil.baseReturn(m, "activity/ticket/reportDetail");
}
//获取文件名
SimpleDateFormat format=new SimpleDateFormat("yyyyMMdd");
String exportDate=format.format(new Date());
fileName="明细_"+exportDate+"_.xls";
// 处理下载文件名
byte b[] = fileName.getBytes("GBK");
byte data[]=getExtractToExcel(reportExtractCatchs,reportLotterys,"明细");
// 输出格式设置
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment; filename=" + new String(b, "ISO-8859-1"));
ServletOutputStream os = response.getOutputStream();
//写入
ouput = response.getOutputStream();
response.setContentLength(data.length);
os.write(data, 0, data.length);
os.flush();
}catch(Exception e){
e.printStackTrace();
log.error("导出文件"+fileName+"失败"+e.getMessage());
}finally{
try{
if(ouput!=null){
ouput.close();
}
if(input!=null){
input.close();
}
}catch(Exception e){
this.log.error("io流异常"+e.getMessage());
}
}
return null;
}
private byte[] getExtractToExcel(List<String[]> reportExtractCatchs,List<String[]> reportLotterys,String name)throws Exception {
// 创建工作簿
ByteArrayOutputStream byteOut = new ByteArrayOutputStream();
WritableWorkbook wwb = Workbook.createWorkbook(byteOut);
int row = 1; // 记录数
int sheetCount = 1; // 工作表数
// 创建工作表
WritableSheet ws = wwb.createSheet(name + sheetCount, sheetCount - 1);
//标题 列
//送/购彩日期 提现日期 报盘名称 期数 手机号 ,账户号,账户名称,联行号,提现金额 送/购彩金额,中奖金额 商户名 商户号 送/购奖终端
String[] title = { "送/购彩日期", "提现日期", "报盘名称", "期数 ", "类型(送彩，购彩，提现)", "手机号",
"账户号", "账户名称", "联行号", "提现金额", "送//购彩金额", "中奖金额", "商户名 ", "商户号","送/购奖终端"};
int colCount = title.length; // 结果集中列的总数
// 标题样式
WritableFont font = new WritableFont(WritableFont.TAHOMA, 9,WritableFont.BOLD);// 定义字体
font.setColour(Colour.BLACK); // 字体颜色
WritableCellFormat wc = new WritableCellFormat(font);
wc.setAlignment(Alignment.LEFT); // 设对齐
wc.setBorder(Border.ALL, BorderLineStyle.THIN); // 设置边框线
wc.setBackground(jxl.format.Colour.LIME); // 设置单元格的背景颜色
ws.setRowView(0, 350); // 设置第一行,第二行的行高
//写入 标题
for (int i = 0; i <colCount; i++) {
ws.setColumnView(i, 20); // 设置列宽
ws.addCell(new Label(i , 0, title[i], wc));
}
int i=0;
// 将提现明细数据写入
for (; i < reportExtractCatchs.size(); i++) {
String[] report = reportExtractCatchs.get(i);
ws.addCell(new Label(1, i + 1, report[1].toString()));
ws.addCell(new Label(2, i + 1, report[2].toString().trim()));
ws.addCell(new Label(4, i + 1, "提现"));
ws.addCell(new Label(5, i + 1, report[7].toString().trim()));
ws.addCell(new Label(6, i + 1, report[3].toString().trim()));
ws.addCell(new Label(7, i + 1, report[4].toString().trim()));
ws.addCell(new Label(8, i + 1, report[5].toString().trim()));
ws.addCell(new Label(9, i + 1, report[6].toString().trim()));
}
//将送彩，购彩，剩余彩票的明细写入
for(int j=0;j<reportLotterys.size();j++){
String[] report=reportLotterys.get(j);
ws.addCell(new Label(0,j+i+1,report[0].toString()));
ws.addCell(new Label(3,j+i+1,report[1].toString()));
String type=report[8].toString();
boolean flag = false;
if (type.equals(Stream.TYPE_0)) {
type = "送彩";
flag = true;
} else if (type.equals(Stream.TYPE_1)) {
type = "购彩";
} else if (type.equals(Stream.TYPE_2)) {
type = "剩余彩票";
}
ws.addCell(new Label(4,j+i+1,type));
ws.addCell(new Label(5,j+i+1,report[2].toString()));
String buyMoney=new DecimalFormat("0.00").format((Integer.parseInt(report[3].toString().trim())*1.0)/100).toString().trim();
ws.addCell(new Label(10,j+i+1,buyMoney));
String prize=new DecimalFormat("0.00").format((Integer.parseInt(report[4].toString().trim())*1.0)/100).toString().trim();
ws.addCell(new Label(11,j+i+1,prize));
if(flag){
ws.addCell(new Label(12,j+i+1,report[5].toString()));
ws.addCell(new Label(13,j+i+1,report[6].toString()));
ws.addCell(new Label(14,j+i+1,report[7].toString()));
}
}
wwb.write();
wwb.close();
return byteOut.toByteArray();//将输出流转换成字节数组
}