前言

在我們?nèi)粘５拈_(kāi)發(fā)過(guò)程中經(jīng)常會(huì)使用Excel文件的形式來(lái)批量地上傳下載系統(tǒng)數(shù)據(jù)，我們最常用的工具是Apache poi,但是如果數(shù)據(jù)到底上百萬(wàn)時(shí)，將會(huì)造成內(nèi)存溢出的問(wèn)題，那么我們?cè)趺慈?shí)現(xiàn)百萬(wàn)數(shù)據(jù)批量導(dǎo)入導(dǎo)出。

正文

Easyexcel

Easyexcel 是阿里巴巴的開(kāi)源項(xiàng)目，用來(lái)優(yōu)化Excel文件處理過(guò)程：

poi消耗內(nèi)存嚴(yán)重：Java解析、生成Excel比較有名的框架有Apache poi、jxl。但他們都存在一個(gè)嚴(yán)重的問(wèn)題就是非常的耗內(nèi)存，poi有一套SAX模式的API可以一定程度的解決一些內(nèi)存溢出的問(wèn)題，但poi還是有一些缺陷，比如07版Excel解壓縮以及解壓后存儲(chǔ)都是在內(nèi)存中完成的，內(nèi)存消耗依然很大。 easyexcel針對(duì)內(nèi)存做出了優(yōu)化：重寫(xiě)了poi對(duì)07版Excel的解析，能夠原本一個(gè)3M的excel用POI sax依然需要100M左右內(nèi)存降低到幾M，并且再大的excel不會(huì)出現(xiàn)內(nèi)存溢出。

SpringBoot+ EasyExcel實(shí)現(xiàn)Excel文件的導(dǎo)入導(dǎo)出

導(dǎo)入依賴

<!--lombok--><dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.2</version> <optional>true</optional></dependency><!--easyExcel--><dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>1.1.2-beat1</version></dependency><!--fastjson--><dependency> <groupId>com.fasterxml.jackson.core</groupId> <artifactId>jackson-databind</artifactId> <exclusions> <exclusion> <groupId>com.fasterxml.jackson.core</groupId> <artifactId>jackson-annotations</artifactId> </exclusion> </exclusions></dependency><dependency> <groupId>com.fasterxml.jackson.core</groupId> <artifactId>jackson-annotations</artifactId></dependency>

為了防止Excel文件被破壞在pom.xml添加以下內(nèi)容

<build> <plugins> <!-- 讓maven不編譯xls文件，但仍將其打包 --> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-resources-plugin</artifactId> <configuration><nonFilteredFileExtensions> <nonFilteredFileExtension>xls</nonFilteredFileExtension> <nonFilteredFileExtension>xlsx</nonFilteredFileExtension></nonFilteredFileExtensions> </configuration> </plugin> </plugins></build>

application.propertis:配置文件

#temp filesproject.tmp.files.path=/Users/mac/Desktop/image/tmp/files/

在SpringBoot啟動(dòng)類添加臨時(shí)文件設(shè)置

@Value('${project.tmp.files.path}')public String filesPath;@BeanMultipartConfigElement multipartConfigElement() { MultipartConfigFactory factory = new MultipartConfigFactory(); //設(shè)置路徑xxx factory.setLocation(filesPath); return factory.createMultipartConfig();}

ExcelUtil:Excel工具類

@Slf4jpublic class ExcelUtil { private static Sheet initSheet; static { initSheet = new Sheet(1, 0); initSheet.setSheetName('sheet'); //設(shè)置自適應(yīng)寬度 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行數(shù)據(jù) * * @param filePath 文件絕對(duì)路徑 * @return */ public static List<Object> readLessThan1000Row(String filePath) { return readLessThan1000RowBySheet(filePath, null); } /** * 讀小于1000行數(shù)據(jù), 帶樣式 * filePath 文件絕對(duì)路徑 * initSheet ： * sheetNo: sheet頁(yè)碼，默認(rèn)為1 * headLineMun: 從第幾行開(kāi)始讀取數(shù)據(jù)，默認(rèn)為0, 表示從第一行開(kāi)始讀取 * clazz: 返回?cái)?shù)據(jù)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('找不到文件或文件路徑錯(cuò)誤, 文件：{}', filePath); } finally { try {if (fileStream != null) { fileStream.close();} } catch (IOException e) {log.info('excel文件讀取失敗, 失敗原因：{}', e); } } return null; } /** * 讀大于1000行數(shù)據(jù) * * @param filePath 文件覺(jué)得路徑 * @return */ public static List<Object> readMoreThan1000Row(String filePath) { return readMoreThan1000RowBySheet(filePath, null); } /** * 讀大于1000行數(shù)據(jù), 帶樣式 * * @param filePath 文件覺(jué)得路徑 * @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('找不到文件或文件路徑錯(cuò)誤, 文件：{}', filePath); } finally { try {if (fileStream != null) { fileStream.close();} } catch (IOException e) {log.error('excel文件讀取失敗, 失敗原因：{}', e); } } return null; } /** * 讀大于1000行數(shù)據(jù), 帶樣式 * * @return */ public static List<Object> readMoreThan1000RowBySheetFromInputStream(InputStream inputStream, Sheet sheet) { sheet = sheet != null ? sheet : initSheet; InputStream fileStream = null; ExcelListener excelListener = new ExcelListener(); EasyExcelFactory.readBySax(inputStream, sheet, excelListener); return excelListener.getDatas(); } /** * 生成excle * * @param filePath 絕對(duì)路徑 * @param data 數(shù)據(jù)源 * @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 數(shù)據(jù)源 * @param sheet excle頁(yè)面樣式 * @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('找不到文件或文件路徑錯(cuò)誤, 文件：{}', filePath); } finally { try {if (writer != null) { writer.finish();}if (outputStream != null) { outputStream.close();} } catch (IOException e) {log.error('excel文件導(dǎo)出失敗, 失敗原因：{}', e); } } } /** * 生成excle * * @param filePath 路徑 * @param data 數(shù)據(jù)源 */ public static void writeWithTemplate(String filePath, List<? extends BaseRowModel> data) { writeWithTemplateAndSheet(filePath, data, null); } /** * 生成excle * * @param filePath 路徑 * @param data 數(shù)據(jù)源 * @param sheet excle頁(yè)面樣式 */ 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('找不到文件或文件路徑錯(cuò)誤, 文件：{}', filePath); } finally { try {if (writer != null) { writer.finish();}if (outputStream != null) { outputStream.close();} } catch (IOException e) {log.error('excel文件導(dǎo)出失敗, 失敗原因：{}', 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('找不到文件或文件路徑錯(cuò)誤, 文件：{}', filePath); } finally { try {if (writer != null) { writer.finish();}if (outputStream != null) { outputStream.close();} } catch (IOException e) {log.error('excel文件導(dǎo)出失敗, 失敗原因：{}', e); } } } /*********************匿名內(nèi)部類開(kāi)始，可以提取出去******************************/ @Data public static class MultipleSheelPropety { private List<? extends BaseRowModel> data; private Sheet sheet; } /** * 解析監(jiān)聽(tīng)器， * 每解析一行會(huì)回調(diào)invoke()方法。 * 整個(gè)excel解析結(jié)束會(huì)執(zhí)行doAfterAllAnalysed()方法 * * @author: chenmingjian * @date: 19-4-3 14:11 */ @Getter @Setter public static class ExcelListener extends AnalysisEventListener { private List<Object> datas = new ArrayList<>(); /** * 逐行解析 * object : 當(dāng)前行的數(shù)據(jù) */ @Override public void invoke(Object object, AnalysisContext context) { //當(dāng)前行 // context.getCurrentRowNum() if (object != null) {datas.add(object); } } /** * 解析完所有數(shù)據(jù)后會(huì)調(diào)用該方法 */ @Override public void doAfterAllAnalysed(AnalysisContext context) { //解析結(jié)束銷毀不用的資源 } } /************************匿名內(nèi)部類結(jié)束，可以提取出去***************************/}

CommonUtil:工具類

public class CommonUtil {/** * 生成32位編碼,不含橫線 * * @return uuid串 */public static String getUUID() { String uuid = UUID.randomUUID().toString().trim().replaceAll('-', ''); return uuid.toUpperCase();}/** * 得到當(dāng)前日期格式化后的字符串，格式：yyyy-MM-dd(年-月-日) * @return 當(dāng)前日期格式化后的字符串 */public static String getTodayStr(){ return new SimpleDateFormat('yyyy-MM-dd').format(new Date()) ;}/** * 將對(duì)象轉(zhuǎn)化成json * * @param t * @return * @throws JsonProcessingException */public static <T> String toJson(T t) throws JsonProcessingException { return OBJECT_MAPPER.get().writeValueAsString(t);}}

UserPojoRes:實(shí)體類

@Setter@Getter@ToStringpublic class UserPojoRes extends BaseRowModel implements Serializable { private static final long serialVersionUID = -2145503717390503506L; /** * 主鍵 */ @ExcelProperty(value = 'ID', index = 0) private String id; /** * 姓名 */ @ExcelProperty(value = '用戶名', index = 1) private String name; public UserPojoRes(String id, String name) { this.id = id; this.name = name; } public UserPojoRes(){ }}

驗(yàn)證

模板下載

這里將模板文件放在resources中

@GetMapping('/exportExcelTempalte')@ApiOperation(value = '下載導(dǎo)入模板')public void exportExcelTempalte(HttpServletResponse response) throws Exception { //Resource目錄中的文件 String filePath = '/excels/導(dǎo)入模板.xlsx'; ClassPathResource classPathResource = new ClassPathResource(filePath); Workbook workbook=WorkbookFactory.create(classPathResource.getInputStream()); ExcelUtil.downLoadExcel('導(dǎo)入模板.xlsx', response, workbook);}

Excel文件導(dǎo)入

@PostMapping('/importExcel')@ApiOperation(value = 'Excel文件導(dǎo)入')public Response importExcel(HttpServletRequest request, MultipartFile file, HttpServletResponse response) throws Exception { List<Object> objects = ExcelUtil.readMoreThan1000RowBySheetFromInputStream(file.getInputStream(),null); List<UserPojoRes> list = new ArrayList<>(); for (Object o : objects) { UserPojoRes userPojoRes = new UserPojoRes(); List<String> stringList = (List<String>) o; userPojoRes.setId(stringList.get(0) != null ? stringList.get(0).toString() : ''); userPojoRes.setName(stringList.get(1) != null ? stringList.get(0).toString() : ''); list.add(userPojoRes); } String json = CommonUtil.toJson(list); return new Response(json);}

Excel文件導(dǎo)出

@Value('${project.tmp.files.path}')public String filesPath;@GetMapping('/exportExcel')@ApiOperation(value = 'Excel文件導(dǎo)出')public void exportExcel(HttpServletResponse response) throws Exception { //創(chuàng)建臨時(shí)文件 String path = filesPath + CommonUtil.getUUID() + '.xlsx'; List<UserPojoRes> list = new ArrayList<>(); UserPojoRes userPojoRes = new UserPojoRes('009', '張三'); UserPojoRes userPojoRes1 = new UserPojoRes('009', '李四'); list.add(userPojoRes); list.add(userPojoRes1); ExcelUtil.writeWithTemplate(path, list); // 根據(jù)excel創(chuàng)建對(duì)象 Workbook workbook = WorkbookFactory.create(new FileInputStream(path)); String fileName = '用戶模塊' + CommonUtil.getTodayStr() + '.xlsx'; ExcelUtil.downLoadExcel(fileName, response, workbook);}

到此這篇關(guān)于SpringBoot中EasyExcel實(shí)現(xiàn)Excel文件的導(dǎo)入導(dǎo)出的文章就介紹到這了,更多相關(guān)Java Excel文件導(dǎo)入導(dǎo)出內(nèi)容請(qǐng)搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持好吧啦網(wǎng)！