【Workbook】EXCEL导出

 Controller

    @PostMapping("/list/export")
    public void exportBill(@RequestBody UserBillQueryVo queryVo, HttpServletResponse response) throws IOException {
        response.setContentType("application/force-download");
        response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("账单.xlsx", "UTF-8"));
        response.addHeader("Access-Control-Expose-Headers", "Content-Disposition");
        ServletOutputStream outputStream = response.getOutputStream();
        userBillService.exportBillList(queryVo, outputStream);
        outputStream.flush();
    }

 service

 @Override
    public void exportBillList(UserBillQueryVo queryVo, ServletOutputStream outputStream) throws IOException {
        Long currentUserInfoId = AuthUserInfoHolder.getUserInfo().map(UserInfo::getId).orElseThrow(() -> new ServiceException(500, "用户信息无效"));
        Page<UserBill> result = baseMapper.pageBillList(new Page<>(1, -1), currentUserInfoId, queryVo);
        List<UserBill> records = result.getRecords();
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("账单");

        // 创建标题行和表头行
        Row titleRow = sheet.createRow(0);
        Cell titleCell = titleRow.createCell(0);
        titleCell.setCellValue("账单");

        // 合并单元格设置标题
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 10)); // 合并第1行的第1列到第11列

        // 设置标题样式
        CellStyle titleStyle = workbook.createCellStyle();
        Font titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints((short) 16);
        titleFont.setBold(true);
        titleStyle.setFont(titleFont);
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleCell.setCellStyle(titleStyle);

        // 创建表头行
        Row headerRow = sheet.createRow(1);
        String[] headers = {"账单编号", "产品名称", "产品类型", "交易类型", "应付金额（元）", "余额支付（元）", "卡券抵扣（元）", "产品单价", "交易编号", "交易时间", "订单编号"};
        CellStyle headerStyle = workbook.createCellStyle();
        Font headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints((short) 12);
        headerFont.setBold(true);
        headerStyle.setFont(headerFont);
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        headerStyle.setFillForegroundColor(IndexedColors.BLUE.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBorderTop(BorderStyle.THIN);
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);

        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headerStyle);
        }

        // 填充数据
        List<LinkedHashMap<String, Object>> dataList = records.stream().map(userBill -> {
            LinkedHashMap<String, Object> map = new LinkedHashMap<>();
            if (userBill.getType() == 2) {
                userBill.setTypeName("消费");
            }
            if (userBill.getType() == 3) {
                userBill.setTypeName("退款");
            }
            map.put("账单编号", userBill.getBillNo());
            map.put("产品名称", userBill.getProductName());
            map.put("产品类型", userBill.getProductType());
            map.put("交易类型", userBill.getTypeName());
            map.put("应付金额", userBill.getType());
            map.put("余额支付", userBill.getAmount());
            map.put("卡券抵扣", userBill.getVoucherDiscount);
            map.put("产品单价", userBill.getUnitPrice());
            map.put("交易编号", userBill.getTransactionNo());
            map.put("交易时间", DateUtils.yyyyMMddHHmmssWithWhiffletree(userBill.getCreateDatetime()));
            map.put("订单编号", userBill.getOrderNo());
            return map;
        }).collect(Collectors.toList());

        int rowNum = 2; // 从第三行开始写入数据
        for (LinkedHashMap<String, Object> data : dataList) {
            Row row = sheet.createRow(rowNum);
            int celNum = 0;
            Set<Map.Entry<String, Object>> entries = data.entrySet();
            for (Map.Entry<String, Object> entry : entries) {
                Cell cell = row.createCell(celNum);
                cell.setCellValue(String.valueOf(entry.getValue()));
                celNum++;
            }
            rowNum++;
        }
        workbook.write(outputStream);
        workbook.close();
    }

mapper

Page<UserBill> pageBillList(Page<Object> objectPage,@Param("currentUserInfoId") Long currentUserInfoId, @Param("queryVo") UserBillQueryVo queryVo);