使用easyexcel框架下载模板
使用让easyexcel导出带下拉框的excel动态模板。不涉及数据导出,不涉及前端代码。
Controller
@PostMapping("/exportUserTemplate")
public void downloadTemplate(HttpServletRequest request, HttpServletResponse response) {
String fileName="xxx.xlsx";
response.setContentType("multipart/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
try {
fileName = URLEncoder.encode(fileName,"UTF-8");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
response.setHeader("Content-disposition","attachment;filename="+fileName);
List<UserDownloadTemplateVO> userDownloadTemplateVOList = new UserDownloadTemplateVOList();
try {
EasyExcel.write(response.getOutputStream(),UserDownloadTemplateVO.class)
.registerWriteHandler(new CustomSheetWriteHandler(xxxx))
.sheet("xxx")
.doWrite(userDownloadTemplateVOList);
} catch (IOException e) {
e.printStackTrace();
}
}
拦截器
public class CustomSheetWriteHandler implements SheetWriteHandler {
private List<String> list;
public CustomSheetWriteHandler(List<String> list) {
this.list= list;
}
public CustomSheetWriteHandler() {
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();
Map<Integer, String[]> mapDropDown = new HashMap<>();
String[] listArray= new String[list.size()];
for (int i = 0; i < list.size(); i++) {
listArray[i] = list.get(i);
}
mapDropDown.put(9, listArray);
Workbook workbook = writeWorkbookHolder.getWorkbook();
String sheetName = "hidden";
Sheet providerSheet = workbook.createSheet(sheetName);
workbook.setSheetHidden(1,true);
for (Map.Entry<Integer, String[]> entry : mapDropDown.entrySet()) {
CellRangeAddressList addressList = new CellRangeAddressList(1, 65535, entry.getKey(), entry.getKey());
String[] values = entry.getValue();
for (int i = 0, length = values.length; i < length; i++) {
Row row = providerSheet.getRow(i);
if (row == null) {
row = providerSheet.createRow(i);
}
row.createCell(entry.getKey()).setCellValue(values[i]);
}
String excelLine = getExcelLine(entry.getKey());
String refers = "=" + sheetName + "!$" + excelLine + "$1:$" + excelLine + "$" + (values.length);
DataValidationConstraint constraint = helper.createFormulaListConstraint(refers);
DataValidation dataValidation = helper.createValidation(constraint, addressList);
writeSheetHolder.getSheet().addValidationData(dataValidation);
}
}
public static String getExcelLine(int num) {
String line = "";
int first = num / 26;
int second = num % 26;
if (first > 0) {
line = (char) ('A' + first - 1) + "";
}
line += (char) ('A' + second) + "";
return line;
}
}
1、如果导出的excel是由多个下拉框的,也可通过新建一个数据然后添加进map。 2、实体类需自己根据需求来,以及动态生成的下拉框也需根据需来。 3、上述导出代码是涉及动态模板导出,不涉及到导出数据(可以理解为此次是下载模板操作) 代码过程中遇到的问题: ps:问题1:下拉框字数255字符限制问题。 已通过添加隐藏sheet解决 问题2:easyexcel添加拦截器后,用excel打开会有提示部分内容有问题,用 wps打开则没事。 已解决。
|