您的位置: 首页  >  文章  >  java查询数据导出excel并返回给浏览器下载

java查询数据导出excel并返回给浏览器下载

分类: 文章 • 2025-04-20 11:46:46

 maven地址为

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>
其中ooxml表示支持2007版本，不带ooxml仅支持2003以前的版本

效果图：

1.点击导出表按钮

java查询数据导出excel并返回给浏览器下载

2.接着就会出现下图

java查询数据导出excel并返回给浏览器下载

3.点击上图中的确定按钮再接着就会出现下图

java查询数据导出excel并返回给浏览器下载

4.点击上图中的保存按钮接着就会出现下图，浏览器下载完成后的提示

java查询数据导出excel并返回给浏览器下载

5.打开下载好的文件如下图

java查询数据导出excel并返回给浏览器下载

好了，废话不多少，上代码

jsp前端代码

[plain] view plain copy
  1. <span style="font-size:18px;"><div style="height:30px;">  
  2.     <a>时间：</a>  
  3.         <input id="startDateConsume" type="text" class="easyui-datebox"> <a>-</a>  
  4.         <input id="endDateConsume" type="text" class="easyui-datebox">  
  5.         <a>消费类型：</a>  
  6.        <select id="consumesType" name="">  
  7.     <option value="0" selected="selected">所有</option>  
  8.     <option value="1">报名费</option>  
  9.     <option value="2">酒水零食类</option>  
  10.     </select>  
  11.         <a>支付状态：</a>  
  12.        <select id="conPaymentStatus" name="">  
  13.     <option value="0" selected="selected">所有</option>  
  14.     <option value="1">未支付</option>  
  15.     <option value="2">已支付</option>  
  16.     </select>  
  17.         <a id="btnConsumesSearch" class="easyui-linkbutton"  
  18.     data-options="iconCls:'icon-search'" style="margin-left:10px">查询</a><a>（查询出来的数据可统计）</a>  
  19.     <a id="consumesOutExcel" class="easyui-linkbutton" style="" data-options="iconCls:'icon-redo'">导出表</a>  
  20. </div></span>  

js前端代码

[javascript] view plain copy
  1. <span style="font-size:18px;">$(function() {  
  2.     //导出excel表  
  3.     $('#consumesOutExcel').on('click',function(){                     
  4.         exportExcel();  
  5.     });  
  6. });  
  7.   
  8. function exportExcel() {  
  9.         $.messager.confirm('确认', '确认把该搜索结果导出Excel表格 ？', function(r) {  
  10.         if (r) {  
  11.             var startTime = $('#startDateConsume').val();  
  12.             var endTime = $('#endDateConsume').val();  
  13.             var consumesType = $('#consumesType').val();  
  14.             var conPaymentStatus = $('#conPaymentStatus').val();  
  15.             $.messager.progress({  
  16.                 title : '处理中',  
  17.                 msg : '请稍后',  
  18.             });  
  19.             $.messager.progress('close');  
  20.             location.href="web/vip/exportExcel.xlsx?startTime="+startTime+"&endTime="+endTime+"&consumesType="+consumesType+"&conPaymentStatus="+conPaymentStatus;  
  21.         }  
  22.     });   
  23. } </span>  

java后端代码

[java] view plain copy
  1. <span style="font-size:18px;">@Controller  
  2. @RequestMapping("/vip")  
  3. public class VipController {  
  4.     //文件下载：导出excel表  
  5.     @RequestMapping(value = "/exportExcel.xlsx",method = RequestMethod.GET)  
  6.     @ResponseBody  
  7.     public void exportExcel(HttpServletRequest request,HttpServletResponse response) throws UnsupportedEncodingException{  
  8.         //一、从后台拿数据  
  9.             if (null == request || null == response)  
  10.             {  
  11.                 return;  
  12.             }  
  13.             List<VipConsumes> list = null;  
  14.             String startTime = request.getParameter("startTime");  
  15.             String endTime = request.getParameter("endTime");  
  16.             int consumesType = Integer.parseInt(request.getParameter("consumesType"));  
  17.             int conPaymentStatus =Integer.parseInt(request.getParameter("conPaymentStatus"));  
  18.   
  19.             VipConsumesExample example = new VipConsumesExample();  
  20.             if(consumesType!=0 && conPaymentStatus!=0){  
  21.                 example.createCriteria().andTimeBetween(startTime, endTime).andConsumeTypeEqualTo(consumesType).andStatusEqualTo(conPaymentStatus);  
  22.             }else if(consumesType ==0 && conPaymentStatus!=0) {  
  23.                 example.createCriteria().andTimeBetween(startTime, endTime).andStatusEqualTo(conPaymentStatus);  
  24.             }else if(consumesType!=0 && conPaymentStatus==0){  
  25.                 example.createCriteria().andTimeBetween(startTime, endTime).andConsumeTypeEqualTo(consumesType);  
  26.             }else {  
  27.                 example.createCriteria().andTimeBetween(startTime, endTime);  
  28.             }  
  29.             list = this.vipConsumesDao.selectByExample(example);  
  30.             //二、 数据转成excel  
  31.             request.setCharacterEncoding("UTF-8");  
  32.             response.setCharacterEncoding("UTF-8");  
  33.             response.setContentType("application/x-download");  
  34.   
  35.             String fileName = "消费记录.xlsx";  
  36.             fileName = URLEncoder.encode(fileName, "UTF-8");  
  37.             response.addHeader("Content-Disposition", "attachment;filename=" + fileName);  
  38.             // 第一步：定义一个新的工作簿  
  39.             XSSFWorkbook wb = new XSSFWorkbook();  
  40.             // 第二步：创建一个Sheet页  
  41.             XSSFSheet sheet = wb.createSheet("startTimeendTime");  
  42.             sheet.setDefaultRowHeight((short) (2 * 256));//设置行高  
  43.             sheet.setColumnWidth(0, 4000);//设置列宽  
  44.             sheet.setColumnWidth(1,5500);  
  45.             sheet.setColumnWidth(2,5500);  
  46.             sheet.setColumnWidth(3,5500);  
  47.             sheet.setColumnWidth(11,3000);  
  48.             sheet.setColumnWidth(12,3000);  
  49.             sheet.setColumnWidth(13,3000);  
  50.             XSSFFont font = wb.createFont();  
  51.             font.setFontName("宋体");  
  52.             font.setFontHeightInPoints((short) 16);  
  53.               
  54.             XSSFRow row = sheet.createRow(0);  
  55.             XSSFCell cell = row.createCell(0);  
  56.             cell.setCellValue("流水号 ");  
  57.             cell = row.createCell(1);  
  58.             cell.setCellValue("微信名 ");  
  59.             cell = row.createCell(2);  
  60.             cell.setCellValue("微信订单号");  
  61.             cell = row.createCell(3);  
  62.             cell.setCellValue("消费时间");  
  63.             cell = row.createCell(4);  
  64.             cell.setCellValue("消费类型");  
  65.             cell = row.createCell(5);  
  66.             cell.setCellValue("剩余积分 ");  
  67.             cell = row.createCell(6);  
  68.             cell.setCellValue("新增积分 ");  
  69.             cell = row.createCell(7);  
  70.             cell.setCellValue("扣除积分 ");  
  71.             cell = row.createCell(8);  
  72.             cell.setCellValue("消费金额");  
  73.             cell = row.createCell(9);  
  74.             cell.setCellValue("支付方式");  
  75.             cell = row.createCell(10);  
  76.             cell.setCellValue("支付状态 ");  
  77.             cell = row.createCell(11);  
  78.             cell.setCellValue("钱包原始金额");  
  79.             cell = row.createCell(12);  
  80.             cell.setCellValue("钱包扣除金额");  
  81.             cell = row.createCell(13);  
  82.             cell.setCellValue("钱包剩余金额");  
  83.               
  84.             XSSFRow rows;  
  85.             XSSFCell cells;  
  86.             for (int i = 0; i < list.size(); i++) {  
  87.                 // 第三步：在这个sheet页里创建一行  
  88.                 rows = sheet.createRow(i+1);  
  89.                 // 第四步：在该行创建一个单元格  
  90.                 cells = rows.createCell(0);  
  91.                 // 第五步：在该单元格里设置值  
  92.                 cells.setCellValue(list.get(i).getConsumeId());  
  93.                   
  94.                 cells = rows.createCell(1);  
  95.                 cells.setCellValue(list.get(i).getName());  
  96.                 cells = rows.createCell(2);  
  97.                 cells.setCellValue(list.get(i).getOrderNumber());  
  98.                 cells = rows.createCell(3);  
  99.                 cells.setCellValue(list.get(i).getTime());  
  100.                 cells = rows.createCell(4);  
  101.                 if (list.get(i).getConsumeType() == 2) {  
  102.                     cells.setCellValue("酒水零食费");  
  103.   
  104.                 } else {  
  105.                     cells.setCellValue("报名费");  
  106.                 }  
  107.                 cells = rows.createCell(5);  
  108.                 cells.setCellValue(list.get(i).getIntegral());  
  109.                 cells = rows.createCell(6);  
  110.                 cells.setCellValue(list.get(i).getIntegralIn());  
  111.                 cells = rows.createCell(7);  
  112.                 cells.setCellValue(list.get(i).getIntegralOut());  
  113.                 cells = rows.createCell(8);  
  114.                 cells.setCellValue(list.get(i).getMoney());  
  115.                 cells = rows.createCell(9);  
  116.                 if (list.get(i).getPayment() == 2) {  
  117.                     cells.setCellValue("积分抵现");  
  118.                 } else if (list.get(i).getPayment() == 3) {  
  119.                     cells.setCellValue("微信支付");  
  120.                 } else if (list.get(i).getPayment() == 4) {  
  121.                     cells.setCellValue("现金");  
  122.                 } else if (list.get(i).getPayment() == 1) {  
  123.                     cells.setCellValue("钱包");  
  124.                 }  
  125.                 cells = rows.createCell(10);  
  126.                 if (list.get(i).getStatus() == 2) {  
  127.                     cells.setCellValue("已支付");  
  128.                 } else if (list.get(i).getStatus() == 1) {  
  129.                     cells.setCellValue("未支付");  
  130.                 }  
  131.                 cells = rows.createCell(11);  
  132.                 cells.setCellValue(list.get(i).getWalletOriginal());  
  133.                 cells = rows.createCell(12);  
  134.                 cells.setCellValue(list.get(i).getWalletOut());  
  135.                 cells = rows.createCell(13);  
  136.                 cells.setCellValue(list.get(i).getWalletSurplus());  
  137.             }  
  138.                   
  139.                 try {  
  140.                     OutputStream out = response.getOutputStream();  
  141.                     wb.write(out);  
  142.                     out.close();  
  143.                     wb.close();  
  144.                 } catch (IOException e) {  
  145.                     // TODO Auto-generated catch block  
  146.                     e.printStackTrace();  
  147.                 }  
  148.   
  149.     }  
  150. }</span>  

相关推荐