easy excel 按标题导入：使用EasyExcel导入导出excel

我们实现导入/导出excel的第三方常用类库有 Apache poi、Java Excel（JXL）和阿里开源的 Easyexcel 等。我比较倾向使用Easyexcel，原因有两点：

1、性能强。有大量的数据去处理时，poi和jxl内存消耗比较大,可能造成内存溢出。

2、上手简单。poi是比较容易理解的，但是操作起来麻烦，比如我上一篇的“poi导入导出完整实现 包含工具类”，为了简单实现，代码写了好多。而easyexcel可以自己处理数据，样式也比较好调整。

如果你想更详细的学习easyexcel建议看官方文档

easyexcel导出

1、加入依赖

<dependency>

<groupId>com.alibaba</groupId>

<artifactId>easyexcel</artifactId>

<version>2.0.4</version>

<scope>compile</scope>

</dependency>

2、编写控制层

@GetMapping("/export")

@ResponseBody

public boolean export(HttpServletResponse response, HttpServletRequest request) {

boolean exportResult = testService.findInfo(response, request);

return exportResult;

}

3、编写实现层和导出实体类

boolean findInfo(HttpServletResponse response, HttpServletRequest request);

@Override

public boolean findInfo(HttpServletResponse response, HttpServletRequest request) {

try {

List<Map<String, String>> dataList = new ArrayList<>();

Map<String, String> map = new HashMap<>();

map.put("id", "1");

map.put("name", "测试");

map.put("phone", "测试");

map.put("address", "测试");

map.put("enrolDate", "2021-12-11");

map.put("des", "测试");

Map<String, String> map1 = new HashMap<>();

map1.put("id", "2");

map1.put("name", "测试1");

map1.put("phone", "测试1");

map1.put("address", "测试1");

map1.put("enrolDate", "2021-12-12");

map1.put("des", "测试1");

dataList.add(map);

dataList.add(map1);

List<TestBo> boList = dataList.stream().filter(Objects::nonNull)

.map(s -> TestBo.builder()

.id(Integer.valueOf(s.get("id")))

.name(s.get("name"))

.phone(s.get("phone"))

.address(s.get("address"))

.enrolDate(s.get("enrolDate"))

.des(s.get("des"))

.build()).collect(Collectors.toList());

//导出文件名称

String fileName = URLEncoder.encode("测试用户导出数据", "utf-8");

response.setHeader("Content-disposition", "attachment;filename=" fileName ".xlsx");

EasyExcel.write(response.getOutputStream(), TestBo.class).sheet("用户导出数据").doWrite(boList);

return true;

} catch (IOException e) {

e.printStackTrace();

return false;

}

}

@Data

@NoArgsConstructor

@AllArgsConstructor

@Builder

/**设置 row 高度，不包含表头*/

@ContentRowHeight(25)

/**设置 表头 高度（与 @ContentRowHeight 相反）*/

@HeadRowHeight(25)

/**设置列宽*/

@ColumnWidth(25)

@Accessors(chain = true)

public class TestBo {

/**设置表头信息*/

@ExcelProperty("id")

private Integer id;

@ExcelProperty("姓名")

private String name;

@ExcelProperty("电话")

private String phone;

@ExcelProperty("地址")

private String address;

@ExcelProperty("时间")

private String enrolDate;

@ExcelProperty("备注")

private String des;

}

4、完成测试

easyexcel导入

1、编写控制层

@PostMapping(value = "/importExcel")

@ResponseBody

public boolean importExcel(@RequestParam("file") MultipartFile file) {

return testService.importData(file);

}

2、编写实现层和实体类

boolean importData(MultipartFile file);

@Override

@Transactional(rollbackFor = Exception.class)

public boolean importData(MultipartFile file) {

try {

List<Object> objectList =ExcelUtil.readMoreThan1000RowBySheetFromInputStream(file.getInputStream(),null,TestPo.class);

List<TestPo> poList=new ArrayList<>();

for (Object object:objectList) {

System.out.println(object);

TestPo po=new TestPo();

List<String> srtList= (List<String>) object;

po.setId(srtList.get(0)!=null?srtList.get(0).toString():"");

po.setName(srtList.get(1)!=null?srtList.get(1).toString():"");

po.setPhone(srtList.get(2)!=null?srtList.get(2).toString():"");

po.setAddress(srtList.get(3)!=null?srtList.get(3).toString():"");

po.setEnrolDate(srtList.get(4)!=null?srtList.get(4).toString():"");

po.setDes(srtList.get(5)!=null?srtList.get(5).toString():"");

poList.add(po);

}

return testDao.saveBatch(poList);

} catch (Exception e) {

e.printStackTrace();

return false;

}

}

//实体类

@Data

public class TestPo {

private String id;

private String name;

private String phone;

private String address;

private String enrolDate;

private String des;

}

实际开发业务不同入库方法我就不提供了，提供导入工具类

package com.example.mydemo1.util;

import com.alibaba.excel.context.AnalysisContext;

import com.alibaba.excel.event.AnalysisEventListener;

import java.util.ArrayList;

import java.util.List;

/**

* 解析监听器，

* 每解析一行会回调invoke()方法。

* 整个excel解析结束会执行doAfterAllAnalysed()方法

* @className ExcelListener

* @description easyexcel

* @date 2021/3/26 11:14

*/

public class ExcelListener<T> extends AnalysisEventListener<T> {

private List<T> datas = new ArrayList<T>();

public List<T> getDatas() {

return datas;

}

public void setDatas(List<T> datas) {

this.datas = datas;

}

/**

* 逐行解析

* object : 当前行的数据

*/

@Override

public void invoke(T object, AnalysisContext context) {

datas.add(object);

//当前行

// context.getCurrentRowNum()

/*if (object != null) {

datas.add((T) object);

}*/

}

/**

* 解析完所有数据后会调用该方法

*/

@Override

public void doAfterAllAnalysed(AnalysisContext context) {

//解析结束销毁不用的资源

}

}

//=============================================

package com.example.mydemo1.util;

import com.alibaba.excel.metadata.BaseRowModel;

import com.alibaba.excel.metadata.Sheet;

import lombok.Data;

import java.util.List;

/**

* @className MultipleSheelPropety

* @description easyexcel

* @date 2021/3/26 11:15

*/

@Data

public class MultipleSheelPropety {

private List<? extends BaseRowModel> data;

private Sheet sheet;

}

//=============================================

package com.example.mydemo1.util;

import com.alibaba.excel.EasyExcelFactory;

import com.alibaba.excel.ExcelWriter;

import com.alibaba.excel.metadata.BaseRowModel;

import com.alibaba.excel.metadata.Sheet;

import lombok.extern.slf4j.Slf4j;

import org.apache.commons.collections.CollectionUtils;

import org.apache.poi.ss.usermodel.Workbook;

import org.springframework.util.StringUtils;

import javax.servlet.http.HttpServletResponse;

import java.io.*;

import java.net.URLEncoder;

import java.util.ArrayList;

import java.util.Collections;

import java.util.List;

/**

* @className ExcelUtil

* @description easyexcel工具类

* @date 2021/3/26 11:10

*/

@Slf4j

public class ExcelUtil {

private static Sheet initSheet;

static {

initSheet = new Sheet(1, 0);

initSheet.setSheetName("sheet");

//设置自适应宽度

initSheet.setAutoWidth(Boolean.TRUE);

}

public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {

try {

response.setCharacterEncoding("UTF-8");

response.setContentType("application/octet-stream;charset=utf-8");

response.setHeader("Content-Disposition", "attachment;filename=" URLEncoder.encode(fileName, "UTF-8"));

workbook.write(response.getOutputStream());

} catch (IOException e) {

// throw new NormalException(e.getMessage());

}

}

/**

* 读取少于1000行数据

*

* @param filePath 文件绝对路径

* @return

*/

public static List<Object> readLessThan1000Row(String filePath) {

return readLessThan1000RowBySheet(filePath, null);

}

/**

* 读小于1000行数据, 带样式

* filePath 文件绝对路径

* initSheet ：

* sheetNo: sheet页码，默认为1

* headLineMun: 从第几行开始读取数据，默认为0, 表示从第一行开始读取

* clazz: 返回数据List<Object> 中Object的类名

*/

public static List<Object> readLessThan1000RowBySheet(String filePath, Sheet sheet) {

if (!StringUtils.hasText(filePath)) {

return null;

}

sheet = sheet != null ? sheet : initSheet;

InputStream fileStream = null;

try {

fileStream = new FileInputStream(filePath);

return EasyExcelFactory.read(fileStream, sheet);

} catch (FileNotFoundException e) {

log.info("找不到文件或文件路径错误, 文件：{}", filePath);

} finally {

try {

if (fileStream != null) {

fileStream.close();

}

} catch (IOException e) {

log.info("excel文件读取失败, 失败原因：{}", e);

}

}

return null;

}

/**

* 读大于1000行数据

*

* @param filePath 文件觉得路径

* @return

*/

public static List<Object> readMoreThan1000Row(String filePath) {

return readMoreThan1000RowBySheet(filePath, null);

}

/**

* 读大于1000行数据, 带样式

*

* @param filePath 文件觉得路径

* @return

*/

public static List<Object> readMoreThan1000RowBySheet(String filePath, Sheet sheet) {

if (!StringUtils.hasText(filePath)) {

return null;

}

sheet = sheet != null ? sheet : initSheet;

InputStream fileStream = null;

try {

fileStream = new FileInputStream(filePath);

ExcelListener excelListener = new ExcelListener();

EasyExcelFactory.readBySax(fileStream, sheet, excelListener);

return excelListener.getDatas();

} catch (FileNotFoundException e) {

log.error("找不到文件或文件路径错误, 文件：{}", filePath);

} finally {

try {

if (fileStream != null) {

fileStream.close();

}

} catch (IOException e) {

log.error("excel文件读取失败, 失败原因：{}", e);

}

}

return null;

}

/**

* 读大于1000行数据, 带样式

*

* @return

*/

public static List readMoreThan1000RowBySheetFromInputStream(InputStream inputStream, Sheet sheet,Class clazz) {

sheet = sheet != null ? sheet : initSheet;

InputStream fileStream = null;

ExcelListener excelListener = new ExcelListener();

EasyExcelFactory.readBySax(inputStream, sheet, excelListener);

/* ExcelReaderBuilder excelReaderBuilder=EasyExcelFactory.read(inputStream,clazz,excelListener);*/

return excelListener.getDatas();

}

/**

* 生成excle

*

* @param filePath 绝对路径

* @param data 数据源

* @param head 表头

*/

public static void writeBySimple(String filePath, List<List<Object>> data, List<String> head) {

writeSimpleBySheet(filePath, data, head, null);

}

/**

* 生成excle

*

* @param filePath 路径

* @param data 数据源

* @param sheet excle页面样式

* @param head 表头

*/

public static void writeSimpleBySheet(String filePath, List<List<Object>> data, List<String> head, Sheet sheet) {

sheet = (sheet != null) ? sheet : initSheet;

if (head != null) {

List<List<String>> list = new ArrayList<>();

head.forEach(h -> list.add(Collections.singletonList(h)));

sheet.setHead(list);

}

OutputStream outputStream = null;

ExcelWriter writer = null;

try {

outputStream = new FileOutputStream(filePath);

writer = EasyExcelFactory.getWriter(outputStream);

writer.write1(data, sheet);

} catch (FileNotFoundException e) {

log.error("找不到文件或文件路径错误, 文件：{}", filePath);

} finally {

try {

if (writer != null) {

writer.finish();

}

if (outputStream != null) {

outputStream.close();

}

} catch (IOException e) {

log.error("excel文件导出失败, 失败原因：{}", e);

}

}

}

/**

* 生成excle

*

* @param filePath 路径

* @param data 数据源

*/

public static void writeWithTemplate(String filePath, List<? extends BaseRowModel> data) {

writeWithTemplateAndSheet(filePath, data, null);

}

/**

* 生成excle

*

* @param filePath 路径

* @param data 数据源

* @param sheet excle页面样式

*/

public static void writeWithTemplateAndSheet(String filePath, List<? extends BaseRowModel> data, Sheet sheet) {

if (CollectionUtils.isEmpty(data)) {

return;

}

sheet = (sheet != null) ? sheet : initSheet;

sheet.setClazz(data.get(0).getClass());

OutputStream outputStream = null;

ExcelWriter writer = null;

try {

outputStream = new FileOutputStream(filePath);

writer = EasyExcelFactory.getWriter(outputStream);

writer.write(data, sheet);

} catch (FileNotFoundException e) {

log.error("找不到文件或文件路径错误, 文件：{}", filePath);

} finally {

try {

if (writer != null) {

writer.finish();

}

if (outputStream != null) {

outputStream.close();

}

} catch (IOException e) {

log.error("excel文件导出失败, 失败原因：{}", e);

}

}

}

/**

* 生成多Sheet的excle

*

* @param filePath 路径

* @param multipleSheelPropetys

*/

public static void writeWithMultipleSheel(String filePath, List<MultipleSheelPropety> multipleSheelPropetys) {

if (CollectionUtils.isEmpty(multipleSheelPropetys)) {

return;

}

OutputStream outputStream = null;

ExcelWriter writer = null;

try {

outputStream = new FileOutputStream(filePath);

writer = EasyExcelFactory.getWriter(outputStream);

for (MultipleSheelPropety multipleSheelPropety : multipleSheelPropetys) {

Sheet sheet = multipleSheelPropety.getSheet() != null ? multipleSheelPropety.getSheet() : initSheet;

if (!CollectionUtils.isEmpty(multipleSheelPropety.getData())) {

sheet.setClazz(multipleSheelPropety.getData().get(0).getClass());

}

writer.write(multipleSheelPropety.getData(), sheet);

}

} catch (FileNotFoundException e) {

log.error("找不到文件或文件路径错误, 文件：{}", filePath);

} finally {

try {

if (writer != null) {

writer.finish();

}

if (outputStream != null) {

outputStream.close();

}

} catch (IOException e) {

log.error("excel文件导出失败, 失败原因：{}", e);

}

}

}

}

3、准备导入文件

本文中没有对导入文档做错误校验，建议在实际开发中为用户提供导入模板，可以减少错误的出现。

4、使用postman测试

————————————————

版权声明：本文为CSDN博主「窈岆瀮珊」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。

原文链接：

https://blog.csdn.net/weixin_53233637/article/details/114576212