背景:产品崽崽想要把一些业务数据通过excel的方式批量导入到系统。有一些配置的标签数据希望设置成下拉选择的方式,导出到excel作为导入数据的模板
如下
尝试了csdn中几种点击量很高的设置方式 ,均达不到我要的效果。遇到的问题,1、其他单元格都可以设置,唯独从数据库中读取的数据设置不了。2、excel手动设置下拉有字符数限制(少于255个字符)
解决:1、产品崽崽给的模板中已经带有设置下拉的格式,需要清空掉。建议将 excel另存为xml ,搜索DataValidation标签,找到对应的设置 ,删除掉,再另存为.xlsx;
2、采用‘从单元格选择下拉选项’。
原来的实现思路:
{
String src = "D:\\1\\0504\\xxx入模版.xlsx";
SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(new XSSFWorkbook(new FileInputStream(src)));
SXSSFSheet sheet = sxssfWorkbook.getSheetAt(0);
DataValidationHelper helper = sheet.getDataValidationHelper();
// 生成下拉列表
// 设置范围
CellRangeAddressList regions = new CellRangeAddressList(3, 5, 12, 12);
// 生成下拉框内容
DataValidationConstraint constraint = helper.createExplicitListConstraint(new String[] { "abc","def", "ghi" });
// 绑定下拉框和作用区域
DataValidation dataValidation = helper.createValidation(constraint, regions);
//处理Excel兼容性问题
if(dataValidation instanceof XSSFDataValidation){
//数据校验
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(true);
}else{
dataValidation.setSuppressDropDownArrow(false);
}
// 对sheet页生效
sheet.addValidationData(dataValidation);
// 写入文件
FileOutputStream fileOut;
try {
String dts = "D:\\1\\0430\\ "+ RandomUtil.randomString(3) + ".xlsx";
fileOut = new FileOutputStream(dts);
sxssfWorkbook.write(fileOut);
fileOut.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// 结束
System.out.println("Over");
}
修改后的代码:
{
OutputStream out = null;
InputStream input = null;
String fileName = "xxxx.xlsx";
try {
//设置要下载的文件的名称
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName,"UTF-8"));
//通知客服文件的MIME类型
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
//获取模板
input = new ClassPathResource("/static/"+fileName).getInputStream();
SXSSFWorkbook wb = new SXSSFWorkbook(new XSSFWorkbook(input));
SXSSFSheet sheet = wb.getSheetAt(0);
//设置一个隐藏的sheet 来存放动态下拉的数据
SXSSFSheet select = wb.createSheet("putSelectHidden");
wb.setSheetHidden(1,true);
DataValidationHelper helper = sheet.getDataValidationHelper();
//简历渠道
List<HrLabel> sourceList = 获取数据库数据
if(CollectionUtil.isNotEmpty(sourceList)){
//数据拼接
String[] values = new String[sourceList.size()];
int i = 0;
for(HrLabel label : sourceList){
values[i] = label.getLabelName() + BusiConstants.LABEL_SPLIT + label.getId();
i++;
}
//关键方法一
String listFormula = buildHiddenSheet(select, values,0,"A");
//关键方法二
addValidationData(sheet, helper, listFormula,12);
}
OutputStream outputStream = response.getOutputStream();
BufferedOutputStream bufferedOutputStream = new BufferedOutputStream(outputStream);
wb.write(bufferedOutputStream);
bufferedOutputStream.flush();
bufferedOutputStream.close();
} catch (Exception e) {
//错误日志
}finally {
//关闭流
}
}
private String buildHiddenSheet(SXSSFSheet select, String[] values,int i,String cName) {
for(int j = 0 ; j < values.length ; j++){
SXSSFRow row = select.getRow(j);
if(null == row){
row = select.createRow(j);
}
SXSSFCell cell = row.createCell(i);
cell.setCellValue(values[j]);
}
String listFormula = select.getSheetName() + "!$"+cName+"$1:$"+ cName +"$" + values.length;
return listFormula;
}
private void addValidationData(SXSSFSheet sheet, DataValidationHelper helper, String listFormula,int cellIndex) {
//区域
CellRangeAddressList regions = new CellRangeAddressList(3, 1000, cellIndex, cellIndex);
// 生成下拉框内容
DataValidationConstraint constraint = helper.createFormulaListConstraint(listFormula);
// 绑定下拉框和作用区域
DataValidation dataValidation = helper.createValidation(constraint, regions);
//处理Excel兼容性问题
if(dataValidation instanceof XSSFDataValidation){
//数据校验
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(true);
}else{
dataValidation.setSuppressDropDownArrow(false);
}
// 对sheet页生效
sheet.addValidationData(dataValidation);
}
效果:
最后2003的excel 的解决思路类似,画瓢就行。不能次次白票