使用EasyExcel简单导入导出

1.创建一个SpringBoot工程并添加依赖项

<!--easyexcel依赖项(必须手动添加)-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.1.0</version>
</dependency>
​
<!--SpringBoot依赖项(勾选)-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
​
<!--lombok依赖项(自动生成getter setter等方法)-->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>
​
<!--springBoot测试依赖项-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>
​
<!--mybatis依赖项(用于存储数据以及读取数据)-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.2.2</version>
</dependency>
​
<!-- 数据库连接池 -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.21</version>
</dependency>

配置properties文件

spring.datasource.url=jdbc:mysql://localhost:3306/excel?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
​
mybatis.mapper-locations=classpath:mapper/*.xml

添加配置类

package cn.tedu.easyexcel.demo.config;
​
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Configuration;
​
@Configuration
@MapperScan("cn.tedu.easyexcel.demo.mapper")
public class MybatisConfiguration {
}

2.创建实体类,用于对应excel表格中的列

package cn.tedu.easyexcel.demo.entity;
​
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
​
import java.io.Serializable;
​
@Data
public class DemoData implements Serializable {
    private Long id;
    @ExcelProperty(index = 0,value = "省")
    private String province;
    @ExcelProperty(index = 1,value = "市")
    private String city;
    @ExcelProperty(index = 2,value = "区")
    private String district;
}

3.创建持久层(插入和查询)

package cn.tedu.easyexcel.demo.mapper;
​
import cn.tedu.easyexcel.demo.entity.DataVO;
import cn.tedu.easyexcel.demo.entity.DemoData;
import org.springframework.stereotype.Repository;
​
import java.util.List;
​
@Repository
public interface ReadMapper {
    int save(DemoData data);
​
    List<DataVO> list();
}

4.编写SQL语句

<?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="cn.tedu.easyexcel.demo.mapper.ReadMapper">
    <insert id="save" useGeneratedKeys="true" keyProperty="id">
        insert into location(
            province,
            city,
            district
        ) values (
            #{province},#{city},#{district}
        )
    </insert>
​
    <select id="list" resultMap="SimpleResult">
        select
        <include refid="Sql" />
        from location order by id;
    </select>
​
    <sql id="Sql">
        id,
        province,
        city,
        district
    </sql>
​
    <resultMap id="SimpleResult" type="cn.tedu.easyexcel.demo.entity.DataVO">
        <id column="id" property="id"></id>
        <result column="province" property="province"/>
        <result column="city" property="city"/>
        <result column="district" property="district"/>
    </resultMap>
</mapper>

5.测试导入数据和导出数据

定义一个模板来构建数据

private List<DataVO> getDemoData() {
    List<DataVO> list = mapper.list();
    return list;
}

导入数据

/**
 * 导入数据
 */
@Test
@Sql("classpath:truncate.sql")
public void testReadExcel() {
    // 读取的excel文件路径
    String filename = "C:\\Users\\lenovo\\Desktop\\demo.xlsx";
    // 读取excel
    EasyExcel.read(filename, DemoData.class, new AnalysisEventListener<DemoData>() {
        // 每解析一行数据,该方法会被调用一次
        @Override
        public void invoke(DemoData demoData, AnalysisContext analysisContext) {
            System.out.println("解析数据为:" + demoData.toString());
            mapper.save(demoData);
        }
        // 全部解析完成被调用
        @Override
        public void doAfterAllAnalysed(AnalysisContext analysisContext) {
            System.out.println("解析完成...");
            // 可以将解析的数据保存到数据库
        }
    }).sheet().doRead();
}

 

导出数据

@Test
public void writeTests() {
    String fileName = "C:\\Users\\lenovo\\Desktop\\demo1.xlsx";
    //创建ExcelWriter对象
    ExcelWriter excelWriter = EasyExcel.write(fileName,DemoData.class).build();
    //创建sheet对象
    WriteSheet sheet = EasyExcel.writerSheet("地区").build();
    //向Excel中写入数据
    excelWriter.write(getDemoData(),sheet);
    //关闭流
    excelWriter.finish();
}