开发中经常会设计到excel的处理,如导出Excel,导入Excel到数据库中
一.EasyExcel介绍
1、EasyExcel的作用
- 数据导入:减轻录入工作量
- 数据导出:统计信息归档
- 数据传输:异构系统之间数据传输
2、EasyExcel的特点
-
快速:快速的读取excel中的数据。
-
简洁:映射excel和实体类,让代码变的更加简洁。
-
大文件:在读写大文件的时候使用磁盘做缓存,更加的节约内存。
参考文档地址:https://easyexcel.opensource.alibaba.com/index.html
二.基本使用:基于springboot
1.在pom文件中添加EasyExcel依赖
<!-- easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.0</version>
</dependency>
<!-- spring-boot-junit -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.24</version>
</dependency>
2.写数据:
实体类(对应表头):
@AllArgsConstructor
@NoArgsConstructor
@Data
public class DemoData {
//指定表头
@ExcelProperty(value = "字符串标题")
private String string;
@ExcelProperty(value = "日期标题")
private Date date;
@ExcelProperty(value = "数字标题")
private Double doubleData;
@ExcelIgnore //此注解表示忽略此字段
private String ignore;
}
测试类:
public class ExcelWriteTest {
@Test //写入示例
public void simpleWrite() {
//获取当前系统时间,避免多次运行文件名重复,数据被覆盖
long time = System.currentTimeMillis();
//格式化时间
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd_HH-mm-ss");
String format = simpleDateFormat.format(time);
//指定将文件写到哪里,即文件位置
String fileName = "D:\\test\\test.xlsx" + format + ".xlsx";
//excelType指定工作表类型 sheet页签名称
EasyExcel.write(fileName, DemoData.class).excelType(ExcelTypeEnum.XLS).sheet("模板").doWrite(data()); //
System.out.println();
}
// 写入文件模型(数据源)
private List<DemoData> data() {
List<DemoData> list = ListUtils.newArrayList();
//算上标题,最多可写65536行(.xls)
//算上标题,最多可写1048576行(.xlsx)
//java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535)
for (int i = 0; i < 10; i++) {
DemoData data = new DemoData();
data.setString("字符串" + i);
data.setDate(new Date());
data.setDoubleData(0.56);
list.add(data);
}
return list;
}
}
查看 D:\\test下的刚生成的文件
自此:写入文件操作完成
读取文件:
在测试目录下创建如下文件:
// 有个很重要的点 DemoDataListener 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去
@Slf4j
public class DemoDataListener implements ReadListener<DemoData> {
/**
* 每隔3条存储数据库,实际使用中可以100条,然后清理list ,方便内存回收
* 默认一次读取3条,可根据实际业务和服务器调整
*/
private static final int BATCH_COUNT = 3;
/**
* 缓存的数据
* 不确定大小,可以自动扩容
* newArrayListWithExpectedSize(),会直接创建一个指定size的容器,但它会通过一条公式计算来进行扩容 (
*
* 5L + (long)arraySize + (long)(arraySize / 10)),
*
* 例如,创建一个10个size的容器,那么 5+10 + (10/10) = 16,当容器添加第17个数据时,这个容器才会进行扩容,
*
* 优点:节约内存,节约时间,节约性能,
*/
private List<DemoData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
/**
* 每解析一行,回调该方法
*/
@Override
public void invoke(DemoData data, AnalysisContext context) {
log.info("解析到一条数据:{}", 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());
// demoDAO.save(cachedDataList);
log.info("存储数据库成功!");
}
}
测试类:
@SpringBootTest
@RunWith(SpringJUnit4ClassRunner.class)
public class ExcelReadTest {
/**
* <p>
* 1. 创建excel对应的实体对象 参照{@link DemoData}
* <p>
* 2. 由于默认一行行的读取excel,所以需要创建excel一行一行的回调监听器
* <p>
* 3. 直接读即可
*/
@Test
public void simpleRead() {
// 有个很重要的点 DemoDataListener 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去
// 写法3:
String fileName = "D:\\test\\test.xlsx2023-07-10_19-39-36.xlsx.xlsx";
// 这里 需要指定读用哪个class去读,然后读取第一个sheet 文件流会自动关闭
EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
}
}
实战(前端页面数据导入导出功能):
实体类还是如上DemoData
controller层:
@Autowired
private DemoDataService demoDataService;
@GetMapping(value = "/exportData")
public void downloadFailedUsingJson(HttpServletResponse response) throws IOException {
// 这里假设数据库中的字段和我DemoData的属性一致
try {
//查询所有数据
List<DemoData> demoData = demoDataService.findDemoDataExcelVoList();
//设置content—type 响应类型 表示后面的文档属于什么MIME类型
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
//设置响应编码格式,避免乱码
response.setCharacterEncoding("utf-8");
// 在发送请求前,先把中文字符使用URLEncoder.encode()方法转码,中文乱码这个问题轻松解决。
//使用URLEncoder.encode()方法解决Tomcat发送HTTP请求中文参数乱码的问题
String fileName = URLEncoder.encode("数据字典", "UTF-8").replaceAll("\\+", "%20");
//Content-disposition是MIME协议的扩展,MIME协议指示MIME用户代理如何显示附加的文件
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
// 这里需要设置不关闭流
EasyExcel.write(response.getOutputStream(), RegionExcelVo.class)
.sheet("数据模型")
.doWrite(demoData);
} catch (Exception e) {
log.error(ExceptionUtils.getStackTrace(e));
// 重置response
response.reset();
response.setContentType("text/html");
response.setCharacterEncoding("utf-8");
response.getWriter().println("导出失败");
}
}
Service层
@Override
public List<RegionExcelVo> findDemoDataExcelVoList() {
// 查询所有
List<DemoData> demoDataList = this.list();
/**
* newArrayListWithExpectedSize 和 newArrayListWithCapacity区别
* 确定你的list装多少个元素,不会改变,使用newArrayListWithCapacity()如果数据容器定义的size,将自动扩容,有性能影响
* 不确定你的list装多少个元素,但差别不会太大,使用newArrayListWithExpectedSize()
*/
// 创建一个集合,集合初始容量等于查出来所有数据的长度
List<RegionExcelVo> dataExcelVoList = ListUtils.newArrayListWithCapacity(regionList.size());
// 遍历集合
for (Region data : demoDataList) {
// 创建一个地区集合查询结果对象
DemoData demoData = new DemoData();
demoData.setString(data.getString());
demoData.setDate(data.getData());
demoData.setDoubleData(data.getDoubleData);
dataExcelVoList.add(demoData);
}
return dataExcelVoList;
}
导入文件(将数据从excel读入,并且写入到数据库):
controller:
@ApiOperation(value = "导入")
@ApiImplicitParam(name = "file", value = "文件", required = true)
@PostMapping("/importData")
public Result importData(MultipartFile file) {
regionService.importData(file);
return Result.ok();
}
service:
需要定义一个文件监听器
@Slf4j
public class RegionExcelListener implements ReadListener<DemoData> {
/**
* 每隔3条存储数据库,实际使用中可以100条,然后清理list ,方便内存回收
*/
private static final int BATCH_COUNT = 100;
/**
* 缓存的数据
*/
private List<DemoData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
/**
* 假设这个是一个DAO,当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
*/
private DemodataMapper demodataMapper;
/**
* 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
*
* @param regionMapper
*/
public RegionExcelListener(DemodataMapper demodataMapper) {
this.demodataMapper = demodataMapper;
}
/**
* 这个每一条数据解析都会来调用
*/
@Override
public void invoke(DemoData data, AnalysisContext context) {
log.info("解析到一条数据:{}", 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());
demodataMapper.saveBatch(cachedDataList);
log.info("存储数据库成功!");
}
}
调用读取文件
@Override
public void importData(MultipartFile multipartFile) {
try {
// 记录当前时间
long begin = System.currentTimeMillis();
// 第一个参数:获取多媒体文件输入流,第二个参数:数据模型 第三个参数:监听器
EasyExcel.read(multipartFile.getInputStream(), DemodataExcelVo.class, new DemoDataListener(baseMapper)).sheet(). doRead();
// 存储完成后再次记录时间
long end = System.currentTimeMillis();
log.info("用时:"+(end-begin)+"ms");
} catch (IOException e) {
throw new RuntimeException(e);
}
}