How to use PHPExcel to read large excel files

: This article describes how to use PHPExcel to read ultra-large excel files. For more information about PHP tutorials, see. Original works can be reprinted. during reprinting, you must mark the original source, author information, and this statement in hyperlink form. Otherwise, legal liability will be held. Http://ustb80.blog.51cto.com/6139482/1066505

During work, part of the content in the xlsx file is often imported into the database. We usually use PHPExcel to read data.

Through the following method, we can easily read an excel table into a php array, 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 );

If the article ends, it will be of little value.

Fortunately, data.xlsx has tens of thousands of rows, each row has many columns, each column has a long string, and some have effects such as color. when using the above method, the memory is insufficient.

Okay, we also have ini_set to increase the memory. you can also use set_time_limit to set a long timeout value, as shown below:

1. Set_time_limit (90 );
2. Ini_set ("memory_limit", "1024 M ");
3. $ Input_file = "data.xlsx ";
4. $ ObjPHPExcel = PHPExcel_IOFactory: load ($ input_file );
5. $ SheetData = $ objPHPExcel-> getSheet (0)-> toArray (null, true );

But responsibly, these are not the ultimate solutions.

I have tried to set the memory to 2 GB and the timeout to 90 seconds. I still cannot read a 4000-row dirty green table. The reason is that the toArray method saves all the processing results to the array. this method is convenient for processing simple tables, but when processing large tables, it's really a cup.

Our solution is as follows:

1. Require 'Lib/PHPExcel. php ';
3. Set_time_limit (90 );
4. $ Input_file = "data.xlsx ";
5. $ ObjPHPExcel = PHPExcel_IOFactory: load ($ input_file );
7. // Read rules
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 ");
12. // Loop all pages
13. Foreach ($ sheet_read_arr as $ key => $ val)
14. {
15. $ CurrentSheet = $ objPHPExcel-> getSheetByName ($ key); // Obtain the current page by page name
16. $ Row_num = $ currentSheet-> getHighestRow (); // number of current page rows
18. // The loop starts from the second row. the first row is usually the header.
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; // cell coordinate
26. // Read the cell content
27. $ Cell_values [] = $ currentSheet-> getCell ($ address)-> getFormattedValue ();
28. }
30. // Check the data
31. Print_r ($ cell_values );
32. }
33. }

The above method is complicated. to read all the cells, use the following method:

1. Require 'Lib/PHPExcel. php ';
3. Set_time_limit (90 );
4. $ Input_file = "data.xlsx ";
5. $ ObjPHPExcel = PHPExcel_IOFactory: load ($ input_file );
7. $ Sheet_count = $ objPHPExcel-> getSheetCount ();
8. For ($ s = 0; $ s <$ sheet_count; $ s ++)
9. {
10. $ CurrentSheet = $ objPHPExcel-> getSheet ($ s); // current page
11. $ Row_num = $ currentSheet-> getHighestRow (); // number of current page rows
12. $ Col_max = $ currentSheet-> getHighestColumn (); // maximum column number on the current page
14. // The loop starts from the second row. the first row is usually the header.
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; // cell coordinate
21. $ Cell_values [] = $ currentSheet-> getCell ($ address)-> getFormattedValue ();
22. }
24. // Check the data
25. Print_r ($ cell_values );
26. }
27. }

We can change the print_r area above into a group spelling SQL statement and write it into a file, and then import it using mysql. of course, we can also directly connect to the database to insert records into the table. this is free.

Using this method, you can easily import tens of thousands of rows of records into the table, and hope to help you.

This article is from the "Fanxing technology blog" blog, please be sure to keep this source http://ustb80.blog.51cto.com/6139482/1066505

The above describes how to use PHPExcel to read ultra-large excel files, including some content, and hope to be helpful to friends who are interested in PHP tutorials.