poi实现生成下拉选联动
在我们实际的程序开发中,经常需要用到从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.创建下拉选的值
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;
}
}
相关文章
- 【网络共享解决】Internet连接共享访问被启用时,出现了一个错误 无法启用Internet连接共享。为LAN连接配置的IP地址需要使用自动IP寻址。
- Shell系列(37)- while和until循环
- Shell系列(36)- for循环语法二简介及批量添加删除用户
- Shell系列(35)- for循环语法一简介及批量解压缩脚本
- Shell系列(34) - 多分支case语句简介及实例
- Shell系列(32)- 双分支if语句判断Apache服务是否启动
- Shell系列(31)- 双分支if语句简介
- Shell系列(30)- 单分支if语句判断分区使用率
- Shell系列(29)- 单分支if语句格式
- Shell条件判断(6)- 多重条件判断
- Shell系列(28)- 条件判断之字符串判断
- Shell系列(27)- 条件判断之两个整数比较
- Shell系列(26)- 条件判断之两个文件比较
- Shell系列(25)- 条件判断之文件权限
- Shell系列(24)- 条件判断之文件类型
- Shell系列(23)- 字符截取命令sed
- Shell系列(22)- 字符截取命令awk
- Shell系列(21)- 字符截取命令printf
- Shell系列(20)- 字符截取命令cut
- Shell系列(19)- 正则表达式