使用解析自定义注解和SXSSFWorkbook 实现后台导出海量数据Excel文件

川长思鸟来 2022-10-09 12:15 425阅读 0赞

### 使用解析自定义注解和SXSSFWorkbook 实现后台导出海量数据Excel文件 ###

*   *  1.1 研发背景
     *  1.2 解决方案
     *  1.3 使用方式
     *   *  1.3.1 为要导出的实体类对象使用注解
         *  1.3.2 调用导出API
     *  1.4 底层实现
     *   *  1.4.1 添加POI依赖
         *  1.4.2 自定义一个注解
         *  1.4.3 自定义一个注解解析对象
         *  1.4.4 工具类封装

## 1.1 研发背景 ##

我们公司后台管理系统导出Excel 报表文件是通过vue-element-admin 自带的前端控件实现的,早期数量少的时候还勉强够用，后来数据量大了之后,后台管理系统导出Excel 数据实在是太慢了,经常需要超时。。。

为了解决这个海量数据导出问题,博主通过调研和实践，开发出了一套解决方案。

## 1.2 解决方案 ##

这个解决方案用到的核心技术如下：

*  JDK 自定义注解和注解解析方法
 *  使用Apache POI 中的 SXSSFWorkbook 组件来处理海量数据的Excel写入和读取的问题。
 *  由于导出的时候有时候需要指定导出顺序的，因此这里采用了 Java 8 开始支持的stream 流实现字段的快速排序。

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

## 1.3 使用方式 ##

### 1.3.1 为要导出的实体类对象使用注解 ###

为了演示，这里不写更多的字段。

@Data
    public class OrderWebVO implements Serializable { 
        private static final long serialVersionUID = 42010782171332699L;
        /** * 序号 */
        @ExportExcelAnnotation(exportColumnTitle = "序号",exportDisplaySort = 1)
        private Integer numberId;
        /** * 主键Id */
        @ExportExcelAnnotation(exportColumnTitle = "订单id",exportDisplaySort = 2)
        private String id;
        private String createBy;
    }

> 其中 `exportColumnTitle` 指定导出的标题名称 `exportDisplaySort` 指定导出的顺序，数字越小越靠前

### 1.3.2 调用导出API ###

调用方式

@PostMapping(value = "/export-order-list.do")
        public VueElementAdminResponse exportOrderList(@ModelAttribute OrderWebQueryParam orderWebQueryParam){ 
            List<OrderEntity> orderEntityList=this.orderInfoWebService.exportAllOrderList(orderWebQueryParam);
            List<OrderWebVO> orderWebVOList=this.orderInfoWebService.convertToWebVOList(orderEntityList);
            String filePath=SmartBigDataExcelUtils.exportBigExcelData(orderEntityList,"/opt/data/download/excel-files/test.xlsx");
            this.vueElementAdminResponse.setCode(20000);
            this.vueElementAdminResponse.setMessage("导出订单列表成功");
            this.vueElementAdminResponse.setData(filePath);
            return this.vueElementAdminResponse;
        }

>  *  Tips: 传统的导出海量日志报表都通过增加超时时间来处理,博主认为不是一个好的架构设计。  
>     如果数据量少还好，数据量大了，而且导出一般也不是单张表的数据，一般都需要进行联表操作,你不能说一个导出按钮点击后等待几分钟，一直延长超时时间吧？
>  *  所以一种更好的设计架构如下：
>     
>      *  首先通过上面的API 进行海量数据导出，上面代码仅供测试方便，实际情况应该开启一个线程异步去执行导出操作。
>      *  然后导出文件后上传到OSS 文件服务器后就将临时文件删除。
>      *  最后再通过邮件发送导出结果即可。  
>         ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hhZHVlcw_size_16_color_FFFFFF_t_70 1]  
>         ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hhZHVlcw_size_16_color_FFFFFF_t_70 2]

## 1.4 底层实现 ##

### 1.4.1 添加POI依赖 ###

<dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>5.0.0</version>
            </dependency>
    
            
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>5.0.0</version>
            </dependency>

### 1.4.2 自定义一个注解 ###

import java.lang.annotation.*;
    
    /** * 导出Excel注解 */
    @Retention(RetentionPolicy.RUNTIME)
    @Target(ElementType.FIELD)
    @Documented
    public @interface ExportExcelAnnotation { 
        String exportColumnTitle() default "-";
        int exportDisplaySort() default 0;
    }

### 1.4.3 自定义一个注解解析对象 ###

import lombok.Data;
    
    import java.io.Serializable;
    
    /** * @author qing-feng.zhao */
    @Data
    public class ParseExcelFieldModel implements Serializable,Comparable<ParseExcelFieldModel>{ 
        String filedName;
        String exportColumnTitle;
        Integer exportDisplaySort;
    
        @Override
        public int compareTo(ParseExcelFieldModel o) { 
            return this.exportDisplaySort.compareTo(o.getExportDisplaySort());
        }
    }

### 1.4.4 工具类封装 ###

关于调用方法只需要知道这个方法就行了其他方法都是辅助。

public static String exportBigExcelDataToLocalPath(List<?> dataList, String saveFilePath);

>  *  其中第一个参数传一个任意List对象，第二个参数传要保存的文件路径地址即可。
>  *  调用示例：  
>     `List<OrderWebVOList> orderWebVOList = ...;`  
>     `SmartBigDataExcelUtils.parseExcelFieldModelList(orderWebVOList);`

如果想了解我是如何解析注解的可以看这个方法

List<ParseExcelFieldModel> parseExcelFieldModelList(Class currentClass)

如果想传值，只需要：`SmartBigDataExcelUtils.parseExcelFieldModelList(OrderWebVO.class);` 即可。

完整工具类定义如下：

import lombok.extern.slf4j.Slf4j;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.xssf.streaming.SXSSFWorkbook;
    
    import java.io.FileNotFoundException;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.lang.reflect.Field;
    import java.util.*;
    import java.util.concurrent.atomic.AtomicInteger;
    import java.util.stream.Collectors;
    
    /** * @author qing-feng.zhao */
    @Slf4j
    public class SmartBigDataExcelUtils { 
        /** * 导出数据列表 * 导出文件保存地址 * @param dataList * @param saveFilePath * @return */
        public static String exportBigExcelDataToLocalPath(List<?> dataList, String saveFilePath){ 
            SXSSFWorkbook wb = SmartBigDataExcelUtils.writeBigDataListToExcel(dataList);
            try(FileOutputStream fileOutputStream=new FileOutputStream(saveFilePath)){ 
                wb.write(fileOutputStream);
            } catch (FileNotFoundException e) { 
                log.error("文件没有找到:",e);
            } catch (IOException e) { 
                log.error("IO异常:",e);
            }finally { 
                //处理磁盘上支持此工作簿的临时文件
                wb.dispose();
            }
            return saveFilePath;
        }
        /** * 生成WB数据 * @param dataList * @return */
        public static SXSSFWorkbook writeBigDataListToExcel(List<?> dataList){ 
            // 如果数据为空则不导出任何数据
            if(null==dataList||dataList.size()<=0){ 
                return null;
            }
            // 大数据处理Excel 核心代码
            // 创建一个Excel表格
            SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
            // 临时文件将会被压缩
            wb.setCompressTempFiles(true);
            // 创建一个Sheet
            Sheet sheet = wb.createSheet();
    
            // 是否有标题行
            Boolean haveTitleFlag=false;
    
            Object parseObject=dataList.get(0);
            // 返回有序的对象
            List<String> columnTitleList=parseExcelColumnTitleList(parseObject.getClass());
            // 初始化标题头
            if(null!=columnTitleList&&columnTitleList.size()>0){ 
                // 创建标题行
                Row row = sheet.createRow(0);
                // 创建标题行列数
                for (int i = 0; i < columnTitleList.size(); i++) { 
                    // 创建一个单元格
                    Cell cell = row.createCell(i);
                    cell.setCellValue(columnTitleList.get(i));
                }
                haveTitleFlag=true;
            }
    
            // 解析数据
            Boolean haveDataFlag=false;
            // 检查是否有数据
            if(null!=dataList&&dataList.size()>0){ 
                haveDataFlag=true;
            }
            // 如果有数据
            Integer lineNumber=0;
            if(haveDataFlag){ 
                if (haveTitleFlag) { 
                    lineNumber=1;
                } else if (!(haveTitleFlag)) { 
                    lineNumber=0;
                }
            }
            List<Map<String,Object>> dataRowLineMapList=parseExcelSheetData(dataList);
    
            for (Map<String,Object> dataRowLineMap:dataRowLineMapList
            ) { 
                Row row = sheet.createRow(lineNumber);
                List<ParseExcelFieldModel> parseExcelFieldModelList=parseExcelFieldModelList(parseObject.getClass());
                // 列数从0开始
                AtomicInteger columnNumber=new AtomicInteger(0);
                for (ParseExcelFieldModel parseExcelFieldModel:parseExcelFieldModelList
                ) { 
                    String checkKey=parseExcelFieldModel.getFiledName();
                    Cell cell = row.createCell(columnNumber.get());
                    if(null!=dataRowLineMap.get(checkKey)){ 
                        cell.setCellValue(String.valueOf(dataRowLineMap.get(checkKey)));
                    }else{ 
                        cell.setCellValue("-");
                    }
                    columnNumber.getAndIncrement();
                }
                lineNumber++;
            }
            return wb;
        }
        
        /** * 核心技术 * 1. 解析字段中的注解并按照指定顺序排序 * 2. 使用Stream流实现快速排序 * * @param currentClass * @return */
        public static List<ParseExcelFieldModel> parseExcelFieldModelList(Class currentClass){ 
            try { 
                List<ParseExcelFieldModel> parseExcelFieldModelList=new ArrayList<>();
                // 获取该对象的所有字段
                Field[] filedArray=currentClass.getDeclaredFields();
                ParseExcelFieldModel parseExcelFieldModel;
                for (Field fieldItem:filedArray
                ) { 
                    // 获取读写权限
                    fieldItem.setAccessible(true);
                    // 检查是否有注解
                    if(fieldItem.isAnnotationPresent(ExportExcelAnnotation.class)){ 
                        // 获取字段注解对象
                        ExportExcelAnnotation exportExcelAnnotation =fieldItem.getAnnotation(ExportExcelAnnotation.class);
    
                        parseExcelFieldModel=new ParseExcelFieldModel();
    
                        // 获取列对象
                        String columnTitleName = exportExcelAnnotation.exportColumnTitle();
                        Integer exportDisplaySort = exportExcelAnnotation.exportDisplaySort();
                        String filedName=fieldItem.getName();
    
                        if(null!=columnTitleName){ 
                            parseExcelFieldModel.setExportColumnTitle(columnTitleName);
                        }
                        if(null!=exportDisplaySort){ 
                            parseExcelFieldModel.setExportDisplaySort(exportDisplaySort);
                        }
                        if(null!=filedName){ 
                            parseExcelFieldModel.setFiledName(filedName);
                        }
                        parseExcelFieldModelList.add(parseExcelFieldModel);
                    }
                }
                parseExcelFieldModelList=parseExcelFieldModelList.stream()
                        .sorted(
                                (Comparator.comparing(ParseExcelFieldModel::getExportDisplaySort,Comparator.nullsLast(Integer::compareTo)))
                        ).collect(Collectors.toList());
                // 返回排序后的结果
                return parseExcelFieldModelList;
            } catch (SecurityException e) { 
                log.error("解析出错",e);
            }
            return Collections.EMPTY_LIST;
        }
        /** * 解析标题成String * @param parseExcelFieldModelList * @return 有序的标题列表 */
        protected static List<String> parseColumnTitleList(List<ParseExcelFieldModel> parseExcelFieldModelList){ 
            if(null==parseExcelFieldModelList||parseExcelFieldModelList.size()<=0){ 
                return Collections.EMPTY_LIST;
            }
            List<String> columnTitleList=new ArrayList<>(parseExcelFieldModelList.size());
            for (ParseExcelFieldModel item:parseExcelFieldModelList
            ) { 
                columnTitleList.add(item.getExportColumnTitle());
            }
            return columnTitleList;
        }
        /** * 解析标题 * @param currentClass * @return */
        protected static List<String> parseExcelColumnTitleList(Class currentClass){ 
            log.info("currentClass={}",currentClass);
            return parseColumnTitleList(parseExcelFieldModelList(currentClass));
        }
        /** * 解析对象字段Map * @param currentClass * @return */
        protected static Map<String,String> parseExcelColumnTitleMap(Class currentClass){ 
            // 存储标题对象
            Map<String,String> columnTitleMap=new HashMap<>();
            // 获取该对象的所有字段
            Field[] filedArray=currentClass.getDeclaredFields();
            for (Field fieldItem:filedArray
            ) { 
                // 获取读写权限
                fieldItem.setAccessible(true);
                // 检查是否有注解
                if(fieldItem.isAnnotationPresent(ExportExcelAnnotation.class)){ 
                    // 获取字段注解对象
                    ExportExcelAnnotation exportExcelAnnotation =fieldItem.getAnnotation(ExportExcelAnnotation.class);
                    // 获取列对象
                    String columnTitleName = exportExcelAnnotation.exportColumnTitle();
                    columnTitleMap.put(fieldItem.getName(),columnTitleName);
                }
            }
            return columnTitleMap;
        }
        protected static List<Map<String,Object>> parseExcelSheetData(List<?> dataList){ 
            List<Map<String,Object>> parseData=new ArrayList<>();
            for (Object o:dataList){ 
                Map<String,Object> linkedMap=new LinkedHashMap<>();
                // 获取所有字段
                Field[] filedArray=o.getClass().getDeclaredFields();
                for (Field field:filedArray){ 
                    field.setAccessible(true);
                    String columnTitleName=null;
                    // 检查字段上是否有ExportExcelAnnotation注解存在
                    if(field.isAnnotationPresent(ExportExcelAnnotation.class)){ 
                        ExportExcelAnnotation exportExcelAnnotation =field.getAnnotation(ExportExcelAnnotation.class);
    
                        if(null!=exportExcelAnnotation&&null!=exportExcelAnnotation.exportColumnTitle()){ 
                            columnTitleName= exportExcelAnnotation.exportColumnTitle();
                            if(null== SmartStringUtils.trimToNull(columnTitleName)){ 
                                columnTitleName=field.getName();
                            }
                        }
                        if(null!=columnTitleName){ 
                            Object value=null;
                            try { 
                                value=field.get(o);
                            } catch (IllegalAccessException e) { 
                                log.error("权限异常",e);
                            }
                            linkedMap.put(field.getName(),value);
                        }
                    }
                }
                parseData.add(linkedMap);
            }
            return parseData;
        }
        private SmartBigDataExcelUtils(){ }
    }

这里依赖了一个工具类

@Slf4j
    public class SmartStringUtils { 
    
        private SmartStringUtils(){ }
    
        public static String trimToNull(String str) { 
            String ts = trim(str);
            if(null!=ts && ts.equals("null")){ 
                return null;
            }
            return isEmpty(ts) ? null : ts;
        }
        private static String trim(String str) { 
            return str == null ? null : str.trim();
        }
        private static boolean isEmpty(CharSequence cs) { 
            return cs == null || cs.length() == 0;
        }
    }

本篇完~

交流即分享,分享才能进步～

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hhZHVlcw_size_16_color_FFFFFF_t_70]: /images/20221005/c9d57b2485454dcb9ecffdd3dd00c18d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hhZHVlcw_size_16_color_FFFFFF_t_70 1]: /images/20221005/3b5f3be63e3f4ce98b447902b4d82a78.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hhZHVlcw_size_16_color_FFFFFF_t_70 2]: /images/20221005/6ec2d2375c1b4e189aeab3a931816c1d.png