java dubbo服务导出excel数据量过大解决方案

场景

公司项目有个以前的程序猿写的订单导出excel功能，后台管理界面先查询要导出的数据，然后点击导出按钮进行导出，原来的实现就是界面吧查询条件传到admim模块，admin通过dubbo远程调用orderService层去查数据库返回数据，然后再在admin把数据放入excel文件，设置返回头信息后返回到浏览器下载，
当初开发测试数据量小没什么问题，后来线上环境数据量大后导出就报错了，
具体就是39000多条还能导出，到了40000多条后就导出失败了，排查定位后，得出结论是dubbo不支持传输大数据，

解决方案

方案一

orderService层查出来后放缓存中间件，然后admim模块再去查缓存中间件，redis的应该可以实现，但是我们公司用的是memcached，存放list要先转成json,但是大数据量转json非常耗时，行不通，

方案二

orderService层查出来后放mongoDB，然后admim模块再去mongoDB拿，这个方案可行，但是我们的admin和order模块是没有mongoDB链接配置的，所以要用这方案，要再2个模块里加链接配置，这样的话还不如直接让admin链数据库，直接查询了

方案三-最终选择方案

orderService层查出来后放本地内存进行缓存，然后admin分多次请求去拿数据，拿完后再把orderService层内存的数据清除（暂时么有用多线程去分多少请求，后期优化可以用多线程去分多次请求去拿数据）那到数据后分sheet页写数据，生成一个excel文件，然后压缩成zip文件，通过流得方式写出

代码

• 控制层

 @RequestMapping("/admin/order/common/list/export")
    public void export(HttpServletRequest request, HttpServletResponse response) {
        // 文件名获取
        Date date = new Date();
        SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
        String f = "订单-" + format.format(date);
        String fileName = f;
        ExcelUtil.setResponseHeader(response,fileName);
        OutputStream out = null;
        try {
            out = response.getOutputStream();
            Map<String, Object> paramsMap = getMap(request);
            String key = UUID.randomUUID().toString();
            AjaxList ajaxList= orderService.listExtend(paramsMap,key);
            if (ajaxList.isSuccess()) {
                int count = (int)ajaxList.getData();
                String path = request.getSession().getServletContext().getRealPath("/");
                String excelPath = path + "excel/";
                File excelFile = new File(excelPath);
                if (!excelFile.exists()) {
                    excelFile.mkdir();
                }
                // 压缩文件
                File zip = new File(path + "excel/"  + f + ".zip");
                //创建excel
                Workbook book = new HSSFWorkbook();
                String file = path + "excel/" + f + ".xls";
                FileOutputStream fos = new FileOutputStream(file);
                Sheet sheet = null;
                //分段去orderservice层拿数据
                for (int i = 0; i <= count; i+=10000) {
                    AjaxList ajaxList1 = orderService.getOrderListFromCache(key, i, i + 10000);
                    if (ajaxList1.isSuccess()) {
                        //创建新的sheet
                        sheet = book.createSheet((i+1)+"-"+(i+10000));
                        List<GeneralOrderBean> list= (List<GeneralOrderBean>)ajaxList1.getData();
                        //转excel
                        toExcel(list,book,sheet,fos);
                    }
                }
                //写excel文件
                book.write(fos);
                fos.flush();
                fos.close();
                //删除orderService层的本地缓存
                orderService.deleteOrderListFromCache(key);
                File []srcfile = new File[1];
                srcfile[0] = new File(file);

                ExcelUtil.zipFiles(srcfile, zip);
                FileInputStream inStream = new FileInputStream(zip);
                byte[] buf = new byte[4096];
                int readLength;
                //用流得方式写出
                while ((readLength = inStream.read(buf)) != -1) {
                    out.write(buf, 0, readLength);
                }
                inStream.close();
                List<String> fileNames = new ArrayList<>();
                fileNames.add(file);
                //删除文件
                ExcelUtil.deleteFile(fileNames, path + "excel/"  + f + ".zip");
            }
        } catch (IOException e1) {
            e1.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                out.flush();
                out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

• toExcel 方法

/**
     * 生成excel
     * @param list
     * @throws IOException
     */
    public void toExcel(List<GeneralOrderBean> list,Workbook book,Sheet sheet,FileOutputStream fos) throws IOException {

        // 生成excel
        try {
            //宽度5.38
            sheet.setColumnWidth(0, ((5 + 1) * 7 + 5) / 7 * 256);
            //宽度50.38
            sheet.setColumnWidth(1, ((10 + 1) * 7 + 5) / 7 * 256);
            sheet.setColumnWidth(2, ((25 + 1) * 7 + 5) / 7 * 256);
            sheet.setColumnWidth(3, ((25 + 1) * 7 + 5) / 7 * 256);
            sheet.setColumnWidth(4, ((25 + 1) * 7 + 5) / 7 * 256);
            sheet.setColumnWidth(5, ((20 + 1) * 7 + 5) / 7 * 256);
            sheet.setColumnWidth(6, ((20 + 1) * 7 + 5) / 7 * 256);
            sheet.setColumnWidth(7, ((20 + 1) * 7 + 5) / 7 * 256);
            sheet.setColumnWidth(8, ((20 + 1) * 7 + 5) / 7 * 256);
            sheet.setColumnWidth(9, ((20 + 1) * 7 + 5) / 7 * 256);
            sheet.setColumnWidth(10, ((20 + 1) * 7 + 5) / 7 * 256);
            sheet.setColumnWidth(11, ((20 + 1) * 7 + 5) / 7 * 256);
            sheet.setColumnWidth(12, ((35 + 1) * 7 + 5) / 7 * 256);
            sheet.setColumnWidth(13, ((35 + 1) * 7 + 5) / 7 * 256);
            sheet.setColumnWidth(14, ((20 + 1) * 7 + 5) / 7 * 256);
            sheet.setColumnWidth(15, ((20 + 1) * 7 + 5) / 7 * 256);
            //创建第一行
            Row row = sheet.createRow(0);
            row.createCell(0).setCellValue("序号");
            row.createCell(1).setCellValue("类型");
            row.createCell(2).setCellValue("编号");
            row.createCell(3).setCellValue("用车时间");
            row.createCell(4).setCellValue("下单人");
            row.createCell(5).setCellValue("下单人电话");
            row.createCell(6).setCellValue("乘车人");
            row.createCell(7).setCellValue("乘车人电话");
            row.createCell(8).setCellValue("司机");
            row.createCell(9).setCellValue("司机电话");
            row.createCell(10).setCellValue("车牌");
            row.createCell(11).setCellValue("车型");
            row.createCell(12).setCellValue("出发地");
            row.createCell(13).setCellValue("目的地");
            row.createCell(14).setCellValue("状态");
            row.createCell(15).setCellValue("订单来源");

            for (int i = 0; i < list.size(); i++) {
                GeneralOrderBean bean = list.get(i);
                Row dateRow = sheet.createRow(i);
                dateRow.createCell(0).setCellValue(i+1);
                for (int cellNum = 1; cellNum <= MagicConstant.INT_FIFTEEN; cellNum++) {

                    Cell cell = dateRow.createCell(cellNum);
                    switch (cellNum) {
                        case 1: {
                            cell.setCellValue(commonService.getTypeTripName(bean.getTypeTripNew()) + "-" + commonService.getTypeTimeName(bean.getTypeTimeNew()));
                            break;
                        }
                        case 2: {
                            if (bean.getOrderNo() != null) {
                                cell.setCellValue(bean.getOrderNo());
                            }
                            break;
                        }
                        case 3: {
                            if (bean.getDeparTime() != null) {
                                cell.setCellValue(DateUtils.format(bean.getDeparTime(), "yyyy-MM-dd HH:mm:ss"));
                            }
                            break;
                        }
                        case 4: {
                            if (bean.getPasNickName() != null) {
                                cell.setCellValue(bean.getPasNickName());
                            }
                            break;
                        }
                        case 5: {
                            cell.setCellValue(bean.getPasMobile());
                            break;
                        }
                        case 6: {
                            if (bean.getActPasNickName() != null) {
                                cell.setCellValue(bean.getActPasNickName());
                            }
                            break;
                        }
                        case 7: {
                            cell.setCellValue(bean.getActPasMobile());
                            break;
                        }
                        case 8: {
                            if (bean.getDriName() != null) {
                                cell.setCellValue(bean.getDriName());
                            }
                            break;
                        }
                        case 9: {
                            if (bean.getDirMobile() != null) {
                                cell.setCellValue(bean.getDirMobile());
                            }
                            break;
                        }
                        case 10: {
                            if (bean.getPlateNum() != null) {
                                cell.setCellValue(bean.getPlateNum());
                            }
                            break;
                        }
                        case 11: {
                            if (bean.getLevelName() != null) {
                                cell.setCellValue(bean.getLevelName());
                            }
                            break;
                        }
                        case 12: {
                            cell.setCellValue(bean.getOriginAddr());
                            break;
                        }
                        case 13: {
                            if (bean.getDestAddr() != null) {
                                cell.setCellValue(bean.getDestAddr());
                            }
                            break;
                        }
                        case 14: {
                            cell.setCellValue(commonService.getOrderMainStatusName(bean.getMainStatus()));
                            break;
                        }
                        case 15: {
                            cell.setCellValue(commonService.getOrderSourceName(bean.getSource()));
                            break;
                        }
                        default:
                            break;
                    }
                }

            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

 /**
 * 设置响应头
 */
public static void setResponseHeader(HttpServletResponse response,String fileName) {
    try {
        response.setContentType("application/octet-stream;charset=UTF-8");
        response.setHeader("Content-Disposition", "attachment;filename="
                + java.net.URLEncoder.encode(fileName, "UTF-8")
                + ".zip");
        response.addHeader("Pargam", "no-cache");
        response.addHeader("Cache-Control", "no-cache");
    } catch (Exception ex) {

    }
}

/**
 *
 * @param srcfile 文件名数组
 * @param zipfile 压缩后文件
 */
public static void zipFiles(java.io.File[] srcfile, java.io.File zipfile) {
    byte[] buf = new byte[1024];
    try {
        ZipOutputStream out = new ZipOutputStream(new FileOutputStream(
                zipfile));
        for (int i = 0; i < srcfile.length; i++) {
            FileInputStream in = new FileInputStream(srcfile[i]);
            out.putNextEntry(new ZipEntry(srcfile[i].getName()));
            int len;
            while ((len = in.read(buf)) > 0) {
                out.write(buf, 0, len);
            }
            out.closeEntry();
            in.close();
        }
        out.close();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

/**
 * 文件删除
 * @param fileNames
 * @param zipPath
 */
public static void deleteFile(List<String> fileNames, String zipPath) {
    String sPath = null;
    File file = null;
    boolean flag = false;
    try {
        // 判断目录或文件是否存在
        for (int i = 0; i < fileNames.size(); i++) {
            sPath = fileNames.get(i);
            file = new File(sPath);
            if (file.exists())  {
                file.delete();
            }
        }
        file = new File(zipPath);
        if (file.exists())  {
            file.delete();
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}   
    

• orderService层代码

/**
 *  先查询总list放本地缓存 服务类
 */
public AjaxList listExtend(Map<String, Object> params,String key) {
    List<GeneralOrderBean> generalOrderBeanList=orderMapper.listExtend(params);
    if (CollectionUtils.isEmpty(generalOrderBeanList)) {
        return AjaxList.createError("获取订单信息失败");
    }
    OrderExportCacheUtil.ordereXportCacheMap.put(key, generalOrderBeanList);
    return  AjaxList.createSuccess("获取订单信息成功",generalOrderBeanList.size());
}

/**
 *  分段在本地内存拿数据 服务类
 */
public AjaxList getOrderListFromCache(String key, int start, int end) {
    List<GeneralOrderBean> generalOrderBeanList = OrderExportCacheUtil.getGeneralOrderBeanList(key, start, end);
    if (CollectionUtils.isEmpty(generalOrderBeanList)) {
        return AjaxList.createError("获取失败");
    }
    return AjaxList.createSuccess("获取成功", generalOrderBeanList);
}

/**
 *  删除本地缓存数据 服务类
 */
public AjaxList deleteOrderListFromCache(String key) {
    OrderExportCacheUtil.delectListByKey(key);
    return AjaxList.createSuccess("删除成功");
}

• OrderExportCacheUtil 订单本地缓存类

/**
 * Description:  订单本地缓存
 *
 * @author daylan
 * @date 2018/12/06
 * @Version: 1.0
 */
public class OrderExportCacheUtil {
    /**
     *  用来缓存
     */
    public static Map<String, List<GeneralOrderBean>> ordereXportCacheMap = new HashMap<>();
    /**
     * 分段获取generalOrderBeanList
     * @param key
     * @param start 包括
     * @param end 不包括
     * @return
     */
    public static List<GeneralOrderBean> getGeneralOrderBeanList(String key ,int start,int end) {
        List<GeneralOrderBean> generalOrderBeanList = null;
        if (ordereXportCacheMap.containsKey(key)) {
           generalOrderBeanList = ordereXportCacheMap.get(key);
            if (!CollectionUtils.isEmpty(generalOrderBeanList) && generalOrderBeanList.size() > start && end > start) {
                if (generalOrderBeanList.size() >= end) {
                    return generalOrderBeanList.subList(start, end);
                } else if (generalOrderBeanList.size() < end) {
                    return generalOrderBeanList.subList(start, generalOrderBeanList.size());
                }
            }
        }
        return null;
    }

    public static void delectListByKey(String key) {
        if (ordereXportCacheMap.containsKey(key)) {
            ordereXportCacheMap.remove(key);
        }
    }
}

总结：

暂时查询4万多条在5秒钟左右，效率还能接受，就没有用多线程去分多次请求，后期有需要优化的话再改进