How to read data from large excel files with PHP

This article mainly introduces how to read large excel file data with PHP. It has certain reference value. Now I share it with you. Friends in need can refer to it

Scenarios and Pain Points

Explanation

Today, an old classmate came to me and said that his company’s logistics business is now handled with Excel. Due to the large amount of data on a monthly basis, one Excel has almost one million data, and the files are close to 100M, it is quite slow to open and search.

Think of the scenario: to import data, the amount of excel data may be very large. Here, some commonly used methods such as phpexcel will often have time and memory limitations.

Below We will use SpreadsheetReader, a class library that uses stream processing, to read large excel

Writing process

Explanation

The key details are commented in the code

Code

<?php /**
 * Created by PhpStorm.
 * User: qkl
 * Date: 2018/7/11
 * Time: 15:14
 */

set_time_limit(0);   // 设置脚本最大执行时间 为0 永不过期
//ini_set(&#39;memory_limit&#39;,&#39;200M&#39;);    // 临时设置最大内存占用

function convert($size)
{
    $unit = array(&#39;b&#39;, &#39;kb&#39;, &#39;mb&#39;, &#39;gb&#39;, &#39;tb&#39;, &#39;pb&#39;);
    return @round($size / pow(1024, ($i = floor(log($size, 1024)))), 2) . &#39; &#39; . $unit[$i];
}

require &#39;../vendor/autoload.php&#39;;

$start = memory_get_usage();
echo convert($start) . PHP_EOL;
//$inputFileName = &#39;./11111111.xlsx&#39;;
$inputFileName = &#39;./example1.xlsx&#39;;

// 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;

Result

Test description

The example1.xlsx file read above is about 100M, and the reading and writing are too slow. The test only enabled reading The current row data of the current default worksheet
has been blocked due to sensitive data

Log record memory usage

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)

内存占用：207.55 kb; 用时：9.5835480690002

The above is the entire content of this article, I hope it will be useful for everyone's learning For help, please pay attention to the PHP Chinese website for more related content!

Related recommendations:

How to use PHP to write a simple interpreter

How to use MixPHP to develop API interfaces

The above is the detailed content of How to read data from large excel files with PHP. For more information, please follow other related articles on the PHP Chinese website!