java动态导出Excel多层级表头

	//导出
	public void exportExcel(HttpServletResponse response, List<String> ids) {
        ServletOutputStream out = null;
        try {
            String fileName = "模板";
            //第一步，创建一个workbook，对应一个Excel文件
            XSSFWorkbook wb = new XSSFWorkbook();
            // 一个sheet
            XSSFSheet sheet = wb.createSheet(fileName);
            // 初始化样式
            // this.setStyle(wb);
            // 重点
            this.setHeader(sheet, fileName);

            List<?> list = this.salaryMapper.getByIds(ids);
            JSONObject salary;
            XSSFCell cell;
            XSSFRow row;
            for (int i = 0; i < list.size(); i++) {
                row = sheet.createRow(i + 5);
                salary = JSONObject.parseObject(JSON.toJSONString(list.get(i)));
                for (Map.Entry<Integer, String> entry : exportFieldMap.entrySet()) {
                    cell = row.createCell(entry.getKey());
                    cell.setCellStyle(null);
                    this.setExcelValue(cell, salary, entry.getValue());
                }
                index++;
            }

            fileName = URLEncoder.encode(fileName + ".xlsx", StandardCharsets.UTF_8.name());
            response.setContentType("application/msword;charset=utf-8 ");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            out = response.getOutputStream();
            wb.write(out);
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

	private void setHeader(XSSFSheet sheet, String fileName) {
		//这里是tree结构的可以使用ToTreeUtil
        List<DynamicList> list = this.dynamicListService.headers("SALARY_TYPE", "SALARY");

        // 第一行 标题（文件名）
        this.setCellValue(sheet, fileName, 0, 0);
        // 第二行 字段名（隐藏）
        fieldRow = sheet.createRow(1);
        exportFieldMap = new HashMap<>();
        // 第三行 标题组
        int maxLevel = ToTreeUtil.getMaxLevel(list);
        int lastCol = this.setCellValue(sheet, list, 2, 0, ++maxLevel);
        this.setCellStyle(sheet, 2, ++maxLevel, lastCol + 1);

        //标题单元格合并
        sheet.addMergedRegion(new CellRangeAddress(0, 0, (short) 0, (short) lastCol));
    }

	private void setExcelValue(XSSFCell cell, JSONObject salary, String fieldName) {
        if (fieldName.contains("Date")) {
            try {
                cell.setCellValue(YYYY_MM.format(salary.getDate(fieldName)));
            } catch (Exception e) {
                System.out.println(e.getMessage());
            }
        } else {
            String value = salary.get(fieldName) + "";
            if (StringUtils.isNotEmpty(value) && !"null".equals(value)) {
                cell.setCellValue(value);
            }
        }
        cell.setCellType(CellType.STRING);
    }

	private void setCellValue(XSSFSheet sheet, String val, int rowNum, int colNum) {
        XSSFCell cell = sheet.createRow(rowNum).createCell(colNum);
        cell.setCellStyle(null);
        cell.setCellValue(val);
    }
    
	private int setCellValue(XSSFSheet sheet, List<DynamicList> list, int rowNum, int colNum, int lastRow) {
        if (CollUtil.isEmpty(list)) return 0;
        XSSFRow row = sheet.getRow(rowNum);
        if (row == null) {
            row = sheet.createRow(rowNum);
        }
        int colIndex = 0;
        int columnIndex;
        XSSFCell cell = null;
        DynamicList dynamicItem;
        List<DynamicList> childes;
        for (int i = colNum; i < list.size() + colNum; i++) {
            dynamicItem = list.get(i - colNum);
            columnIndex = i + colIndex;
            this.setFieldValue(dynamicItem, columnIndex);
            row.createCell(columnIndex).setCellValue(dynamicItem.getColumnName());
            childes = dynamicItem.getChildes();
            colIndex += this.setCellValue(sheet, childes, rowNum + 1, columnIndex, lastRow);

            sheet.setColumnWidth(columnIndex, 12 * 256);
            if (CollUtil.isEmpty(childes) && rowNum < lastRow) {
                sheet.addMergedRegion(new CellRangeAddress(rowNum, lastRow, columnIndex, columnIndex));
            }
            if (columnIndex < i + colIndex) {
                sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, columnIndex, i + colIndex));
            }
        }
        return list.size() + colIndex - 1;
    }
    
    private static XSSFRow fieldRow;
    
    private static Map<Integer, String> exportFieldMap;
    
	//用于导入映射字段
    private void setFieldValue(DynamicList dynamicItem, int columnIndex) {
        String fieldName = dynamicItem.getFieldName();
        if (StringUtils.isNotEmpty(fieldName)) {
            XSSFCell cell = fieldRow.createCell(columnIndex);
            cell.setCellStyle(null);
            cell.setCellValue(fieldName);
            exportFieldMap.putIfAbsent(columnIndex, fieldName);
        }
    }

	//导入
	public List<SalarySave> importExcel(MultipartFile file) {
        XSSFSheet sheet = null;
        try {
            sheet = new XSSFWorkbook(file.getInputStream()).getSheetAt(0);
        } catch (IOException e) {
            e.printStackTrace();
        }
        XSSFRow row;
        JSONObject obj;
        List<?> list = new LinkedList<>();
        Map<Integer, String> fieldMap = this.getFieldMap(sheet.getRow(1));
        for (int i = 5; i <= sheet.getLastRowNum(); i++) {
            obj = new JSONObject();
            row = sheet.getRow(i);
            for (Map.Entry<Integer, String> entry : fieldMap.entrySet()) {
                try {
                    this.setObjValue(obj, row, entry.getValue(), entry.getKey());
                } catch (Exception e) {
                    System.out.println(e.getMessage());
                }
            }
            list.add(obj.toJavaObject(Salary.class));
        }
        this.saveOrUpdateBatch(list);
        return null;
    }
    
	private Map<Integer, String> getFieldMap(XSSFRow row) {
        String cellNum = String.valueOf(row.getLastCellNum());
        Map<Integer, String> fieldMap = new HashMap<>();
        for (int i = 0; i < Integer.parseInt(cellNum); i++) {
            fieldMap.put(i, this.getCellValue(row.getCell(i)));
        }
        return fieldMap;
    }

	private void setObjValue(JSONObject obj, XSSFRow row, String fieldName, Integer key) {
        String value = this.getCellValue(row.getCell(key));
        obj.putIfAbsent(fieldName, value);
    }

	private String getCellValue(XSSFCell cell) {
        try {
            cell.setCellType(CellType.STRING);
            return cell.getStringCellValue();
        } catch (Exception e) {
            return null;
        }
    }

public class ToTreeUtil {

    /**
     * parseTree(list, "childes", "dictId", "dictParentId", "")
     *
     * @param list
     * @param childes       子集容器
     * @param idField       主键ID字段
     * @param parentIdField 父级ID字段
     * @param parentId      父级ID
     * @return
     */
    private static JSONArray parseTree(List<?> list, String childes, String idField, String parentIdField, Object parentId) {
        JSONArray obj = new JSONArray();
        for (Object object : list) {
            JSONObject tempObj = JSONObject.parseObject(JSON.toJSONString(object));
            Object uuid = tempObj.get(idField);
            Object pid = tempObj.get(parentIdField);
            if (parentId.toString().equals(pid.toString())) {
                JSONArray trees = parseTree(list, childes, idField, parentIdField, uuid);
                tempObj.put(childes, trees);
                obj.add(tempObj);
            }
        }
        return obj;
    }

    private static final String CHILDES = "childes";

    private static final String ID_FIELD = "id";

    private static final String PARENT_ID_FIELD = "parentId";

    private static final String DEF_PARENT = "";


    public static <T> List<T> parseTree(List<T> list, String childes, String idField, String parentIdField, Object parentId, Class clazz) {
        JSONArray parseTree = parseTree(list, childes, idField, parentIdField, parentId);
        return JSONObject.parseArray(JSONArray.toJSONString(parseTree), clazz);
    }

    public static <T> List<T> parseTree(List<T> list, String childes, String idField, String parentIdField, Class clazz) {
        JSONArray parseTree = parseTree(list, childes, idField, parentIdField, DEF_PARENT);
        return JSONObject.parseArray(JSONArray.toJSONString(parseTree), clazz);
    }

    public static <T> List<T> parseTree(List<T> list, String idField, String parentIdField, Class clazz) {
        JSONArray parseTree = parseTree(list, CHILDES, idField, parentIdField, DEF_PARENT);
        return JSONObject.parseArray(JSONArray.toJSONString(parseTree), clazz);
    }

    public static <T> List<T> parseTree(List<T> list, String childes, Object parentId, Class clazz) {
        JSONArray parseTree = parseTree(list, childes, ID_FIELD, PARENT_ID_FIELD, parentId);
        return JSONObject.parseArray(JSONArray.toJSONString(parseTree), clazz);
    }

    public static <T> List<T> parseTree(List<T> list, Object parentId, Class clazz) {
        JSONArray parseTree = parseTree(list, CHILDES, ID_FIELD, PARENT_ID_FIELD, parentId);
        return JSONObject.parseArray(JSONArray.toJSONString(parseTree), clazz);
    }

    public static <T> List<T> parseTree(List<T> list, Class clazz) {
        JSONArray parseTree = parseTree(list, CHILDES, ID_FIELD, PARENT_ID_FIELD, DEF_PARENT);
        return JSONObject.parseArray(JSONArray.toJSONString(parseTree), clazz);
    }

    private static int getMaxLevel(List<?> list, String childes, int level) {
        int max_level = 0;
        if (CollUtil.isNotEmpty(list)) {
            max_level = level;
            for (Object object : list) {
                JSONObject tempObj = JSONObject.parseObject(JSON.toJSONString(object));
                Object obj = tempObj.get(childes);
                max_level = Math.max(getMaxLevel((List<?>) obj, childes, level + 1), max_level);
            }
        }
        return max_level;
    }

    public static int getMaxLevel(List<?> list, String childes) {
        return getMaxLevel(list, childes, 1);
    }

    public static int getMaxLevel(List<?> list) {
        return getMaxLevel(list, CHILDES, 1);
    }

}

public class DynamicList implements Serializable {

    @TableId(value = "id", type = IdType.ASSIGN_UUID)
    private String id;
    /**
     * 父类ID
     */
    @TableField(value = "parent_id")
    private String parentId;
    /**
     * 类型
     */
    @TableField(value = "type")
    private String type;
    /**
     * 列名
     */
    @TableField(value = "column_name")
    private String columnName;
    /**
     * 字段
     */
    @TableField(value = "field_name")
    private String fieldName;

    @TableField(exist = false)
    private List<DynamicList> childes;
}