Apache POI导出操作

下载请求不可以用ajax，ajax返回值是字符型，需要的是返回文件流到浏览器
这里前端页面使用的是XMLHttpRequest请求

//导出数据
function myExport() {
    layer.confirm('确定要导出现有条件下的记录？', {
        btn: ['确定', '取消']
    }, function () {
        let url = prefix + '/export';
        let data = {
            memberPhone:$('#memberPhone').val(),
            name:$('#name').val(),
            isOut:$('#isOut').val(),
            startDate:$('#startDate').val().substr(0,10),
            endDate:$('#startDate').val().substr(12,22)
        }
        let xhr = new XMLHttpRequest();
        xhr.open('post', url, true); 		//POST的格式相对比较灵活，参数可以有比较多的形式，例如JSON，表单FORM等
        xhr.responseType = "blob";    // 返回类型blob
        xhr.setRequestHeader("Content-Type","application/json");//提交的数据为json格式
        // 定义请求完成的处理函数
        xhr.onload = function () {
            // 请求完成
            if (this.status === 200) {
                // 返回200
                let blob = this.response;
                let reader = new FileReader();
                reader.readAsDataURL(blob);    // 转换为base64，可以直接放入a表情href
                reader.onload = function (e) {
                    if(blob.size == 0){
                            layer.close(layer.index);
                            layer.alert("无数据不允许导出",{
                                icon:5,
                                title:"提示"
                            });
                            return;
                        }
                    // 转换完成，创建一个a标签用于下载
                    let a = document.createElement('a');
                    let myDate = new Date();
                    let date = myDate.toLocaleString();
                    let fileName = "公排单表格"+date+".xls";
                    a.download = fileName;
                    a.href = e.target.result;
                    $("body").append(a);    // 修复firefox中无法触发click
                    a.click();
                    $(a).remove();
                    layer.close(layer.index);
                }
            }
        };
        // 发送ajax请求,案例中我们使用POST的请求格式，参数类型为JSON
        xhr.send(JSON.stringify(data));
    })
}

Controller层代码

   @Log("导出提现审核列表")
    @ResponseBody
    @RequestMapping("/export")
    @RequiresPermissions("system:transfer:sExport")
    public void goumai(@RequestBody JSONObject params) {
        List<TransferAuditDO> list = service.listPay(params);
        if(list == null || list.size()<=0){
            return;
        }
        ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletResponse response = requestAttributes.getResponse();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd-HH-mm-ss");
        String now = sdf.format(new Date());
        // 文件名
        String filename = "提现审核表格" + now + ".xls";
        try {
            // 写到服务器上
           String path = "/usr/local/nginx/html/afterImages/" + filename;  // 测试服的
//            String path = "/D:\\downLoad/" + filename;  // 本地地址

            //写在服务器地址
            System.out.println("服务器文件存放地址" + path);
//            String path = "C:\\Users\\PC\\Desktop\\" + filename;;

            // 写到服务器上（这种测试过，在本地可以，放到linux服务器就不行）
            //String path =  this.getClass().getClassLoader().getResource("").getPath()+"/"+filename;
            File name = new File(path);
            WritableWorkbook workbook = Workbook.createWorkbook(name);
            WritableSheet sheet = workbook.createSheet("提现审核列表", 0);
            WritableFont font = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
            WritableCellFormat cellFormat = new WritableCellFormat(font);
            cellFormat.setBackground(Colour.WHITE);
            cellFormat.setBorder(Border.ALL, BorderLineStyle.DASH_DOT);
            cellFormat.setAlignment(Alignment.CENTRE);
            cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
            sheet.getSettings().setDefaultColumnWidth(20);
            cellFormat.setWrap(true);

            // 单元格
            Label label0 = new Label(0, 0, "用户名", cellFormat);
            Label label1 = new Label(1, 0, "用户手机号", cellFormat);
            Label label2 = new Label(2, 0, "会员等级", cellFormat);
            Label label3 = new Label(3, 0, "申请金额", cellFormat);
            Label label4 = new Label(4, 0, "申请时间", cellFormat);
            Label label5 = new Label(5, 0, "美生账号", cellFormat);
            Label label6 = new Label(6, 0, "USDT收款地址", cellFormat);
            Label label7 = new Label(7, 0, "备注信息", cellFormat);
            Label label8 = new Label(8, 0, "审核时间", cellFormat);
            Label label9 = new Label(9, 0, "审核状态", cellFormat);

            sheet.addCell(label0);
            sheet.addCell(label1);
            sheet.addCell(label2);
            sheet.addCell(label3);
            sheet.addCell(label4);
            sheet.addCell(label5);
            sheet.addCell(label6);
            sheet.addCell(label7);
            sheet.addCell(label8);
            sheet.addCell(label9);
//            // 给第二行设置背景、字体颜色、对齐方式等等;
            WritableFont font2 = new WritableFont(WritableFont.ARIAL, 14, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
            WritableCellFormat cellFormat2 = new WritableCellFormat(font2);
            int n = 1;
            if (list != null && list.size() > 0) {
                // 遍历
                for (TransferAuditDO a : list) {
                    Label lt0 = new Label(0, n, a.getMemberName(), cellFormat2);
                    Label lt1 = new Label(1, n, a.getMemberPhone(), cellFormat2);
                    Label lt2 = new Label(2, n, a.getMemberLevel(), cellFormat2);
                    Label lt3 = new Label(3, n, a.getPrice()+"", cellFormat2);
                    SimpleDateFormat date = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    String create_time = "";
                    if(a.getCreateTime() != null){
                        create_time = date.format(a.getCreateTime());
                    }
                    Label lt4 = new Label(4, n, create_time + "", cellFormat2);
                    Label lt5 = new Label(5, n, a.getMsAccount() + "", cellFormat2);
                    Label lt6 = new Label(6, n, a.getUsdtAddress() + "", cellFormat2);
                    Label lt7 = new Label(7, n, a.getRemarks() + "", cellFormat2);
                    String update_time = "";
                    if(a.getUpdateTime() != null){
                        update_time = date.format(a.getUpdateTime());
                    }
                    Label lt8 = new Label(8, n, update_time, cellFormat2);

                    String statusName = "";
                    if(a.getStatus() == 0){
                        statusName = "待审核";
                    } else if (a.getStatus() == 1){
                        statusName = "已通过";
                    } else {
                        statusName = "已拒绝";
                    }
                    Label lt9 = new Label(9, n, statusName, cellFormat2);
                    sheet.addCell(lt0);
                    sheet.addCell(lt1);
                    sheet.addCell(lt2);
                    sheet.addCell(lt3);
                    sheet.addCell(lt4);
                    sheet.addCell(lt5);
                    sheet.addCell(lt6);
                    sheet.addCell(lt7);
                    sheet.addCell(lt8);
                    sheet.addCell(lt9);
                    n++;
                }
            }
            //开始执行写入操作
            workbook.write();
            //关闭流
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        // 第六步，下载excel
        OutputStream out = null;
        String path3 = "";
        try {
            response.addHeader("content-disposition", "attachment;filename="
                    + java.net.URLEncoder.encode(filename, "utf-8"));
            // 2.下载
            out = response.getOutputStream();
            path3 = "/usr/local/nginx/html/afterImages/" + filename; // 测试服地址
//            path3 = "/D:\\downLoad/" + filename; //本地地址
            // inputStream：读文件，前提是这个文件必须存在，要不就会报错
            InputStream is = new FileInputStream(path3);
            byte[] b = new byte[4096];
            int size = is.read(b);
            while (size > 0) {
                out.write(b, 0, size);
                size = is.read(b);
            }
            out.close();
            is.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }