不升級 POI 版本，如何生成符合新版標準的Excel 2007檔案

開心一刻

記得小時候，家裡丟了錢，是我拿的，可爸媽卻一口咬定是弟弟拿的

爸爸把弟弟打的遍體鱗傷，弟弟氣憤的斜視著我

我不敢直視弟弟，目光轉向爸爸說到：爸爸，你看他，好像還不服

問題描述

專案基於 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 檔案是個壓縮包！

相信大家沒疑問了吧；我們來對比下兩個目錄

雖然差異檔案挺多，但可以歸為兩類

1. 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 中還有一個差異：

   

   建立時間不同是正常的，這個差異可以忽略

2. 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 進行以下處理

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，用別的可能會有驚嚇！