easyExcel导入（easyExcel导出）

我们实现导入/导出excel的第三方常用类库有 Apache poi、Java Excel（JXL）和阿里开源的 Easyexcel 等。我比较倾向使用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")@ResponseBodypublic boolean export(HttpServletResponse response, HttpServletRequest request) {boolean exportResult = testService.findInfo(response, request);return exportResult;}

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

boolean findInfo(HttpServletResponse response, HttpServletRequest request);

@Overridepublic 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")@ResponseBodypublic 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;}}

//实体类@Datapublic 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 : 当前行的数据*/@Overridepublic void invoke(T object, AnalysisContext context) {datas.add(object);//当前行// context.getCurrentRowNum()/*if (object != null) {datas.add((T) object);}*/}

/*** 解析完所有数据后会调用该方法*/@Overridepublic 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*/@Datapublic 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*/@Slf4jpublic 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测试

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

上面就是小居数码小编今天给大家介绍的关于（easyExcel导出）的全部内容，希望可以帮助到你，想了解更多关于数码知识的问题，欢迎关注我们，并收藏，转发，分享。

94%的朋友还想知道的：

（264）个朋友认为回复得到帮助。