JSP（一定要用window.location.href来提交数据，用AJAX提交，就算代码正确，也导不出EXCEL）：
//导出列表
function export2Excel() {
var begin=$('#begin').datebox('getValue');
var end=$('#end').datebox('getValue');
window.location.href="<%=basePath%>mlc/export2Excel?begin="+begin+"&end="+end;
}
Controller:
@RequestMapping("/export2Excel")
public void export2Excel(String begin,String end,HttpServletRequest req,HttpServletResponse res,HttpSession session) {
res.setCharacterEncoding("text/html,charset=utf-8");
// 设置response方式,使执行此controller时候自动出现下载页面,而非直接使用excel打开
res.setContentType("APPLICATION/OCTET-STREAM");
SXSSFWorkbook excel = null;
OutputStream out = null;
Map map=new HashMap();
if(begin!=null && !begin.equals("")){
map.put("begindate", begin.replaceAll("-", ""));
}
if(end!=null && !end.equals("")){
map.put("enddate", end.replaceAll("-", ""));
}
User user=(User) session.getAttribute("user");
map.put("loginname", user.getLoginname());
try {
excel = merchantListService.export2Excel(map);
res.setHeader("Content-Disposition", "attachment;fileName="+URLEncoder.encode("交易记录表.xlsx", "UTF-8"));
out = res.getOutputStream();
excel.write(out);
excel.close();
out.flush();
out.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
Service(POI操作EXCEL的重要代码):
public SXSSFWorkbook export2Excel(Map map) {
// TODO Auto-generated method stub
String title = "交易记录表";
String[] headers = {"Id","商户代码 ","商户法定名称","商户注册地址","商户联系人",
"商户联系人电话","真实商户类型","拓展类型","服务区域",
"终端号","合作银行","产权","维护","型号","记录创建时间",
"员工名称","清算日","金额","手续费","净金额","是否IC卡","是否非接"};
// 第一步，创建一个webbook，对应一个Excel文件
XSSFWorkbook wb = new XSSFWorkbook();
SXSSFWorkbook swb = new SXSSFWorkbook(wb,100);
// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
SXSSFSheet sheet = swb.createSheet(title);
// 设置表格默认高宽
sheet.setDefaultColumnWidth(50);
sheet.setDefaultRowHeightInPoints(25);
// 第三步，在sheet中添加表头第n行
SXSSFRow row = sheet.createRow(0);
row.setHeightInPoints(22);
// 第四步，创建单元格，并设置值表头 设置表头居中
CellStyle style = swb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setWrapText(true);
//生成表头
for (int i = 0; i < headers.length; i++) {
SXSSFCell cell = row.createCell(i);
cell.setCellStyle(style);
XSSFRichTextString text = new XSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
List<MerchantInfo> dataList = merchantListMapper.findAllRecord(map);
// 第五步，写入实体数据 实际应用中这些数据从数据库得到，
for (int i = 0; i < dataList.size(); i++){
row = sheet.createRow((int) (i+1) );
row.setHeightInPoints(25);
MerchantInfo temp = dataList.get(i);
SXSSFCell cell0 = row.createCell(0);
cell0.setCellValue(temp.getId());
cell0.setCellStyle(style);
row.createCell(1).setCellValue(temp.getMerchantCode());
SXSSFCell cell2 = row.createCell(2);
cell2.setCellStyle(style);
cell2.setCellValue(temp.getMerchantName());
SXSSFCell cell3 = row.createCell(3);
cell3.setCellStyle(style);
cell3.setCellValue(temp.getMerchantAddr());
row.createCell(4).setCellValue(temp.getMerchantContact());
row.createCell(5).setCellValue(temp.getMerchantTel());
row.createCell(6).setCellValue(temp.getMerchantType());
row.createCell(7).setCellValue(temp.getExtensionType());
row.createCell(8).setCellValue(temp.getCoverage());
row.createCell(9).setCellValue(temp.getTerminalNum());
row.createCell(10).setCellValue(temp.getCooperateBank());
row.createCell(11).setCellValue(temp.getProperty());
row.createCell(12).setCellValue(temp.getMaintenance());
row.createCell(13).setCellValue(temp.getModel());
row.createCell(14).setCellValue(temp.getCreateTime());
row.createCell(15).setCellValue(temp.getUserName());
row.createCell(16).setCellValue(temp.getClearDate());
row.createCell(17).setCellValue(temp.getAccount());
row.createCell(18).setCellValue(temp.getPoundage());
row.createCell(19).setCellValue(temp.getNetAmount());
row.createCell(20).setCellValue(temp.getIsIC());
row.createCell(21).setCellValue(temp.getIsRF());
}
return swb;
}
=====================================================
测试：
