Java项目中Excel百万级别的导入导出

痛定思痛。 2023-02-14 13:06 20阅读 0赞

在Excel文件导入过程中实际上有两个问题 (采用apche poi的方式)  
\* xls的文件限制了不能一次性导出65536条数据  
\* 实际过程中Excel文件的解析有可能会造成内存溢出的问题  
\* mybatis（mysql）批量导入数据性能过差

内存泄漏：是指申请的内存空间无法正常被收回，造成“无用又不可收回”的状况内存溢出：指程序在申请内存空间时，已经没有足够的内存空间被使用。（内存泄漏通常是内存溢出的主要原因）针对上述的问题，需要对项目进一步地优化：  
1. 文件上传到服务器的请求方式使用ftp而不是http  
2. 尽可能减少遍历的次数  
3. 使用StringBuffer而不是String (并且StringBuffer相对于StringBuilder线程安全的)  
4. 使用FileWriter写入文件时要要一次写入，减少IO读写次数  
5. 采用多线程的方式去批量地提交事务 （补充 提供List.subList(开始index，结束index)的方式截取集合，或者采用流式表达式的方式进行切分）https://blog.csdn.net/FKJGFK/article/details/104004216  
6. mysql数据库不能同时支持30万数据的 一次性事务提交，需要分批次地去进行事务的提交，否则数据将堆积到mysql数据库中，导致数据库性能过低。（需要采用编程式事务的方式分批次多次提交）

## **方式一：优化内存** ##

针对内存溢出的情况我们做以下调整：  
1.提高了编译器的jvm的空间 -Xms128m -Xmx1700m （在项目的启动脚本中 nohup -Xms128m -Xmx1700m -jar 包名 & ）  
2.创建Workbook 借助xlsx-streamer（StreamingReader） 来创建一个缓冲区域批量地读取文件 （不会将整个文件实例化到对象当中）  
依赖

<dependency>
       <groupId>com.monitorjbl</groupId>
       <artifactId>xlsx-streamer</artifactId>
       <version>2.1.0</version>
    </dependency>

实现方式

public List<AddressBookPojo> importExcelxlsx( String tmpFile ) { 
        List<AddressBookPojo> list = new ArrayList<AddressBookPojo>();
        FileInputStream in=null;
        try { 
            in = new FileInputStream(tmpFile);
            Workbook wk = StreamingReader.builder()
                    .rowCacheSize(1000)  //缓存到内存中的行数，默认是10
                    .bufferSize(8192)  //读取资源时，缓存到内存的字节大小，默认是1024
                    .open(in);  //打开资源，必须，可以是InputStream或者是File，注意：只能打开XLSX格式的文件
            Sheet sheet = wk.getSheetAt(0);
            int i =0;
            for (Row row : sheet) { 
                i++;//从第2行开始取数据,默认第一行是表头.
                if(i==1)
                    continue;
                AddressBookPojo abp = new AddressBookPojo();
                //遍历所有的列
                int j=0;
                for (Cell cell : row) { 
                    switch (j) { 
                        case 0 :abp.setName(cell.getStringCellValue());break;
                        case 1 :abp.setFristDepartName(cell.getStringCellValue());break;
                        /**这里省略其他的属性**/
                        default: break;
                    }
                    j++;
                }
                list.add(abp);
            }
        } catch (IOException e) { 
            e.printStackTrace();
        }finally { 
            IOUtils.closeQuietly(in);
        }
        return list;
    }

## 方式二：EasyExcel ##

EasyExcel是阿里的引用了apache poi，所以不能再引用apache poi的依赖。  
使用lombok可以简化Excel对POJO的绑定关系。  
依赖

<dependency>
       <groupId>com.alibaba</groupId>
       <artifactId>easyexcel</artifactId>
       <version>1.1.2-beat1</version>
    </dependency>
    
    
    
    <dependency>
       <groupId>org.projectlombok</groupId>
       <artifactId>lombok</artifactId>
       <version>1.18.2</version>
       <optional>true</optional>
    </dependency>

**工具类**

package com.xiyuan.startingpoint.utils;
    import com.alibaba.excel.EasyExcelFactory;
    import com.alibaba.excel.ExcelWriter;
    import com.alibaba.excel.context.AnalysisContext;
    import com.alibaba.excel.event.AnalysisEventListener;
    import com.alibaba.excel.metadata.BaseRowModel;
    import com.alibaba.excel.metadata.Sheet;
    import lombok.Data;
    import lombok.Getter;
    import lombok.Setter;
    import lombok.extern.slf4j.Slf4j;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.springframework.util.CollectionUtils;
    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;
    
    /** * @description: * @author: chenmingjian * @date: 19-3-18 16:16 */
    @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;
        }
    
        /** * 生成excle * @param filePath 绝对路径, 如：/home/chenmingjian/Downloads/aaa.xlsx * @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 绝对路径, 如：/home/chenmingjian/Downloads/aaa.xlsx * @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 绝对路径, 如：/home/chenmingjian/Downloads/aaa.xlsx * @param data 数据源 */
        public static void writeWithTemplate(String filePath, List<? extends BaseRowModel> data){ 
            writeWithTemplateAndSheet(filePath,data,null);
        }
    
        /** * 生成excle * @param filePath 绝对路径, 如：/home/chenmingjian/Downloads/aaa.xlsx * @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 绝对路径, 如：/home/chenmingjian/Downloads/aaa.xlsx * @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) { 
                //解析结束销毁不用的资源
            }
    
        }
    
        /************************匿名内部类结束，可以提取出去***************************/
    
    
    }

**POJO**

/** * 用户Pojo */
    @Data
    @TableName("tb_user")
    @EqualsAndHashCode(callSuper = true)
    @JsonIgnoreProperties(ignoreUnknown = true)
    public class UserInfoPojo extends CommentPojo{ 
    
        private static final long serialVersionUID = 1L;
        //解决mybatis-plus和easyExcel的冲突问题
        @JsonIgnoreProperties(ignoreUnknown = true)
        @TableField(exist = false)
        private Map<Integer,CellStyle> cellStyleMap = new HashMap<Integer, CellStyle>();
    
    
        @TableId(type = IdType.UUID)
        private String id; //主键
    
    
        @TableField("user_name")
        @ExcelProperty(value = "用户名（必填）", index = 0)
        private String userName; //用户名
        @ExcelProperty(value = "密码（必填）", index = 1)
        private String password;//密码
    }

**Excel导入**

/** * 导入Excel * * @param file * @param username * @return */
    @Override
    public ResponseResult importExcel(MultipartFile file, String username) throws Exception { 
        String tempfilePath = null;
        File tempfile = null;
        FileWriter fw = null;
        try { 
            //第一步：创建临时文件
            String fileType = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1);
            tempfilePath = tempPath + CommonUtil.getUUID() + "." + fileType;
            tempfile = new File(tempfilePath);
            file.transferTo(tempfile);
            List<Object> objects = ExcelUtil.readMoreThan1000Row(tempfilePath);
            //第二步：进行Excel文件校验
            ResponseResult responseResult = checkExcel(objects,username);
            if (responseResult.getSuccess()) { //List读取成功
                List<UserInfoPojo> list = (List<UserInfoPojo>) responseResult.getValue();
                insertBatch(list);
                return ResponseResult.success(ConstantsUtil.OPERATE_SUCCESS);
            } else { //读取失败
                String errortempFile = tempPath +"/"+username+ "/user/" + CommonUtil.getUUID() + ".txt";
                File file1 = new File(errortempFile);
                fw = new FileWriter(errortempFile);
                fw.write(responseResult.getMessage());
                responseResult.setValue(errortempFile);
                return responseResult;
            }
        } catch (IOException e) { 
            logger.error("用户模块导入失败" + e.getMessage(), e);
            return ResponseResult.error(ConstantsUtil.OPERATE_ERROR);
        } finally { 
            if(fw !=null)
            fw.close();
    
    
            FileUtils.deleteQuietly(tempfile);
        }
    }
    
    private ResponseResult checkExcel(List<Object> objects,String username) throws Exception { 
        List<UserInfoPojo> userInfoPojos = new ArrayList<>();
        StringBuffer bf = new StringBuffer();
        if (objects != null && objects.size() > 1) { 
            int i = 2;
            for (int j = 1; j < objects.size(); j++) { 
                List<String> list = (List<String>) objects.get(j);
                if(list.size()<2){ 
                    bf.append("第" + i + "行中的存在大量必填项未填写，请检查" + newLine);
                    continue;
                }
                UserInfoPojo userInfoPojo = new UserInfoPojo();
                userInfoPojo.setId(CommonUtil.getUUID());
                userInfoPojo.setUpdater(username);
                userInfoPojo.setUpdateTime(new Date());
                if (StringUtils.isBlank(list.get(0))) { 
                    bf.append("第" + i + "行中的用户名为必填项不得为空" + newLine);
                } else { 
                    userInfoPojo.setUserName(list.get(0));
                }
                if (StringUtils.isBlank(list.get(1))) { 
                    bf.append("第" + i + "行中的密码为必填项不得为空" + newLine);
                } else { 
                    userInfoPojo.setPassword(list.get(1));
                }
                userInfoPojos.add(userInfoPojo);
                i++;
            }
        }
        if (bf.length() > 0) { //导入失败
            return ResponseResult.error(bf.toString());
        } else { //导入成功
            return ResponseResult.success(userInfoPojos, ConstantsUtil.OPERATE_SUCCESS);
        }
    }

**Excel导出**

/** * Excel文件导入 * * @param list * @return */
    @Override
    public String exportExcel(List<UserInfoPojo> list) throws Exception { 
        String tempfile = tempPath + CommonUtil.getUUID() + ".xlsx";
        ExcelUtil.writeWithTemplate(tempfile, list);
        return tempfile;
    }

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDk5MDgxOA_size_16_color_FFFFFF_t_70_pic_center]: https://img-blog.csdnimg.cn/20200812232921559.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDk5MDgxOA==,size_16,color_FFFFFF,t_70#pic_center