将数据以Excel表格形式导出

以导出差旅行程为例

1.控制层代码

添加依赖

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

    @ApiOperation("导出查询差旅行程列表")
    @PostMapping("/exportTravelList")
    public void exportTravelDetailList(@Valid @RequestBody TravelDetailReq req, HttpServletResponse response) throws IOException{
        SXSSFWorkbook wb = new SXSSFWorkbook(100);
        wb.setCompressTempFiles(true);
        excelService.generateTravelTripExcel(wb,req);
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + "TravelMsg.xlsx");
        response.flushBuffer();
        OutputStream outputStream = response.getOutputStream();
        wb.write(response.getOutputStream());
        outputStream.flush();
        outputStream.close();
    }

其中：TravelDetailReq为后期查询及筛选条件

@Data
public class TravelDetailReq {

    @NotNull(message = "页面数量不能为空")
    private Integer pageSize;
    @NotNull(message = "查询页码不能为空")
    private Integer pageNum;
    private String startTime;
    private String endTime;
    private String auditStatus;
    private String fromCityCode;
    private String toCityCode;
    private String users;
    private List<String> usercodes;
    private String companyId;

}

2.业务层代码（ExcelService excelService）

@Override
    public void generateTravelTripExcel(SXSSFWorkbook wb, TravelDetailReq req) {
        Integer pageNum = 1;
        Integer pageSize = 10000;
        Integer rowNum = 1;
        req.setPageNum(pageNum);
        req.setPageSize(pageSize);
        List<TravelDetailVO> list = null;
        Sheet sheet = wb.createSheet("差旅行程明细列表");
        sheet.setDefaultRowHeightInPoints(20);//行高
        sheet.setDefaultColumnWidth(20);//列宽
        Row row1 = sheet.createRow(0);
        //设置表头字段
        row1.createCell(0).setCellValue("OA账号");
        row1.createCell(1).setCellValue("公司名称");
        row1.createCell(2).setCellValue("申请人");
        row1.createCell(3).setCellValue("出发城市");
        row1.createCell(4).setCellValue("到达城市");
        row1.createCell(5).setCellValue("所属部门");
        row1.createCell(6).setCellValue("开始时间");
        row1.createCell(7).setCellValue("结束时间");
        do {
        	//分页循环插入Excel表数据
            PageHelper.startPage(pageNum, pageSize, true);
            //根据查询条件进行查询
            PageInfo<TravelDetailVO> detailMsgList = travelDetailService.getTravelDetailMsgList(req);
            list = detailMsgList.getList();
            for (TravelDetailVO data : list) {
                Row row = sheet.createRow(rowNum);
                for (int cellnum = 0; cellnum < 8; cellnum++) {
                    Cell cell = row.createCell(cellnum);
                    if (cellnum == 0) {
                        cell.setCellValue(data.getUserCode());
                    } else if (cellnum == 1) {
                        cell.setCellValue(data.getCompanyName());
                    } else if (cellnum == 2) {
                        cell.setCellValue(data.getUserFullName());
                    } else if (cellnum == 3) {
                        cell.setCellValue(data.getFromCityName());
                    } else if (cellnum == 4) {
                        cell.setCellValue(data.getToCityName());
                    } else if (cellnum == 5) {
                        cell.setCellValue(data.getDeptName());
                    } else if (cellnum == 6) {
                        cell.setCellValue(data.getBeginDate());
                    } else if (cellnum == 7) {
                        cell.setCellValue(data.getEndDate());
                    }
                }
                rowNum++;
            }
            pageNum++;
        }while (CollectionUtils.isNotEmpty(list) && list.size() == pageSize);

    }

至此，将所有查询出来的数据就已成功导出到Excel表格中了。