easyexcel从数据库导出excel (easy excel导出数据库表)

这是一个最简单的例子，使用了Springboot/Thymeleaf/Mybatis等技术完成了初步的导入，给和我一样的初学者提供一点参考。

一、数据表结构

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
 `age` int(255) NULL DEFAULT NULL COMMENT '年龄',
 PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 350 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

二、实体类

package pers.gl.entity;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

@Data
public class User {
private String id;
@ExcelProperty(value = "姓名", index = 0)
private String name;
@ExcelProperty(value = "年龄", index = 1)
private Integer age;
}

三、mybatis的xml文件

使用了批量插入数据的语句foreach。

<?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="pers.gl.dao.UserMapper">
<!-- 查询全部 -->
   <select id="findAll" resultType="pers.gl.entity.User">
      select * from user order by id desc
   </select>
<!-- 单条插入数据-->
   <insert id="insertData" parameterType="pers.gl.entity.User">
      insert into user(name,age) values(#{name},#{age})
    </insert>
    <!-- 批量插入数据-->
<insert id="saveList">
      insert into user(name,age) values
       <foreach item="item" index="index" collection="list" separator=",">
          (#{item.name},#{item.age})
       </foreach>
   </insert>
</mapper>

四、mapper接口

package pers.gl.dao;
import java.util.List;
import pers.gl.entity.User;
public interface UserMapper {
// 全部数据
public List<User> findAll();
// 插入数据
public int insertData(User user);
// 批量增加保存
public int saveList(List<User> list);
}

五、Service

package pers.gl.service;

import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.alibaba.excel.EasyExcel;
import pers.gl.dao.UserMapper;
import pers.gl.entity.User;

@Service
public class UserService {
@Autowired
private UserMapper userMapper;
// 全部数据
public List<User> findAll() {
return userMapper.findAll();
}
// 插入数据
public int insertData(User user) {
return userMapper.insertData(user);
}
// 批量增加保存
public int saveList(List<User> list) {
return userMapper.saveList(list);
}
   //导出EXCEL文件
public void exportExcel(HttpServletResponse response) {
// 需要导出的数据
List<User> list = findAll();
// 设置响应类型
response.setContentType("application/vnd.ms-excel");
// 设置字符编码
response.setCharacterEncoding("utf-8");
// 设置文件名字
String fileName = "downFile_" + System.currentTimeMillis() + ".xlsx";
try {
// 设置响应头信息
response.setHeader("Content-Disposition",
"attachment; filename=" + java.net.URLEncoder.encode(fileName, "UTF-8"));

// 写入文件数据
EasyExcel.write(response.getOutputStream(), User.class).sheet("download").doWrite(list);
} catch (Exception e) {
throw new RuntimeException(e);
}
}

}

六、ExcelListener监听

package pers.gl.service;

import java.util.ArrayList;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import pers.gl.entity.User;

public class ExcelListener extends AnalysisEventListener<User> {
private static final Logger logger = LoggerFactory.getLogger(User.class);
/**
 * 每隔5条存储数据库，实际使用中可以3000条，然后清理list，方便内存回收
 */
private static final int BATCH_COUNT = 5;
List<User> list = new ArrayList<>();

private UserService userService;

// 构造函数，一定要写，添加到监听中
public ExcelListener(UserService userService) {
this.userService = userService;
}

@Override
public void invoke(User data, AnalysisContext context) {
// TODO Auto-generated method stub
logger.info("invoke方法被调用");
logger.info("解析到一条数据:{}", JSON.toJSONString(data));
list.add(data);
// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
if (list.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
list.clear();
}
}

/**
 * 所有数据解析完成了 都会来调用
 *
 * @param context
 */
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// TODO Auto-generated method stub
System.out.println("doAfterAllAnalysed方法 被调用");
// 这里也要保存数据，确保最后遗留的数据也存储到数据库
saveData();
logger.info("所有数据解析完成！");
}

/**
 * 加上存储数据库
 */
private void saveData() {
logger.info("{}条数据，开始存储数据库！", list.size());
userService.saveList(list);
logger.info("存储数据库成功！");
}
   
}

七、Controller控制器

package pers.gl.controller;

import java.io.IOException;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.multipart.MultipartFile;
import com.alibaba.excel.EasyExcel;
import pers.gl.entity.User;
import pers.gl.service.ExcelListener;
import pers.gl.service.UserService;

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

@GetMapping("list")
public String list(Model model) {
List<User> users = userService.findAll();
model.addAttribute("users", users);
return "userlist";
}

@GetMapping("/download")
public void excelExport02(HttpServletResponse response) {
userService.exportExcel(response);
}

@GetMapping("/import")
public String toImportExcelPage() {
return "importexcel";
}

@PostMapping("/uploadExcel")
public String upload(MultipartFile file) throws IOException {
EasyExcel.read(file.getInputStream(), User.class, new ExcelListener(userService)).sheet().doRead();
return "redirect:/user/list";
}
}

八、前台上传页面

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>导入EXCEL</title>
</head>
<body>
<form th:action="@{/user/uploadExcel}" method="post" enctype="multipart/form-data">
   <input type="file" name="file"/>
   <input type="submit" value="上传文件">
</form>
</body>
</html>