Java 自定义导出excel

循环显示数据，list内容嵌套在表头list中，具体代码片段如下：

    /**
     * 信息导出
     */
    @ApiOperation(value = "导出信息列表", notes = "导出信息列表接口")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "page", value = "页码（当前接口忽略）"),
            @ApiImplicitParam(name = "limit", value = "每页记录数（当前接口忽略）"),
            @ApiImplicitParam(name = "sortKey", value = "排序字段（当前接口忽略）"),
            @ApiImplicitParam(name = "sortType", value = "排序方式（当前接口忽略）")
    })
    @GetMapping("export")
    public void export(HttpServletResponse response,QueryVo queryVo) {
        try {
            List<SuperviseExcel> list = superviseStatisticService.exportSuperviseTaskList(queryVo);

            //创建HSSFWorkbook对象(excel的文档对象)
            HSSFWorkbook wb = new HSSFWorkbook();
            //创建sheet对象（excel的表单）
            HSSFSheet sheet = wb.createSheet("工作任务办理单");
            //设置列宽
            sheet.setColumnWidth(0, 3000);
            sheet.setColumnWidth(1, 8000);
            sheet.setColumnWidth(2, 18000);
            sheet.setColumnWidth(3, 4000);
            sheet.setColumnWidth(4, 5000);
            sheet.setColumnWidth(5, 4000);
            sheet.setColumnWidth(6, 4000);
            sheet.setColumnWidth(7, 8000);
            sheet.setColumnWidth(8, 18000);

            //标题字体
            HSSFFont titleFont = wb.createFont();
            titleFont.setFontName("黑体");
            titleFont.setFontHeightInPoints((short) 18);// 字体大小
            //标题样式
            HSSFCellStyle titleStyle = wb.createCellStyle();
            titleStyle.setFont(titleFont);
            titleStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
            titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
            titleStyle.setLocked(true);

            //标题标注字体
            HSSFFont titleFont2 = wb.createFont();
            titleFont2.setFontName("宋体");
            titleFont2.setFontHeightInPoints((short) 12);// 字体大小
            //标题标注样式
            HSSFCellStyle titleStyle2 = wb.createCellStyle();
            titleStyle2.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
            titleStyle2.setFont(titleFont2);

            //表头字体
            HSSFFont headerFont = wb.createFont();
            headerFont.setFontName("宋体");
            headerFont.setFontHeightInPoints((short) 12);// 字体大小
            //表头样式
            HSSFCellStyle headerStyle = wb.createCellStyle();
            headerStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
            headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
            headerStyle.setBorderTop(BorderStyle.THIN); // 上边框
            headerStyle.setBorderBottom(BorderStyle.THIN); // 下边框
            headerStyle.setBorderLeft(BorderStyle.THIN); // 左边框
            headerStyle.setBorderRight(BorderStyle.THIN); // 右边框
            headerStyle.setFont(headerFont);

            //数据字体
            HSSFFont font = wb.createFont();
            font.setFontName("宋体");
            font.setFontHeightInPoints((short) 11);// 字体大小
            //数据样式
            HSSFCellStyle style = wb.createCellStyle();
            style.setWrapText(true);// 自动换行
            style.setAlignment(HorizontalAlignment.CENTER);// 左右居中
            style.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
            style.setBorderTop(BorderStyle.THIN); // 上边框
            style.setBorderBottom(BorderStyle.THIN); // 下边框
            style.setBorderLeft(BorderStyle.THIN); // 左边框
            style.setBorderRight(BorderStyle.THIN); // 右边框
            style.setFont(font);

            //数据字体
            HSSFFont font2 = wb.createFont();
            font2.setFontName("宋体");
            font2.setFontHeightInPoints((short) 11);// 字体大小
            //数据样式
            HSSFCellStyle style2 = wb.createCellStyle();
            style2.setWrapText(true);// 自动换行
            style2.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
            style2.setBorderTop(BorderStyle.THIN); // 上边框
            style2.setBorderBottom(BorderStyle.THIN); // 下边框
            style2.setBorderLeft(BorderStyle.THIN); // 左边框
            style2.setBorderRight(BorderStyle.THIN); // 右边框
            style2.setFont(font);

            //表头
            String[] columnNames = {
                    "序号", "工作任务", "办理要求", "完成时限", "牵头领导", "承办部门", "负责人", "协办部门及负责人", "完成情况"};

            int startRow = 0;
            if (!ToolsUtil.isEmpty(list)) {
                for (int i = 0; i < list.size(); i++) {
                    SuperviseExcel supervise = list.get(i);

                    if (i > 0) {
                        List<TaskExcel> lastTaskExcels = list.get(i - 1).getTaskExcels();
                        if (!ToolsUtil.isEmpty(lastTaskExcels)) {
                            startRow = startRow + lastTaskExcels.size() + 5;
                        } else {
                            startRow = startRow + 5;
                        }
                    }

                    //合并单元格
                    CellRangeAddress cellRangeAddress = new CellRangeAddress(startRow, startRow, 0, 8);
                    sheet.addMergedRegion(cellRangeAddress);

                    //创建第一行
                    HSSFRow row1 = sheet.createRow(startRow);
                    //设置行高
                    row1.setHeight((short) 900);
                    //创建第一列
                    HSSFCell cell = row1.createCell(0);
                    cell.setCellValue(supervise.getName() + "--工作任务办理单");
                    cell.setCellStyle(titleStyle);

                    //发起单位及时间
                    HSSFRow row2 = sheet.createRow(startRow + 1);
                    row2.setHeight((short) 600);
                    HSSFCell cell20 = row2.createCell(0);
                    cell20.setCellValue("发起单位：");
                    cell20.setCellStyle(titleStyle2);
                    HSSFCell cell21 = row2.createCell(1);
                    cell21.setCellValue(supervise.getPublishDept());
                    cell21.setCellStyle(titleStyle2);
                    HSSFCell cell27 = row2.createCell(7);
                    cell27.setCellValue("发起时间：");
                    cell27.setCellStyle(titleStyle2);
                    HSSFCell cell28 = row2.createCell(8);
                    cell28.setCellValue(supervise.getCreateTime());
                    cell28.setCellStyle(titleStyle2);

                    //表头
                    HSSFRow row3 = sheet.createRow(startRow + 2);
                    row3.setHeight((short) 500);
                    for (int k = 0; k < columnNames.length; k++) {
                        HSSFCell cell3 = row3.createCell(k);
                        cell3.setCellValue(columnNames[k]);
                        cell3.setCellStyle(headerStyle);// 自动换行
                    }

                    int size = 0;
                    //内容
                    List<TaskExcel> taskExcelList = supervise.getTaskExcels();
                    if (!ToolsUtil.isEmpty(taskExcelList)) {
                        size = taskExcelList.size();
                        for (int j = 0; j < taskExcelList.size(); j++) {
                            TaskExcel task = taskExcelList.get(j);

                            HSSFRow row = sheet.createRow(startRow + 2 + j + 1);

                            HSSFCell cell0 = row.createCell(0);
                            cell0.setCellValue(j + 1);
                            cell0.setCellStyle(style);

                            HSSFCell cell1 = row.createCell(1);
                            cell1.setCellValue(task.getName());
                            cell1.setCellStyle(style2);// 自动换行

                            HSSFCell cell2 = row.createCell(2);
                            cell2.setCellValue(task.getDescription());
                            cell2.setCellStyle(style2);// 自动换行

                            HSSFCell cell3 = row.createCell(3);
                            cell3.setCellValue(task.getPlanEndTime());
                            cell3.setCellStyle(style);

                            HSSFCell cell4 = row.createCell(4);
                            cell4.setCellValue(task.getLeader());
                            cell4.setCellStyle(style);

                            HSSFCell cell5 = row.createCell(5);
                            cell5.setCellValue(task.getDeptName());
                            cell5.setCellStyle(style);

                            HSSFCell cell6 = row.createCell(6);
                            cell6.setCellValue(task.getUserName());
                            cell6.setCellStyle(style);

                            HSSFCell cell7 = row.createCell(7);
                            cell7.setCellValue(task.getCoopDepts());
                            cell7.setCellStyle(style);

                            HSSFCell cell8 = row.createCell(8);
                            cell8.setCellValue(task.getReportContent());
                            cell8.setCellStyle(style2);// 自动换行
                        }

                    }
                    HSSFRow rowk1 = sheet.createRow(startRow + 2 + size + 1);
                    rowk1.createCell(0).setCellValue("");
                    HSSFRow rowk2 = sheet.createRow(startRow + 2 + size + 2);
                    rowk2.createCell(0).setCellValue("");

                }
            }
            String fileName = "工作任务办理单";
            //输出Excel文件
            OutputStream output = response.getOutputStream();
            response.reset();
            //设置响应头
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");
            response.setContentType("application/x-xls");
            wb.write(output);
            output.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

导出数据类：

package com.test.pojo;

import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.util.List;


@Data
public class SuperviseExcel {
    @ApiModelProperty(value = "id")
    private Integer id;

    @ApiModelProperty(value = "督办名称")
    private String name;

    @ApiModelProperty(value = "发起单位")
    private String publishDept;

    @ApiModelProperty(value = "发起时间")
    private String createTime;

    @ApiModelProperty(value = "任务详情")
    List<TaskExcel> taskExcels;

}

TaskExcel

package com.test.pojo;

import io.swagger.annotations.ApiModelProperty;
import lombok.Data;


@Data
public class TaskExcel {

    @ApiModelProperty(value = "序号")
    private Integer id;

    @ApiModelProperty(value = "督办事项")
    private String name;

    @ApiModelProperty(value = "办理要求")
    private String description;

    @ApiModelProperty(value = "完成时限")
    private String planEndTime;

    @ApiModelProperty(value = "牵头领导")
    private String leader;

    @ApiModelProperty(value = "承办部门")
    private String deptName;

    @ApiModelProperty(value = "负责人")
    private String userName;

    @ApiModelProperty(value = "协办部门及负责人")
    private String coopDepts;

    @ApiModelProperty(value = "完成情况")
    private String reportContent;

}

效果图如下：