欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2022-07-13 13:15:19
...

场景

公司项目有个以前的程序猿写的订单导出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秒钟左右,效率还能接受,就没有用多线程去分多次请求,后期有需要优化的话再改进