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 < length; i++){
			//取出数组中的每个元素
			String name = strings[i];
			//根据i创建相应的行对象（说明我们将会把每个元素单独放一行）
			Row row = hidden.createRow(i);
			//创建每一行中的第一个单元格
			cell = row.createCell(0);
			//然后将数组中的元素赋值给这个单元格
			cell.setCellValue(name);
		}
		// 创建名称，可被其他单元格引用
		Name namedCell = workbook.createName();
		namedCell.setNameName(sheetName);
		// 设置名称引用的公式
		namedCell.setRefersToFormula(sheetName+"!$A$1:$A$" + strings.length);
		//加载数据,将名称为hidden的sheet中的数据转换为List形式
		DVConstraint constraint = DVConstraint.createFormulaListConstraint(sheetName);

		// 设置第一列的3-65534行为下拉列表
		// (3, 65534, 2, 2) ====> (起始行,结束行,起始列,结束列)
		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();
			}
		}
	}