1.使用流输出excel文件
ServletOutputStream out = null;
HSSFWorkbook workbook = null;
InputStream importTemplate = this.getClass().getClassLoader().getResourceAsStream("templates/template.xls");
workbook = new HSSFWorkbook(importTemplate);
exportExcel(workbook);
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setCharacterEncoding("UTF-8");
String fileName = URLEncoder.encode("自动补填规则导入模板", "UTF8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
out = response.getOutputStream();
workbook.write(out);
out.flush();
if (null != out) {
out.close();
}
if (null != workbook) {
workbook.close();
}
2.构建文件单个标签页
private void exportExcel(HSSFWorkbook workbook) {
// 线程计数器,等待所有线程执行完成后才执行
final CountDownLatch countDownLatch = new CountDownLatch(1);
createAllSheet(workbook, countDownLatch);
try {
// 设置超时时间
countDownLatch.await(3, TimeUnit.SECONDS);
log.info("自动补填规则导入模板数据填充完成");
} catch (Exception e) {
e.printStackTrace();
}
}
3.异步生成数据
private void createAllSheet(HSSFWorkbook workbook, CountDownLatch countDownLatch) {
// 异步线程池
ServiceLocator.getExecutorTaskUtil().asyncExecutorTask(() -> {
create(workbook, countDownLatch);
return null;
});
}
4.线程池的构建
- 先注册一个线程池
@Configuration("com.config.ExecutorConfig")
@EnableAsync
@Slf4j
public class ExecutorConfig {
@Value("${async.executor.thread.core_pool_size:10}")
private int corePoolSize;
@Value("${async.executor.thread.max_pool_size:20}")
private int maxPoolSize;
@Value("${async.executor.thread.queue_capacity:500}")
private int queueCapacity;
@Value("${async.executor.thread.keep_alive_seconds:30}")
private int keepAliveSeconds;
@Value("${async.executor.thread.name.prefix:aims-export-template}")
private String namePrefix;
/**
* 在实现多个线程池时,可能会引发其他框架在获取TaskExecutor时,一旦未指定bean,就会报错。
* 向spring容器中注入一个默认的TaskExecutor,防止获取TaskExecutor报错。例如:
*/
@Primary
@Bean
@ConditionalOnMissingBean
public TaskExecutor taskExecutor() {
return new SimpleAsyncTaskExecutor();
}
@Qualifier("AsyncServiceExecutor")
@Bean(name = "AsyncServiceExecutor")
public Executor asyncServiceExecutor() {
ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
// 设置核心线程数
executor.setCorePoolSize(corePoolSize);
// 设置最大线程数
executor.setMaxPoolSize(maxPoolSize);
// 设置缓冲队列大小
executor.setQueueCapacity(queueCapacity);
// 设置线程的最大空闲时间
executor.setKeepAliveSeconds(keepAliveSeconds);
// 设置线程名字的前缀
executor.setThreadNamePrefix(namePrefix);
// 设置拒绝策略:当线程池达到最大线程数时,如何处理新任务
// CALLER_RUNS:在添加到线程池失败时会由主线程自己来执行这个任务
executor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy());
// 线程池初始化
log.info("开启spring boot线程池: [核心线程数:{},最大线程数:{},缓冲队列大小:{},最大空闲时间:{}秒]",
corePoolSize, maxPoolSize, queueCapacity, keepAliveSeconds);
executor.initialize();
return executor;
}
}
- 使用已注册的线程池
@Lazy
@Component("ExecutorTaskUtil")
public class ExecutorTaskUtil {
/**
* 异步执行无参有返回的任务
*
* @param supplier supplier
* @param <R> <R>
* @return null
*/
@Async("AsyncServiceExecutor")
public <R> R asyncExecutorTask(Supplier<R> supplier) {
return supplier.get();
}
}
5.写入页签
public static void doExportExcel(HSSFWorkbook workbook, int sheetIndex, List<String[]> sheetRows, int startRowIndex) {
// 获取第n个sheet
HSSFSheet sheet = workbook.getSheetAt(sheetIndex);
// 设置表格样式
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setWrapText(true);
// 如果数据不为空
if (CollectionUtil.isNotEmpty(sheetRows)) {
// 循环数据
for (int i = 0; i < sheetRows.size(); i++) {
// 设置开始行
int rowIndex = i + startRowIndex;
// 创建开始行
HSSFRow row = sheet.createRow(rowIndex);
// 获取单行的数据
String[] columns = sheetRows.get(i);
for (int cellIndex = 0; cellIndex < columns.length; cellIndex++){
// 建一个单元格对象
HSSFCell cell = row.createCell(cellIndex);
// 设置单元格样式
cell.setCellStyle(cellStyle);
// 设置单元格数据
cell.setCellValue(columns[cellIndex]);
}
}
}
}
6.技术总结
这里使用到了 HSSFWorkbook
进行构建文件,以及使用 CountDownLatch
进行异步等待,然后通过使用构建好的线程池 ThreadPoolTaskExecutor
来进行执行构建任务。
- HSSFWorkbook:读取xls文件,也就是老版本的97-2003版本的excel,如果读取新版(高于2003)的就使用XSSFWorkbook。
- CountDownLatch:一个线程(或者多个), 等待另外N个线程完成某个事情之后才能执行。
- ThreadPoolTaskExecutor:Spring默认自带的线程池。