> 文档中心 > EasyPoi导出Excel带下拉框,值超过255报错问题

EasyPoi导出Excel带下拉框,值超过255报错问题


问题描述:

通过EasyPoi导出Excel带下拉框字段时,下拉框内值超过255时,会报错

String literals in formulas can't be bigger than 255 characters ASCII


解决方案:

额外创建sheet页去存储下拉框内数据,然后从这个sheet页中读取下拉框数据存到下拉列表中,最后需将额外创建的sheet隐藏。

public void exportXlsByT(HttpServletRequest request, HttpServletResponse response) throws IOException {Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("表名", "导出人" , "depart"), list, new ArrayList());selectList("status", 1, workbook, 0, 0, new String[]{"审批中-3","审批完成-2","未提交-1","终止-0"});selectList("synchronize", 2, workbook, 1, 1, new String[]{"是-1","否-0"});downLoadExcel(sb.append(fileName).toString(), workbook, request, response);}

 如果有多列下拉框,我是每个下拉框创建一个sheet页并隐藏,index参数是sheet页下标,firstCol和lastCol为设置下拉框的起始列及结束列。

public void selectList(String sheetName, Integer index, Workbook workbook, int firstCol, int lastCol, String[] strings ){//将下拉框数据放到新的sheet里,然后excle通过新的sheet数据加载下拉框数据Sheet hidden = workbook.createSheet(sheetName);//创建单元格对象Cell cell =null;//遍历我们上面的数组,将数据取出来放到新sheet的单元格中for (int i = 0, length = strings.length; i  (起始行,结束行,起始列,结束列)CellRangeAddressList regions = new CellRangeAddressList(0, 65535, firstCol, lastCol);// 将设置下拉选的位置和数据的对应关系 绑定到一起DataValidation dataValidation = new HSSFDataValidation(regions, constraint);//将第二个sheet设置为隐藏workbook.setSheetHidden(index, true);//将数据赋给下拉列表workbook.getSheetAt(0).addValidationData(dataValidation);}
public void downLoadExcel(String fileName, Workbook workbook, HttpServletRequest request, HttpServletResponse response) throws IOException {OutputStream output = null;BufferedOutputStream bufferedOutPut = null;try {// 重置响应对象  这里可能会导致前后端跨域  //response.reset();// 当前日期,用于导出文件名称SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");String dateStr = fileName + "-" + sdf.format(new Date());String UserAgent = request.getHeader("USER-AGENT").toLowerCase();// 指定下载的文件名--设置响应头if (UserAgent.indexOf("firefox") >= 0) {response.setHeader("content-disposition", "attachment;filename=\"" + new String(dateStr.getBytes("UTF-8"), "iso-8859-1") +".xls\"");}else {response.setHeader("Content-Disposition","attachment;filename=" + URLEncoder.encode(dateStr, "UTF-8")+".xls");}response.setContentType("application/vnd.ms-excel;charset=UTF-8");// 编码response.setCharacterEncoding("UTF-8");output = response.getOutputStream();bufferedOutPut = new BufferedOutputStream(output);workbook.write(bufferedOutPut);bufferedOutPut.flush();} catch (Exception e) {} finally {if (bufferedOutPut != null) {bufferedOutPut.close();}if (output != null) {output.close();}}}