How PHP reads data from large Excel files

This article mainly introduces how PHP read large Excel file data, has a certain reference value, now share to everyone, the need for friends can refer to

Scenes and pain points

Description

today, because of an old classmate to me, said that their company's logistics business is now in Excel processing, by month because of the large amount of data, an Excel almost millions of data, files have close to 100M, open and search is quite slow

Lenovo to the scene: to import data, it may be a large amount of Excel data, the use of some commonly used methods such as phpexcel will often have time and memory limitations.

Let's use a stream-processing class library Spreadsheetreader to make Excel read

Writing process

Description

key specific comments in code

Code

<?php/** * Created by Phpstorm.   * USER:QKL * DATE:2018/7/11 * time:15:14 */set_time_limit (0);    Set script maximum execution time to 0 never expires//ini_set (' Memory_limit ', ' 200M ');    Temporarily set maximum memory consumption function convert ($size) {$unit = array (' B ', ' KB ', ' MB ', ' GB ', ' TB ', ' PB '); Return @round ($size/pow (1024x768, ($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);//Get all worksheets for the current file $sheets = $Reader->sheets (), if (! $sheets) {die ("no Worksheets");} Change the currently processed sheet $reader->changesheet (0);//Print the current row data var_dump ($Reader->current ()) on the current worksheet;// Because the Reader class is integrated with ITER, it can be processed iteratively//Here is a reminder if the file is oversized, the processing speed will be too slow, but the memory performance problem will not be raised//$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 "Memory footprint:". Convert ($memoryUse). "; Spents: ". ($endTime-$startTime). Php_eol;

Results

Test instructions

the above read example1.xlsx file has about 100M, read and write too slow, the test only read the current default sheet of the current row of data
Blocked due to data sensitivity

Log Recording Memory utilization

147.77 Kbarray (size=50) 0 = String ' xxxxxxxxxxxxxx ' (length=25) 1 = String ' xxxxxxxxxxxxxx ' (length=15) 2 =&gt ; String ' xxxxxxxxxxxxxx ' (length=18) 3 = String ' xxxxxxxxxxxxxx ' (length=12) 4 = String ' xxxxxxxxxxxxxx ' (length= 5 = String ' xxxxxxxxxxxxxx ' (length=12) 6 = String ' xxxxxxxxxxxxxx ' (length=24) 7 = String ' xxxxxxxxxxx xxx ' (length=12) 8 = String ' xxxxxxxxxxxxxx ' (length=27) 9 = String ' xxxxxxxxxxxxxx ' (length=12) ten = Strin  G ' xxxxxxxxxxxxxx ' (length=15) = "String ' xxxxxxxxxxxxxx ' (length=28) + = String ' xxxxxxxxxxxxxx ' (length=9) [=] string ' xxxxxxxxxxxxxx ' (length=12) and string ' xxxxxxxxxxxxxx ' (length=9) [xxxxxxxxxxxxx] X ' (length=6) + = String ' xxxxxxxxxxxxxx ' (length=9) + = String ' xxxxxxxxxxxxxx ' (length=3) + = String ' Xxxxxxxxxxxxxx ' (length=6) + = String ' xxxxxxxxxxxxxx ' (length=3) + = String ' xxxxxxxxxxxxxx ' (length=15) 21 = = String ' xxxxxxxxXXXxxx ' (length=15) + = String ' xxxxxxxxxxxxxx ' (length=19) + = String ' xxxxxxxxxxxxxx ' (length=13) String ' xxxxxxxxxxxxxx ' (length=19) + = String ' xxxxxxxxxxxxxx ' (length=12) + = String ' xxxxxxxxxxxxxx ' (lengt h=12) + = String ' xxxxxxxxxxxxxx ' (length=12) (length=6) + string ' xxxxxxxxxxxxxx ' xxxxxxx Xxxxxxx ' (length=12) + = String ' xxxxxxxxxxxxxx ' (length=6) + = String ' xxxxxxxxxxxxxx ' (length=15) + = String ' xxxxxxxxxxxxxx ' (length=24) = = String ' xxxxxxxxxxxxxx ' (length=18), String ' xxxxxxxxxxxxxx ' (lengt h=18) (String ' xxxxxxxxxxxxxx ' (length=24) * = String ' xxxxxxxxxxxxxx ' (length=12) PNs = String ' xxxxxx  xxxxxxxx ' (length=18), String ' xxxxxxxxxxxxxx ' (length=21), and string ' xxxxxxxxxxxxxx ' (length=9) String ' xxxxxxxxxxxxxx ' (length=9) * = String ' xxxxxxxxxxxxxx ' (length=18), String ' xxxxxxxxxxxxxx ' (lengt h=21) + = String 'Xxxxxxxxxxxxxx ' (length=15), "Xxxxxxxxxxxxxx" (length=12), String ' xxxxxxxxxxxxxx ' (length=6) 46  = = String ' xxxxxxxxxxxxxx ' (length=12) = = String ' xxxxxxxxxxxxxx ' (length=22) = = String ' xxxxxxxxxxxxxx ' (length=22) (length=0) memory footprint: 207.55 KB; Spents: 9.5835480690002

The above is the whole content of this article, I hope that everyone's learning has helped, more relevant content please pay attention to topic.alibabacloud.com!