1

My PHP app needs to read Excel files of arbitrary sizes uploaded by users without data loss.

  • Pure PHP libraries are only suitable for very small files.

  • ODBC would seem the obvious option, but my tests with Microsoft drivers (the only ones I've found) reveal that they are painfully unreliable (and poorly documented).

  • MSDN mentions endless additional acronymims (MDAC, OleDB, ADODB...) but, in the end, they all seem to be wrappers on top of ODBC drivers with all their flaws.

What's the industry standard to read Microsoft Excel files from PHP on a Windows server?

Álvaro González
  • 142,137
  • 41
  • 261
  • 360
  • Another high rep member gave this answer http://stackoverflow.com/a/563679/ – Funk Forty Niner Aug 14 '14 at 12:07
  • Here's another http://www.paggard.com/projects/xls.reader/ apparently produced by MS Excel. I quote: *"XLS Reader is a PHP class, that makes it possible to read binary XLS files (produced by MS Excel) using PHP only. This class does not require any additional PHP modules or COM objects and work an all operating systems without any dependences if MS Excel is installed or not."* – Funk Forty Niner Aug 14 '14 at 12:14
  • Thanks for your pointers but I've extensively explored that path. The thing is I do have a Windows computer: I can install additional PHP modules. Or call COM objects. On anything. I don't need to wait 30 minutes until PHP exhausts system RAM and crashes when some native piece of software can read the file in 10 seconds. – Álvaro González Aug 14 '14 at 12:26
  • 1
    You're welcome and I'm sure you have explored that path; just as I too have looked into that in the past (spent 2-3 days actually), but found no other method to do the job effectively. As far as I know, there doesn't seem to be an industry standard *per se*. I searched through IBM's developers section and did find a few, but they mostly mention to save the file as an XML spreadsheet. – Funk Forty Niner Aug 14 '14 at 12:31
  • This is leaving the trail for a moment: Can the Excel file be converted to CSV (stable input structure)? Anytime I've worked with Excel data - though not with PHP - loading CSV files directly into a database ended up to be the best way to handle them. Even if you still want to process the data before persisting them, you could just create a temporary DB table on the fly and work from there. – matt_jay Aug 14 '14 at 12:40
  • @codejak I'm actually considering that option, either by requiring direct CSV upload or by implementing [on-the-fly conversion](http://stackoverflow.com/questions/1858195/convert-xls-to-csv-on-command-line). I guess that, unless I somehow get an answer to this question, reading Excel will become one of the things I no longer accept doing. – Álvaro González Aug 14 '14 at 12:43
  • Funny you should say that's an option (DB). The IBM developers section I mentioned earlier contains DB code etc. If you haven't stumbled upon it and that you're interested to look at, am including the link to it: http://www.ibm.com/developerworks/library/os-phpexcel/ – Funk Forty Niner Aug 14 '14 at 12:45
  • 1
    @ÁlvaroG.Vicario: you should already be aware of it, but do consider that asking users to create CSV files can create a new set of nightmares ;-) So on-the-fly conversion should probably be preferred. – matt_jay Aug 14 '14 at 12:50

1 Answers1

0

Unless proved wrong, I'd dare say that running Windows does not provide any particular competitive advantage when faced with the task of reading Excel data from PHP. There's no tool that allows fast and reliable direct access to data so you have to use the same hacks you'd use on any other OS.

Transparent conversion to CSV stands out as an interesting workaround but, while it can be done with Excel itself (use COM to instantiate an Application.Excel object, assuming you can afford a Microsoft Office licence just for that) there're equivalent cross-platform solutions using LibreOffice or Gnumeric.

To give a tentative answer to the question: there's probably no industry standard at all.

Community
  • 1
  • 1
Álvaro González
  • 142,137
  • 41
  • 261
  • 360