Java导出自定义Excel表格

        
        
            org.apache.poi
            poi-ooxml
            3.17
        

注意这里请求方式要使用GET，否则浏览器打开链接时不会下载Excel文件

    @ApiOperation("导出**太力分货表")
    @RequestMapping(value = "exportTlDeatail", method = RequestMethod.GET)
    @ResponseBody
    public CommonResult exportTlDeatail(
            @ApiParam(value = "分货id") @RequestParam(value = "distId") List distId,
            HttpServletRequest request,
            HttpServletResponse response) {
        try {
            hwPeriodService.exportTlDeatail(distId, request, response);
            return CommonResult.success("导出成功");
        } catch (Exception e) {
            return CommonResult.failed(e.getMessage());
        }
    }

    
    @Override
    public void exportTlDeatail(List distId, HttpServletRequest request, HttpServletResponse response) {
        if (distId.isEmpty()) {
            throw new RuntimeException("分货d不能为空");
        }
        List hwPmallTlDtos = hwPeriodDao.findHwPmallTl(distId);
        if (hwPmallTlDtos != null && hwPmallTlDtos.size() > 0) {
            this.toExportTlDeatail(hwPmallTlDtos, request, response);
        }
    }

    
    @SneakyThrows
    @Async
    void toExportTlDeatail(List hwPmallTlDtos, HttpServletRequest request, HttpServletResponse response) {
        //创建一个workbook,对应一个Excel文件夹
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        XSSFSheet sheet = workbook.createSheet("**分货明细表");
        //设置默认宽度
        sheet.setDefaultColumnWidth(20);

        String fileName = null;
        fileName = "**分货明细表" + new String(new DateTime().toString("yyyyMMddHHmm").getBytes("gbk"), "iso-8859-1");

        // 创建一个居中格式
        XSSFCellStyle style = workbook.createCellStyle();
        // 创建一个居中格式
        style.setAlignment(HorizontalAlignment.CENTER);
        //自动换行
        style.setWrapText(true);

        //设置边框
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        XSSFRow row0 = sheet.createRow(0);
        //设置每列标题
        String[] headers = {"店铺:", "", "广州市*****有限公司", ""};
        //在excel表中添加表头
        for (int i = 0; i < headers.length; i++) {
            XSSFCell cell = row0.createCell(i);
            XSSFRichTextString text = new XSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        //店铺
        CellRangeAddress titleRegion1 = new CellRangeAddress(0, 0, 0, 1);
        sheet.addMergedRegion(titleRegion1);
        //广州市德致商成信息科技有限公司
        CellRangeAddress titleRegion2 = new CellRangeAddress(0, 0, 2, 3);
        sheet.addMergedRegion(titleRegion2);

        XSSFRow row1 = sheet.createRow(1);
        //设置每列标题
        String[] headers2 = {"分货周期:", "", DateUtil.formatDateTime(hwPmallTlDtos.get(0).getStartTime()) + "~" + DateUtil.formatDateTime(hwPmallTlDtos.get(0).getEndTime()), ""};
        //在excel表中添加表头
        for (int i = 0; i < headers2.length; i++) {
            XSSFCell cell = row1.createCell(i);
            XSSFRichTextString text = new XSSFRichTextString(headers2[i]);
            cell.setCellValue(text);
        }
        //分货周期
        CellRangeAddress titleRegion3 = new CellRangeAddress(1, 1, 0, 1);
        sheet.addMergedRegion(titleRegion3);
        //时间段
        CellRangeAddress titleRegion4 = new CellRangeAddress(1, 1, 2, 3);
        sheet.addMergedRegion(titleRegion4);

        XSSFRow row2 = sheet.createRow(2);
        //设置每列标题
        String[] headers3 = {"组织编码:", "组织"};
        //在excel表中添加表头
        for (int i = 0; i < headers3.length; i++) {
            XSSFCell cell = row2.createCell(i);
            XSSFRichTextString text = new XSSFRichTextString(headers3[i]);
            cell.setCellValue(text);
        }
        //组织编码
        CellRangeAddress titleRegion5 = new CellRangeAddress(2, 3, 0, 0);
        sheet.addMergedRegion(titleRegion5);
        //组织
        CellRangeAddress titleRegion6 = new CellRangeAddress(2, 3, 1, 1);
        sheet.addMergedRegion(titleRegion6);

        XSSFRow row3 = sheet.createRow(4);
        //设置每列标题
        String[] headers4 = {"000000000000", "其他"};
        //在excel表中添加表头
        for (int i = 0; i < headers4.length; i++) {
            XSSFCell cell = row3.createCell(i);
            XSSFRichTextString text = new XSSFRichTextString(headers4[i]);
            cell.setCellValue(text);
        }

        for (int i = 0; i < hwPmallTlDtos.size(); i++) {
            row2.createCell(2 + i).setCellValue(hwPmallTlDtos.get(i).getHwProductSn());
        }

        XSSFRow row4 = sheet.createRow(3);
        for (int i = 0; i < hwPmallTlDtos.size(); i++) {
            row4.createCell(2 + i).setCellValue(hwPmallTlDtos.get(i).getProductName().replace('n', ' '));
        }

        for (int i = 0; i < hwPmallTlDtos.size(); i++) {
            row3.createCell(2 + i).setCellValue(hwPmallTlDtos.get(i).getTlPlanQuantity());
        }

        // //设置单元格的宽度
        // for (int i = 0; i < headers.length; i++) {
        //     sheet.setColumnWidth(i, 20 * 256);
        // }
        // 必要地清除response中的缓存信息
        response.reset();
        response.setContentType(request.getServletContext().getMimeType(fileName));
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.setHeader("name", fileName);
        response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");
        response.setHeader("Pragma", "public");
        response.setDateHeader("Expires", 0);
        response.setHeader("Access-Control-Allow-Origin", "*");
        response.setHeader("Access-Control-Allow-Methods", "POST, GET, OPTIONS, DELETE");
        response.setHeader("Content-disposition",
                "attachment; filename="" + URLEncoder.encode(fileName, "UTF-8") + """ + ".xlsx");
        OutputStream osOut = response.getOutputStream();
        workbook.write(osOut);
        osOut.flush();
        osOut.close();
    }