课程链接:神器!阿里开源的Excel操作工具
源代码:
1、pom文件导入easyexcel依赖
<!--easyexcel依赖-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>
2、读取Excel数据
(1)读取–返回Object对象,完整代码
@SpringBootTest
public class ExcelReadTest {
String PATH = "C:\\Users\\wano\\Desktop\\毕设资料\\技术Demo\\Excel读写\\myExcel\\src\\main\\resources\\static\\testData\\";
@Test
public void simpleRead() {
// 读取文件
// 创建ExcelReaderBuilder实例
ExcelReaderBuilder readerBuilder = EasyExcel.read();
// 获取文件对象
readerBuilder.file(PATH+"课程体系.xlsx");
// 指定sheet
readerBuilder.sheet();
// 自动关闭输入流
readerBuilder.autoCloseStream(true);
// 设置文件格式
readerBuilder.excelType(ExcelTypeEnum.XLSX);
// 注册监听器进行数据解析
readerBuilder.registerReadListener(new AnalysisEventListener() {
@Override
public void invoke(Object o, AnalysisContext analysisContext) {
// 一行数据读取完成后进行回调, 一行数据封装成一个对象后回调
System.out.println(o);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
// 文件读取完毕日志
System.out.println("文件读取完毕!");
}
});
// 构建读取器
ExcelReader reader = readerBuilder.build();
// 读取数据
reader.readAll();
// 读取完毕
reader.finish();
}
}
结果:
{0=1, 1=高等数学a, 2=通识必修课, 3=必修, 4=1, 5=2, 6=24, 7=8, 8=32}
{0=2, 1=大学物理a, 2=通识必修课, 3=必修, 4=1, 5=2, 6=24, 7=8, 8=32}
{0=3, 1=计算机引论, 2=通识必修课, 3=必修, 4=1, 5=2, 6=24, 7=8, 8=32}
(2)读取–返回Map对象,完整写法
@Test
public void simpleReadMap() {
// 读取文件 Map
// 创建ExcelReaderBuilder实例
ExcelReaderBuilder readerBuilder = EasyExcel.read();
// 获取文件对象
readerBuilder.file(PATH+"课程体系.xlsx");
// 指定sheet
readerBuilder.sheet();
// 自动关闭输入流
readerBuilder.autoCloseStream(true);
// 设置文件格式
readerBuilder.excelType(ExcelTypeEnum.XLSX);
// 注册监听器进行数据解析
readerBuilder.registerReadListener(new AnalysisEventListener<Map<Integer,String>>() {
@Override
public void invoke(Map<Integer, String> integerStringMap, AnalysisContext analysisContext) {
// 一行数据读取完成后进行回调, 一行数据封装成一个Map对象后回调
Set<Integer> keySet = integerStringMap.keySet();
Iterator<Integer> iterator = keySet.iterator();
while (iterator.hasNext()) {
Integer key = iterator.next();
System.out.print(key + ":" + integerStringMap.get(key) + ", ");
}
System.out.println();
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
// 文件读取完毕日志
System.out.println("文件读取完毕!");
}
});
// 构建读取器
ExcelReader reader = readerBuilder.build();
// 读取数据
reader.readAll();
// 读取完毕
reader.finish();
}
(3)读取–返回Map对象,存入List数组中,简略写法
@Test
public void shortReadList(){
List<Map<Integer,String>> list = new LinkedList<>();
EasyExcel.read(PATH+"课程体系.xlsx")
.sheet()
.registerReadListener(new AnalysisEventListener<Map<Integer,String>>() {
@Override
public void invoke(Map<Integer, String> map, AnalysisContext analysisContext) {
list.add(map);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("数据读取完毕~!");
}
}).doRead();
// System.out.println(list);
for (Map<Integer,String> map: list) {
Set<Integer> keySet = map.keySet();
Iterator<Integer> iterator = keySet.iterator();
while (iterator.hasNext()) {
Integer key = iterator.next();
System.out.print(key + ":" + map.get(key) + ", ");
}
System.out.println();
}
}
(4)读取–封装成Course对象返回,简略写法
- 添加head方法,将封装对象写入
.head(Course.class)
- 在Course实体类的属性上添加注解
@ExcelProperty("属性名称")
- 数据存储在list中
@SpringBootTest
public class ExcelReadTest {
String PATH = "C:\\Users\\wano\\Desktop\\毕设资料\\技术Demo\\Excel读写\\myExcel\\src\\main\\resources\\static\\testData\\";
@Test
public void shortReadCourse(){
List<Course> list = new LinkedList<>();
EasyExcel.read(PATH+"课程体系.xlsx")
.head(Course.class)
.sheet()
.registerReadListener(new AnalysisEventListener<Course>() {
@Override
public void invoke(Course course, AnalysisContext analysisContext) {
list.add(course);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("数据读取完毕~!");
}
}).doRead();
for (Course c: list) {
System.out.println(c);
}
}
}
(5)实际应用(涉及数据库,完成Excel和数据库的数据的存储和转换)
1、依赖
<!-- MySQL驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- MyBatis整合SpringBoot框架的起步依赖-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.2</version>
</dependency>
<!--easyexcel依赖-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>
<!--JSON依赖-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.66</version>
</dependency>
<!--lombok-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.22</version>
</dependency>
2、实体类Course及相关注解
package com.wano.model;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
@Data
public class Course {
@ExcelProperty("课程ID")
private Integer id;
@ExcelProperty("课程名称")
private String name;
@ExcelProperty("课程类型")
private String type;
@ExcelProperty("课程性质")
private String properties;
@ExcelProperty("开课学期")
private Integer term;
@ExcelProperty("课程学分")
private Double credit;
@ExcelProperty("理论学时")
private Integer theoryHour;
@ExcelProperty("实验学时")
private Integer expHour;
@ExcelProperty("总学时")
private Integer allHour;
}
3、Mapper中批量保存数据方法
mapper
// 批量插入数据
int saveTest(List<Course> list);
mapper.xml
<insert id="saveTest">
INSERT INTO course (id, name, type,
properties, term, credit,
theory_hour, exp_hour, all_hour
)
VALUES
<foreach collection="list" close="" index="index" item="item" open="" separator=",">
(#{item.id},#{item.name},#{item.type},
#{item.properties},#{item.term},#{item.credit},
#{item.theoryHour},#{item.expHour},#{item.allHour})
</foreach>
</insert>
4、test中写读取Excel数据并保存到数据库中的方法。
@SpringBootTest
public class ExcelReadTest {
String PATH = "C:\\Users\\wano\\Desktop\\毕设资料\\技术Demo\\Excel读写\\myExcel\\src\\main\\resources\\static\\testData\\";
@Autowired
private CourseService courseService;
@Test
public void shortReadCourse(){
List<Course> list = new LinkedList<>();
EasyExcel.read(PATH+"课程体系.xlsx")
.head(Course.class)
.sheet()
.registerReadListener(new AnalysisEventListener<Course>() {
@Override
public void invoke(Course course, AnalysisContext analysisContext) {
list.add(course);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("数据读取完毕~!");
}
}).doRead();
courseService.saveTest(list);
/*for (Course course:list
) {
System.out.println(course);
}*/
}
}
3、写入Excel数据
- pom文件
- 实体类,加注解
- 编写一个数据池,准备写入的数据
- 将数据写入Excel表中
(1)pom文件(完整)
<dependencies>
<!-- MySQL驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- MyBatis整合SpringBoot框架的起步依赖-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.2</version>
</dependency>
<!--easyexcel依赖-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>
<!--JSON依赖-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.66</version>
</dependency>
<!--lombok-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.22</version>
</dependency>
<!--SpringBoot web起步依赖-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--SpringBoot 测试起步依赖-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<resources>
<!--手动指定文件夹为resource,将mapper.xml读入资源文件夹中-->
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
</resource>
</resources>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
<!--mybatis 代码自动生成插件-->
<plugin>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-maven-plugin</artifactId>
<version>1.3.6</version>
<configuration>
<!--配置文件的位置-->
<configurationFile>GeneratorMapper.xml</configurationFile>
<verbose>true</verbose>
<overwrite>true</overwrite>
</configuration>
</plugin>
</plugins>
</build>
(2)实体类,加注解
package com.wano.model;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
@Data
public class GradRequire {
@ExcelProperty("id")
private String id;
@ExcelProperty("毕业要求编码")
private String code;
@ExcelProperty("毕业要求名称")
private String name;
@ExcelProperty("毕业要求内容描述")
private String content;
}
(3)准备一个数据池,将数据写入Excel表中
package com.wano;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.wano.model.GradRequire;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.LinkedList;
import java.util.List;
@SpringBootTest
public class ExcelTest {
String PATH = "C:\\Users\\wano\\Desktop\\毕设资料\\数据库设计\\测试数据\\";
/**
* 读取Excel中的数据
*/
@Test
public void ExcelRead(){
List<GradRequire> list = new LinkedList<>();
// EasyExcel.read(PATH+"测试数据.xlsx")
EasyExcel.read(PATH+"grad_require.xlsx")
.head(GradRequire.class)
.sheet()
.registerReadListener(new AnalysisEventListener<GradRequire>() {
@Override
public void invoke(GradRequire gradRequire, AnalysisContext analysisContext) {
list.add(gradRequire);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("数据读取完毕~!");
}
}).doRead();
//pointService.saveTest(list);
for (GradRequire gradRequire:list) {
System.out.println(gradRequire);
}
}
//数据库 将上面Excel中读取的数据保存下来,写入另一个Excel中
public List<GradRequire> ExcelData(){
List<GradRequire> list = new LinkedList<>();
EasyExcel.read(PATH+"grad_require.xlsx")
.head(GradRequire.class)
.sheet()
.registerReadListener(new AnalysisEventListener<GradRequire>() {
@Override
public void invoke(GradRequire gradRequire, AnalysisContext analysisContext) {
list.add(gradRequire);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("数据读取完毕~!");
}
}).doRead();
return list;
}
/**
* 将数据写入Excel中
*/
@Test
public void ExcelWrite(){
List<GradRequire> list = ExcelData();
EasyExcel.write(PATH+"测试数据.xlsx")
.head(GradRequire.class) //写入字段标题
.excelType(ExcelTypeEnum.XLSX)
.sheet("毕业要求表")
.doWrite(list);
}
}