多线程解决导出excel性能问题
第一步：Controller 发起导出数据请求
@RequestMapping(value = "/subpolicy/hdevpayback/exportOtherExcelAll.json")
public void exportOtherExcelAll(final HttpServletRequest request, final HttpServletResponse response,
String statDate, String uuId) {
if (!LockUtils.getLock("exportHardDevExcelAll", 180)) {
try {
response.setContentType("application/json;charset=UTF-8");
response.getWriter().write("其他用户正在导出，请稍后再试...");
response.getWriter().flush();
} catch (Exception e) {
ExceptionUtils.throwBusinessException(getClass(), "返回消息异常", e);
}
return;
}
try {
RedisUtil.setKey(uuId, "1", 120);
String file =
hardDevPayBackService.exportOtherHardExcelAll(statDate, Constants.EXPORT_MODE_LOCAL, request.getSession()
.getServletContext().getRealPath("/"));
FileUtils.exportFile(response, new File(file), "其他设备返款全量导出", "csv");
RecordLogManager.getInstance().commitOriginalLog(MyJedisCon.getRedisUser(request).getUserName(),
"HDEVPAYBACK_MENU", "exportAll", "设备返款全部导出");
RedisUtil.deleteKey(uuId);
} finally {
LockUtils.unlock("exportHardDevExcelAll");
}
}
第二步：计算总记录数,根据总记录数分配线程数和每个线程处理的记录数。
@Override
public String exportOtherHardExcelAll(String statDate, int mode, String filePath) {
String csvFilePath = filePath + "exportTmp/hardDevAll/";
if (StringUtils.isBlank(statDate)) {
statDate = DateUtil.getYesterday();
}
String[] fields = {"payState", "payDate", "payNum", "payMoney", "payMark", "isPayBack", "devNumber", "devType",
"equipNum", "devModel", "agentNumber", "agentName", "schoolAccount", "schoolName", "price", "payAgentName",
"payAgentNumber", "paySchoolName", "paySchoolAccount", "paySchoolNumber", "formalTime", "bindTime", "category"};
String[] head = {"返款状态", "返款时间", "返款数量", "返款金额", "说明", "可否返款", "设备编号", "设备类型", "数量", "设备型号", "代理商编号", "代理商名称",
"幼儿园账号", "幼儿园名称", "价格", "返款代理商", "返款代理商编号", "返款幼儿园", "返款幼儿园账号", "返款幼儿园id", "幼儿园转正时间", "绑定时间", "幼儿园类别"};
int count = countOtherExcelAll(statDate);//计算总记录数
int[] indexs = ThreadUtils.getIndex(count, 10000, 5);//根据总记录数分配线程数和每个线程处理的记录数
CountDownLatch latch = new CountDownLatch(indexs.length - 1);
for (int j = 1; j < indexs.length; j++) {
taskExecutor.execute(new HardDevExportThread(latch, Constants.THREAD_TYPE_OTHER_HARD_DEV, j, csvFilePath,
statDate, indexs[j - 1], indexs[j] - indexs[j - 1], fields));
}
String exportFilePath = getOneCsv(latch, csvFilePath, head, indexs.length);
return exportFilePath;
}
附：ThreadUtils.getIndex方法，最终结果 [0,10000,20000,30000,40000,50000]
public class ThreadUtils {
/**
* 返回每个线程的数据下标始末，限制最大线程数
* @param size 总数
* @param minSize 单个线程最小执行数量
* @param maxTask 最大线程数
* @return
*/
public static int[] getIndex(int size, int minSize, int maxTask) {
int listIndexCount;
double sizeDb = (double) size, minSizeDb = (double) minSize, maxTaskDb = (double) maxTask;
if (sizeDb / minSizeDb < maxTaskDb) {
listIndexCount = Double.valueOf(Math.ceil(sizeDb / minSizeDb)).intValue();
} else {
listIndexCount = maxTask;
}
int each = Double.valueOf(Math.floor(sizeDb / listIndexCount)).intValue();
int[] indexs = new int[listIndexCount + 1];
indexs[0] = 0;
int totalCount = 0;
for (int i = 1; i < listIndexCount; i++) {
indexs[i] = indexs[i - 1] + each;
totalCount += each;
}
// 最后一个线程可能多分担一点
indexs[listIndexCount] = size - totalCount + indexs[listIndexCount - 1];
return indexs;
}
}
第三步：每个任务处理的事情 取数和导出到excel文件（每个线程导出一个文件）
@Override
public void run() {
try {
int each = 10000;
int times = Double.valueOf(Math.floor(size / each)).intValue();
int totalCount = 0;
for (int i = 0; i < times; i++) {
int beforeCount = totalCount;
totalCount += each;
List list;
if (DEV_EXPORT_THREAD.equals(threadType)) {//硬件返款和其他硬件返款
list = hardDevPayBackService.getHardExcelAllByIndex(statDate, beforeCount + startIndex, each);//取数
}
else {
list = hardDevPayBackService.getOtherHardExcelAllByIndex(statDate, beforeCount + startIndex, each);
}
ExcelExport.exportCsvLocal(filePath, threadNum.toString(), list, null, fields, i == 0);
}
if (totalCount < size) {// 额外冗余each条的limit，以防count有漏掉
List list;
if (DEV_EXPORT_THREAD.equals(threadType)) {
list = hardDevPayBackService.getHardExcelAllByIndex(statDate, totalCount + startIndex, size - totalCount + each);
}
else {
list = hardDevPayBackService.getOtherHardExcelAllByIndex(statDate, totalCount + startIndex, size - totalCount + each);
}
ExcelExport.exportCsvLocal(filePath, threadNum.toString(), list, null, fields, totalCount == 0);//写入excel文件
}
} catch (Exception e) {
e.printStackTrace();
} finally {
countDownLatch.countDown();
}
}
第四步：把所有excel文件合并到一个文件
private String getOneCsv(CountDownLatch latch, String filePath, String[] head, int fileCount) {
BufferedReader reader = null;
BufferedWriter writer = null;
try {
latch.await();//等待所有线程都完成才执行。
File file = new File(filePath + "all.csv");
if (file.exists() && !file.isDirectory()) {
file.delete();
}
file.createNewFile();
writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(filePath + "all.csv", true), "GB2312"));
for (int i = 0; i < head.length; i++) {
writer.write("\"" + head[i] + "\"");
if (i < head.length - 1) {
writer.write(",");
}
}
writer.write("\r\n");
for (int i = 1; i < fileCount; i++) {
reader = new BufferedReader(new InputStreamReader(new FileInputStream(filePath + i + ".csv"), "GB2312"));
char[] buffer = new char[1024];
int len = 0;
while ((len = reader.read(buffer)) > 0) {
writer.write(buffer, 0, len);
}
reader.close();
writer.flush();
}
} catch (Exception e) {
e.printStackTrace();
return null;
} finally {
try {
if (reader != null) {
reader.close();
}
writer.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return filePath + "all.csv";
}