How to read very large excel files with PHPExcel

Original works, reprinting is allowed. When reprinting, please be sure to indicate the article original source , author information and this statement in the form of a hyperlink. Otherwise held liable. http://ustb80.blog.51cto.com/6139482/1066505

In my work, I often encounter the problem of importing part of the xlsx file into the database. Usually we use PHPExcel to read.

We can easily read an excel table into a php array through the following method, and then we can do whatever we want:

1. $input_file = "data.xlsx"; 
2. $objPHPExcel = PHPExcel_IOFactory::load($input_file); 
3. $sheetData = $objPHPExcel->getSheet(0)->toArray(null, true, true, true); 

If the article ends here, it will not be of much value.

Unfortunate situations always exist. When data.xlsx has tens of thousands of rows, each row has many columns, each column has a long string, and some have color and other effects, use the above What often happens with methods is that they run out of memory.

Well, we still have ini_set to increase the memory, and we can also use set_time_limit to set a longer timeout, as follows:

1. set_time_limit(90); 
2. ini_set("memory_limit", "1024M"); 
3. $input_file = "data.xlsx"; 
4. $objPHPExcel = PHPExcel_IOFactory::load($input_file); 
5. $sheetData = $objPHPExcel->getSheet(0)->toArray(null, true, true, true); 

But it is very responsible to say that these are not the ultimate solution.

I once tried setting the memory to 2G and the timeout to 90 seconds, but I still couldn’t read a colorful table with 4,000 rows. The reason lies in the toArray method, which saves all the processing results into an array. This method is very convenient when processing simple tables, but it is really useless when processing large tables.

Our solution is as follows:

1. require 'lib/PHPExcel.php'; 
2.  
3. set_time_limit(90); 
4. $input_file = "data.xlsx"; 
5. $objPHPExcel = PHPExcel_IOFactory::load($input_file); 
6.  
7. // 读取规则 
8. $sheet_read_arr = array(); 
9. $sheet_read_arr["sheet1"] = array("A","B","C","D","F"); 
10. $sheet_read_arr["sheet2"] = array("A","B","C","D","F"); 
11.  
12. // 循环所有的页 
13. foreach ($sheet_read_arr as $key => $val) 
14. { 
15.     $currentSheet = $objPHPExcel->getSheetByName($key);// 通过页名称取得当前页 
16.     $row_num = $currentSheet->getHighestRow();// 当前页行数 
17.  
18.     // 循环从第二行开始，第一行往往是表头 
19.     for ($i = 2; $i <= $row_num; $i++)
20. {
21. $cell_values = array();
22. foreach ($val as $cell_val)
23. {
24. $address = $cell_val . $i;// 单元格坐标
26. // 读取单元格内容
27. $cell_values[] = $currentSheet->getCell($address)->getFormattedValue(); 
28.         } 
29.  
30.         // 看看数据 
31.         print_r($cell_values); 
32.     } 
33. } 

The above method is considered a more complicated situation. If you just want to read out all the cells, just use the following method:

1. require 'lib/PHPExcel.php'; 
2.  
3. set_time_limit(90); 
4. $input_file = "data.xlsx"; 
5. $objPHPExcel = PHPExcel_IOFactory::load($input_file); 
6.  
7. $sheet_count = $objPHPExcel->getSheetCount(); 
8. for ($s = 0; $s < $sheet_count; $s++)
9. {
10. $currentSheet = $objPHPExcel->getSheet($s);// 当前页 
11.     $row_num = $currentSheet->getHighestRow();// 当前页行数 
12.     $col_max = $currentSheet->getHighestColumn(); // 当前页最大列号 
13.  
14.     // 循环从第二行开始，第一行往往是表头 
15.     for($i = 2; $i <= $row_num; $i++)
16. {
17. $cell_values = array();
18. for($j = 'A'; $j < $col_max; $j++)
19. {
20. $address = $j . $i; // 单元格坐标
21. $cell_values[] = $currentSheet->getCell($address)->getFormattedValue(); 
22.         } 
23.  
24.         // 看看数据 
25.         print_r($cell_values); 
26.     } 
27. } 

We can The above print_r place is changed to combine the sql statement and write it to the file, and then use mysql to import it. Of course, you can also directly connect to the database to insert records into the table. This is optional.

Using this method, tens of thousands of rows of records can be easily imported into the table. I hope it will be helpful to everyone.

This article comes from the "Fanxing's Technology Blog" blog, please be sure to keep this source http://ustb80.blog.51cto.com/6139482/1066505

The above introduces how to use PHPExcel to read very large excel files, including the relevant content. I hope it will be helpful to friends who are interested in PHP tutorials.