POI原生方法导出复杂Excel示例代码

public ResponseBean exportExcel(QueryData data, HttpServletResponse response) {

//省略数据准备代码，根据具体业务完成数据准备

//导出模板
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();

//表头，createHeadCellStyle方法见下方代码，主要就是关于excel样式的设置
HSSFCellStyle headerStyle = ExcelUtil.createHeadCellStyle(wb);

//数据内容，createContentCellStyle方法见下代码，主要就是关于excel样式的设置
HSSFCellStyle contentStyle = ExcelUtil.createContentCellStyle(wb);

//声明一个变量来保存Excel的行数
int rowNum=0;

//使用sheet的createRow方法画下第一行
HSSFRow row = sheet.createRow(rowNum++);

//设置每一个单元格的宽高
sheet.setColumnWidth(0,180*55);
sheet.setColumnWidth(1,143*55);
sheet.setColumnWidth(2,143*55);
sheet.setColumnWidth(3,143*55);
sheet.setColumnWidth(4,143*55);
sheet.setColumnWidth(5,143*55);
sheet.setColumnWidth(6,143*55);
sheet.setColumnWidth(7,143*55);
sheet.setColumnWidth(8,143*55);

//声明row_first数组，保存每一个单元格的内容
String[] row_first={"",examName,"","","","","","",""};
for(int i=0;i<row_first.length;i++){

//使用行的方法创建单元格   
 HSSFCell tempCell = row.createCell(i);
//给单元格设置内容值
    tempCell.setCellValue(row_first[i]);
//给单元格设置表头样式
    tempCell.setCellStyle(headerStyle);
}

//可以简单总结为：sheet.createRow(rowNum++)方法画行；
//row.createCell(i)方法画每一行的单元格。

//最后复杂表头肯定少不了合并单元格了；
//使用sheet的addMergedRegion()方法设置单元格合并，注意同一个格子不能存在于两个合并单元格中，会发生错误，行和列的计数都是由0开始；
//四个入参分别是：起始行，终止行，起始列，终止列。

sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 8));
sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 2));
sheet.addMergedRegion(new CellRangeAddress(1, 1, 3, 7));
sheet.addMergedRegion(new CellRangeAddress(2, 3, 8, 8));

//定义一个到处的Excel文件名
String fileName = "导出结果详情.xls";

try {

    //设置返回的response参数
    response.setCharacterEncoding("UTF-8");
    response.setHeader("content-Type", "application/vnd.ms-excel");
    response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
    response.setHeader( "Access-Control-Expose-Headers", "Content-disposition");
    wb.write(response.getOutputStream());

} catch (IOException e) {

//写你的异常处理逻辑

}

}

/**
 * 创建表头样式
 * @param wb
 * @return
 */

public static HSSFCellStyle createHeadCellStyle(Workbook wb){
    HSSFCellStyle cellStyle = (HSSFCellStyle)wb.createCellStyle();
    //设置自动换行
    cellStyle.setWrapText(true);
    //设置背景颜色
     cellStyle.setFillForegroundColor(IndexedColors.WHITE.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
 */

public static HSSFCellStyle createContentCellStyle(Workbook wb){
    HSSFCellStyle cellStyle = (HSSFCellStyle)wb.createCellStyle();
    //水平居中
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    //垂直居中
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

    //设置自动换行
    cellStyle.setWrapText(true);
    //设置背景颜色
    cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());

    //设置边框
    cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);

    //上边框
    cellStyle.setBorderTop(BorderStyle.THIN);
    //下边框
    cellStyle.setBorderBottom(BorderStyle.THIN);
    //左边框
    cellStyle.setBorderLeft(BorderStyle.THIN);
    //右边框
    cellStyle.setBorderRight(BorderStyle.THIN);

    //设置字体
    HSSFFont font = (HSSFFont)wb.createFont();
    font.setColor((short)8);
    font.setFontHeightInPoints((short)12);

    return cellStyle;
}