EasyExcel读写操作
- 写操作
- 读操作
1、实体类添加注解@ExcelProperty
,与写操作不同
@ExcelProperty(value="用户名称",index=1) // index=1意思是excle中的第2列,index=0是第一页
private String name;
2、写一个监听器(写操作不用),目的是一行一行读取excel内容
public class ExcelListener extends AnalysisEventListener<Dict>{
// 一行一行地读取excel内容,从第二行开始读取
@Override
public void invoke(Dict dict, AnalysisContext analysisContext) {
}
//获取excel表头信息
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
}
//读取之后执行
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
}
3、测试,实现读取
public static void main(String[] args) {
// 读取文件路径
String fileName = "F://excel/01.xlsx";
//调用方法实现读取
EasyExcel.read(fileName,Dict.class,new ExcelListener()).sheet().doRead();
}
运用:导出数据字典
导出:导出过程其实就是一个下载过程。所以可以先将数据转换成流,返回给前端下载
后端接口内容:
- 实体类DictVo
@Data
public class DictVo {
@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;
}
- 业务逻辑DictServiceImpl
//导出数据字典
@Override
public void exportDictData(HttpServletResponse response) {
//设置一些下载的头信息
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = "dict";
// Content-disposition:以下载的方式打开
// "attachment;fileName=dict.xlsx
response.setHeader("Content-disposition", "attachment;fileName=" + fileName + ".xlsx");
//查数据库
List<Dict> dictList = baseMapper.selectList(null);
// Dict 转成 DictVo
List<DictVo> dictVoList = new ArrayList<>();
for (Dict dict : dictList
) {
// 把每个dict复制到dictVo里面去
DictVo dictVo = new DictVo();
BeanUtils.copyProperties(dict, dictVo); // 把dict复制到dictVo中去,
//等价于
// dictVo.setId(dict.getId());
dictVoList.add(dictVo);
}
// 写到excel中
try {
EasyExcel.write(response.getOutputStream(), DictVo.class).sheet("dict")
.doWrite(dictVoList);
} catch (IOException e) {
e.printStackTrace();
}
}
- 前端调用下载接口
<el-button type="success" size="mini" @click="exportDict">导出</el-button>
// 导出数据字典
exportDict() {
// 调用导出接口(相当于下载)
window.location.href = 'http://localhost:8202/admin/cmn/dict/exportDict'
}
运用:导入数据字典
后端接口内容:
- 实体类Dict:
@Data
@ApiModel(description = "数据字典")
@TableName("dict")
public class Dict {
private static final long serialVersionUID = 1L;
@ApiModelProperty(value = "id")
private Long id;
@ApiModelProperty(value = "创建时间")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@TableField("create_time")
private Date createTime;
@ApiModelProperty(value = "更新时间")
@TableField("update_time")
private Date updateTime;
@ApiModelProperty(value = "逻辑删除(1:已删除,0:未删除)")
@TableLogic
@TableField("is_deleted")
private Integer isDeleted;
@ApiModelProperty(value = "其他参数")
@TableField(exist = false)
private Map<String,Object> param = new HashMap<>();
@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;
- 业务逻辑DictServiceImpl
// 导入数据字典
@Override
public void importDictData(MultipartFile file) {
try {
EasyExcel.read(file.getInputStream(), DictVo.class, new ExcelListener(dictMapper))// 也可以传baseMapper
.sheet().doRead();
} catch (IOException e) {
e.printStackTrace();
}
}
- 监听器ExcelListener,在这里将读取到的数据添加到数据库中
public class ExcelListener extends AnalysisEventListener<DictVo>{
private DictMapper dictMapper;
public ExcelListener(DictMapper dictMapper) { // 相当于构造器注入
this.dictMapper = dictMapper;
}
// 一行一行地读取excel内容,从第二行开始读取,并将数据加入到数据库中
@Override
public void invoke(DictVo dictVo, AnalysisContext analysisContext) {
// 调用方法添加到数据库中
Dict dict = new Dict();
BeanUtils.copyProperties(dictVo,dict);
dictMapper.insert(dict);
}
//获取excel表头信息
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
}
//读取之后执行
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
}
前端代码:
- 添加element上传弹层
:visible.sync="dialogImportVisible"
dialogImportVisible=true时,弹框才打开
<el-button type="success" size="mini" @click="importDict">导入</el-button>
<el-dialog title="导入" :visible.sync="dialogImportVisible" width="480px">
<el-form label-position="right" label-width="170px">
<el-form-item label="文件">
<el-upload
:multiple="false"
:on-success="onUploadSuccess"
:action="'http://localhost:8202/admin/cmn/dict/importDict'"
class="upload-demo"
>
<el-button size="small" type="primary">点击上传</el-button>
<div slot="tip" class="el-upload__tip">只能上传excel文件,且不超过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>
- 方法调用部分
data() {
return {
dialogImportVisible: false, // 设置弹框是否弹出
dictList: []
}
},
methods: {
// 导入数据字典
importDict() {
this.dialogImportVisible = true
},
// 上传成功调用
onUploadSuccess() {
// 关闭弹框
this.dialogImportVisible = false
// 刷新页面
this.getDictList(1)
}
}