LayUI多级表头导出

用layUI方法渲染的数据表格导出前后端封装，业务变动增删改字段时，只需改动前端表格的cols参数即可，方便实用。

前端代码：

//每列属性上增加id,和parentId两个属性，用来组织表头的层级关系，方便java端导出封装
var cols = [
    [
        {title:'地区',align:'center',field:'latnName',rowspan:2,id:'1'},
        {title:'张三编码',align:'center',field:'ZSCode',rowspan:2,id:'2'},
        {title:'李四编码',align:'center',field:'LSCode',rowspan:2,id:'3'},
        {title:'张三收入',align:'center',field:'',colspan:2,id:'4'},
        {title:'李四收入',align:'center',field:'',colspan:2,id:'5'},
    ],
    [
        {title:'大儿子',align:'center',field:'bigSon',id:'6',parentId:'4'},
        {title:'小儿子',align:'center',field:'samllSon',id: '7',parentId:'4'},
        {title:'大儿子',align:'center',field:'bigSon_',id:'8',parentId:'5'},
        {title:'小儿子',align:'center',field:'samllSon_',id:'9',parentId:'5'},
    ]
]

//渲染表格
table.render({
    elem: '#table', 
    url: “”, //接口地址
    method: 'post',
    page: true,
    cols: cols,
});


/**
* 前端公共方法，查询参数较多，post 方式导出
*
* queryParam 查询参数
* url 接口地址
* titleList 表格的cols参数
*/
function exportExcel(queryParam, url, titleList){
    var form = document.createElement("form");
    form.style.display = "none";
    form.action = url;
    form.method = "post";
    document.body.appendChild(form);
    for (var key in queryParam) {
        var input = document.createElement("input");
        input.type = "hidden";
        input.name = key;
        input.value = queryParam[key];
        form.appendChild(input);
    }
    var titleListInput = document.createElement("input");
    titleListInput.type = "hidden";
    titleListInput.name = "titleList";
    titleListInput.value = JSON.stringify(titleList);
    form.appendChild(titleListInput);
    form.submit();
    form.remove();
}

//调用
exportExcel({}, "", cols)

后端代码实体类

import java.util.ArrayList;
import java.util.List;

public class Column {

    public String id; //当前列的id,根节点指定为0

    public String parentId; //当前列的父id, 一级表头父id指定为0

    public String enFieldName; //字段名称（英文）

    public String cnFieldName; //字段名称（中文）

    public boolean isHasChilren; //是否有子节点

    public int totalRow; //总行数

    public int totalCol; //总列数

    public int rowNum; //第几行

    public int colNum; //第几列

    public int rowSpan; //跨多少行

    public int colSpan; //跨多少列

    public List<Column> chilrenColumn = new ArrayList<>(); //子节点集合

    public Column(){};

    public Column(String id, String parentId, String enFieldName, String cnFieldName) {
        this.id = id;
        this.parentId = parentId;
        this.enFieldName = enFieldName;
        this.cnFieldName = cnFieldName;
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getParentId() {
        return parentId;
    }

    public void setParentId(String parentId) {
        this.parentId = parentId;
    }

    public String getEnFieldName() {
        return enFieldName;
    }

    public void setEnFieldName(String enFieldName) {
        this.enFieldName = enFieldName;
    }

    public String getCnFieldName() {
        return cnFieldName;
    }

    public void setCnFieldName(String cnFieldName) {
        this.cnFieldName = cnFieldName;
    }

    public boolean isHasChilren() {
        return isHasChilren;
    }

    public void setHasChilren(boolean hasChilren) {
        isHasChilren = hasChilren;
    }

    public int getTotalRow() {
        return totalRow;
    }

    public void setTotalRow(int totalRow) {
        this.totalRow = totalRow;
    }

    public int getTotalCol() {
        return totalCol;
    }

    public void setTotalCol(int totalCol) {
        this.totalCol = totalCol;
    }

    public int getRowNum() {
        return rowNum;
    }

    public void setRowNum(int rowNum) {
        this.rowNum = rowNum;
    }

    public int getColNum() {
        return colNum;
    }

    public void setColNum(int colNum) {
        this.colNum = colNum;
    }

    public int getRowSpan() {
        return rowSpan;
    }

    public void setRowSpan(int rowSpan) {
        this.rowSpan = rowSpan;
    }

    public int getColSpan() {
        return colSpan;
    }

    public void setColSpan(int colSpan) {
        this.colSpan = colSpan;
    }

    public List<Column> getChilrenColumn() {
        return chilrenColumn;
    }

    public void setChilrenColumn(List<Column> chilrenColumn) {
        this.chilrenColumn = chilrenColumn;
    }
}

构建树结构工具类

import java.util.ArrayList;
import java.util.Collections;
import java.util.Iterator;
import java.util.List;

public class TreeTool {

    /**
     * 获取树的深度
     *
     * @param list
     * @return
     */
    public static int getMaxStep(List<Column> list) {
        List<Integer> nums = new ArrayList<>();
        for (Column column : list) {
            nums.add(getNodeStep(list, column.getId(), 0));
        }
        return Collections.max(nums);
    }

    /**
     * 获取节点的深度
     *
     * @param list
     * @param id   根节点
     * @param step
     * @return
     */
    public static int getNodeStep(List<Column> list, String id, int step) {
        if ("".equals(id) || id == null) {
            return step;
        }
        for (Column column : list) {
            if (id.equals(column.getId())) {
                return getNodeStep(list, column.getParentId(), step + 1);
            }
        }
        return step;
    }

    /**
     * 获取所有叶子节点个数
     *
     * @param list
     * @param rootId
     * @return
     */
    public static int getLeafNode(List<Column> list, String rootId) {
        int sum = 0;
        for (Column column : list) {
            if (rootId.equals(column.getParentId())) {
                sum++;
                if (hasChild(list, column)) {
                    sum += getLeafNode(list, column.getId()) - 1;
                }
            }
        }
        return sum;
    }

    /**
     * 获取父节点
     *
     * @param list
     * @param parentId
     * @return
     */
    public static Column getParentNode(List<Column> list, String parentId) {
        for (Column column : list) {
            if (parentId != null && parentId.equals(column.getId())) {
                return column;
            }
            if (parentId == null && null == column.getId()) {
                return column;
            }
        }
        return new Column() {{
            setColNum(0);
            setRowNum(0);
        }};
    }

    /**
     * 获取兄弟节点个数
     *
     * @param list
     * @param column
     * @return
     */
    public static int getBrotherNodeNum(List<Column> list, Column column) {
        int sum = 0;
        for (Column cc : list) {
            if (column.getId().equals(cc.getId())) break;
            if (!column.getParentId().equals(cc.getParentId())) continue;
            int temp = getLeafNode(list, cc.getId());
            if (temp == 0 || temp == 1)
                sum++;
            else
                sum += temp;
        }
        return sum;
    }

    /**
     * 判断是否有子节点
     *
     * @param list 遍历的数据
     * @param node 某个节点
     * @return
     */
    public static boolean hasChild(List<Column> list, Column node) {
        return getChildList(list, node).size() > 0;
    }

    /**
     * 获取子节点列表
     *
     * @param list 遍历的数据
     * @param node 某个节点
     * @return
     */
    public static List<Column> getChildList(List<Column> list, Column node) {
        List<Column> nodeList = new ArrayList<>();
        Iterator<Column> it = list.iterator();
        while (it.hasNext()) {
            Column n = (Column) it.next();
            if (n.getParentId() != null && n.getParentId().equals(node.getId())) {
                nodeList.add(n);
            }
        }
        return nodeList;
    }

    /**
     * 建树
     *
     * @param list
     * @return
     */
    public static List<Column> buildTree(List<Column> list, String rootId) {
        List<Column> treeList = new ArrayList<>();
        for (Column column : list) {
            if (rootId != null && rootId.equals(column.getParentId())) {
                treeList.add(findChildren(column, list));
            }
        }
        return treeList;
    }

    /**
     * 查找子节点
     *
     * @param treeNodes
     * @return
     */
    public static Column findChildren(Column treeNode, List<Column> treeNodes) {
        for (Column it : treeNodes) {
            if (treeNode.getId().equals(it.getParentId())) {
                if (treeNode.getChilrenColumn() == null) {
                    treeNode.setChilrenColumn(new ArrayList<>());
                }
                treeNode.getChilrenColumn().add(findChildren(it, treeNodes));
            }
        }
        return treeNode;
    }
}

导出工具类

import com.spire.ms.System.Exception;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.commons.collections4.MapUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelTool {
    private final static Logger logger = LoggerFactory.getLogger(ExcelTool.class);

    private static XSSFWorkbook workbook; //excel对象

    private static XSSFCellStyle cellStyle; //单元格样式

    private static int rowHeight = 20; //行高

    private static int colWidth = 20; //列宽

    private static int sheetDataCount = 65535; //每个sheet页最多记录数

    private static String excelName; //导出的excel文件名

    private static String TITLE_LIST_KEY = "titleList";

    private static String ROOT_ID = "0";

    private static String ID = "id";

    private static String PARENT_ID = "parentId";

    private static String FIELD = "field";

    private static final String TITLE = "title";


    /**
     * 结合layUi封装导出excel公共方法,支持一级表头和多级表头导出
     *
     * @param request
     * @param response
     * @param excelName
     * @param paramMap
     * @param data
     * @throws IllegalAccessException
     */
    public static <T> void exportExcel(HttpServletRequest request, HttpServletResponse response, String excelName, Map<String, Object> paramMap, List<T> data) throws IllegalAccessException {
        if (data == null) {
            throw new RuntimeException("原始数据不能为null");
        }
        Object obj = MapUtils.getObject(paramMap, TITLE_LIST_KEY);
        if (obj == null) {
            throw new RuntimeException("表头数据不能为null");
        }
        List<Column> columnList = getColumnList(JSONArray.fromObject(obj));
        List<Column> treeList = TreeTool.buildTree(columnList, ROOT_ID);
        initWorkbook(excelName);
        setRowSpan(columnList);
        setColSpan(columnList, treeList);
        writeData(data, treeList);
        writeFile(request, response);
    }

    /**
     * 将layui表格的cols转换成List<Column>
     *
     * @param jsonArray
     * @return
     */
    private static List<Column> getColumnList(JSONArray jsonArray) {
        if (jsonArray.size() == 0) {
            throw new RuntimeException("没有表头数据，表头数据不能为空");
        }
        if (jsonArray.size() == 1) {
            JSONArray arr = jsonArray.getJSONArray(0);
            for (int i = 0; i < arr.size(); i++) {
                if (!arr.getJSONObject(i).containsKey(ID)) arr.getJSONObject(i).put(ID, i + 1);
            }
        }
        List<Column> columnList = new ArrayList<>();
        for (int i = 0; i < jsonArray.size(); i++) {
            addCloumn(jsonArray.getJSONArray(i), columnList);
        }
        return columnList;
    }

    /**
     * 添加title
     *
     * @param jsonArray
     * @param columnList
     */
    private static void addCloumn(JSONArray jsonArray, List<Column> columnList) {
        for (int i = 0; i < jsonArray.size(); i++) {
            JSONObject jo = jsonArray.getJSONObject(i);
            String id = null, parentId = null, enFieldName = null, cnFieldName = null;
            if (jo.containsKey(ID)) {
                id = jo.getString(ID);
            } else {
                throw new RuntimeException("表头ID参数为必传");
            }
            if (jo.containsKey(PARENT_ID)) {
                parentId = jo.getString(PARENT_ID);
            } else {
                parentId = ROOT_ID;
            }
            if (jo.containsKey(FIELD)) {
                enFieldName = jo.getString(FIELD);
            } else {
                throw new RuntimeException("表头field参数为必传");
            }
            if (jo.containsKey(TITLE)) {
                cnFieldName = jo.getString(TITLE);
            } else {
                throw new RuntimeException("表头title参数为必传");
            }
            columnList.add(new Column(id, parentId, enFieldName, cnFieldName));
        }
    }

    /**
     * 设置excel单元格跨多少行
     *
     * @param columnlist
     */
    private static void setRowSpan(List<Column> columnlist) {
        int rowSpan = 0, totalRow = TreeTool.getMaxStep(columnlist), totalCol = TreeTool.getLeafNode(columnlist, ROOT_ID);
        for (Column column : columnlist) {
            int nodeStep = TreeTool.getNodeStep(columnlist, column.getParentId(), 0);
            column.setTotalRow(totalRow);
            column.setTotalCol(totalCol);
            column.setRowNum(nodeStep);
            column.setHasChilren(TreeTool.hasChild(columnlist, column));
            if (column.isHasChilren()) {
                column.setRowSpan(0);
            } else {
                if (nodeStep < totalRow) {
                    rowSpan = totalRow - nodeStep;
                }
                column.setRowSpan(rowSpan);
            }
        }
    }

    /**
     * 设置excel单元格跨多少列
     *
     * @param list
     * @param treeList
     */
    private static void setColSpan(List<Column> list, List<Column> treeList) {
        List<Column> newList = new ArrayList<>();
        for (Column column : treeList) {
            int colNum = TreeTool.getParentNode(list, column.getParentId()).getColNum(),
                    brotherColNum = TreeTool.getBrotherNodeNum(list, column),
                    colSpan = TreeTool.getLeafNode(list, column.getId());
            if (colSpan <= 1) {
                colSpan = 0;
            }
            ;
            column.setColNum(colNum + brotherColNum);
            column.setColSpan(colSpan);
            if (column.getChilrenColumn().size() > 0) {
                newList.addAll(column.getChilrenColumn());
            }
        }
        if (newList.size() > 0) {
            setColSpan(list, newList);
        }
    }

    /**
     * 将数据分页写入sheet,每个sheet不能超过sheetDataCount的值
     *
     * @param data
     * @param columns
     * @throws IllegalAccessException
     */
    private static <T> void writeData(List<T> data, List<Column> columns) throws IllegalAccessException {
        int dataTotalCount = data.size(), sheetCount = dataTotalCount / sheetDataCount;
        for (int i = 1; i <= sheetCount; i++) {
            writeSheet(workbook.createSheet(excelName + i), data.subList((i - 1) * sheetDataCount, i * sheetDataCount), columns);
        }
        writeSheet(workbook.createSheet(excelName + (sheetCount + 1)), data.subList(sheetCount * sheetDataCount, dataTotalCount), columns);
    }

    /**
     * 将数据写入sheet
     *
     * @param sheet
     * @param data
     * @param columns
     * @throws IllegalAccessException
     */
    private static <T> void writeSheet(XSSFSheet sheet, List<T> data, List<Column> columns) throws IllegalAccessException {
        int totaLRow = columns.get(0).getTotalRow(), totalCol = columns.get(0).getTotalCol();
        for (int i = 0; i < totaLRow; i++) {
            XSSFRow row = sheet.createRow(i);
            for (int j = 0; j < totalCol; j++) {
                row.createCell(j);
            }
        }
        createExcelHead(columns, sheet, 0);
        setSheetValue(columns, data, sheet, totaLRow + 1);
    }

    /**
     * 递归写入表头数据 支持单级，多级表头的创建
     *
     * @param columns  表头数据
     * @param sheet    sheet页
     * @param rowIndex 当前Excel的第几行
     */
    private static void createExcelHead(List<Column> columns, XSSFSheet sheet, int rowIndex) {
        XSSFRow row = sheet.getRow(rowIndex);
        for (int i = 0; i < columns.size(); i++) {
            Column column = columns.get(i);
            int rowNum = column.getRowNum(), colNum = column.getColNum();
            int rowSpan = column.getRowSpan(), colSpan = column.getColSpan();
            int endRow = rowNum + rowSpan, endCol = colNum + colSpan;
            if (endCol > colNum) endCol--;
            XSSFCell cell = row.getCell(colNum);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(new XSSFRichTextString(column.getCnFieldName()));
            sheet.setDefaultColumnWidth(colWidth);
            sheet.setDefaultRowHeightInPoints(rowHeight);
            sheet.setColumnWidth(i, Math.max(15 * 256, Math.min(255 * 256, sheet.getColumnWidth(i) * 12 / 10)));
            sheet.addMergedRegion(new CellRangeAddress(rowNum, endRow, colNum, endCol));
            if (column.isHasChilren()) {
                rowIndex = rowNum + 1;
                createExcelHead(column.getChilrenColumn(), sheet, rowIndex);
            }
        }
    }

    /**
     * 把数据写入到单元格
     *
     * @param columns
     * @param data
     * @param sheet
     * @param rowIndex
     * @throws IllegalAccessException
     */
    private static <T> void setSheetValue(List<Column> columns, List<T> data, XSSFSheet sheet, int rowIndex) throws IllegalAccessException {
        List<Column> newList = new ArrayList<>();
        filterColumn(columns, newList);
        for (int i = 0, index = rowIndex; i < data.size(); i++, index++) {
            XSSFRow row = sheet.createRow(index);
            for (int j = 0; j < newList.size(); j++) {
                setCellValue(row, newList.get(j), data.get(i));
            }
        }
    }

    /**
     * 数据填入单元格
     *
     * @param row
     * @param column
     * @param obj
     * @throws IllegalAccessException
     */
    private static void setCellValue(XSSFRow row, Column column, Object obj) throws IllegalAccessException {
        XSSFCell cell = row.createCell(column.getColNum());
        cell.setCellStyle(cellStyle);
        Object value = null;
        if (obj instanceof Map) {
            Map<String, Object> map = (Map<String, Object>) obj;
            for (Map.Entry entry : map.entrySet()) {
                if (column.getEnFieldName().equals(entry.getKey()) && !column.isHasChilren()) {
                    value = entry.getValue();
                }
            }
        } else {
            for (Field field : obj.getClass().getDeclaredFields()) {
                field.setAccessible(true);
                if (column.getEnFieldName().equals(field.getName()) && !column.isHasChilren()) value = field.get(obj);
                if (value instanceof Date) value = parseDate((Date) value);
            }
        }
        if (value != null) {
            cell.setCellValue(new XSSFRichTextString(value.toString()));
        }
    }

    /**
     * 写入excel文件到OutputStream,给页面下载
     *
     * @param request
     * @param response
     */
    private static void writeFile(HttpServletRequest request, HttpServletResponse response) {
        setUserAgent(request, response);
        OutputStream out = null;
        try {
            out = response.getOutputStream();
            workbook.write(out);
        } catch (IOException e) {
            logger.error("写入流报错：{}", e.getMessage(), e);
        } finally {
            try {
                if (out != null) {
                    out.flush();
                    out.close();
                }
            } catch (IOException e) {
                logger.error("关闭流出错：{}", e.getMessage(), e);
            }
        }
    }

    /**
     * 设置用户浏览器兼容
     *
     * @param response
     */
    private static void setUserAgent(HttpServletRequest request, HttpServletResponse response) {
        response.reset();
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        try {
            String userAgent = request.getHeader("user-agent").toLowerCase(), filename = "";
            if (userAgent.contains("msie") || userAgent.contains("edge") || (userAgent.contains("gecko") && userAgent.indexOf("rv:11") > 0)) {
                filename = URLEncoder.encode(excelName, String.valueOf(StandardCharsets.UTF_8));
            } else {
                filename = new String(excelName.getBytes(), StandardCharsets.ISO_8859_1);
            }
            response.setHeader("Content-disposition", "attachment; filename=" + filename + ".xlsx");
        } catch (UnsupportedEncodingException e) {
            logger.error("设置浏览器兼容出错：{}", e.getMessage(), e);
        }
    }

    /**
     * 过滤表头的脏数据
     *
     * @param oldList
     * @param newList
     */
    private static void filterColumn(List<Column> oldList, List<Column> newList) {
        for (Column column : oldList) {
            if (column.getEnFieldName() != null) {
                newList.add(column);
            }
            List<Column> chilrenColumn = column.getChilrenColumn();
            if (chilrenColumn.size() > 0) {
                filterColumn(chilrenColumn, newList);
            }
        }
    }

    /**
     * 格式化日期
     *
     * @param date
     * @return String
     */
    private static String parseDate(Date date) {
        String dateStr = "";
        try {
            dateStr = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);
        } catch (Exception e) {
            logger.error("日期转换出错：{}", e.getMessage(), e);
        }
        return dateStr;
    }

    /**
     * 初始化Workbook
     *
     * @param excelName
     */
    private static void initWorkbook(String excelName) {
        ExcelTool.excelName = excelName;
        ExcelTool.workbook = new XSSFWorkbook();
        ExcelTool.cellStyle = ExcelTool.workbook.createCellStyle();
        ExcelTool.cellStyle.setAlignment(HorizontalAlignment.CENTER);
        ExcelTool.cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    }
}

后端调用： 

//后端调用
@PostMapping("/")
@ResponseBody
public void exportReportTable(@RequestParam Map<String, Object> paramMap,         
                        HttpServletRequest request, HttpServletResponse response) {
    List<Map<String, Object>> list = service.qryData(paramMap);
    ExcelTool.exportExcel(request, response, "导出excel", paramMap, list);
}

应用实例截图：

导出结果：

  

总结：适用layUi方法级渲染的多级表头的数据表格，其核心就是cols这个数组，用id和parentId这个两个属性组织好多级表头关系，将cols传给后端，因业务需求变动，后期增删改字段，都无需修改后端代码。

借鉴代码：JAVA POI 实现EXCEL 动态表头、动态添加数据（导入导出）、 Tree结构的遍历_嘻哈嘻哈-CSDN博客_poi动态表头