1、直接添加下拉框,数据量过多会有问题
/**
* 创建下拉列表选项(单元格下拉框数据小于255字节时使用)
*
* @param sheet 所在Sheet页面
* @param values 下拉框的选项值
* @param firstRow 起始行(从0开始)
* @param lastRow 终止行(从0开始)
* @param firstCol 起始列(从0开始)
* @param lastCol 终止列(从0开始)
*/
public void createDropDownList(Sheet sheet, String[] values, int firstRow, int lastRow, int firstCol, int lastCol) {
DataValidationHelper helper = sheet.getDataValidationHelper();
CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
DataValidationConstraint constraint = helper.createExplicitListConstraint(values);
DataValidation dataValidation = helper.createValidation(constraint, addressList);
if (dataValidation instanceof HSSFDataValidation ) {
dataValidation.setSuppressDropDownArrow(false);
} else {
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(true);
}
sheet.addValidationData(dataValidation);
}
2、使用隐藏sheet的方式实现
/**
* 隐藏Sheet方式创建下拉框(单元格下拉框数据大于255字节时使用)
*
* @param sheet 需要添加下拉框的Sheet
* @param firstRow 起始行
* @param firstCol 其实列
* @param endRow 终止行
* @param endCol 终止列
* @param dataArray 下拉框数组
* @param wbCreat 所在excel的WorkBook,用于创建隐藏Sheet
* @param hidddenSheetName 隐藏Sheet的名称
* @return
*/
public void createDropDownListWithHiddenSheet(Sheet sheet, int firstRow,
int firstCol, int endRow,
int endCol, String[] dataArray,
Workbook wbCreat,
String hidddenSheetName) {
Sheet hidden = wbCreat.createSheet(hidddenSheetName);
Cell cell = null;
for (int i = 0, length = dataArray.length; i < length; i++) {
String name = dataArray[i];
Row row = hidden.createRow(i);
cell = row.createCell(0);
cell.setCellValue(name);
}
Name namedCell = wbCreat.createName();
namedCell.setNameName(hidddenSheetName);
namedCell.setRefersToFormula(hidddenSheetName + "!$A$1:$A$" + dataArray.length);
//sheet设置为隐藏
wbCreat.setSheetHidden(wbCreat.getSheetIndex(hidden), true);
//加载数据,将名称为hidden的
DataValidationConstraint constraint = null;
// 设置数据有效性加载在哪个单元格上,四个参数分别是:起始行、终止行、起始列、终止列
CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol,
endCol);
// 创建 DataValidation
DataValidation validation = null;
if (sheet instanceof XSSFSheet || sheet instanceof SXSSFSheet) {
DataValidationHelper dvHelper = sheet.getDataValidationHelper();
constraint = dvHelper.createFormulaListConstraint(hidddenSheetName);
validation = dvHelper.createValidation(constraint, addressList);
} else {
constraint = DVConstraint.createFormulaListConstraint(hidddenSheetName);
validation = new HSSFDataValidation(addressList, constraint);
}
if (validation instanceof HSSFDataValidation ) {
validation .setSuppressDropDownArrow(false);
} else {
validation .setSuppressDropDownArrow(true);
validation .setShowErrorBox(true);
}
sheet.addValidationData(validation);
}
3、多选下拉框
由于Excel要做成多选下拉框,是需要用给Excel写宏的方式实现,但是使用POI没办法给Excel写宏,所以我们使用读取一个有Excel的宏的模板,然后往这个Excel里面写宏的方式实现
首先我们需要一个有宏的模板
- 打开excel->文件->选项->自定义功能区->右侧的开发工具点击√
- 开发工具->Visual Basic->双击需要添加宏的sheet->在右侧粘贴下面的代码
Sub Worksheet_Change(ByVal Target As Range)
'让数据有效性选择 可以多选,不可重复
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
If InStr(Cells(3, Target.Column), "多选") Then '本列的第三行的单元格是否包含对选 也可以直接用数字,数字是你想要多选的列是多少,多个用or连接。Target.Column = 7 Or 9
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If oldVal = "" Then
Else
If newVal = "" Then
Else '去除重复的字段
If InStr(1, oldVal, newVal) <> 0 Then
If InStr(1, oldVal, newVal) + Len(newVal) - 1 = Len(oldVal) Then '最后一个选项重复
Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 1)
Else
Target.Value = Replace(oldVal, newVal & ",", "") '不是最后一个选项重复的时候处理逗号
End If
Else '不是重复选项就视同增加选项
Target.Value = oldVal _
& "," & newVal '可以是任意符号隔开
End If
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
4、参考:
链接地址:https://gitee.com/lemur/easypoi/issues/I4Y4TE
分为两个版本
若导出xls格式的excel时,下拉框列表字符串长度超过255时,会报出异常。这个属于poi源码中的异常,不好修改。
若导出xlsx格式的excel时,下拉框列表字符串长度超过255时,会自动取消下拉框。
在实际应用中遇到了绕不开的问题,必须解决,所以参考了创建新的隐藏sheet页,再将sheet页中数据引用至下拉框中方法。
需要修改两个地方,具体如下:
cn\afterturn\easypoi\excel\export\ExcelExportService.java 中 createSheetForMap方法。
cn\afterturn\easypoi\excel\export\ExcelExportService.java 中 createAddressList方法
public void createSheetForMap(Workbook workbook, ExportParams entity,
List<ExcelExportEntity> entityList, Collection<?> dataSet) {
if (LOGGER.isDebugEnabled()) {
LOGGER.debug("Excel version is {}",
entity.getType().equals(ExcelType.HSSF) ? "03" : "07");
}
if (workbook == null || entity == null || entityList == null || dataSet == null) {
throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);
}
super.type = entity.getType();
if (type.equals(ExcelType.XSSF)) {
MAX_NUM = 1000000;
}
if (entity.getMaxNum() > 0) {
MAX_NUM = entity.getMaxNum();
}
Sheet sheet = null;
try {
sheet = workbook.createSheet(entity.getSheetName());
} catch (Exception e) {
// 重复遍历,出现了重名现象,创建非指定的名称Sheet
sheet = workbook.createSheet();
}
if (dataSet.getClass().getClass().getName().contains("Unmodifiable")) {
List dataTemp = new ArrayList<>();
dataTemp.addAll(dataSet);
dataSet = dataTemp;
}
//-------------修改这里,以下为新增代码--------------
//---在生成EXCEL数据验证功能时,将下拉框中数据长度----
//---大于255的数据,放入一个隐藏sheet页中,在引用-----
//---至之前的位置上。-------------------------------
dictHandler = entity.getDictHandler();
Map<Integer,List<String>> tmpMap = new HashMap<>();
for (int i = 0; i < entityList.size(); i++) {
if (entityList.get(i).isAddressList()) {
ExcelExportEntity eee = entityList.get(i);
String[] addressLists = getAddressListValues(eee);
if (StrUtil.totalLength(addressLists) > 255){
tmpMap.put(i,Arrays.asList(addressLists));
}
}
}
// 创建隐藏的sheet页,将长下拉框的值,依次存入其中。
String[] colIndex = {"A","B","C","D","E","F","G","H","I","G","K","L"};
Sheet hideSheet = workbook.createSheet("hiddenSheet");
if (tmpMap.size() > 0){
int col = 0;
for (Map.Entry<Integer,List<String>> entry : tmpMap.entrySet()){
// 从第一列开始,每个第一行存放 key值
for (int i = 0; i < entry.getValue().size(); i++) {
hideSheet.createRow(i).createCell(col).setCellValue(entry.getValue().get(i));
}
// 创建名称,可被其他单元格引用
Name refName = workbook.createName();
refName.setNameName("hidden");
// 设置名称引用的公式
refName.setRefersToFormula("hiddenSheet!" + "$"+colIndex[col]+"$1:$"+colIndex[col]+"$"+entry.getValue().size());
// 获取上文名称内数据
DataValidationHelper helper = sheet.getDataValidationHelper();
DataValidationConstraint constraint = helper.createFormulaListConstraint("hidden");
// 设置下拉框位置
CellRangeAddressList addressList = new CellRangeAddressList(1, 10000, entry.getKey(), entry.getKey());
DataValidation dataValidation = helper.createValidation(constraint, addressList);
sheet.addValidationData(dataValidation);
col++;
}
}
//-----------------结束------------------------
insertDataToSheet(workbook, entity, entityList, dataSet, sheet);
if (entity.isReadonly()) {
sheet.protectSheet(UUID.randomUUID().toString());
}
sheet.setForceFormulaRecalculation(true);
if (isAutoSize(entity, entityList)) {
int len0 = 0, len1 = 0;
if (sheet.getRow(0) !=null){
len0 = sheet.getRow(0).getLastCellNum();
}
if (sheet.getRow(1) !=null){
len1 = sheet.getRow(1).getLastCellNum();
}
int len = Math.max(len0,len1);
for (int i = 0; i < len; i++) {
sheet.autoSizeColumn(i, true);
}
}
}
private int createAddressList(Sheet sheet, int index, List<ExcelExportEntity> excelParams, int cellIndex) {
for (int i = 0; i < excelParams.size(); i++) {
if (excelParams.get(i).isAddressList()) {
ExcelExportEntity entity = excelParams.get(i);
CellRangeAddressList regions = new CellRangeAddressList(index,
this.type.equals(ExcelType.XSSF) ? 1000000 : 65535, cellIndex, cellIndex);
//---------------修改位置-----------------------------
//---若数据验证下拉框总长度大于255,则不进行下面的操作----
String[] addressList = getAddressListValues(entity);
if (StrUtil.totalLength(addressList) < 255){
//-----------------------结束-------------------------------------------------
DataValidation dataValidation = sheet.getDataValidationHelper().createValidation(sheet.getDataValidationHelper().createExplicitListConstraint(getAddressListValues(entity)), regions);
// 处理Excel兼容性问题
if (dataValidation instanceof XSSFDataValidation) {
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(false);
} else {
dataValidation.setSuppressDropDownArrow(false);
}
sheet.addValidationData(dataValidation);
}
}
if (excelParams.get(i).getList() != null) {
cellIndex = createAddressList(sheet, index, excelParams.get(i).getList(), cellIndex);
} else {
cellIndex++;
}
}
return cellIndex;
}