poi导出excel用隐藏sheet方式封装下拉框（下拉选项值多的情况）

2003方式

public static void dropDownList2003(String dataSource, String filePath)
      throws Exception {
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet realSheet = workbook.createSheet("下拉列表测试");
    HSSFSheet hidden = workbook.createSheet("hidden");
    //数据源sheet页不显示    workbook.setSheetHidden(1, true);
    String[] datas = dataSource.split("\\,");
    CellStyle style = workbook.createCellStyle();
    style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    HSSFRow row = null;
    HSSFCell cell = null;
    for (int i = 0, length = datas.length; i < length; i++) {
      row = hidden.createRow(i);
      cell = row.createCell(0);
      cell.setCellValue(Integer.valueOf(datas[i]));
    }
    Name namedCell = workbook.createName();
    namedCell.setNameName("hidden");
    namedCell.setRefersToFormula("hidden!$A$1:$A$" + datas.length);
    DVConstraint constraint = DVConstraint
        .createFormulaListConstraint("hidden");
    CellRangeAddressList addressList = null;
    HSSFDataValidation validation = null;
    row = null;
    cell = null;
    for (int i = 0; i < 100; i++) {
      row = realSheet.createRow(i);
      cell = row.createCell(0);
      cell.setCellStyle(style);
      addressList = new CellRangeAddressList(i, i, 0, 0);
      validation = new HSSFDataValidation(addressList, constraint);
      realSheet.addValidationData(validation);
      validation.setShowErrorBox(false);// 取消弹出错误框    }
    FileOutputStream stream = new FileOutputStream(filePath);
    workbook.write(stream);
    stream.close();
    style = null;
    addressList = null;
    validation = null;
  }

2007方式

public static void dropDownList2007(String dataSource, String filePath)
      throws Exception {
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("下拉列表测试");
    XSSFSheet hidden = workbook.createSheet("hidden");
    //数据源sheet页不显示    workbook.setSheetHidden(1, true);
    String[] datas = dataSource.split("\\,");
    CellStyle style = workbook.createCellStyle();
    style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    XSSFRow row = null;
    XSSFCell cell = null;
    for (int i = 0, length = datas.length; i < length; i++) {
      row = hidden.createRow(i);
      cell = row.createCell(0);
      cell.setCellValue(Integer.valueOf(datas[i]));
    }
    Name namedCell = workbook.createName();
    namedCell.setNameName("hidden");
    namedCell.setRefersToFormula("hidden!$A1:$A" + datas.length);
    XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
    XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
        .createFormulaListConstraint("hidden");
    CellRangeAddressList addressList = null;
    XSSFDataValidation validation = null;
    for (int i = 0; i < 100; i++) {
      row = sheet.createRow(i);
      cell = row.createCell(0);
      cell.setCellStyle(style);
      addressList = new CellRangeAddressList(i, i, 0, 0);
      validation = (XSSFDataValidation) dvHelper.createValidation(
          dvConstraint, addressList);
      sheet.addValidationData(validation);
    }
    FileOutputStream stream = new FileOutputStream(filePath);
    workbook.write(stream);
    stream.close();
    addressList = null;
    validation = null;
  }