PHP讀取超大的excel文件數(shù)據(jù)的方案

dkzwm 發(fā)布于2019-07-01 10:18 / 3307人閱讀

摘要：場(chǎng)景和痛點(diǎn)說(shuō)明今天因?yàn)橐粋€(gè)老同學(xué)找我，說(shuō)自己公司的物流業(yè)務(wù)都是現(xiàn)在用處理，按月因?yàn)閿?shù)據(jù)量大，一個(gè)差不多有百萬(wàn)數(shù)據(jù)，文件有接近，打開和搜索就相當(dāng)?shù)穆?lián)想到場(chǎng)景要導(dǎo)入數(shù)據(jù)，可能數(shù)據(jù)量很大，這里利用常用的一些方法比如會(huì)常有時(shí)間和內(nèi)存限制問(wèn)題下面我

場(chǎng)景和痛點(diǎn) 說(shuō)明

今天因?yàn)橐粋€(gè)老同學(xué)找我，說(shuō)自己公司的物流業(yè)務(wù)都是現(xiàn)在用excel處理，按月因?yàn)閿?shù)據(jù)量大，一個(gè)excel差不多有百萬(wàn)數(shù)據(jù)，文件有接近100M，打開和搜索就相當(dāng)?shù)穆?p>聯(lián)想到場(chǎng)景：要導(dǎo)入數(shù)據(jù)，可能excel數(shù)據(jù)量很大，這里利用常用的一些方法比如phpexcel會(huì)常有時(shí)間和內(nèi)存限制問(wèn)題
下面我們就利用一個(gè)利用流處理的類庫(kù)SpreadsheetReader來(lái)做大excel的讀取

編寫過(guò)程 說(shuō)明

關(guān)鍵具體在代碼里注釋

代碼

Sheets();
if (!$sheets) {
    die("沒(méi)有工作表");
}

//改變當(dāng)前處理的工作表
$Reader->ChangeSheet(0);

//打印當(dāng)前所在工作表的當(dāng)前所在行數(shù)據(jù)
var_dump($Reader->current());

//因?yàn)閞eader類集成了Iter所以可以用迭代方式處理
//這里提醒 如果文件超大，這邊的處理速度會(huì)過(guò)慢，不過(guò)不會(huì)引發(fā)內(nèi)存性能問(wèn)題
//$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 "內(nèi)存占用：" . convert($memoryUse) . "; 用時(shí)：" . ($endTime - $startTime) . PHP_EOL;

結(jié)果 測(cè)試說(shuō)明

上面讀取的example1.xlsx文件有100M左右，讀寫過(guò)慢，測(cè)試只開了讀取當(dāng)前默認(rèn)工作表的當(dāng)前所在行數(shù)據(jù)
因數(shù)據(jù)敏感，已做屏蔽

日志記錄內(nèi)存使用率

147.77 kb
array (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)

內(nèi)存占用：207.55 kb; 用時(shí)：9.5835480690002