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

转⾃：

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:\\\est.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\

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

org.apache.poi poi 3.17

org.apache.poi poi-ooxml 3.17

⼯具类

public class Test {

public static void main(String[] args) { try {

String[] titles = new String[]{\"name\ String[] data1 = {\"name1\ String[] data2 = {\"name2\ String[] data3 = {\"name3\

ArrayList dataList = Lists.newArrayList(data1, data2, data3); download(\"C:\\\\Users\\\\G007112\\\\Desktop\\\emp3.xlsx\ } catch (Exception e) { e.printStackTrace(); } }

public static void download(String filePath, String[] titles, List dataList) throws Exception {

XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet(\"sheet1\"); addData(sheet,titles, dataList);

String[] enums = new String[]{\"JAVA\

String[] enums2 = new String[]{\"JAVA1\ 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 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还⽀持对整数、浮点数、⽇期、时间、唯⼀性等做验证，功能很强⼤。