效果如下:

直接上代码，自己看

工具类：

import cn.hutool.core.util.ObjectUtil;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.hj.chain.platform.vo.check.CheckFactorInfoVo;
import org.hj.chain.platform.vo.check.CheckFactorSubsetVo;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;


@Slf4j
public class PoiUtil<T> {
    private static final String MAIN_SHEET = "信息";
    private static final String SHEET_MAP = "仪器";


    /**
     * 计算formula
     *
     * @param offset   偏移量，如果给0，表示从A列开始，1，就是从B列
     * @param rowId    第几行
     * @param colCount 一共多少列
     * @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1
     */
    private static String getRange(int offset, int rowId, int colCount) {
        char start = (char) ('A' + offset);
        if (colCount <= 25) {
            char end = (char) (start + colCount - 1);
            return "$" + start + "$" + rowId + ":$" + end + "$" + rowId;
        } else {
            char endPrefix = 'A';
            char endSuffix = 'A';
            if ((colCount - 25) / 26 == 0 || colCount == 51) {// 26-51之间，包括边界（仅两次字母表计算）
                if ((colCount - 25) % 26 == 0) {// 边界值
                    endSuffix = (char) ('A' + 25);
                } else {
                    endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
                }
            } else {// 51以上
                if ((colCount - 25) % 26 == 0) {
                    endSuffix = (char) ('A' + 25);
                    endPrefix = (char) (endPrefix + (colCount - 25) / 26 - 1);
                } else {
                    endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
                    endPrefix = (char) (endPrefix + (colCount - 25) / 26);
                }
            }
            return "$" + start + "$" + rowId + ":$" + endPrefix + endSuffix + "$" + rowId;
        }
    }

    //-----------------------------------------------------------

    /**
     * 导出三级联动and单个下拉框的excel
     *
     * @param name       文件名称
     * @param headers    表头
     * @param mapOneList 一级所有内容
     * @param map        三级联动对应内容
     * @param list       导出数据
     */
    public static <T> void export(HttpServletResponse response, String name, List<String> headers, List<String> mapOneList, Map<String, LinkedList<String>> map, List<T> list) {
        // 1.创建Excel
        // 1)创建workbook
        HSSFWorkbook hssfWorkBook = new HSSFWorkbook();
        // 2)创建sheet
        HSSFSheet mainSheet = hssfWorkBook.createSheet(MAIN_SHEET);// 主sheet
        AtomicInteger add = new AtomicInteger();
        //获取行数
        for (int i = 0; i < list.size(); i++) {
            CheckFactorInfoVo checkFactorInfoVo = (CheckFactorInfoVo) list.get(i);
            if ("1".equals(checkFactorInfoVo.getIsFactor())) {
                if (ObjectUtil.isNotEmpty(checkFactorInfoVo.getFactorSubsetVos())) {
                    add.addAndGet(checkFactorInfoVo.getFactorSubsetVos().size());
                } else {
                    add.addAndGet(1);
                }
            } else {
                add.addAndGet(1);
            }
        }
        // 用于展示
        //2 创建表头，供用户输入
        initHeaders(hssfWorkBook, mainSheet, headers);
        //导出数据到主sheet
        setMainSheet(mainSheet, list);
        //三级联动 sheet
        HSSFSheet mapSheet = hssfWorkBook.createSheet(SHEET_MAP);
        // true:隐藏/false:显示
        //仪器关系sheet
        hssfWorkBook.setSheetHidden(hssfWorkBook.getSheetIndex(mapSheet), true);// 设置sheet是否隐藏
        // 3.写入数据
        writeData(hssfWorkBook, mapSheet, mapOneList, map);// 将数据写入隐藏的sheet中并做好关联关系
        // 4.设置数据有效性
        setDataValid(hssfWorkBook, mainSheet, mapOneList, map, add.get());


        response.reset();

        try (OutputStream output = response.getOutputStream()) {
            response.setContentType("application/octet-stream;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment; filename=" + name);
            output.flush();
            hssfWorkBook.write(output);
        } catch (IOException e) {
            log.error("导出失败!");
            e.printStackTrace();
        }
        response.setCharacterEncoding("UTF-8");


    }

    /**
     * 生成主页面表头
     *
     * @param wb
     * @param mainSheet
     * @param headers
     */
    private static void initHeaders(HSSFWorkbook wb, HSSFSheet mainSheet, List<String> headers) {
        //表头样式
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
        //字体样式
        HSSFFont fontStyle = wb.createFont();
        fontStyle.setFontName("微软雅黑");
        fontStyle.setFontHeightInPoints((short) 12);
        fontStyle.setBold(true);
        style.setFont(fontStyle);
        //生成主内容
        HSSFRow rowFirst = mainSheet.createRow(0);//第一个sheet的第一行为标题
        mainSheet.createFreezePane(0, 1, 0, 1); //冻结第一行

        CellRangeAddress region1 = new CellRangeAddress(0, 0, 5, 6);
        CellRangeAddress region2 = new CellRangeAddress(0, 0, 9, 11);
        mainSheet.addMergedRegion(region1);
        mainSheet.addMergedRegion(region2);

        //写标题
        for (int i = 0; i < headers.size(); i++) {
            HSSFCell cell;
            if (i < 6) {
                cell = rowFirst.createCell(i); //获取第一行的每个单元格
            } else if (i >= headers.size() - 3) {
                cell = rowFirst.createCell(i + 3);
            } else {
                cell = rowFirst.createCell(i + 1);
            }

            mainSheet.setColumnWidth(i, 4000); //设置每列的列宽
            if (i == 3) {
                mainSheet.setColumnWidth(i, 6000);
            }
            cell.setCellStyle(style); //加样式
            cell.setCellValue(headers.get(i)); //往单元格里写数据
        }
    }

    private static <T> void setMainSheet(HSSFSheet mainSheet, List<T> list) {

        HSSFCellStyle style = mainSheet.getWorkbook().createCellStyle();
        style.setVerticalAlignment(VerticalAlignment.CENTER); // 创建一个居中格式
        HSSFFont font = mainSheet.getWorkbook().createFont();
        font.setFontName("宋体");
        style.setFont(font);

        //同系套餐因子占用的总行数
        AtomicInteger add = new AtomicInteger();
        for (int j = 0; j < list.size(); j++) {
            //行
            Row row = mainSheet.createRow(j + 1 + add.get());
            CheckFactorInfoVo obj = (CheckFactorInfoVo) list.get(j);

            if (obj != null) {
                //因子数据填充
                if (!"1".equals(obj.getIsFactor())) {
                    for (int i = 0; i <= 13; i++) {
                        Cell cell = row.createCell(i);
                        cell.setCellStyle(style);
                        switch (i) {
                            case 0:
                                cell.setCellValue(obj.getSampleNo());
                                break;
                            case 1:
                            case 5:
                                cell.setCellValue(obj.getFactorName());
                                break;
                            case 2:
                                cell.setCellValue(obj.getStandardNo());
                                break;
                            case 3:
                                cell.setCellValue(obj.getStandardName());
                                break;
                            case 4:
                                cell.setCellValue(obj.getFactorRemark());
                                break;
                            case 6:
                                if (ObjectUtil.isNotNull(obj.getCheckRes())) {
                                    JSONObject jsonObject = JSONObject.parseObject(obj.getCheckRes());
                                    String v1 = jsonObject.getString("v1");
                                    String v2 = jsonObject.getString("v2");
                                    if (ObjectUtil.isNull(v2)|| "".equals(v2)) {
                                        cell.setCellValue(v1);
                                    } else {
                                        String initial = getInitial(v1, v2);
                                        cell.setCellValue(initial);
                                    }
                                }
                                break;
                            case 7:
                                cell.setCellValue(obj.getUnitName());
                                break;
                            case 8:
                                if (ObjectUtil.isNotNull(obj.getRemark())) {
                                    cell.setCellValue(obj.getRemark());
                                }
                                break;
                            case 9:
                                if (ObjectUtil.isNotNull(obj.getCheckEquipment())) {
                                    JSONObject jsonObject = JSONObject.parseObject(obj.getCheckEquipment());
                                    cell.setCellValue(jsonObject.getString("equipmentName"));
                                }
                                break;
                            case 10:
                                if (ObjectUtil.isNotNull(obj.getCheckEquipment())) {
                                    JSONObject jsonObject = JSONObject.parseObject(obj.getCheckEquipment());
                                    cell.setCellValue(jsonObject.getString("equipmentModel"));
                                }
                                break;
                            case 11:
                                if (ObjectUtil.isNotNull(obj.getCheckEquipment())) {
                                    JSONObject jsonObject = JSONObject.parseObject(obj.getCheckEquipment());
                                    cell.setCellValue(jsonObject.getString("equipmentNumber"));
                                }
                                break;
                            case 12:
                                cell.setCellValue(obj.getCheckFactorId());
                                break;
                            case 13:
                                cell.setCellValue("1".equals(obj.getIsFactor()) ? "是" : "否");
                                break;
                        }

                    }
                } else {

                    //套餐内容填充
                    for (int i = 0; i < 5; i++) {
                        Cell cell = row.createCell(i);
                        cell.setCellStyle(style);
                        switch (i) {
                            case 0:
                                cell.setCellValue(obj.getSampleNo());
                                break;
                            case 1:
                                cell.setCellValue(obj.getFactorName());
                                break;
                            case 2:
                                cell.setCellValue(obj.getStandardNo());
                                break;
                            case 3:
                                cell.setCellValue(obj.getStandardName());
                                break;
                            case 4:
                                cell.setCellValue(obj.getFactorRemark());
                                break;
                        }
                    }

                    List<CheckFactorSubsetVo> factorSubsetVos = obj.getFactorSubsetVos();
                    row.createCell(12).setCellValue(obj.getCheckFactorId());

                    //套餐因子填充
                    if (ObjectUtil.isNotEmpty(factorSubsetVos)) {
                        for (int i = 0; i < factorSubsetVos.size(); i++) {
                            if (i != 0) {
                                row = mainSheet.createRow(j + 1 + add.get() + i);
                            }
                            //填充因子名称
                            Cell cell5 = row.createCell(5);
                            cell5.setCellValue(factorSubsetVos.get(i).getFactorName());
                            cell5.setCellStyle(style);

                            Cell cell7 = row.createCell(7);
                            cell7.setCellValue(factorSubsetVos.get(i).getUnitName());
                            cell7.setCellStyle(style);

                            Cell cell12 = row.createCell(12);
                            cell12.setCellValue(obj.getCheckFactorId());
                            cell12.setCellStyle(style);

                            Cell cell13 = row.createCell(13);
                            cell13.setCellValue("1".equals(obj.getIsFactor()) ? "是" : "否");
                            cell13.setCellStyle(style);

                            Cell cell14 = row.createCell(14);
                            cell14.setCellValue(factorSubsetVos.get(i).getId());
                            cell14.setCellStyle(style);

                            if (ObjectUtil.isNotNull(factorSubsetVos.get(i).getCheckSubRes())) {
                                JSONObject jsonObject = JSONObject.parseObject(factorSubsetVos.get(i).getCheckSubRes());
                                String v1 = jsonObject.getString("v1");
                                String v2 = jsonObject.getString("v2");
                                Cell cell6 = row.createCell(6);
                                if (ObjectUtil.isNull(v2)|| "".equals(v2)) {
                                    cell6.setCellValue(v1);
                                } else {
                                    String initial = getInitial(v1, v2);
                                    cell6.setCellValue(initial);
                                }
                                cell6.setCellStyle(style);
                            }

                            if (ObjectUtil.isNotNull(obj.getRemark())) {
                                Cell cell8 = row.createCell(8);
                                cell8.setCellStyle(style);
                                cell8.setCellValue(obj.getRemark());
                            }

                            if (ObjectUtil.isNotNull(obj.getCheckEquipment())) {
                                JSONObject jsonObject = JSONObject.parseObject(obj.getCheckEquipment());

                                Cell cell9 = row.createCell(9);
                                cell9.setCellStyle(style);
                                cell9.setCellValue(jsonObject.getString("equipmentName"));

                                Cell cell10 = row.createCell(10);
                                cell10.setCellStyle(style);
                                cell10.setCellValue(jsonObject.getString("equipmentModel"));

                                Cell cell11 = row.createCell(11);
                                cell11.setCellStyle(style);
                                cell11.setCellValue(jsonObject.getString("equipmentNumber"));
                            }
                        }

                    }

                    //累加套餐因子所占行数
                    int oldAdd = add.getAndAdd(factorSubsetVos.size() - 1);

                    // 四个参数分别是：起始行、终止行、起始列、终止列.
                    CellRangeAddress region1 = new CellRangeAddress(j + oldAdd + 1, j + add.get() + 1, 0, 0);
                    CellRangeAddress region2 = new CellRangeAddress(j + oldAdd + 1, j + add.get() + 1, 1, 1);
                    CellRangeAddress region3 = new CellRangeAddress(j + oldAdd + 1, j + add.get() + 1, 2, 2);
                    CellRangeAddress region4 = new CellRangeAddress(j + oldAdd + 1, j + add.get() + 1, 3, 3);
                    CellRangeAddress region5 = new CellRangeAddress(j + oldAdd + 1, j + add.get() + 1, 4, 4);
                    CellRangeAddress region6 = new CellRangeAddress(j + oldAdd + 1, j + add.get() + 1, 8, 8);
                    CellRangeAddress region7 = new CellRangeAddress(j + oldAdd + 1, j + add.get() + 1, 9, 9);
                    CellRangeAddress region8 = new CellRangeAddress(j + oldAdd + 1, j + add.get() + 1, 10, 10);
                    CellRangeAddress region9 = new CellRangeAddress(j + oldAdd + 1, j + add.get() + 1, 11, 11);
                    mainSheet.addMergedRegion(region1);
                    mainSheet.addMergedRegion(region2);
                    mainSheet.addMergedRegion(region3);
                    mainSheet.addMergedRegion(region4);
                    mainSheet.addMergedRegion(region5);
                    mainSheet.addMergedRegion(region6);
                    mainSheet.addMergedRegion(region7);
                    mainSheet.addMergedRegion(region8);
                    mainSheet.addMergedRegion(region9);
                }

            }
            mainSheet.setColumnHidden(12, true);
            mainSheet.setColumnHidden(13, true);
            mainSheet.setColumnHidden(14, true);
        }
    }


    private static void setDataValid(HSSFWorkbook HSSFWorkBook, HSSFSheet mainSheet, List<String> firstList, Map<String, LinkedList<String>> siteMap, Integer line) {
        //设置一级下拉
        HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper((HSSFSheet) mainSheet);
        String[] dataArray = firstList.toArray(new String[0]);
        HSSFSheet hidden = HSSFWorkBook.createSheet("hidden");
        HSSFCell cell = null;
        for (int i = 0, length = dataArray.length; i < length; i++) {
            String name = dataArray[i];
            HSSFRow row = hidden.createRow(i);
            cell = row.createCell(0);
            cell.setCellValue(name);
        }

        Name namedCell = HSSFWorkBook.createName();
        namedCell.setNameName("hidden");
        namedCell.setRefersToFormula("hidden!$A$1:$A$" + dataArray.length);
        //加载数据,将名称为hidden的
        DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden");
        // 四个参数分别是：起始行、终止行、起始列、终止列.
        // 1 (一级下拉框代表从excel第1+1行开始) line(一级下拉框代表从excel第1+10行结束) 9(代表第几列开始，0是第一列，1是第二列) 1(代表第几列结束，0是第一列，1是第二列)
        CellRangeAddressList firstRangeAddressList = new CellRangeAddressList(1, line, 9, 9);
        DataValidation firstDataValidation = dvHelper.createValidation(constraint, firstRangeAddressList);
        firstDataValidation.createErrorBox("error", "请选择正确");
        firstDataValidation.setShowErrorBox(true);
        // firstDataValidation.setSuppressDropDownArrow(true);
        //将第二个sheet设置为隐藏
        // true:隐藏/false:显示
        HSSFWorkBook.setSheetHidden(HSSFWorkBook.getSheetIndex(hidden), true);// 设置sheet是否隐藏
//        HSSFWorkBook.setSheetHidden(1, true);
        mainSheet.addValidationData(firstDataValidation);

        // 设置第二级、第三级下拉
        // i <= 10 ,10代表第二级、第三级下拉框到10+1行结束
        for (int i = 0; i <= 10; i++) {
            setDataValidation('J', mainSheet, i + 1, 10);// "J"是指父类所在的列，i+1初始值为1代表从第2行开始，10要与“J”对应，为J的列号加1，假如第一个参数为“C”，那么最后一个参数就3
        }

    }

    /**
     * 设置有效性
     *
     * @param offset 主影响单元格所在列，即此单元格由哪个单元格影响联动
     * @param sheet
     * @param rowNum 行数
     * @param colNum 列数
     */
    private static void setDataValidation(char offset, HSSFSheet sheet, int rowNum, int colNum) {
        HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheet);
        DataValidation dataValidationList1;
        DataValidation dataValidationList2;
        dataValidationList1 = getDataValidationByFormula("INDIRECT($" + offset + (rowNum) + ")", rowNum, colNum, dvHelper);
        dataValidationList2 = getDataValidationByFormula("INDIRECT($" + (char) (offset + 1) + (rowNum) + ")", rowNum, colNum + 1, dvHelper);
        sheet.addValidationData(dataValidationList1);
        sheet.addValidationData(dataValidationList2);
    }


    private static DataValidation getDataValidationByFormula(String formulaString, int naturalRowIndex, int naturalColumnIndex, HSSFDataValidationHelper dvHelper) {
        DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint(formulaString);
        CellRangeAddressList regions = new CellRangeAddressList(naturalRowIndex, 65535, naturalColumnIndex, naturalColumnIndex);
        HSSFDataValidation data_validation_list = (HSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
        data_validation_list.setEmptyCellAllowed(false);
        if (data_validation_list instanceof HSSFDataValidation) {
            // data_validation_list.setSuppressDropDownArrow(true);
            data_validation_list.setShowErrorBox(true);
        } else {
            // data_validation_list.setSuppressDropDownArrow(false);
        }
        // 设置输入信息提示信息
        data_validation_list.createPromptBox("下拉选择提示", "请使用下拉方式选择合适的值！");
        return data_validation_list;
    }


    private static void writeData(HSSFWorkbook hssfWorkBook, HSSFSheet mapSheet, List<String> firstList, Map<String, LinkedList<String>> subMap) {

        //循环将父数据写入siteSheet的第1行中
        int siteRowId = 0;
        HSSFRow firstRow = mapSheet.createRow(siteRowId++);
        firstRow.createCell(0).setCellValue("父列表");
        for (int i = 0; i < firstList.size(); i++) {
            firstRow.createCell(i + 1).setCellValue(firstList.get(i));
        }
        // 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。
        Iterator<String> keyIterator = subMap.keySet().iterator();
        while (keyIterator.hasNext()) {
            String key = keyIterator.next();
            List<String> son = subMap.get(key);
            HSSFRow siteRow = mapSheet.createRow(siteRowId++);
            siteRow.createCell(0).setCellValue(key);
            for (int i = 0; i < son.size(); i++) {
                siteRow.createCell(i + 1).setCellValue(son.get(i));
            }

            // 添加名称管理器
            String range = getRange(1, siteRowId, son.size());
            Name name = hssfWorkBook.createName();
            name.setNameName(key);
            String formula = mapSheet.getSheetName() + "!" + range;
            name.setRefersToFormula(formula);
        }
    }

    private static String getInitial(String a, String b) {

        if (ObjectUtil.isNull(b)|| "".equals(b)) {
            return a;
        } else {
            StringBuffer sb = new StringBuffer();
            return sb.append(a).append("*").append("10^").append(b).toString();
        }

    }

}

使用方法:
根据自身业务进行组装数据

public void export(HttpServletResponse response, CheckFactorSearchVo searchVo) {
	    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd-HH-mm-ss");
	    String dateString = format.format(new Date());
	    //文件名称
	    String name = dateString+".xls";
	    //excel 表头
	    List<String> headers = Arrays.asList("样品编号","检测因子","标准号", "检测标准", "备注信息", "检测结果","单位","备注","检测设备","检测因子id","因子套餐","套餐因子id");
        JSONArray tree = (JSONArray)equipmentInfoService.laboratoryEquipmentTree().getResult();

        LinkedList<String> firstList = new LinkedList<>();
        HashMap<String, LinkedList<String>> subMap = new HashMap();

        tree.forEach( object->{
             String firstName = ((JSONObject) object).getString("name");
             firstList.add(firstName);
             JSONArray children = ((JSONObject) object).getJSONArray("children");
             LinkedList<String> subList = new LinkedList<>();
             children.forEach( subObject ->{
                 String secondName = ((JSONObject) subObject).getString("name");
                 LinkedList<String> thirdList = new LinkedList<>();
                 subList.add(secondName);

                 JSONArray secondChildren = ((JSONObject) subObject).getJSONArray("children");
                 secondChildren.forEach( thirdObject->{
                      String thirdName = ((EquipmentTreeVo)thirdObject).getEquipmentNumber();
                      thirdList.add(thirdName);
                 });
                 subMap.put(secondName,thirdList);
             });
             subMap.put(firstName,subList);
        });
		PoiUtil.export(response,name,headers,firstList,subMap,factorInfoVos);
    }

导出内容如下:

有两个隐藏的表格用来实现级联下拉