excel导出时数据量太大导致宕机
- 初步解决方式
-
分页查询
-
XSSFWorkbook 换成了SXSSFWorkbook
- 但是excel每一页最多一百多万数据,还是解决不了问题,最后选择导出csv文件
public static boolean exportCsv(File file, List<Map<String,Object>> list,int page) {
boolean isSucess = false;
if (!file.getParentFile().exists()) {
file.getParentFile().mkdirs();
}
if (!file.exists()) {
try {
file.createNewFile();
} catch (IOException e) {
throw new RuntimeException(e);
}
}
FileOutputStream out = null;
OutputStreamWriter osw = null;
BufferedWriter bw = null;
try {
out = new FileOutputStream(file,true);
osw = new OutputStreamWriter(out, "UTF-8");
bw = new BufferedWriter(osw,1024);
if(list.size()>0){
if(page==0) {
Map<String, Object> firstMap = list.get(0);
for (String key : firstMap.keySet()) {
bw.write("\""+key + "\",");
}
bw.write("\r\n");
}
for( Map<String, Object> dataMap: list ){
for (String key : dataMap.keySet()) {
bw.write(dataMap.get(key)==null?""+",":"\""+dataMap.get(key).toString().replaceAll("\"", "\"\"")+"\",");
}
bw.write("\r\n");
}
}
isSucess=true;
} catch (Exception e) {
log.error("生成csv文件失败",e);
e.printStackTrace();
isSucess = false;
} finally {
if (bw != null) {
try {
bw.close();
bw = null;
} catch (IOException e) {
e.printStackTrace();
}
}
if (osw != null) {
try {
osw.close();
osw = null;
} catch (IOException e) {
e.printStackTrace();
}
}
if (out != null) {
try {
out.close();
out = null;
} catch (IOException e) {
e.printStackTrace();
}
}
}
return isSucess;
}
csv文件导出时,需要注意以下两个字符
- 1、英文逗号表示换单元格,所以需要需要内容添加双引号
- 2、如果有双引号,需要将"替换为""
- 导出时可采用异步的方式进行,生成文件的过程中有异常或者out of memory可进行导出状态的回滚 oom采用catch error的方式进行
在这里插入代码片
@Async
@Override
public void exportDownloadFileAtLocal(String dm_id) {
try {
/*业务代码*/
} catch (Exception e) {
logger.error("捕获exception", e);
dealFailure(dm);
} catch (Error e) {
logger.error("捕获error", e);
dealFailure(dm);
}
}