PHP如何讀取大excel檔案資料的方法

這篇文章主要介紹了關於PHP如何讀取大excel檔案資料的方法，有著一定的參考價值，現在分享給大家，有需要的朋友可以參考一下

情境和痛點

說明

今天因為一個老同學找我，說自己公司的物流業務都是現在用excel處理，按月因為資料量大，一個excel差不多有百萬資料，檔案有接近100M，開啟和搜尋就相當的慢

聯想到情境：要匯入資料，可能excel資料量很大，這裡利用常用的一些方法比如phpexcel會常有時間和記憶體限制問題

下面我們就利用一個利用流處理的類庫SpreadsheetReader來做大excel的讀取

編寫過程

說明

關鍵具體在代碼裡注釋

代碼

<?php/** * Created by PhpStorm. * User: qkl * Date: 2018/7/11 * Time: 15:14 */set_time_limit(0);   // 設定指令碼最大執行時間 為0 永不到期//ini_set('memory_limit','200M');    // 臨時設定最大記憶體佔用function convert($size){    $unit = array('b', 'kb', 'mb', 'gb', 'tb', 'pb');    return @round($size / pow(1024, ($i = floor(log($size, 1024)))), 2) . ' ' . $unit[$i];}require '../vendor/autoload.php';$start = memory_get_usage();echo convert($start) . PHP_EOL;//$inputFileName = './11111111.xlsx';$inputFileName = './example1.xlsx';// If you need to parse XLS files, include php-excel-reader$startTime = microtime(true);$Reader = new SpreadsheetReader($inputFileName);//擷取當前檔案所有的工作表$sheets = $Reader->Sheets();if (!$sheets) {    die("沒有工作表");}//改變當前處理的工作表$Reader->ChangeSheet(0);//列印當前所在工作表的當前所在行資料var_dump($Reader->current());//因為reader類整合了Iter所以可以用迭代方式處理//這裡提醒 如果檔案超大，這邊的處理速度會過慢，不過不會引發記憶體效能問題//$i = 0;//foreach ($Reader as $Row)//{//    if ($i>=3) {//        break;//    }////    echo $i . PHP_EOL;//    print_r($Row);////    $i++;//}$endTime = microtime(true);$memoryUse = memory_get_usage();echo "記憶體佔用：" . convert($memoryUse) . "; 用時：" . ($endTime - $startTime) . PHP_EOL;

結果

測試說明

上面讀取的example1.xlsx檔案有100M左右，讀寫過慢，測試只開了讀取當前預設工作表的當前所在行資料
因資料敏感，已做屏蔽

日誌記錄記憶體使用量率

147.77 kbarray (size=50)  0 => string 'xxxxxxxxxxxxxx' (length=25)  1 => string 'xxxxxxxxxxxxxx' (length=15)  2 => string 'xxxxxxxxxxxxxx' (length=18)  3 => string 'xxxxxxxxxxxxxx' (length=12)  4 => string 'xxxxxxxxxxxxxx' (length=12)  5 => string 'xxxxxxxxxxxxxx' (length=12)  6 => string 'xxxxxxxxxxxxxx' (length=24)  7 => string 'xxxxxxxxxxxxxx' (length=12)  8 => string 'xxxxxxxxxxxxxx' (length=27)  9 => string 'xxxxxxxxxxxxxx' (length=12)  10 => string 'xxxxxxxxxxxxxx' (length=15)  11 => string 'xxxxxxxxxxxxxx' (length=28)  12 => string 'xxxxxxxxxxxxxx' (length=9)  13 => string 'xxxxxxxxxxxxxx' (length=12)  14 => string 'xxxxxxxxxxxxxx' (length=9)  15 => string 'xxxxxxxxxxxxxx' (length=6)  16 => string 'xxxxxxxxxxxxxx' (length=9)  17 => string 'xxxxxxxxxxxxxx' (length=3)  18 => string 'xxxxxxxxxxxxxx' (length=6)  19 => string 'xxxxxxxxxxxxxx' (length=3)  20 => string 'xxxxxxxxxxxxxx' (length=15)  21 => string 'xxxxxxxxxxxxxx' (length=15)  22 => string 'xxxxxxxxxxxxxx' (length=19)  23 => string 'xxxxxxxxxxxxxx' (length=13)  24 => string 'xxxxxxxxxxxxxx' (length=19)  25 => string 'xxxxxxxxxxxxxx' (length=12)  26 => string 'xxxxxxxxxxxxxx' (length=12)  27 => string 'xxxxxxxxxxxxxx' (length=12)  28 => string 'xxxxxxxxxxxxxx' (length=6)  29 => string 'xxxxxxxxxxxxxx' (length=12)  30 => string 'xxxxxxxxxxxxxx' (length=6)  31 => string 'xxxxxxxxxxxxxx' (length=15)  32 => string 'xxxxxxxxxxxxxx' (length=24)  33 => string 'xxxxxxxxxxxxxx' (length=18)  34 => string 'xxxxxxxxxxxxxx' (length=18)  35 => string 'xxxxxxxxxxxxxx' (length=24)  36 => string 'xxxxxxxxxxxxxx' (length=12)  37 => string 'xxxxxxxxxxxxxx' (length=18)  38 => string 'xxxxxxxxxxxxxx' (length=21)  39 => string 'xxxxxxxxxxxxxx' (length=9)  40 => string 'xxxxxxxxxxxxxx' (length=9)  41 => string 'xxxxxxxxxxxxxx' (length=18)  42 => string 'xxxxxxxxxxxxxx' (length=21)  43 => string 'xxxxxxxxxxxxxx' (length=15)  44 => string 'xxxxxxxxxxxxxx' (length=12)  45 => string 'xxxxxxxxxxxxxx' (length=6)  46 => string 'xxxxxxxxxxxxxx' (length=12)  47 => string 'xxxxxxxxxxxxxx' (length=22)  48 => string 'xxxxxxxxxxxxxx' (length=22)  49 => string '' (length=0)記憶體佔用：207.55 kb; 用時：9.5835480690002

以上就是本文的全部內容，希望對大家的學習有所協助，更多相關內容請關注topic.alibabacloud.com！