How horrible is PHPExcel, and what to use instead?
-
So yeah, I have to do this Excel shit now... I heard horror stories about PHPExcel (
@Arantor) already, so I'm weary of it. Is it particularly horrible, or just annoying? Do note I don't need to worry about shared hosting or whatever, so I can take a RAM hit as long as it isn't too bad.In case there's a preferable replacement, my requirements are:
- XLSX, I don't care about XLS, fuck 'em!
- Writing, reading is not something I need right away but it would be nice, if the best solution is combining libraries suggestions for the reader are welcome
- Able to define column types manually (any auto-guessing is bound to fuck up with this data cough leading zeros that I need preserved cough)
- Charts, because someone is going to ask for it sooner or later and I don't feel like rewriting this any time soon
- Doesn't blow up in PHP 7 but not PHP 7 exclusive if possible, 5.6 support would be nice so I can defer system updates for a bit on a few systems which take longer to arrange the whole upgrade thing for
- PSR4 support welcome, but I can live without it
-
-
@raceprouk said in How horrible is PHPExcel, and what to use instead?:
@onyx said in How horrible is PHPExcel, and what to use instead?:
I'm w
eary of itFTFY ;)
Potato, poteato
Flied under: I am perfec
-
@onyx problem is, it's pretty much the only PHP tool. It does export, it does XLSX, they claim it averages 1KB per cell for memory use, but I'm not convinced that's true.
You can set what format you want things in, it runs on PHP 5.3 and up, there is probably an autoloading version though it will handle this itself for the most part.
Don't know about charts.
Big thing I would say is that it isn't fast.
-
@arantor Yeah, I checked the feature list and it's nice in that regard. Charts are supported.
For the autoloader, apparently I should use PHPSpreadsheet instead which does have it and is properly namespaced, which is a rewrite but the API is still unstable, apparently, so I'm not sure if I should mess with it.
I have found another one (https://github.com/mk-j/PHP_XLSXWriter/) which is rather fast and allegedly doesn't have RAM problems, but it doesn't have charts and can't read the files. Also, the docs are rather... sparse.
-
@arantor Is the Excel tool you wrote owned by your current employer?
-
@onyx How tied are you to PHP?
If you can use any .NET language, there's an excellent Office Interop library available. Not sure what OSes it runs on, though, or if it requires the original Office DLLs to use (meaning: you'd have to have a copy of Office on the server.)
@raceprouk said in How horrible is PHPExcel, and what to use instead?:
FTFY
It's "Coding Help" not "Spelling Help", go be a pedantic dickweed elsewhere.
-
@raceprouk said in How horrible is PHPExcel, and what to use instead?:
@onyx said in How horrible is PHPExcel, and what to use instead?:
I'm w
eary of itFTFY ;)
He's working in PHP. Pretty sure they're both correct.
-
-
@blakeyrat said in How horrible is PHPExcel, and what to use instead?:
have a copy of Office on the server.)
For whatever reason, my last employer was extremely against that idea...
-
@blakeyrat said in How horrible is PHPExcel, and what to use instead?:
How tied are you to PHP?
The entire web backend is PHP. Also, I don't Office DLLs won't work on Linux, so.
I think the same thing you're suggesting is doable with LibreOffice as well, at least up to a point, but it requires the executable (not dynamic libraries only), and AFAIK it's impossible to install it without pulling in a bunch of GUI libraries + the X server, which I'm not keen on at all.
-
@tsaukpaetra said in How horrible is PHPExcel, and what to use instead?:
@blakeyrat said in How horrible is PHPExcel, and what to use instead?:
have a copy of Office on the server.)
For whatever reason, my last employer was extremely against that idea...
I'm pretty sure the MS license does not allow that. (Forget where I read that - probably here...)
Of course, what the license allows and what people do... well, we know how people behave.
-
@tsaukpaetra said in How horrible is PHPExcel, and what to use instead?:
For whatever reason, my last employer was extremely against that idea...
There are security implications, but the Interop is using the Office libraries, it's not running the actual applications, so that shouldn't (I think?) be much of an issue.
-
@jaloopa said in How horrible is PHPExcel, and what to use instead?:
@arantor Is the Excel tool you wrote owned by your current employer?
It is but I could probably knock together a clean room writing implementation, though I don't give enough fucks about proper data types (nearly everything is a string) and it didn't do charts or reading files.
-
@blakeyrat said in How horrible is PHPExcel, and what to use instead?:
@onyx How tied are you to PHP?
If you can use any .NET language, there's an excellent Office Interop library available. Not sure what OSes it runs on, though, or if it requires the original Office DLLs to use (meaning: you'd have to have a copy of Office on the server.)Oh I remember using this in one project.
It was PITA.
Try explaining to your client's suspicious admin why they need to install Office on their server to run your software (and where do they get the license).
This would be my absolute LAST option in the world.
-
@blakeyrat said in How horrible is PHPExcel, and what to use instead?:
@raceprouk said in How horrible is PHPExcel, and what to use instead?:
FTFY
It's "Coding Help" not "Spelling Help", go be a pedantic dickweed elsewhere.
Me correcting the usage of two very different words is a fockton more helpful than suggesting a hideously expensive and inefficient solution in completely the wrong technology.
-
@dcon said in How horrible is PHPExcel, and what to use instead?:
@tsaukpaetra said in How horrible is PHPExcel, and what to use instead?:
@blakeyrat said in How horrible is PHPExcel, and what to use instead?:
have a copy of Office on the server.)
For whatever reason, my last employer was extremely against that idea...
I'm pretty sure the MS license does not allow that. (Forget where I read that - probably here...)
Of course, what the license allows and what people do... well, we know how people behave.I believe it requires a CAL in addition to the appropriate office license.
-
@arantor said in How horrible is PHPExcel, and what to use instead?:
averages 1KB per cell for memory use
Wat. Seriously, is that reasonable? It seems like it should be off by a couple orders of magnitude.
-
@dreikin said in How horrible is PHPExcel, and what to use instead?:
@arantor said in How horrible is PHPExcel, and what to use instead?:
averages 1KB per cell for memory use
Wat. Seriously, is that reasonable? It seems like it should be off by a couple orders of magnitude.
Well, it's gotta hold the cell info which includes three different representations of the data, formatting, Meta data, metadata about the Meta data, and the XML representation of the above.
-
@tsaukpaetra close but not quite.
PHPExcel supports old Excel, new Excel and other stuff. When it imports or creates dynamically from code, it builds an array of worksheets, which contain an array of rows, which each contain an array of cells.
Each cell is an instance of the PHPExcel_Cell class, which contains details like cell content, data type, font styling, fill styling and all the other stuff Excel can hold in a cell, even down to formulas and calculating the results of formulas for you, as Excel would.
There is a surprising amount of meta data bundled into Excel and its format because formatting wise, for example, there is the font (which PHPExcel enumerates in full for each cell, which has bold, italic, underline (which isn't Boolean), strikethrough, size and family, plus a colour. Cells then have a fill style plus foreground and background for that fill. Then on top of that you have to deal with themes. All of which PHPExcel renders in detail because it doesn't know what you're going to do with that data, e.g. import XLS and export XLSX. Or vice versa, that's a thing too.
So you have this in-memory model of the entire spreadsheet, rendered for completion purposes and accuracy purposes rather than using any of the shortcuts that the file formats themselves might use such as deduplicating strings across a file into an array (and then only storing the array index as the cell value)
Given my use case was almost pure data with minimal formatting, but literal millions of cells, I could easily run a server with 32GB of RAM out of RAM if I ran these reporting jobs concurrently.
It doesn't help that this stuff doesn't exactly GC well in PHP.
-
@arantor said in How horrible is PHPExcel, and what to use instead?:
underline (which isn't Boolean)
Double underline. For when a single underline just isn't enough...
-
@tsaukpaetra exactly, and for bonus points, all of the other formatting items are in the XML as
b="0"orstrike="0"but instead ofu="0"for no underline oru="1"for single underline, you getu="none"and IIRCu="single"because fuck consistency or logic.
-
@arantor said in How horrible is PHPExcel, and what to use instead?:
fuck consistency or logic
Oh come on, it's not like they had the time to properly think this through when they were forcing the format through the ISO standardisation process.
-
Well, seems I'll be going with PHPExcel. I'll try out PHPSpreadsheet as well, if it's noticeably better I might bite the bullet on potentially breaking changes and fix shit as it comes for the benefit of not having to port everything from scratch once it goes stable.
Currently I'm not worried about concurrent reports given the manner in which the software will be used, and given that I have some sort of estimate of the final size using @Arantor's numbers I can work out some sort of queue to minimize potential problems.
Turing have mercy on my black soul.
