JAVA导出EXCEL并下载
一 ，流程大致如下
1.JSP页面点击按钮
2.点击导出excel按钮
3.点击确定按钮
（1）在谷歌浏览器中会直接下载
（2）在ie浏览器会提示保存路径
4.最后下载保存到本地，打开Excel表格，效果如下。
二，下面来看具体代码
前端js代码
function exportExcel() {
$.messager.confirm('确认', '确认把该搜索结果导出Excel表格 ？', function(r) {
if (r) {
var startTime = $('#startTime1').datetimebox('getValue');
var endTime = $('#endTime1').datetimebox('getValue');
var username = $("#username").val();
var mphone = $("#mphone").val();
var ordercode = $("#ordercode").val();
var orderStatus = $("#select_value").val();
$.messager.progress({
title : '处理中',
msg : '请稍后',
});
$.messager.progress('close');
location.href="${ctx}/tradingManage/exportExcel?startTime="+startTime+"&endTime="+endTime+"&username="+username+"&mphone="+mphone+"&ordercode="+ordercode+"&orderStatus="+orderStatus;
}
});
}
java代码
@ResponseBody
@RequestMapping(value="exportExcel")
public void exportExcel(HttpServletRequest request, HttpServletResponse resp) throws IOException
{
try
{
if (null == request || null == resp)
{
return;
}
List<VUserOrder> listContent = null;
String startTime = request.getParameter("startTime");
String endTime = request.getParameter("endTime");
String userName = request.getParameter("username");
String mphone = request.getParameter("mphone");
String orderCode = request.getParameter("ordercode");
String orderStatus = request.getParameter("orderStatus");
Map<String, Object> searchParams = new HashMap<String, Object>();
searchParams.put("LTE_type", OrderType.tx.getType());
searchParams.put("GTE_userId", 0);
if (null != startTime)
searchParams.put("GTE_createTime", DateUtils.getFormatDate(startTime, "yyyy-MM-dd HH:mm:ss"));
if (null != endTime)
searchParams.put("LTE_createTime", DateUtils.getFormatDate(endTime, "yyyy-MM-dd HH:mm:ss"));
if (null != userName)
searchParams.put("EQ_userName", userName);
if (null != mphone)
searchParams.put("EQ_userMphone", mphone);
if (null != orderCode)
searchParams.put("EQ_orderCode", orderCode);
if (null != orderStatus)
searchParams.put("EQ_status", orderStatus);
Specification<VUserOrder> spec = JpaQueryUtils.buildSpecification(VUserOrder.class, searchParams);
listContent = userOrderService.findAll(spec);
Map<Long, Map<String, String>> maps = authChsiService.getVUserOrderSchoolNameAndFacultyName(listContent);
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
if (null != listContent)
{
int listContentSize = listContent.size();
for (int i = 0; i < listContentSize; i++)
{
if (null != listContent.get(i))
{
Map<String, String> map2 = maps.get(listContent.get(i).getId());
listContent.get(i).setJyrq(formatter.format((listContent.get(i).getUpdateTime())));
if (null != map2)
{
listContent.get(i).setSchoolName(map2.get("schoolName"));
listContent.get(i).setFacultyName(map2.get("facultyName"));
}
}
}
}
//生成Excel文件
userOrderService.exportExcel(request, resp, listContent);
}
catch (ServiceException e)
{
logger.info("=====导出excel异常====");
}
catch (Exception e1)
{
logger.info("=====导出excel异常====");
}
}
上面的代码主要逻辑是查询出一个list对象，最后一步然后调用userOrderService中的exportExcel方法，可以直接忽视调用该方法前的查询代码，从controller直接调用以下方法。如下
（而我的业务逻辑是根据搜索条件查询到相应的list，然后把管理员想导出的数据导出Excel）
/**
* excel导出交易记录
* @param request
* @param resp
* @throws UnsupportedEncodingException
*/
public void exportExcel(HttpServletRequest request,HttpServletResponse resp,List<VUserOrder> listContent) throws UnsupportedEncodingException
{
HSSFWorkbook wb = new HSSFWorkbook();
request.setCharacterEncoding("UTF-8");
resp.setCharacterEncoding("UTF-8");
resp.setContentType("application/x-download");
String fileName = "交易记录.xls";
fileName = URLEncoder.encode(fileName, "UTF-8");
resp.addHeader("Content-Disposition", "attachment;filename=" + fileName);
HSSFSheet sheet = wb.createSheet("会员交易记录");
sheet.setDefaultRowHeight((short) (2 * 256));
sheet.setColumnWidth(0, 50 * 160);
HSSFFont font = wb.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 16);
HSSFRow row = sheet.createRow((int) 0);
sheet.createRow((int) 1);
sheet.createRow((int) 2);
sheet.createRow((int) 3);
sheet.createRow((int) 4);
sheet.createRow((int) 5);
sheet.createRow((int) 6);
sheet.createRow((int) 7);
sheet.createRow((int) 8);
sheet.createRow((int) 9);
sheet.createRow((int) 10);
sheet.createRow((int) 11);
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFCell cell = row.createCell(0);
cell.setCellValue("编号 ");
cell.setCellStyle(style);
cell = row.createCell(1);
cell.setCellValue("日期 ");
cell.setCellStyle(style);
cell = row.createCell(2);
cell.setCellStyle(style);
cell.setCellValue("订单号");
cell = row.createCell(3);
cell.setCellStyle(style);
cell.setCellValue("会员姓名");
cell = row.createCell(4);
cell.setCellStyle(style);
cell.setCellValue("会员手机号");
cell = row.createCell(5);
cell.setCellStyle(style);
cell.setCellValue("学校 ");
cell = row.createCell(6);
cell.setCellStyle(style);
cell.setCellValue("院系 ");
cell = row.createCell(7);
cell.setCellStyle(style);
cell.setCellValue("交易日期 ");
cell = row.createCell(8);
cell.setCellStyle(style);
cell.setCellValue("消费类型");
cell = row.createCell(9);
cell.setCellStyle(style);
cell.setCellValue("产品名称");
cell = row.createCell(10);
cell.setCellStyle(style);
cell.setCellValue("消费金额 ");
cell = row.createCell(11);
cell.setCellStyle(style);
cell.setCellValue("状态");
List<VUserOrder> vUserOrder = listContent;
for (int i = 0; i < vUserOrder.size(); i++)
{
HSSFRow row1 = sheet.createRow((int) i + 1);
VUserOrder vuserOrder = vUserOrder.get(i);
row1.createCell(0).setCellValue(i + 1);
row1.createCell(1).setCellValue(DateUtils.getFormatDateTime(vuserOrder.getCreateTime()));//日期
row1.createCell(2).setCellValue(vuserOrder.getOrderCode());//订单号
row1.createCell(3).setCellValue(vuserOrder.getUserName());//会员姓名
row1.createCell(4).setCellValue(vuserOrder.getUserMphone());//会员手机号
row1.createCell(5).setCellValue(vuserOrder.getSchoolName());//学校
row1.createCell(6).setCellValue(vuserOrder.getFacultyName());//院系
row1.createCell(7).setCellValue(vuserOrder.getJyrq());//交易日期
int orderType = vuserOrder.getType();
String type = "";
if (orderType == OrderType.xx.getType())
{
type = "线下消费";
}
else if (orderType == OrderType.df.getType())
{
type = "网购代付";
}
else if (orderType == OrderType.tx.getType())
{
type = "用户提现";
}
else if (orderType == OrderType.qe.getType())
{
type = "全额还款";
}
else if (orderType == OrderType.fq.getType())
{
type = "分期还款";
}
row1.createCell(8).setCellValue(type);//消费类型
row1.createCell(9).setCellValue(vuserOrder.getProductName());//产品名称
row1.createCell(10).setCellValue(vuserOrder.getAmount().doubleValue());//消费金额
row1.createCell(11).setCellValue(
(vuserOrder.getStatus() == 1) ? "交易成功" : (vuserOrder.getStatus() == 2) ? "失败" : "处理中");//状态
}
try
{
OutputStream out = resp.getOutputStream();
wb.write(out);
out.close();
}
catch (ServiceException e)
{
logger.info("=====导出excel异常====");
}
catch (Exception e1)
{
logger.info("=====导出excel异常====");
}
}
所以代码结束。调用该方法直接传进list对象，剩下的自己稍微修改下list的属性就可以ok了。
需要注意的是这里的jsp中用 location.href 发送到controller。如果有不明白的可以一起交流。
