下载Excel模板文件(标题行合并)

public class DownloadExcelTemplateUtils {

    public static void downloadExcel(HttpServletResponse response) {
        /** 第一步，创建一个Workbook，对应一个Excel文件  */
        Workbook wb = new HSSFWorkbook();

        /** 第二步，在Workbook中添加一个sheet,对应Excel文件中的sheet  */
        Sheet sheet = wb.createSheet("用户信息");

        /** 第三步，设置样式以及字体样式*/
        CellStyle headerStyle = createHeadCellStyle(wb);

        /** 第四步，创建标题 ,合并标题单元格 */
        // 行号
        int rowNum = 0;

        //第一行
        Row row1 = sheet.createRow(rowNum++);
        row1.setHeight((short) 500);
        String[] row_second = {"身份证号", "姓名", "登录账号", "性别", "联系电话", "家庭地址", "", "", "", "紧急联系人电话", "单位", "部门"};
        for (int i = 0; i < row_second.length; i++) {
            Cell tempCell = row1.createCell(i);
            tempCell.setCellValue(row_second[i]);
            tempCell.setCellStyle(headerStyle);
        }
        // 合并
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));//身份证号
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));//姓名
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2));//登录账号
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 3, 3));//性别
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 4, 4));//联系电话
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 5, 8));//家庭地址
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 9, 9));//紧急联系人电话
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 10, 10));//单位
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 11, 11));//部门

        //第二行
        Row row2 = sheet.createRow(rowNum++);
        row2.setHeight((short) 500);
        String[] row_third = {"", "","","","","省", "市", "区县", "详细地址","","",""};
        for (int i = 0; i < row_third.length; i++) {
            Cell tempCell = row2.createCell(i);
            tempCell.setCellValue(row_third[i]);
            tempCell.setCellStyle(headerStyle);
        }

        sheet.setColumnWidth(0, 30 * 256);//身份证
        sheet.setColumnWidth(1, 15 * 256);//姓名
        sheet.setColumnWidth(2, 20 * 256);//登录账号
        sheet.setColumnWidth(3, 15 * 256);//性别
        sheet.setColumnWidth(4, 15 * 256);//联系电话
        sheet.setColumnWidth(5, 10 * 256);//省
        sheet.setColumnWidth(6, 10 * 256);//市
        sheet.setColumnWidth(7, 10 * 256);//县
        sheet.setColumnWidth(8, 20 * 256);//详细地址
        sheet.setColumnWidth(9, 20 * 256);//紧急联系人电话
        sheet.setColumnWidth(10, 15 * 256);//单位
        sheet.setColumnWidth(11, 15 * 256);//部门

        //导出
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=excel.xls");
            OutputStream stream = response.getOutputStream();
            if (null != wb && null != stream) {
                wb.write(stream);// 将数据写出去
                wb.close();
                stream.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 创建标题样式
     * @param wb
     * @return
     */
    private static CellStyle createTitleCellStyle(Workbook wb) {
        CellStyle 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 CellStyle createHeadCellStyle(Workbook wb) {
        CellStyle 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;
    }

    /**
     * 创建内容样式
     * @param wb
     * @return
     */
    private static CellStyle createContentCellStyle(Workbook wb) {
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
        cellStyle.setWrapText(true);// 设置自动换行
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框
        // 生成12号字体
        Font font = wb.createFont();
        font.setColor((short)8);
        font.setFontHeightInPoints((short) 12);
        cellStyle.setFont(font);
        return cellStyle;
    }
}