5

Possible Duplicate:
Alternative for PHP_excel
PHPExcel reader — help required

First post. I have a very specific need for reading and writing very large (5mb+) excel files using PHP/My SQL. I have used PHPExcel and, while it is very elegant, it is way too slow and uses far too much PHP memory for my needs. At the moment I am limited to 32M PHP memory from my host.

My current solution is as follows:

Importing to database

I use php-excel reader (http://code.google.com/p/php-excel-reader/) for .xls files - Can import about 3000 rows before PHP runs out of memory.
I use spreadsheet reader (https://github.com/nuovo/spreadsheet-reader) for .xlsx files and so far I've tried 5000 rows+ without running out of memory. It is very slow which leads me to believe it is opening and closing the excel file and reading chunks of data at a time.

Exporting

PHP Excel for anything under about 500 rows. Works beautifully but is very slow and can't handle for than a few hundred rows with 32Mb PHP memory (Note: I am using cacheing which has helped but not enough)

Basic tab-delimited text for any more than 500 rows. Very fast, no limit on rows but no formatting and not true excel.

Ideally, I'm looking for an all-in-one solution that can read and write .xls and .xlsx files quickly and without running into memory issues. I don't mind a commercial product and would be happy to pay a few hundred dollars for something that works.

I have spent hours scouring the internet and this site but haven't found anything yet.

Any ideas?

PS: If anyone wants to see how I've implemented the above solutions, I'd be happy to share the code

Community
  • 1
  • 1
Kage
  • 81
  • 1
  • 2
  • 11
  • Well, you simply need memory to store data, and several thousand rows of almost any data in 32MB is pretty tight however you look at it. You cannot bump the memory using `ini_set`? Are you storing the data in memory or are you writing it out to a file immediately? – deceze Oct 15 '12 at 10:13
  • Just install Excel on your server. If you server is not compatible with Excel, change the Server OS to a compatible one. – hakre Oct 15 '12 at 10:13
  • Incidentally, the latest 1.7.8 release of PHPExcel allows cell caching in SQLite, which is highly memory efficient and not too much of a speed overhead – Mark Baker Oct 15 '12 at 10:19
  • Can't you just force everyone to save as `.csv` instead? :) – Robin Castlin Oct 15 '12 at 10:39
  • Not duplicate of help required as that is specifcally looking for help with PHPExcel. Can't install Excel or increase memory at this stage. Currently exploring the alternatives Mark has given. Thanks All – Kage Oct 15 '12 at 10:39
  • Of course I can use .csv but I'm trying to write a site that outputs data nicely formatted in native excel formats. – Kage Oct 15 '12 at 11:21
  • Spout in PHP might be a good one in your case, you can read a good explanation here https://www.tutsplanet.com/spout-an-awesome-library-for-reading-and-writing-in-excel/ . I've used this myself, writing 100K records without any problems with memory. – Duke Sep 08 '19 at 06:01

5 Answers5

4

As you're unhappy with the speed and memory requirements of PHPExcel, I'll point you to my standard answer to this question. If the answer isn't listed there, then it probably doesn't exist: that list is as comprehensive as I know.... though people writing their own alternatives to PHPExcel rarely bother to tell me making it difficult to keep up-to-date

Community
  • 1
  • 1
Mark Baker
  • 209,507
  • 32
  • 346
  • 385
  • Thanks Mark. I just spotted your alternatives list and am exploring a few now. Don't get me wrong, PHPExcel is excellent and very easy to use I'm just stuck with certain limitations and am trying to work around them. My current solutions work but nowhere near as elegantly as PHP Excel. I was just hoping there was something out there that could read/write as fast as PHP outputs CSV but with a few formatting functions as well. – Kage Oct 15 '12 at 10:38
  • You're unlikely to find anything remotely close to CSV in terms of speed: CSV is a simple format that can be written simply, line by line; while BIFF is effectively a directory with lots of individual (non-linear files), and OfficeOpenXML is a complex set of XML files in a zip wrapper. If you find anything though, let me know (if nothing else, so I can try and keep the alternatives list up-to-date) – Mark Baker Oct 15 '12 at 10:45
  • Thanks Mark. Problem solved. Using the two import libraries I mentioned above and using one of the alternatives you suggested for export (php-writeexcel), I'm now importing and exporting over 10,000 rows successfully. I suggest you add Nuovo ((https://github.com/nuovo/spreadsheet-reader) to your list if you haven't already. Only reads xlsx and it's quite slow but no memory issues. – Kage Oct 15 '12 at 11:19
  • 1
    I maintain the spreadsheet-reader library and since this question, XLSX performance has been improved - the culprit was that XLSX files have the so called "shared strings" that are stored in a separate XML file. I previously wrote the code to just read the XML file sequentially but a problem arises when shared strings are not in the same order as they are in the spreadsheet. However now they are cached in memory (up to a limit, so as not to exhaust the available memory), so XLSX reading should be much speedier. Also, the library reads XLS, CSV, ODS and various delimited text files just fine. – pilsetnieks Mar 13 '13 at 11:33
  • Thanks Nouveau - spreadsheet-reader is working very well for me. I don't suppose you are looking into writing an excel writer? – Kage Mar 15 '13 at 11:30
2

As far as my experiences go, excel implementations in php don't have a good performance (it took perl implementations 8 minutes to write what php was doing for 3 hours) so I end up using Perl.

The only think you can really do about problem with running out of memory is to set higher memory_limit.

Vyktor
  • 20,559
  • 6
  • 64
  • 96
  • Have a look at this one. http://www.bettina-attack.de/jonny/view.php/projects/php_writeexcel/demo/. Just implemented it and it's writing 1000 rows of 30 columns in a few seconds. It's a PHP conversion of a Perl script – Kage Oct 15 '12 at 11:51
  • @Kage I need to write 30 000 rows :) after 10 000 rows all php implementations I found back in 2011 were incredibly slow. – Vyktor Oct 15 '12 at 12:04
  • Vyktor. Not sure what you consider slow but the script I mention above outputs approximately 10,000 rows each with 34 columns of data (about 6mb) in about 15 seconds. Would a perl script be much faster than this? – Kage Oct 15 '12 at 14:43
  • @Kage Ah 10k rows was a threshold when it was working, then time complexity started to increase ... fast :) 20k took more than 20 minutes – Vyktor Oct 15 '12 at 18:50
0

For writing you might want to give a try to http://pear.php.net/package/Spreadsheet_Excel_Writer

edlerd
  • 2,145
  • 1
  • 16
  • 24
-1

I would recommend PHPExcel. It's easy to work with and easy to change functionality in if that would be needed. I have used it a lot and never had any issues, even with big complicated spreadsheets.

inquam
  • 12,664
  • 15
  • 61
  • 101
  • 1
    As I state in my question, I've already been using PHPExcel – Kage Oct 15 '12 at 11:50
  • 1
    Sorry I missread. There is another package called php_excel or something like that which I thought was the one you tried. My bad :P – inquam Oct 16 '12 at 05:39
-2

PHPExcel Class This class can:

  1. read
  2. write
saidOnder
  • 15
  • 6