java excel复杂表头和复杂数据导出

package com.ruoyi.common.utils.poi;

import com.ruoyi.framework.config.RuoYiConfig;
import com.ruoyi.framework.web.domain.AjaxResult;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;
import java.util.UUID;

public class DocumentUtil<T extends TreeNode> {
    private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);

    private String sheetName;

    private Workbook wb;

    private Sheet sheet;

    private List<T> list;

    private List<Column> columns;

    private List<Column> fields;

    private int maxDepth;

    public Class<T> clazz;

    public DocumentUtil(Class<T> clazz)
    {
        this.clazz = clazz;
    }

    public void init(List<T> list, List<Column> columns, String sheetName)
    {
        if (list == null)
        {
            list = new ArrayList<T>();
        }
        this.list = list;
        this.fields = new ArrayList<>();
        this.columns = columns;
        this.sheetName = sheetName;
        this.wb = new SXSSFWorkbook(500);
    }

    public AjaxResult exportExcel(List<T> list, List<Column> columns, String sheetName)
    {
        this.init(list, columns, sheetName);
        return exportExcel();
    }

    private AjaxResult exportExcel() {
        OutputStream out = null;
        try {
            sheet = wb.createSheet(sheetName);
            generateHeader(this.columns);
            this.fields.sort((a, b) -> b.getFirstCol() - a.getFirstCol());
            generateData(this.list,maxDepth + 1);
            String filename = encodingFilename(sheetName);
            out = new FileOutputStream(getAbsoluteFile(filename));
            wb.write(out);
            return AjaxResult.success(filename);
        }
        catch (Exception ex) {
            System.out.print(ex.getMessage());
        }
        return null;
    }

    public String encodingFilename(String filename)
    {
        filename = UUID.randomUUID().toString() + "_" + filename + ".xlsx";
        return filename;
    }

    public String getAbsoluteFile(String filename)
    {
        String downloadPath = RuoYiConfig.getDownloadPath() + filename;
        File desc = new File(downloadPath);
        if (!desc.getParentFile().exists())
        {
            desc.getParentFile().mkdirs();
        }
        return downloadPath;
    }

    private void generateHeader(List<Column> columns) {
        maxDepth = getMaxDepth(this.columns, 0);
        generateHeader(columns, null, maxDepth);
    }

    private void generateHeader(List<Column> columns, Column parent, int maxDepth) {
        int colIndex = 0, rowIndex = 0;
        if (parent != null) {
            colIndex = parent.getFirstCol();
            rowIndex = parent.getLastRow() + 1;
        }
        for (Column column : columns) {
            Row row = sheet.getRow(rowIndex);
            if (row == null) {
                row = sheet.createRow(rowIndex);
            }
            column.setFirstRow(rowIndex);
            column.setLastRow(rowIndex);
            column.setFirstCol(colIndex);
            column.setLastCol(colIndex);
            if (column.getChildrens() != null && column.getChildrens().size() > 0) {
                column.setLastCol(colIndex + column.getChildrens().size() - 1);
                generateHeader(column.getChildrens(), column, maxDepth);
            } else {
                if (column.getWidth() > 0) {
                    this.sheet.setColumnWidth(colIndex, column.getWidth() * 256);
                }
                this.fields.add(column);
                column.setLastRow(maxDepth);
            }
            if (parent != null && column.getLastCol() > parent.getLastCol()) {
                parent.setLastCol(column.getLastCol());
            }
            if (column.getLastRow() > column.getFirstRow() || column.getLastCol() > column.getFirstCol()) {
                sheet.addMergedRegion(new CellRangeAddress(column.getFirstRow(), column.getLastRow(), column.getFirstCol(), column.getLastCol()));
            }
            Cell cell = row.createCell(column.getFirstCol());
            cell.setCellValue(column.getName());
            colIndex = column.getLastCol() + 1;
        }
    }
    
    private int generateData(List<T> list, int firstRow) {
        int lastRow = firstRow;
        int totalRow = 0;
        for (T item : list) {
            Row row = sheet.getRow(lastRow);
            if (row == null) {
                row = sheet.createRow(lastRow);
            }
            if (item.getChildrens() != null && item.getChildrens().size() > 0) {
                int total = generateData(item.getChildrens(), lastRow);
                lastRow += total - 1;
                totalRow += total;
            } else {
                totalRow++;
            }
            Class cls = item.getClass();
            for (Field field : cls.getDeclaredFields()) {
                Column column = this.fields.stream().filter(p -> p.getField().equals(field.getName())).findFirst().get();
                try {
                    field.setAccessible(true);
                    Object obj = field.get(item);
                    String value = obj == null ? "" : obj.toString();
                    Cell cell = row.createCell(column.getFirstCol());
                    cell.setCellValue(value);
                    if (lastRow > firstRow) {
                        sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, column.getFirstCol(), column.getLastCol()));
                    }
                } catch (Exception ex) {
                    System.out.print(ex.getMessage());
                }
            }
            lastRow++;
            firstRow = lastRow;
        }
        return totalRow;
    }

    private <K, T extends TreeNode<K>> int getMaxDepth(List<T> list, int depth) {
        int maxDepth = 0;
        for (TreeNode item : list)
        {
            item.setDepth(depth);
            if (item.getChildrens() != null)
            {
                int res = getMaxDepth(item.getChildrens(), depth + 1);
                if (res > maxDepth) maxDepth = res;
            }
            if (depth > maxDepth) maxDepth = depth;
        }
        return maxDepth;
    }

}

package com.ruoyi.common.utils.poi;

import java.util.List;

public abstract class TreeNode<T> {

    private int depth;

    private List<T> childrens;

    public int getDepth() {
        return depth;
    }

    public void setDepth(int depth) {
        this.depth = depth;
    }

    public List<T> getChildrens() {
        return childrens;
    }

    public void setChildrens(List<T> childrens) {
        this.childrens = childrens;
    }
}

package com.ruoyi.common.utils.poi;

import java.util.List;

public class Column extends TreeNode<Column> {

    public Column(String name, String field) {
        this.name = name;
        this.field = field;
    }

    public Column(String name, List<Column> childrens) {
        this.name = name;
        this.setChildrens(childrens);
    }

    private String field;

    private String name;

    private int width;

    private int firstRow;

    private int lastRow;

    private int firstCol;

    private int lastCol;

    public String getField() {
        return field;
    }

    public void setField(String field) {
        this.field = field;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getWidth() {
        return width;
    }

    public void setWidth(int width) {
        this.width = width;
    }

    public int getFirstRow() {
        return firstRow;
    }

    public void setFirstRow(int firstRow) {
        this.firstRow = firstRow;
    }

    public int getLastRow() {
        return lastRow;
    }

    public void setLastRow(int lastRow) {
        this.lastRow = lastRow;
    }

    public int getFirstCol() {
        return firstCol;
    }

    public void setFirstCol(int firstCol) {
        this.firstCol = firstCol;
    }

    public int getLastCol() {
        return lastCol;
    }

    public void setLastCol(int lastCol) {
        this.lastCol = lastCol;
    }
}