Was there a particular reason that this library was written? The only thing I can glean from the repo is that it seems to be faster than C# alternatives.
I had a project where I had to translate a frequently updated Excel workbook containing lots of complicated formulas into a PHP project. So I parsed each cell and transpiled the Excel stuff into PHP, and the ran the resulting code.
The reason was Excel’s lack of an API. The formulas themselves were too complicated for existing solutions, and kept breaking them. I only built what was necessary so it worked out quicker.
No idea if that’s the same as the original use case, but it this kind of library looks like it does the same kind of thing on a more general level.
The kind of API I was looking for but couldn’t find (in 2014) was “when I change the value of cell A1 to 17, what’s the value of cell C5?” And get the answer within a couple of seconds. There was no supported way to programmatically change the value of a cell and get the calculated result of a different cell, at least not that I could find.
Somebody needed to read excel files from Rust, possibly not from Windows? That would also be the reason why openpyxl and xlrd exist for Python: http://www.python-excel.org
e.g. importable data using real spreadsheet files is much more reliable than CSV, and easier for clients to generate.
They're "technically" Microsoft Office XML-format[1], and not "Excel files", but users don't tend to be amused by programmers being technically right so I tend not to argue about that sort of thing. I only bring it up here in case someone wants to search for a specification on it.
this is a spreadsheet ml2003 and not supporting it is not a big deal since this format is probably not widely used in the wild.
Basically this file should need a .xml file format, but excel will read files with .xls as well (besides that this is not correct).
Basically the XLS was/is a binary format and XLSX is the new spreadsheet ml 2007+ syntax that is standardised.
(edit: well the good part of the xml-files were that they could be streamed. this is / was huge for data processing and exporting a la csv)
I think that's the format you get for Salesforce reports, and as it's neither the old XLS nor the zipped-xml xlsx, quite a few tools have issues importing that, not just this lib. And then we're back to the legion of CSV variants…
I'd argue that you must at least attempt deal with csvs before you can claim to be an excel file reader because end-users will be supplying you with whatever format their reports are in.
"Be flexible in what you accept" seems especially apt here.
Also, Excel will read the old BIFF files (XLS), CSVs, SYLK, and whatnot. You are doing, in reverse, exactly that which you oppose: "unless this library can open each and every and any file that Excel can, bug-for-bug compatible, it's Wrong!"
(While it is true that users will toss anything at you, it's quite pointless to expect one library to handle all of it: we used one to sniff the content, another to parse CSVs, yet another to parse XLSX, and in-house code for this obscure abomination which sadly still gets about 10% of use)
> it's quite pointless to expect one library to handle all of it
Libraries don't just come with implementation, but also a use model. Perl, for example has:
Spreadsheet::XLSX
Spreadsheet::ParseExcel
These have very different user (programmer) interfaces -- so different that now there's a Spreadsheet::ParseXLSX which just allows programmers (and programs) used to one interface, consume the new files with very little changes.
This rust library has it's own model, and if it's a good model it makes sense to implement other file formats with this model -- and indeed this is a good way to test if it's a good model.
Other things like CSV are so fundamentally different from XLS/XLSX that they're going to have a different model, and so it makes sense that these require different libraries.
True, PHP has its own classes for building XML/HTML[0], although using it to build or parse HTML (say, from a cURL request) is a bit hairy. Proper headers should be sent as well.
Which C# libraries were used in comparison? The 2 I know of, EPPlus and ClosedXML, do much more than just read cell values, so it's unfair to compare against those. For example the additional complexity of handling styles adds overhead.
For me, the gold standard of these libraries is Apache POI and NPOI. Porting to Rust would be perfectly possible, but it would be _a lot_ of work. Until you support that level of functionality, it's pretty much irrelevant what your "performance" is. You can always improve performance by dropping features.
There was a time I had to read XLS (BIFF formats) in PHP. Poked around, found some libraries (slow as hell), decided to port[1] one of them (python's xlrd).
What followed, even though it was almost 1:1 port, was an inevitable loss of huge part of my life, I'll never get back. However, I learned a lot about what a mess that format is. Since then ANY xml format Microsoft uses is godsent.
37 comments
[ 3.2 ms ] story [ 76.9 ms ] threadThe reason was Excel’s lack of an API. The formulas themselves were too complicated for existing solutions, and kept breaking them. I only built what was necessary so it worked out quicker.
No idea if that’s the same as the original use case, but it this kind of library looks like it does the same kind of thing on a more general level.
https://github.com/OfficeDev/Open-Xml-Sdk
They also support being used on .NET Core.
Is the situation different in 2017?
Disclaimer: I'm a current ClosedXML contributor.
e.g. importable data using real spreadsheet files is much more reliable than CSV, and easier for clients to generate.
It can also be more a pita since cells can have different formats.
Here's a little Excel-file writer in PHP; Excel will read these files, but this library won't:
They're "technically" Microsoft Office XML-format[1], and not "Excel files", but users don't tend to be amused by programmers being technically right so I tend not to argue about that sort of thing. I only bring it up here in case someone wants to search for a specification on it.[1]: https://en.wikipedia.org/wiki/Microsoft_Office_XML_formats
(edit: well the good part of the xml-files were that they could be streamed. this is / was huge for data processing and exporting a la csv)
"Be flexible in what you accept" seems especially apt here.
Does this work with libreoffice? Do you have a sample on hand that I can test?
It works with libreoffice. It also works with gnumeric.
The output of the program is:
https://www.dropbox.com/s/9joeudijfav3pe7/a.xls?dl=1
(although it was short enough to fit in a comment, so you might just have tried running it!)
A format that's trivial to generate in a single pass with no special packing and zipping requirements is a godsend. It supports Word documents too!
(While it is true that users will toss anything at you, it's quite pointless to expect one library to handle all of it: we used one to sniff the content, another to parse CSVs, yet another to parse XLSX, and in-house code for this obscure abomination which sadly still gets about 10% of use)
Libraries don't just come with implementation, but also a use model. Perl, for example has:
Spreadsheet::XLSX
Spreadsheet::ParseExcel
These have very different user (programmer) interfaces -- so different that now there's a Spreadsheet::ParseXLSX which just allows programmers (and programs) used to one interface, consume the new files with very little changes.
This rust library has it's own model, and if it's a good model it makes sense to implement other file formats with this model -- and indeed this is a good way to test if it's a good model.
Other things like CSV are so fundamentally different from XLS/XLSX that they're going to have a different model, and so it makes sense that these require different libraries.
[0]http://php.net/manual/en/class.domdocument.php
Disclaimer: I'm a current ClosedXML contributor.
What followed, even though it was almost 1:1 port, was an inevitable loss of huge part of my life, I'll never get back. However, I learned a lot about what a mess that format is. Since then ANY xml format Microsoft uses is godsent.
[1]: https://github.com/laacz/xls-reader