使用EasyExcel分页导出与导入大量数据
使用EasyExcel分页导出与导入大量数据1.项目截图2.项目使用的pom文件3.配置文件4.config配置类4.1 MybatisPlusConfig4.2 SwaggerConfiguration5.工具类5.1 DeleteFileUtil5.2 EasyExcelUtil5.3 EmailUtil6.实体类6.1 User6.2 Achievement7. service接口7.1 U
·
使用EasyExcel分页导出与导入大量数据
在我们使用easyexcel导出大量数据时，如果数据量过大会造成内存溢出，写了一个简单的demo提供参考。
提供了一下方法：
- 导入单个sheet数据
- 导入多个sheet数据（并提供了两个不同的实体类导入）
- 简单导出数据（适合少量数据）
- 分页导出数据
- 分页导出多sheet数据（Excel2003版最大行数是65536行，Excel2007开始的版本最大行数是1048576行）
- 分页导出多sheet数据并发送邮件（由于数据量过大后台运行时间过长，采用这个）
1.项目截图
2.项目使用的pom文件
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.4.1</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.easyexcel</groupId>
<artifactId>easyexcel</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>easyexcel</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
<!--配合插件lombok 使用-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.2</version>
<scope>provided</scope>
</dependency>
<!--swagger依赖-->
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<version>2.9.2</version>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger-ui</artifactId>
<version>2.9.2</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.16</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.27</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-extension</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-generator</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-typehandlers-jsr310</artifactId>
<version>1.0.2</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.71</version>
</dependency>
<!-- 分页插件 -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.3</version>
</dependency>
<!--发送邮件依赖jar包-->
<!-- https://mvnrepository.com/artifact/javax.mail/javax.mail-api -->
<dependency>
<groupId>javax.mail</groupId>
<artifactId>javax.mail-api</artifactId>
<version>1.6.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.sun.mail/javax.mail -->
<dependency>
<groupId>com.sun.mail</groupId>
<artifactId>javax.mail</artifactId>
<version>1.6.1</version>
</dependency>
<!-- END: Java mail -->
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
</resource>
</resources>
</build>
</project>
3.配置文件
server:
port: 8080
spring:
datasource:
druid:
url: jdbc:mysql://localhost:3306/easyexcel?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai
username: root
password: root
mybatis‐plus:
typeAliasesPackage: com.easyexcel.dao
mapper-locations: classpath*:com.easyexcel.mapper/*.xml
4.config配置类
4.1 MybatisPlusConfig
package com.easyexcel.config;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.plugins.PerformanceInterceptor;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
/**
* <P>
* Mybatis-Plus 配置
* </p>
*
* @author
* @since 2020-12-23
*/
@Configuration
@MapperScan("com.easyexcel.mapper")
public class MybatisPlusConfig {
/**
* 分页插件，自动识别数据库类型
*/
@Bean
public PaginationInterceptor paginationInterceptor() {
return new PaginationInterceptor();
}
/**
* 启用性能分析插件
*/
@Bean
public PerformanceInterceptor performanceInterceptor(){
return new PerformanceInterceptor();
}
}
4.2 SwaggerConfiguration
package com.easyexcel.config;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import springfox.documentation.builders.ApiInfoBuilder;
import springfox.documentation.builders.PathSelectors;
import springfox.documentation.builders.RequestHandlerSelectors;
import springfox.documentation.service.ApiInfo;
import springfox.documentation.service.Contact;
import springfox.documentation.spi.DocumentationType;
import springfox.documentation.spring.web.plugins.Docket;
import springfox.documentation.swagger2.annotations.EnableSwagger2;
@Configuration
@ConditionalOnProperty(prefix = "swagger",value = {"enable"},havingValue = "true")
@EnableSwagger2
public class SwaggerConfiguration {
@Bean
public Docket buildDocket() {
return new Docket(DocumentationType.SWAGGER_2)
.apiInfo(buildApiInfo())
.select()
// 要扫描的API(Controller)基础包
.apis(RequestHandlerSelectors.basePackage("com.easyexcel.controller"))
.paths(PathSelectors.any())
.build();
}
/**
* @param
* @return springfox.documentation.service.ApiInfo
* @Title: 构建API基本信息
* @methodName: buildApiInfo
*/
private ApiInfo buildApiInfo() {
Contact contact = new Contact("开发者","","");
return new ApiInfoBuilder()
.title("API文档")
.description("")
.contact(contact)
.version("1.0.0").build();
}
}
5.工具类
5.1 DeleteFileUtil
package com.easyexcel.util;
import java.io.File;
public class DeleteFileUtil {
/**
* 删除文件，可以是文件或文件夹
*
* @param fileName 要删除的文件名
* @return 删除成功返回true，否则返回false
*/
public static boolean delete(String fileName) {
File file = new File(fileName);
if (!file.exists()) {
return false;
} else {
if (file.isFile()) {
return deleteFile(fileName);
} else {
return deleteDirectory(fileName);
}
}
}
/**
* 删除单个文件
*
* @param fileName 要删除的文件的文件名
* @return 单个文件删除成功返回true，否则返回false
*/
public static boolean deleteFile(String fileName) {
File file = new File(fileName);
// 如果文件路径所对应的文件存在，并且是一个文件，则直接删除
if (file.exists() && file.isFile()) {
if (file.delete()) {
return true;
} else {
return false;
}
} else {
return false;
}
}
/**
* 删除目录及目录下的文件
*
* @param dir 要删除的目录的文件路径
* @return 目录删除成功返回true，否则返回false
*/
public static boolean deleteDirectory(String dir) {
// 如果dir不以文件分隔符结尾，自动添加文件分隔符
if (!dir.endsWith(File.separator)) {
dir = dir + File.separator;
}
File dirFile = new File(dir);
// 如果dir对应的文件不存在，或者不是一个目录，则退出
if ((!dirFile.exists()) || (!dirFile.isDirectory())) {
return false;
}
boolean flag = true;
// 删除文件夹中的所有文件包括子目录
File[] files = dirFile.listFiles();
for (int i = 0; i < files.length; i++) {
// 删除子文件
if (files[i].isFile()) {
flag = DeleteFileUtil.deleteFile(files[i].getAbsolutePath());
if (!flag) {
break;
}
}
// 删除子目录
else if (files[i].isDirectory()) {
flag = DeleteFileUtil.deleteDirectory(files[i]
.getAbsolutePath());
if (!flag) {
break;
}
}
}
if (!flag) {
return false;
}
// 删除当前目录
if (dirFile.delete()) {
return true;
} else {
return false;
}
}
}
5.2 EasyExcelUtil
package com.easyexcel.util;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.util.CollectionUtils;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
/**
* @Copyright (C), 2018-2020
* @FileName: EasyExcelUtil
* @Author: 杜景涛
* @Date: 2020/12/23 14:58
* @Description: easyexcel工具类
*/
public class EasyExcelUtil {
/**
* 导出数据到excel
*
* @param response
* @param fileName 文件名
* @param sheetName 表格sheet名
* @param list 导出数据
* @param clazz 实体类
*/
public static void export(HttpServletResponse response, String fileName, String sheetName, List<?> list, Class clazz) {
if (CollectionUtils.isEmpty(list)) {
return;
}
try (ServletOutputStream out = response.getOutputStream()) {
response.setCharacterEncoding("utf8");
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
response.setHeader("Cache-Control", "no-store");
response.addHeader("Cache-Control", "max-age=0");
EasyExcel.write(out, clazz).sheet(sheetName).doWrite(list);
out.flush();
} catch (IOException e) {
throw new RuntimeException();
}
}
}
5.3 EmailUtil
package com.easyexcel.util;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import javax.activation.DataHandler;
import javax.activation.DataSource;
import javax.activation.FileDataSource;
import javax.mail.Authenticator;
import javax.mail.BodyPart;
import javax.mail.Multipart;
import javax.mail.PasswordAuthentication;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeBodyPart;
import javax.mail.internet.MimeMessage;
import javax.mail.internet.MimeMultipart;
import javax.mail.internet.MimeUtility;
/**
* 发送邮件
*/
public class EmailUtil {
private static final String SMTP_HOST = "smtp.163.com";
private static final String USER_NAME = "dujingtao0228@163.com";
//邮箱授权码
private static final String PASSWORD = "*******";
/**
* 发送邮件
*
* @param title
* @param content
* @param receiveList
* @throws Exception
*/
public static void sendEmail(String title, String content, List<String> receiveList, String fileBytes) {
Session session = EmailUtil.getSession();
MimeMessage message = new MimeMessage(session);
InternetAddress[] toArray = new InternetAddress[receiveList.size()];
try {
//接收列表
for (int i = 0; i < toArray.length; i++) {
toArray[i] = new InternetAddress(receiveList.get(i));
}
message.setSubject(title);
message.setSentDate(new Date());
message.setFrom(new InternetAddress(USER_NAME));
message.addRecipients(MimeMessage.RecipientType.TO, toArray);
// 创建消息部分
BodyPart messageBodyPart = new MimeBodyPart();
// 消息
messageBodyPart.setContent(content, "text/html;charset=utf-8");
// 创建多重消息
Multipart multipart = new MimeMultipart();
// 设置文本消息部分
multipart.addBodyPart(messageBodyPart);
// 附件部分
messageBodyPart = new MimeBodyPart();
// 设置要发送附件的文件路径
DataSource source = new FileDataSource(fileBytes);
messageBodyPart.setDataHandler(new DataHandler(source));
// 处理附件名称中文（附带文件路径）乱码问题
messageBodyPart.setFileName(MimeUtility.encodeText(title + ".zip"));
multipart.addBodyPart(messageBodyPart);
// 发送完整消息
message.setContent(multipart);
// 发送消息
Transport.send(message);
} catch (Exception e) {
}
}
public static Session getSession() {
Properties props = new Properties();
props.setProperty("mail.transport.protocol", "smtp");
props.setProperty("mail.smtp.host", SMTP_HOST);
props.setProperty("mail.smtp.port", "25");
props.setProperty("mail.smtp.auth", "true");
Session session = Session.getInstance(props,
new Authenticator() {
@Override
protected PasswordAuthentication getPasswordAuthentication() {
return new PasswordAuthentication(USER_NAME, PASSWORD);
}
});
return session;
}
}
6.实体类
6.1 User
package com.easyexcel.dao;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.*;
/**
* @Copyright (C), 2018-2020
* @FileName: User
* @Author: 杜景涛
* @Date: 2020/12/23 14:47
* @Description: 用户实体类
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName("user")
public class User {
/**
* 用户id'
*/
/**
* 忽略这个字段
*/
@ExcelIgnore
@TableId(value = "id",type = IdType.AUTO)
private Integer id;
/**
* 姓名
*/
@ExcelProperty("姓名")
private String name;
/**
* 性别
*/
@ExcelProperty("性别")
private String sex;
/**
* 年龄
*/
@ExcelProperty("年龄")
private Integer age;
/**
* 手机号
*/
@ExcelProperty("手机号")
private String phone;
/**
* 住址
*/
@ExcelProperty("住址")
private String address;
}
6.2 Achievement
package com.easyexcel.dao;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
* @Copyright (C), 2018-2020
* @FileName: Achievement
* @Author: 杜景涛
* @Date: 2020/12/24 14:23
* @Description: 成绩实体类
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName("achievement")
public class Achievement {
/**
* id'
*/
/**
* 忽略这个字段
*/
@ExcelIgnore
@TableId(value = "id", type = IdType.AUTO)
private Integer id;
/**
* 用户id
*/
@ExcelProperty("用户id")
@TableId(value = "uid")
private Integer uid;
/**
* 语文
*/
@ExcelProperty("语文")
private Double chinese;
/**
* 数学
*/
@ExcelProperty("数学")
private Double mathematics;
/**
* 英语
*/
@ExcelProperty("英语")
private Double english;
/**
* 总和
*/
@ExcelProperty("总和")
private Double sum;
}
7. service接口
7.1 UserService
package com.easyexcel.service;
import com.easyexcel.dao.User;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
/**
* @Copyright (C), 2018-2020
* @FileName: UserService
* @Author: 杜景涛
* @Date: 2020/12/23 14:57
* @Description:
*/
public interface UserService {
/**
* 批量插入
*
* @param users
*/
void insertBatch(List<User> users);
/**
* 导出数据
*
* @param response
*/
void simpleWrite(HttpServletResponse response);
/**
* 分页导出数据
*
* @param response
*/
void pageWrite(HttpServletResponse response);
/**
* 分页多sheet导出数据
*
* @param response
*/
void pageWriteSheets(HttpServletResponse response);
/**
* 发送excel到邮箱
*
* @param response
*/
void writeSendMail(HttpServletResponse response) throws IOException;
}
7.2 AchievementService
package com.easyexcel.service;
import com.easyexcel.dao.Achievement;
import java.util.List;
/**
* @Copyright (C), 2018-2020
* @FileName: AchievementService
* @Author: 杜景涛
* @Date: 2020/12/24 14:28
* @Description:
*/
public interface AchievementService {
/**
* 批量插入
*
* @param achievements
*/
void insertBatch(List<Achievement> achievements);
}
8. xml
8.1 UserMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.easyexcel.mapper.UserMapper">
<insert id="insertBatch" parameterType="java.util.List">
INSERT INTO user
(name,sex,age,phone,address)
VALUES
<foreach collection="list" close="" index="index" item="item" open="" separator=",">
(#{item.name},#{item.sex},#{item.age},#{item.phone},#{item.address})
</foreach>
</insert>
<select id="findPageUser" resultType="com.easyexcel.dao.User">
select * from user
</select>
</mapper>
8.2 AchievementMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.easyexcel.mapper.AchievementMapper">
<insert id="insertBatch" parameterType="java.util.List">
INSERT INTO achievement
(uid,chinese,mathematics,english,sum)
VALUES
<foreach collection="list" close="" index="index" item="item" open="" separator=",">
(#{item.uid},#{item.chinese},#{item.mathematics},#{item.english},#{item.sum})
</foreach>
</insert>
</mapper>
9. service实现类
9.1 UserServiceImpl
package com.easyexcel.service.impl;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.easyexcel.dao.User;
import com.easyexcel.mapper.UserMapper;
import com.easyexcel.service.UserService;
import com.easyexcel.util.DeleteFileUtil;
import com.easyexcel.util.EasyExcelUtil;
import com.easyexcel.util.EmailUtil;
import com.github.pagehelper.PageHelper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
/**
* @Copyright (C), 2018-2020
* @FileName: UserServiceImpl
* @Author: 杜景涛
* @Date: 2020/12/23 14:57
* @Description:
*/
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserMapper userMapper;
/**
* 获得程序当前路径
*/
private String basePath = System.getProperty("user.dir");
@Override
public void insertBatch(List<User> users) {
userMapper.insertBatch(users);
}
@Override
public void simpleWrite(HttpServletResponse response) {
EasyExcelUtil.export(response, "test01", "sheet1", userMapper.findAll(), User.class);
}
@Override
public void pageWrite(HttpServletResponse response) {
ExcelWriter excelWriter = null;
try (ServletOutputStream out = response.getOutputStream()) {
//设置字符集为utf-8
response.setCharacterEncoding("utf8");
response.setContentType("application/vnd.ms-excel;charset=utf-8");
//通知浏览器服务器发送的数据格式
response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode("Test02" + ".xlsx", "UTF-8"));
//发送一个报头，告诉浏览器当前页面不进行缓存，每次访问的时间必须从服务器上读取最新的数据
response.setHeader("Cache-Control", "no-store");
response.addHeader("Cache-Control", "max-age=0");
// 这里 需要指定写用哪个class去写
excelWriter = EasyExcel.write(out, User.class).build();
// 这里注意 如果同一个sheet只要创建一次
WriteSheet writeSheet = EasyExcel.writerSheet("sheet").build();
Integer number = userMapper.number();
int page = (int) Math.ceil((double) number / (double) 10);
// 去调用写入,根据数据库分页的总的页数来
for (int i = 1; i <= page; i++) {
//先定义一个空集合每次循环使他变成null减少内存的占用
List<User> pageUser = null;
PageHelper.startPage(i, 10);
pageUser = userMapper.findPageUser();
excelWriter.write(pageUser, writeSheet);
pageUser.clear();
}
// 千万别忘记finish 会帮忙关闭流
excelWriter.finish();
out.flush();
} catch (IOException e) {
throw new RuntimeException();
}
}
@Override
public void pageWriteSheets(HttpServletResponse response) {
ExcelWriter excelWriter = null;
try (ServletOutputStream out = response.getOutputStream()) {
response.setCharacterEncoding("utf8");
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode("Test03" + ".xlsx", "UTF-8"));
response.setHeader("Cache-Control", "no-store");
response.addHeader("Cache-Control", "max-age=0");
// 这里 需要指定写用哪个class去写
excelWriter = EasyExcel.write(out, User.class).build();
// 这里注意 如果同一个sheet只要创建一次
WriteSheet writeSheet = EasyExcel.writerSheet("sheet").build();
Integer number = userMapper.number();
Integer count = 0;
int page = (int) Math.ceil((double) number / (double) 10);
// 去调用写入,根据数据库分页的总的页数来
for (int i = 1; i <= page; i++) {
if (count == 2) {
writeSheet = EasyExcel.writerSheet("sheet2").build();
count = 0;
}
//先定义一个空集合每次循环使他变成null减少内存的占用
List<User> pageUser = null;
PageHelper.startPage(i, 10);
pageUser = userMapper.findPageUser();
excelWriter.write(pageUser, writeSheet);
pageUser.clear();
count++;
}
// 千万别忘记finish 会帮忙关闭流
excelWriter.finish();
out.flush();
} catch (IOException e) {
throw new RuntimeException();
}
}
@Override
public void writeSendMail(HttpServletResponse response) throws IOException {
ExcelWriter excelWriter = null;
UUID uuid = UUID.randomUUID();
ZipOutputStream zip = new ZipOutputStream(new FileOutputStream(basePath + "\\src\\main\\resources\\file\\" + uuid + ".zip"));
ZipEntry entry = new ZipEntry("明细查询.xlsx");
zip.putNextEntry(entry);
try {
// 这里需要指定写用哪个class去写
excelWriter = EasyExcel.write(zip, User.class).build();
// 这里注意 如果同一个sheet只要创建一次
WriteSheet writeSheet = EasyExcel.writerSheet("sheet").build();
Integer number = userMapper.number();
int count = 0;
int page = (int) Math.ceil((double) number / (double) 10);
// 去调用写入,根据数据库分页的总的页数来
for (int i = 1; i <= page; i++) {
if (count == 2) {
writeSheet = EasyExcel.writerSheet("sheet2").build();
count = 0;
}
//先定义一个空集合每次循环使他变成null减少内存的占用
List<User> pageUser = null;
PageHelper.startPage(i, 10);
pageUser = userMapper.findPageUser();
excelWriter.write(pageUser, writeSheet);
pageUser.clear();
count++;
}
} finally {
// 千万别忘记finish 会帮忙关闭流
if (excelWriter != null) {
excelWriter.finish();
zip.flush();
zip.close();
}
}
List<String> receiveList = new ArrayList<>();
receiveList.add("2209273361@qq.com");
EmailUtil.sendEmail("明细查询", "查询数据已发送,请注意查收！", receiveList, basePath + "\\src\\main\\resources\\file\\" + uuid + ".zip");
DeleteFileUtil.delete(basePath + "\\src\\main\\resources\\file\\" + uuid + ".zip");
}
}
9.2 AchievementServiceImpl
package com.easyexcel.service.impl;
import com.easyexcel.dao.Achievement;
import com.easyexcel.mapper.AchievementMapper;
import com.easyexcel.service.AchievementService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
/**
* @Copyright (C), 2018-2020
* @FileName: AchievementServiceImpl
* @Author: 杜景涛
* @Date: 2020/12/24 14:29
* @Description:
*/
@Service
public class AchievementServiceImpl implements AchievementService {
@Autowired
private AchievementMapper achievementMapper;
@Override
public void insertBatch(List<Achievement> achievements) {
achievementMapper.insertBatch(achievements);
}
}
10.拦截器
10.1 UserDataListener
package com.easyexcel.listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.easyexcel.dao.User;
import com.easyexcel.service.UserService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.ArrayList;
import java.util.List;
/**
* 模板的读取类
*/
public class UserDataListener extends AnalysisEventListener<User> {
private static final Logger LOGGER = LoggerFactory.getLogger(UserDataListener.class);
/**
* 每隔3000条存储数据库,然后清理list ，方便内存回收
*/
private static final int BATCH_COUNT = 3000;
List<User> list = new ArrayList<>();
private UserService userService;
/**
* 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
*
* @param userService
*/
public UserDataListener(UserService userService) {
this.userService = userService;
}
@Override
public void invoke(User data, AnalysisContext context) {
LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
list.add(data);
if (list.size() >= BATCH_COUNT) {
saveData();
list.clear();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
saveData();
LOGGER.info("所有数据解析完成！");
}
/**
* 加上存储数据库
*/
private void saveData() {
LOGGER.info("{}条数据，开始存储数据库！", list.size());
userService.insertBatch(list);
LOGGER.info("存储数据库成功！");
}
}
10.2 AchievementDataListener
package com.easyexcel.listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.easyexcel.dao.Achievement;
import com.easyexcel.service.AchievementService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.ArrayList;
import java.util.List;
/**
* 模板的读取类
*/
public class AchievementDataListener extends AnalysisEventListener<Achievement> {
private static final Logger LOGGER = LoggerFactory.getLogger(AchievementDataListener.class);
/**
* 每隔3000条存储数据库，然后清理list ，方便内存回收
*/
private static final int BATCH_COUNT = 3000;
List<Achievement> list = new ArrayList<>();
private AchievementService achievementService;
/**
* 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
*
* @param achievementService
*/
public AchievementDataListener(AchievementService achievementService) {
this.achievementService = achievementService;
}
/**
* 这个每一条数据解析都会来调用
*
* @param data
* @param context
*/
@Override
public void invoke(Achievement data, AnalysisContext context) {
LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
list.add(data);
// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
if (list.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
list.clear();
}
}
/**
* 所有数据解析完成了 都会来调用
*
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据，确保最后遗留的数据也存储到数据库
saveData();
LOGGER.info("所有数据解析完成！");
}
/**
* 加上存储数据库
*/
private void saveData() {
LOGGER.info("{}条数据，开始存储数据库！", list.size());
achievementService.insertBatch(list);
LOGGER.info("存储数据库成功！");
}
}
11.Controller服务层
11.1UserController
package com.easyexcel.controller;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.easyexcel.dao.Achievement;
import com.easyexcel.dao.User;
import com.easyexcel.service.AchievementService;
import com.easyexcel.service.UserService;
import com.easyexcel.listener.UserDataListener;
import com.easyexcel.listener.AchievementDataListener;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
/**
* @Copyright (C), 2018-2020
* @FileName: UserController
* @Author: 杜景涛
* @Date: 2020/12/23 14:58
* @Description:
*/
@RestController
@RequestMapping("/v1")
@Api(tags = {"数据导出导入"})
public class UserController {
@Resource
private UserService userService;
@Resource
private AchievementService achievementService;
@ApiOperation(value = "导入单个sheet数据")
@PostMapping("/importSheet")
public void importSheet(@RequestParam("multipartFile") MultipartFile multipartFile) {
try {
InputStream inputStream = multipartFile.getInputStream();
//异步读取所有sheet数据,可在sheet方法参数中指定sheet索引,sheet名称
EasyExcel.read(inputStream, User.class, new UserDataListener(userService)).sheet().doRead();
} catch (IOException e) {
e.printStackTrace();
}
}
@ApiOperation(value = "导入多个sheet数据")
@PostMapping("/importSheets")
public void importSheets(@RequestParam("multipartFile") MultipartFile multipartFile) {
ExcelReader excelReader = null;
try {
InputStream inputStream = multipartFile.getInputStream();
excelReader = EasyExcel.read(inputStream).build();
ReadSheet readSheet1 =
//构建ExcelReader对象
EasyExcel.readSheet(0).head(User.class).registerReadListener(new UserDataListener(userService)).build();
ReadSheet readSheet2 =
//构建ExcelReader对象
EasyExcel.readSheet(1).head(Achievement.class).registerReadListener(new AchievementDataListener(achievementService)).build();
//这里注意 一定要把sheet1 sheet2 一起传进去
excelReader.read(readSheet1, readSheet2);
} catch (IOException e) {
e.printStackTrace();
} finally {
if (excelReader != null) {
// 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
excelReader.finish();
}
}
}
@ApiOperation(value = "导出数据", produces = "application/octet-stream")
@PostMapping("/simpleWrite")
public void simpleWrite(HttpServletResponse response) {
userService.simpleWrite(response);
}
@ApiOperation(value = "分页导出数据", produces = "application/octet-stream")
@PostMapping("/pageWrite")
public void pageWrite(HttpServletResponse response) {
userService.pageWrite(response);
}
@ApiOperation(value = "分页导出多sheet数据", produces = "application/octet-stream")
@PostMapping("/pageWriteSheets")
public void pageWriteSheets(HttpServletResponse response) {
userService.pageWriteSheets(response);
}
@ApiOperation(value = "分页导出多sheet数据发送邮件")
@PostMapping("/writeSendMail")
public void writeSendMail(HttpServletResponse response) throws IOException {
userService.writeSendMail(response);
}
}
项目地址
https://gitee.com/du_jing_tao/easyexcel.git
为开发者提供学习成长、分享交流、生态实践、资源工具等服务，帮助开发者快速成长。
更多推荐
6
0- 0
已为社区贡献1条内容
所有评论(0)