不升级 POI 版本，如何生成符合新版标准的Excel 2007文件

青石路

发布于 2024-09-13 13:39:43

700

发布于 2024-09-13 13:39:43

文章被收录于专栏：开发技术

问题描述

项目基于 POI 4.1.2 生成 Excel 2007 文件，已经对接了很多客户，也稳定运行了好几年了；就在前两天，对接一个新的客户，生成的 Excel 2007 文件导入他们的系统失败，提示：

-700006004当前Excel表单列名中未查找到该列.

实话实说，这个提示对我而言，一毛钱作用没有，那就只能问他们系统的开发人员了；经过半天的排查，他们的开发人员给出的结论是：

你们的Excel 2007文件看着像是旧版的，不符合新版标准

这个回答让我更懵了，触及到我的知识盲区，都不直到如何接话了

Excel 2007 文件还有标准与非标准之分？这个问题我们先不纠结，本着优先解决问题的原则，试着去尝试升级下 POI 的版本

为什么第一时间想到的是升级 POI 版本？因为是用 POI 生成的 Excel 2007 文件嘛（貌似等于没说）

将 POI 版本升级到 5.3.0，代码不做任何调整，重新生成文件发送给客户，客户验证可以正常导入；你们是不是以为事情到此告一段落，升级 POI 版本就好了嘛，我只能说你们是有了新欢忘了旧爱，已经对接的客户怎么办？你敢保证升级 POI 后生成的 Excel 2007（2003 也会跟着受影响）还能正常导入这些客户的系统吗，所以我们的野心能不能更大一些：新欢旧爱都要！

既对已有客户不造成影响，又能满足新客户要求，也就引申出了本文标题

不升级 POI 版本，如何生成符合新版标准的Excel 2007文件

是个压缩包

从 Excel 2007 开始，Microsoft 采用了新的文件格式，称为开放的 XML 文件格式，很好地改进了文件和数据管理、数据恢复和可交互能力；而 Excel 2007 就是是一个包含 XML、图片等文件的压缩包；我们暂且先只关注 XML，先基于 POI 4.1.2

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>

String filePath = "D:/POI_4_1_2.xlsx";

public void createExcel(String filePath) throws Exception {
    try(SXSSFWorkbook wb = new SXSSFWorkbook();
        OutputStream os = Files.newOutputStream(Paths.get(filePath))) {
        SXSSFSheet sheetA = wb.createSheet("a");
        SXSSFSheet sheetB = wb.createSheet("b");
        SXSSFRow sheetA_row1 = sheetA.createRow(0);
        sheetA_row1.createCell(0).setCellValue("hello world");
        sheetA_row1.createCell(1).setCellValue("666");
        SXSSFRow sheetA_row2 = sheetA.createRow(1);
        sheetA_row2.createCell(0).setCellValue("888");
        sheetA_row2.createCell(1).setCellValue("999");
        SXSSFRow sheetB_row1 = sheetB.createRow(0);
        sheetB_row1.createCell(0).setCellValue("qsl");
        sheetB_row1.createCell(1).setCellValue("青石路");
        wb.write(os);
        os.flush();
    }
}

生成个旧版的 Excel 2007 文件：POI_4_1_2.xlsx，直接用 7z 进行提取（也可以直接将 POI_4_1_2.xlsx 重命名成 POI_4_1_2.zip，然后进行解压）

解压之后目录结构如下

所有的文件都是 XML；将 POI 升级到 5.3.0

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.3.0</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.3.0</version>
</dependency>

String filePath = "D:/POI_5_3_0.xlsx";

public void createExcel(String filePath) throws Exception {
    try(SXSSFWorkbook wb = new SXSSFWorkbook();
        OutputStream os = Files.newOutputStream(Paths.get(filePath))) {
        SXSSFSheet sheetA = wb.createSheet("a");
        SXSSFSheet sheetB = wb.createSheet("b");
        SXSSFRow sheetA_row1 = sheetA.createRow(0);
        sheetA_row1.createCell(0).setCellValue("hello world");
        sheetA_row1.createCell(1).setCellValue("666");
        SXSSFRow sheetA_row2 = sheetA.createRow(1);
        sheetA_row2.createCell(0).setCellValue("888");
        sheetA_row2.createCell(1).setCellValue("999");
        SXSSFRow sheetB_row1 = sheetB.createRow(0);
        sheetB_row1.createCell(0).setCellValue("qsl");
        sheetB_row1.createCell(1).setCellValue("青石路");
        wb.write(os);
        os.flush();
    }
}

解压 POI_5_3_0.xlsx，目录结构与 POI_4_1_2.xlsx 的解压目录结构一致，文件名与文件数量也一致

关于

Excel 2007 文件是个压缩包！

相信大家没疑问了吧；我们来对比下两个目录

虽然差异文件挺多，但可以归为两类

standalone 差异

_rels\.rels
docProps\core.xml
xl\_rels\workbook.xml.rels
[Content_Types].xml

这四个文件的差异是一样的（四个文件都是一行，我为了突显差异，将相同的换到了第二行）

POI 4.1.2 生成的 xml 中的 standalone 值是 no，而 POI 5.3.0 生成的 xml 中的 standalone 值是 yes，就这么一个区别

core.xml 中还有一个差异：

创建时间不同是正常的，这个差异可以忽略

dimension 差异

xl\worksheets 目录下存放的是 sheet 相关的 xml，但是名字是 sheet1 ~ sheetn，而不是我们代码中指定的 a、b，有多少个 sheet，对应就会有多少个 xml 文件，我们只需要看其中某个 xml 文件的差异即可，其他类似

就一处差异：POI 4.1.2 生成的 sheet 中是 <dimension ref="A1"/>，而 POI 5.3.0 中是 <dimension ref="A1:B2"/>

这么看来，Excel 2007 文件确实有标准与非标之分

回到问题

不升级 POI 版本，如何生成符合新版标准的Excel 2007文件

你们会如何处理？

要保证不影响已对接的客户（潜台词就是：既不能更换掉 POI，也不能升级 POI）的同时，还要能生成标准版的 Excel 2007文件来满足新客户，感觉没什么办法了呀，只能增加配置项：是否生成标准Excel 2007，默认值是：否，表示生成非标Excel 2007文件，保证已对接的客户不受影响，配置项值如果是：是，则生成标准Excel 2007文件；那么问题又来了

标准Excel 2007文件如何生成？

通过 POI 生成肯定是不行了，因为不能升级其版本，生成的是非标Excel 2007文件，那怎么办呢，我们可以换个组件嘛，条条大路通罗马，生成Excel 2007的组件肯定不只有 POI，换个组件来生成标准Excel 2007文件就好了嘛

其他组件

阿里的 EasyExcel ，你们肯定都知道吧，那就用它来生成标准Excel 2007文件，引入依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>4.0.2</version>
</dependency>

我们来看下它的依赖树

框住的部分，你们应该能看懂吧；EasyExcel 依赖 POI，但因为 POI 4.1.2 的优先级高于 EasyExcel 依赖的 5.2.5，所以最终依赖的还是 POI 4.1.2

关于 maven 的优先级可查看：结合实例看 maven 传递依赖与优先级，难顶也得上丫

此时你们是不是懵逼了？

显然用 EasyExcel 行不通；我还试了 jxl，发现也不行（解压后目录结构完全不一样），没有去试其他组件，因为我想到了一种感觉可行的方案

重打包

还记得前面的目录对比吗，差异文件分两类，standalone 差异固定是 4 个文件

_rels\.rels
docProps\core.xml
xl\_rels\workbook.xml.rels
[Content_Types].xml

dimension 差异固定为一类文件

xl\worksheets\sheet*.xml

除了这些差异文件，其他文件都是一致的，那么我们是不是可以这样处理

Excel 2007 文件还是基于 POI 4.1.2 生成，若配置项：是否生成标准Excel 2007 未配置或者配置的是 否，则文件生成结束（既有逻辑），如果配置项配置的是：是，则对生成好的 Excel 2007 进行以下处理

解压生成好的 Excel 2007 文件
对差异文件进行修改，将对应的差异项修改成标准值
重新打包成 Excel 2007 文件，并替换掉之前的旧 Excel 2007 文件

这样是不是就实现需求了？方案有了那就试呗

解压

就用 POI 依赖的 commons-compress 进行解压即可

/**
 * 对 Excel 2007 文件进行解压
 * @param sourceFile 源Excel 2007文件
 * @param unzipDir 解压目录
 * @throws IOException 解压异常
 * @author 青石路
 */
private void unzip(File sourceFile, String unzipDir) throws IOException {
    try (ZipFile zipFile = new ZipFile(sourceFile)) {
        // 遍历 ZIP 文件中的每个条目
        Enumeration<ZipArchiveEntry> entries = zipFile.getEntries();
        while(entries.hasMoreElements()) {
            ZipArchiveEntry entry = entries.nextElement();
            // 创建输出文件的路径
            Path outputPath = Paths.get(unzipDir, entry.getName());
            if (!Files.exists(outputPath.getParent())) {
                // 确保父目录存在
                Files.createDirectories(outputPath.getParent());
            }
            try (InputStream inputStream = zipFile.getInputStream(entry);
                 FileOutputStream outputStream = new FileOutputStream(outputPath.toFile())) {
                IOUtils.copy(inputStream, outputStream);
            }
        }
    }
}

修改

standalone 值修改

/**
 * 修改xml 的 standalone 属性值
 * @param filePath 包含 standalone 属性的xml文件
 * @throws IOException IO异常
 * @author 青石路
 */
private void updateXmlStandalone(Path filePath) throws IOException {
    Path bakPath = Paths.get(filePath.getParent().toString(), filePath.getFileName() + "_bak");
    try (BufferedReader reader = Files.newBufferedReader(filePath)) {
        String line = reader.readLine();
        String replace = line.replace("standalone=\"no\"", "standalone=\"yes\"");
        Files.write(bakPath, replace.getBytes(StandardCharsets.UTF_8));
    }
    Files.delete(filePath);
    Files.move(bakPath, filePath);
}

dimension 修改，首先我们需要弄清楚 ref 值的含义

<dimension ref="A1"/> // POI 4.1.2 <dimension ref="A1:B2"/> // POI 5.3.0

POI 4.1.2 中，ref 的值仅表示起始坐标，A表示X坐标值，1表示Y坐标值，而在 POI 5.3.0 中，ref 的值不仅有起始坐标，还包括结束坐标，A1 表示起始坐标，B2 表示结束坐标，这里的 2 表示数据行数

/**
 * 修改xml 的 dimension ref 属性值
 * @param sheetDir sheet xml所在目录
 * @throws IOException IO异常
 * @author 青石路
 */
private void updateSheetXmlDimension(Path sheetDir) throws IOException {
    // 修改第二行中的 <dimension ref="A1"/>
    try (Stream<Path> filePaths = Files.list(sheetDir)) {
        filePaths.forEach(filePath -> {
            // 先获取列数和行数，rows：数据行数，totalRows：内容总行数
            AtomicInteger columns = new AtomicInteger(0);
            AtomicInteger rows = new AtomicInteger(0);
            try (Stream<String> lines = Files.lines(filePath)) {
                lines.forEach(line -> {
                    if (line.endsWith("</row>")) {
                        rows.incrementAndGet();
                    }
                    if (rows.get() == 1 && line.endsWith("</row>")) {
                        columns.set(line.split("</c>").length - 1);
                    }
                });
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
            // Excel 列坐标 A ~ Z，AA ~ ZZ，...
            int circleTimes = columns.get() % 26 == 0 ? (columns.get() / 26 - 1) : (columns.get() / 26);
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < circleTimes; i++) {
                sb.append("A");
            }
            sb.append((char) ('A' + (columns.get() % 26 == 0 ? 25 : (columns.get() % 26 - 1))));
            // <dimension ref="A1:B2"/>
            String objStr = "<dimension ref=\"A1:" + sb + rows.get();
            try {
                Path bakPath = Paths.get(filePath.getParent().toString(), filePath.getFileName() + "_bak");
                Files.createFile(bakPath);
                try (Stream<String> lines = Files.lines(filePath)) {
                    lines.forEach(line -> {
                        try {
                            if (line.contains("<dimension ref=\"A1")) {
                                line = line.replace("<dimension ref=\"A1", objStr);
                            }
                            if (!line.endsWith("</worksheet>")) {
                                line = line + "\n";
                            }
                            Files.write(bakPath, line.getBytes(StandardCharsets.UTF_8), StandardOpenOption.APPEND);
                        } catch (IOException e) {
                            throw new RuntimeException(e);
                        }
                    });
                }
                Files.delete(filePath);
                Files.move(bakPath, filePath);
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
        });
    };
}

这个代码稍微复杂一点，但可以归纳为以下几步

遍历 sheet xml文件的内容，得到列数和行数
根据列数去推算出最大列坐标（B），再根据行数（2）得到结束坐标（B2），那么 ref 的值也就是：A1:B2 这里有个小坑，当数据只有一行一列时，新版的 ref 的值与旧版的 ref 值一致，都是 A1，但上述代码得到却是 A1:A1，所以还需要兼容调整下，至于如何调整，就交给你们了，我这里只是提示你们要注意这个坑！！！
进行 sheet xml 数据拷贝，并用 <dimension ref=\"A1:B2 替换掉 <dimension ref=\"A1，最后用新的 sheet xml 文件替换旧的

打包

需要修改的 xml 文件都修改完成之后重新进行打包，这里继续用 commons-compress

/**
 * 重新打包成 xlsx
 * @param basePath 解压根目录（[Content_Types].xml所在目录）
 * @param oriFile 源Excel 2007文件
 * @throws IOException
 * @author 青石路
 */
private void repackage(String basePath, File oriFile) throws IOException {
    File newFile = new File(basePath + ".xlsx");
    try (FileOutputStream fos = new FileOutputStream(newFile);
         ZipArchiveOutputStream zaos = new ZipArchiveOutputStream(fos)) {
        // 获取源文件夹下的所有文件和子文件夹
        File srcDir = new File(basePath);
        for (File f : Objects.requireNonNull(srcDir.listFiles())) {
            addToZip(f, "", zaos);
        }
    }
    // 用新文件覆盖原文件
    Path oriPath = oriFile.toPath();
    Files.delete(oriPath);
    Files.move(newFile.toPath(), oriPath);
}

private void addToZip(File file, String parentFolder, ZipArchiveOutputStream zaos) throws IOException {
    if (file.isDirectory()) {
        // 如果是目录，则遍历其中的文件并递归调用 addToZip
        for (File childFile : Objects.requireNonNull(file.listFiles())) {
            addToZip(childFile, parentFolder + file.getName() + "/", zaos);
        }
    } else {
        // 如果是文件，则将其添加到 ZIP 文件中
        try (FileInputStream fis = new FileInputStream(file)) {
            // 创建一个不带第一层目录的 ZipArchiveEntry
            String entryName = parentFolder + file.getName();
            if (entryName.startsWith("/")) {
                entryName = entryName.substring(1);
            }
            ZipArchiveEntry entry = new ZipArchiveEntry(entryName);
            zaos.putArchiveEntry(entry);
            IOUtils.copy(fis, zaos);
            zaos.closeArchiveEntry();
        }
    }
}

没什么复杂点，相信你们都能看懂

串联

将上面 3 步串起来

/**
 * 重打包Excel2007文件
 * @param ifExcel2007New 是否重新打包
 * @param xlsxFile xlsx源文件
 * @throws IOException
 * @author 青石路
 */
private void repackageExcel2007(boolean ifExcel2007New, File xlsxFile) throws IOException {
    if (!ifExcel2007New) {
        return;
    }
    Path unzipDir = Files.createTempDirectory("");
    try {
        String basePath = Paths.get(unzipDir.toString(), xlsxFile.getName().substring(0, xlsxFile.getName().lastIndexOf("."))).toString();
        // 解压xlsx
        unzip(xlsxFile, basePath);
        // 修改xml
        updateXmlStandalone(Paths.get(basePath, "_rels", ".rels"));
        updateXmlStandalone(Paths.get(basePath, "docProps", "core.xml"));
        updateXmlStandalone(Paths.get(basePath, "xl", "_rels", "workbook.xml.rels"));
        updateXmlStandalone(Paths.get(basePath, "[Content_Types].xml"));
        updateSheetXmlDimension(Paths.get(basePath, "xl", "worksheets"));
        // 打包成xlsx
        repackage(basePath, xlsxFile);
    } finally {
        // 删除临时文件夹
        try (Stream<Path> walk = Files.walk(unzipDir)) {
            walk.sorted(Comparator.reverseOrder())
                    .map(Path::toFile)
                    .forEach(File::delete);
        }
    }
}

至此，大功告成！我已经试过了，重打包之后的 Excel 2007 文件，用 Windows 的 Excel 工具能正常打开，WPS 也能正常打开，给新客户测试，也能正常导入，简直完美！