一、准备
1.pom.xml引入easyexcel依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.9</version>
</dependency>
2.pom.xml引入poi依赖，poi版本对应引入的easyexcel中poi依赖的版本
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
二、导入
1.准备一个导入实体类
@ContentRowHeight(15)
@HeadRowHeight(22)
@ColumnWidth(15)
@ApiModel
public class RuleIndexImportDTO {
@ApiModelProperty(value = "指标名称")
@ExcelProperty(value = "指标名称*", index = 0)
private String indexName;
@ApiModelProperty(value = "指标编码")
@ExcelProperty(value = "指标编码*", index = 1)
private String indexCode;
@ApiModelProperty(value = "指标类型")
@ExcelProperty(value = "指标类型*", index = 2)
private String dataType;
@ApiModelProperty(value = "指标形式")
@ExcelProperty(value = "指标形式*", index = 3)
private String indexAct;
@ColumnWidth(50)
@ApiModelProperty(value = "指标描述")
@ExcelProperty(value = "指标描述*", index = 4)
private String indexDesc;
public String getIndexName() {
return indexName;
}
public void setIndexName(String indexName) {
this.indexName = indexName;
}
public String getIndexCode() {
return indexCode;
}
public void setIndexCode(String indexCode) {
this.indexCode = indexCode;
}
public String getDataType() {
return dataType;
}
public void setDataType(String dataType) {
this.dataType = dataType;
}
public String getIndexAct() {
return indexAct;
}
public void setIndexAct(String indexAct) {
this.indexAct = indexAct;
}
public String getIndexDesc() {
return indexDesc;
}
public void setIndexDesc(String indexDesc) {
this.indexDesc = indexDesc;
}
}
2.编写导入模板接口相关代码
(1).controller层代码
@GetMapping("/template")
@ApiOperation("下载指标导入模板")
public void downloadIndexTemplate(HttpServletResponse response) {
ruleIndexService.downloadIndexTemplate(response);
}
(2).service层代码
/**
* 下载指标导入模板
*
* @param response
*/
void downloadIndexTemplate(HttpServletResponse response);
/**
* 下载指标导入模板
*
* @param response
*/
@Override
public void downloadIndexTemplate(HttpServletResponse response) {
try {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("指标导入模板", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
//查询字典数据
EasyExcel.write(response.getOutputStream())
.head(RuleIndexImportDTO.class)
.registerWriteHandler(new SelectSheetWriteHandler())
.sheet("指标导入模板")
.doWrite(new ArrayList<>());
} catch (IOException e) {
e.printStackTrace();
}
}
3.编写导入接口相关代码
(1).controller层代码
@PostMapping("/{indexSetId}/import")
@ApiOperation("导入指标")
public int importIndex(@PathVariable("indexSetId") @ApiParam("指标集id") Integer indexSetId,
HttpServletRequest request) {
if (null == indexSetId) {
throw new RuleException("指标集id不能为空");
}
StandardServletMultipartResolver resolver = new StandardServletMultipartResolver();
MultipartHttpServletRequest multipart = resolver.resolveMultipart(request);
Map<String, MultipartFile> fileMap = multipart.getFileMap();
if (MapUtils.isEmpty(fileMap)) {
throw new RuleException("未找到要上传的文件!");
}
return ruleIndexService.importIndex(indexSetId, fileMap);
}
(2).service层代码
/**
* 导入指标
*
* @param indexSetId
* @param fileMap
* @return
*/
@Transactional(rollbackFor = Exception.class)
int importIndex(Integer indexSetId, Map<String, MultipartFile> fileMap);
/**
* 导入指标
*
* @param indexSetId
* @param fileMap
* @return
*/
@Override
public int importIndex(Integer indexSetId, Map<String, MultipartFile> fileMap) {
int result = 1;
if (fileMap.size() > 1) {
throw new RuleException("只支持导入单个文件！");
}
MultipartFile file = null;
for (Map.Entry<String, MultipartFile> temp : fileMap.entrySet()) {
file = temp.getValue();
}
String fileName = file.getOriginalFilename();
if (StringUtils.isNotBlank(fileName) && !fileName.endsWith(".xlsx")) {
throw new RuleException("文件格式不正确！");
}
// 检查模板是否正确
try {
EasyExcel.read(file.getInputStream(), RuleIndexImportDTO.class, new EasyExcelListener()).sheet().doRead();
} catch (Exception e) {
throw new RuleException("模板错误，请检查导入模板！");
}
List<RuleIndexImportDTO> list = null;
try {
list = EasyExcel.read(file.getInputStream())
.head(RuleIndexImportDTO.class)
.sheet(0)
.doReadSync();
} catch (IOException e) {
throw new RuleException("文件解析失败！");
}
if (!CollectionUtils.isEmpty(list)) {
// 判断数据是否有重复
// 判断指标名称是否有重复
Map<String, List<RuleIndexImportDTO>> indexNameMap = list.stream()
.collect(Collectors.groupingBy(RuleIndexImportDTO::getIndexName));
for (Map.Entry<String, List<RuleIndexImportDTO>> entry : indexNameMap.entrySet()) {
if (!CollectionUtils.isEmpty(entry.getValue()) && entry.getValue().size() > 1) {
RuleIndexImportDTO index = entry.getValue().get(0);
throw new RuleException("指标名称 [ " + index.getIndexName() + " ] 重复!");
}
}
// 判断指标编码是否重复
Map<String, List<RuleIndexImportDTO>> indexCodeMap = list.stream()
.collect(Collectors.groupingBy(RuleIndexImportDTO::getIndexCode));
for (Map.Entry<String, List<RuleIndexImportDTO>> entry : indexCodeMap.entrySet()) {
if (!CollectionUtils.isEmpty(entry.getValue()) && entry.getValue().size() > 1) {
RuleIndexImportDTO index = entry.getValue().get(0);
throw new RuleException("指标编码 [ " + index.getIndexCode() + " ] 重复!");
}
}
RuleIndexSet indexSet = ruleIndexSetDao.queryById(indexSetId);
if (Objects.isNull(indexSet)) {
throw new RuleException("指标集不存在！");
}
List<RuleIndex> indices = new ArrayList<>();
for (RuleIndexImportDTO importedIndex : list) {
// 校验
importedIndex.checkParam();
// 保证指标名称和指标编码唯一
RuleIndex indexName = new RuleIndex();
indexName.setName(importedIndex.getIndexName());
indexName.setIndexSetId(indexSetId);
indexName.setEnable(RuleIndex.Constant.ENABLE);
RuleIndex indexByName = ruleIndexDao.queryIndex(indexName);
RuleIndex indexCode = new RuleIndex();
indexCode.setCode(importedIndex.getIndexCode());
indexCode.setIndexSetId(indexSetId);
indexCode.setEnable(RuleIndex.Constant.ENABLE);
RuleIndex indexByCode = ruleIndexDao.queryIndex(indexCode);
if (Objects.nonNull(indexByName) || Objects.nonNull(indexByCode)) {
continue;
}
RuleIndex ruleIndex = new RuleIndex();
ruleIndex.setId(null);
ruleIndex.setCode(importedIndex.getIndexCode());
ruleIndex.setName(importedIndex.getIndexName());
ruleIndex.setDataType(importedIndex.getDataType());
ruleIndex.setIndexAct(importedIndex.getIndexAct());
ruleIndex.setIndexSetId(indexSetId);
ruleIndex.setIndexDesc(importedIndex.getIndexDesc());
ruleIndex.setCreateBy(RuleIndex.Constant.USER_NAME);
ruleIndex.setCreateTime(new Date());
ruleIndex.setUpdateBy(RuleIndex.Constant.USER_NAME);
ruleIndex.setUpdateTime(new Date());
ruleIndex.setEnable(RuleIndex.Constant.ENABLE);
indices.add(ruleIndex);
}
// 批量新增
if (!CollectionUtils.isEmpty(indices)) {
ruleIndexDao.insertBatch(indices);
}
log.info("导入完成");
log.info("本次共导入" + indices.size() + "条指标数据");
} else {
throw new RuleException("导入的数据不能为空！");
}
return result;
}
(3).dao层代码
dao层省略
(4).SelectSheetWriteHandler实现SheetWriteHandler接口重写afterSheetCreate方法实现导入模板中的下拉选择框
public class SelectSheetWriteHandler implements SheetWriteHandler {
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
/** CHECKSTYLE:OFF:MagicNumber */
Map<Integer, String[]> map = new HashMap<>();
// 指标类型
String[] dataType = {"String", "Integer", "Enum", "Date", "Boolean", "BigDecimal", "List"};
// 指标形式
String[] indexAct = {"In", "Out", "InOut"};
// key->需要添加下拉框的列 value->下拉框中数据
map.put(2, dataType);
map.put(3, indexAct);
// 获取工作簿
Sheet sheet = writeSheetHolder.getSheet();
// 设置下拉框
DataValidationHelper helper = sheet.getDataValidationHelper();
for (Map.Entry<Integer, String[]> entry : map.entrySet()) {
// 表头不设置下拉框
CellRangeAddressList addressList = new CellRangeAddressList(1, 65535, entry.getKey(), entry.getKey());
// 设置下拉框数据
DataValidationConstraint constraint = helper.createExplicitListConstraint(entry.getValue());
DataValidation dataValidation = helper.createValidation(constraint, addressList);
sheet.addValidationData(dataValidation);
}
/** CHECKSTYLE:ON:MagicNumber*/
}
}
效果如下：
(5).EasyExcelListener继承AnalysisEventListener重写invokeHeadMap方法实现对模板的校验
public class EasyExcelListener extends AnalysisEventListener<RuleIndexImportDTO> {
@Override
public void invoke(RuleIndexImportDTO ruleIndexImportDTO, AnalysisContext analysisContext) {
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
int count = 0;
Field[] fields = RuleIndexImportDTO.class.getDeclaredFields();
// 遍历字段进行判断
for (Field field : fields) {
// 获取字段上ExcelProperty注解信息
ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
if (null != annotation) {
++count;
String headName = headMap.get(annotation.index());
// 如果表头为空或者表头和当前设置的表头名称不同这模板错误
if (StringUtils.isBlank(headName) || !headName.equals(annotation.value()[0])) {
throw new RuleException("模板错误，请检查导入模板！");
}
}
}
if (count != headMap.size()) {
throw new RuleException("模板错误，请检查导入模板！");
}
}
}
三、导出
1.准备一个导出实体类
@ContentRowHeight(15)
@HeadRowHeight(22)
@ColumnWidth(15)
@ApiModel
public class RuleIndexExportDTO {
@ApiModelProperty(value = "指标集名称")
@ExcelProperty(value = "指标集名称", index = 0)
private String indexSetName;
@ApiModelProperty(value = "指标名称")
@ExcelProperty(value = "指标名称", index = 1)
private String indexName;
@ApiModelProperty(value = "指标编码")
@ExcelProperty(value = "指标编码", index = 2)
private String indexCode;
@ApiModelProperty(value = "指标类型")
@ExcelProperty(value = "指标类型", index = 3)
private String dataType;
@ApiModelProperty(value = "指标形式")
@ExcelProperty(value = "指标形式", index = 4)
private String indexAct;
@ColumnWidth(50)
@ApiModelProperty(value = "指标描述")
@ExcelProperty(value = "指标描述", index = 5)
private String indexDesc;
public String getIndexSetName() {
return indexSetName;
}
public void setIndexSetName(String indexSetName) {
this.indexSetName = indexSetName;
}
public String getIndexName() {
return indexName;
}
public void setIndexName(String indexName) {
this.indexName = indexName;
}
public String getIndexCode() {
return indexCode;
}
public void setIndexCode(String indexCode) {
this.indexCode = indexCode;
}
public String getDataType() {
return dataType;
}
public void setDataType(String dataType) {
this.dataType = dataType;
}
public String getIndexAct() {
return indexAct;
}
public void setIndexAct(String indexAct) {
this.indexAct = indexAct;
}
public String getIndexDesc() {
return indexDesc;
}
public void setIndexDesc(String indexDesc) {
this.indexDesc = indexDesc;
}
}
2.编写导出接口相关代码
(1).controller层代码
@GetMapping("/{indexSetId}/export")
@ApiOperation("导出指标")
public void exportIndex(@PathVariable("indexSetId") @ApiParam("指标集id") Integer indexSetId,
HttpServletResponse response) {
if (null == indexSetId) {
throw new RuleException("指标集id不能为空");
}
ruleIndexService.exportIndex(indexSetId, response);
}
(2).service层代码
/**
* 导出指标
*
* @param indexSetId
* @param response
*/
void exportIndex(Integer indexSetId, HttpServletResponse response);
/**
* 导出指标
*
* @param indexSetId
* @param response
*/
@Override
public void exportIndex(Integer indexSetId, HttpServletResponse response) {
RuleIndexSet indexSet = ruleIndexSetDao.queryById(indexSetId);
if (Objects.isNull(indexSet)) {
throw new RuleException("指标集不存在！");
}
String fileName = indexSet.getName() + "指标集数据";
List<RuleIndexExportDTO> indexExportDTOS = ruleIndexDao.queryByIndexSetId(indexSetId);
try {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream())
.head(RuleIndexExportDTO.class)
.sheet(indexSet.getName() + "指标集数据")
.doWrite(indexExportDTOS);
} catch (IOException e) {
e.printStackTrace();
}
}
(3).dao层代码
dao层代码省略
