java poi导出excel设置下拉选择框

anningkang 2021-08-26 原文

基本的过程就是设置一个下拉选择框的校验，然后绑定到sheet的指定格子上。XSS和HSS的方式有些许不同，但是原理是一样的。代码示例如下：

        //性别
        String[] datas = new String[]{"男","女"};
        if(excelType.equals(ExcelTypeEnum.XLSX)){
            //数据验证帮助程序
            XSSFDataValidationHelper dvHelper2 = new XSSFDataValidationHelper((XSSFSheet) sheet);
            XSSFDataValidationConstraint dvConstraint2 = (XSSFDataValidationConstraint) dvHelper2.createExplicitListConstraint(datas);
            CellRangeAddressList regions2 =new CellRangeAddressList(1, 5000, 2, 2);
            XSSFDataValidation dataValidation2 =  (XSSFDataValidation) dvHelper2.createValidation( dvConstraint2, regions2);
            sheet.addValidationData(dataValidation2);
        }else{
            //性别
            // 设置第5列的2-5000行为下拉列表
            CellRangeAddressList regions2 = new CellRangeAddressList(1, 5000, 2, 2);
            // 创建下拉列表数据
            DVConstraint constraint2 = DVConstraint.createExplicitListConstraint(datas);
            // 绑定
            HSSFDataValidation dataValidation2 = new HSSFDataValidation(regions2, constraint2);
            sheet.addValidationData(dataValidation2);
        }

————

完整的一个示例代码如下。导入包后可以直接运行实验。

package com.insigma;
import java.io.FileOutputStream;

import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelTest {
    public static void main(String[] args) {
        try{
            dropDownList42007("D:\\test.xlsx");
        }catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static void dropDownList42007(String filePath)  throws Exception {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("下拉列表测试");
        String[] datas = new String[] {"男","女"};
        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
        XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(datas);
        CellRangeAddressList addressList =  new CellRangeAddressList(0, 100, 0, 0);
        XSSFDataValidation validation  = (XSSFDataValidation) dvHelper.createValidation( dvConstraint, addressList);
        sheet.addValidationData(validation);
        FileOutputStream stream = new FileOutputStream(filePath);
        workbook.write(stream);
        stream.close();
    }
}