Java实现复杂excel格式导出

根据公司业务需求，导出复杂格式的Excel表格，主要是单元格的合并。

效果图如下：

 标题、表头、内容样式

具体根据各自业务需求对参数进行调整

   /**
     * 创建标题样式
     *
     * @param wb
     * @return
     */
    private static HSSFCellStyle createTitleCellStyle(HSSFWorkbook wb) {
        HSSFCellStyle cellStyle = wb.createCellStyle();
        //水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //垂直对齐
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //背景颜色
        cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
        // 创建字体样式
        HSSFFont headerFont1 = (HSSFFont) wb.createFont();
        //字体加粗
        headerFont1.setBold(true);
        // 设置字体类型
        headerFont1.setFontName("黑体");
        // 设置字体大小
        headerFont1.setFontHeightInPoints((short) 15);
        // 为标题样式设置字体样式
        cellStyle.setFont(headerFont1);

        return cellStyle;
    }

/**
     * 创建表头样式
     *
     * @param wb
     * @return
     */
    private static HSSFCellStyle createHeadCellStyle(HSSFWorkbook wb) {
        HSSFCellStyle cellStyle = wb.createCellStyle();
        // 设置自动换行
        cellStyle.setWrapText(true);
        //背景颜色
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        //水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //垂直对齐
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
        //下边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        //左边框
        cellStyle.setBorderLeft(BorderStyle.THIN);
        //右边框
        cellStyle.setBorderRight(BorderStyle.THIN);
        //上边框
        cellStyle.setBorderTop(BorderStyle.THIN);
        // 创建字体样式
        HSSFFont headerFont = (HSSFFont) wb.createFont();
        //字体加粗
        headerFont.setBold(true);
        // 设置字体类型
        headerFont.setFontName("黑体");
        // 设置字体大小
        headerFont.setFontHeightInPoints((short) 12);
        // 为标题样式设置字体样式
        cellStyle.setFont(headerFont);

        return cellStyle;
    }

具体实现

 /** 第一步，创建一个Workbook，对应一个Excel文件  */
        HSSFWorkbook wb = new HSSFWorkbook();

        /** 第二步，在Workbook中添加一个sheet,对应Excel文件中的sheet  */
        HSSFSheet sheet = wb.createSheet("excel导出标题");

        /** 第三步，设置样式以及字体样式*/
        HSSFCellStyle titleStyle = createTitleCellStyle(wb);
        HSSFCellStyle headerStyle = createHeadCellStyle(wb);
        HSSFCellStyle contentStyle = createContentCellStyle(wb);

        /** 第四步，创建标题 ,合并标题单元格 */
        // 行号
        int rowNum = 0;
        // 创建第一页的第一行，索引从0开始
        HSSFRow row0 = sheet.createRow(rowNum++);
        row0.setHeight((short) 800);// 设置行高

        String title = "标题名";
        HSSFCell c00 = row0.createCell(0);
        c00.setCellValue(title);
        c00.setCellStyle(titleStyle);
        // 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）
        //标题合并单元格操作，7为总列数
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
        // 第二行
        HSSFRow row1 = sheet.createRow(rowNum++);
        row1.setHeight((short) 500);
        sheet.setColumnWidth(0, (short) 5000);
        sheet.setColumnWidth(1, (short) 5000);
        sheet.setColumnWidth(2, (short) 5000);
        sheet.setColumnWidth(3, (short) 5000);
        sheet.setColumnWidth(4, (short) 5000);
        sheet.setColumnWidth(5, (short) 5000);
        sheet.setColumnWidth(6, (short) 5000);
        sheet.setColumnWidth(7, (short) 5000);
        //第二行表头名称 
        String[] row_first = {"", "", "", "", "", "", "", ""};
        for (int i = 0; i < row_first.length; i++) {
            HSSFCell tempCell = row1.createCell(i);
            tempCell.setCellStyle(headerStyle);
            tempCell.setCellValue(row_first[i]);
        }
        //查询出来的数据 直接调用查询接口  
        List<Query> retrieve = exportExcelService.getList();

        //加多一条list
        for (Query excelData : retrieve) {
            HSSFRow tempRow = sheet.createRow(rowNum++);
            tempRow.setHeight((short) 500);
            // 循环单元格填入数据
            for (int j = 0; j < 8; j++) {
                HSSFCell tempCell = tempRow.createCell(j);
                tempCell.setCellStyle(contentStyle);
                String tempValue;
                if (j == 0) {
                    //get对应的字段名称 
                    tempValue = excelData.getConstructionUnit();
                } else if (j == 1) {
                    tempValue = excelData.getTarget();
                } else if (j == 2) {
                    tempValue = excelData.getSuccessesNumber().toString();
                } else if (j == 3) {
                    tempValue = excelData.getCompletedNumber().toString();
                } else if (j == 4) {
                    tempValue = excelData.getCompletionRate();
                } else if (j == 5) {
                    tempValue = excelData.getWhetherPublishList();
                } else if (j == 6) {
                    tempValue = excelData.getAdministrativeDistrict();
                } else {
                    tempValue = excelData.getEconomicInformationTarget().toString();
                }
                tempCell.setCellValue(tempValue);
            }
        }
        //根据业务需求对单元格进行合并   参数依次为起始行，结束行，起始列，结束列 （索引0开始）
        sheet.addMergedRegion(new CellRangeAddress(8, 10, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(8, 10, 2, 2));
        sheet.addMergedRegion(new CellRangeAddress(8, 10, 3, 3));
        sheet.addMergedRegion(new CellRangeAddress(8, 10, 4, 4));
        sheet.addMergedRegion(new CellRangeAddress(12, 13, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(12, 13, 2, 2));
        sheet.addMergedRegion(new CellRangeAddress(12, 13, 3, 3));
        sheet.addMergedRegion(new CellRangeAddress(12, 13, 4, 4));
        sheet.addMergedRegion(new CellRangeAddress(7, 8, 6, 6));
        sheet.addMergedRegion(new CellRangeAddress(7, 8, 7, 7));
        sheet.addMergedRegion(new CellRangeAddress(10, 12, 6, 6));
        sheet.addMergedRegion(new CellRangeAddress(10, 12, 7, 7));


        String nowMonthDate = DateTimeUtil.getNowMonthDate();
        String fileName = "表名" + nowMonthDate + ".xls";
        try {
            fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
            response.setHeader("Content-disposition", "attachment;filename=\"" + fileName + "\"");
            OutputStream stream = response.getOutputStream();
            if (null != wb && null != stream) {
                wb.write(stream);// 将数据写出去
                wb.close();
                stream.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }