你的位置:首页 > 软件开发 > Java > poi导出联动下拉选择的excel

poi导出联动下拉选择的excel

发布时间:2017-12-01 01:00:08
最近碰到一个功能, 觉得可以记录下来.在web中, 经常会碰到上下级的数据, 或者是联动数据, 比如省市联动. 那么在导入数据的时候, 是否可以在动态生成的模板中, 加入联动限制呢. 一. 数据准备public class DictData { public static M ...

poi导出联动下拉选择的excel

最近碰到一个功能, 觉得可以记录下来.

web中, 经常会碰到上下级的数据, 或者是联动数据, 比如省市联动. 那么在导入数据的时候, 是否可以在动态生成的模板中, 加入联动限制呢. 

一. 数据准备

public class DictData { public static Map<String, Object> dict = null; static {  dict = new HashMap<>();  //父节点  List<String> list = new ArrayList<>();  list.add("玲场地");  list.add("综合楼");  list.add("加建筑物");  dict.put("Building", list);  //子节点  Map<String, List<String>> subMap = new HashMap<>();  list = new ArrayList<>();  list.add("玲101");  list.add("玲102");  list.add("玲103");  list.add("玲104");  subMap.put("玲场地", list);  list = new ArrayList<>();  list.add("防灾体验中心");  list.add("财商教师");  list.add("沙画教室");  list.add("生命救护中心");  subMap.put("综合楼", list);  list = new ArrayList<>();  list.add("测试1");  list.add("测试2");  list.add("测试3");  list.add("测试4");  subMap.put("加建筑物", list);  dict.put("Room", subMap); } /** 获取数据字典中的值 */ public static Object getDict(String dict) {  return DictData.dict.get(dict); }}

数据结构:

  poi导出联动下拉选择的excel

二. 单元格坐标类

public class RowCellIndex { /** 单元格的行索引 */ private int rowIndex; /** 单元格的列索引 */ private int cellIndex;  public RowCellIndex(int rowIndex, int cellIndex) {  this.rowIndex = rowIndex;  this.cellIndex = cellIndex; } public int incrementRowIndexAndGet() {  this.rowIndex++;  return this.getRowIndex(); } public int incrementCellIndexAndGet() {  this.cellIndex++;  return this.getCellIndex(); } public int reduceRowIndexAndGet(){  this.rowIndex--;  return this.getRowIndex(); } public int reduceCellIndexAndGet(){  this.cellIndex--;  return this.getCellIndex(); } //region getter / setter 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; } //endregion}

操作的时候, 可以借助这个来确定坐标

 

三.  数据关系类

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; } //region getter / setter public String getTitle() {  return title; } public void setTitle(String title) {  this.title = title; } public String getField() {  return field; } public void setField(String field) {  this.field = field; } 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 String getMainDict() {  return mainDict; } public void setMainDict(String mainDict) {  this.mainDict = mainDict; } public String getSubDict() {  return subDict; } public void setSubDict(String subDict) {  this.subDict = subDict; } public String getSubField() { return subField; } public void setSubField(String subField) {  this.subField = subField; } public String getRefName() {  return refName; } public void setRefName(String refName) {  this.refName = refName; } public String getPoint() {  return point; } public void setPoint(String point) {  this.point = point; } public boolean isValidate() {  return validate; } public void setValidate(boolean validate) {  this.validate = validate; } //endregion}

 

四. 帮助类

public class LinkagePoiUtils { 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 < 1000; 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); } /**  * 创建数据字典sheet页  * @param edList  * @param wb  */ private static void createDictSheet(List<ExportDefinition> edList, Workbook wb, String dictSheetName, Map<String, Object> dic) {  Sheet sheet = wb.createSheet(dictSheetName);  sheet.setDefaultColumnWidth (15);// 设置单元格宽度  RowCellIndex rci = new RowCellIndex(0, 0);  for (ExportDefinition ed : edList) {   String mainDict = ed.getMainDict();   List<String> mainDictList = null;   //第一列选择项   if (null != mainDict && null == ed.getRefName()) {// 是第一个下拉选    mainDictList = (List<String>) dic.get(mainDict);    String refersToFormula = createParentDictAndReturnRefFormula(sheet, rci, mainDictList, dictSheetName);    // 创建 命名管理    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>>) dic.get(ed.getSubDict());    rci.setRowIndex(1);    rci.setCellIndex(0);    for (String keys : mainDictList) {     List<String> values = subDictListMap.get(keys);     String refersToFormula = createSonDicAndReturnRefFormula(sheet, rci, values, dictSheetName);     // 创建 命名管理     createName(wb, keys, refersToFormula);     rci.setRowIndex(1);     rci.incrementCellIndexAndGet();    }   }  } } /**  * 纵向输出数据 -- 子节点  *  * @param sheet  * @param rci  * @param datas  * @return  */ private static String createSonDicAndReturnRefFormula(Sheet sheet, RowCellIndex rci, List<String> datas, String dicSheetName) {  Row row = sheet.getRow(rci.getRowIndex());  if (row == null) {   row = sheet.createRow(rci.getRowIndex());   row.setHeightInPoints (20);// 行高  }  int startRow = rci.getRowIndex();  int startCell = rci.getCellIndex();  for (String dict : datas) {   row.createCell(rci.getCellIndex()).setCellValue(new HSSFRichTextString(dict));   rci.incrementRowIndexAndGet();   row = sheet.getRow(rci.getRowIndex());   if (row == null) {    row = sheet.createRow(rci.getRowIndex());    row.setHeightInPoints (20);// 行高   }  }  rci.reduceRowIndexAndGet();  int endRow = rci.getRowIndex();  int endCell = rci.getCellIndex();  String startName = new CellReference(dicSheetName, startRow, startCell, true, true).formatAsString();  String endName = new CellReference(endRow, endCell, true, true).formatAsString();  String refersToFormula = startName + ":" + endName;  rci.incrementRowIndexAndGet();  return refersToFormula; } /**  * 横向输出数据 -- 父节点  *  * @param sheet  * @param rci  * @param datas  * @return  */ private static String createParentDictAndReturnRefFormula(Sheet sheet, RowCellIndex rci, List<String> datas, String dictSheetName) {  Row row = sheet.createRow(rci.getRowIndex());  row.setHeightInPoints (20);// 行高  rci.setCellIndex(0);  int startRow = rci.getRowIndex();  int startCell = rci.getCellIndex();  for (String dict : datas) {   row.createCell(rci.getCellIndex()).setCellValue(new HSSFRichTextString(dict));   rci.incrementCellIndexAndGet();  }  rci.reduceCellIndexAndGet();  int endRow = rci.getRowIndex();  int endCell = rci.getCellIndex();  String startName = new CellReference(dictSheetName, startRow, startCell, true, true).formatAsString();  String endName = new CellReference(endRow, endCell, true, true).formatAsString();  String refersToFormula = startName + ":" + endName;  rci.incrementRowIndexAndGet();  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  * @param startRow 数据开始存放的行  * @param startCell 数据开始存放的列  * @param targetSheetName  * @return  */ private static Sheet createExportSheet(List<ExportDefinition> edList, Workbook wb, int startRow, int startCell, String targetSheetName) {  //获取需要加入联动的sheet  Sheet sheet = wb.getSheet(targetSheetName);  if (sheet == null) {   sheet = wb.createSheet(targetSheetName);  }  //初始化位置  RowCellIndex rci = new RowCellIndex(startRow, startCell);  Row row = sheet.getRow(rci.getRowIndex());  if (row == null) {   //创建行   row = sheet.createRow(rci.getRowIndex());   row.setHeightInPoints (20);// 行高  }  CellReference cr = null;  Cell cell = null;  for (ExportDefinition ed : edList) {   //从顶格开始   //创建单元格   cell = row.getCell(rci.getCellIndex());   if(cell == null){    cell = row.createCell(rci.getCellIndex());   }   if(ed.getTitle() != null && ed.getTitle().trim() != ""){    cell.setCellValue(new HSSFRichTextString(ed.getTitle()));   }   ed.setRowIndex(rci.getRowIndex());   ed.setCellIndex(rci.getCellIndex());   //将单元格坐标映射到excel坐标   cr = new CellReference(ed.getRowIndex() + 1, ed.getCellIndex(), true, true);   ed.setPoint(cr.formatAsString());   rci.incrementCellIndexAndGet();  }  return sheet; } /**  * 创建字典sheet并绑定数据下拉  * @param edList  * @param startRow  * @param startCell  * @param targetSheetName  * @param dictSheetName  * @param dic  */ public static Workbook createData(List<ExportDefinition> edList, int startRow, int startCell, String targetSheetName, String dictSheetName, Map<String, Object> dic){  // 2.生成导出模板  Workbook wb = new HSSFWorkbook();  // 2.生成导出模板  Sheet sheet = createExportSheet(edList, wb, startRow, startCell,targetSheetName);  // 3.创建数据字典sheet页  createDictSheet(edList, wb, dictSheetName, dic);  // 4.设置数据有效性  setDataValidation(edList, sheet);  return wb; }}

 

五. 测试

@Controller@RequestMapping("poi")public class PoiController { @RequestMapping("create") public void create(){  try {   // 1.准备需要生成excel模板的数据   List<ExportDefinition> edList = new ArrayList<>();   edList.add(new ExportDefinition("建筑", "Building", "Building", "Room", "Room"));   edList.add(new ExportDefinition("场室", "Room", "Room", "", ""));   Workbook wb = LinkagePoiUtils.createData(edList,0,0,"Sheet1", "dict_data", DictData.dict);   // 5.保存excel到本地   OutputStream os = new FileOutputStream("f:/4.xls");   wb.write(os);   System.out.println("模板生成成功.");  }  catch (Exception e){   System.out.println(e.getMessage());  } }}

结果:

  poi导出联动下拉选择的excel

第一行数据是横着排列的, 是父级数据

下面的数据, 都是竖着排列的, 是各父级数据的子级数据集合.

poi导出联动下拉选择的excel

在下拉中, 出现了与之匹配的下拉选择数据.

此时还有一个问题, 当父级数据改变时, 子级数据不会清空, 这时候, 需要配合excel文件来实现这个功能.

具体的方法, 是, 先准备一个模板, 在模板中, 预先加入vba代码, 然后后台读取这个文件, 操作之后, 保存或者输出到客户端.

在Sheet上面, 点击右键, 然后点击查看代码, 将下面这段代码复制进去, 就可以实现这个联动清除的功能

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 1 And Target.Row > 1 Then  Target.Offset(0, 1).ClearContents End IfEnd Sub

这里的Target就是操作的单元格, 可以使用 MsgBox Target.value 查看到单元格的值, 判断vba是否起作用. 因为有些杀毒软件, 会将excel中的宏屏蔽掉

Target.Offset - 这个是偏移量, 对当前操作的单元格进行一个偏移, 得到你想操作的单元格.

 

这里的联动, 只能适用于二级联动, 不能支持三级联动.  如果要三级联动, 需要修改其中的部分代码. 

 

原标题:poi导出联动下拉选择的excel

关键词:excel

*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。

可能感兴趣文章

我的浏览记录