pom dependency
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.3.2</version>
</dependency>
Controller layer
//The method return value is void when exported
@PostMapping("/exportQAData")
public void exportQAData(String startDate, String endDate, HttpServletResponse response) {<!-- -->
qADataManageService.exportQAData(response, startDate,endDate);
}
Service layer
@Component
@Slf4j
public class QADataManageService implements FileExportService<QADataManageVo> {<!-- -->
/**
* Declare and obtain dao layer instance
*/
@Autowired
private QADataManageDao qADataManageDao;
@Autowired
private ExcelExportUtil excelExportUtil;
public Boolean exportQAData(HttpServletResponse response, String startDate,String endDate) {<!-- -->
Map<String, Object> maps = new HashMap<>();
maps.put(QADataManageConstant.CUSTOMER_TIME, startDate);
maps.put(QADataManageConstant.FORECAST_TIME, endDate);
Integer qaDataTotal = qADataManageDao.getQADataTotal(maps);
if (qaDataTotal > 0) {<!-- -->
excelExportUtil.multiThreadExportExcel(response, qaDataTotal, QADataManageVo.class, this, maps, null);
return true;
}
return false;
}
@Override
public List<QADataManageVo> queryPageExcel(Map<String, Object> map) {<!-- -->
Integer pageNum = (Integer) map.get("page");
Integer pageSize = (Integer) map.get("pageSize");
List<QADataManagePo> qaDataManagePos = qADataManageDao.findAllList(pageNum, pageSize, map);
return buildVoItem(qaDataManagePos);
}
}
Strategy Factory
- Interface level
public interface FileExportService<R> {<!-- -->
/**
* Description Universal paging interface
* @date 2023/11/2 11:12
* @param: map contains page size and current page data
*/
List<R> queryPageExcel(Map<String,Object> map);
}
- factory
public class FileExportFactory<R> {<!-- -->
private FileExportService<R> fileExportService;
public FileExportFactory(FileExportService<R> fileExportService){<!-- -->
this.fileExportService = fileExportService;
}
public List<R> queryPageExcel(Map<String,Object> map){<!-- -->
return fileExportService.queryPageExcel(map);
}
}
Tools
@Component
public class ExcelExportUtil {<!-- -->
@Autowired
@Qualifier("excelThreadPool")
private ThreadPoolTaskExecutor threadPoolTaskExecutor;
private static final Logger logger = LoggerFactory.getLogger(ExcelExportUtil.class);
/**
* @param response response
* @param totalCount total number of records
* @param clazz exported Excel object
* @throwsException
*/
public <R> void exportExcel(HttpServletResponse response, int totalCount, Class<?> clazz, FileExportService<R> service, String fileName) throws Exception {<!-- -->
//file name
fileName = StrUtil.isNotEmpty(fileName) ? fileName : String.valueOf(System.currentTimeMillis());
FileExportFactory<R> context = new FileExportFactory<>(service);
OutputStream outputStream = null;
try {<!-- -->
//Each Sheet stores 5000 pieces of data
int sheetDataRows = 5000;
//The amount of data written each time is 3000, and each page is queried 3000
Integer writeDataRows = 3000;
//Calculate the number of Sheets required
int sheetNum = totalCount % sheetDataRows == 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows + 1);
//Calculate the number of times each Sheet needs to be written under normal circumstances (generally, the last sheet is not included, because the last sheet is not sure how many pieces of data will be written)
int oneSheetWriteCount = sheetDataRows / writeDataRows;
//Calculate the number of times the last sheet needs to be written
int lastSheetWriteCount = totalCount % sheetDataRows == 0 ? oneSheetWriteCount : (totalCount % sheetDataRows % writeDataRows == 0 ? (totalCount / sheetDataRows / writeDataRows) : (totalCount / sheetDataRows / writeDataRows + 1));
// Get the output stream
outputStream = response.getOutputStream();
//Must be placed outside the loop, otherwise the stream will be refreshed
ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
//Cache written data
List<R> dataList = new ArrayList<>();
Map<String, Object> queryMap = new ConcurrentHashMap<>();
//Start batch query and write sheetNum in batches
for (int i = 0; i < sheetNum; i + + ) {<!-- -->
//Create Sheet
WriteSheet sheet = new WriteSheet();
sheet.setSheetName("Sheet" + i);
sheet.setSheetNo(i);
//Number of loop writes: The auto-increment condition of j is that when it is not the last Sheet, the number of writes is the normal number of writes for each Sheet. If it is the last one, the calculated number of lastSheetWriteCount needs to be used.
for (int j = 0; j < (i != sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j + + ) {<!-- -->
// Clear the collection after each data write
dataList.clear();
//Get data from database and get data in paging
queryMap.put("page", i * sheetDataRows + j * writeDataRows);
queryMap.put("pageSize", writeDataRows);
dataList = context.queryPageExcel(queryMap);
//Save data in separate sheets
WriteSheet writeSheet = EasyExcel.writerSheet(i, "Sheet" + (i + 1)).head(clazz)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
excelWriter.write(dataList, writeSheet);
}
}
// Download EXCEL and return to the front-end stream
response.setContentType("application/octet-stream");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
excelWriter.finish();
outputStream.flush();
} catch (Exception e) {<!-- -->
logger.info("excel export exception", e);
throw new ServiceException("excel export exception");
} finally {<!-- -->
if (outputStream != null) {<!-- -->
outputStream.close();
}
}
}
/**
* @param totalCount total number of records
* @param clazz exported Excel object
* @param service The service class that specifically implements query data
* @param map query parameters
*/
public <R> void multiThreadExportExcel(HttpServletResponse response, int totalCount, Class<?> clazz, FileExportService<R> service, Map<String, Object> map, String fileName) {<!-- -->
try{<!-- -->
FileExportFactory<R> context = new FileExportFactory<>(service);
//file name
fileName = StrUtil.isNotEmpty(fileName) ? fileName : String.valueOf(System.currentTimeMillis());
OutputStream outputStream = null;
try {<!-- -->
//Each Sheet stores 1w pieces of data
int sheetDataRows = 100;
//The amount of data written each time is 5000, and each page is queried 5000
int writeDataRows = 50;
//Calculate the number of Sheets required
int sheetNum = totalCount % sheetDataRows == 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows + 1);
//Calculate the number of times each Sheet needs to be written under normal circumstances (generally, the last sheet is not included, because the last sheet is not sure how many pieces of data will be written)
int oneSheetWriteCount = sheetDataRows / writeDataRows;
//Calculate the number of times the last sheet needs to be written
int lastSheetWriteCount = totalCount % sheetDataRows == 0 ? oneSheetWriteCount : totalCount % sheetDataRows % writeDataRows == 0 ? totalCount % sheetDataRows / writeDataRows : (totalCount % sheetDataRows / writeDataRows) + 1;
outputStream = response.getOutputStream();
//Must be placed outside the loop, otherwise the stream will be refreshed
ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
Map<Integer, List<R>> pageMap = new ConcurrentHashMap<>(Math.toIntExact(sheetNum));
CountDownLatch countDownLatch = new CountDownLatch(Math.toIntExact(sheetNum));
//Multi-threaded query parameter Map
Map<Integer, Map<String, Object>> queryMap = new ConcurrentHashMap<>();
//Start batch query and write sheetNum in batches
for (int i = 0; i < sheetNum; i + + ) {<!-- -->
//Create Sheet
WriteSheet sheet = new WriteSheet();
sheet.setSheetName("Sheet" + i);
sheet.setSheetNo(i);
int finalNum = i;
threadPoolTaskExecutor.submit(() -> {<!-- -->
ConcurrentHashMap<String, List<List<String>>> dataListMap = new ConcurrentHashMap<>();
//The number of loop writes, the auto-increment condition of j is that when it is not the last Sheet, the number of writes is the normal number of writes for each Sheet. If it is the last one, the calculated number of lastSheetWriteCount needs to be used.
for (int j = 0; j < (finalNum != sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j + + ) {<!-- -->
int finalJ = j;
queryMap.put(finalNum, new HashMap<String, Object>() {<!-- -->
{<!-- -->
put("page", finalNum * sheetDataRows + finalJ * writeDataRows);
put("pageSize", writeDataRows);
if (CollectionUtil.isNotEmpty(map)) {<!-- -->
// Pass other query parameters
putAll(map);
}
}
});
// Strategy mode call query
List<R> dataList = pageMap.get(finalNum);
if (CollectionUtil.isEmpty(dataList)){<!-- -->
dataList = new ArrayList<>();
}
dataList.addAll(context.queryPageExcel(queryMap.get(finalNum)));
//Storage paging data
pageMap.put(finalNum, dataList);
}
countDownLatch.countDown();
});
}
try {<!-- -->
countDownLatch.await();
} catch (Exception e) {<!-- -->
logger.info("Multi-thread startup exception");
throw new ServiceException("Multi-thread startup exception");
}
pageMap.forEach((k, v) -> {<!-- -->
//Save data in separate sheets
WriteSheet writeSheet = EasyExcel.writerSheet(k, "Sheet" + (k + 1)).head(clazz)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
excelWriter.write(v, writeSheet);
pageMap.remove(k);
});
// Download EXCEL and return to the front-end stream
response.setContentType("application/octet-stream");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
excelWriter.finish();
outputStream.flush();
} catch (Exception e) {<!-- -->
logger.info("Data export exception",e);
} finally {<!-- -->
if (outputStream != null) {<!-- -->
outputStream.close();
}
}
} catch (Exception e) {<!-- -->
logger.info("Multi-threaded export data exception");
throw new ServiceException("Multi-threaded export data exception");
}
}
}