import com.google.common.collect.Lists; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddressList; import org.apache.poi.util.IOUtils; import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.FileOutputStream; import java.util.*; /** * program: jbootops * description: ${description} * author: zxn * create: 2020-01-18 18:19 **/ public class MutilTwoExcel { public static void main(String[] args) { // 查询所有的省名称 List<String> provNameList = new ArrayList<String>(); provNameList.add("安徽省"); provNameList.add("浙江省"); // 整理数据,放入一个Map中,mapkey存放父地点,value 存放该地点下的子区域 Map<String, List<String>> siteMap = new HashMap<String, List<String>>(); siteMap.put("浙江省", Lists.newArrayList("杭州市", "宁波市")); siteMap.put("安徽省", Lists.newArrayList("芜湖市", "滁州市")); // 创建一个excel Workbook book = new XSSFWorkbook(); // 创建需要用户填写的数据页 // 设计表头 Sheet sheet1 = book.createSheet("sheet1"); Row row0 = sheet1.createRow(0); row0.createCell(0).setCellValue("省"); row0.createCell(1).setCellValue("市"); //创建一个专门用来存放地区信息的隐藏sheet页 //因此也不能在现实页之前创建,否则无法隐藏。 Sheet hideSheet = book.createSheet("site"); book.setSheetHidden(book.getSheetIndex(hideSheet), true); int rowId = 0; // 设置第一行,存省的信息 Row proviRow = hideSheet.createRow(rowId++); proviRow.createCell(0).setCellValue("省列表"); for (int i = 0; i < provNameList.size(); i ++) { Cell proviCell = proviRow.createCell(i + 1); proviCell.setCellValue(provNameList.get(i)); } // 将具体的数据写入到每一行中,行开头为父级区域,后面是子区域。 Iterator<String> keyIterator = siteMap.keySet().iterator(); while(keyIterator.hasNext()){ String key = keyIterator.next(); List<String> son = siteMap.get(key); Row row = hideSheet.createRow(rowId++); row.createCell(0).setCellValue(key); for(int i = 0; i < son.size(); i ++){ Cell cell = row.createCell(i + 1); cell.setCellValue(son.get(i)); } // 添加名称管理器 String range = getRange(1, rowId, son.size()); Name name = book.createName(); name.setNameName(key); String formula = "site!" + range; name.setRefersToFormula(formula); } XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)sheet1); // 省规则 DataValidationConstraint provConstraint = dvHelper.createExplicitListConstraint(provNameList.toArray(new String[]{})); CellRangeAddressList provRangeAddressList = new CellRangeAddressList(1, 1024, 0, 0); DataValidation provinceDataValidation = dvHelper.createValidation(provConstraint, provRangeAddressList); provinceDataValidation.createErrorBox("error", "请选择正确的省份"); provinceDataValidation.setShowErrorBox(true); provinceDataValidation.setSuppressDropDownArrow(true); sheet1.addValidationData(provinceDataValidation); // 市以规则,此处仅作一个示例 // "INDIRECT($A$" + 2 + ")" 表示规则数据会从名称管理器中获取key与单元格 A2 值相同的数据,如果A2是浙江省,那么此处就是 // 浙江省下的区域信息。 DataValidationConstraint formula = dvHelper.createFormulaListConstraint("INDIRECT($A" + 2 + ")"); CellRangeAddressList rangeAddressList = new CellRangeAddressList(1,1024,1,1); DataValidation cacse = dvHelper.createValidation(formula, rangeAddressList); cacse.createErrorBox("error", "请选择正确的市"); sheet1.addValidationData(cacse); FileOutputStream os = null; try { os = new FileOutputStream("D:/testCascadeTwo2007.xlsx"); book.write(os); } catch (Exception e) { e.printStackTrace(); } finally { IOUtils.closeQuietly(os); } } public static String getRange(int offset, int rowId, int colCount) { char start = (char)('A' + offset); if (colCount <= 25) { char end = (char)(start + colCount - 1); return "$" + start + "$" + rowId + ":$" + end + "$" + rowId; } else { char endPrefix = 'A'; char endSuffix = 'A'; if ((colCount - 25) / 26 == 0 || colCount == 51) {// 26-51之间,包括边界(仅两次字母表计算) if ((colCount - 25) % 26 == 0) {// 边界值 endSuffix = (char)('A' + 25); } else { endSuffix = (char)('A' + (colCount - 25) % 26 - 1); } } else {// 51以上 if ((colCount - 25) % 26 == 0) { endSuffix = (char)('A' + 25); endPrefix = (char)(endPrefix + (colCount - 25) / 26 - 1); } else { endSuffix = (char)('A' + (colCount - 25) % 26 - 1); endPrefix = (char)(endPrefix + (colCount - 25) / 26); } } return "$" + start + "$" + rowId + ":$" + endPrefix + endSuffix + "$" + rowId; } } }
excel 联动下拉选择
最新推荐文章于 2023-03-13 18:31:51 发布