java操作excel给单元格加下拉列表（转）

转自：

https://www.cnblogs.com/person008/p/9395531.html

import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  /**
   * 思路：
   * 1.工作簿
   * 2.下拉框位置
   * 3.下拉框数据
   * @param filePath
   * @throws Exception
   */

public class ExcelTest {
    public static void main(String[] args) {
        try 
        {
            dropDownList42007("E:\\test.xlsx");
        } 
        catch (Exception e) {

            e.printStackTrace();
        }
    }


    

    public static void dropDownList42007(String filePath)
            throws Exception {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("test");
        String [] subjects = new String []{"JAVA","C++","JS"};
        DataValidationHelper helper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = helper.createExplicitListConstraint(subjects);
        CellRangeAddressList addressList = null;
        DataValidation dataValidation = null;
        for (int i = 0; i < 100; i++) {
            addressList = new CellRangeAddressList(i, i, 0, 0);
            dataValidation = helper.createValidation(constraint, addressList);
            sheet.addValidationData(dataValidation);
        }
        FileOutputStream stream = new FileOutputStream(filePath);
        workbook.write(stream);
        stream.close();
        addressList = null;
        dataValidation = null;
    }
}

相关依赖pom

  <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>


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

 工具类

public class Test {
    public static void main(String[] args) {
        try {
            String[] titles = new String[]{"name", "age", "address"};
            String[] data1 = {"name1", "age1", "address1"};
            String[] data2 = {"name2", "age2", "address2"};
            String[] data3 = {"name3", "age3", "address3"};
            ArrayList<String[]> dataList = Lists.newArrayList(data1, data2, data3);
            download("C:\\Users\\G007112\\Desktop\\temp3.xlsx", titles, dataList);
        } catch (Exception e) {

            e.printStackTrace();
        }
    }


    public static void download(String filePath, String[] titles, List<String[]> dataList)
            throws Exception {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("sheet1");
        addData(sheet,titles, dataList);

        String[] enums = new String[]{"JAVA", "C++", "JS"};
        String[] enums2 = new String[]{"JAVA1", "C#", "Python"};
        addValidation(sheet,enums,0);
        addValidation(sheet,enums2,2);


        FileOutputStream stream = new FileOutputStream(filePath);
        workbook.write(stream);
        stream.close();

    }

    private static void addData( XSSFSheet sheet,String[] titles, List<String[]> dataList) {
        XSSFRow firstRow = sheet.createRow(0);
        XSSFCell[] cells = new XSSFCell[titles.length];

        //循环设置表头信息
        for (int i = 0; i < titles.length; i++) {
            cells[0] = firstRow.createCell(i);
            cells[0].setCellValue(titles[i]);
        }

        for (int i = 0; i < dataList.size(); i++) {
            XSSFRow row = sheet.createRow(i + 1);
            String[] data = dataList.get(i);
            for (int j = 0; j < data.length; j++) {
                XSSFCell cell = row.createCell(j); //第一列
                cell.setCellValue(data[j]);
            }
        }
    }

    private static void addValidation(XSSFSheet sheet,String[] enums,int colIndex) {
        DataValidationHelper helper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = helper.createExplicitListConstraint(enums);
        CellRangeAddressList addressList = null;
        DataValidation dataValidation = null;
        //指定校验某一列
        for (int i = 1; i < 100; i++) {
            addressList = new CellRangeAddressList(i, i, colIndex, colIndex);
            dataValidation = helper.createValidation(constraint, addressList);
            sheet.addValidationData(dataValidation);
        }
    }
}

 除了可以使用枚举的下拉列表对单元格内容做限定外，POI还支持对整数、浮点数、日期、时间、唯一性等做验证，功能很强大。