Reading very large (more than 100MB) Excel files in PHP

Question

I'm trying to read a larger than 100MB Excel file using PHPExcel but it crashes while loading the file. I don't need any styling. I tried using:

$objReader->setReadDataOnly(true);

but it still crashes.

Is there any efficient way to read this size of Excel file in PHP?

read this: http://stackoverflow.com/questions/162176/reading-very-large-files-in-php — matanco, Oct 13 '15 at 06:32
@matanco this link is fore CSV file I am talking about Excel file. — Mubeen Ali, Oct 13 '15 at 06:33
@KA_lin I need to read and insert records in database. Can I do this using CLI? — Mubeen Ali, Oct 13 '15 at 06:35
Sure you can but only if it`s not that often( a user upload and generate statistics for example not recomended) — ka_lin, Oct 13 '15 at 06:37
Have you checked the logs to see if its a timeout error, or if it's running out of memory? Have you read about cell caching in the documentation? — Mark Baker, Oct 13 '15 at 06:56
for big data u can use Spout, here's an example http://teknosains.com/php/read-big-excel-file-using-php-spout-library — jsdev, Aug 24 '17 at 11:02

score 14 · Answer 1 · answered Oct 13 '15 at 15:30

Try Spout: https://github.com/box/spout.

This is a PHP library that was created to solve your problem (reading/writing large files). Here is why it works:

Other libraries keep a representation of the spreadsheet in memory which make them subject to out of memory errors. Using some caching strategies will help with these kind of errors but will affect performance pretty badly.

On the other hand, Spout uses streams to read or write data. This means that there is only one row kept in memory at all times, all read/written rows being freed from memory. This allows fast read/write of dataset of any size! Give it a try :)

yaap I use Spout. Its fast. here i have an example http://teknosains.com/php/read-big-excel-file-using-php-spout-library — jsdev, Sep 05 '17 at 10:41

score 1 · Answer 2 · answered Oct 13 '21 at 06:54

1

Spout just saved my time! I couldn't read a large file with PhpOffice/PhPSpreedSheet with many Fatal Error Memory size, and with Spout it works like a charm.

answered Oct 13 '21 at 06:54

Guillaume

113
6

Reading very large (more than 100MB) Excel files in PHP

2 Answers2

Linked