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

本文介绍如何使用Java程序为Excel工作表创建下拉列表选择项,包括2003版和2007版的不同实现方式。通过隐藏的工作表提供数据源,并利用公式约束设置动态数据验证。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

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;
  }
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值