由于业务要求使用easyexcel无对象导出的方式，通过sql获取list<string,object>数据，同时列名还需要用字段名转成中文，导致for循环比较多，因为单线程执行，数据量又是几千条，每一行的数据又是七八百列，导致效率非常慢
通过拆分list，将list拆成多段后，使用线程池，每个线程执行一个任务，最后把结果合并起来
问题比较多，也没对异常做很好的处理
java
public void exportSqlScriptExcuteResultExcel(HttpServletResponse response, RecSearchSolutionSqlScriptExcuteResultRequest request) {
request.setType(1);
IPage<Map<String, Object>> iPage = this.getSqlScriptExcuteResult(request);
List<Map<String,Object>> dataMaplist= iPage.getRecords();
//查询当前方案的已选字段
RecSearchSolutionFieldRequest fieldRequest=new RecSearchSolutionFieldRequest();
fieldRequest.setSolutionId(request.getSolutionId());
List<RecSearchSolutionFieldEntity> recSearchSolutionFieldList=iRecSearchSolutionFieldService.getRecSearchSolutionFieldMapByIsDisplay(fieldRequest);
Map<String, String> recSearchSolutionFieldMap = recSearchSolutionFieldList.stream().collect(Collectors.toMap(arr -> arr.getField(), arr -> arr.getText(), (v1, v2) -> null, LinkedHashMap::new));
recSearchSolutionFieldMap.values().removeIf(Objects::isNull);
//列名的集合
List<List<String>> headList = new ArrayList<>();
//每行数据的集合
List<List<Object>> dataList = new ArrayList<>();
//是否为首次 0为首次
int i = 0;
try {
for (Map<String, Object> map : dataMaplist) {
List<Object> data = new ArrayList<>();
List<String>[] head = new List[]{null};
for (Map.Entry<String, Object> m : map.entrySet()) {
if (recSearchSolutionFieldMap!=null){
if (!recSearchSolutionFieldMap.containsKey(m.getKey())){
continue;
}
}
if (m.getValue() instanceof Date){
data.add(DateUtil.format((Date) m.getValue(),"yyyy-MM-dd HH:ss:mm"));
}else {
data.add(m.getValue());
}
//首次遍历就进入
if (i == 0) {
int headNum = 0;
head[headNum] = new ArrayList<>();
String text=recSearchSolutionFieldMap.get(m.getKey());
if(StrUtil.isBlank(text)){
head[headNum].add(m.getKey());
}else{
head[headNum].add(text);
}
headList.add(head[headNum]);
headNum++;
}
}
i++;
dataList.add(data);
}
String nowDate = DateUtil.format(new Date(),"yyyy_MM_dd_HH_mm_ss");
String fileName = "综合查询导出_" + nowDate;
response.setCharacterEncoding("utf-8");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "utf-8"));
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short)10);
headWriteCellStyle.setWriteFont(headWriteFont);
HorizontalCellStyleStrategy horizontalCellStyleStrategy=new HorizontalCellStyleStrategy();
horizontalCellStyleStrategy.setHeadWriteCellStyle(headWriteCellStyle);
EasyExcel.write(response.getOutputStream()).registerWriteHandler(horizontalCellStyleStrategy).head(headList).autoCloseStream(Boolean.FALSE).sheet("sheet").doWrite(dataList);
} catch (IOException e) {
throw new BusinessException(ResultCodeEnum.ERR_0x1000);
}
}
先生成head,不用每次都操作判断i，增加对空结果的操作以及生成excel失败的操作、并且超时情况的处理，把复用代码抽取出来，核心逻辑方便操作。
java
public void exportSqlScriptExcuteResultExcel(HttpServletResponse response, RecSearchSolutionSqlScriptExcuteResultRequest request) throws IOException {
request.setType(1);
IPage<Map<String, Object>> iPage = this.getSqlScriptExcuteResult(request);
List<Map<String,Object>> dataMaplist= iPage.getRecords();
//每列的列名的集合
List<List<String>> headList = new ArrayList<>();
//每行数据的集合
List<List<Object>> dataList = new ArrayList<>();
//如果sql结果为空则返回空的excel
if (CollectionUtil.isEmpty(dataMaplist)){
this.generateExcel(response, headList, dataList);
}else {
//查询当前方案的已选字段
RecSearchSolutionFieldRequest fieldRequest = new RecSearchSolutionFieldRequest();
fieldRequest.setSolutionId(request.getSolutionId());
List<RecSearchSolutionFieldEntity> recSearchSolutionFieldList = iRecSearchSolutionFieldService.getRecSearchSolutionFieldMapByIsDisplay(fieldRequest);
Map<String, String> recSearchSolutionFieldMap = recSearchSolutionFieldList.stream().collect(Collectors.toMap(arr -> arr.getField(), arr -> arr.getText(), (v1, v2) -> null, LinkedHashMap::new));
recSearchSolutionFieldMap.values().removeIf(Objects::isNull);
//先处理把第一个list拿出来处理EexcelHead
Map<String, Object> headmap = dataMaplist.get(0);
headmap.entrySet().forEach(h -> {
List<String>[] head = new List[]{null};
int headNum = 0;
head[headNum] = new ArrayList<>();
String text=recSearchSolutionFieldMap.get(h.getKey());
if(StrUtil.isBlank(text)){
head[headNum].add(h.getKey());
}else{
head[headNum].add(text);
}
headList.add(head[headNum]);
headNum++;
});
try {
// 创建一个有两个线程的线程池
ExecutorService executor = Executors.newFixedThreadPool(2);
// 将dataMaplist分成两个子列表
int mid = dataMaplist.size() / 2;
List<Map<String, Object>> sublist1 = dataMaplist.subList(0, mid);
List<Map<String, Object>> sublist2 = dataMaplist.subList(mid, dataMaplist.size());
// 提交第一个任务给线程池
executor.execute(() -> {
// 遍历第一个子列表
for (Map<String, Object> map : sublist1) {
this.processMap(map, dataList);
}
});
// 提交第二个任务给线程池
executor.execute(() -> {
// 遍历第二个子列表
for (Map<String, Object> map : sublist2) {
this.processMap(map, dataList);
}
});
// 关闭线程池
executor.shutdown();
// 等待所有任务完成，如果超出30分钟就抛出异常
executor.awaitTermination(30, TimeUnit.MINUTES);
} catch (InterruptedException e) {
e.printStackTrace();
// 重置response
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
Map<String, String> map = MapUtils.newHashMap();
map.put("status", "failure");
map.put("message", "下载文件失败" + e.getMessage());
response.getWriter().println(JSON.toJSONString(map));
}
//生成excel文件
this.generateExcel(response, headList, dataList);
}
}
private void generateExcel(HttpServletResponse response,List<List<String>> headList, List<List<Object>> dataList) throws IOException {
try {
String nowDate = DateUtil.format(new Date(), "yyyy_MM_dd_HH_mm_ss");
String fileName = "综合查询导出_" + nowDate;
response.setCharacterEncoding("utf-8");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "utf-8"));
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 10);
headWriteCellStyle.setWriteFont(headWriteFont);
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy();
horizontalCellStyleStrategy.setHeadWriteCellStyle(headWriteCellStyle);
EasyExcel.write(response.getOutputStream()).registerWriteHandler(horizontalCellStyleStrategy).head(headList).autoCloseStream(Boolean.FALSE).sheet("sheet").doWrite(dataList);
} catch (IOException e) {
e.printStackTrace();
// 重置response
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
Map<String, String> map = MapUtils.newHashMap();
map.put("status", "failure");
map.put("message", "下载文件失败" + e.getMessage());
response.getWriter().println(JSON.toJSONString(map));
}
}
private void processMap(Map<String, Object> map, List<List<Object>> dataList) {
List<Object> data = new ArrayList<>();
map.entrySet().forEach(m-> {
data.add(m.getValue());
});
dataList.add(data);
}
目前感觉还不是太好还有进一步优化空间，比如说通过Runtime.getRuntime().availableProcessors()方法获取当前可用线程数，动态的增加每次同时执行任务的数量，进一步加快速度,改用Future或者CompletableFuture ，查询视图优化，子查询太多，为子查询用到的字段添加索引。
本文作者:Weee
本文链接:
版权声明:本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！