SpringBoot实现Excel文件上传至数据库与下载数据库数据为Excel
这里是整合poi实现的，后来发现poi有一些缺陷，但这些缺陷被阿里的EasyExcel解决了，于是采用了第二种。
一、第一种（不推荐，推荐下方第二种使用easyExcel）
首先创建一个SpringBoot项目，pom.xml中加入操作Excel需要的jar包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
创建库
usercontroller
/**
* (User)表控制层
*
* @author makejava
* @since 2020-07-22 09:57:55
*/
@RestController
@RequestMapping("user")
public class UserController {
/**
* 服务对象
*/
@Resource
private UserService userService;
/**
* 通过主键查询单条数据
*
* @param id 主键
* @return 单条数据
*/
@GetMapping("selectOne")
public User selectOne(Integer id) {
return this.userService.queryById(id);
}
@RequestMapping(value = "/uploadExcl")
public @ResponseBody
Map<String ,Object> uploadExcl(HttpServletRequest request, @RequestParam("file") MultipartFile file){
Map<String ,Object> result = new HashMap<>();
String path = request.getSession().getServletContext().getRealPath("/");
try{
// 如果文件不为空，写入上传路径
if(!file.isEmpty()){
result = userService.uploadExcl(file);
}else {
result.put("code","1");
result.put("message","上传文件为空！");
}
}catch (Exception e){
e.printStackTrace();
}
if (result.get("code").equals("0")){
//根据时间戳创建新的文件名，这样即便是第二次上传相同名称的文件，也不会把第一次的文件覆盖了
//也可以用UUID创建
String fileName = System.currentTimeMillis() + file.getOriginalFilename();
//通过req.getServletContext().getRealPath("") 获取当前项目的真实路径，然后拼接前面的文件名
String destFileName = request.getContextPath()+ "uploaded" + File.separator + fileName;
System.out.println(request.getServletPath());
System.out.println(request.getServletContext());
System.out.println(request.getServletContext().getRealPath(""));
System.out.println(request.getServletContext().getRealPath("/"));
System.out.println(request.getContextPath());
System.out.println(destFileName);
//第一次运行的时候，这个文件所在的目录往往是不存在的，这里需要创建一下目录
File destFile = new File(destFileName);
destFile.getParentFile().mkdirs();
System.out.println(destFile);
//把浏览器上传的文件复制到希望的位置
try {
file.transferTo(destFile);
} catch (IOException e) {
e.printStackTrace();
}
System.out.println(fileName);
}
return result;
}
}
在UserService中新增一个接口
public Map<String,Object> uploadExcl(MultipartFile file);
实现该接口
/**
* 读取excl并插入到数据中
* @param file
* @return
*/
@Override
public Map<String,Object> uploadExcl(MultipartFile file) {
Map<String,Object> ruslt = new HashMap<>();
try {
String fileName = file.getOriginalFilename();
//判断文件格式并获取工作簿
Workbook workbook;
if(fileName.endsWith("xls")){
workbook = new HSSFWorkbook(file.getInputStream());
}else if(fileName.endsWith("xlsx")){
workbook = new XSSFWorkbook(file.getInputStream());
} else {
ruslt.put("code","1");
ruslt.put("message","文件格式非excl");
return ruslt;
}
//判断第一页不为空
if(null != workbook.getSheetAt(0)){
//读取excl第二行，从1开始
for(int rowNumofSheet = 1;rowNumofSheet <=workbook.getSheetAt(0).getLastRowNum();rowNumofSheet++){
if (null != workbook.getSheetAt(0).getRow(rowNumofSheet)) {
//定义行，并赋值
Row aRow = workbook.getSheetAt(0).getRow(rowNumofSheet);
User user = new User();
System.out.println(aRow.getLastCellNum());
for(int cellNumofRow=0;cellNumofRow<aRow.getLastCellNum();cellNumofRow++){
//读取rowNumOfSheet值所对应行的数据
//获得行的列数
Cell xCell = aRow.getCell(cellNumofRow);
Object cell_val;
if(cellNumofRow == 0){
if(xCell != null && !xCell.toString().trim().isEmpty()){
cell_val = xCell.getStringCellValue();
if(cell_val != null){
String temp = (String)cell_val;
user.setName(temp);
}
}
}
if(cellNumofRow == 1){
if(xCell != null && !xCell.toString().trim().isEmpty()){
cell_val = xCell.getStringCellValue();
if(cell_val != null){
String temp = (String)cell_val;
if("男".equals(temp)){
user.setSex("1");
} else {
user.setSex("0");
}
user.setCreateTime(new Date());
userDao.insert(user);
}
}
}
}
}
}
ruslt.put("code","0");
ruslt.put("message","成功插入数据库！");
}else {
ruslt.put("code","1");
ruslt.put("message","第一页EXCL无数据！");
}
}catch (Exception e){
e.printStackTrace();
ruslt.put("code","1");
ruslt.put("message",e.getMessage());
}
return ruslt;
}
在resource/static下新建一个index.html页面作为前端上传Excel上传页面
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>上传excl</title>
<script src="https://apps.bdimg.com/libs/jquery/2.1.4/jquery.min.js">
</script>
</head>
<body>
<div class="panel panel-primary">
<!-- .panel-heading 面板头信息。 -->
<div class="panel-heading">
<!-- .panel-title 面板标题。 -->
<h1 class="panel-title">上传excl并插入到数据库</h1>
</div>
</div>
<form class="form-horizontal" action="/user/uploadExcl" enctype="multipart/form-data" method="post">
<div class="form-group">
<div class="input-group col-md-4">
<span class="input-group-addon">
<i class="glyphicon glyphicon-search"></i>
</span>
<input class="form-control" placeholder="请选择文件" type="file" name="file"/>
</div>
</div>
<div class="form-group">
<div class="col-md-4">
<div class="btn-group btn-group-justified" >
<div class="btn-group" >
<button type="submit" class="btn btn-success" id="submitbtn">
<span class="glyphicon glyphicon-share"></span> 文件上传</button>
</div>
</div>
</div>
</div>
</form>
<table border="0" style="margin-top:4px; margin-left: 18px">
<tr>
<td><a href="#" class="easyui-linkbutton" onclick="downloadfile();">数据导出</a></td>
</tr>
</table>
<script>
function downloadfile(){
window.location.href="/user/UserExcelDownloads";
}
</script>
</body>
</html>
启动服务，输入下面网址上传文件，再去数据库中查看，数据是成功插入了
http://localhost:8088/
Excel文件内容如下
接下来编写下载数据库数据为Excel的代码，easycode生成的代码动态查询所有数据在mapper中的sql和dao层的接口是编写了，但是在service层确没有编写接口及实现类，这里先补一下
UserService
public List<User> queryAll(User user);
@Override
public List<User> queryAll(User user) {
return this.userDao.queryAll(user);
}
在UserController中添加下载方法
/**
* 下载数据库数据为exles
*
* @param response
* @throws IOException
*/
@RequestMapping(value = "UserExcelDownloads", method = RequestMethod.GET)
public void downloadAllClassmate(HttpServletResponse response) throws IOException {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("信息表");
List<User> userList = userService.queryAll(new User());
String fileName = "userinf" + ".xls";//设置要导出的文件的名字
//新增数据行，并且设置单元格数据
int rowNum = 1;
String[] headers = { "id", "name", "sex", "create_time"};
//headers表示excel表中第一行的表头
HSSFRow row = sheet.createRow(0);
//在excel表中添加表头
for(int i=0;i<headers.length;i++){
HSSFCell cell = row.createCell(i);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
//在表中存放查询到的数据放入对应的列
for (User user : userList) {
HSSFRow row1 = sheet.createRow(rowNum);
row1.createCell(0).setCellValue(user.getId());
row1.createCell(1).setCellValue(user.getName());
row1.createCell(2).setCellValue(user.getSex());
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String dateString = formatter.format(user.getCreateTime());
row1.createCell(3).setCellValue(dateString);
System.out.println(dateString);
rowNum++;
}
response.setContentType("application/octet-stream");
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
response.flushBuffer();
workbook.write(response.getOutputStream());
}
下载按钮已经在之前的html页面中添加过了，重启服务，进入localhost:8088点击数据导出
打开之后文件内容如下(因为我之前测试反复上传过很多次，所以都是重复的数据)
二、第二种使用EasyExcel方法（推荐）
阿里巴巴的easyExcel官方文档网站
上一篇写了一个SpringBoot整合poi实现excel导入导出的，但是使用poi会有一些问题，这时阿里带着EasyExcel来了。
Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存，poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题，但POI还是有一些缺陷，比如07版Excel解压缩以及解压后存储都是在内存中完成的，内存消耗依然很大。easyexcel重写了poi对07版Excel的解析，能够原本一个3M的excel用POI sax依然需要100M左右内存降低到几M，并且再大的excel不会出现内存溢出，03版依赖POI的sax模式。在上层做了模型转换的封装，让使用者更加简单方便。
这个整合EasyExcel的demo是在上一篇整合poi的基础上改进的，这里给个传送门SpringBoot实现Excel文件上传至数据库及下载数据库数据为Excel
源码已上传至码云
下面贴出修改的地方
先改一下依赖pom.xml
<!-- easyexcel相关依赖 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>1.1.2-beta5</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
测试的页面index.html修改如下
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>上传excl</title>
<script src="https://apps.bdimg.com/libs/jquery/2.1.4/jquery.min.js">
</script>
</head>
<body>
<div class="panel panel-primary">
<!-- .panel-heading 面板头信息。 -->
<div class="panel-heading">
<!-- .panel-title 面板标题。 -->
<h1 class="panel-title">上传excl并插入到数据库</h1>
</div>
</div>
<form class="form-horizontal" action="/user/uploadExcl" enctype="multipart/form-data" method="post">
<div class="form-group">
<div class="input-group col-md-4">
<span class="input-group-addon">
<i class="glyphicon glyphicon-search"></i>
</span>
<input class="form-control" placeholder="请选择文件" type="file" name="file"/>
</div>
</div>
<div class="form-group">
<div class="col-md-4">
<div class="btn-group btn-group-justified" >
<div class="btn-group" >
<button type="submit" class="btn btn-success" id="submitbtn">
<span class="glyphicon glyphicon-share"></span> 文件上传poi</button>
</div>
</div>
</div>
</div>
</form>
<form class="form-horizontal" action="/user/uploadEasyExcl" enctype="multipart/form-data" method="post">
<div class="form-group">
<div class="input-group col-md-4">
<span class="input-group-addon">
<i class="glyphicon glyphicon-search"></i>
</span>
<input class="form-control" placeholder="请选择文件" type="file" name="file"/>
</div>
</div>
<div class="form-group">
<div class="col-md-4">
<div class="btn-group btn-group-justified" >
<div class="btn-group" >
<button type="submit" class="btn btn-success" id="submitbtn">
<span class="glyphicon glyphicon-share"></span> 文件上传EasyExcel</button>
</div>
</div>
</div>
</div>
</form>
<table border="0" style="margin-top:4px; margin-left: 18px">
<tr>
<td><a href="#" class="easyui-linkbutton" onclick="downloadfile1();">数据导出poi</a></td>
</tr>
<tr>
<td><a href="#" class="easyui-linkbutton" onclick="downloadfile2();">数据导出easyexcel</a></td>
</tr>
</table>
<script>
function downloadfile1(){
window.location.href="/user/UserExcelDownloads";
}
function downloadfile2(){
window.location.href="/user/UserExcelDownloadsEasyExcel";
}
</script>
</body>
</html>
导出
user实体类
导出 Excel 时，若需要表头，那么相应的实体类需要继承 BaseRowModel，并加入 @ExcelProperty(value = “id”, index = 0) 注解。其中 value 代表在导出 Excel 时，该字段对应的表头名称；index 代表该字段对应的表头位置（从0开始）
@ExcelProperty 这个注解用于指定该属性对应excel文件中的哪一列数据。里面有两个属性，一个是value,另一个是index（从0开始），
这里不建议 index 和 name 同时用，要么一个对象只用index，要么一个对象只用name去匹配。
package com.thz.excl_upload.entity;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import java.io.Serializable;
import java.util.Date;
/**
* (User)实体类
*
* @author makejava
* @since 2020-07-22 09:57:53
*/
public class User extends BaseRowModel implements Serializable {
private static final long serialVersionUID = -75075031034829113L;
@ExcelProperty(value = {"ID"}, index = 0)
private Integer id;
@ExcelProperty(value = {"neme"}, index = 1)
private String name;
@ExcelProperty(value = {"性别"}, index = 2)
private String sex;
@ExcelProperty(value = {"创建时间"}, index = 3)
private Date createTime;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
}
UserController中添加下面的方法
@RequestMapping("UserExcelDownloadsEasyExcel")
public void UserExcelDownloadsEasyExcel(HttpServletResponse response) throws IOException {
ExcelWriter writer = EasyExcelFactory.getWriter(response.getOutputStream());
// 写仅有一个 Sheet 的 Excel 文件, 此场景较为通用
Sheet sheet1 = new Sheet(1, 0, User.class);
// 第一个 sheet 名称
sheet1.setSheetName("第一个sheet");
//设置自适应宽度
sheet1.setAutoWidth(Boolean.TRUE);
// 写数据到 Writer 上下文中
// 入参1: 数据库查询的数据list集合
// 入参2: 要写入的目标 sheet
writer.write(userService.queryAll(new User()), sheet1);
// 将上下文中的最终 outputStream 写入到指定文件中
response.setContentType("application/octet-stream");
String fileName = "userinf" + ".xls";
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
response.flushBuffer();
writer.finish();
}
导入
因为EasyExcel是依据实体类中的@ExcelProperty注解来解析Excel表中每一列的数据的，如果说上传表中数据和数据库中字段是对应的 ，那我们可以继续使用上面的实体类。但是通常业务传过来的表可能是只有一些关键字段，这时候我们还想进行上传需要修改原先User实体的注解与上传表一致，可这样导出的数据就不全了。为了解决这个问题需要再创建一个专门的上传UserUpLoad实体类。
我的表是上面这样的，所以新创建了如下UserUpLoad实体
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import java.io.Serializable;
import java.util.Date;
/**
* (User)实体类
*
* @author makejava
* @since 2020-07-22 09:57:53
*/
public class UserUpLoad extends BaseRowModel implements Serializable {
private static final long serialVersionUID = -75075031034829113L;
private Integer id;
@ExcelProperty(value = {"neme"}, index = 0)
private String name;
@ExcelProperty(value = {"性别"}, index = 1)
private String sex;
private Date createTime;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
}
UserUpLoadDao
/**
* (UserUpLoad)表数据库访问层
*
* @author makejava
* @since 2020-07-27 09:57:53
*/
@Mapper
@Component
public interface UserUpLoadDao {
/**
* 新增数据
*
* @param user 实例对象
* @return 影响行数
*/
int insert(UserUpLoad user);
}
UserUpLoadDao.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.thz.excl_upload.dao.UserUpLoadDao">
<resultMap type="com.thz.excl_upload.entity.UserUpLoad" id="UserMap">
<result property="id" column="id" jdbcType="INTEGER"/>
<result property="name" column="name" jdbcType="VARCHAR"/>
<result property="sex" column="sex" jdbcType="VARCHAR"/>
<result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
</resultMap>
<!--新增所有列-->
<insert id="insert" keyProperty="id" useGeneratedKeys="true">
insert into excl_test.user(name, sex, create_time)
values (#{name}, #{sex}, #{createTime})
</insert>
</mapper>
实体类的准备工作做完了，下面修改一下Controller
@RequestMapping(value = "/uploadEasyExcl")
public @ResponseBody
Map<String ,Object> uploadEasyExcl(HttpServletRequest request, @RequestParam("file") MultipartFile file) throws IOException {
Map<String ,Object> result = new HashMap<>();
userService.saveUser(file);
return result;
}
UserServiceImpl
@Override
public void saveUser(MultipartFile file) throws IOException {
if(!file.getOriginalFilename().equals("上传测试.xls") && !file.getOriginalFilename().equals("上传测试.xlsx") ){
return;
}
InputStream inputStream = new BufferedInputStream(file.getInputStream());
//实例化实现了AnalysisEventListener接口的类
ExcelListener excelListener = new ExcelListener(userUpLoadDao);
ExcelReader reader = new ExcelReader(inputStream,null,excelListener);
//读取信息
reader.read(new Sheet(1,1,UserUpLoad.class));
}
参考easyExcel官方GitHub demo
ExcelListener
package com.thz.excl_upload.listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.thz.excl_upload.dao.UserUpLoadDao;
import com.thz.excl_upload.entity.UserUpLoad;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class ExcelListener extends AnalysisEventListener<UserUpLoad> {
private List<UserUpLoad> datas = new ArrayList<>();
private static final int BATCH_COUNT = 3000;
private UserUpLoadDao userUpLoadDao;
public ExcelListener(UserUpLoadDao userUpLoadDao){
this.userUpLoadDao = userUpLoadDao;
}
@Override
public void invoke(UserUpLoad user, AnalysisContext analysisContext) {
//数据存储到datas，供批量处理，或后续自己业务逻辑处理。
datas.add(user);
//达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
if(datas.size() >= BATCH_COUNT){
saveData();
// 存储完成清理datas
datas.clear();
}
}
private void saveData() {
for(UserUpLoad user : datas){
user.setCreateTime(new Date());
this.userUpLoadDao.insert(user);
}
}
public List<UserUpLoad> getDatas() {
return datas;
}
public void setDatas(List<UserUpLoad> datas) {
this.datas = datas;
}
/**
* 所有数据解析完成了 都会来调用
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
saveData();//确保所有数据都能入库
}
}
三、注意mybatisPlus 集成 easyExcel时遇到的坑
没有cell_style_map字段
用mybatisPlus自带的方法找不到cell_style_map字段,我特么惊了,这是个啥字段啊,去数据库也没发现这个字段,用自己写的方法就不报错,经过一系列骚操作,发现了BaseRowModel类中有一个属性private Map<Integer,CellStyle> cellStyleMap = new HashMap<Integer,CellStyle>();,就是他导致的报错.
解决方案
1、于是乎去百度解决方案,查看mybatis-plus,在数据库添加字段cell_style_map,这是什么神仙操作?(不推荐)
2、在自己的实体类中加入该属性(推荐)
@TableField(exist = false)
private Map<Integer, CellStyle> cellStyleMap = new HashMap<Integer,CellStyle>();
导入Excel获取不到数据
导入数据时AnalysisEventListener类中invoke方法中获取不到数据
解决方案
实体类中@ExcelProperty(index = 0,value = “”),查看index属性是否加上了,别问我怎么知道的,全特么是泪!
604
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
