SpringBoot基于EasyExcel解析Excel实现文件导出导入、读取写入

短命女 2023-10-05 13:52 90阅读 0赞

1. 简介

Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存，poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题，但POI还是有一些缺陷，比如07版Excel解压缩以及解压后存储都是在内存中完成的，内存消耗依然很大。easyexcel重写了poi对07版Excel的解析，能够原本一个3M的excel用POI sax依然需要100M左右内存降低到几M，并且再大的excel不会出现内存溢出，03版依赖POI的sax模式，在上层做了模型转换的封装，让使用者更加简单方便。  
  64M内存1分钟内读取75M(46W行25列)的Excel：

![253c53b1df6e9b67e20953129cb6892b.png][]

更多请参考： [https://github.com/alibaba/easyexcel][https_github.com_alibaba_easyexcel]

2. 示例代码

*  创建工程
    
    ![3185f1292cda596f5b4a36886fe45a90.png][]
 *  修改pom.xml

<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>
    	<groupId>com.c3stones</groupId>
    	<artifactId>spring-boot-easyexcel-demo</artifactId>
    	<version>0.0.1-SNAPSHOT</version>
    	<name>spring-boot-easyexcel-demo</name>
    	<description>SpringBoot + Easyexcel Demo</description>
    
    	<parent>
    		<groupId>org.springframework.boot</groupId>
    		<artifactId>spring-boot-starter-parent</artifactId>
    		<version>2.2.8.RELEASE</version>
    		<relativePath />
    	</parent>
    
    	<dependencies>
    		<dependency>
    			<groupId>com.alibaba</groupId>
    			<artifactId>easyexcel</artifactId>
    			<version>2.2.6</version>
    		</dependency>
    		<dependency>
    			<groupId>org.projectlombok</groupId>
    			<artifactId>lombok</artifactId>
    			<optional>true</optional>
    		</dependency>
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-web</artifactId>
    		</dependency>
    	</dependencies>
    
    </project>

*  创建实体

import java.util.Date;
    
    import com.alibaba.excel.annotation.ExcelProperty;
    import com.alibaba.excel.annotation.write.style.ColumnWidth;
    import com.alibaba.excel.annotation.write.style.ContentRowHeight;
    import com.alibaba.excel.annotation.write.style.HeadRowHeight;
    
    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    
    /**
     * 学生实体
     * 
     * @author CL
     *
     */
    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    @HeadRowHeight(20)
    @ColumnWidth(20)
    @ContentRowHeight(15)
    public class Student {
    
    	@ExcelProperty(index = 0, value = "学号")
    	private String sno;
    
    	@ExcelProperty(index = 1, value = "姓名")
    	private String name;
    
    	@ExcelProperty(index = 2, value = "年龄")
    	private Integer age;
    
    	@ExcelProperty(index = 3, value = "性别")
    	private String gender;
    
    	@ExcelProperty(index = 4, value = "籍贯")
    	private String nativePlace;
    
    	@ExcelProperty(index = 5, value = "入学时间")
    	private Date enrollmentTime;
    
    }

*  创建文件读取类  
      该类需要继承抽象类`AnalysisEventListener`，但是不需要被Spring管理。

import java.util.ArrayList;
    import java.util.List;
    
    import com.alibaba.excel.context.AnalysisContext;
    import com.alibaba.excel.event.AnalysisEventListener;
    import com.c3stones.entity.Student;
    
    import lombok.Getter;
    
    /**
     * 学生读取类
     * 
     * @author CL
     *
     */
    public class StudentListener extends AnalysisEventListener<Student> {
    
    	@Getter
    	private List<Student> studentList = new ArrayList<Student>();
    
    	public StudentListener() {
    		super();
    		studentList.clear();
    	}
    
    	/**
    	 * 每一条数据解析都会调用
    	 */
    	@Override
    	public void invoke(Student student, AnalysisContext context) {
    		studentList.add(student);
    	}
    
    	/**
    	 * 所有数据解析完成都会调用
    	 */
    	@Override
    	public void doAfterAllAnalysed(AnalysisContext context) {
    		studentList.forEach(System.out::println);
    	}
    
    }

*  创建文件导出导入Controller示例

import java.io.IOException;
    import java.net.URLEncoder;
    import java.text.ParseException;
    import java.text.SimpleDateFormat;
    import java.util.ArrayList;
    import java.util.List;
    
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    import org.springframework.web.multipart.MultipartFile;
    
    import com.alibaba.excel.EasyExcel;
    import com.c3stones.entity.Student;
    import com.c3stones.listener.StudentListener;
    
    /**
     * 学生Controller
     * 
     * @author CL
     *
     */
    @RestController
    @RequestMapping(value = "student")
    public class StudentController {
    
    	/**
    	 * 导出学生信息
    	 * 
    	 * @param response
    	 * @param request
    	 * @throws IOException
    	 * @throws ParseException
    	 */
    	@SuppressWarnings("serial")
    	@RequestMapping(value = "export")
    	public void exportStudentInfos(HttpServletResponse response, HttpServletRequest request)
    			throws IOException, ParseException {
    		// 设置响应类型
    		response.setContentType("application/vnd.ms-excel");
    		// 设置字符编码
    		response.setCharacterEncoding("utf-8");
    		// 设置响应头信息
    		response.setHeader("Content-disposition",
    				"attachment;filename*=utf-8''" + URLEncoder.encode("学生花名册", "UTF-8") + ".xlsx");
    
    		List<Student> studentList = new ArrayList<Student>() {
    			{
    				SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
    				add(new Student("1001", "张三", 23, "男", "陕西西安", dateFormat.parse("2020-09-01")));
    				add(new Student("1002", "李四", 22, "女", "陕西渭南", dateFormat.parse("2020-09-01")));
    			}
    		};
    
    		// 写入文件
    		EasyExcel.write(response.getOutputStream(), Student.class).sheet("学生信息").doWrite(studentList);
    	}
    
    	/**
    	 * 导入学生信息
    	 * 
    	 * @param file
    	 * @throws IOException
    	 */
    	@RequestMapping(value = "import")
    	public List<Student> importStudentInfos(MultipartFile file) throws IOException {
    		StudentListener studentListener = new StudentListener();
    		EasyExcel.read(file.getInputStream(), Student.class, studentListener).sheet().doRead();
    		return studentListener.getStudentList();
    	}
    
    }

*  创建文件读取写入Controller示例

import java.text.ParseException;
    import java.text.SimpleDateFormat;
    import java.util.ArrayList;
    import java.util.List;
    
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import com.alibaba.excel.EasyExcel;
    import com.c3stones.entity.Student;
    import com.c3stones.listener.StudentListener;
    
    /**
     * 文件Controller
     * 
     * @author CL
     *
     */
    @RestController
    @RequestMapping(value = "file")
    public class FileController {
    
    	/**
    	 * 读取Excel
    	 * 
    	 * @return
    	 */
    	@RequestMapping(value = "readExcel")
    	public List<Student> readExcel() {
    		String fileName = "C:\\Users\\Administrator\\Desktop\\学生花名册.xlsx";
    		StudentListener studentListener = new StudentListener();
    		EasyExcel.read(fileName, Student.class, studentListener).sheet().doRead();
    		return studentListener.getStudentList();
    	}
    
    	/**
    	 * 写入Excel
    	 * 
    	 * @return
    	 * @throws ParseException
    	 */
    	@SuppressWarnings("serial")
    	@RequestMapping(value = "writeExcel")
    	public Boolean writeExcel() throws ParseException {
    		String fileName = "C:\\Users\\Administrator\\Desktop\\学生花名册2.xlsx";
    
    		List<Student> studentList = new ArrayList<Student>() {
    			{
    				SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
    				add(new Student("2001", "张三2", 23, "男", "陕西西安", dateFormat.parse("2020-09-01")));
    				add(new Student("2002", "李四2", 22, "女", "陕西渭南", dateFormat.parse("2020-09-01")));
    			}
    		};
    
    		EasyExcel.write(fileName, Student.class).sheet("学生信息2").doWrite(studentList);
    		return true;
    	}
    
    }

*  创建启动类

import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    
    /**
     * 启动类
     * 
     * @author CL
     *
     */
    @SpringBootApplication
    public class Application {
    
    	public static void main(String[] args) {
    		SpringApplication.run(Application.class, args);
    	}
    
    }

*  启动项目

3. 测试

通过Postman依次测试导出、导入、读取和写入：

*  测试导出
    
    ![1754a04af6f2c7f78bf15b3079ebea9b.png][]
    
      
      将导出文件保存到桌面(学生花名册.xlsx)。
 *  测试导入
    
    ![478cf3923108444aecbbad2fbcddda5b.png][]
 *  测试读取
    
    ![d79f6e292c30abe77faf1b2c7b25060e.png][]
 *  测试写入
    
    ![56866864ba120db27de1d5b732721dd8.png][]
    
      
      可以看到在代码中配置的文件目录已存在写入成功的文件(学生花名册2.xlsx)。

4. 项目地址

[spring-boot-easyexcel-demo][]

[253c53b1df6e9b67e20953129cb6892b.png]: https://img-blog.csdnimg.cn/img_convert/253c53b1df6e9b67e20953129cb6892b.png
[https_github.com_alibaba_easyexcel]: https://github.com/alibaba/easyexcel
[3185f1292cda596f5b4a36886fe45a90.png]: https://img-blog.csdnimg.cn/img_convert/3185f1292cda596f5b4a36886fe45a90.png
[1754a04af6f2c7f78bf15b3079ebea9b.png]: https://img-blog.csdnimg.cn/img_convert/1754a04af6f2c7f78bf15b3079ebea9b.png
[478cf3923108444aecbbad2fbcddda5b.png]: https://img-blog.csdnimg.cn/img_convert/478cf3923108444aecbbad2fbcddda5b.png
[d79f6e292c30abe77faf1b2c7b25060e.png]: https://img-blog.csdnimg.cn/img_convert/d79f6e292c30abe77faf1b2c7b25060e.png
[56866864ba120db27de1d5b732721dd8.png]: https://img-blog.csdnimg.cn/img_convert/56866864ba120db27de1d5b732721dd8.png
[spring-boot-easyexcel-demo]: https://github.com/C3Stones/blog