5 apache poi实现excel的动态下拉框功能

excel下拉框

@RequestMapping("xiala")public void xiala(HttpServletResponse response){String fileName = "僵尸表";try{response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");response.setHeader("Content-Disposition","attachment;filename=" +URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20") + ".xlsx");Workbook workbook = new XSSFWorkbook();Sheet sheet = workbook.createSheet("Sheet1");String[] dropdownOptions = {"Option 1", "Option 2", "Option 3"};CellRangeAddressList addressList = new CellRangeAddressList(1, 100, 0, 0); // A1单元格。第二行到第100行记录数// 创建数据验证助手DataValidationHelper validationHelper = sheet.getDataValidationHelper();// 创建数据验证约束DataValidationConstraint constraint = validationHelper.createExplicitListConstraint(dropdownOptions);// 创建数据验证对象DataValidation dataValidation = validationHelper.createValidation(constraint, addressList);// 将数据验证添加到表格中sheet.addValidationData(dataValidation);//处罚来源String[] publishType = {"个人", "企业", "33"};CellRangeAddressList publishTypeList = new CellRangeAddressList(1, 100, 1, 1);//A2单元格 处罚来源，第二行到第100行记录数DataValidationConstraint publishTypeConstraint = validationHelper.createExplicitListConstraint(publishType);DataValidation publishValidation = validationHelper.createValidation(publishTypeConstraint, publishTypeList);sheet.addValidationData(publishValidation);OutputStream os = new BufferedOutputStream(response.getOutputStream());workbook.write(os);os.flush();os.close();} catch (IOException e) {e.printStackTrace();} finally {}System.out.println("Excel文件创建成功，包含下拉框！");}

excel sheet的复制

@RequestMapping("list")public void list(HttpServletResponse response) throws Exception{System.out.println("------ 开始下载模板 ------");//获取要下载的模板名称String fileName = URLEncoder.encode("名单导入模板","UTF-8");response.setHeader("Content-Disposition","attachment;filename=" + fileName + ".xlsx");response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");//source sheetInputStream inputStream = Thread.currentThread().getContextClassLoader().getResourceAsStream("template/template.xlsx");Workbook sourceWorkbook = new XSSFWorkbook(inputStream);Workbook destWorkbook = new XSSFWorkbook();// 获取源工作簿中的第一个工作表Sheet sourceSheet = sourceWorkbook.getSheetAt(0);// 在目标工作簿中创建一个新的工作表Sheet destSheet = destWorkbook.createSheet(sourceSheet.getSheetName());// 复制每一行for (int rowIndex = 0; rowIndex <= sourceSheet.getLastRowNum(); rowIndex++) {Row sourceRow = sourceSheet.getRow(rowIndex);Row destRow = destSheet.createRow(rowIndex);if (sourceRow != null) {// 复制每一列for (int colIndex = 0; colIndex < sourceRow.getLastCellNum(); colIndex++) {Cell sourceCell = sourceRow.getCell(colIndex);Cell destCell = destRow.createCell(colIndex);if (sourceCell != null) {// 复制单元格的样式CellStyle newCellStyle = destWorkbook.createCellStyle();newCellStyle.cloneStyleFrom(sourceCell.getCellStyle());destCell.setCellStyle(newCellStyle);// 复制单元格的值switch (sourceCell.getCellType()) {case STRING:destCell.setCellValue(sourceCell.getStringCellValue());break;case NUMERIC:destCell.setCellValue(sourceCell.getNumericCellValue());break;case BOOLEAN:destCell.setCellValue(sourceCell.getBooleanCellValue());break;case FORMULA:destCell.setCellFormula(sourceCell.getCellFormula());break;case BLANK:destCell.setBlank();break;default:break;}}}}}OutputStream os = new BufferedOutputStream(response.getOutputStream());destWorkbook.write(os);os.flush();os.close();}

excel列之间的级联