目录:
1.easyexcel
2.easyexcel的写操作
3.easyexcel写操作--web模式
4.easyexcel完成读操作
5.easyexcel读操作--web模式
1.easyexcel
EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。 在尽可能节约内存的情况下支持读写百M的Excel。github地址:https://github.com-alibaba/easyexcel
我们现在用这个网站写excel(文档已经迁移) · 语雀EasyExcel写Excel的示例https://www.yuque.com/easyexcel/doc/write通过java代码完成对Excel的读写操作。 所谓的读写理解为上传和下载
2.easyexcel写操作
所谓的写操作就是把java中的类对象写入到excel表格中
实现步骤:
1.引入相关依赖 2.配置相应的实体类 3.通过excel进行写入操作
1.引入相关依赖
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.3.12.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.hql</groupId>
<artifactId>spring-easyexcel</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>spring-easyexcel</name>
<description>spring-easyexcel</description>
<properties>
<java.version>8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.2</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.62</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
</build>
</project>
2.配置相应的实体类
package com.hql.excel;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
* @program: spring-easyexcel
* @description:
* @author:
* @create: 2023-04-28 14:51
**/
@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName
public class ExcelDemo {
@ExcelProperty("ID")//标记excel标题内容
@TableId(type = IdType.AUTO)
private int id;
@ExcelProperty("姓名")
private String ename;
@ExcelProperty("年龄")
private int age;
// @ExcelIgnore 该属性不写入excel表格里
}
3.通过excel进行写入操作
这里的data,以后我们可以通过查询数据库
package com.hql.excel;
import com.alibaba.excel.EasyExcel;
import java.util.ArrayList;
import java.util.List;
/**
* @program: spring-easyexcel
* @description:
* @author:
* @create: 2023-04-28 14:54
**/
public class TestWrite {
public static void main(String[] args) {
String fileName = "D:\\maven\\apache-maven-3.8.6\\conf\\spring-easyexcel\\mingren.xls";
// 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
// 如果这里想使用03 则 传入excelType参数即可
List<ExcelDemo> data=new ArrayList<>();
data.add(new ExcelDemo(1,"李白",18));
data.add(new ExcelDemo(2,"杜甫",18));
data.add(new ExcelDemo(3,"辛弃疾",18));
data.add(new ExcelDemo(4,"苏轼",18));
EasyExcel.write(fileName, ExcelDemo.class).sheet("ok").doWrite(data);
}
}
运行后
3.easyexcel写操作web模式
package com.ykq.qy163easyexcel.controller;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.util.MapUtils;
import com.alibaba.fastjson.JSON;
import com.ykq.qy163easyexcel.dao.UserDao;
import com.ykq.qy163easyexcel.excel.ExcelDemo;
import com.ykq.qy163easyexcel.listener.UserDataListener;
import com.ykq.qy163easyexcel.pojo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* @program: qy163-easyexcel
* @description:
* @author:
* @create: 2023-04-28 15:06
**/
@Controller
public class UploadController {
@GetMapping("/upload")
public void downloadFailedUsingJson(HttpServletResponse response) throws IOException {
// 这里需要设置不关闭流
List<ExcelDemo> data=new ArrayList<>();
data.add(new ExcelDemo(1,"李白",18));
data.add(new ExcelDemo(2,"杜甫",18));
data.add(new ExcelDemo(3,"辛弃疾",18));
data.add(new ExcelDemo(4,"苏轼",18));
upload(response,"今天放假",data);
}
public void upload(HttpServletResponse response,String title,List<?> data) throws IOException{
// 这里注意 使用swagger 会导致各种问题,请直接用浏览器或者用postman
try {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode("qy163下载学员信息", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream(), ExcelDemo.class).autoCloseStream(Boolean.FALSE).sheet("模板")
.doWrite(data);
} catch (Exception e) {
// 重置response
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
Map<String, String> map = MapUtils.newHashMap();
map.put("status", "failure");
map.put("message", "下载文件失败" + e.getMessage());
response.getWriter().println(JSON.toJSONString(map));
}
}
}
4.easyexcel完成读操作
1.监听器
package com.hql.listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.fastjson.JSON;
import com.hql.dao.ExcelDao;
import com.hql.excel.ExcelDemo;
import lombok.extern.slf4j.Slf4j;
import java.util.List;
/**
* @program: spring-easyexcel
* @description:
* @author:
* @create: 2023-04-28 16:30
**/
@Slf4j
public class ExcelListener implements ReadListener<ExcelDemo> {
/**
* 每隔5条存储数据库,实际使用中可以100条,然后清理list ,方便内存回收
*/
private static final int BATCH_COUNT = 100;
/**
* 缓存的数据
*/
private List<ExcelDemo> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
/**
* 假设这个是一个DAO,当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
*/
private ExcelDao excelDao;
public ExcelListener() {
// 这里是demo,所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
// excelDao = new ExcelDao();
}
/**
* 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
*
* @param excelDao
*/
public ExcelListener(ExcelDao excelDao) {
this.excelDao = excelDao;
}
/**
* 这个每一条数据解析都会来调用
*
* @param data one row value. Is is same as {@link AnalysisContext#readRowHolder()}
* @param context
*/
@Override
public void invoke(ExcelDemo data, AnalysisContext context) {
log.info("解析到一条数据:{}", JSON.toJSONString(data));
cachedDataList.add(data);
// 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
if (cachedDataList.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
}
}
/**
* 所有数据解析完成了 都会来调用
*
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据,确保最后遗留的数据也存储到数据库
saveData();
log.info("所有数据解析完成!");
}
/**
* 加上存储数据库
*/
private void saveData() {
log.info("{}条数据,开始存储数据库!", cachedDataList.size());
excelDao.batchSave(cachedDataList);
log.info("存储数据库成功!");
}
}
2.dao层
package com.hql.dao;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.hql.excel.ExcelDemo;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
/**
* @program: spring-easyexcel
* @description:
* @author:
* @create: 2023-04-28 16:36
**/
@Mapper
public interface ExcelDao extends BaseMapper<ExcelDemo>{
void batchSave(List<ExcelDemo> cachedDataList);
}
3.测试层
package com.hql.excel;
import com.alibaba.excel.EasyExcel;
import com.hql.dao.ExcelDao;
import com.hql.listener.ExcelListener;
import org.springframework.beans.factory.annotation.Autowired;
import java.io.File;
/**
* @program: spring-easyexcel
* @description:
* @author:
* @create: 2023-04-28 16:18
**/
public class TestRead {
// @Autowired
// private ExcelDao excelDao;
public static void main(String[] args) {
String fileName = "D:\\maven\\apache-maven-3.8.6\\conf\\spring-easyexcel\\mingre.xls";
// 这里 需要指定读用哪个class去读,然后读取第一个sheet 文件流会自动关闭
EasyExcel.read(fileName, ExcelDemo.class, new ExcelListener()).sheet().doRead();
}
}
easyexce读操作web模式
package com.hql.controller;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.util.MapUtils;
import com.alibaba.fastjson.JSON;
import com.hql.dao.ExcelDao;
import com.hql.excel.ExcelDemo;
import com.hql.listener.ExcelListener;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* @program: spring-easyexcel
* @description:
* @author:
* @create: 2023-04-28 15:15
**/
@RestController
public class ExcelController {
/**
* 文件上传
*/
@Autowired
private ExcelDao excelDao;
@PostMapping("upload2")
public String upload2(MultipartFile file) throws IOException {
EasyExcel.read(file.getInputStream(), ExcelDemo.class, new ExcelListener(excelDao)).sheet().doRead();
return "success";
}
}
通过postman进行测试
查看数据库