支持单组数据(List<T>)异步导出,比如1000条数据,可以按100条数据一个sheet的形式,去异步写入10个sheet中,每个sheet有100条数据
createPageExcel方法是通过HttpServletResponse直接响应给前端
createPageExcelUrl是生成一个File,然后上传到OSS,获取到Url返回给前端,此方法中有redis和oss上传、自定义异常处理的类需要自行修改
import cn.hutool.core.thread.NamedThreadFactory; 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> { @Resource private FileInfoFeignClient fileInfoFeignClient; @Resource private RedisService redisService; /** * 根据单组数据异步创建分页sheet * Author: wjce * Date: 2024/5/11 11:21 * @params [response, excelSheet, dataList, fileName] ExcelSheet实现类,单组数据,文件名 */ public void createPageExcel(HttpServletResponse response, Class<ExcelSheet> excelSheet, List<T> dataList, 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, dataList, 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(); } } /** * * Author: wjce * Date: 2024/5/11 16:59 * @params [excelSheet, dataList, fileName, batchSize, key, verify, delete] * ExcelSheet实现类,单组数据,文件名,每个sheet页行数,存入redis的key, 校验同时导出, 导出后是否删除缓存中的url->非定时任务调用设置true */ public String createPageExcelUrl(Class<ExcelSheet> excelSheet, List<T> dataList, 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<>(); try{ long start = System.currentTimeMillis(); File file = new File(fileName); if (!file.exists()) { file.createNewFile(); } FileOutputStream out = new FileOutputStream(file); 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); }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); } }
标签:style,sheet,String,int,导出,org,excel,单组,import From: https://www.cnblogs.com/gqymy/p/18186967