poi实现生成下拉选联动

来源：互联网发布：朱雀网络和小米的关系编辑：程序博客网时间：2024/06/14 16:12

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

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

级联下拉选的实现步骤：

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

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

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

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

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

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

思路：

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

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

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

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

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

步骤：

下载点击

1.创建下拉选的值

Java代码  
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对象，用于维护，当前创建了下拉选的第几行第几列

Java代码  
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方法

Java代码  
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.实现导出

下载点击

Java代码  
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("模板生成成功.");  
    }  
  
    /** 
     * @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 {// 说明是二级下拉选  
                    constraint = DVConstraint.createFormulaListConstraint("INDIRECT(" + ed.getRefName() + ")");  
                    createDataValidate(sheet, ed, constraint);  
                }  
            }  
        }  
    }  
  
    /** 
     * @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;  
    }  
  
}  

0 0