Java实现POI导出excel复杂表头

1. 主方法加数据和excel表头布局

/**
     * 导出
     * @param request
     * @param response
     * @throws IOException
     */
    @RequestMapping("/exportMultipleItemStatisticsList.do")
    public void export(HttpServletRequest request, HttpServletResponse response) throws IOException {
        String startTime = request.getParameter("startTime")==null ?"":request.getParameter("startTime").toString();//开始时间
        String endTime=request.getParameter("endTime")==null ?"":request.getParameter("endTime").toString();//结束时间
        String category = request.getParameter("category")==null ?"":request.getParameter("category").toString();//场点类型
        String reportcode=request.getParameter("reportcode")==null ?"":request.getParameter("reportcode").toString();//受检编号
        String entername=request.getParameter("entername")==null ?"":request.getParameter("entername").toString();//受检单位
        String immName = request.getParameter("immName")==null ?"":request.getParameter("immName").toString();//是否免疫
        String areaName = request.getParameter("areaName")==null ?"":request.getParameter("areaName").toString();//地市
        Map entityMap=new HashMap();
        entityMap.put("startTime",startTime);
        entityMap.put("endTime",endTime);
        entityMap.put("category",category);
        entityMap.put("reportcode",reportcode);
        entityMap.put("entername",entername);
        entityMap.put("immName",immName);
        entityMap.put("areaName",areaName);
        List<Map> list=multipleItemStatisticsService.getMultipleItemStatisticsList(entityMap);
        //1.创建一个workbook,对应一个excel文件
        HSSFWorkbook wb = new HSSFWorkbook();

        //2.在workbook中添加一个sheet,对应Excel中的sheet
        HSSFSheet sheet = wb.createSheet("多重检测项目明细");

        //设置每一列的列宽
        sheet.setColumnWidth(0,256*15);
        sheet.setColumnWidth(1,256*15);
        sheet.setColumnWidth(2,256*15);
        sheet.setColumnWidth(3,256*15);
        sheet.setColumnWidth(4,256*15);
        sheet.setColumnWidth(5,256*15);
        sheet.setColumnWidth(6,256*15);
        sheet.setColumnWidth(7,256*15);
        sheet.setColumnWidth(8,256*15);
        sheet.setColumnWidth(9,256*15);
        sheet.setColumnWidth(10,256*15);
        sheet.setColumnWidth(11,256*15);
        sheet.setColumnWidth(12,256*15);
        sheet.setColumnWidth(13,256*15);
        sheet.setColumnWidth(14,256*15);
//        sheet.setColumnWidth(15,256*15);

        //3.设置样式以及字体样式
        HSSFCellStyle titleStyle = ExcelUtils.createTitleCellStyle(wb);
        HSSFCellStyle headerStyle = ExcelUtils.createHeadCellStyle(wb);
        HSSFCellStyle contentStyle = ExcelUtils.createContentCellStyle(wb);

        //4.创建标题，合并标题单元格
        //行号
        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开始）
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 14));//标题合并单元格操作，总列数为16

        //第二行
        HSSFRow row1 = sheet.createRow(rowNum++);
        row1.setHeight((short)500);
        String[] row_first = {"受检编号","样品编号","受检单位","地市","监测动物种类","年龄阶段","监测场点类型","饲养量","样品总量",list.get(0).get("name").toString(),"","","","",""};
        for (int i = 0; i < row_first.length; i++) {
            HSSFCell tempCell = row1.createCell(i);
            tempCell.setCellValue(row_first[i]);
            tempCell.setCellStyle(headerStyle);
        }
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(1, 3, 0, 0));//受检编号
        sheet.addMergedRegion(new CellRangeAddress(1, 3, 1, 1));//样品编号
        sheet.addMergedRegion(new CellRangeAddress(1, 3, 2, 2));//受检单位
        sheet.addMergedRegion(new CellRangeAddress(1, 3, 3, 3));//地市
        sheet.addMergedRegion(new CellRangeAddress(1, 3, 4, 4));//监测动物种类
        sheet.addMergedRegion(new CellRangeAddress(1, 3, 5, 5));//年龄阶段
        sheet.addMergedRegion(new CellRangeAddress(1, 3, 6, 6));//年龄阶段
        sheet.addMergedRegion(new CellRangeAddress(1, 3, 7, 7));//年龄阶段
        sheet.addMergedRegion(new CellRangeAddress(1, 3, 8, 8));//年龄阶段
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 9, 14));//截止2019年12月27日业务明细

        //第三行
        HSSFRow row2 = sheet.createRow(rowNum++);
        row2.setHeight((short)500);
        String[] row_second = {"","","","","","","","","",list.get(0).get("standard_name0").toString(),"","",list.get(0).get("standard_name1").toString(),"",""};
        for (int i = 0; i < row_second.length; i++) {
            HSSFCell tempCell = row2.createCell(i);
            tempCell.setCellValue(row_second[i]);
            tempCell.setCellStyle(headerStyle);
        }

        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 9, 11));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 12, 14));

        //第四行
        HSSFRow row3 = sheet.createRow(rowNum++);
        row3.setHeight((short)500);
        String[] row_third = {"","","","","","","","","","检测样品数","阳性数","阳性率（%）","检测样品数","阳性数","阳性率（%）"};
        for (int i = 0; i < row_third.length; i++) {
            HSSFCell tempCell = row3.createCell(i);
            tempCell.setCellValue(row_third[i]);
            tempCell.setCellStyle(headerStyle);
        }

        //查询月度明细列表
//        List<BusinessDetail> list = businessService.selectMonthBusinessList(businessDetail);
        for(int i = 0;i<list.size();i++){
            HSSFRow tempRow = sheet.createRow(rowNum++);
            tempRow.setHeight((short)500);
            //循环单元格填入数据
            for(int j=0;j<15;j++){
                HSSFCell tempCell = tempRow.createCell(j);
                tempCell.setCellStyle(contentStyle);
                String cellValue = "";
                if(j ==0){
                    //受检编号
                    cellValue = list.get(i).get("reportcode").toString();
                }else if(j == 1){
                    //样品编号
                    cellValue = list.get(i).get("roundsamplecode").toString();
                }else if(j ==2){
                    //受检单位
                    if(list.get(i).get("takeinspectionunit") ==null){
                        cellValue = "";
                    }else{
                        cellValue = list.get(i).get("takeinspectionunit").toString();
                    }
                }else if(j == 3){
                    //地市
                    if(list.get(i).get("areaName") ==null){
                        cellValue = "0";
                    }else{
                        cellValue = list.get(i).get("areaName").toString();
                    }
                }else if(j ==4){
                    //监测动物种类
                    if(list.get(i).get("animal") ==null){
                        cellValue = "0";
                    }else{
                        cellValue = list.get(i).get("animal").toString();
                    }
                }else if(j == 5){
                    //年龄阶段
                    if(list.get(i).get("sampleAnimalName") ==null){
                        cellValue = "0";
                    }else{
                        cellValue = list.get(i).get("sampleAnimalName").toString();
                    }
                }else if(j == 6){
                    //监测场点类型
                    if(list.get(i).get("category") ==null){
                        cellValue = "0";
                    }else{
                        cellValue = list.get(i).get("category").toString();
                    }
                }else if(j ==7){
                    //饲养量
                    if(list.get(i).get("sampleSumNum") ==null){
                        cellValue = "0";
                    }else{
                        cellValue = list.get(i).get("sampleSumNum").toString();
                    }
                }else if(j == 8){
                    //样品总量
                    if(list.get(i).get("allNum") ==null){
                        cellValue = "0";
                    }else{
                        cellValue = list.get(i).get("allNum").toString();
                    }
                }else if(j == 9){
                    //a检测样品数
                    if(list.get(i).get("sampleNum0") ==null){
                        cellValue = "0";
                    }else{
                        cellValue = list.get(i).get("sampleNum0").toString();
                    }
                }else if(j == 10){
                    //阳性数
                    if(list.get(i).get("positiveNum0") ==null){
                        cellValue = "0";
                    }else{
                        cellValue = list.get(i).get("positiveNum0").toString();
                    }
                }else if(j == 11){
                    //阳性率（%）
                    if(list.get(i).get("sun0") ==null){
                        cellValue = "0";
                    }else{
                        cellValue = list.get(i).get("sun0").toString();
                    }
                }else if( j == 12){
                    //a检测样品数
                    if(list.get(i).get("sampleNum1") ==null){
                        cellValue = "0";
                    }else{
                        cellValue = list.get(i).get("sampleNum1").toString();
                    }
                }else if(j == 13){
                    //阳性数
                    if(list.get(i).get("positiveNum1") ==null){
                        cellValue = "0";
                    }else{
                        cellValue = list.get(i).get("positiveNum1").toString();
                    }
                }else if(j ==14){
                    //阳性率（%）
                    if(list.get(i).get("sun1") ==null){
                        cellValue = "0";
                    }else{
                        cellValue = list.get(i).get("sun1").toString();
                    }
                }
                tempCell.setCellValue(cellValue);
            }
        }

        //导出excel
//        HttpServletResponse response = this.getResponse();
        String fileName = "月度业务明细.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();
            }
//            FileOutputStream outputStream = new FileOutputStream(new File("d:\\月度业务明细.xls"));
//            wb.write(outputStream);
//            outputStream.close();
        }catch (Exception e){
            e.printStackTrace();
        }
//        return null;
    }

2.对应样式单独加到一个工具类中

/**
 * 导出excel设置的样式
 */
public class ExcelUtils {
    /**
     * 创建标题样式
     * @param wb
     * @return
     */
    public static HSSFCellStyle createTitleCellStyle(HSSFWorkbook wb){
        HSSFCellStyle cellStyle = wb.createCellStyle();
        //水平居中
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //垂直对齐
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//        cellStyle.setFillPattern(HSSFCellStyle.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
     */
    public static HSSFCellStyle createHeadCellStyle(HSSFWorkbook wb){
        HSSFCellStyle cellStyle = wb.createCellStyle();
        //设置自动换行
        cellStyle.setWrapText(true);
        //设置背景颜色
//        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        //水平居中
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //垂直对齐
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//        cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
        //下边框
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //左边框
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        //右边框
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //上边框
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_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(HSSFWorkbook wb){
        HSSFCellStyle cellStyle = wb.createCellStyle();
        //水平居中
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //垂直居中
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //设置自动换行
        cellStyle.setWrapText(true);
        //上边框
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //下边框
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //左边框
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        //右边框
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

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

        return cellStyle;
    }

}

3.只需要以上配置即可，保存路径在页面请求自定义路径。

window.location.href = excel_url;