如何用box/spout解析大型Excel表格

PHP解析Excel有個比較有名的函式庫是phpoffice/phpexcel。在實際使用的過程中，遇到上萬行的Excel時，phpexcel的記憶體使用量會飆升。今天我來介紹另一個高效解析Excel的PHP函式庫box/spout。

看官方介紹是：Read and write spreadsheet files in a fast and scalable way，可見這個函式庫在解析Excel時，Excel檔案的大小不會太影響記憶體的使用量。可以說是PHPExcel一種替代方案。

由於box/spout函式庫的Github主頁上的文件連結已經掛了，這裡簡單寫了一個XLSX檔轉換成CSV的例子：

include 'vendor/autoload.php';
use Box\Spout\Reader\ReaderFactory;
use Box\Spout\Common\Type;

$t = time();
$reader = ReaderFactory::create(Type::XLSX);
//如果注释掉，单元格内的日期类型将会是DateTime，不注释的话Spout自动帮你将日期转化成string
//$reader->setShouldFormatDates(true);
$reader->open('./test.xlsx');
$iterator = $reader->getSheetIterator();
$iterator->rewind();
$sheet1 = $iterator->current();
$rowIter = $sheet1->getRowIterator();
foreach ($rowIter as $row) {
    $d = '';
    foreach ($row as $col) {
        echo $d;
        if ($col instanceof DateTime) {
            echo $col->format('Y-m-d');
        } else {
            echo $col;
        }
        $d = "\t";
    }
    echo PHP_EOL;
}
$reader->close();

使用方法還是很簡潔明了的。

使用記憶體快取String字典

前面說到box/spout在解析時消耗的記憶體大小，不受Excel檔案大小的影響，這是如何做到的呢？

這裡簡單科普一下：XLSX檔案格式是符合一項標準的，這項標準叫做OOXML（https://zh.wikipedia.org/zh-cn/Office_Open_XML）。 XLSX其實就是一個Zip包，可以解壓縮出來看看其中的內容。

XLSX表格中，如果單元格的內容是一串字串時，實際保存的時候只保存一個stringId，字串真正的內容保存在一個String字典中。

在讀取XLSX檔時，如果儲存格是字串，Spout就要去查詢String字典。 Spout有兩種查詢方法，一是每次從文件裡讀取字典的一部分查詢，二是把整個字典載入到記憶體裡查詢。

顯然把String字典整個載入到記憶體裡，查詢速度最快。但Spout過於保守，很多情況下都是採用第一種查詢方式。所以稍微修改下Spout的程式碼，讓Spout盡量把字典全部載入到記憶體：

# Spout/Reader/XLSX/Helper/SharedStringsCaching/CachingStrategyFactory.php
class CachingStrategyFactory {
  ....
  const MAX_NUM_STRINGS_PER_TEMP_FILE = 10000; // 改成50000
  ....
}

試驗一個1.3w行、28列、2.8MB大小的EXCEL轉換成CSV，做個比較：

方式	耗時	佔用記憶體
字典不載入到記憶體	185 s	1.3 MB
字典載入到記憶體	43 s	#9.4 MB

可見處理時間相差還是挺大的。

推薦學習：php影片教學

以上是如何用box/spout解析大型Excel表格的詳細內容。更多資訊請關注PHP中文網其他相關文章！