public void officeExcelOut(OutputStream out,List<Partner> l,String level) { try { hwb = new HSSFWorkbook(); hwb = employeeDown(l,level); hwb.write(out); out.flush(); out.close(); } catch (IOException e) { e.printStackTrace(); } } private static final String[] titles = {"所属企业","登录代码", "用户密码","姓名", "手机(文本格式)","邮箱","等级"}; public HSSFWorkbook employeeDown(List<Partner> l,String level) {// EXcle生成 HSSFWorkbook hwb = new HSSFWorkbook(); HSSFSheet sheet = hwb.createSheet("模板"); String hiddenSheet = null; String[] strs = null; // 用于下拉的数组 HSSFRow firstrow = sheet.createRow(0); int countColumnNum = titles.length; for (int i = 0; i < countColumnNum; i++) { HSSFCell firstcell = firstrow.createCell(i); HSSFCellStyle style = hwb.createCellStyle(); //设置CELL格式为文本格式 HSSFDataFormat format = hwb.createDataFormat(); style.setDataFormat(format.getFormat("@")); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); style.setWrapText(true); firstcell.setCellStyle(style); firstcell.setCellType(HSSFCell.CELL_TYPE_STRING); sheet.setColumnWidth(i, 6000); sheet.setDefaultColumnStyle(i, style); firstcell.setCellValue(new HSSFRichTextString(titles[i])); } hiddenSheet = "category1Hidden"; HSSFSheet category1Hidden = hwb.createSheet(hiddenSheet); // 创建隐藏域 List<String> list = new ArrayList<String>(); System.out.println("l____传值____"+l); for (int i = 0; i <l.size(); i++) { list.add(l.get(i).getPartnerCode() + "-" + l.get(i).getPartnerName()); } String[] array= new String[list.size()]; strs= list.toArray(array); for (int j = 0; j <strs.length; j++) { category1Hidden.createRow(j+1000).createCell(0).setCellValue(strs[j]); } Name category1Name = hwb.createName(); category1Name.setNameName(hiddenSheet); category1Name.setRefersToFormula(hiddenSheet + "!A1:A" + (strs.length+1000)); // A1:A代表隐藏域创建第?列createCell(?)时。以A1列开始A行数据获取下拉数组 System.out.println("********************************"); System.out.println("hiddenSheet____下拉框的值_____"+hiddenSheet); System.out.println("********************************"); DVConstraint constraint = DVConstraint.createFormulaListConstraint(hiddenSheet); CellRangeAddressList addressList = new CellRangeAddressList(1, 1000, 0, 0); HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint); hwb.setSheetHidden(1, true); // 1隐藏、0显示 sheet.addValidationData(validation);//下拉框数值较多,使用隐藏,防止这里验证过不去 // excel 等级 下拉选项 List<String> list1 = new ArrayList<String>(); System.out.println("level____传值____"+level); if ("0".equals(level) || "".equals(level)) { list1.add("1-一级"); list1.add("2-二级"); list1.add("3-三级"); } else if ("1".equals(level)) { list1.add("2-二级"); list1.add("3-三级"); } else if ("2".equals(level)) { list1.add("3-三级"); } System.out.println("********************************"); System.out.println("list1_____下拉框的值_____"+list1); System.out.println("********************************"); String[] array1 = new String[list1.size()]; list1.toArray(array1); DVConstraint constraint1 = DVConstraint.createFormulaListConstraint(hiddenSheet); CellRangeAddressList addressList1 = new CellRangeAddressList(1, 1000, 6, 6) HSSFDataValidation validation1 = new HSSFDataValidation(addressList1, constraint1); sheet.addValidationData(validation1);//下拉框数值较少 return hwb; }
poi 表单下载下拉框,多条问题解决
最新推荐文章于 2024-09-02 08:30:58 发布