SpringBoot+POI实现本地导出,浏览器下载

SpringBoot+POI实现本地导出,浏览器下载

本博客代码都是在Centroller层进行编写,仅供参考!

导出本地excel表格

  		//本地导出
  		XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet1 = workbook.createSheet("sheet1");
        for (int i = 0; i < 10; i++) {
            XSSFRow row = sheet1.createRow(i);
            for (int j = 0; j < 15; j++) {
                row.createCell(j).setCellValue(j);
            }
        }
        String filePath = "C:\\Users\\zyh\\Desktop\\text.xlsx";
        FileOutputStream fileOutputStream = new FileOutputStream(filePath);
        //导出本地桌面
        workbook.write(fileOutputStream);
        fileOutputStream.close();

前端下载 两种方式

第一种: 通过设置头信息,使用ResponseEntity将文件返回

 		HttpHeaders headers = new HttpHeaders();
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        String fileName = "订单信息表";
        //中文转UTF-8防止乱码
        headers.setContentDispositionFormData("attachment",  URLEncoder.encode(fileName,"utf-8") +".xlsx");
        File file = new File(filePath);
        byte[] bytes = FileUtils.readFileToByteArray(file);
        return new ResponseEntity<byte[]>(bytes, headers, HttpStatus.CREATED);

第二种: 使用HttpServletResponse设置头信息,通过response的输出流将excel写到相应信息中.

        response.setContentType("application/octet-stream");
        String fileName = "订单信息表";
        response.setHeader("Content-Disposition","attachment;fileName=" + URLEncoder.encode(fileName,"utf-8") +"xlsx");
        response.flushBuffer();
        workbook.write(response.getOutputStream());
        //CommonResponse这是我自己封装的工具类.
        return new CommonResponse().success();

excel工具类

该方法用来导出查询数据库多条记录,通过反射的机制先拿到实体类的属性列表,再拿到方法列表,通过遍历属性列表进行拼接,与方法列表进行比较. 通过get方法给单元格赋值.

 private Workbook createExcel(String filePath, List<Order> orders) throws IOException, NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        XSSFWorkbook workbook = new XSSFWorkbook();
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.index);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  
        Sheet sheet = workbook.createSheet("详情");
          //合并单元格 参数: 起始行号,终止行号,起始列号,终止列号
//        CellRangeAddress cellAddresses = new CellRangeAddress(1, 3, 1, 3);
//        sheet .addMergedRegion(cellAddresses);

        for (int j = 0; j < orders.size(); j++) {
            Row row = sheet.createRow(j);

            Order order = orders.get(j);
            Field[] fields = order.getClass().getDeclaredFields();
            Method[] methods = order.getClass().getMethods();
            for (int i = 0; i < fields.length - 2; i++) {
                String value = "get" + fields[i + 2].getName();
                Cell cell = row.createCell(i);

                for (Method method : methods) {
                    String methodName = method.getName();
                    if ((value.toLowerCase()).equals(methodName.toLowerCase())) {
                        if (j == 0) {
                            boolean annotationPresent = fields[i + 2].isAnnotationPresent(ApiModelProperty.class);
                            if (annotationPresent) {
                                String value1 = fields[i + 2].getAnnotation(ApiModelProperty.class).value();
                                int columnWidth = sheet.getColumnWidth(i);
                                int length = value1.getBytes().length * 256;
                                if (value1.getBytes().length * 256 > columnWidth) {
                                    sheet.setColumnWidth(i, value1.getBytes().length * 2 * 128);
                                }
                                cell.setCellValue(value1);
                                cell.setCellStyle(cellStyle);
                            }
                        } else {
                            Method method1 = order.getClass().getMethod(methodName);
                            String invoke = method1.invoke(order) == null ? "" : method1.invoke(order).toString();
                            int columnWidth = sheet.getColumnWidth(i);
                            int op = invoke.getBytes().length * 256;
                            if (invoke.getBytes().length * 256 > columnWidth) {
                                sheet.setColumnWidth(i, invoke.getBytes().length * 2 * 128);
                            }
                            cell.setCellValue(invoke);
                        }

                    }
                }
            }
        }

        return workbook;
    }

前台访问

通过踩坑得出,要想在前台实现下载,访问就必须使用get方式.因为我还需要向后台参数,所以在后台返回后又重新把浏览器做了一次重定向.
也可以直接绑定标签的点击事件,来进行重定向.

 $.ajax({
                url: "order/order/load",
                data: queryParams,
                method: "GET",
                // datatype: "json",
                statusCode: {
                    201: function (res) {
                        layer.open({
                            title: '导出成功'
                            , content: '文件路径'
                        })
                        window.location.href = "order/order/load"
                    },
                    200: function (res) {
                        layer.open({
                            title: '导出成功'

                        })
                        window.location.href = "order/order/load"
                    },
                    500: function (res) {
                        layer.open({
                            title: '导出失败'
                            , content: '另一个程序正在使用此文件，进程无法访问。'
                        })
                    }
                },
                error: function (res) {
                    console.log(">>>>>>>>" + JSON.stringify(res))
                    layer.open({
                        title: '导出失败'
                        , content: '文件报错'
                    })
                }
            })

感谢您的浏览,代码还有很多可优化的地方,这里只是做了导出的基本功能.如有帮助,麻烦动动手指给个赞!!!