关于java:前后端导出excel

后端

1. 导入 poi 包

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.0.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.0.1</version>
        </dependency>

2. 实现向前端导出文件流

@RequestMapping(value = "export", method = RequestMethod.GET)
    public void export(HttpServletResponse response) {
        // 获取所有
        List<MicData> micData = dataService.findAllExcept();

        try (HSSFWorkbook wb = new HSSFWorkbook()) {writeChargeSheet(micData, wb);
            
            response.setContentType("application/octet-stream");
            response.addHeader("Content-Disposition", "attachment; filename=" + "data.xls");
            try {wb.write(response.getOutputStream());
                response.getOutputStream().flush();
            } catch (IOException ex) {ex.printStackTrace();
            }

        } catch (IOException e) {e.printStackTrace();
            throw new IllegalArgumentException("请从新导出");
        }

    }


    private void writeChargeSheet(List<MicData> micDataList, HSSFWorkbook wb) {Sheet sheet = wb.createSheet("麦克风记录数据");
        writeHeadCell(sheet.createRow(0), "线号", "创立工夫", "测试人员", "测试次数", "杆号", "1 号麦克风 Max","1 号麦克风 Min","2 号麦克风 Max","2 号麦克风 Min",
                "3 号麦克风 Max","3 号麦克风 Min","4 号麦克风 Max","4 号麦克风 Min","5 号麦克风 Max","5 号麦克风 Min","6 号麦克风 Max","6 号麦克风 Min","7 号麦克风 Max","7 号麦克风 Min",
                "8 号麦克风 Max","8 号麦克风 Min","9 号麦克风 Max","9 号麦克风 Min");
        sheet.setColumnWidth(0, 10 * 256);
        sheet.setColumnWidth(1, 19 * 256);
        sheet.setColumnWidth(2, 13 * 256);
        sheet.setColumnWidth(4, 13 * 256);
        sheet.setColumnWidth(5, 10 * 256);
        sheet.setColumnWidth(6, 8 * 256);
        sheet.setColumnWidth(8, 8 * 256);
        sheet.setColumnWidth(9, 8 * 256);
        sheet.setColumnWidth(10, 8 * 256);
        sheet.setColumnWidth(11, 8 * 256);
        sheet.setColumnWidth(12, 8 * 256);
        sheet.setColumnWidth(13, 8 * 256);
        sheet.setColumnWidth(14, 8 * 256);
        sheet.setColumnWidth(15, 8 * 256);
        sheet.setColumnWidth(16, 8 * 256);
        sheet.setColumnWidth(17, 8 * 256);
        sheet.setColumnWidth(18, 8 * 256);
        sheet.setColumnWidth(19, 8 * 256);
        sheet.setColumnWidth(20, 8 * 256);
        sheet.setColumnWidth(21, 8 * 256);
        sheet.setColumnWidth(22, 8 * 256);
        sheet.setColumnWidth(23, 8 * 256);
        sheet.setColumnWidth(24, 8 * 256);
        for (int i = 0; i < micDataList.size(); i++) {writeChargeCell(sheet.createRow(i + 1), micDataList.get(i));
        }
    }

    private void writeHeadCell(Row r, String... vs) {for (int i = 0; i < vs.length; i++) {r.createCell(i).setCellValue(vs[i]);
        }
    }

    private void writeChargeCell(Row r, MicData micData) {
        int cellIndex = 0;
        r.createCell(cellIndex++).setCellValue(micData.getLineNo());
        r.createCell(cellIndex++).setCellValue(micData.getCreateTime());
        r.createCell(cellIndex++).setCellValue(micData.getCreateName());
        r.createCell(cellIndex++).setCellValue(micData.getNumber());
        r.createCell(cellIndex++).setCellValue(micData.getRodNo());
        r.createCell(cellIndex++).setCellValue(micData.getMic1Max());
        r.createCell(cellIndex++).setCellValue(micData.getMic1Min());
        r.createCell(cellIndex++).setCellValue(micData.getMic2Max());
        r.createCell(cellIndex++).setCellValue(micData.getMic2Min());
        r.createCell(cellIndex++).setCellValue(micData.getMic3Max());
        r.createCell(cellIndex++).setCellValue(micData.getMic3Min());
        r.createCell(cellIndex++).setCellValue(micData.getMic4Max());
        r.createCell(cellIndex++).setCellValue(micData.getMic4Min());
        r.createCell(cellIndex++).setCellValue(micData.getMic5Max());
        r.createCell(cellIndex++).setCellValue(micData.getMic5Min());
        r.createCell(cellIndex++).setCellValue(micData.getMic6Max());
        r.createCell(cellIndex++).setCellValue(micData.getMic6Min());
        r.createCell(cellIndex++).setCellValue(micData.getMic7Max());
        r.createCell(cellIndex++).setCellValue(micData.getMic7Min());
        r.createCell(cellIndex++).setCellValue(micData.getMic8Max());
        r.createCell(cellIndex++).setCellValue(micData.getMic8Min());
        r.createCell(cellIndex++).setCellValue(micData.getMic9Max());
        r.createCell(cellIndex++).setCellValue(micData.getMic9Min());
    }

3. 前端接管

function derVice(){
                $.ajax({
                    headers: {'content-type':'application/json;charset=ISO8859-1'},
                    type: "GET",
                    url: "api/export",
                    xhrFields: {responseType: "arraybuffer",},
                    success: function (res, _, xhr) {console.log(res);
                        let [, fileName] = xhr
                                .getResponseHeader("Content-Disposition")
                                .match(/filename=(.*)$/);
                        let blob = new Blob([res], {type: "application/vnd.ms-excel",});
                        let link = document.createElement("a");
                        link.href = window.URL.createObjectURL(blob);
                        link.download = fileName;
                        link.click();
                        window.URL.revokeObjectURL(link.href);
                    },
                });

            }