poi导出excel方法

/**
     * 带分类标题导出Excel的方法
     *
     * @param title        excel中的sheet名称
     * @param header     两列的头的标题  单层表头集合
     * @param parentHeader 分类列  双层表头父表头
     * @param childSize    分类列行数   将子标题长度保存起来
     * @param childHeader  分类列行数  双层表头子表头
     * @param columns      列名  标题key,用于取表格中各列的值
     * @param result       结果集
     * @param response     响应
     * @param pattern      时间格式
     * @param columnWidth  列宽  每列的宽度集合
     * @param mergeColumn  需要合并几列,1表示合并第一列,2表示第一列和第二列都要合并
     */
    public void exportoExcel(String title, List<String> header, List<String> parentHeader, List<Integer> childSize,
                             List<String> childHeader, List<String> columns, Collection<T> result, HttpServletResponse response,
                             String pattern, List<Integer> columnWidth, int mergeColumn){
        OutputStream out = null;
        try {
            //设置头信息为一次只能下载一个
            response.setContentType("application/binary;charset=UTF-8");
            //设置前端能拿取到除了固定的访问头之外的访问头
            response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
            response.setHeader("Access-Control-Allow-Credentials", "true");
            String fileName = ExportChineseProcessing.chineseProcess(title, ".xls");
            response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"; filename*=utf-8''" + fileName);

            out = response.getOutputStream();
            // 声明一个工作薄
            HSSFWorkbook workbook = new HSSFWorkbook();
            // 生成一个表格
            HSSFSheet sheet = workbook.createSheet(title);

            //设置表头样式
            ExportUtil exportUtil = new ExportUtil(workbook, sheet);
            HSSFCellStyle headStyle = exportUtil.getHeadStyle();

            // 声明一个画图的顶级管理器
            HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

            HSSFCellStyle leftBodyStyle = exportUtil.getBodyStyle(HorizontalAlignment.LEFT);
            HSSFCellStyle rightBodyStyle = exportUtil.getBodyStyle(HorizontalAlignment.RIGHT);

            //构建大表头,第一行
            //和并单元格,1和2表示行的开始到行的结束,3和4表示列的开始到列结束
            //参数:起始行号，终止行号， 起始列号，终止列号
            CellRangeAddress cra = new CellRangeAddress(0, (short) 0, 0, (short) (columns.size() - 1));
            sheet.addMergedRegion(cra);
            Row row = sheet.createRow(0);
            row.setHeight((short) 1000);
            Cell row0 = row.createCell(0);
            row0.setCellValue(title);
            row0.setCellStyle(headStyle);

            //小表头 第二行和第三行合并
            //是否需要合并列
            boolean isMergeRow = false;
            //单元格集合,为后续添加边框用
            List<CellRangeAddress> cellSetBorderList = new ArrayList<>();

            //根据header_2的个数,填充多少个需要合并两行的列,剩余的分别在各自的行上横向合并
            row = sheet.createRow(1);
            //如果双层表头父表头和字表头都为空则不合并
            if (parentHeader != null && parentHeader.size() != 0 && childHeader != null && childHeader.size() != 0) {
                if (header != null && header.size() > 0) {
                    for (int i = 0; i < header.size(); i++) {
                        cra = new CellRangeAddress(1, 2, i, i);
                        sheet.addMergedRegion(cra);
                        cellSetBorderList.add(cra);
                        final Cell cell = row.createCell(i);
                        cell.setCellStyle(headStyle);
                        cell.setCellValue(header.get(i));
                        isMergeRow = true;
                    }
                }
                //双层表头
                //header_cate双层表头父表头
                int startCol = header == null ? 0 : header.size();
                int endCol = header == null ? -1 : header.size() - 1;
                for (int i = 0; i < parentHeader.size(); i++) {
                    //header.size()表示单层标题的个数,columns.get(i)表示第几个双层标题下的子标题个数
                    //如果父表体的长度不等于1则合并,否则直接填充值
                    if (childSize.get(i) != 1) {
                        cra = new CellRangeAddress(1, 1, startCol += (i == 0 ? 0 : childSize.get(i - 1)),
                                endCol += (childSize.get(i)));
                        sheet.addMergedRegion(cra);
                        cellSetBorderList.add(cra);
                    } else {
                        startCol += (i == 0 ? 0 : childSize.get(i - 1));
                        endCol += (childSize.get(i));
                    }
                    final Cell cell = row.createCell(startCol);
                    cell.setCellStyle(headStyle);
                    cell.setCellValue(parentHeader.get(i));
                }
                //双层表头子表头
                row = sheet.createRow(2);
                for (int i = 0; i < childHeader.size(); i++) {
                    final Cell cell = row.createCell(i + (header == null ? 0 : header.size()));
                    cell.setCellStyle(headStyle);
                    cell.setCellValue(childHeader.get(i));
                }
            } else {
                for (int i = 0; i < header.size(); i++) {
                    final Cell cell = row.createCell(i);
                    cell.setCellStyle(headStyle);
                    cell.setCellValue(header.get(i));
                }
            }

            //将第一列上填充值
            int index = 2;
            if (isMergeRow) {
                index = 3;
            }
            for (T t : result) {
                row = sheet.createRow(index++);
                for (short j = 0; j < mergeColumn; j++) {
                    String fieldName = columns.get(j);
                    Cell cell = row.createCell(j);
                    //判断是否有取对象中的值
                    String[] name = null;
                    if (fieldName.contains(".")) {
                        String[] strName = fieldName.split("\\.");
                        fieldName = strName[0];
                        name = strName[1].replace("[", "").replace("]", "")
                                .split("\\+");
                    }
                    //列名
                    Object value;
                    value = getValueOrFormatValue(t, fieldName, name);
                    //设置值
                    String textValue = null;
                    textValue = getTextValue(pattern, workbook, sheet, patriarch, row, index, j, name, value, textValue);
                    HSSFRichTextString richString = new HSSFRichTextString(textValue);
                    cell.setCellValue(richString);
                    cell.setCellStyle(leftBodyStyle);
                }
            }
            //是否需要合并列
            boolean isMergeCell = false;
            //下面去值时从第几行开始
            index = 2;
            if (isMergeRow) {
                index = 3;
            }
            //如果所有的行数大与2则开始判断是否要合并
            if (sheet.getLastRowNum() > 2) {
                //得到第三行
                HSSFRow row_1 = sheet.getRow(index);
                //这里暂时用于mergeColumn列中的上下行合并
                for (int j = 0; j < mergeColumn; j++) {
                    //得到第一列
                    HSSFCell cell_1 = row_1.getCell(j);
                    //得到第一列的值
                    String departname = cell_1.getStringCellValue();
                    //从第四行开与前一行比较,如果一致则设置值为空
                    for (int i = (index + 1); i <= sheet.getLastRowNum(); i++) {
                        HSSFRow rows = sheet.getRow(i);
                        //此处表示对单元格进行内容相同合并处理
                        HSSFCell cells_1 = rows.getCell(j);
                        //这里值相同则设置位空，方便之后的合并
                        if (departname.equals(cells_1.getStringCellValue())) {
                            cells_1.setCellValue("");
                            isMergeCell = true;
                        } else {
                            departname = cells_1.getStringCellValue();
                        }
                    }
                }
            }
            if (isMergeCell) {
                //将为空的单元格与之前不为空的合并
                for (int j = 0; j < mergeColumn; j++) {
                    int sk = (isMergeRow ? 3 : 2);
                    for (int i = (isMergeRow ? 4 : 3); i <= sheet.getLastRowNum(); i++) {
                        HSSFRow rows = sheet.getRow(i);
                        HSSFCell cell_0 = rows.getCell(j);
                        //如果为空但还没对比到最后一行，继续循环
                        if ("".equals(cell_0.getStringCellValue())) {
                            if (i == sheet.getLastRowNum()) {
                                CellRangeAddress cellRangeAddress = new CellRangeAddress(sk, i, j, j);
                                //如果已经对比到最后一行，开始合并
                                sheet.addMergedRegion(cellRangeAddress);
                                cellSetBorderList.add(cellRangeAddress);
                            }
                        } else {
                            //不为空且i-1不为sk则合并
                            if (sk != i - 1) {
                                CellRangeAddress cellRangeAddress = new CellRangeAddress(sk, i - 1, j, j);
                                sheet.addMergedRegion(cellRangeAddress);
                                cellSetBorderList.add(cellRangeAddress);
                            }
                            sk = i;
                        }
                    }
                }
            }
            //添加边框
            for (CellRangeAddress cellRangeAddress : cellSetBorderList) {
                exportUtil.setBorder(cellRangeAddress);
            }
            // 遍历集合数据，产生数据行
            if (result != null) {
                for (T t : result) {
                    row = sheet.getRow(index++);
                    for (short i = (short) mergeColumn; i < columns.size(); i++) {
                        Cell cell = row.createCell(i);
                        String fieldName = columns.get(i);
                        //判断是否有取对象中的值
                        String[] name = null;
                        if (fieldName.contains(".")) {
                            String[] strName = fieldName.split("\\.");
                            fieldName = strName[0];
                            name = strName[1].replace("[", "").replace("]", "")
                                    .split("\\+");
                        }
                        Object value;
                        //格式化数据或通过可以获取数据
                        value = getValueOrFormatValue(t, fieldName, name);
                        String textValue = null;
                        textValue = getTextValue(pattern, workbook, sheet, patriarch, row, index, i, name, value, textValue);

                        if (textValue != null) {
                            Pattern p = Pattern.compile("^//d+(//.//d+)?$");
                            Matcher matcher = p.matcher(textValue);
                            if (matcher.matches()) {
                                // 是数字当作double处理
                                cell.setCellValue(Double.parseDouble(textValue));
                                cell.setCellStyle(rightBodyStyle);
                            } else {
                                HSSFRichTextString richString = new HSSFRichTextString(textValue);
                                cell.setCellValue(richString);
                                cell.setCellStyle(leftBodyStyle);
                            }
                        }
                    }
                }
            }
            // 设置表格默认列宽度为20个字节
            if (columnWidth == null || columnWidth.size() == 0) {
                sheet.setDefaultColumnWidth((short) 10);
            } else {
                for (int i = 0; i < columns.size(); i++) {
                    sheet.setColumnWidth(i, columnWidth.get(i) * 256);
                }
            }
            // 自适应调整宽度
//        for (int i = 0; i < columns.size(); i++) {
//            sheet.autoSizeColumn(i);
//        }
            workbook.write(out);
            out.flush();
            out.close();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                if (out != null){
                    out.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }