SpringBoot整合EasyPoi实现Excel文件的导入和导出

1.整合依赖dependencies

 <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <!-- 去掉springboot默认日志配置 -->
            <exclusions>
                <exclusion>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-starter-logging</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency> <!-- 引入log4j2依赖 -->
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-log4j2</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.1</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.16</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.23</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>4.2.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.0.0</version>
            <exclusions>
                <exclusion>
                    <groupId>com.google.guava</groupId>
                    <artifactId>guava</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.6</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
    </dependencies>

2.application.yml和application-dev.yml

server:
  port: 8084

spring:
  profiles:
    active: dev

logging:
  config: classpath:log4j2.xml

server:
  port: 8085

spring:
  application:
    name: userApps
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/db1?useUnicode=true&characterEncoding=utf-8
    username: root
    password: 123456
    type: com.alibaba.druid.pool.DruidDataSource
mybatis:
  mapper-locations: classpath:mapper/*Dao.xml
  type-aliases-package: com.concom.domain
  configuration:
    map-underscore-to-camel-case: true

3.实体类

package com.concom.domain;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelEntity;
import com.fasterxml.jackson.annotation.JsonIdentityInfo;
import lombok.Data;
import lombok.ToString;

@Data
@ToString
public class User {
	@Excel(name = "序号", width = 8,isImportField = "true")
	private int id;
	@Excel(name = "姓名", width = 8,isImportField = "true")
	private String userName;
	@Excel(name = "性别", width = 8,isImportField = "true")
	private String userSex;
	@Excel(name = "年龄", width = 8,isImportField = "true")
	private int userAge;
	@Excel(name = "联系方式", width = 8,isImportField = "true")
	private String userTelephone;
}

4.控制器

package com.concom.controller;

import com.concom.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@RestController
@RequestMapping("/user")
public class UserController {
	@Autowired
	private UserService userService;

	@PostMapping(value = "/import")
	public String importExcel(@RequestParam(value = "file") MultipartFile file) {
		userService.importExcel(file);
		return "上传成功";
	}

	@GetMapping(value = "/downLoad")
	public String downLoadExcel(HttpServletRequest request, HttpServletResponse response) {
		userService.downLoadExcel(request, response);
		return "下载成功";
	}
}

5.业务层

package com.concom.service.impl;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import com.concom.dao.UserDao;
import com.concom.domain.User;
import com.concom.service.UserService;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;

@Service
@Slf4j
public class UserServiceImpl implements UserService {
    @Autowired
    private UserDao userDao;
    @Override
    public void importExcel(MultipartFile file) {
        log.info("{}===开始导入数据{}===");
        ImportParams importParams = new ImportParams();
        try{
            ExcelImportResult<User> data = ExcelImportUtil.importExcelMore(file.getInputStream(), User.class, importParams);
            List<User> userList = data.getList();
            userDao.saveUser(userList);
            log.info("{}===导入数据成功");
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    @Override
    public void downLoadExcel(HttpServletRequest request, HttpServletResponse response) {
        log.info("{}===开始导出数据库中的数据{}===");
        //从数据库中查出所有的数据
        List<User> userList=userDao.findAllUsers();
        response.setContentType("application/vnd.ms-excel");
        OutputStream out=null;
        try{
            response.setHeader("Content-Disposition","attachment;filename="+ URLEncoder.encode("用户表.xls","utf-8"));
            Workbook workBook=ExcelExportUtil.exportExcel(new ExportParams("用户信息表","用户"),User.class,userList);
            out=response.getOutputStream();
            workBook.write(out);
        }catch(Exception e){
            e.printStackTrace();
            log.error("{}数据导出失败,失败原因是:"+e.getMessage());
        }finally {
            try {
                out.close();
            }catch (IOException e){
                log.error("下载失败，原因是:{}",e);
            }
        }
        log.info("{}===数据导出成功{}===");
    }
}

6.数据层

package com.concom.dao;

import com.concom.domain.User;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface UserDao {
	/**
	 * 查询所有的用户
	 * @return
	 */
	List<User> findAllUsers();

	/**
	 * 导入数据
	 * @param userList
	 */
	void saveUser(List<User> userList);
}

7.mapper映射文件UserDaox.ml

<?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.concom.dao.UserDao">
        <select id="findAllUsers" resultType="user">
            select id,user_name,user_sex,user_age,user_telephone from t_user
        </select>
        <insert id="saveUser">
            insert into t_user values
            <foreach collection="list" item="user" separator=",">
                (id=#{user.id},#{user.userName},#{user.userSex},#{user.userAge},#{user.userTelPhone})
            </foreach>
        </insert>
</mapper>

8.SQL语句

DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(16) DEFAULT NULL,
  `user_sex` varchar(3) DEFAULT NULL,
  `user_age` int(10) DEFAULT NULL,
  `user_telephone` varchar(11) DEFAULT NULL,
  `user_pwd` varchar(80) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

注:本文为初学者，不足之处还请各位见谅，仅供参考，谢谢！