Java excel大批量图片数据导出解决内存溢出问题

如果使用poi直接将所有图片一张张写到excel里，最后excel里图片达到10000张之后，内存就快爆掉了，所以，使用该方法将图片写到磁盘里，最后一次性替换掉，目前没有找到更好的方法，暂时使用该方法试试看

一共有六个步骤

思路是：excel文件本身也跟zip压缩文件类似，我就将其该名为zip文件，再将其解压，找到压缩文件固定存储路径，将原图替换占位图
1.修改名称
2.解压文件
3.图片替换
4.文件压缩
5.修改名称
6.删除缓存文件

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.nio.charset.Charset;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.zip.ZipEntry;
import java.util.zip.ZipInputStream;
import java.util.zip.ZipOutputStream;

public class ExcelFileUtils {
    private byte[] buf = new byte[1024];
    private Logger logger =  LoggerFactory.getLogger(ExcelFileUtils.class);


    public static void main(String[] args) throws Exception {
        ExcelFileUtils excelTest = new ExcelFileUtils();
        String out = excelTest.excelImageReplace("D:\\workPlace\\java\\BeiShi\\测试Excel\\测试案例\\1673838522783.xlsx","D:\\workPlace\\java\\BeiShi\\测试Excel\\测试案例\\image");
        System.out.println(out);
    }

    public String excelImageReplace(String originFile,String imageDirecytory) throws IOException {
        ExcelFileUtils excelTest = new ExcelFileUtils();
        String localPath = originFile.substring(0,originFile.lastIndexOf(File.separator)+1);
        //1.修改名称
        String reName = originFile.substring(0,originFile.lastIndexOf(".")+1)+"zip";
        excelTest.rename(originFile,reName);
        //2.解压文件
        //文件解压路径
        String zipFile = localPath + new Date().getTime()+File.separator;
        excelTest.unzipFile(reName,zipFile);
        //3.图片替换
        String imageFileDirectory = zipFile+"xl"+File.separator+"media"+File.separator;
        File imageFile = new File(imageFileDirectory);
        if (!(imageFile.exists()&&imageFile.isDirectory())){
            logger.error("文件解压异常，excel重新编辑失败");
        }
        //清空原文件夹内图片
        List<String> renameList = new ArrayList<>();
        for (File file : imageFile.listFiles()) {
            file.delete();
            renameList.add(file.getAbsolutePath());
        }
        //将图片移动到该文件夹内
        File imageDirecytoryFile = new File(imageDirecytory);
        List<File> imageList = Arrays.asList(imageDirecytoryFile.listFiles());
        for (int i = 0; i < renameList.size(); i++) {
            String rename = renameList.get(i);
            File image = imageList.get(i);
            image.renameTo(new File(rename));

        }
        //4.文件压缩
        String zipOutName = localPath+ new Date().getTime()+".zip";
        File zipDir = new File(zipFile);
        excelTest.toZip(zipOutName,
                Arrays.asList(zipDir.listFiles()));

        //5.修改名称
        String xlsxOutName = zipOutName.substring(0,zipOutName.lastIndexOf(".")+1)+"xlsx";
        excelTest.rename(zipOutName,xlsxOutName);
        //6.删除缓存文件
        excelTest.deleteFolder(imageDirecytory);
        excelTest.deleteFolder(zipFile);
        new File(reName).delete();
        zipDir.delete();
        return xlsxOutName;

    }

    //删除文件夹
    public void deleteFolder(String fileName){
        File file = new File(fileName);
        if (!file.isDirectory()){
            file.delete();
        }else {
            for (File listFile : file.listFiles()) {
                deleteFolder(listFile.getAbsolutePath());
            }
            file.delete();
        }

    }
    public Boolean rename(String path/*原文件路径+文件名*/, String newname/*要修改成的文件名*/) throws IOException {
        File oldFile = new File(path);
        if (!oldFile.exists()) {
            return false;//如果原文件不存在,返回no
        }
        File newFile = new File(newname);
        if (oldFile.renameTo(newFile)) {
            return true;
        } else {
            return false;
        }
    }



    /**
     * 解压zip压缩文件到指定目录
     *
     * @param zipPath zip压缩文件绝对路径
     * @param descDir 指定的解压目录
     */
    public void unzipFile(String zipPath, String descDir) throws IOException {
        try {
            File zipFile = new File(zipPath);
            if (!zipFile.exists()) {
                throw new IOException("要解压的压缩文件不存在");
            }
            File pathFile = new File(descDir);
            if (!pathFile.exists()) {
                pathFile.mkdirs();
            }
            InputStream input = new FileInputStream(zipPath);
            unzipWithStream(input, descDir);
        } catch (Exception e) {
            throw new IOException(e);
        }
    }

    /**
     * 解压
     *
     * @param inputStream
     * @param descDir
     */
    public  void unzipWithStream(InputStream inputStream, String descDir) {
        if (!descDir.endsWith(File.separator)) {
            descDir = descDir + File.separator;
        }
        try (ZipInputStream zipInputStream = new ZipInputStream(inputStream, Charset.forName("GBK"))) {
            ZipEntry zipEntry;
            while ((zipEntry = zipInputStream.getNextEntry()) != null) {
                String zipEntryNameStr = zipEntry.getName();
                String outPath = (descDir + zipEntryNameStr).replace("\\", "/");
                File outFile = new File(outPath.substring(0, outPath.lastIndexOf('/')));
                if (!outFile.exists()) {
                    outFile.mkdirs();
                }
                if (new File(outPath).isDirectory()) {
                    continue;
                }
                writeFile(outPath, zipInputStream);
                zipInputStream.closeEntry();
            }
            System.out.println("======解压成功=======");
        } catch (IOException e) {
            System.out.println("压缩包处理异常，异常信息{}" + e);
        }
    }

    /**
     * 将流写到文件中
     * @param filePath
     * @param zipInputStream
     */
    public void writeFile(String filePath, ZipInputStream zipInputStream) {
        try (OutputStream outputStream = new FileOutputStream(filePath)) {
            byte[] bytes = new byte[4096];
            int len;
            while ((len = zipInputStream.read(bytes)) != -1) {
                outputStream.write(bytes, 0, len);
            }
        } catch (IOException ex) {
            System.out.println("解压文件时，写出到文件出错");
        }
    }


    /**
     * 压缩成ZIP 方法2  一次性压缩多个文件
     *
     * @param zipFileName 压缩文件输出
     * @param srcFiles 需要压缩的文件列表
     * @throws RuntimeException 压缩失败会抛出运行时异常
     */
    public void toZip(String zipFileName, List<File> srcFiles) {
        long start = System.currentTimeMillis();
        ZipOutputStream zos = null;
        try {
            FileOutputStream fileOutputStream = new FileOutputStream(zipFileName);
            zos = new ZipOutputStream(fileOutputStream);
            for (File srcFile : srcFiles) {
                compress(srcFile, zos, srcFile.getName(), true);
            }
            long end = System.currentTimeMillis();
            System.out.println("压缩完成，耗时：" + (end - start) + " 毫秒");
        } catch (Exception e) {
            throw new RuntimeException("zip error from ZipUtils", e);
        } finally {
            if (zos != null) {
                try {
                    zos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 递归压缩方法
     *
     * @param sourceFile       源文件
     * @param zos              zip输出流
     * @param name             压缩后的名称
     * @param KeepDirStructure 是否保留原来的目录结构,true:保留目录结构;
     *                         false:所有文件跑到压缩包根目录下(注意：不保留目录结构可能会出现同名文件,会压缩失败)
     * @throws Exception
     */
    public void compress(File sourceFile, ZipOutputStream zos, String name,
                                boolean KeepDirStructure) throws Exception {

        if (sourceFile.isFile()) {
            // 向zip输出流中添加一个zip实体，构造器中name为zip实体的文件的名字
            zos.putNextEntry(new ZipEntry(name));
            // copy文件到zip输出流中
            int len;
            FileInputStream in = new FileInputStream(sourceFile);
            while ((len = in.read(buf)) != -1) {
                zos.write(buf, 0, len);
            }
            // Complete the entry
            zos.closeEntry();
            in.close();
        } else {
            File[] listFiles = sourceFile.listFiles();
            if (listFiles == null || listFiles.length == 0) {
                // 需要保留原来的文件结构时,需要对空文件夹进行处理
                if (KeepDirStructure) {
                    // 空文件夹的处理
                    zos.putNextEntry(new ZipEntry(name + "/"));
                    // 没有文件，不需要文件的copy
                    zos.closeEntry();
                }
            } else {
                for (File file : listFiles) {
                    // 判断是否需要保留原来的文件结构
                    if (KeepDirStructure) {
                        // 注意：file.getName()前面需要带上父文件夹的名字加一斜杠,
                        // 不然最后压缩包中就不能保留原来的文件结构,即：所有文件都跑到压缩包根目录下了
                        compress(file, zos, name + "/" + file.getName(), KeepDirStructure);
                    } else {
                        compress(file, zos, file.getName(), KeepDirStructure);
                    }

                }
            }
        }
    }
}