JAVA的Excel导出

java的Excel导出，包括单元格的合并，样式设置
实体类

@Data
public class EmergencyReportingEntity {

    /**
     * 主标题
     */
    private String mainTitle;
    /**
     * 副标题
     */
    private List<String> subTitle;
    /**
     * 数据 List<Object> 对应 subTitle 的长度
     */
    private List<EmergencyResourcesVo> data;

}

数据处理

   public Workbook exportExcel() {
        // 根据数据生成 List<EmergencyReportingEntity>
        List<EmergencyReportingEntity> list = new ArrayList<>();
        addData1(list);
        addData2(list);
        return xlsOutputResourcePreparation(list);
    }

    public void addData1(List<EmergencyReportingEntity> list) {
        EmergencyReportingEntity emergencyReportingEntity = new EmergencyReportingEntity();
        emergencyReportingEntity.setMainTitle("事件类型");
        emergencyReportingEntity.setSubTitle(Arrays.asList("事件类型", "事件数量"));
        List<List<Object>> dataList = new ArrayList<>();
        for (int i = 0; i < 4; i++) {
            List<Object> data = new ArrayList<>();
            data.add("自然灾害" + i);
            data.add("22" + i);
            dataList.add(data);
        }
        emergencyReportingEntity.setData(dataList);
        list.add(emergencyReportingEntity);
    }

    public void addData2(List<EmergencyReportingEntity> list) {
        EmergencyReportingEntity emergencyReportingEntity = new EmergencyReportingEntity();
        emergencyReportingEntity.setMainTitle("重点突出类型");
        emergencyReportingEntity.setSubTitle(Arrays.asList("类型名称", "类型数量"));
        List<List<Object>> dataList = new ArrayList<>();
        for (int i = 0; i < 7; i++) {
            List<Object> data = new ArrayList<>();
            data.add("安全生产" + i);
            data.add("88" + i);
            dataList.add(data);
        }
        emergencyReportingEntity.setData(dataList);
        list.add(emergencyReportingEntity);
    }


 public Workbook xlsOutputResourcePreparation(List<EmergencyReportingEntity> list) {

        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("社会治理资源准备情况");
        for (int i = 0; i < 4; i++) {
            sheet.setColumnWidth(i, 10000);
        }
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 1));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 3));

        setStyle(workbook, sheet, list, "社会治理资源准备情况");
        return workbook;
    }
    /**
     * Excel样式设计，数据写入
     *
     * @param workbook
     * @param sheet
     * @param list
     * @param sheetName
     */
    public void setStyle(HSSFWorkbook workbook, HSSFSheet sheet, List<EmergencyReportingEntity> list, String sheetName) {

        // 表头
        HSSFRow rowReportTitle = sheet.createRow(0);
        Cell cell1 = rowReportTitle.createCell(0); // 0列
        // 设置值
        cell1.setCellValue(sheetName);

        // 合并表头

        rowReportTitle.setHeight((short) 600); // 行高

        //设置表头字体
        Font headFont = workbook.createFont();
        headFont.setFontName("宋体");
        headFont.setFontHeightInPoints((short) 20);// 字体大小

        CellStyle headStyle = workbook.createCellStyle();
        headStyle.setFont(headFont);
        headStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
        headStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
        // 头部样式添加
        cell1.setCellStyle(headStyle);

        //设置主标题
        Font mainFont = workbook.createFont();
        mainFont.setFontName("宋体");
        mainFont.setFontHeightInPoints((short) 16);// 字体大小

        CellStyle mainStyle = workbook.createCellStyle();
        mainStyle.setFont(mainFont);
        mainStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
        mainStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中

        //设置副标题
        Font subFont = workbook.createFont();
        subFont.setFontName("宋体");
        subFont.setFontHeightInPoints((short) 10);// 字体大小

        CellStyle subStyle = workbook.createCellStyle();
        subStyle.setFont(subFont);
        subStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
        subStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中

        //表的样式
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);// 左右居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中

        AtomicInteger rowIndex = new AtomicInteger(1);
        AtomicInteger columnIndex = new AtomicInteger(0);
        list.forEach(data -> {
            AtomicInteger rowOffset = new AtomicInteger(0);
            AtomicInteger columnOffset = new AtomicInteger(0);

            HSSFRow mainRow = sheet.getRow(rowIndex.get() + rowOffset.get()) == null ? sheet.createRow(rowIndex.get() + rowOffset.getAndIncrement()) : sheet.getRow(rowIndex.get() + rowOffset.getAndIncrement());
            HSSFCell cell = mainRow.createCell(columnIndex.get());
            cell.setCellValue(data.getMainTitle());
            cell.setCellStyle(mainStyle);
            mainRow.setHeight((short) 400);

            List<String> subTitle = data.getSubTitle();
            subTitle.forEach(sub -> {
                HSSFRow subRow = sheet.getRow(rowIndex.get() + rowOffset.get()) == null ? sheet.createRow(rowIndex.get() + rowOffset.get()) : sheet.getRow(rowIndex.get() + rowOffset.get());
                HSSFCell cell2 = subRow.createCell(columnIndex.get() + columnOffset.getAndIncrement());
                cell2.setCellValue(sub);
                cell2.setCellStyle(subStyle);
                subRow.setHeight((short) 300);
            });
            rowOffset.getAndIncrement();
            columnOffset.set(0);
            List<EmergencyResourcesVo> excelData = data.getData();
            excelData.forEach(excel -> {
//                excel.forEach(o -> {
                HSSFRow dataRow = sheet.getRow(rowIndex.get() + rowOffset.get()) == null ? sheet.createRow(rowIndex.get() + rowOffset.get()) : sheet.getRow(rowIndex.get() + rowOffset.get());
                HSSFCell cell2 = dataRow.createCell(columnIndex.get() + columnOffset.getAndIncrement());
                cell2.setCellValue(excel.getName());
                cell2.setCellStyle(style);

//                    dataRow.setRowNum();
                HSSFRow dataRow1 = sheet.getRow(rowIndex.get() + rowOffset.get()) == null ? sheet.createRow(rowIndex.get() + rowOffset.get()) : sheet.getRow(rowIndex.get() + rowOffset.get());
                dataRow1.setRowStyle(style);
                HSSFCell cell3 = dataRow1.createCell(columnIndex.get() + columnOffset.getAndIncrement());
                if (excel.getCount() == null) {
                    excel.setCount(0);
                }
                cell3.setCellValue(excel.getCount());
                cell3.setCellStyle(style);
//                });
                rowOffset.getAndIncrement();
                columnOffset.set(0);
            });
            columnIndex.set(columnIndex.get() + subTitle.size());
//            columnIndex.set(subTitle.size());

        });
    }

controller

 @RequestMapping("/outputEventReporting")
    public void xlsOutputConditionsEmergencyData(HttpServletResponse response) throws Exception {

        response.setContentType("application/octet-stream;charset=UTF-8");
        response.addHeader("Access-Control-Expose-Headers", "Content-Disposition");
        response.addHeader("Content-Disposition", "attachment;fileName=" + new String(("测试情况导出.xls").getBytes(), "ISO8859-1" ) );
        performanceStatisticsService.outputEventReporting().write(response.getOutputStream());
    }