导入的时候,一般会有模版下载,那么会涉及到下拉框的情况,此下拉框为动态生成,今天我们来看下
所用工具easypoi
@Override
public void downLoadTemplete(HttpServletResponse response) throws Exception {
// 模版下载,空数据,列对就可以了
List<ImportBasicPersonVo> importBasicPersonVos = new ArrayList<>();
// 文件保存路径
String tempZipFilePath = "zipTempPath";
// 创建xls文件
File tempXlsxFile = new File(tempZipFilePath + File.separator + "import_person.xls");
OutputStream xlsxOut = new FileOutputStream(tempXlsxFile);
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), ImportBasicPersonVo.class,
importBasicPersonVos);
// 设置下拉
setSheetDropDown(workbook);
workbook.write(xlsxOut);
}设置下拉,即从数据库中查询要下拉的数据,然后设置,经过实践,当下拉中显示的字符总数超过255字节的时候,会报错,这里两种情况分别展示
// 显示的示例表格
Sheet sheetAt = workbook.getSheetAt(0);
// 下拉数据,绑定后就隐藏的表格
Sheet hidden = workbook.createSheet("hidden");
Cell cell = null;
//下拉数据
List<String> orgList = orgService.list().stream().map(org -> org.getName()).collect(Collectors.toList());
// 将数据都放入hidden中
for (int i = 0, length = orgList.size(); i < length; i++)
{
String name = orgList.get(i);
Row row = hidden.createRow(i);
cell = row.createCell(0);
cell.setCellValue(name);
}
Name namedCell = workbook.createName();
// 绑定name
namedCell.setNameName("hidden");
// 在hidden中数据要存放的位置
namedCell.setRefersToFormula("hidden!$A$1:$A$" + orgList.size());
DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden");
// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList addressList = new CellRangeAddressList(1, 100, 8, 8);
HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);
//将第二个sheet设置为隐藏
workbook.setSheetHidden(1, false);
if (null != validation)
{
sheetAt.addValidationData(validation);// 将数据加入到示例导出表格中 位置为1, 100, 8, 8
}
//=====================这是大数据量的下拉,下面是普通下拉===============================
// 准备下拉列表数据
List<DictGroupItemVo> dictGroupAndItemList = basicDictGroupMapper.getDictGroupAndItemList(Arrays.asList("person_type", "id_type", "degree", "nation"));
List<String> personTypeList = dictGroupAndItemList.stream().filter(dictGroupItemVo -> dictGroupItemVo.getGroupCode().equals("person_type")).map(dictGroupItemVo -> dictGroupItemVo.getName()).collect(Collectors.toList());
List<String> idTypeList = dictGroupAndItemList.stream().filter(dictGroupItemVo -> dictGroupItemVo.getGroupCode().equals("id_type")).map(dictGroupItemVo -> dictGroupItemVo.getName()).collect(Collectors.toList());
List<String> degreeList = dictGroupAndItemList.stream().filter(dictGroupItemVo -> dictGroupItemVo.getGroupCode().equals("degree")).map(dictGroupItemVo -> dictGroupItemVo.getName()).collect(Collectors.toList());
List<String> nationList = dictGroupAndItemList.stream().filter(dictGroupItemVo -> dictGroupItemVo.getGroupCode().equals("nation")).map(dictGroupItemVo -> dictGroupItemVo.getName()).collect(Collectors.toList());
// List<String> orgList = orgService.list(new QueryWrapper<Org>().select().eq(ColumnNameConstants.DELETE_STATUS, 0)).stream().map(org -> org.getName()).collect(Collectors.toList());
// 获取当前用户有权限的组织集合
// List<String> orgList = orgService.queryOrgListByUserId().stream().map(org -> org.getName()).collect(Collectors.toList());
// 类别 证件类型 组织 学历 民族 性别
// 设置第一列的1-100行为下拉列表
CellRangeAddressList personRegions = new CellRangeAddressList(1, 100, 5, 5);
CellRangeAddressList idTypeRegions = new CellRangeAddressList(1, 100, 6, 6);
CellRangeAddressList degreeRegions = new CellRangeAddressList(1, 100, 11, 11);
CellRangeAddressList nationRegions = new CellRangeAddressList(1, 100, 12, 12);
CellRangeAddressList sexRegions = new CellRangeAddressList(1, 100, 4, 4);
// CellRangeAddressList orgRegions = new CellRangeAddressList(1, 100, 8, 8);
// 创建下拉列表数据
DVConstraint personConstraint = null;
DVConstraint idTypeConstraint = null;
DVConstraint degreeConstraint = null;
DVConstraint nationConstraint = null;
DVConstraint sexConstraint = null;
DVConstraint orgConstraint = null;
if (CollectionUtil.isNotEmpty(personTypeList)) {
personConstraint = DVConstraint.createExplicitListConstraint(personTypeList.toArray(new String[personTypeList.size()]));
idTypeConstraint = DVConstraint.createExplicitListConstraint(idTypeList.toArray(new String[idTypeList.size()]));
degreeConstraint = DVConstraint.createExplicitListConstraint(degreeList.toArray(new String[degreeList.size()]));
nationConstraint = DVConstraint.createExplicitListConstraint(nationList.toArray(new String[nationList.size()]));
sexConstraint = DVConstraint.createExplicitListConstraint(new String[]{"男", "女"});
// orgConstraint = DVConstraint.createExplicitListConstraint(orgList.toArray(new String[orgList.size()]));
}
// 绑定
HSSFDataValidation personValidation = new HSSFDataValidation(personRegions, personConstraint);
HSSFDataValidation idTypeValidation = new HSSFDataValidation(idTypeRegions, idTypeConstraint);
HSSFDataValidation degreeValidation = new HSSFDataValidation(degreeRegions, degreeConstraint);
HSSFDataValidation nationValidation = new HSSFDataValidation(nationRegions, nationConstraint);
HSSFDataValidation sexValidation = new HSSFDataValidation(sexRegions, sexConstraint);
// HSSFDataValidation orgValidation = new HSSFDataValidation(orgRegions, orgConstraint);
sheetAt.addValidationData(personValidation);
sheetAt.addValidationData(idTypeValidation);
sheetAt.addValidationData(degreeValidation);
sheetAt.addValidationData(nationValidation);
sheetAt.addValidationData(sexValidation);
// sheetAt.addValidationData(orgValidation);可以看到 其中orgValidation 开始也是用大多数普通下拉的,但是由于数据了较大,报错255,百度后,单独提出去了,位置都是(1,100,8,8)这样可以达到大数据下拉框的展示,并且不报错
