引言
文件的下载是一个非常常见的功能,也有一些非常好的框架可以使用,这里我们就介绍一种比较常见的场景,下载Excel模版,导入功能通常会配有一个模版下载的功能,根据下载的模版,填充数据然后再上传。
需求
如图所示用户下载如下模版,模版中的地区是从数据库中读出来的。
分析
如果模版都是固定的内容,这种比较简单,我们可以直接把模版上传到我们的文件服务器,用户下载的时候可以直接在文件服务器上下载,或者是把文件放到我们的工程里面,直接读取文件然后以流的方式返回给前端,用户直接下载。
这里比较麻烦点的就是这个模版的内容不是固定的,需要在库里查询出来,写到excel然后在返给前端。同时这个文件又有一定的样式,一般有样式的文件,我们可以根据模版填充,而不是把说有的样式都用程序代码写,程序代码写这种样式会很麻烦。所以我们这里用的方式就是填充模版
准备模版
把准备好的模版放到我们工程里面,创建文件夹template 并把文件夹设置为resource,设置为resoucre后该文件夹下面的文件在mvn打包的时候回合我们的java类打在同一个路径下,方便我们获取文件
编写下拉选项的handler
public class SelectSheetWriteHandler implements SheetWriteHandler {
private String[] list;
private int firstRow;
private int lastRow;
private int firstCol;
private int lastCol;
public SelectSheetWriteHandler(String[] list, int firstRow, int lastRow, int firstCol, int lastCol) {
this.list = list;
this.firstRow = firstRow;
this.lastRow = lastRow;
this.firstCol = firstCol;
this.lastCol = lastCol;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
// 区间设置 第一列第一行和第二行的数据。由于第一行是头,所以第一、二行的数据实际上是第二三行
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();
DataValidationConstraint constraint = helper.createExplicitListConstraint(list);
DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
dataValidation.setShowErrorBox(true);
writeSheetHolder.getSheet().addValidationData(dataValidation);
}
}
下载的方法
public void export(HttpServletResponse response) throws IOException {
//模板文件
InputStream templateFile = TenantInsuranceFundServiceImpl.class.getResourceAsStream("/五险一金导入模板.xlsx");
//导出后的文件名
String fileName = "五险一金导入模板";
List<BaseDictionaryVO> baseDictionaryVOS = dictionaryService.queryDictionaryDetail(DictionaryEnum.AREA.getCode(), DictionaryEnum.AREA.getName());
Set<String> areaNameList = new HashSet<>();
if(!CollectionUtils.isEmpty(baseDictionaryVOS)){
areaNameList = baseDictionaryVOS.stream().map(BaseDictionaryVO::getName).collect(Collectors.toSet());
}
SelectSheetWriteHandler selectSheetWriteHandler = new SelectSheetWriteHandler(areaNameList.toArray(new String[areaNameList.size()]), 2, 5000, 0, 0);
//写入
ExcelWriter excelWriter = null;
try {
//流输出
excelWriter = EasyExcel.write(response.getOutputStream())
.withTemplate(templateFile)
.registerWriteHandler(selectSheetWriteHandler)
.build();
WriteSheet writeSheet = EasyExcel.writerSheet().build();
// 直接写入list数据 这里没有数据需要填写直接一个空集合
excelWriter.fill(Arrays.asList(), writeSheet);
//浏览器下载操作
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码
fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
excelWriter.finish();
} catch (Exception e) {
// 重置response
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
Map<String, String> map = new HashMap<String, String>();
map.put("code", "1");
map.put("message", "下载模板失败" + e.getMessage());
response.getWriter().println(JSON.toJSONString(map));
} finally {
// 千万别忘记关闭流
if (excelWriter != null) {
excelWriter.finish();
}
}
}
以上的代码如果下拉框里面的可选项数据不多的话(一般50个以内),完全没有问题,在测试过程中发现如果下拉框里的数据选项比较多上百个,这个功能就失效了
下拉数据超出50限制
在原来的模板中新建一个sheet起名为dict,这个sheet 是用来专门写字典数据的,就是下拉框里面的数据,如果有几百条,在这里肯定是可以写下的没有限制{.name}这个是easyExcel固定的写法,表示集合填充A列的数据,如果没有"." 就表示对象填充{name}
在需要下拉框的位置引用A列的数据就可以实现我们想要的效果,数量也不会有50的限制.
代码的改动
public class SelectSheetWriteHandler implements SheetWriteHandler {
private String[] list;
private int firstRow;
private int lastRow;
private int firstCol;
private int lastCol;
private int length;
private String sheetDict;
public SelectSheetWriteHandler(String[] list, int firstRow, int lastRow, int firstCol, int lastCol,String sheetDict) {
this.list = list;
this.firstRow = firstRow;
this.lastRow = lastRow;
this.firstCol = firstCol;
this.lastCol = lastCol;
this.length = list.length;
this.sheetDict = sheetDict;
}
public SelectSheetWriteHandler(String[] list, int firstRow, int lastRow, int firstCol, int lastCol) {
new SelectSheetWriteHandler(list,firstRow,lastRow,firstCol,lastCol,null);
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
// 设置隐藏 sheet
WriteSheet writeSheet = writeSheetHolder.getWriteSheet();
if (writeSheet.getSheetNo() > 0) {
//如果是字典sheet设置隐藏
Workbook workbook = writeSheetHolder.getParentWriteWorkbookHolder().getWorkbook();
workbook.setSheetHidden(writeSheet.getSheetNo(), true);
return;
}else{
// 区间设置 第一列第一行和第二行的数据。由于第一行是头,所以第一、二行的数据实际上是第二三行
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();
DataValidationConstraint constraint = helper.createExplicitListConstraint(list);
DataValidation dataValidation;
if (length < 40) {
dataValidation = helper.createValidation(constraint, cellRangeAddressList);
}else{
//下拉选项数据大于40条 则引用字典sheet页的写入的数据项 从字典sheet页的第一行第一列到第一行第leng列
DataValidationConstraint formulaListConstraint = helper.createFormulaListConstraint(sheetDict + "!$A$1:$A$" + length);
dataValidation = helper.createValidation(formulaListConstraint, cellRangeAddressList);
}
dataValidation.setShowErrorBox(true);
writeSheetHolder.getSheet().addValidationData(dataValidation);
}
}
}
@Override
public void export(HttpServletResponse response) throws IOException {
//模板文件
InputStream templateFile = TenantInsuranceFundServiceImpl.class.getResourceAsStream("/五险一金导入模板.xlsx");
//导出后的文件名
String fileName = "五险一金导入模板";
List<BaseDictionaryVO> baseDictionaryVOS = dictionaryService.queryDictionaryDetail(DictionaryEnum.AREA.getCode(), DictionaryEnum.AREA.getName());
Set<String> areaNameList = new HashSet<>();
if(!CollectionUtils.isEmpty(baseDictionaryVOS)){
areaNameList = baseDictionaryVOS.stream().map(BaseDictionaryVO::getName).collect(Collectors.toSet());
}
SelectSheetWriteHandler selectSheetWriteHandler = new SelectSheetWriteHandler(areaNameList.toArray(new String[areaNameList.size()]), 2, 5000, 0, 0,"dict");
//写入
ExcelWriter excelWriter = null;
try {
//流输出
excelWriter = EasyExcel.write(response.getOutputStream())
.withTemplate(templateFile)
.registerWriteHandler(selectSheetWriteHandler)
.build();
//获取第一个sheet
WriteSheet writeSheet = EasyExcel.writerSheet(0).build();
//获取第二个sheet
WriteSheet citySheet = EasyExcel.writerSheet(1).build();
// citySheet 写入city值
excelWriter.fill(baseDictionaryVOS, citySheet);
//第一个sheet写入空数据
excelWriter.fill(Arrays.asList(), writeSheet);
//浏览器下载操作
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码
fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
excelWriter.finish();
} catch (Exception e) {
// 重置response
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
Map<String, String> map = new HashMap<String, String>();
map.put("code", "1");
map.put("message", "下载模板失败" + e.getMessage());
response.getWriter().println(JSON.toJSONString(map));
} finally {
// 千万别忘记关闭流
if (excelWriter != null) {
excelWriter.finish();
}
}
}