POI导出EXCEL合并单元格对象嵌套List数据

导出EXCEL

在实际的开发过程当中，我们会遇到一些比较复杂的导出需求，例如需要导出的实体类中需要嵌套集合对象等，正好最近碰到了所以分享出来，希望对大家有帮助

---

一、POI是什么

简单的说就是Apache软件基金会提供的一个库，这个库里有API，API赋予了Java程序更强大的读和写的能力

二、使用步骤

这里是结果

1、POM.XML

    <dependencies>
        <dependency>
            <groupId>com.monitorjbl</groupId>
            <artifactId>xlsx-streamer</artifactId>
            <version>2.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.16.10</version>
        </dependency>
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.6.0</version>
        </dependency>
    </dependencies>

2、常量

    public static final Integer ZERO = 0;
    public static final Integer ONE = 1;
    public static final Integer UNQUALIFIED_INFO_INDEX = 24;
    public static final String[] TITLE_LIST = new String[]{
   
            "汉皇重色思倾国", "御宇多年求不得", "杨家有女初长成", "养在深闺人未识", "天生丽质难自弃", "一朝选在君王侧",
            "回眸一笑百媚生", "六宫粉黛无颜色", "春寒赐浴华清池", "温泉水滑洗凝脂", "侍儿扶起娇无力", "始是新承恩泽时",
            "云鬓花颜金步摇", "芙蓉帐暖度春宵", "春宵苦短日高起", "从此君王不早朝", "承欢侍宴无闲暇", "春从春游夜专夜",
            "后宫佳丽三千人", "三千宠爱在一身", "金屋妆成娇侍夜", "玉楼宴罢醉和春", "姊妹弟兄皆列土", "可怜光彩生门户",
            "花冠不整下堂来", "风吹仙袂飘飖举", "犹似霓裳羽衣舞", "在天愿作比翼鸟", "在地愿为连理枝", "天长地久有时尽",
            "此恨绵绵无绝期", "九重城阙烟尘生", "千乘万骑西南行", "西出都门百余里"};

该处为标题

3、实体类

package cn.itsaxon.vo;

import lombok.*;

import java.io.Serializable;

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@Getter
@Setter
public class ExportVO implements Serializable {
   
    private String orderId = "";
    private String customerProvince = "";
    private String customerArea = "";
    private String dealerName = "";
    private String customerName = "";
    private String customerTel = "";
    private String customerIdCard = "";
    private String inverterList = "";
    private String inverterNum = "";
    private String cooperationName = "";
    private String customerAddress = "";
    private String roofType = "";
    private String singlePower = "";
    private String actualNum = "";
    private String actualPower = "";
    private String onGridModel = "";
    private String installDate = "";
    private String taskType = "";
    private String startInspectionDate = "";
    private String endInspectionDate = "";
    private String taskOperatorName = "";
    private String auditor = "";
    private String taskStatus = "";
    private String acceptResult = "";
    private String overTimeStatus = "";
    private String proposalSubmitDate = "";
    private String resultSubmitDate = "";
    private String onGridPeriod= "";
    private String riskAssessment = "";
    private String applyDescribe = "";
    private String handlingOpinions = "";
    private String completedFlag = "";
    private String unqualifiedInfo = "";
}

4、表格工具类

package cn.itsaxon.utils;

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.*;

/**
 * Excel工具类
 *
 * @author itsaxon
 * @date 2021-03-17 16:39
 */
public class ExcelUtil {
   

    /**
     * 将Sheet列号变为列名
     *
     * @param index 列号, 从0开始
     * @return 0->A; 1->B...26->AA
     */
    public static String index2ColName(int index) {
   
        if (index < 0) {
   
            return null;
        }
        // A的Unicode码
        int num = 65;
        String colName = "";
        do {
   
            if (colName.length() > 0) {
   
                index--;
            }
            int remainder = index % 26;
            colName = ((char) (remainder + num)) + colName;
            index = (int) ((index - remainder) / 26);
        } while (index > 0);
        return colName;
    }

    /**
     * 根据表元的列名转换为列号
     *
     * @param colName 列名, 从A开始
     * @return A1->0; B1->1...AA1->26
     */
    public static int colName2Index(String colName) {
   
        int index = -1;
        // A的Unicode码
        int num = 65;
        int length = colName.length();
        for (int i = 0; i < length; i++) {
   
            char c = colName.charAt(i);
            // 确定指定的char值是否为数字
            if (Character.isDigit(c))