Java设置Excel下拉列表

Excel在office2007之前和之后的格式不一样，07之前的后缀是.xls，07及07之后.xlsx,针对不同的格式，apache提供了不同的类来操作：HSSF来操作07之前的，XSSF来操作07及07之后。

在pom.xml中要对包进行依赖，一定要两个包都要引入，因为这两个包有依赖关系：

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.0.1</version>
        </dependency>

对07之前版本的Excel，操作如下：

        //textlists存放列表中要展示的字符串
        String[] textlists = {"doudou1", "doudou2", "doudou3"};
        //生成一个工作簿对象
        HSSFWorkbook workbook = new HSSFWorkbook();
        //生成一个名称为Info的表单
        HSSFSheet sheet = workbook.createSheet("Info");

        //设置下拉列表作用的单元格(firstrow, lastrow, firstcol, lastcol)
        CellRangeAddressList regions = new CellRangeAddressList(1, 100, 0, 0);
        //生成并设置数据有效性验证
        DVConstraint constraint = DVConstraint.createExplicitListConstraint(textlists);
        HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);
        //将有效性验证添加到表单
        sheet.addValidationData(data_validation_list);

        try
        {
            OutputStream os = new FileOutputStream("D:\\doudou.xls");
            workbook.write(os);   //将工作簿内容输出到Excel文件中
            os.close();
            workbook.close();

        } catch (Exception e)
        {
            e.printStackTrace();
        }

对Excel07及之后的文件，使用的类不同，思想类似：

            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 = null;
            XSSFDataValidation validation = null;

            addressList = new CellRangeAddressList(1,100,0,0);
            validation = (XSSFDataValidation)dvHelper.createValidation(dvConstraint, addressList);

            //这两行设置单元格只能是列表中的内容，否则报错
            validation.setSuppressDropDownArrow(true);
            validation.setShowErrorBox(true);   

            sheet.addValidationData(validation);

            try
            {
                OutputStream os = new FileOutputStream("D:\\dou.xlsx");
                workbook.write(os);
                os.close();
                workbook.close();
            } catch (Exception e)
            {
                e.printStackTrace();
            }
            

对以上demo做以记录，便于回顾