怎么在SpringBoot中利用EasyExcel导入Excel文件
这期内容当中小编将会给大家带来有关怎么在SpringBoot中利用EasyExcel导入Excel文件，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。
1、 在pom.xml中添加EasyExcel依赖
<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.1.3</version> </dependency>
2、创建EasyExcel映射实体类
import com.alibaba.excel.annotation.ExcelProperty;
public class ExcelEntity {
// ExcelProperty中的参数要对应Excel中的标题
@ExcelProperty("ID")
private int ID;
@ExcelProperty("NAME")
private String name;
@ExcelProperty("AGE")
private int age;
public ExcelEntity() {
}
public ExcelEntity(int ID, String name, int age) {
this.ID = ID;
this.name = name;
this.age = age;
}
public int getID() {
return ID;
}
public void setID(int ID) {
this.ID = ID;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
}
3、创建自定义Easyexcel的监听类
这个监听类里面每一个ExcelEntity对象代表一行数据
在这个监听类里面可以对读取到的每一行数据进行单独操作
这里的读取的数据是按照Excel中每一条数据的顺序进行读取的
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import java.util.ArrayList;
import java.util.List;
public class UploadExcelListener extends AnalysisEventListener<ExcelEntity> {
private static final Logger logger = LoggerFactory.getLogger(LoggerItemController.class);
public static final List<ExcelEntity> list = new ArrayList<>();
@Override
public void invoke(ExcelEntity excelEntity, AnalysisContext context) {
logger.info(String.valueOf(excelEntity.getID()));
logger.info(excelEntity.getName());
logger.info(String.valueOf(excelEntity.getAge()));
list.add(excelEntity);
}
4、创建controller
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
@RestController
@CrossOrigin
@RequestMapping("/loggerItem")
public class LoggerItemController {
// MultipartFile 这个类一般是用来接受前台传过来的文件
@PostMapping("/upload")
public List<ExcelEntity> upload(@RequestParam(value = "multipartFile") MultipartFile multipartFile){
if (multipartFile == null){
return null;
}
InputStream in = null;
try {
// 从multipartFile获取InputStream流
in = multipartFile.getInputStream();
/*
* EasyExcel 有多个不同的read方法，适用于多种需求
* 这里调用EasyExcel中通过InputStream流方式读取Excel的Read方法
* 他会返回一个ExcelReaderBuilder类型的返回值
* ExcelReaderBuilder中有一个doReadAll方法，会读取所有的Sheet
*/
EasyExcel.read(in,ExcelEntity.class,new UploadExcelListener())
.sheet("Sheet1")
.doRead();
// 每次EasyExcel的read方法读取完之后都会关闭流，我这里为了试验doReadAll方法，所以重新获取了一次
in = multipartFile.getInputStream();
/*
* ExcelReaderBuilder中的Sheet方法，需要添加读取的Sheet名作为参数
* 并且不要忘记在后面再调用一下doReadAll方法，否则不会进行读取操作
*/
EasyExcel.read(in,ExcelEntity.class,new UploadExcelListener()).doReadAll();
} catch (IOException e) {
e.printStackTrace();
}
return UploadExcelListener.list;
}
}
5、application.yml配置
server: # 指定端口号 port: 8080 spring: servlet: multipart: # 配置单个上传文件大小 file-size-threshold: 100M # 配置总上传大小 max-request-size: 300M
6、测试
我们先搞一个简单的Excel，用来测试
然后通过Postman模拟发送请求
选择Post请求并输入请求地址
在下面选择Body
Key的框中输入controller中的请求的方法中的参数，后面的下拉框中选择File
VALUE框中有一个Select File ，点击后选择自己刚才创建的测试的Excel
最后点击Send发送请求
返回值如下：
由于我读了两次都放在同一个List中返回，所以返回值中有8个对象。
[
{
"name": "小黑",
"age": 25,
"id": 1
},
{
"name": "小白",
"age": 22,
"id": 2
},
{
"name": "小黄",
"age": 22,
"id": 3
},
{
"name": "小绿",
"age": 23,
"id": 4
},
{
"name": "小黑",
"age": 25,
"id": 1
},
{
"name": "小白",
"age": 22,
"id": 2
},
{
"name": "小黄",
"age": 22,
"id": 3
},
{
"name": "小绿",
"age": 23,
"id": 4
}
]
三、EasyExcel中的Read方法汇总
/**
* Build excel the read
*
* @return Excel reader builder.
*/
public static ExcelReaderBuilder read() {
return new ExcelReaderBuilder();
}
/**
* Build excel the read
*
* @param file
* File to read.
* @return Excel reader builder.
*/
public static ExcelReaderBuilder read(File file) {
return read(file, null, null);
}
/**
* Build excel the read
*
* @param file
* File to read.
* @param readListener
* Read listener.
* @return Excel reader builder.
*/
public static ExcelReaderBuilder read(File file, ReadListener readListener) {
return read(file, null, readListener);
}
/**
* Build excel the read
*
* @param file
* File to read.
* @param head
* Annotate the class for configuration information.
* @param readListener
* Read listener.
* @return Excel reader builder.
*/
public static ExcelReaderBuilder read(File file, Class head, ReadListener readListener) {
ExcelReaderBuilder excelReaderBuilder = new ExcelReaderBuilder();
excelReaderBuilder.file(file);
if (head != null) {
excelReaderBuilder.head(head);
}
if (readListener != null) {
excelReaderBuilder.registerReadListener(readListener);
}
return excelReaderBuilder;
}
/**
* Build excel the read
*
* @param pathName
* File path to read.
* @return Excel reader builder.
*/
public static ExcelReaderBuilder read(String pathName) {
return read(pathName, null, null);
}
/**
* Build excel the read
*
* @param pathName
* File path to read.
* @param readListener
* Read listener.
* @return Excel reader builder.
*/
public static ExcelReaderBuilder read(String pathName, ReadListener readListener) {
return read(pathName, null, readListener);
}
/**
* Build excel the read
*
* @param pathName
* File path to read.
* @param head
* Annotate the class for configuration information.
* @param readListener
* Read listener.
* @return Excel reader builder.
*/
public static ExcelReaderBuilder read(String pathName, Class head, ReadListener readListener) {
ExcelReaderBuilder excelReaderBuilder = new ExcelReaderBuilder();
excelReaderBuilder.file(pathName);
if (head != null) {
excelReaderBuilder.head(head);
}
if (readListener != null) {
excelReaderBuilder.registerReadListener(readListener);
}
return excelReaderBuilder;
}
/**
* Build excel the read
*
* @param inputStream
* Input stream to read.
* @return Excel reader builder.
*/
public static ExcelReaderBuilder read(InputStream inputStream) {
return read(inputStream, null, null);
}
/**
* Build excel the read
*
* @param inputStream
* Input stream to read.
* @param readListener
* Read listener.
* @return Excel reader builder.
*/
public static ExcelReaderBuilder read(InputStream inputStream, ReadListener readListener) {
return read(inputStream, null, readListener);
}
/**
* Build excel the read
*
* @param inputStream
* Input stream to read.
* @param head
* Annotate the class for configuration information.
* @param readListener
* Read listener.
* @return Excel reader builder.
*/
public static ExcelReaderBuilder read(InputStream inputStream, Class head, ReadListener readListener) {
ExcelReaderBuilder excelReaderBuilder = new ExcelReaderBuilder();
excelReaderBuilder.file(inputStream);
if (head != null) {
excelReaderBuilder.head(head);
}
if (readListener != null) {
excelReaderBuilder.registerReadListener(readListener);
}
return excelReaderBuilder;
}
所有的方法都在这儿了，其实如果看不懂到底应该调用哪一个read方法的话，可以以根据自己所能得到的参数来判断。
四、扩展
读取本地Excel
public static void main(String[] args) {
EasyExcel.read("C:/Users/Lonely Programmer/Desktop/新建 Microsoft Excel 工作表.xlsx"
,ExcelEntity.class
,new UploadExcelListener())
.doReadAll();
}
读取本地的Excel和通过InPutStream流读取的方式是一样的，只是参数变了，原本传的是InPutStream流，现在传的是文件的绝对路径。我这里监听类和映射实体类都没有变，和上传用的是同一个，大家也可以根据需求来设定自己的监听类与实体类
MultipartFile文档
MultipartFile文档地址：https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/web/multipart/MultipartFile.html
翻译是通过Google Chrome自带翻译插件进行翻译的，建议大家使用Google Chrome打开，自带翻译功能
上述就是小编为大家分享的怎么在SpringBoot中利用EasyExcel导入Excel文件了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注行业资讯频道。