EASYUI POI导出EXCEL二级联动下拉框

1、项目应用，工具类ExcelTemplate

package com.utils;

import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Classname ExcelTemplate
 * @Description 导出模板下拉框二级联动
 * @Created by wcy
 * @date 2021/12/21 10:16
 */
public class ExcelTemplate {

    private static final int XLS_MAX_ROW = 65535; //0开始
    private static final String PARENT_NAME = "oneName";
    private static final String hiddenSheet = "hidden";

    /**
     * 创建excel二级联动模板
     */
    public static void createExcelComboBox(Workbook wb, Sheet mainSheet, List<String> parent, Map<String, List<String>> itemMap,int oneOrderNum, int twoOrderNum) throws IOException {

            //创建工作薄,存放下拉框数据
            Sheet hidden = wb.createSheet(hiddenSheet);
            wb.setSheetHidden(1, true); //将用于存储下拉框数据的sheet隐藏
            //初始化下拉框数据
            initComboBox(wb, hidden, parent, itemMap);
			//初始化级联关系
          initSheetNameMapping(mainSheet,oneOrderNum,twoOrderNum);
    }

    /**
     * 初始化二级联动下拉框数据
     */
    private static void initComboBox(Workbook workbook, Sheet sheet, List<String> parent, Map<String, List<String>> itemList) {
        writeOneComboBox(sheet, parent);
        writeTwoComboBox(workbook, sheet, parent, itemList);
        initParentNameMapping(workbook, sheet.getSheetName(), parent.size());
    }

    /**
     * 在隐藏sheet中写入二级下拉框数据
     */
    private static void writeTwoComboBox(Workbook workbook, Sheet sheet, List<String> parent, Map<String, List<String>> itemList) {
        for (int i = 0; i < parent.size(); i++) {
            int referColNum = i + 1;
            String parentName = parent.get(i);
            List<String> items = itemList.get(parentName);
            if (CollectionUtils.isNotEmpty(items)) {
                int rowCount = sheet.getLastRowNum();
                if(rowCount == 0 && sheet.getRow(0) == null )
                    sheet.createRow(0);
                for (int j = 0; j < items.size(); j++) {
                    if (j <= rowCount) { //前面创建过的行，直接获取行，创建列
                        sheet.getRow(j).createCell(referColNum).setCellValue(items.get(j)); //设置对应单元格的值
                    } else { //未创建过的行，直接创建行、创建列
                        sheet.setColumnWidth(j, 4000); //设置每列的列宽
                        //创建行、创建列
                        sheet.createRow(j).createCell(referColNum).setCellValue(items.get(j)); //设置对应单元格的值
                    }
                }
            }
            initShelfNameMapping(workbook, sheet.getSheetName(), parentName, referColNum, items.size());
        }
    }

    /**
     * 一级与二级下拉选择数据关联
     */
    private static void initShelfNameMapping(Workbook workbook, String sheetName, String parentName, int referColNum, int shelfQuantity) {
        Name name = workbook.createName();
        // 设置二级名称
        name.setNameName(parentName);
        String referColName = getColumnName(referColNum);
        String formula = sheetName + "!$" + referColName + "$1:$" + referColName + "$" + shelfQuantity;
        name.setRefersToFormula(formula);
    }

    /**
     * 在隐藏sheet中写入一级下拉框数据
     */
    private static void writeOneComboBox(Sheet sheet, List<String> parent) {
        for (int i = 0; i < parent.size(); i++) {
            Row row = sheet.createRow(i);
            Cell cell = row.createCell(0);
            cell.setCellValue(parent.get(i));
        }
    }

    /**
     * 初始化一级下拉选择“名称”
     */
    private static void initParentNameMapping(Workbook workbook, String sheetName, int parentQuantity) {
        Name name = workbook.createName();
        // 设置一级下拉名称
        name.setNameName(PARENT_NAME);
        name.setRefersToFormula(sheetName + "!$A$1:$A$" + parentQuantity);
    }

    /**
     * 根据数据值确定单元格位置（比如：0-A, 27-AB）
     *
     * @param index
     * @return
     */
    public static String getColumnName(int index) {
        StringBuilder s = new StringBuilder();
        while (index >= 26) {
            s.insert(0, (char) ('A' + index % 26));
            index = index / 26 - 1;
        }
        s.insert(0, (char) ('A' + index));
        return s.toString();
    }

    /**
     * 主sheet中下拉框初始化
     *
     * @param mainSheet
     */
    private static void initSheetNameMapping(Sheet mainSheet, int oneOrderNum, int twoOrderNum) {
        DataValidation warehouseValidation = getDataValidationByFormula(PARENT_NAME, oneOrderNum-1);
        DataValidation shelfValidation = getDataValidationByFormula("INDIRECT($"+doHandle(oneOrderNum)+"1)", twoOrderNum-1); //formula同"INDIRECT(INDIRECT(\"R\"&ROW()&\"C\"&(COLUMN()-1),FALSE))"
        // 主sheet添加验证数据
        mainSheet.addValidationData(warehouseValidation);
        mainSheet.addValidationData(shelfValidation);
    }

    /**
     * 生成下拉框及提示
     *
     * @param formulaString
     * @param columnIndex
     * @return
     */
    public static DataValidation getDataValidationByFormula(String formulaString, int columnIndex) {
        // 加载下拉列表内容
        DVConstraint constraint = DVConstraint.createFormulaListConstraint(formulaString);
        // 设置数据有效性加载在哪个单元格上。
        // 四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(2, XLS_MAX_ROW, columnIndex, columnIndex);
        // 数据有效性对象
        DataValidation dataValidationList = new HSSFDataValidation(regions, constraint);
        dataValidationList.createErrorBox("Error", "请选择或输入有效的选项，或下载最新模版重试！");
        String promptText = initPromptText(columnIndex);
        dataValidationList.createPromptBox("", promptText);
        return dataValidationList;
    }

    public static String doHandle(final int num) {
        String[] charArr = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J",
                "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V",
                "W", "X", "Y", "Z" };
        return charArr[num - 1].toString();
    }

    /**
     * 初始化下拉框提示信息
     *
     * @param columnIndex
     * @return
     */
    private static String initPromptText(int columnIndex) {
        String promptText ="";
        //custom column prompt
        switch (columnIndex) {
            case 0:
                promptText = "请输入日期格式为yyyyMM！";
                break;
            case 2:
                promptText = "请下拉选择或输入有效项！且先选择数据分类！";
                break;
        }
        return promptText;
    }

}

导出的属性：

package com.sgcc.electric.price.data.business.vo;

import cn.afterturn.easypoi.excel.annotation.Excel;
import com.sgcc.utils.validator.anno.Valid;
import lombok.Data;

import javax.validation.constraints.Pattern;
import java.io.Serializable;

/**
 * @Description  数据
 * @Author  wcy
 * @Date 2021-12-20 17:51:00 
 */
@Data
public class DataVO implements Serializable {

	private static final long serialVersionUID = -539282916553401314L;
	/**
	 * 主键
	 */
	private String id;

	/**
	 * 年月
	 */
	@Excel(name = "年月",orderNum = "1")
	@Valid(empty = false, name = "年月")
	@Pattern(regexp = "^(20|19){1}[0-9]{2}(((0){1}[1-9]{1})|((1){1}(0|1|2){1}))$", message = "月份格式不正确！")
	private String tradeDate;

	/**
	 * 数据分类
	 */
	private String dataClassification;
	@Excel(name = "数据分类",orderNum = "2")
	@Valid(empty = false, name = "数据分类")
	private String dataClassificationName;

	/**
	 * 二级分类
	 */
	private String secondaryClassification;
	@Excel(name = "二级分类",orderNum = "3")
	@Valid(empty = false, name = "二级分类")
	private String secondaryClassificationName;
	
	/**
	 * 创建人员
	 */
	private String createUser;

	/**
	 * 创建时间
	 */
	private String createTime;

	/**
	 * 修改人员
	 */
	private String updateUser;

	/**
	 * 修改时间
	 */
	private String updateTime;

}

导出时使用：

/**
     * 模板下载
     */
    @Override
    public void exportExcelTemplate() {
        try {

            String title = "数据导入模板";//表格标题
            String fileName = "数据导入模板.xls";
            List<DataVO> vos = new ArrayList<DataVO>();
            ExportParams exportParams = new ExportParams(title, title);
            Workbook workbook = ExcelExportUtils.exportExcel(exportParams, DataVO.class, vos);

            //获取二级联动下拉框的值
            Sheet excelSheet = workbook.getSheet(title);
            List<EnumVO> enumList = (List<EnumVO>)enumService.selectTreeEnumByCode(EnumConstant.DATA_CLASSIFICATION).getData();
            List<String> parentList = enumList.stream().map(a -> a.getEnumItemName()).collect(Collectors.toList());
            Map<String, List<String>> itemMap = new HashMap<>();
            enumList.stream().forEach(a->{
                List<String> list = a.getEnumItemList().stream().map(b -> b.getEnumItemName()).collect(Collectors.toList());
                itemMap.put(a.getEnumItemName(),list);
            });
            //获取下拉框excel orderNum
            DataVO dataVO = new DataVO();
            Field field1 = dataVO.getClass().getDeclaredField("dataClassificationName");
            Excel annotation1 = field1.getAnnotation(Excel.class);
            Integer oneOrderNum = Integer.parseInt(annotation1.orderNum());
            Field field2 = dataVO.getClass().getDeclaredField("secondaryClassificationName");
            Excel annotation2 = field2.getAnnotation(Excel.class);
            Integer twoOrderNum = Integer.parseInt(annotation2.orderNum());

            //创建二级联动下拉框
            ExcelTemplate.createExcelComboBox(workbook,excelSheet,parentList,itemMap,oneOrderNum,twoOrderNum);

            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Content-Disposition", "attachment; filename="+ URLEncoder.encode(fileName, "UTF-8"));
            ServletOutputStream sos = response.getOutputStream();
            workbook.write(sos);
        }catch(Exception e){
            log.error("[BusinessDataServiceImpl]数据导入下载模板出现异常,{}",e);
        }
    }

2、参考文档：https://www.jianshu.com/p/be2f1af3af92

package com.utils;

import org.apache.commons.collections.CollectionUtils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by hqk2015@foxmail.com on 2017/11/14.
 * poi version: poi-3.10-FINAL-20140208
 */
public class ExcelTemplate2 {

    private static final int XLS_MAX_ROW = 65535; //0开始
    private static final String MAIN_SHEET_NAME = "template";
    private static final String HIDDEN_SHEET1_NAME = "hidden1";
    private static final String HIDDEN_SHEET2_NAME = "hidden2";
    private static final String WAREHOUSE_NAMES = "warehouses";
    private static final String DEVICE_NAMES = "devices";
    private static final String DEVICE_TYPE_NAMES = "deviceTypes";


    /**
     * 创建入库excel模版
     * @param filePath
     * @param headers
     * @param devices
     * @param deviceTypes
     * @param warehouses
     * @param warehouseAndShelves
     * @return
     * @throws IOException
     */
    private static File createStoreInExcelTemplate(String filePath, List<String> headers,List<String> devices, List<String> deviceTypes, List<String> warehouses, Map<String, List<String>> warehouseAndShelves) throws IOException {
        FileOutputStream out = null;
        File file;
        try {
            file = new File("D:/excel.xls"); //写文件
//            file = File.createTempFile("入库数据模版", ".xls");
            out = new FileOutputStream(file);
            HSSFWorkbook wb = new HSSFWorkbook();//创建工作薄
            HSSFSheet mainSheet = wb.createSheet(MAIN_SHEET_NAME);
            HSSFSheet dtHiddenSheet = wb.createSheet(HIDDEN_SHEET1_NAME);
            HSSFSheet wsHiddenSheet = wb.createSheet(HIDDEN_SHEET2_NAME);
            //wb.setSheetHidden(1, true); //将第二个用于存储下拉框数据的sheet隐藏
            //wb.setSheetHidden(2, true);
            initHeaders(wb, mainSheet, headers);
            initDevicesAndType(wb, dtHiddenSheet, devices, deviceTypes);
            initWarehousesAndShelves(wb, wsHiddenSheet, warehouses, warehouseAndShelves);
            initSheetNameMapping(mainSheet);
            initOtherConstraints(mainSheet);
            out.flush();
            wb.write(out);
//            file.deleteOnExit();
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        } finally {
            if (out != null)
                try {
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
        }
        return file;
    }

    /**
     * 初始化仓库&货架下拉框数据
     *
     * @param workbook
     * @param wsSheet
     * @param warehouses
     * @param warehousesAndShelves
     */
    private static void initWarehousesAndShelves(HSSFWorkbook workbook, HSSFSheet wsSheet, List<String> warehouses, Map<String, List<String>> warehousesAndShelves) {
        writeWarehouses(workbook, wsSheet, warehouses);
        writeShelves(workbook, wsSheet, warehouses, warehousesAndShelves);
        initWarehouseNameMapping(workbook, wsSheet.getSheetName(), warehouses.size());
    }

    /**
     * 在隐藏sheet中写入货架下拉框数据
     *
     * @param workbook
     * @param wsSheet
     * @param warehouses
     * @param warehousesAndShelves
     */
    private static void writeShelves(HSSFWorkbook workbook, HSSFSheet wsSheet, List<String> warehouses, Map<String, List<String>> warehousesAndShelves) {
        for (int i = 0; i < warehouses.size(); i++) {
            int referColNum = i + 1;
            String warehouseName = warehouses.get(i);
            List<String> shelves = warehousesAndShelves.get(warehouseName);
            if (CollectionUtils.isNotEmpty(shelves)) {
                int rowCount = wsSheet.getLastRowNum();
                if(rowCount == 0 && wsSheet.getRow(0) == null )
                    wsSheet.createRow(0);
                for (int j = 0; j < shelves.size(); j++) {
                    if (j <= rowCount) { //前面创建过的行，直接获取行，创建列
                        wsSheet.getRow(j).createCell(referColNum).setCellValue(shelves.get(j)); //设置对应单元格的值
                    } else { //未创建过的行，直接创建行、创建列
                        wsSheet.setColumnWidth(j, 4000); //设置每列的列宽
                        //创建行、创建列
                        wsSheet.createRow(j).createCell(referColNum).setCellValue(shelves.get(j)); //设置对应单元格的值
                    }
                }
            }
            initShelfNameMapping(workbook, wsSheet.getSheetName(), warehouseName, referColNum, shelves.size());
        }
    }

    /**
     * 货架与库房下拉选择数据关联
     *
     * @param workbook
     * @param wsSheetName
     * @param warehouseName
     * @param referColNum
     * @param shelfQuantity
     */
    private static void initShelfNameMapping(HSSFWorkbook workbook, String wsSheetName, String warehouseName, int referColNum, int shelfQuantity) {
        Name name = workbook.createName();
        // 设置货架名称
        name.setNameName(warehouseName);
        String referColName = getColumnName(referColNum);
        String formula = wsSheetName + "!$" + referColName + "$1:$" + referColName + "$" + shelfQuantity;
        name.setRefersToFormula(formula);
    }

    /**
     * 在隐藏sheet中写入库房下拉框数据
     *
     * @param workbook
     * @param wsSheet
     * @param warehouses
     */
    private static void writeWarehouses(HSSFWorkbook workbook, HSSFSheet wsSheet, List<String> warehouses) {
        for (int i = 0; i < warehouses.size(); i++) {
            HSSFRow row = wsSheet.createRow(i);
            HSSFCell cell = row.createCell(0);
            cell.setCellValue(warehouses.get(i));
        }
    }

    /**
     * 初始化库房选择“名称”
     *
     * @param workbook
     * @param wsSheetName
     * @param warehouseQuantity
     */
    private static void initWarehouseNameMapping(HSSFWorkbook workbook, String wsSheetName, int warehouseQuantity) {
        Name name = workbook.createName();
        // 设置仓库名称
        name.setNameName(WAREHOUSE_NAMES);
        name.setRefersToFormula(wsSheetName + "!$A$1:$A$" + warehouseQuantity);
    }

    /**
     * 根据数据值确定单元格位置（比如：0-A, 27-AB）
     *
     * @param index
     * @return
     */
    public static String getColumnName(int index) {
        StringBuilder s = new StringBuilder();
        while (index >= 26) {
            s.insert(0, (char) ('A' + index % 26));
            index = index / 26 - 1;
        }
        s.insert(0, (char) ('A' + index));
        return s.toString();
    }

    private static void initDevicesAndType(HSSFWorkbook wb, HSSFSheet dtHiddenSheet, List<String> devices, List<String> deviceTypes) {
        writeDevices(wb, dtHiddenSheet, devices);
        writeDeviceTypes(wb, dtHiddenSheet, deviceTypes);
        initDevicesNameMapping(wb, dtHiddenSheet.getSheetName(), devices.size());
        initDeviceTypesNameMapping(wb, dtHiddenSheet.getSheetName(), deviceTypes.size());
    }

    private static void initDeviceTypesNameMapping(HSSFWorkbook wb, String dtHiddenSheetName, int deviceTypeQuantity) {
        Name name = wb.createName();
        // 设置设备“名称”
        name.setNameName(DEVICE_TYPE_NAMES);
        name.setRefersToFormula(dtHiddenSheetName + "!$B$1:$B$" + deviceTypeQuantity);
    }

    private static void writeDeviceTypes(HSSFWorkbook wb, HSSFSheet dtHiddenSheet, List<String> deviceTypes) {
        int lastRow = dtHiddenSheet.getLastRowNum();
        if(lastRow == 0 && dtHiddenSheet.getRow(0) == null )
            dtHiddenSheet.createRow(0);
        if (CollectionUtils.isNotEmpty(deviceTypes))
            for (int j = 0; j < deviceTypes.size(); j++) {
                if (j <= lastRow) { //前面创建过的行，直接获取行，创建列
                    dtHiddenSheet.getRow(j).createCell(1).setCellValue(deviceTypes.get(j)); //设置对应单元格的值
                } else { //未创建过的行，直接创建行、创建列
                    dtHiddenSheet.setColumnWidth(j, 4000); //设置每列的列宽
                    //创建行、创建列
                    dtHiddenSheet.createRow(j).createCell(1).setCellValue(deviceTypes.get(j)); //设置对应单元格的值
                }
            }
    }

    private static void writeDevices(HSSFWorkbook wb, HSSFSheet dtHiddenSheet, List<String> devices) {
        for (int i = 0; i < devices.size(); i++) {
            HSSFRow row = dtHiddenSheet.createRow(i);
            HSSFCell cell1 = row.createCell(0);
            cell1.setCellValue(devices.get(i));
        }
    }

    private static void initDevicesNameMapping(HSSFWorkbook workbook, String dtHiddenSheetName, int deviceQuantity) {
        Name name = workbook.createName();
        // 设置设备“名称”
        name.setNameName(DEVICE_NAMES);
        name.setRefersToFormula(dtHiddenSheetName + "!$A$1:$A$" + deviceQuantity);
    }

    /**
     * 初始化表头
     *
     * @param wb
     * @param mainSheet
     * @param headers
     */
    private static void initHeaders(HSSFWorkbook wb, HSSFSheet mainSheet, List<String> headers) {

        //生成sheet1内容
        HSSFRow rowFirst = mainSheet.createRow(0);//第一个sheet的第一行为标题
        mainSheet.createFreezePane(0, 1, 0, 1); //冻结第一行
        //写标题
        for (int i = 0; i < headers.size(); i++) {
            HSSFCell cell = rowFirst.createCell(i); //获取第一行的每个单元格
            mainSheet.setColumnWidth(i, 4000); //设置每列的列宽

            cell.setCellValue(headers.get(i)); //往单元格里写数据
        }
    }

    /**
     * 主sheet中下拉框初始化
     *
     * @param mainSheet
     */
    private static void initSheetNameMapping(HSSFSheet mainSheet) {
        DataValidation warehouseValidation = getDataValidationByFormula(WAREHOUSE_NAMES, 3);
        DataValidation shelfValidation = getDataValidationByFormula("INDIRECT($D1)", 4); //formula同"INDIRECT(INDIRECT(\"R\"&ROW()&\"C\"&(COLUMN()-1),FALSE))"
        DataValidation deviceValidation = getDataValidationByFormula(DEVICE_NAMES, 0);
        DataValidation deviceTypeValidation = getDataValidationByFormula(DEVICE_TYPE_NAMES, 1);
        // 主sheet添加验证数据
        mainSheet.addValidationData(warehouseValidation);
        mainSheet.addValidationData(shelfValidation);
        mainSheet.addValidationData(deviceValidation);
        mainSheet.addValidationData(deviceTypeValidation);
    }

    /**
     * 初始化其他列输入限定
     *
     * @param mainSheet
     */
    private static void initOtherConstraints(HSSFSheet mainSheet) {
        DataValidation quantityValidation = getDecimalValidation(1, XLS_MAX_ROW, 2);
        DataValidation tierValidation = getDecimalValidation(1, XLS_MAX_ROW, 5);
        DataValidation colValidation = getDecimalValidation(1, XLS_MAX_ROW, 6);
        mainSheet.addValidationData(tierValidation);
        mainSheet.addValidationData(colValidation);
        mainSheet.addValidationData(quantityValidation);
    }
    /**
     * 生成下拉框及提示
     *
     * @param formulaString
     * @param columnIndex
     * @return
     */
    public static DataValidation getDataValidationByFormula(String formulaString, int columnIndex) {
        // 加载下拉列表内容
        DVConstraint constraint = DVConstraint.createFormulaListConstraint(formulaString);
        // 设置数据有效性加载在哪个单元格上。
        // 四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(1, XLS_MAX_ROW, columnIndex, columnIndex);
        // 数据有效性对象
        DataValidation dataValidationList = new HSSFDataValidation(regions, constraint);
        dataValidationList.createErrorBox("Error", "请选择或输入有效的选项，或下载最新模版重试！");
        String promptText = initPromptText(columnIndex);
        dataValidationList.createPromptBox("", promptText);
        return dataValidationList;
    }

    /**
     * 数字输入验证
     * @param firstRow
     * @param lastRow
     * @param columnIndex
     * @return
     */
    private static DataValidation getDecimalValidation(int firstRow, int lastRow, int columnIndex) {
        // 创建一个规则：>0的整数
        DVConstraint constraint = DVConstraint.createNumericConstraint(DVConstraint.ValidationType.INTEGER, DVConstraint.OperatorType.GREATER_OR_EQUAL, "0", "0");
        // 设定在哪个单元格生效
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, columnIndex, columnIndex);
        // 创建规则对象
        HSSFDataValidation decimalVal = new HSSFDataValidation(regions, constraint);
        decimalVal.createPromptBox("", initPromptText(columnIndex));
        decimalVal.createErrorBox("输入值类型或大小有误！", "数值型，请输入大于0 的整数。");
        return decimalVal;
    }

    /**
     * 初始化下拉框提示信息
     *
     * @param columnIndex
     * @return
     */
    private static String initPromptText(int columnIndex) {
        String promptText ="";
        //custom column prompt
        switch (columnIndex) {
            case 2:
                promptText = "请输入大于0的整数！";
                break;
            case 4:
                promptText = "请下拉选择或输入有效项！且先选择库房！";
                break;
            case 5:
                promptText = "请输入大于0的整数！";
                break;
            case 6:
                promptText = "请输入大于0的整数！";
                break;
        }
        return promptText;
    }

    public static File test() throws IOException {
        //************* mock data ************
        List<String> headers = Arrays.asList("设备名称", "设类型", "数量", "存放库房", "存放货架", "存放层", "存放列");
        List<String> deviceTypes = Arrays.asList("type1", "type2", "type3", "type4");
        List<String> devices = Arrays.asList("设备1", "设备2", "设备3");
        List<String> warehouses = Arrays.asList("库房1", "库房2");
        Map<String, List<String>> warehousesAndShelves = new HashMap<>();
        warehousesAndShelves.put("库房1", Arrays.asList("货架1-1", "货架1-2", "货架1-3"));
        warehousesAndShelves.put("库房2", Arrays.asList("货架2-1", "货架2-2", "货架2-3"));
        return ExcelTemplate2.createStoreInExcelTemplate("f:/test.xls", headers,devices, deviceTypes, warehouses, warehousesAndShelves);
    }

    public static void main(String[] args) throws IOException {
        ExcelTemplate2.test();
    }
}