开心一刻
记得小时候，家里丢了钱，是我拿的，可爸妈却一口咬定是弟弟拿的
爸爸把弟弟打的遍体鳞伤，弟弟气愤的斜视着我
我不敢直视弟弟，目光转向爸爸说到：爸爸，你看他，好像还不服
问题描述
项目基于
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值的含义// 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); } }); }; }这个代码稍微复杂一点，但可以归纳为以下几步
-
遍历 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 也能正常打开，给新客户测试，也能正常导入，简直完美！
总结
-
Excel 2007 文件是集 xml、图片等文件的压缩包
-
引入新功能时，一定不能影响已有功能
都说了能不动就别动，非要去调整，出生产事故了吧
-
可以通过解压、修改、打包的方式，修改Excel 2007文件的元数据
-
解压与打包都用
commons-compress，用别的可能会有惊吓！
-
