java excel多选_POI生成可多选下拉框excel

Poi生成可多选下拉框excel步骤：

先准备一个带有VB语言实现下拉菜单多选功能的Excel模板

a数据-->数据有效性-->数据有效性，“允许”选择“序列”，然后把需要选择的内容输入到来源里，中间用英文逗号“,”隔开。下拉框单选便成了。

b 在下拉表所打开的sheet中(如sheet1)，鼠标右击下面的工作表。选择“查看代码”，就可打开VBA编辑界面。复制答案最后面的代码，并将其中一行的If Target.Column = 3 Then中的3修改为下来数据表所在的列数

代码看附录。

用java POI

读取这个模板文件，写入下拉数据，代码如下：

Workbook workbook = null;

try {

workbook = WorkbookFactory.create(new

File("C:\\Users\\LQS\\Desktop/test.xlsm")); //读取模板Excel文件，

} catch (IOException e) {

e.printStackTrace();

} catch (InvalidFormatException e) {

e.printStackTrace();

}

//创建一个标签页

Sheet sheet = workbook.getSheetAt(0);//获取sheet

DataValidationHelper dvHelper =

sheet.getDataValidationHelper();

XSSFDataValidationConstraint dvConstraint =

(XSSFDataValidationConstraint) dvHelper

.createExplicitListConstraint(strs);//strs下拉菜单的数据数组例如:String[]

strs = {"A","B","C"}

addressList = new CellRangeAddressList(1,endRow, i,

i);// 第一个参数是开始行，第二个是结束行，第三个是开始列，第四个是结束列

validation = dvHelper.createValidation(dvConstraint,

addressList);

//设置只能选下拉菜单里的值不能随便输入

validation.setSuppressDropDownArrow(true);

validation.setShowErrorBox(true);sheet.addValidationData(validation);

附录：

Private Sub Worksheet_Change(ByVal Target As

Range)

' Developed by Contextures Inc.

'

Dim rngDV As Range

Dim oldVal As String

Dim newVal As String

If Target.Count > 1 Then GoTo

exitHandler

On Error Resume Next

Set rngDV =

Cells.SpecialCells(xlCellTypeAllValidation)

On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo

exitHandler

If Intersect(Target, rngDV) Is Nothing

Then

'do

nothing

Else

Application.EnableEvents =

False

newVal =

Target.Value

Application.Undo

oldVal =

Target.Value

Target.Value =

newVal

If Target.Column = 1

Then

If

oldVal = "" Then

'do nothing

Else

If newVal = "" Then

'do nothing

Else

Target.Value = oldVal _

& ", " & newVal

'NOTE: you can use a line break,

'instead of a comma

'Target.Value = oldVal _

'& Chr(10) & newVal

End If

End

If

End If

End If

exitHandler:

Application.EnableEvents =

True

End Sub