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

2021-03-15 19:31

阅读：748

标签：响应 Delve maven 解析 str pre static 问题简单

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：
技术图片
??更多请参考：https://github.com/alibaba/easyexcel

2. 示例代码

创建工程
修改pom.xml

4.0.0com.c3stones
	spring-boot-easyexcel-demo
	0.0.1-SNAPSHOTspring-boot-easyexcel-demoSpringBoot + Easyexcel Demoorg.springframework.boot
		spring-boot-starter-parent
		2.2.8.RELEASEcom.alibaba
			easyexcel
			2.2.6org.projectlombok
			lombok
			trueorg.springframework.boot
			spring-boot-starter-web

创建实体

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 {

	@Getter
	private List studentList = new ArrayList();

	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 studentList = new ArrayList() {
			{
				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 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 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 studentList = new ArrayList() {
			{
				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);
	}

}