林强源优秀作者
原创内容 来源:小居数码网 时间:2024-07-29 15:29:01 阅读() 收藏:46 分享:51 爆
导读:您正在阅读的是关于【数码知识】的问题,本文由科普作家协会,生活小能手,著名生活达人等整理监督编写。本文有4069个文字,大小约为13KB,预计阅读时间11分钟。
我们实现导入/导出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")@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%的朋友还想知道的:
(485)个朋友认为回复得到帮助。
部分文章信息来源于以及网友投稿,转载请说明出处。
本文标题:easyExcel导入(easyExcel导出):http://sjzlt.cn/shuma/152606.html