1、模板实体类
@Data
public class SysUserImportExcel {
private static final String bigTitle= "填写须知: \n" +
"1.第1、2行为固定结构,不可更改;以下示例行,导入前请先删除\n" +
"2.请严格按照填写规则输入数据,不合规的数据无法成功导入";
@ExcelProperty(value = {bigTitle,"姓名(必填)"}, index = 0)
private String userName;
@ExcelProperty(value = {bigTitle,"性别(必填)"}, index = 1)
private String userSexName;
@ExcelProperty(value = {bigTitle,"手机号码(必填)"}, index = 2)
private String userMobile;
@ExcelProperty(value = {bigTitle,"出生年月(必填)"}, index = 3)
private Date userBirthday;
@ExcelProperty(value = {bigTitle,"工作单位(必填)"}, index = 4)
private String deptName;
@ExcelProperty(value = {bigTitle,"职务(必填)"}, index = 5)
private String unitPosition;
@ExcelProperty(value = {bigTitle,"干部类别(必填)"}, index = 6)
private String leaderTypeName;
@ExcelProperty(value = {bigTitle,"用户状态(必填)"}, index = 7)
private String userStatusName;
}
多行表头方法二:
/**
* 设置模板表格的表头
* @return
*/
private List<List<String>> getMorningCheckHead(){
String bigTitle= "填写须知: \n" +
"1.第1、2行为固定结构,不可更改;以下示例行,导入前请先删除\n" +
"2.请严格按照填写规则输入数据,不合规的数据无法成功导入";
List<List<String>> head = new ArrayList<List<String>>();
List<String> head0 = new ArrayList<>();
head0.add(bigTitle);
head0.add("姓名(必填)");
List<String> head1 = new ArrayList<>();
head1.add(bigTitle);
head1.add("性别(必填)");
List<String> head2 = new ArrayList<>();
head2.add(bigTitle);
head2.add("手机号码(必填)");
List<String> head3 = new ArrayList<>();
head3.add(bigTitle);
head3.add("出生年月(必填)");
List<String> head4 = new ArrayList<>();
head4.add(bigTitle);
head4.add("工作单位(必填)");
List<String> head5 = new ArrayList<>();
head5.add(bigTitle);
head5.add("职务(必填)");
List<String> head6 = new ArrayList<>();head.add(head0);
head.add(head1);
head.add(head2);
head.add(head3);
head.add(head4);
head.add(head5);
return head;
}
}
2、自定义下拉框
@Data
@Slf4j
public class SysUserWriteHandler implements SheetWriteHandler {
private Map<String, Map<String, String>> paramMap = null;
public SysUserWriteHandler() {}
public SysUserWriteHandler(Map<String, Map<String, String>> paramMap) {
this.paramMap = paramMap;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
//性别
Map<String, String> userSexMap = paramMap.get(SysParamConstants.USER_SEX);
//过滤key为-1的值
Map<String, String> newUserSexMap = userSexMap.entrySet().stream().filter(map -> ObjectUtil.notEqual(CommonConstants.TOP_KEY, map.getKey()))
.collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue));
//名称转成数组
List<String> userSexList = newUserSexMap.values().stream().collect(Collectors.toList());
String[] userSex = userSexList.toArray(new String[userSexList.size()]);
//干部类别
Map<String, String> leaderTypeMap = paramMap.get(SysParamConstants.USER_LEADER_TYPE);
//过滤key为-1的值
Map<String, String> newLeaderTypeMap = leaderTypeMap.entrySet().stream().filter(map -> ObjectUtil.notEqual(CommonConstants.TOP_KEY, map.getKey()))
.collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue));
//名称转成数组
List<String> leaderTypeList = newLeaderTypeMap.values().stream().collect(Collectors.toList());
String[] leaderType = leaderTypeList.toArray(new String[leaderTypeList.size()]);
Map<Integer,String[]> mapDropDown = new HashMap<>();
mapDropDown.put(1,userSex); //性别
mapDropDown.put(6,leaderType); //干部类别
Sheet sheet = writeSheetHolder.getSheet();
//设置下拉框
DataValidationHelper helper = sheet.getDataValidationHelper();
mapDropDown.forEach((k, v) -> {
// 下拉列表约束数据
DataValidationConstraint constraint = helper.createExplicitListConstraint(v);
// 设置下拉单元格的首行 末行 首列 末列
CellRangeAddressList rangeList = new CellRangeAddressList(2, 65536, k, k);
// 设置约束
DataValidation validation = helper.createValidation(constraint, rangeList);
// 阻止输入非下拉选项的值
validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
validation.setShowErrorBox(true);
validation.setSuppressDropDownArrow(true);
validation.createErrorBox("提示","此值与单元格定义格式不一致");
// validation.createPromptBox("填写说明:","填写内容只能为下拉数据集中的单位,其他单位将会导致无法入仓");
sheet.addValidationData(validation);
});
}
}
3、导出模板工具类
/**
* 导出excel模板
* @param response
* @param fileName
* @param sheetName
* @param model
* @throws Exception
*/
public static HttpServletResponse exportExcelModel(HttpServletResponse response, String fileName, String sheetName,List<? extends Object> data, Class<?> model, SheetWriteHandler sheetWriteHandler) throws Exception {
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 设置表头居中对齐
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
// 颜色
headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 10);
// 字体
headWriteCellStyle.setWriteFont(headWriteFont);
headWriteCellStyle.setWrapped(true);
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 设置内容靠中对齐
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
// 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
EasyExcel.write(getOutputStream(fileName, response), model).excelType(ExcelTypeEnum.XLSX).sheet(sheetName)
.registerWriteHandler(horizontalCellStyleStrategy).registerWriteHandler(sheetWriteHandler).doWrite(data);
return null;
}
/**
* 导出文件时为Writer生成OutputStream.
*
* @param fileName 文件名
* @param response response
* @return ""
*/
private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
try {
fileName = new String(fileName.getBytes("utf-8"), "ISO8859-1");
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setCharacterEncoding("utf8");
response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");
response.setHeader("Pragma", "public");
response.setHeader("Cache-Control", "no-store");
response.addHeader("downloadName", fileName + ".xlsx");
response.addHeader("Cache-Control", "max-age=0");
return response.getOutputStream();
} catch (IOException e) {
throw new IOException("导出excel表格失败!", e);
}
}
4、下载模板方法:
public void downloadExcelModel(HttpServletResponse response) {
String fileName = "导入模板";
String sheetName = "模板";
try {
List<SysUserImportExcel> sysUserImportExcelList = getSysUserImportExcel();
//获取数字字典
Map<String, Map<String, String>> paramMap = redisService.mget(CommonConstants.CacheKey.REIDS_SYS_PARAM_DATA);
//输出文件流
EasyExcelUtil.exportExcelModel(response,fileName,sheetName,sysUserImportExcelList,SysUserImportExcel.class,new SysUserWriteHandler(paramMap));
}catch (Exception e){
e.printStackTrace();
throw new GlobalException(ExceptionCodeEnum.FAIL.getCode(), "下载导入模板失败");
}
}
5、前端处理
1、点击事件:
<butn btntype="primary" title="下载模板" @click="downloadData"></butn>
2、请求js:/* 下载导入用户模板 */
export function downloadExcelModel(params) {
return request({
url: `/admin/sys/user/downloadModel`,
method: "put",
responseType: "blob",
headers: {
Accept: "application/json, text/plain, */*, application/octet-stream",
"Content-Type": "application/json;charset=UTF-8"
},
data: params
});
}
3、下载模板方法
downloadData() {
downloadExcelModel().then(response => {
try {
let jsonData = JSON.parse(this.result);
console.log(jsonData);
if (jsonData.code) {
this.$message({
message: jsonData.msg,
type: "error"
});
}
} catch (e) {
let blob = new Blob([response.data]); //创建一个blob对象
const uA = window.navigator.userAgent;
const isIE =
/msie\s|trident\/|edge\//i.test(uA) &&
!!(
"uniqueID" in document ||
"documentMode" in document ||
"ActiveXObject" in window ||
"MSInputMethodContext" in window
);
let a = document.createElement("a"); //创建一个<a></a>标签
let href = window.URL.createObjectURL(blob); // response is a blob
a.href = href;
let title = decodeURI(escape(response.headers.downloadname)); //文件名称
a.download = title;
a.style.display = "none";
document.body.appendChild(a);
if (isIE) {
// 兼容IE11无法触发下载的问题
navigator.msSaveBlob(blob, title);
} else {
a.click();
}
document.body.removeChild(a); // 下载完成移除元素
window.URL.revokeObjectURL(href); // 释放url
}
});
},