java excel 多选下拉列表设置

java excel 多选下拉列表设置

小编教你java excel 多选下拉列表设置

1、需要用到的jar包：

poi-3.10-FINAL.jar

poi-ooxml-3.10-FINAL.jar

poi-ooxml-schemas-3.10-FINAL.jar

gradle引入：

compile group: 'org.apache.poi', name: 'poi', version: '3.10'compile group: 'org.apache.poi', name: 'poi-ooxml', version: '3.10-FINAL'

public static HSSFWorkbook write(InputStream inputStream) throws IOException, ClassNotFoundException

{ // 初始一个workbook

HSSFWorkbook workbook = new HSSFWorkbook(inputStream);

// 创建一个sheet HSSFSheet sheet = workbook.getSheetAt(0);

// 准备下拉列表数据 String[] strs = new String[] { "刘德华", "张学友", "黎明", "郭富城" };

// 设置第一列的1-10行为下拉列表 CellRangeAddressList regions = new CellRangeAddressList(0, 9, 0, 0);

// 创建下拉列表数据 DVConstraint constraint = DVConstraint.createExplicitListConstraint(strs);

// 绑定 HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);

sheet.addValidationData(dataValidation);

return workbook;

}

方案二

方案二的思路是在excel中添加一个隐藏的sheet存放数据源（下拉的数据），在真正需要下拉的sheet单元格写入属性。

1.需要使用到的util类

package com.excel.select;import java.io.InputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;

import org.apache.poi.hssf.usermodel.HSSFClientAnchor;

import org.apache.poi.hssf.usermodel.HSSFFont;

import org.apache.poi.hssf.usermodel.HSSFRichTextString;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.hssf.util.HSSFColor;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.CellStyle;

import org.apache.poi.ss.usermodel.Comment;import org.apache.poi.ss.usermodel.Drawing;

import org.apache.poi.ss.usermodel.Font;import org.apache.poi.ss.usermodel.RichTextString;

import org.apache.poi.ss.usermodel.Workbook;

2.Test类

package com.excel.select;

import java.io.FileOutputStream;

import org.apache.poi.hssf.usermodel.DVConstraint;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFDataFormat;

import org.apache.poi.hssf.usermodel.HSSFDataValidation;

import org.apache.poi.hssf.usermodel.HSSFName;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.hssf.util.CellRangeAddressList;

import org.apache.poi.ss.usermodel.CellStyle;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;