EasyExcel ----- 实现数据库数据导出为Excel表和Excel表导入数据功能

最新推荐文章于 2025-07-06 15:13:51 发布

零碎de記憶

最新推荐文章于 2025-07-06 15:13:51 发布

阅读量6.3k

点赞数 10

CC 4.0 BY-SA版权

分类专栏： EasyExcel

本文链接：https://blog.youkuaiyun.com/qq_39135287/article/details/98348091

一、EasyExcel简介

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

EasyExcel阿里官方文档：https://github.com/alibaba/easyexcel

二、EasyExcel的简单使用

1、搭建EasyExcel项目环境

1.1、添加EasyExcel所需依赖

<properties>
    <poi.version>3.17</poi.version>
    <spring.version>5.1.5.RELEASE</spring.version>
</properties>

<dependencies>
    <!-- ******************** EasyExcel依赖包 开始 ******************** -->
    <!-- xls格式 excel依赖包 -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>${poi.version}</version>
    </dependency>
    
    <!--xlsx格式 excel依赖包-->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>${poi.version}</version>
    </dependency>
       
    <!-- EasyExcel依赖包 -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>1.1.2-beta5</version>
     </dependency>
     <!-- ******************** EasyExcel依赖包 结束 ******************** -->
     
     <!-- MySql驱动 -->
     <dependency>
         <groupId>mysql</groupId>
         <artifactId>mysql-connector-java</artifactId>
         <version>5.1.47</version>
     </dependency>

     <!-- Druid连接池 -->
     <dependency>
         <groupId>com.alibaba</groupId>
         <artifactId>druid</artifactId>
         <version>1.1.10</version>
     </dependency>
     
     <!-- Mybatis -->
     <dependency>
         <groupId>org.mybatis</groupId>
         <artifactId>mybatis</artifactId>
         <version>3.4.6</version>
     </dependency>
     <dependency>
         <groupId>org.mybatis</groupId>
         <artifactId>mybatis-spring</artifactId>
         <version>1.3.2</version>
     </dependency>

     <!-- Lombok -->
     <dependency>
         <groupId>org.projectlombok</groupId>
         <artifactId>lombok</artifactId>
         <version>1.16.20</version>
         <scope>provided</scope>
     </dependency>
     
     <!-- javax.servlet-api -->
     <dependency>
         <groupId>javax.servlet</groupId>
         <artifactId>javax.servlet-api</artifactId>
         <version>3.1.0</version>
         <scope>provided</scope>
     </dependency>

     <!-- Spring -->
     <dependency>
         <groupId>org.springframework</groupId>
         <artifactId>spring-context</artifactId>
         <version>${spring.version}</version>
     </dependency>
     <dependency>
         <groupId>org.springframework</groupId>
         <artifactId>spring-beans</artifactId>
         <version>${spring.version}</version>
     </dependency>
     <dependency>
         <groupId>org.springframework</groupId>
         <artifactId>spring-webmvc</artifactId>
         <version>${spring.version}</version>
     </dependency>
     <dependency>
         <groupId>org.springframework</groupId>
         <artifactId>spring-jdbc</artifactId>
         <version>${spring.version}</version>
     </dependency>
     <dependency>
         <groupId>org.springframework</groupId>
         <artifactId>spring-aspects</artifactId>
         <version>${spring.version}</version>
     </dependency>
     <dependency>
         <groupId>org.springframework</groupId>
         <artifactId>spring-jms</artifactId>
         <version>${spring.version}</version>
     </dependency>
     <dependency>
         <groupId>org.springframework</groupId>
         <artifactId>spring-context-support</artifactId>
         <version>${spring.version}</version>
     </dependency>
     
     <!-- junit单元测试 -->
     <dependency>
         <groupId>junit</groupId>
         <artifactId>junit</artifactId>
         <version>4.11</version>
         <scope>test</scope>
     </dependency>

</dependencies>

      
     <!-- 打包资源文件 -->
     <build>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
            </resource>
        </resources>
    </build>

1.2、设计并创建对应的user数据库表

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(11) DEFAULT NULL,
  `sex` varchar(3) DEFAULT NULL,
  `age` int(4) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1.3、在resources资源目录下，创建一个properties目录，并创建和编写jdbc.properties配置文件，设置jdbc连接数据库的四个参数

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/easy_excel?rewriteBatchedStatements=true&allowMultiQueries=true&useUnicode=true&characterEncoding=utf-8&autoReconnect=true&failOverReadOnly=false&useSSL=false
jdbc.username=root
jdbc.password=123456

---------------------------mysql 连接数据库参数设置介绍

//设置JDBC驱动批量执行SQL
rewriteBatchedStatements=true 

//设置允许JDBC连接能够一次执行多条增删改查操作,假如没配这个参数的话，所有批量操作都会报错。
allowMultiQueries=true       

//指定字符的编码、解码格式
useUnicode=true&characterEncoding=utf-8  

//在使用数据库连接池的情况下,最好设置如下两个参数
autoReconnect=true  //当数据库连接异常中断时，自动重新连接
failOverReadOnly=false   //自动重连成功后，连接是否设置为只读

//设置关闭SSL连接
useSSL=false

1.4、在resources资源目录下，创建一个spring目录，并创建和编写applicationContext-spring.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans-4.2.xsd
	   http://www.springframework.org/schema/context
	   http://www.springframework.org/schema/context/spring-context-4.2.xsd
	   http://www.springframework.org/schema/tx
	   http://www.springframework.org/schema/tx/spring-tx-4.2.xsd
       http://www.springframework.org/schema/aop
       http://www.springframework.org/schema/aop/spring-aop-4.2.xsd">

    <!-- 配置包扫描器，扫描注解的类 -->
    <context:component-scan base-package="com.easyexcel.service"/>

    <!-- 加载配置文件 -->
    <context:property-placeholder location="classpath:properties/jdbc.properties" />
    <!-- 数据库连接池 -->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
        <property name="url" value="${jdbc.url}" />
        <property name="username" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />
        <property name="driverClassName" value="${jdbc.driver}" />
        <property name="maxActive" value="10" />
        <property name="minIdle" value="5" />
    </bean>

    <!-- 让spring管理sqlsessionfactory 使用mybatis和spring整合包中的 -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <!-- 数据库连接池 -->
        <property name="dataSource" ref="dataSource" />
    </bean>

    <!-- MapperScannerConfigurer 自动扫描将Mapper接口生成代理注入到spring -->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <!--basePackage指定要扫描的包，在此包之下的映射器都会被搜索到。可指定多个包，包与包之间用逗号或分号分隔-->
        <property name="basePackage" value="com.easyexcel.mapper" />
    </bean>

    <!-- 事务管理器 -->
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <!-- 数据源 -->
        <property name="dataSource" ref="dataSource" />
    </bean>

    <!-- 通知 -->
    <tx:advice id="txAdvice" transaction-manager="transactionManager">
        <tx:attributes>
            <!-- 传播行为 -->
            <tx:method name="save*" propagation="REQUIRED" />
            <tx:method name="insert*" propagation="REQUIRED" />
            <tx:method name="add*" propagation="REQUIRED" />
            <tx:method name="create*" propagation="REQUIRED" />
            <tx:method name="delete*" propagation="REQUIRED" />
            <tx:method name="update*" propagation="REQUIRED" />
            <tx:method name="find*" propagation="SUPPORTS" read-only="true" />
            <tx:method name="select*" propagation="SUPPORTS" read-only="true" />
            <tx:method name="get*" propagation="SUPPORTS" read-only="true" />
        </tx:attributes>
    </tx:advice>
    <!-- 切面 -->
    <aop:config>
        <aop:advisor advice-ref="txAdvice" pointcut="execution(* com.easyexcel.service.*.*(..))" />
    </aop:config>

</beans>

2、使用EasyExcel导入、导出带有表头注解的Java实体类模型的Excel

2.1、编写与数据库表对应的带有表头注解的实体类模型 (需要继承BaseRowModel类)

package com.easyexcel.pojo;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.ToString;

import java.io.Serializable;
import java.util.Date;

@Getter
@Setter
@ToString
@NoArgsConstructor
public class User extends BaseRowModel implements Serializable {

    /**
     * @ExcelProperty(value = "表头名称",index = 0,format = "yyyy-MM-dd")
     * value是表头数据，默认会写在excel的表头位置
     * index代表第几列，从0开始
     * format表示时间日期输出到Excel表中的格式
     */
    @ExcelProperty(value="主键id",index=0)
    private Integer id;
    @ExcelProperty(value="姓名",index=1)
    private String name;
    @ExcelProperty(value="性别",index=2)
    private String sex;
    @ExcelProperty(value="年龄",index=3)
    private Integer age;
    @ExcelProperty(value="生日",index=4,format="yyyy-MM-dd")
    private Date birthday;

    private static final long serialVersionUID = 1L;

}

2.2、编写与数据库交互的UserMapper接口

package com.easyexcel.mapper;

import com.easyexcel.pojo.User;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface UserMapper {

    /**
     * 查询所有用户信息
     * @return List<User>
     */
    List<User> selectAllOfUser();

    /**
     * 批量插入所有用户信息
     * @param users 用户信息集合
     * @return int
     */
    int insertAllOfUser(@Param("users") List<User> users);

}

2.3、编写与UserMapper接口对应的UserMapper.xml

<?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="com.easyexcel.mapper.UserMapper" >
  <resultMap id="BaseResultMap" type="com.easyexcel.pojo.User" >
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="name" property="name" jdbcType="VARCHAR" />
    <result column="sex" property="sex" jdbcType="VARCHAR" />
    <result column="age" property="age" jdbcType="INTEGER" />
    <result column="birthday" property="birthday" jdbcType="DATE" />
  </resultMap>

  <sql id="Base_Column_List" >
    id, name, sex, age, birthday
  </sql>

  <select id="selectAllOfUser" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from user
  </select>

  <insert id="insertAllOfUser" parameterType="com.easyexcel.pojo.User" >
    insert into user 
    (<include refid="Base_Column_List" />)
    values
    <foreach collection="users" item="user" separator=",">
      (#{user.id},#{user.name},#{user.sex},#{user.age},#{user.birthday})
    </foreach>
  </insert>
  
</mapper>

2.4、编写UserService接口

package com.easyexcel.service;

import com.easyexcel.pojo.User;
import java.util.List;

public interface UserService {

    /**
     * 查询所有用户信息
     *
     * @return List<User> User实体类对象集合
     */
    List<User> selectAllOfUser();

    /**
     * 批量插入所有用户信息
     *
     * @param users 用户信息集合
     * @return int 影响行数
     */
    int insertAllOfUser(List<User> users);

}

2.5、编写UserService接口的实现类UserServiceImpl

package com.easyexcel.service.impl;

import com.easyexcel.mapper.UserMapper;
import com.easyexcel.pojo.User;
import com.easyexcel.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;


@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserMapper userMapper;

    @Override
    public List<User> selectAllOfUser() {
        return userMapper.selectAllOfUser();
    }

    @Override
    public int insertAllOfUser(List<User> users) {
        return userMapper.insertAllOfUser(users);
    }
}

2.6、编写ExcelException异常处理器

package com.easyexcel.exception;

public class ExcelException extends RuntimeException{

    public ExcelException(String message){
        super(message);
    }
}

2.7、编写Exc