java导出execl

1、导入依赖

		<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
	<dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.7.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>

2、编写导出execl接口

@GetMapping("/export")
    public void export(@ModelAttribute DevicePageParam pageParam ,HttpServletResponse response) {
        
        List<Dispatch> deviceList = deviceFeignClient.getDeviceList(pageParam);

        String[] heards = {"机构", "部门", "设备名称", "标签ID", "SIP", "MAC", "人员名称"};
        String fileName = StrUtil.concat(false, "设备信息", RandomUtil.randomNumbers(10), ".xls");
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("设备信息");
        //初始化每列的单元格宽度
        sheet.setColumnWidth(0, 20 * 256);
        sheet.setColumnWidth(1, 20 * 256);
        sheet.setColumnWidth(2, 40 * 256);
        sheet.setColumnWidth(3, 20 * 256);
        sheet.setColumnWidth(4, 15 * 256);
        sheet.setColumnWidth(5, 40 * 256);
        sheet.setColumnWidth(6, 15 * 256);

        CellStyle titleCellStyle = createWorkBookCellStyle(workbook, IndexedColors.GREY_25_PERCENT.index, true);
        Row titleRow = sheet.createRow(0);

        for (int i = 0; i < heards.length; i++) {
            Cell rowCell = titleRow.createCell(i);
            rowCell.setCellStyle(titleCellStyle);
            rowCell.setCellValue(heards[i]);
        }
        CellStyle bobyCellStyle = createWorkBookCellStyle(workbook, IndexedColors.LIGHT_CORNFLOWER_BLUE.index, true);
        if (CollectionUtil.isNotEmpty(deviceList)) {

            for (int i = 0; i < deviceList.size(); i++) {

                Row row = sheet.createRow(i + 1);

                Dispatch dispatch = deviceList.get(i);

                Cell rowCell0 = row.createCell(0);
                rowCell0.setCellValue(dispatch.getOrganizationName());
                rowCell0.setCellStyle(bobyCellStyle);

                Cell rowCell1 = row.createCell(1);
                rowCell1.setCellValue(dispatch.getDepartmentName());
                rowCell1.setCellStyle(bobyCellStyle);

                Cell rowCell2 = row.createCell(2);
                rowCell2.setCellValue(dispatch.getName());
                rowCell2.setCellStyle(bobyCellStyle);

                Cell rowCell3 = row.createCell(3);
                rowCell3.setCellValue(dispatch.getTagId());
                rowCell3.setCellStyle(bobyCellStyle);

                Cell rowCell4 = row.createCell(4);
                rowCell4.setCellValue(dispatch.getSip());
                rowCell4.setCellStyle(bobyCellStyle);

                Cell rowCell5 = row.createCell(5);
                rowCell5.setCellValue(dispatch.getMac());
                rowCell5.setCellStyle(bobyCellStyle);

                Cell rowCell6 = row.createCell(6);
                rowCell6.setCellValue(dispatch.getPersonnelName());
                rowCell6.setCellStyle(bobyCellStyle);
            }
        }
        try {
            response.setContentType("application/octet-stream");
            response.setHeader("Content-disposition", StrUtil.concat(false, "attachment;filename=", URLEncoder.encode(fileName, StandardCharsets.UTF_8.name())));
            workbook.write(response.getOutputStream());
            workbook.close();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
	/**
     * 获取样式
     * @param workbook
     * @param fillForegroundColorIndex
     * @param showBorder
     * @return
     */
    private CellStyle createWorkBookCellStyle(Workbook workbook, short fillForegroundColorIndex, Boolean showBorder) {
        CellStyle cellStyle = workbook.createCellStyle();
        //设置背景色
        //设置填充模式为前景颜色填充
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //设置填充前景颜色
        cellStyle.setFillForegroundColor(fillForegroundColorIndex);

        if (showBorder) {
            //边框样式
            //设置上边框线条类型
            cellStyle.setBorderTop(BorderStyle.THIN);
            //设置右边框线条类型
            cellStyle.setBorderRight(BorderStyle.THIN);
            //设置下边框线条类型
            cellStyle.setBorderBottom(BorderStyle.THIN);
            //设置左边框线条类型
            cellStyle.setBorderLeft(BorderStyle.THIN);
            //设置上边框线条颜色
            cellStyle.setTopBorderColor(IndexedColors.BLUE_GREY.getIndex());
            //设置右边框线条颜色
            cellStyle.setRightBorderColor(IndexedColors.BLUE_GREY.getIndex());
            //设置下边框线条颜色
            cellStyle.setBottomBorderColor(IndexedColors.BLUE_GREY.getIndex());
            //设置左边框线条颜色
            cellStyle.setLeftBorderColor(IndexedColors.BLUE_GREY.getIndex());
        }

        return cellStyle;
    }