您的位置：首页 > 数码常识数码常识
easyExcel导入（easyExcel导出）
2025-05-10人已围观
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@ContentRowHeight(25)@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;
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;}
@Overridepublic void invoke(T object, AnalysisContext context) {datas.add(object);//当前行// context.getCurrentRowNum()}
@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;
@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;
@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());}}
public static List<Object> readLessThan1000Row(String filePath) {return readLessThan1000RowBySheet(filePath, null);}
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;}
public static List<Object> readMoreThan1000Row(String filePath) {return readMoreThan1000RowBySheet(filePath, null);}
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;}
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);return excelListener.getDatas();}
public static void writeBySimple(String filePath, List<List<Object>> data, List<String> head) {writeSimpleBySheet(filePath, data, head, null);}
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);}}}
public static void writeWithTemplate(String filePath, List<? extends BaseRowModel> data) {writeWithTemplateAndSheet(filePath, data, null);}
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);}}
}
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%的朋友还想知道的：
152606
我们实现导入/导出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@ContentRowHeight(25)@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;
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;}
@Overridepublic void invoke(T object, AnalysisContext context) {datas.add(object);//当前行// context.getCurrentRowNum()}
@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;
@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;
@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());}}
public static List<Object> readLessThan1000Row(String filePath) {return readLessThan1000RowBySheet(filePath, null);}
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;}
public static List<Object> readMoreThan1000Row(String filePath) {return readMoreThan1000RowBySheet(filePath, null);}
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;}
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);return excelListener.getDatas();}
public static void writeBySimple(String filePath, List<List<Object>> data, List<String> head) {writeSimpleBySheet(filePath, data, head, null);}
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);}}}
public static void writeWithTemplate(String filePath, List<? extends BaseRowModel> data) {writeWithTemplateAndSheet(filePath, data, null);}
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);}}
}
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%的朋友还想知道的：
152606
很赞哦！ ()
下一篇：返回列表