java导出标题多行且合并单元格的EXCEL

场景：项目中遇到有需要导出Excel的需求，并且是多行标题且有合并单元格的，参考网上的文章，加上自己的理解，封装成了可自由扩展的导出工具

先上效果，再贴代码：

调用工具类进行导出：

 public static void main(String[] args) throws Exception {
        FileOutputStream fos=new FileOutputStream("D:\test.xls");

        String[] head0 = new String[]{"周期", "时间范围", "付款", "付款", "站外推广", "站外推广", "销售", "销售", "退款", "退款", "宝贝成本", "净销售", "净销售", "统计汇总", "统计汇总"};
        String[] head1 = new String[]{" ", " ", "销量", "金额", "销量", "金额", "销量", "金额", "退款金额", "退回宝贝成本", "", "销量", "金额", "宝贝利润", "利润率"};
        //对应excel中的行和列，（"开始行,结束行,开始列,结束列"）
        String[] headnum0 = new String[]{"0,1,0,0", "0,1,1,1,", "0,0,2,3", "0,0,4,5", "0,0,6,7", "0,0,8,9", "0,1,10,10", "0,0,11,12", "0,0,13,14"};
        List<String> attrList = Lists.newArrayList("reportDate", "dateRange", "saleNum", "payFee", "specialSaleNum", "specialSaleFee", "totalSaleNum", "saleFee", "refundFee",
                "refundCost", "costFee", "retaSaleNum", "retaProfitFee", "profitFee", "profitPercent");

        //组装头部Map
        Map<Integer, Map<String, String[]>> headMap = Maps.newHashMap();
        Map<String, String[]> head0InfoMap = Maps.newHashMap();
        head0InfoMap.put(ExcelUtil.HEADNAME, head0);
        head0InfoMap.put(ExcelUtil.HEADNUM, headnum0);

        Map<String, String[]> head1InfoMap = Maps.newHashMap();
        head1InfoMap.put(ExcelUtil.HEADNAME, head1);

        headMap.put(0, head0InfoMap);
        headMap.put(1, head1InfoMap);

        //数据列表
        List<ItemProfitExcelVo> itemProfitExcelVoList = Lists.newArrayList();
        int i =10;
        while (i<20){
            i++;
            ItemProfitExcelVo itemProfitExcelVo = new ItemProfitExcelVo();
            itemProfitExcelVo.setReportDate("2019-06-"+i);
            itemProfitExcelVo.setDateRange("2019-06-"+i);
            itemProfitExcelVo.setPayFee(i+"元");
            itemProfitExcelVo.setSaleNum(i+"个");
            itemProfitExcelVoList.add(itemProfitExcelVo);
        }

        Workbook workbook = ExcelUtil.exportMergeXls(sheetName, itemProfitExcelVoList, attrList, headMap, ItemProfitExcelVo.class);
        workbook.write(fos);
        fos.close();
    }

工具类ExcelUtil：

package com.verse.hades.web.stats.util;

import com.google.common.base.Predicates;
import com.verse.hades.core.constant.ResultCodeConstant;
import com.verse.hades.core.exception.BusinessException;
import com.verse.hades.excel.annotaion.ExcelNum;
import com.verse.hades.excel.annotaion.ExcelPercent;
import com.verse.hades.excel.annotaion.ExcelUnit;
import com.verse.hades.utils.LocalDateUtil;
import com.verse.hades.utils.MoneyUtil;
import com.verse.hades.utils.ReflectionHelper;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.reflections.ReflectionUtils;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.*;

public class ExcelUtil {
    public static final String HEADNAME = "headName";  //列名 key的值

    public static final String HEADNUM = "headNum";     //合并单元格下标 key的值

    /**
     * @param: sheetName
     * @param: objList 导出的数据对象列表
     * @param: attr 导出数据对象对应的字段
     * @param: headMap 头部列表名称数组与合并单元格数组的Map
     * @param: clazz 对象类
     * @Return: org.apache.poi.ss.usermodel.Workbook
     * @Decription:
     * @Modify:
     */
    public static <T> Workbook exportMergeXls(String sheetName, List<T> objList, List<String> attr, Map<Integer/*行标*/,
            Map<String/*头部列表key的名字*/, String[]/*对应的值*/>> headMap, Class<T> clazz) {
        //String[] head0, String[] headnum0, String[] head1, String[] headnum1,
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet(sheetName);

        //头部单元格样式
        CellStyle cellStyle = workbook.createCellStyle();
        setAlign(cellStyle, HorizontalAlignment.CENTER, VerticalAlignment.CENTER);
        setBorder(cellStyle, BorderStyle.THIN, IndexedColors.BLACK);
        setColor(cellStyle, IndexedColors.GREY_25_PERCENT.index, FillPatternType.SOLID_FOREGROUND);

        Font font = workbook.createFont();
        font.setFontName("Arial");
        font.setFontHeightInPoints((short) 10);//设置字体大小
        cellStyle.setFont(font);

        Set<Integer> headKeySet = headMap.keySet();
        if (headKeySet.size() > 0) {
            headKeySet.stream().sorted();
            for (Integer rowNum : headKeySet) {
                Map<String, String[]> rowInfo = headMap.get(rowNum);
                String[] headName = rowInfo.get(HEADNAME);
                String[] headNum = rowInfo.get(HEADNUM);

                if (null != headName && headName.length > 0) {
                    Row row1 = sheet.createRow(rowNum);
                    for (int i = 0; i < headName.length; i++) {
                        Cell cell = row1.createCell(i);
                        cell.setCellValue(headName[i]);
                        cell.setCellStyle(cellStyle);
                    }
                }

                if (null != headNum && headNum.length > 0) {
                    //动态合并单元格
                    for (String aHeadNum : headNum) {
                        String[] temp = aHeadNum.split(",");
                        Integer startrow = Integer.parseInt(temp[0]);
                        Integer overrow = Integer.parseInt(temp[1]);
                        Integer startcol = Integer.parseInt(temp[2]);
                        Integer overcol = Integer.parseInt(temp[3]);
                        CellRangeAddress cra = new CellRangeAddress(startrow, overrow, startcol, overcol);
                        sheet.addMergedRegion(cra);
                    }
                }
            }
        }

        return insertData(workbook, sheet, objList, clazz, attr, headKeySet.size(), attr.size());
    }

    /**
     * @param: workbook
     * @param: sheet
     * @param: objList  对象列表
     * @param: clazz 对象类
     * @param: attr  对象的字段名数组
     * @param: startRow  数据开始的行数
     * @param: colNum 总共的列数
     * @Return: org.apache.poi.ss.usermodel.Workbook
     * @Decription: 插入数据
     * @Modify:
     */
    private static <T> Workbook insertData(Workbook workbook, Sheet sheet, List<T> objList, Class<T> clazz, List<String> attr, int startRow, int colNum) {
        CellStyle cellStyle = workbook.createCellStyle();
        setAlign(cellStyle, HorizontalAlignment.CENTER, VerticalAlignment.CENTER);
        setBorder(cellStyle, BorderStyle.THIN, IndexedColors.BLACK);
        Font font = workbook.createFont();
        font.setFontName("Arial");
        font.setFontHeightInPoints((short) 10);//设置字体大小
        cellStyle.setFont(font);

        List<List<Object>> lists = getValueList(objList, clazz, attr);

        for (List<Object> list : lists) {
            Row row = sheet.createRow(startRow++);
            for (int col = 0; col < colNum; col++) {
                Cell cell = row.createCell(col);

                //设置单元格换行
                cell.setCellStyle(cellStyle);

                writeCell(workbook, list.get(col), cell);
            }
        }
        return workbook;
    }

    /**
     * @param: workbook
     * @param: obj
     * @param: cell
     * @Return: void
     * @Decription: 将数据写入单元格
     * @Modify:
     */
    private static void writeCell(Workbook workbook, Object obj, Cell cell) {
        if (obj == null) {
            cell.setCellValue("");
        } else if (obj instanceof String) {
            cell.setCellValue((String) obj);
        } else if (obj instanceof Character) {
            cell.setCellValue((Character) obj);
        } else if (obj instanceof Long) {
            cell.setCellValue((Long) obj);
        } else if (obj instanceof Integer) {
            cell.setCellValue((Integer) obj);
        } else if (obj instanceof Short) {
            cell.setCellValue((Short) obj);
        } else if (obj instanceof Byte) {
            cell.setCellValue((Byte) obj);
        } else if (obj instanceof Float) {
            cell.setCellValue((Float) obj);
        } else if (obj instanceof Double) {
            cell.setCellValue((Double) obj);
        } else if (obj instanceof Boolean) {
            cell.setCellValue((Boolean) obj);
        } else if (obj instanceof Date) {
            CellStyle cellStyle = workbook.createCellStyle();
            CreationHelper createHelper = workbook.getCreationHelper();
            cellStyle.setDataFormat(
                    createHelper.createDataFormat().getFormat("yyyy/m/d h:mm:ss"));
            cell.setCellValue((Date) obj);
            cell.setCellStyle(cellStyle);
        } else if (obj instanceof LocalDate) {
            cell.setCellValue(LocalDateUtil.getDateAsString((LocalDate) obj));
        } else if (obj instanceof LocalDateTime) {
            cell.setCellValue(LocalDateUtil.getDateTimeAsString((LocalDateTime) obj));
        }
    }


    /**
     * @param: objList
     * @param: clazz
     * @param: attr
     * @Return: java.util.List<java.util.List<java.lang.Object>>
     * @Decription: 获取对象对应的字段的值
     * @Modify:
     */
    private static <T> List<List<Object>> getValueList(List<T> objList, Class<T> clazz, List<String> attr) {
        Set<Method> methods = ReflectionUtils.getAllMethods(clazz, Predicates.and(ReflectionUtils.withModifier(Modifier.PUBLIC),
                ReflectionUtils.withPrefix("get")));
        Map<String, Object> annotationMap = getAnnotaions(clazz);

        Map<String, Method> methodMap = new HashMap<>();
        for (Method method : methods) {
            String attrName = ReflectionHelper.getAttrNameFromMethod(method.getName());
            if (attr.contains(attrName)) {
                methodMap.put(attrName, method);
            }
        }

        if (methodMap.keySet().size() != attr.size()) {
            throw new BusinessException(ResultCodeConstant.ERROR_SERVICE_CODE, "excel格式错误,列表和数据不一致,处理失败");
        }

        List<List<Object>> res = new ArrayList<>();
        for (T obj : objList) {
            List<Object> list = new ArrayList<>();
            for (String name : attr) {
                Method method = methodMap.get(name);
                list.add(wrapperValue(method, name, annotationMap.get(name), obj));
            }
            res.add(list);
        }
        return res;
    }

    private static Object wrapperValue(Method method, String name, Object annotation, Object obj) {
        String hasConvert = "";
        Object value;
        try {
            value = method.invoke(obj);
        } catch (Exception e) {
            throw new BusinessException(ResultCodeConstant.ERROR_SERVICE_CODE, "excel格式错误,不能获取对应列的数据|" + name);
        }

        if (value != null) {
            if (annotation instanceof ExcelPercent) {
                ExcelPercent excelPercent = (ExcelPercent) annotation;
                hasConvert = excelPercent.pre() + MoneyUtil.convertCentToString(getLongValue(value, name), 2) + "%";
            } else if (annotation instanceof ExcelNum) {
                ExcelNum excelNum = (ExcelNum) annotation;
                hasConvert = excelNum.pre() + MoneyUtil.convertCentToString(getLongValue(value, name), 2) + excelNum.unit();
            } else if (annotation instanceof ExcelUnit) {
                ExcelUnit excelUnit = (ExcelUnit) annotation;
                hasConvert = excelUnit.pre() + value + excelUnit.value();
            }
        }

        if (hasConvert.equals("")) {
            return value;
        } else {
            return hasConvert;
        }
    }

    private static Long getLongValue(Object value, String name) {
        Long conver2Long = 0L;
        if (value instanceof Integer) {
            conver2Long = ((Integer) value).longValue();
        } else if (value instanceof Long) {
            conver2Long = (Long) value;
        } else {
            throw new BusinessException(ResultCodeConstant.ERROR_SERVICE_CODE, "excel格式错误,当前类型无法转换" + name);
        }
        return conver2Long;
    }

    private static Map<String, Object> getAnnotaions(Class c) {
        Map<String, Object> annotationMap = new HashMap<>();
        Field[] fields = c.getDeclaredFields();
        for (Field field : fields) {
            if (field.getAnnotation(ExcelPercent.class) != null) {
                annotationMap.put(field.getName(), field.getAnnotation(ExcelPercent.class));
            } else if (field.getAnnotation(ExcelNum.class) != null) {
                annotationMap.put(field.getName(), field.getAnnotation(ExcelNum.class));
            } else if (field.getAnnotation(ExcelUnit.class) != null) {
                annotationMap.put(field.getName(), field.getAnnotation(ExcelUnit.class));
            }
        }
        return annotationMap;
    }

    private static void setAlign(CellStyle cellStyle, HorizontalAlignment halign, VerticalAlignment valign) {
        cellStyle.setAlignment(halign);
        cellStyle.setVerticalAlignment(valign);
    }

    private static void setBorder(CellStyle cellStyle, BorderStyle borderSize, IndexedColors colorIndex) {
        cellStyle.setBorderBottom(borderSize);
        cellStyle.setBottomBorderColor(colorIndex.index);
        cellStyle.setBorderLeft(borderSize);
        cellStyle.setLeftBorderColor(colorIndex.index);
        cellStyle.setBorderRight(borderSize);
        cellStyle.setRightBorderColor(colorIndex.index);
        cellStyle.setBorderTop(borderSize);
        cellStyle.setTopBorderColor(colorIndex.index);
    }

    private static void setColor(CellStyle cellStyle, short color, FillPatternType fillPattern) {
        cellStyle.setFillForegroundColor(color);
        cellStyle.setFillPattern(fillPattern);
    }
}