poi实现生成下拉选联动

淩亂°似流年 2024-02-19 18:33 16阅读 0赞

在我们实际的程序开发中，经常需要用到从excel导入数据中系统中，而为了防止用户在excel中乱输入文字，有些需要用到下拉选的地方，就需要从程序中动态生成模板。本例子简单的讲解一下，如何生成级联下拉选。

**效果图：**（选择汽车这个下拉选、后面水果下拉选的值动态改变）

![aHR0cDovL2RsMi5pdGV5ZS5jb20vdXBsb2FkL2F0dGFjaG1lbnQvMDEyNC8wODc1LzMwZjBhNzQ0LWZjNTItMzBiNy1iNGE4LWVkMGZjNDIzZTViOS5wbmc][]![aHR0cDovL2RsMi5pdGV5ZS5jb20vdXBsb2FkL2F0dGFjaG1lbnQvMDEyNC8wODc3LzIwOWZiYWNkLWRhODUtMzgzZC05MjhiLTE0MmZmN2VhMTQ4My5wbmc][]

级联下拉选的实现步骤：

1.在excel中的另外一个sheet页中，保存着下拉选的值

2.给下拉中的数据创建一个名字( 类似于在excel中操作命名管理器 )  
![aHR0cDovL2RsMi5pdGV5ZS5jb20vdXBsb2FkL2F0dGFjaG1lbnQvMDEyNC8wODgzLzNiZGQzNGY3LWFiMzAtMzczZi04MmFmLTNmOTUwY2ZmNzYyNS5wbmc][]

3.使用INDIRECT()函数获取到级联下拉选的值。

4.poi官网上创建级联下拉选的说明

![aHR0cDovL2RsMi5pdGV5ZS5jb20vdXBsb2FkL2F0dGFjaG1lbnQvMDEyNC8wODg5LzhhOGI1YzM2LTNmYTYtMzI4ZS1hZTY5LTc3YjAzYzU1ZmFiNS5wbmc][]  
 **本例子中，动态excel模板的生成的一个简单思路。**

**注意：**每一个模板字段，都是一个ExportDefinition对象，如果是下拉选的类型，则**mainDict**字段有值，根据它的值从DictData中加载下拉选的值，如果选择它存在一个级联操作，那么**subDict**和**subField**字段有值。subDict也是从DictData中加载数据，subField表示级联的字段

**思路：**

1.根据一组ExportDefinition定义对象，生成excel的导出模板，此时导出的模板中只有一行到出头数据。

2.创建数据字典页，这个里面保存下拉选需要用到的数据。

3.如果是主下拉选，则给主下拉选创建一个名称管理。

4.如果是主下拉选，并且关联了级联下拉选，则此时需要加载子下拉选的数据，且根据主下拉选中的每一项创建一个名称管理，值为关联的子下拉选的值

5.使用**INDIRECT**函数、设置数据的有效性等。

**步骤：**

**1.创建下拉选的值**

public class DictData {
    	public static Map<String, Object> dict = null;
    	static {
    		dict = new HashMap<>();
    		List<String> list = new ArrayList<>();
    		list.add("汽车");
    		list.add("水果");
    		dict.put("car-dict", list);
    		Map<String, List<String>> subMap = new HashMap<>();
    		list = new ArrayList<>();
    		list.add("宝马");
    		list.add("大众");
    		subMap.put("汽车", list);
    		list = new ArrayList<>();
    		list.add("苹果");
    		list.add("梨子");
    		subMap.put("水果", list);
    		dict.put("fruit-dict", subMap);
    		list = new ArrayList<>();
    		list.add("汽车-1");
    		list.add("水果-1");
    		dict.put("t-dict", list);
    	}
    
    	/** 获取数据字典中的值 */
    	public static Object getDict(String dict) {
    		return DictData.dict.get(dict);
    	}
    }

**2.创建一个RowCellIndex对象，用于维护，当前创建了下拉选的第几行第几列**

public class RowCellIndex {
    	/** 单元格的行索引 */
    	private int rowIndex;
    	/** 单元格的列索引 */
    	private int cellIndex;
    	public RowCellIndex(int rowIndex, int cellIndex) {
    		this.rowIndex = rowIndex;
    		this.cellIndex = cellIndex;
    	}
    	public int getRowIndex() {
    		return rowIndex;
    	}
    	public void setRowIndex(int rowIndex) {
    		this.rowIndex = rowIndex;
    	}
    	public int getCellIndex() {
    		return cellIndex;
    	}
    	public void setCellIndex(int cellIndex) {
    		this.cellIndex = cellIndex;
    	}
    	public int incrementRowIndexAndGet() {
    		this.rowIndex++;
    		return this.getRowIndex();
    	}
    	public int incrementCellIndexAndGet() {
    		this.cellIndex++;
    		return this.getCellIndex();
    	}
    }

**3.创建一个excel列的导出定义对象，**省略部分getter和setter方法

public class ExportDefinition {
    	private String title; // 标题
    	private String field; // 字段
    	private int rowIndex; // 所在的行
    	private int cellIndex; // 所在的列
    	private String mainDict; // 主字典-用于加载主字典的数据
    	private String subDict; // 子字典-用于加载subField的数据
    	private String subField; // 即需要级联的字典
    	private String refName; // 主字段所在的位置
    	private String point; // 标题的坐标
    	private boolean validate;// 是否设置数据的有限性
    	public ExportDefinition(String title, String field, String mainDict, String subDict, String subField) {
    		this.title = title;
    		this.field = field;
    		this.mainDict = mainDict;
    		this.subDict = subDict;
    		this.subField = subField;
    	}

**4.实现导出**

package com.huan.excel.ex2;
    
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.OutputStream;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Map;
    import java.util.Map.Entry;
    import java.util.Objects;
    
    import org.apache.poi.hssf.usermodel.DVConstraint;
    import org.apache.poi.hssf.usermodel.HSSFDataValidation;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.hssf.util.CellReference;
    import org.apache.poi.ss.usermodel.DataValidation;
    import org.apache.poi.ss.usermodel.Name;
    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.CellRangeAddressList;
    
    /**
     * 生成级联下拉选
     * 
     * @描述
     * @作者 huan
     * @时间 2017年3月31日 - 下午9:14:43
     */
    public class DropDownListTest {
    
    	private static final String DICT_SHEET = "DICT_SHEET";
    
    	public static void main(String[] args) throws IOException {
    		// 1.准备需要生成excel模板的数据
    		List<ExportDefinition> edList = new ArrayList<>(2);
    		edList.add(new ExportDefinition("生活用品", "xx", null, null, null));
    		edList.add(new ExportDefinition("汽车", "cat", "car-dict", "fruit-dict", "fruit"));
    		edList.add(new ExportDefinition("水果", "fruit", "fruit-dict", "", ""));
    		edList.add(new ExportDefinition("测试", "yy", "t-dict", null, null));
    
    		// 2.生成导出模板
    		Workbook wb = new HSSFWorkbook();
    		Sheet sheet = createExportSheet(edList, wb);
    
    		// 3.创建数据字典sheet页
    		createDictSheet(edList, wb);
    
    		// 4.设置数据有效性
    		setDataValidation(edList, sheet);
    
    		// 5.保存excel到本地
    		OutputStream os = new FileOutputStream("d:/4.xls");
    		wb.write(os);
    
    		System.out.println("模板生成成功.");
    	}
       public static void createDataValidateSubList(Sheet sheet, ExportDefinition ed) {
    		int rowIndex = ed.getRowIndex();
    		CellRangeAddressList cal;
    		DVConstraint constraint;
    		CellReference cr;
    		DataValidation dataValidation;
    		System.out.println(ed);
    		for (int i = 0; i < 100; i++) {
    			int tempRowIndex = ++rowIndex;
    			cal = new CellRangeAddressList(tempRowIndex, tempRowIndex, ed.getCellIndex(), ed.getCellIndex());
    			cr = new CellReference(rowIndex, ed.getCellIndex() - 1, true, true);
    			constraint = DVConstraint.createFormulaListConstraint("INDIRECT(" + cr.formatAsString() + ")");
    			dataValidation = new HSSFDataValidation(cal, constraint);
    			dataValidation.setSuppressDropDownArrow(false);
    			dataValidation.createPromptBox("操作提示", "请选择下拉选中的值");
    			dataValidation.createErrorBox("错误提示", "请从下拉选中选择，不要随便输入");
    			sheet.addValidationData(dataValidation);
    		}
    	}
    
    	/**
    	 * @param edList
    	 * @param sheet
    	 */
    	private static void setDataValidation(List<ExportDefinition> edList, Sheet sheet) {
    		for (ExportDefinition ed : edList) {
    			if (ed.isValidate()) {// 说明是下拉选
    				DVConstraint constraint = DVConstraint.createFormulaListConstraint(ed.getField());
    				if (null == ed.getRefName()) {// 说明是一级下拉选
    					createDataValidate(sheet, ed, constraint);
    				} else {// 说明是二级下拉选
    					createDataValidateSubList(sheet, ed);
    				}
    			}
    		}
    	}
    
    	/**
    	 * @param sheet
    	 * @param ed
    	 * @param constraint
    	 */
    	private static void createDataValidate(Sheet sheet, ExportDefinition ed, DVConstraint constraint) {
    		CellRangeAddressList regions = new CellRangeAddressList(ed.getRowIndex() + 1, ed.getRowIndex() + 100, ed.getCellIndex(), ed.getCellIndex());
    		DataValidation dataValidation = new HSSFDataValidation(regions, constraint);
    		dataValidation.setSuppressDropDownArrow(false);
    		// 设置提示信息
    		dataValidation.createPromptBox("操作提示", "请选择下拉选中的值");
    		// 设置输入错误信息
    		dataValidation.createErrorBox("错误提示", "请从下拉选中选择，不要随便输入");
    		sheet.addValidationData(dataValidation);
    	}
    
    	/**
    	 * @param edList
    	 * @param wb
    	 */
    	private static void createDictSheet(List<ExportDefinition> edList, Workbook wb) {
    		Sheet sheet = wb.createSheet(DICT_SHEET);
    		RowCellIndex rci = new RowCellIndex(0, 0);
    		for (ExportDefinition ed : edList) {
    			String mainDict = ed.getMainDict();
    			if (null != mainDict && null == ed.getRefName()) {// 是第一个下拉选
    				List<String> mainDictList = (List<String>) DictData.getDict(mainDict);
    				String refersToFormula = createDictAndReturnRefFormula(sheet, rci, mainDictList);
    				// 创建 命名管理
    				createName(wb, ed.getField(), refersToFormula);
    				ed.setValidate(true);
    			}
    			if (null != mainDict && null != ed.getSubDict() && null != ed.getSubField()) {// 联动时加载ed.getSubField()的数据
    				ExportDefinition subEd = fiterByField(edList, ed.getSubField());// 获取需要级联的那个字段
    				if (null == subEd) {
    					continue;
    				}
    				subEd.setRefName(ed.getPoint());// 保存主下拉选的位置
    				subEd.setValidate(true);
    				Map<String, List<String>> subDictListMap = (Map<String, List<String>>) DictData.getDict(ed.getSubDict());
    				for (Entry<String, List<String>> entry : subDictListMap.entrySet()) {
    					String refersToFormula = createDictAndReturnRefFormula(sheet, rci, entry.getValue());
    					// 创建 命名管理
    					createName(wb, entry.getKey(), refersToFormula);
    				}
    			}
    		}
    	}
    
    	/**
    	 * @param sheet
    	 * @param rci
    	 * @param mainDict
    	 * @return
    	 */
    	private static String createDictAndReturnRefFormula(Sheet sheet, RowCellIndex rci, List<String> datas) {
    		Row row = sheet.createRow(rci.incrementRowIndexAndGet());
    		rci.setCellIndex(0);
    		int startRow = rci.getRowIndex();
    		int startCell = rci.getCellIndex();
    		for (String dict : datas) {
    			row.createCell(rci.incrementCellIndexAndGet()).setCellValue(dict);
    		}
    		int endRow = rci.getRowIndex();
    		int endCell = rci.getCellIndex();
    		String startName = new CellReference(DICT_SHEET, startRow, startCell, true, true).formatAsString();
    		String endName = new CellReference(endRow, endCell, true, true).formatAsString();
    		String refersToFormula = startName + ":" + endName;
    		System.out.println(refersToFormula);
    		return refersToFormula;
    	}
    
    	/**
    	 * @param wb
    	 * @param nameName
    	 *            表示命名管理的名字
    	 * @param refersToFormula
    	 */
    	private static void createName(Workbook wb, String nameName, String refersToFormula) {
    		Name name = wb.createName();
    		name.setNameName(nameName);
    		name.setRefersToFormula(refersToFormula);
    	}
    
    	private static ExportDefinition fiterByField(List<ExportDefinition> edList, String field) {
    		for (ExportDefinition ed : edList) {
    			if (Objects.equals(ed.getField(), field)) {
    				return ed;
    			}
    		}
    		return null;
    	}
    
    	/**
    	 * @param edList
    	 * @param wb
    	 */
    	private static Sheet createExportSheet(List<ExportDefinition> edList, Workbook wb) {
    		Sheet sheet = wb.createSheet("导出模板");
    		RowCellIndex rci = new RowCellIndex(0, 0);
    		Row row = sheet.createRow(rci.getRowIndex());
    		CellReference cr = null;
    		for (ExportDefinition ed : edList) {
    			row.createCell(rci.incrementCellIndexAndGet()).setCellValue(ed.getTitle());
    			ed.setRowIndex(rci.getRowIndex());
    			ed.setCellIndex(rci.getCellIndex());
    			cr = new CellReference(ed.getRowIndex() + 1, ed.getCellIndex(), true, true);
    			ed.setPoint(cr.formatAsString());
    		}
    		return sheet;
    	}
    
    }

[aHR0cDovL2RsMi5pdGV5ZS5jb20vdXBsb2FkL2F0dGFjaG1lbnQvMDEyNC8wODc1LzMwZjBhNzQ0LWZjNTItMzBiNy1iNGE4LWVkMGZjNDIzZTViOS5wbmc]: https://imgconvert.csdnimg.cn/aHR0cDovL2RsMi5pdGV5ZS5jb20vdXBsb2FkL2F0dGFjaG1lbnQvMDEyNC8wODc1LzMwZjBhNzQ0LWZjNTItMzBiNy1iNGE4LWVkMGZjNDIzZTViOS5wbmc
[aHR0cDovL2RsMi5pdGV5ZS5jb20vdXBsb2FkL2F0dGFjaG1lbnQvMDEyNC8wODc3LzIwOWZiYWNkLWRhODUtMzgzZC05MjhiLTE0MmZmN2VhMTQ4My5wbmc]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/ef7a79ee7aae43c1be9087cde442202a.png
[aHR0cDovL2RsMi5pdGV5ZS5jb20vdXBsb2FkL2F0dGFjaG1lbnQvMDEyNC8wODgzLzNiZGQzNGY3LWFiMzAtMzczZi04MmFmLTNmOTUwY2ZmNzYyNS5wbmc]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/c07ad66373d540bea2167b3afbccc6cc.png
[aHR0cDovL2RsMi5pdGV5ZS5jb20vdXBsb2FkL2F0dGFjaG1lbnQvMDEyNC8wODg5LzhhOGI1YzM2LTNmYTYtMzI4ZS1hZTY5LTc3YjAzYzU1ZmFiNS5wbmc]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/5537cce204b2434ca4ae94c4ca479aa6.png