public void exportExcel(List<List<String>> list,HttpServletResponse response,String fileName) throws Exception { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheetCs = wb.createSheet("sheet1"); //为首行赋值 for(int i=0;i<list.size();i++){ XSSFRow row = sheetCs.createRow(i); for(int j=0;j<list.get(i).size();j++){ sheetCs.setColumnWidth(j, 3500);//设置列宽 XSSFCell cell = row.createCell(j); cell.setCellValue(list.get(i).get(j)); } } int sheetTotal=wb.getNumberOfSheets(); XSSFRow row; //第一个下拉选 //新建一个sheet页 String hiddenSheetName = "hiddenSheet" + sheetTotal; XSSFSheet hiddenSheet = wb.createSheet(hiddenSheetName); //数据 String[] selectList =new String[100]; //对应列下拉框数据 for(int i=0;i<100;i++){ selectList[i]="测试数据"+i; } //写入下拉数据到新的sheet页中 for (int i = 0; i < selectList.length; i++) { row = hiddenSheet.createRow(i); XSSFCell cell = row.createCell(0); cell.setCellValue(selectList[i]); } //获取新sheet页内容 String strFormula = hiddenSheetName + "!$A$1:$A$65535"; XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST,strFormula); // 设置数据有效性加载在哪个单元格上,四个参数分别是:起始行、终止行、起始列、终止列 CellRangeAddressList regions = new CellRangeAddressList(1,65535, 0, 0); // 数据有效性对象 DataValidationHelper help = new XSSFDataValidationHelper((XSSFSheet) sheetCs); DataValidation validation = help.createValidation(constraint, regions); sheetCs.addValidationData(validation); //将新建的sheet页隐藏掉 wb.setSheetHidden(sheetTotal, true); sheetTotal++; //第二个下拉选 hiddenSheetName = "hiddenSheet" + sheetTotal; XSSFSheet hiddenSheet2 = wb.createSheet(hiddenSheetName); String[] selectList2 =new String[100]; //对应列下拉框数据 for(int i=0;i<100;i++){ selectList2[i]="测试数据2"+i; } for (int i = 0; i < selectList2.length; i++) { row = hiddenSheet2.createRow(i); XSSFCell cell = row.createCell(0); cell.setCellValue(selectList2[i]); } String strFormula2 = hiddenSheetName + "!$A$1:$A$65535"; XSSFDataValidationConstraint constraint2 = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST,strFormula2); CellRangeAddressList regions2 = new CellRangeAddressList(1,65535, 1, 1); DataValidationHelper help2 = new XSSFDataValidationHelper((XSSFSheet) sheetCs); DataValidation validation2 = help2.createValidation(constraint2, regions2); sheetCs.addValidationData(validation2); wb.setSheetHidden(sheetTotal, true); sheetTotal++; try { OutputStream output = response.getOutputStream(); fileName = URLEncoder.encode(fileName,"UTF-8"); response.addHeader("Content-Disposition", "inline;filename="+fileName+".xls"); response.setContentType("application/msexcel"); wb.write(output); output.flush(); output.close(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } // 结束 System.out.println("Over"); }
正文
poi导出excel 下拉列表值超过255问题
这篇文章最后更新于1819天前,您需要注意相关的内容是否还可用,如有疑问请联系作者!
文章版权声明:除非注明,否则均为华仔部落原创文章,转载或复制请以超链接形式并注明出处。
还没有评论,来说两句吧...