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); } }
数据结构:
二. 单元格坐标类
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()); } } }
结果:
第一行数据是横着排列的, 是父级数据
下面的数据, 都是竖着排列的, 是各父级数据的子级数据集合.
在下拉中, 出现了与之匹配的下拉选择数据.
此时还有一个问题, 当父级数据改变时, 子级数据不会清空, 这时候, 需要配合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 If End Sub
这里的Target就是操作的单元格, 可以使用 MsgBox Target.value 查看到单元格的值, 判断vba是否起作用. 因为有些杀毒软件, 会将excel中的宏屏蔽掉
Target.Offset - 这个是偏移量, 对当前操作的单元格进行一个偏移, 得到你想操作的单元格.
这里的联动, 只能适用于二级联动, 不能支持三级联动. 如果要三级联动, 需要修改其中的部分代码.
