目录
1、导出excel
1.1、导入依赖
<!-- easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.1</version>
</dependency>
1.2、编写需要导出对象的实体类
原实体类Dict:
@Data
@ApiModel(description = "数据字典")
@TableName("dict")
public class Dict extends BaseEntity {
private static final long serialVersionUID = 1L;
@ApiModelProperty(value = "上级id")
@TableField("parent_id")
private Long parentId;
@ApiModelProperty(value = "名称")
@TableField("name")
private String name;
@ApiModelProperty(value = "值")
@TableField("value")
private String value;
@ApiModelProperty(value = "编码")
@TableField("dict_code")
private String dictCode;
@ApiModelProperty(value = "是否包含子节点")
@TableField(exist = false)
private boolean hasChildren;
}
导出实体类DictEeVo:
@Data
public class DictEeVo {
@ExcelProperty(value = "id" ,index = 0)
private Long id;
@ExcelProperty(value = "上级id" ,index = 1)
private Long parentId;
@ExcelProperty(value = "名称" ,index = 2)
private String name;
@ExcelProperty(value = "值" ,index = 3)
private String value;
@ExcelProperty(value = "编码" ,index = 4)
private String dictCode;
}
1.3、编写service
导出接口:
/**
* 导出
* @param response
*/
void exportData(HttpServletResponse response);
实现类:
/**
* excel导出
* @param response
*/
@Override
public void exportData(HttpServletResponse response) {
try {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode("数据字典", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename="+ fileName + ".xlsx");
//查询需要导出的数据
List<Dict> dictList = dictMapper.selectList(null);
//vo
List<DictEeVo> dictVoList = new ArrayList<>(dictList.size());
//将dict复制到dictVo
for(Dict dict : dictList) {
DictEeVo dictVo = new DictEeVo();
BeanUtils.copyProperties(dict, dictVo, DictEeVo.class);
dictVoList.add(dictVo);
}
EasyExcel.write(response.getOutputStream(), DictEeVo.class).sheet("数据字典").doWrite(dictVoList);
} catch (IOException e) {
e.printStackTrace();
}
}
1.4、编写controller
@ApiOperation(value="导出")
@GetMapping(value = "/exportData")
public void exportData(HttpServletResponse response) {
dictService.exportData(response);
}
1.5、编写前端页面
按钮:
<el-button type="primary" @click="exportData">导出</el-button>
导出方法:
//导出excel
exportData() {
window.location.href = "http://localhost:8202/dict/exportData";
},
1.6、测试
2、导入excel
2.1、创建回调监听器
package com.rk.cmn.listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.rk.cmn.service.DictService;
import com.rk.hosp.model.cmn.Dict;
import com.rk.hosp.vo.cmn.DictEeVo;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeanUtils;
import java.util.ArrayList;
import java.util.List;
/**
* @author :Rk.
* @date : 2022/11/4
*/
// 有个很重要的点 DictEeVoListener 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去 构造方法注入
@Slf4j
public class DickListener extends AnalysisEventListener<DictEeVo> {
/**
* 每隔5条存储数据库,实际使用中可以100条,然后清理list ,方便内存回收
*/
private static final int BATCH_COUNT = 100;
/**
* 缓存的数据
*/
private List<DictEeVo> cachedDataList =new ArrayList<>(BATCH_COUNT);
/**
* 真正存储的集合
*/
List<Dict> list= new ArrayList<>();
private DictService dictService;
public DickListener(DictService dictService) {
this.dictService = dictService;
}
/**
* 这个每一条数据解析都会来调用
*/
@Override
public void invoke(DictEeVo dictEeVo, AnalysisContext context) {
cachedDataList.add(dictEeVo);
//需要将dictEeVo对象转换为dict 才能存储
Dict dict=new Dict();
BeanUtils.copyProperties(dictEeVo,dict);
list.add(dict);
// 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
if (cachedDataList.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
cachedDataList.clear();
list.clear();
}
}
/**
* 所有数据解析完成了 都会来调用
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据,确保最后遗留的数据也存储到数据库
saveData();
log.info("所有数据解析完成!");
}
/**
* 加上存储数据库
*/
private void saveData() {
log.info("{}条数据,开始存储数据库!", list.size());
dictService.saveBatch(list);
log.info("存储数据库成功!");
}
}
2.2、创建接口
@ApiOperation(value = "导入")
@PostMapping("importData")
public Result importData(MultipartFile file) throws IOException {
//0代表读取excel的sheet1
EasyExcel.read(file.getInputStream(), DictEeVo.class,new DickListener(dictService)).sheet(0).doRead();
return Result.ok();
}
2.3、前端上传按钮和上传弹窗
按钮:
<el-button type="success" @click="importData"><i class="fa fa-plus" /> 导入</el-button>
弹窗:
<el-dialog title="导入" :visible.sync="dialogImportVisible" width="480px">
<el-form>
<el-form-item label="文件">
<el-upload
class="upload-demo"
:on-success="onUploadSuccess"
:on-error="onUploadError"
:show-file-list="false"
action="http://localhost:8202/admin/cmn/dict/importData"
:multiple="false"
>
<el-button size="small" type="primary">点击上传</el-button>
<div slot="tip" class="el-upload__tip">只能上传xls文件,且不超过500kb</div>
</el-upload>
</el-form-item>
</el-form>
<div slot="footer" class="dialog-footer">
<el-button @click="dialogImportVisible = false"> 取消 </el-button>
</div>
</el-dialog>
2.4、前端上传方法
//导入ecxel
importData(){
//弹出上传窗口
this.dialogImportVisible=true;
},
//文件上传成功调用
onUploadSuccess(){
this.$message({
type: "success",
message: "导入成功!",
});
this.dialogImportVisible = false
this.getChlidData(1);
},
//文件上传失败调用
onUploadError(){
this.$message({
type: "error",
message: "导入失败!",
});
this.dialogImportVisible = false
this.fetchData()
},
2.5、测试
将下面的excel表格上传