EasyExcel实现Excel文件的导入导出

小鱼儿 2022-12-10 08:45 358阅读 0赞

# EasyExcel实现Excel文件的导入导出 #

## 前言 ##

在我们日常的开发过程中经常会使用`Excel`文件的形式来批量地上传下载系统数据，我们最常用的工具是`Apache poi`,但是如果数据到底上百万时，将会造成内存溢出的问题，那么我们怎么去实现百万数据批量导入导出。

## 正文 ##

## Easyexcel ##

**Easyexcel** 是阿里巴巴的开源项目，用来优化`Excel`文件处理过程：

*  **poi消耗内存严重**：Java解析、生成`Excel`比较有名的框架有`Apache poi`、`jxl`。但他们都存在一个严重的问题就是非常的耗内存，`poi`有一套`SAX`模式的`API`可以一定程度的解决一些内存溢出的问题，但`poi`还是有一些缺陷，比如07版`Excel`解压缩以及解压后存储都是在内存中完成的，内存消耗依然很大。
 *  **easyexcel针对内存做出了优化**：重写了`poi`对07版`Excel`的解析，能够原本一个3M的`excel`用`POI sax`依然需要100M左右内存降低到几M，并且再大的`excel`不会出现内存溢出。

![在这里插入图片描述][20200925005027772.png_pic_center]

## SpringBoot+ EasyExcel实现Excel文件的导入导出 ##

**导入依赖**

<dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.2</version>
        <optional>true</optional>
    </dependency>
    
    
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>1.1.2-beat1</version>
    </dependency>
    
    
    <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`添加以下内容**

<build>
        <plugins>
            
            <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 files
    project.tmp.files.path=/Users/mac/Desktop/image/tmp/files/

**在SpringBoot启动类添加临时文件设置**

@Value("${project.tmp.files.path}")
    public String filesPath;
    
    @Bean
    MultipartConfigElement multipartConfigElement() { 
        MultipartConfigFactory factory = new MultipartConfigFactory();
        //设置路径xxx
        factory.setLocation(filesPath);
        return factory.createMultipartConfig();
    }

**ExcelUtil:Excel工具类**

@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<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 绝对路径 * @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);
                }
            }
        }
    
    
        /*********************匿名内部类开始，可以提取出去******************************/
    
    
    
    
        @Data
        public static class MultipleSheelPropety { 
            private List<? extends BaseRowModel> data;
            private Sheet sheet;
        }
    
    
    
    
        /** * 解析监听器， * 每解析一行会回调invoke()方法。 * 整个excel解析结束会执行doAfterAllAnalysed()方法 * * @author: chenmingjian * @date: 19-4-3 14:11 */
        @Getter
        @Setter
        public static class ExcelListener extends AnalysisEventListener { 
            private List<Object> datas = new ArrayList<>();
    
    
            /** * 逐行解析 * object : 当前行的数据 */
            @Override
            public void invoke(Object object, AnalysisContext context) { 
                //当前行
                // context.getCurrentRowNum()
                if (object != null) { 
                    datas.add(object);
                }
            }
    
    
            /** * 解析完所有数据后会调用该方法 */
            @Override
            public void doAfterAllAnalysed(AnalysisContext context) { 
                //解析结束销毁不用的资源
            }
    
    
    
    
        }
    
    
        /************************匿名内部类结束，可以提取出去***************************/
    }

**CommonUtil:工具类**

public class CommonUtil { 
    
    /** * 生成32位编码,不含横线 * * @return uuid串 */
    public static String getUUID() { 
        String uuid = UUID.randomUUID().toString().trim().replaceAll("-", "");
        return uuid.toUpperCase();
    }
    
    /** * 得到当前日期格式化后的字符串，格式：yyyy-MM-dd(年-月-日) * @return 当前日期格式化后的字符串 */
    public static String getTodayStr(){ 
        return new SimpleDateFormat("yyyy-MM-dd").format(new Date()) ;
    }
    
    /** * 将对象转化成json * * @param t * @return * @throws JsonProcessingException */
    public static <T> String toJson(T t) throws JsonProcessingException { 
        return OBJECT_MAPPER.get().writeValueAsString(t);
    }
    
    }

**UserPojoRes:实体类**

@Setter
    @Getter
    @ToString
    public 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(){ 
    
    
        }
    }

## 验证 ##

**模板下载**

*  这里将模板文件放在`resources`中

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

**Excel文件导入**

@PostMapping("/importExcel")
    @ApiOperation(value = "Excel文件导入")
    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);
    }

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDk5MDgxOA_size_16_color_FFFFFF_t_70_pic_center]

**Excel文件导出**

@Value("${project.tmp.files.path}")
    public String filesPath;
    
    @GetMapping("/exportExcel")
    @ApiOperation(value = "Excel文件导出")
    public void exportExcel(HttpServletResponse response) throws Exception { 
    
        //创建临时文件
        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);
        // 根据excel创建对象
        Workbook workbook = WorkbookFactory.create(new FileInputStream(path));
        String fileName = "用户模块" + CommonUtil.getTodayStr() + ".xlsx";
        ExcelUtil.downLoadExcel(fileName, response, workbook);
    }

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDk5MDgxOA_size_16_color_FFFFFF_t_70_pic_center 1]

## 源码 ##

项目源码可从的我的github中获取：[github源码地址][github]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDk5MDgxOA_size_16_color_FFFFFF_t_70_pic_center 2]

[20200925005027772.png_pic_center]: /images/20221123/f1672bb09e5c40acafaba56a5fc0aed4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDk5MDgxOA_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/04db4b6940a04d2db9bda907d1259951.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDk5MDgxOA_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221123/6f936c9add1746cdba9ad2ec73958708.png
[github]: https://github.com/luo948521848/xiyuan-for-dubbo
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDk5MDgxOA_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221123/7c14a3c266d14521982ccab42bffa0ee.png