场景
公司项目有个以前的程序猿写的订单导出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秒钟左右，效率还能接受，就没有用多线程去分多次请求，后期有需要优化的话再改进
494
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
