java导出Excel增加下拉框选项
这篇文章主要介绍了Java 导出Excel增加下拉框选项，excel对于下拉框较多选项的，需要使用隐藏工作簿来解决，使用函数取值来做选项，下文具体的操作详情，需要的小伙伴可以参考一下！
excel对于下拉框较多选项的，需要使用隐藏工作簿来解决，使用函数取值来做选项
一、小数据量情况
选项较少（一般少于5个）：
private static DataValidation setFewDataValidation(Sheet sheet, String[] textList, int firstRow, int endRow, int firstCol, int endCol) {
DataValidationHelper helper = sheet.getDataValidationHelper();
//加载下拉列表内容
DataValidationConstraint constraint = helper.createExplicitListConstraint(textList);
constraint.setExplicitListValues(textList);
//设置数据有效性加载在哪个单元格上。四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList((short) firstRow, (short) endRow, (short) firstCol, (short) endCol);
//数据有效性对象
return helper.createValidation(constraint, regions);
}
二、大数据量情况
选项较多
创建隐藏工作簿:
Sheet sheetHidden = wb.createSheet("Sheet2");
wb.setSheetHidden(1, true);
每一个列表占用一列
当然也可以每个列表使用一张工作簿，只用第一列。 这里是使用一个工作簿使用每个列，先26个字母，一般够用了
String[] arr = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"};
for (int j = 0; j < dataList.size(); j++) {
if (index == 0) { //第1个下拉选项，直接创建行、列
row = sheetHidden.createRow(j); //创建数据行
// sheetHidden.setColumnWidth(j, 4000); //设置每列的列宽
row.createCell(0).setCellValue(dataList.get(j)); //设置对应单元格的值
} else { //非第1个下拉选项
int rowCount = sheetHidden.getLastRowNum();
if (j <= rowCount) { //前面创建过的行，直接获取行，创建列
//获取行，创建列
sheetHidden.getRow(j).createCell(index).setCellValue(dataList.get(j)); //设置对应单元格的值
} else { //未创建过的行，直接创建行、创建列
// sheetHidden.setColumnWidth(j, 4000); //设置每列的列宽
//创建行、创建列
sheetHidden.createRow(j).createCell(index).setCellValue(dataList.get(j)); //设置对应单元格的值
}
}
}
index 代表第几个下拉框，也就是在隐藏工作簿的第几列，dataList表示下拉框的内容
创建公式：
String strFormula = “Sheet2!$” + arr[index] + “
1
:
1:
1:” + arr[index] + “$” + dataList.size();
Sheet2第A1到A5000作为下拉列表来源数据
xls和xlsx生成下拉框的选项不一样
private static DataValidation setMoreDataValidation(Workbook wb, Sheet sheet, String strFormula, int startRow, int endRow, int startColumn, int endColumn) {
DataValidation dataValidation;
// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(startRow, endRow, startColumn, endColumn);
if (wb instanceof XSSFWorkbook) {
//获取新sheet页内容
XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST, strFormula);
// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
// 数据有效性对象
DataValidationHelper help = new XSSFDataValidationHelper((XSSFSheet) sheet);
dataValidation = help.createValidation(constraint, regions);
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(true);
} else {
// 设置数据有效性加载在哪个单元格上。四个参数分别是：起始行、终止行、起始列、终止列
DVConstraint constraint = DVConstraint.createFormulaListConstraint(strFormula);
dataValidation = new HSSFDataValidation(regions, constraint);
dataValidation.setSuppressDropDownArrow(false);
}
dataValidation.setEmptyCellAllowed(true);
dataValidation.setShowPromptBox(true);
dataValidation.createErrorBox("Error", "请选择下拉框中的数据");
dataValidation.createPromptBox("提示", "只能选择下拉框里面的数据");
return dataValidation;
}
加入工作簿:
sheet.addValidationData()
完整代码:
private static void setValidationDate(Workbook wb, Sheet sheet, List<DataValidationCell> dataValidationCellList) {
if (dataValidationCellList.isEmpty()) {
return;
}
String[] arr = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"};
int index = 0;
Row row;
Sheet sheetHidden = wb.createSheet("Sheet2");
wb.setSheetHidden(1, true);
for (DataValidationCell dataValidationCell : dataValidationCellList) {
List<String> dataList = dataValidationCell.getDataList();
if (CollectionUtils.isEmpty(dataList)) {
continue;
}
if (dataList.size() <= 5) {
sheet.addValidationData(setFewDataValidation(sheet, dataList.toArray(new String[0]),
dataValidationCell.getStartRow(), dataValidationCell.getEndRow(),
dataValidationCell.getStartColumn(), dataValidationCell.getEndColumn())); //超过255个报错
} else {
//String strFormula = "Sheet2!$A$1:$A$5000" ; //Sheet2第A1到A5000作为下拉列表来源数据
String strFormula = "Sheet2!$" + arr[index] + "$1:$" + arr[index] + "$" + dataList.size(); //Sheet2第A1到A5000作为下拉列表来源数据
sheet.addValidationData(setMoreDataValidation(wb, sheet, strFormula,
dataValidationCell.getStartRow(), dataValidationCell.getEndRow(),
dataValidationCell.getStartColumn(), dataValidationCell.getEndColumn())); //下拉列表元素很多的情况
//2、生成sheet2内容
for (int j = 0; j < dataList.size(); j++) {
if (index == 0) { //第1个下拉选项，直接创建行、列
row = sheetHidden.createRow(j); //创建数据行
// sheetHidden.setColumnWidth(j, 4000); //设置每列的列宽
row.createCell(0).setCellValue(dataList.get(j)); //设置对应单元格的值
} else { //非第1个下拉选项
int rowCount = sheetHidden.getLastRowNum();
if (j <= rowCount) { //前面创建过的行，直接获取行，创建列
//获取行，创建列
sheetHidden.getRow(j).createCell(index).setCellValue(dataList.get(j)); //设置对应单元格的值
} else { //未创建过的行，直接创建行、创建列
// sheetHidden.setColumnWidth(j, 4000); //设置每列的列宽
//创建行、创建列
sheetHidden.createRow(j).createCell(index).setCellValue(dataList.get(j)); //设置对应单元格的值
}
}
}
index++;
}
}
}
java导出Excel增加下拉框选项(java结合easyExcel)
excel对于下拉框较多选项的，需要使用隐藏工作簿来解决，使用函数取值来做选项
添加传参模型ConsumablesAddDTO
/**
* 描述：添加传参模型ConsumablesAddDTO
*/
@Data
@ApiModel(value = "ConsumablesAddDTO", description = "耗材表AddDTO")
public class ConsumablesAddDTO implements Serializable {
private static final long serialVersionUID = 1L;
@ApiModelProperty(value = "仓库名称", required = true)
@Excel(name = "*仓库名称", orderNum = "5", width = 20)
@NotBlank(message = "仓库名称必填", groups = {Default.class})
private String wareHouseName;
@ApiModelProperty(value = "是否启用 0:否 1:是")
@Excel(name = "是否启用", orderNum = "13",addressList = true,replace = {"否_0","是_1"})
private Integer isEnable;
@ApiModelProperty(value = "单位id")
private String unitId;
@ApiModelProperty(value = "单位名称", required = true)
@Excel(name = "*单位", orderNum = "14", addressList = true, replace = {"瓶"}, width = 10)
@NotBlank(message = "单位名称不能为空", groups = {Default.class})
private String unitName;
}
仓库名称（wareHouseName）为大数据量，单位名称（unitName）为小数据量
一、小数据量情况(仅供参考)
选项较少（一般少于5个）：
/**
* 生成excel下拉框(适用于下拉框选项的数量较少的情况)
*
* @param pojoClass
*/
public void replaceHandel(Class<?> pojoClass, String[] List) {
Field[] fields = pojoClass.getDeclaredFields();
try {
for (int i = 0; i < fields.length; i++) {
Field field = fields[i];
String name = field.getName();
if (name.equals("unitName")) {
// 这里可以加上指定字段做处理，也可以将需要的字段枚举或者是字段存到redis
Excel annotation = field.getAnnotation(Excel.class);
if (annotation != null && annotation.replace().length > 0) {
InvocationHandler invocationHandler = Proxy.getInvocationHandler(annotation);
Field memberValues = invocationHandler.getClass().getDeclaredField("memberValues");
memberValues.setAccessible(true);
Map map = (Map) memberValues.get(invocationHandler);
// replace 的值是字符串数组，自定义的时候记得数据格式
map.put("replace", List);
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
二、大数据量情况(推荐)
适用于下拉框选项较多情况
下拉框核心代码
/**
* 使用createFormulaListConstraint生成excel下拉框(适用于下拉框选项的数量较多的情况)
*
* @param workbook
* @param formulaString
*/
public void setDropDownAndHidden( Workbook workbook, String[] formulaString) {
// 获取工作表
// 注意：不管exel中有多少个下拉框，这里都是固定的getSheetAt(0)
Sheet sheet = workbook.getSheetAt(0);
//Sheet sheet = workbook.getSheet("sheet1");
// 创建隐藏sheet
Sheet hideSheet = workbook.createSheet("hiddenSheet");
for (int i = 0; i < formulaString.length; i++) {
// 这里面是下拉框的内容
hideSheet.createRow(i).createCell(0).setCellValue(formulaString[i]);
}
// 创建名称，可被其他单元格引用
Name categoryName = workbook.createName();
categoryName.setNameName("hidden");
// 设置名称引用的公式
// 使用像'A1：B1'这样的相对值会导致在Microsoft Excel中使用工作簿时名称所指向的单元格的意外移动，
// 通常使用绝对引用，例如'$A$1:$B$1'可以避免这种情况。
categoryName.setRefersToFormula("hiddenSheet!" + "$A$1:$A$" + formulaString.length);
// 获取上文名称内数据
DataValidationHelper helper = sheet.getDataValidationHelper();
DataValidationConstraint constraint = helper.createFormulaListConstraint("hidden");
// 设置下拉框位置
// 在第3行第5列位置处生成下拉框（下标从0开始）：firstCol = lastCol = orderNum - 1;
CellRangeAddressList addressList = new CellRangeAddressList(2, 4000, 4, 4);
DataValidation dataValidation = helper.createValidation(constraint, addressList);
// 处理Excel兼容性问题
if (dataValidation instanceof XSSFDataValidation) {
// 数据校验
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(true);
} else {
dataValidation.setSuppressDropDownArrow(false);
}
// 作用在目标sheet上
sheet.addValidationData(dataValidation);
// 设置hiddenSheet隐藏
workbook.setSheetHidden(1, true);
}
注意
如果exel中有多个下拉框，则只需多次调用setDropDownAndHidden这个方法，修改hiddenSheet字符串、hidden字符串、new CellRangeAddressList(2, 4000, 4, 4) 中的数值 、workbook.setSheetHidden(1, true)中有1个下拉框就是 (1,true) ，有两个下拉框就是 (2,true) ，以此类推…只需要修改这几个地方即可！
// 创建隐藏sheet
Sheet hideSheet = workbook.createSheet("hiddenSheet");
categoryName.setNameName("hidden");
categoryName.setRefersToFormula("hiddenSheet!" + "$A$1:$A$" + formulaString.length);
DataValidationConstraint constraint = helper.createFormulaListConstraint("hidden");
// 设置下拉框位置
// 在第3行第5列位置处生成下拉框（下标从0开始）：firstCol = lastCol = orderNum - 1;
CellRangeAddressList addressList = new CellRangeAddressList(2, 4000, 4, 4);
// 设置hiddenSheet隐藏
workbook.setSheetHidden(1, true);
完整代码
Controller层
@GetMapping(value = "/template")
@ApiOperation(value = "获取耗材批量导入模板")
@LogAnnotation(operation = "获取耗材批量导入模板")
public void getTemplate(HttpServletResponse response) {
try {
baseService.template(response);
} catch (IOException e) {
e.printStackTrace();
}
}
Service层
/**
* 获取导入模板
*
* @param response
**/
void template(HttpServletResponse response) throws IOException;
ServiceImpl层
@Override
public void template(HttpServletResponse response) throws IOException {
String companyId = SecurityUtils.getCompanyId();
// 导入模板下载
List<String> unitList = unitMapper.findAllUnit(companyId).stream()
.map(t -> t.getString("unitName") + "_" + t.getString("id"))
.collect(Collectors.toList());
Map<String,List<String>> map = new HashMap<>(2);
map.put("unitId",unitList);
// 单位名称集合
LbqWrapper<Unit> unitLbqWrapper = Wraps.<Unit>lbQ();
unitLbqWrapper.eq(Unit::getCompanyId, companyId);
List<Unit> units = unitMapper.selectList(unitLbqWrapper);
List<String> unitNames = units.stream().map(Unit::getUnitName).collect(Collectors.toList());
// list转String数组
String[] unitNameList = unitNames.toArray(new String[unitNames.size()]);
// 仓库名称集合
LbqWrapper<WareHouse> wareHouseWrapper = Wraps.lbQ();
wareHouseWrapper.eq(WareHouse::getCompanyId, companyId);
List<WareHouse> wareHouseList = wareHouseMapper.selectList(wareHouseWrapper);
List<String> wareHouseNames = wareHouseList.stream().map(WareHouse::getWareHouseName).collect(Collectors.toList());
// list转String数组
String[] wareHouseNameList = wareHouseNames.toArray(new String[wareHouseNames.size()]);
try {
ReplaceValue.replace(ConsumablesAddDTO.class,map);
} catch (NoSuchFieldException | IllegalAccessException e) {
e.printStackTrace();
throw new BizException("获取导入模板失败");
}
ExportParams params = new ExportParams("注：\n" +
"1、物料编码具有唯一性，若重复会导入失败。若导入物料有对应编码则沿用，若无编码则系统随机生成；\n" +
"2、物料分类、存放仓库、品牌、供应商请填物料分类编码、仓库名称、品牌编码、供应商编码，不能填写物料分类名称；\n" +
"3、若存在多个采购员请使用英文逗号分隔；\n" +
"4、安全库存和预留库存请填写正整数；\n" +
"5、不允许对表头字段进行增删改。\n", ConsumableConstant.SHEET_NAME, ExcelType.XSSF);
params.setTitleHeight((short) 30);
params.setCreateHeadRows(true);
params.setStyle(ExcelExportStyleImpl.class);
// 生成带模板数据的workbook
Workbook workbook = ExcelExportUtil.exportExcel(params, ConsumablesAddDTO.class, new ArrayList<ConsumablesAddDTO>());
// 生成excel下拉框(适用于下拉框选项的数量较多的情况)
// 这里excel中有两处要生成下拉框，所以调用了2次生成下拉框的方法
setDropDownAndHidden1(workbook, wareHouseNameList);
setDropDownAndHidden2(workbook, unitNameList);
// replaceHandel(workbook, unitNameList);// 这个方法不推荐(适用于下拉框选项的数量较少的情况)，仅供参考
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("耗材导入模板" + ".xlsx", "UTF-8"));
response.flushBuffer();
workbook.write(response.getOutputStream());
workbook.close();
} catch (IOException var4) {
var4.printStackTrace();
throw new BizException(var4.getMessage());
}
}
/**
* 使用createFormulaListConstraint生成excel下拉框(适用于下拉框选项的数量较多的情况)
*
* @param workbook
* @param formulaString
*/
public void setDropDownAndHidden1( Workbook workbook, String[] formulaString) {
// 获取工作表
// 注意：不管exel中有多少个下拉框，这里都是固定的getSheetAt(0)
Sheet sheet = workbook.getSheetAt(0);
// 创建隐藏sheet
Sheet hideSheet = workbook.createSheet("hiddenSheet1");
for (int i = 0; i < formulaString.length; i++) {
// 这里面是下拉框的内容
hideSheet.createRow(i).createCell(0).setCellValue(formulaString[i]);
}
// 创建名称，可被其他单元格引用
Name categoryName = workbook.createName();
categoryName.setNameName("hidden1");
// 设置名称引用的公式
// 使用像'A1：B1'这样的相对值会导致在Microsoft Excel中使用工作簿时名称所指向的单元格的意外移动，
// 通常使用绝对引用，例如'$A$1:$B$1'可以避免这种情况。
categoryName.setRefersToFormula("hiddenSheet1!" + "$A$1:$A$" + formulaString.length);
// 获取上文名称内数据
DataValidationHelper helper = sheet.getDataValidationHelper();
DataValidationConstraint constraint = helper.createFormulaListConstraint("hidden1");
// 设置下拉框位置
// 在第3行第5列位置处生成下拉框（下标从0开始）：firstCol = lastCol = orderNum - 1;
CellRangeAddressList addressList = new CellRangeAddressList(2, 4000, 4, 4);
DataValidation dataValidation = helper.createValidation(constraint, addressList);
// 处理Excel兼容性问题
if (dataValidation instanceof XSSFDataValidation) {
// 数据校验
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(true);
} else {
dataValidation.setSuppressDropDownArrow(false);
}
// 作用在目标sheet上
sheet.addValidationData(dataValidation);
// 设置hiddenSheet隐藏
workbook.setSheetHidden(1, true);
}
/**
* 使用createFormulaListConstraint生成excel下拉框(适用于下拉框选项的数量较多的情况)
*
* @param workbook
* @param formulaString
*/
public void setDropDownAndHidden2( Workbook workbook, String[] formulaString) {
// 获取工作表
// 注意：不管exel中有多少个下拉框，这里都是固定的getSheetAt(0)
Sheet sheet = workbook.getSheetAt(0);
// 创建隐藏sheet
Sheet hideSheet = workbook.createSheet("hiddenSheet2");
for (int i = 0; i < formulaString.length; i++) {
// 这里面是下拉框的内容
hideSheet.createRow(i).createCell(0).setCellValue(formulaString[i]);
}
// 创建名称，可被其他单元格引用
Name categoryName = workbook.createName();
categoryName.setNameName("hidden2");
// 设置名称引用的公式
// 使用像'A1：B1'这样的相对值会导致在Microsoft Excel中使用工作簿时名称所指向的单元格的意外移动，
// 通常使用绝对引用，例如'$A$1:$B$1'可以避免这种情况。
categoryName.setRefersToFormula("hiddenSheet2!" + "$A$1:$A$" + formulaString.length);
// 获取上文名称内数据
DataValidationHelper helper = sheet.getDataValidationHelper();
DataValidationConstraint constraint = helper.createFormulaListConstraint("hidden2");
// 设置下拉框位置
// 在第3行第14列位置处生成下拉框（下标从0开始）：firstCol = lastCol = orderNum - 1;
CellRangeAddressList addressList = new CellRangeAddressList(2, 4000, 13, 13);
DataValidation dataValidation = helper.createValidation(constraint, addressList);
// 处理Excel兼容性问题
if (dataValidation instanceof XSSFDataValidation) {
// 数据校验
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(true);
} else {
dataValidation.setSuppressDropDownArrow(false);
}
// 作用在目标sheet上
sheet.addValidationData(dataValidation);
// 设置hiddenSheet隐藏
workbook.setSheetHidden(2, true);
}
/**
* 生成excel下拉框(适用于下拉框选项的数量较少的情况)
*
* @param pojoClass
*/
public void replaceHandel(Class<?> pojoClass, String[] List) {
Field[] fields = pojoClass.getDeclaredFields();
try {
for (int i = 0; i < fields.length; i++) {
Field field = fields[i];
String name = field.getName();
if (name.equals(CommonConstant.UNIT_NAME)) {
// 这里可以加上指定字段做处理，也可以将需要的字段枚举或者是字段存到redis
Excel annotation = field.getAnnotation(Excel.class);
if (annotation != null && annotation.replace().length > 0) {
InvocationHandler invocationHandler = Proxy.getInvocationHandler(annotation);
Field memberValues = invocationHandler.getClass().getDeclaredField("memberValues");
memberValues.setAccessible(true);
Map map = (Map) memberValues.get(invocationHandler);
// replace 的值是字符串数组，自定义的时候记得数据格式
map.put("replace", List);
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
到此这篇关于Java 导出Excel增加下拉框选项的文章就介绍到这了!
