poi异步导出、excel异步导出-通用-仅限单组数据导出
支持单组数据（List<T>）异步导出，比如1000条数据，可以按100条数据一个sheet的形式，去异步写入10个sheet中，每个sheet有100条数据
createPageExcel方法是通过HttpServletResponse直接响应给前端
createPageExcelUrl是生成一个File，然后上传到OSS，获取到Url返回给前端，此方法中有redis和oss上传、自定义异常处理、根据枚举类型查询导出的excel数据的类需要自行修改
package com.yzf.costcontrol.core.excel;
import cn.hutool.core.thread.NamedThreadFactory;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.FileUtils;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.stereotype.Component;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.nio.file.Files;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
/**
* 创建excel
* @author wjce
* @date 2024/5/11 11:14
*/
@Component
@Slf4j
public class ExcelCreator<T extends ExcelBaseVO> {
@Resource
private FileInfoFeignClient fileInfoFeignClient;
@Resource
private RedisService redisService;
@Resource
private ExcelDataBuilder excelDataBuilder;
/**
* 根据单组数据异步创建分页sheet
* Author: wjce
* Date: 2024/5/11 11:21
* @params [response, excelSheet, dataList, fileName] ExcelSheet实现类，单组数据，文件名
*/
public void createPageExcel(HttpServletResponse response, Class<ExcelSheet> excelSheet, ExcelEnum excelEnum, JSONObject queryParam, String fileName, Integer batchSize){
SXSSFWorkbook wb = new SXSSFWorkbook();
wb.setCompressTempFiles(true);
List<CompletableFuture<Void>> futures = new ArrayList<>();
try{
long start = System.currentTimeMillis();
fileName = URLEncoder.encode(fileName, "UTF-8");
OutputStream out = response.getOutputStream();
addSheetData(excelSheet, excelDataBuilder.dataListBuild(excelEnum, queryParam), batchSize, wb, futures);
response.setCharacterEncoding("UTF-8");
response.setHeader("Content-disposition", "attachment;filename="+fileName+";"+"filename*=utf-8''"+fileName);
wb.write(out);
out.flush();
long end = System.currentTimeMillis();
log.info("excel写入sheet耗时：{}", end-start);
}catch (Exception e){
log.error("创建excel失败", e);
}finally {
wb.dispose();
}
}
/**
* 根据单组数据异步创建分页sheet并返回oss文件地址
* Author: wjce
* Date: 2024/5/11 16:59
* @params [excelSheet, queryParam, fileName, batchSize, key, verify, delete]
* ExcelSheet实现类，excelEnum excel数据查询枚举, excel数据查询参数，文件名，每个sheet页行数，存入redis的key, 校验同时导出, 导出后是否删除缓存中的url->非定时任务调用设置true
*/
public String createPageExcelUrl(Class<ExcelSheet> excelSheet, ExcelEnum excelEnum, JSONObject queryParam, String fileName, Integer batchSize, String key, boolean verify, boolean delete){
Object urlObj = RedisUtil.getValue(key);
if(urlObj != null){
return urlObj.toString();
}
String verifyKey = key + ":verify";
if(verify) {
verifyExportIsBeing(verifyKey);
}
SXSSFWorkbook wb = new SXSSFWorkbook();
wb.setCompressTempFiles(true);
String url = null;
List<CompletableFuture<Void>> futures = new ArrayList<>();
FileOutputStream out = null;
File file = new File(fileName);
try{
long start = System.currentTimeMillis();
if (!file.exists()) {
file.createNewFile();
}
out = new FileOutputStream(file);
List<T> dataList = excelDataBuilder.dataListBuild(excelEnum, queryParam);
addSheetData(excelSheet, dataList, batchSize, wb, futures);
wb.write(out);
out.close();
long end = System.currentTimeMillis();
log.info("excel写入sheet耗时：{}", end-start);
url = getExcelUrl(fileName);
redisService.setExportKey(key, url);
}catch (Exception e){
log.error("创建excel失败", e);
try {
out.close();
FileUtils.forceDelete(file);
}catch (Exception exception){}
}finally {
wb.dispose();
RedisUtil.delKey(verifyKey);
if(delete){
RedisUtil.delKey(key);
}
}
return url;
}
/**
* excel上传oss并返回链接
* Author: wjce
* Date: 2024/5/11 16:29
* @params [fileName]
*/
public String getExcelUrl(String fileName){
try {
File file = new File(fileName);
if(!file.exists()){
log.error("writeExcel：{}文件不存在", fileName);
return "";
}
InputStream inputStream = Files.newInputStream(file.toPath());
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
byte[] bytes = new byte[1024];
int len = 0;
while ( (len = inputStream.read(bytes)) != -1 ){
byteArrayOutputStream.write(bytes, 0, len);
}
inputStream.close();
String url = fileInfoFeignClient.uploadBytes(5, fileName, byteArrayOutputStream.toByteArray());
try {
FileUtils.forceDelete(file);
}catch (Exception e){
log.error("删除excel失败");
}
return url;
}catch (Exception e){
log.error("writeExcel：", e);
}
return null;
}
/**
* 校验excel是否正在导出，防止并行导出
* Author: wjce
* Date: 2024/5/11 16:29
* @params [verifyKey]
*/
public void verifyExportIsBeing(String verifyKey){
Object val = RedisUtil.getValue(verifyKey);
if(val != null){
throw new CostControlException(CostControlErrorCode.PROCESS_PROJECT_QUOTA_SYNC_EXPORT_REPEAT);
}
redisService.setExportKey(verifyKey, "1");
}
/**
* 添加sheet页数据
* Author: wjce
* Date: 2024/5/11 16:57
* @params [excelSheet, dataList, batchSize, wb, futures] ExcelSheet实现类，单组数据，每个sheet页行数
*/
private void addSheetData(Class<ExcelSheet> excelSheet, List<T> dataList, Integer batchSize, SXSSFWorkbook wb, List<CompletableFuture<Void>> futures) throws InstantiationException, IllegalAccessException, java.lang.reflect.InvocationTargetException, NoSuchMethodException {
batchSize = batchSize == null ? 10000 : batchSize;
int totalSize = dataList.size();
int batchCount = (totalSize + batchSize - 1) / batchSize;
ExecutorService pool = Executors.newFixedThreadPool(batchCount+1, new NamedThreadFactory("ExcelCreator", false));
for (int i = 0; i < batchCount; i++) {
int startIndex = i * batchSize;
int endIndex = Math.min(startIndex + batchSize, totalSize);
List<T> subList = dataList.subList(startIndex, endIndex);
SXSSFSheet dataSheet = wb.createSheet(String.format("第%s页", i + 1));
ExcelSheet sheet = excelSheet.getDeclaredConstructor().newInstance();
CompletableFuture<Void> future = CompletableFuture.runAsync(() -> {
sheet.set(wb, dataSheet, subList);
}, pool);
futures.add(future);
}
CompletableFuture<Void> allOf = CompletableFuture.allOf(futures.toArray(new CompletableFuture[0]));
allOf.join();
pool.shutdown();
}
}
ExcelSheet
import org.apache.commons.lang3.StringUtils; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.RegionUtil; import org.apache.poi.xssf.streaming.SXSSFCell; import org.apache.poi.xssf.streaming.SXSSFRow; import org.apache.poi.xssf.streaming.SXSSFSheet; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFClientAnchor; import org.apache.poi.xssf.usermodel.XSSFRichTextString; import java.util.Map; /** * @author wjc * @date 2021/7/21 9:43 */ public interface ExcelSheet { /** * @author wjc * @Description 设置excelSXSSFSheet内容 * @Date 2021/7/21 9:43 * @Param [workbook, SXSSFSheet] **/ int set(SXSSFWorkbook workbook, SXSSFSheet sheet, Object param); /** * @author wjc * @Description 设置合并后的单元格边框 * @Date 2021/7/23 10:54 * @Param [rangeAddress, SXSSFSheet] **/ default void setMergeBorder(CellRangeAddress rangeAddress, SXSSFSheet sheet, BorderStyle style){ RegionUtil.setBorderBottom(style, rangeAddress, sheet); RegionUtil.setBorderLeft(style, rangeAddress, sheet); RegionUtil.setBorderRight(style, rangeAddress, sheet); RegionUtil.setBorderTop(style, rangeAddress, sheet); } default int getRowNum(SXSSFSheet sheet, int rowNum, CellStyle xssfCellStyle, String[] rols) { return getRowNum(sheet, rowNum, xssfCellStyle, rols, null); } default int getRowNum(SXSSFSheet sheet, int rowNum, CellStyle xssfCellStyle, Integer commentIndex, String comment, String[] rols) { return getRowNum(sheet, rowNum, xssfCellStyle, commentIndex, comment, rols, null); } default int getRowNum(SXSSFSheet sheet, int rowNum, CellStyle xssfCellStyle, String[] rols, Short height) { return getRowNum(sheet, rowNum, xssfCellStyle, rols, height, null); } default int getRowNum(SXSSFSheet sheet, int rowNum, CellStyle xssfCellStyle, Integer commentIndex, String comment, String[] rols, Short height) { return getRowNum(sheet, rowNum, xssfCellStyle, commentIndex, comment, rols, height, null); } default int getRowNum(SXSSFSheet sheet, int rowNum, CellStyle xssfCellStyle, String[] rols, Short height, Map<Integer, XSSFCellStyle> otherStyle) { return getRowNum(sheet, rowNum, xssfCellStyle, null, null, rols, height, otherStyle); } default int getRowNum(SXSSFSheet sheet, int rowNum, CellStyle xssfCellStyle, Integer commentIndex, String comment, String[] rols, Short height, Map<Integer, XSSFCellStyle> otherStyle) { SXSSFRow r1 = sheet.createRow(rowNum++); if (height != null) { r1.setHeight(height); } for (int i = 0; i < rols.length; i++) { SXSSFCell tempXSSFCell = r1.createCell(i); tempXSSFCell.setCellStyle(xssfCellStyle); tempXSSFCell.setCellValue(rols[i]); if (StringUtils.isNotBlank(comment)) { addComment(tempXSSFCell, comment); } } if(otherStyle != null && !otherStyle.isEmpty()){ otherStyle.forEach((k,v) -> { SXSSFCell XSSFCell = r1.getCell(k); XSSFCell.setCellStyle(v); }); } return rowNum; } default CellStyle setHeadStyle(SXSSFWorkbook workbook){ CellStyle headerStyle = workbook.createCellStyle(); headerStyle.setAlignment(HorizontalAlignment.CENTER); headerStyle.setVerticalAlignment(VerticalAlignment.CENTER); headerStyle.setLocked(true); headerStyle.setWrapText(true); headerStyle.setBorderBottom(BorderStyle.THIN); headerStyle.setBorderLeft(BorderStyle.THIN); headerStyle.setBorderTop(BorderStyle.THIN); headerStyle.setBorderRight(BorderStyle.THIN); Font headerFont = workbook.createFont(); headerFont.setFontName("宋体"); headerFont.setFontHeightInPoints((short) 12); headerFont.setBold(true); headerFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex()); headerStyle.setFont(headerFont); //填充单元格 // headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // short color = HSSFColor.HSSFColorPredefined.PALE_BLUE.getIndex(); //设置单元格背景色 // headerStyle.setFillForegroundColor(color); return headerStyle; } /** * 设置某列单元格的自定义格式 * * @param sheet * @param startRowIndex 开始行 * @param endRowIndex 结束行 * @param columnIndex 列数 */ default void setCellDefinedFormat(SXSSFWorkbook workbook, SXSSFSheet sheet, int startRowIndex, int endRowIndex, int columnIndex, Short color) { CellStyle style = workbook.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); style.setLocked(true); style.setWrapText(false); style.setBorderBottom(BorderStyle.THIN); style.setBorderLeft(BorderStyle.THIN); style.setBorderTop(BorderStyle.THIN); style.setBorderRight(BorderStyle.THIN); if(color != null) { //填充单元格 style.setFillPattern(FillPatternType.SOLID_FOREGROUND); //设置单元格背景色 style.setFillForegroundColor(color); } for(int i = startRowIndex; i <= endRowIndex; i++) { Row row = sheet.getRow(i); if(row == null){ break; } Cell cell = row.getCell(columnIndex); cell.setCellStyle(style); } } /** * 给Cell添加批注 * * @param cell 单元格 * @param value 批注内容 */ default void addComment(Cell cell, String value) { Sheet sheet = cell.getSheet(); cell.removeCellComment(); ClientAnchor anchor = new XSSFClientAnchor(); // 关键修改 anchor.setDx1(0); anchor.setDx2(0); anchor.setDy1(0); anchor.setDy2(0); anchor.setCol1(cell.getColumnIndex()); anchor.setRow1(cell.getRowIndex()); anchor.setCol2(cell.getColumnIndex()+5); anchor.setRow2(cell.getRowIndex()+5); // 结束 Drawing drawing = sheet.createDrawingPatriarch(); Comment comment = drawing.createCellComment(anchor); // 输入批注信息 comment.setString(new XSSFRichTextString(value)); // 将批注添加到单元格对象中 cell.setCellComment(comment); } }
博客链接：https://www.cnblogs.com/gqymy/
