之前也写过Excel的导入导出功能,但是都是直接在方法中利用easyExcel来进行转化,但是一个大型的管理系统中,必然会有很多的Excel导入导出功能需求,因此整理了一个excel工具类,附上对应已经实现的demo。
工具类:ExcelUtils:
package com.example.webdemo.utils.excel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.example.webdemo.Dao.UserDao;
import com.example.webdemo.Entity.User;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.List;
/**
* @author zhangqianwei
* @date 2022/2/16 19:45
*/
public class ExcelUtils {
/**
* @param is 导入文件输入流
* @param clazz Excel实体映射类
* @return
*/
public static Boolean readExcel(InputStream is, Class clazz, UserDao userDao){
BufferedInputStream bis = null;
try {
bis = new BufferedInputStream(is);
// 解析每行结果在listener中处理
AnalysisEventListener listener = new ExcelListener(userDao);
ExcelReader excelReader = EasyExcelFactory.getReader(bis, listener);
excelReader.read(new Sheet(1, 1, clazz));
} catch (Exception e) {
e.printStackTrace();
return false;
} finally {
if (bis != null) {
try {
bis.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return true;
}
/**
*
* @param response http请求报文
* @param clazz Excel实体映射类
* @param data 导出数据
* @return
*/
public static Boolean writeExcel(HttpServletResponse response, Class clazz, List<? extends BaseRowModel> data){
BufferedOutputStream bos= null;
try {
ServletOutputStream out = response.getOutputStream();
ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX, true);
String fileName = "用户数据";
//这边对应的实体类改为你所导出的实体类
Sheet sheet = new Sheet(1, 0, User.class);
//设置自适应宽度
sheet.setAutoWidth(Boolean.TRUE);
// 第一个 sheet 名称
sheet.setSheetName("第一个sheet");
writer.write(data, sheet);
//通知浏览器以附件的形式下载处理,设置返回头要注意文件名有中文
response.setHeader("Content-disposition", "attachment;filename=" + new String( fileName.getBytes("gb2312"), "ISO8859-1" ) + ".xlsx");
writer.finish();
response.setContentType("multipart/form-data");
response.setCharacterEncoding("utf-8");
out.flush();
} catch (Exception e) {
e.printStackTrace();
return false;
} finally {
if (bos != null) {
try {
bos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return true;
}
}
监听类用于操作excel批量导入:
package com.example.webdemo.utils.excel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.example.webdemo.Dao.UserDao;
import com.example.webdemo.Entity.User;
import java.util.ArrayList;
import java.util.List;
/**
* @author zhangqianwei
* @date 2022/2/16 20:03
*/
/** 解析监听器,
* 每解析一行会回调invoke()方法。
* 整个excel解析结束会执行doAfterAllAnalysed()方法
*/
public class ExcelListener extends AnalysisEventListener {
private final UserDao userDao;
//解析出来的每一行数据可以存入一个list中
private List<Object> datas = new ArrayList<Object>();
public ExcelListener(UserDao userDao) {
this.userDao = userDao;
}
@Override
public void invoke(Object object, AnalysisContext context) {
System.out.println("当前行:"+context.getCurrentRowNum());
System.out.println(object);
datas.add(object);//数据存储到list,供批量处理,或后续自己业务逻辑处理。
insert(object);//根据自己业务做处理
}
private void insert(Object object) {
User user = (User) object;
userDao.addUser(user);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// datas.clear();//解析结束销毁不用的资源
}
public List<Object> getDatas() {
return datas;
}
public void setDatas(List<Object> datas) {
this.datas = datas;
}
}
功能实现Demo:
整体结构:
pom文件引入maven依赖:
<!-- 引入阿里巴巴的easyExcel导出Excel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.0.5</version>
</dependency>
实体类:User
package com.example.webdemo.Entity;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;
import java.util.List;
/**
* @author zhangqianwei
* @date 2021/9/11 15:58
*/
//使用easyExcel JavaBean使用相对应的注解需要继承BaseRowModel类
@Data
public class User extends BaseRowModel {
@ExcelProperty(value = {"主键ID"}, index = 0)
private int id;
@ExcelProperty(value = {"用户姓名"}, index = 1)
private String name;
@ExcelProperty(value = {"用户年龄"}, index = 2)
private int age;
@ExcelIgnore
private List<Integer> selects;
public User(int id, String name, int age) {
this.id = id;
this.name = name;
this.age = age;
}
public User() {
}
}
Controller:
//excel导出用户数据
@GetMapping("/user/export")
public void exportUser(@RequestParam("name") String name,HttpServletResponse response) {
List<User> userList = userDao.queryUsersByName(name);
ExcelUtils.writeExcel(response, User.class,userList);
}
//excel批量导入用户数据
@PostMapping("/user/import")
public Object addUserList(@RequestParam("file") MultipartFile file) {
try {
BufferedInputStream in = new BufferedInputStream(file.getInputStream());
//调用写的一个工具类传入 文件流,所要转换存储的类,以及业务逻辑(我这边直接就是插入语句)
ExcelUtils.readExcel(in, User.class,userDao);
}catch (Exception e){
e.printStackTrace();
}
return "ok";
}
//批量导入需要先下载模板
@GetMapping("/download/excel")
public void downloadExcel(HttpServletResponse response) {
try(FileInputStream in =new FileInputStream("src\\main\\resources\\用户数据导入模板.xlsx")) {
String fileName = "用户数据导入模板";
//通知浏览器以附件的形式下载处理,设置返回头要注意文件名有中文
response.setHeader("Content-disposition", "attachment;filename=" +new String( fileName.getBytes("gb2312"), "ISO8859-1" ) + ".xlsx");
response.setContentType("multipart/form-data");
response.setCharacterEncoding("utf-8");
OutputStream os = response.getOutputStream();
byte[] b = new byte[1024];
int len;
while((len = in.read(b)) > 0){
os.write(b,0,len);
}
os.flush();
os.close();
in.close();
}catch (Exception e) {
e.printStackTrace();
}
}
剩下的就剩Service层逻辑处理 和 Dao层的SQL查询语句,当然我这边直接调的Dao层,没有经过Service层。
完成结果: