首页 > 其他分享 >excel异步导出-通用-仅限单组数据导出

excel异步导出-通用-仅限单组数据导出

时间:2024-05-11 18:10:58浏览次数:23  
标签:style sheet String int 导出 org excel 单组 import

 

支持单组数据(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

相关文章

  • 怎么把excel里已经变成科学记数法的数字恢复
    从数据库导出的数据经常有id字段,包含一长串的数字,导出为excel或者csv格式后打开,id字段经常被转换成科学技术法了,不方便查看。如果想把它转变为通常的显示方法,在单元格上点击鼠标右键,点击“设置单元格格式”。在打开的窗口中,切换到“数字”选项卡,点击“自定义”,然后在右侧“类型......
  • js之模块导入与导出:export、export default、module.exports、exports
    前两者export、exportdefault可为一组,是es6的规范,和import匹配,import是es6中的语法标准;后两者module.exports、exports可为一组,是commonjs的规范,和require匹配,require是amd规范下的引入方式。当组合不适配时,当然就会报错了。module.exports、exports1,从使用方式来看,exports是mo......
  • sqlserver 导出数据到另外一个数据库
    选择数据源选择目标复制数据选择需要复制的表正在复制报错:无法在只读列id插入数据解决:id设置了自增,因此无法直接通过设置id列的数据,选择需要复制的数据列编辑映射忽略id列再次复制数据......
  • 界面组件DevExpress Reporting中文教程 - 标记(可访问)PDF导出增强
    DevExpressReporting是.NETFramework下功能完善的报表平台,它附带了易于使用的VisualStudio报表设计器和丰富的报表控件集,包括数据透视表、图表,因此您可以构建无与伦比、信息清晰的报表。可访问性支持在DevExpress这里仍然是一个高优先级,在v23.2版本生命周期中,我们专注于控制级......
  • poi报表导出 复杂导出 指定合并列和对比重复列合并行动态导出
    导出代码:@OverridepublicvoidstatCheckAndCaptureOutPutExcel(Queryparams,HttpServletRequestreq,HttpServletResponseresp){//创建表格时间//2.定义变量值创建Excel文件StringheadString="学上考试成绩统计";//定义表格标题StringfileName=DateUtil.f......
  • mysql导入导出整个数据库
    要将整个MySQL数据库导入到另一个MySQL实例中,您可以使用mysqldump工具导出数据库,并使用mysql客户端导入它。以下是一般的步骤:1. 导出数据库使用mysqldump工具导出数据库到一个SQL文件。例如,如果您要导出名为mydatabase的数据库,可以这样做:mysqldump-u[username]-pmydatabas......
  • 命令方式通过wps表格打开特定excel文件
    1、通过wps表格桌面快捷方式--右键菜单--打开方式--文本编辑器,如下图其中Exec=/usr/bin/et%F表示wps表格的可执行文件是位于/usr/bin目录下的et,%F为程序默认打开时打开的excel文件 2、可以通过终端命令方式调用et打开特定文件终端命令:nohup/usr/bin/et/home/wqz/gzgld......
  • 01-Excel基础操作-学习笔记
    01选择性粘贴一般的复制粘贴,可以直接使用快捷键Ctrl+C复制,Ctrl+V粘贴。但是在工作场景中,这样简单的复制粘贴远远不能满足需要。因此,学习了这个选择性粘贴工具。选择性粘贴工具在哪里?选中单元格区域——复制——鼠标右击——选择性粘贴应用场景一:要求保留原有格式,不......
  • 前端导出简单的Excel
    //报表导出exportProjectCount:asyncfunction(){letthat=this;awaitthat.getProjectCount().then(()=>{console.log("日志输出",that.dataCount.length)letdataLi......
  • xlwings:在Excel中集成Python
    Github地址:https://github.com/xlwings/xlwings在现代数据分析和报表生成中,MicrosoftExcel仍然是一款非常流行和强大的工具。与此同时,Python作为一种高级编程语言,具备出色的数据处理和分析能力。那么,有没有一种方法可以将Excel和Python完美结合,以发挥它们各自的优势?答案是肯定的......