大家好,我是walker
一个从文科自学转行的程序员~
爱好编程,偶尔写写编程文章和生活
欢迎关注公众号【I am Walker】,回复“电子书”,就可以获得200多本编程相关电子书哈~
我的gitee:https://gitee.com/shen-chuhao/walker.git 里面很多技术案例!
依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.0.5</version>
</dependency>
知识要点
@ExcelProperty
- 用于映射类的属性和excel表行的属性的一个注解
EasyExcel.read
//inputStream :输入流
//head 对应的类
//readListener 监听器
public static ExcelReaderBuilder read(InputStream inputStream, Class head, ReadListener readListener)
场景,案例
导入一批数据进行处理,最后输出是否成功失败
1、创建对应的excel类
excel的数据格式
package cn.trussan.business.account.model.data;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
@Data
public class MemberExcelData {
//这里的value要和excel定义的一致
@ExcelProperty(value = "客户真实姓名(主沟通人姓名)")
private String name;
@ExcelProperty(value = "unionid")
private String unionid;
@ExcelProperty(value = "主沟通人身份证号码")
private String idcard;
@ExcelProperty(value = "客户手机号")
private String tel;
@ExcelProperty(value = "是否成功")
private String success;
}
2、编写监听器 listener
package cn.trussan.business.account.common.listener;
import cn.trussan.business.account.common.utils.ExcelUtil;
import cn.trussan.business.account.component.MemberComponent;
import cn.trussan.business.account.model.data.MemberExcelData;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.mashape.unirest.http.exceptions.UnirestException;
import lombok.extern.slf4j.Slf4j;
import java.util.ArrayList;
import java.util.List;
@Slf4j
//1、继承AnalysisEventListener
public class MemberExcelDataListener extends AnalysisEventListener<MemberExcelData> {
//2、需要引进一个service或者component,用于编写逻辑方法
private MemberComponent memberComponent;
//3、构造方法
public MemberExcelDataListener(MemberComponent memberComponent) {
this.memberComponent = memberComponent;
}
//这里我是用来接收处理之后的返回结果,主要用来重新打印是否处理成功或者失败,可根据实际情况而定,也可以不需要
List<MemberExcelData> memberExcelDataList=new ArrayList<>();
//4、实现invoke方法、和doAfterAllAnalysed方法
@Override
public void invoke(MemberExcelData memberExcelData, AnalysisContext analysisContext) {
try {
log.info("读取excel数据:{}",memberExcelData);
//逻辑可以写在这个方法里面
memberComponent.importMember(memberExcelData);
memberExcelDataList.add(memberExcelData);
} catch (UnirestException e) {
log.info("importMember 执行失败");
}
}
// 数据处理之后
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
log.info("处理之后返回数据:{}",JSON.toJSONString(memberExcelDataList));
//导出excel文件到根目录下。
ExcelUtil.export2File(this.getClass().getResource("/").getPath(),
"会员导入执行结果","执行情况",
MemberExcelData.class
,memberExcelDataList);
}
}
生成在这个文件目录下
3、创建controller
@IgnoreAuthToken
@PostMapping("/importMember")
//1、使用MultipartFile进行接收对象
public R importMember(MultipartFile multipartFile,String key) throws IOException {
String mykey="dsadsadasxz1";
if(!mykey.equals(key)) throw new PlbException("key错误");
//2、使用EasyExcel.read()读取excel文件内容
EasyExcel.read(multipartFile.getInputStream(),
MemberExcelData.class,
new MemberExcelDataListener(memberComponent))
.sheet().doRead();
return R.ok();
}
MurtipartFile类可以使用Postman进行传输文件,进行测试
Excel工具类
package cn.trussan.business.account.common.utils;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.util.List;
public class ExcelUtil {
//用于导出excel文件
public static void export2File(String path, String excelName, String sheetName, Class clazz, List data) {
String fileName = path.concat(excelName).concat(ExcelTypeEnum.XLSX.getValue());
EasyExcel.write(fileName, clazz).sheet(sheetName).doWrite(data);
}
//浏览器中输出excel
public static void export2Web(HttpServletResponse response, String excelName, String sheetName, Class clazz, List data) throws Exception {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码
excelName = URLEncoder.encode(excelName, "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue());
EasyExcel.write(response.getOutputStream(), clazz).sheet(sheetName).doWrite(data);
}
}