首页 > 其他分享 >不升级 POI 版本，如何生成符合新版标准的Excel 2007文件

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

时间：2024-09-12 09:24:35浏览次数：10  
标签：xml 文件 filePath Excel 2007 POI

开心一刻

记得小时候，家里丢了钱，是我拿的，可爸妈却一口咬定是弟弟拿的

爸爸把弟弟打的遍体鳞伤，弟弟气愤的斜视着我

我不敢直视弟弟，目光转向爸爸说到：爸爸，你看他，好像还不服

还不服

问题描述

项目基于 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，然后进行解压）

7z解压

解压之后目录结构如下

都是xml文件

所有的文件都是 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 的解压目录结构一致，文件名与文件数量也一致

poi5_3_0目录结构

关于

Excel 2007 文件是个压缩包！

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

新旧目录结构对比

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

  1. standalone 差异

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

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

    standalone差异

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

    core.xml 中还有一个差异：

    core时间差异

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

  2. dimension 差异

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

    sheet_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_依赖

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

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

此时你们是不是懵逼了？

EasyExcel怎么依赖POI

显然用 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 进行以下处理

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

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

  1. 解压

    就用 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);
            }
        }
    }
}

  2. 修改

    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 值的含义

    // POI 4.1.2

    // 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);
            }
        });
    };
}

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

    1. 遍历 sheet xml文件的内容，得到列数和行数

    2. 根据列数去推算出最大列坐标（B），再根据行数（2）得到结束坐标（B2），那么 ref 的值也就是：A1:B2

      这里有个小坑，当数据只有一行一列时，新版的 ref 的值与旧版的 ref 值一致，都是 A1，但上述代码得到却是 A1:A1，所以还需要兼容调整下，至于如何调整，就交给你们了，我这里只是提示你们要注意这个坑！！！

    3. 进行 sheet xml 数据拷贝，并用 <dimension ref=\"A1:B2 替换掉 <dimension ref=\"A1，最后用新的 sheet xml 文件替换旧的

  3. 打包

    需要修改的 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();
        }
    }
}

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

  4. 串联

    将上面 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 也能正常打开，给新客户测试，也能正常导入，简直完美！

    愣着干啥，鼓掌

    总结

    1. Excel 2007 文件是集 xml、图片等文件的压缩包

    2. 引入新功能时，一定不能影响已有功能

      都说了能不动就别动，非要去调整，出生产事故了吧

    3. 可以通过解压、修改、打包的方式，修改Excel 2007文件的元数据

    4. 解压与打包都用 commons-compress，用别的可能会有惊吓！

标签：xml,文件,filePath,Excel,2007,POI
From： https://www.cnblogs.com/youzhibing/p/18409511

相关文章

  • Azure web app has no access to openai private endpoint in virtual network
    题意："AzureWeb应用无法访问虚拟网络中的OpenAI私有端点。"问题背景：IamtryingtohostawebapplicationsimilartoaprivateChatGPTinstancewithinasecludedvirtualnetwork,ensuringthatthere'snoexternalinternetaccess."我正在尝试在一个隔离的......
  • C# .netcore NPOI库 实现报表的列自适应删减
    实际需求：业务上的一个需求，数据库表A中的B字段存放的是该条数据的一些标签，标签存在两级【即一级标签和二级标签】，现在要是实现将这些标签统计到报表中，一级标签作为表头，二级标签作为填充值。由于之前的报表每增加一个列都需要去数据库表中增加这个字段名称，然后代码中写统计逻辑，这......
  • POI常见包名称含义，常见HSSF，SXXF，SXSSF区别
    Java单元格合并后边框消失了在使用Java编写Excel文件时，经常会遇到合并单元格的需求。单元格合并可以使表格更加美观，同时减少重复的内容。然而，有时候在合并单元格后会发现边框消失了，这给我们带来了困惑。本文将介绍这个问题的原因以及解决方法，并提供相应的代码示例。问题描述当我......
  • python读取Excel表格内容
    importpandasaspdio=r'C:\Users\lijj1\Downloads\OSS.xlsx'#绝对路径。PS：跟当前脚本在同一目录下用相对路径，否则用绝对路径。data=pd.read_excel(io,sheet_name='工作表1')#指定读取第一个sheetprint(data.head(2))#读取前2行column_data=data.loc[:,'对应域......
  • [ZJOI2007] 时态同步
    [ZJOI2007]时态同步题目描述小Q在电子工艺实习课上学习焊接电路板。一块电路板由若干个元件组成，我们不妨称之为节点，并将其用数字\(1,2,3\cdots\)进行标号。电路板的各个节点由若干不相交的导线相连接，且对于电路板的任何两个节点，都存在且仅存在一条通路（通路指连接两个元件的......
  • PointNet++改进策略 ：模块改进 | SWA| PVT，融入Transformer的点云与体素的模块同时提升
    目录介绍PVT原理PVT的核心思想和结构PVT模块结构体素分支点分支代码实现论文题目：PVT:Point-VoxelTransformerforPointCloudLearning发布期刊：InternationalJournalofIntelligentSystems通讯地址：杭州电子科技大学&伦敦大学学院代码地址：https://github.com/......
  • PointNet++改进策略 ：模块改进 | x-Conv | PointCNN， 结合局部结构与全局排列提升模型性
    目录前言PointCNN实现细节1.XXX-Conv操作输入输出步骤2.PointCNN网络架构层级卷积分类与分割任务......
  • Excel数据清洗工具：提高数据处理效率的利器
    Excel数据清洗工具：提高数据处理效率的利器引言在当今的数据驱动时代，数据的质量直接影响着分析结果的可靠性和有效性。然而，在实际工作中，我们常常会遇到数据中的各种问题，如重复记录、缺失值、格式不一致等。为了解决这些问题，一款名为“Excel数据清洗工具”的应用程序应运而......
  • 【大数据】如何读取多个Excel文件并计算列数据的最大求和值
    导语：在数据分析和处理中，我们经常需要从多个Excel文件中提取数据并进行计算。本文将带您通过一个实用的Python教程，学习如何读取D盘目录下特定文件夹内的多个Excel文件，并计算特定列数据的最大求和值。文章目录一、准备工作二、教程步骤1.导入必要的库2.设置文件路径3.......
  • Excel--常用办公技巧
     一、常用快捷键：Ctrl＋F查找内容，Ctrl＋H替换内容Ctrl＋N新建文件，Ctrl＋P/F2（＋Fn）打印预览Ctrl＋Z 撤销操作，Ctrl＋Y 恢复操作Ctrl＋1，打开单元格格式对话框Ctrl＋2/B字体加粗Ctrl＋3/I字体倾斜Ctrl＋4/U添加下划线Ctrl＋5添加删除线Ctrl＋9，快速隐藏行（选中列则快速隐藏全部工作表......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99