xlslib, at a brief glance, looks like it is based on the .xsl format, not .xlsx. Wondering if it is easier to generate .xlsx XML files for export, or not.
xls is not just one format. There are several formats which Excel will understand, that uses that extension. One of them is a real simple xml-based format (SpreadsheetML), that is way easier to generate than proper excel files and has most of the features you need (At least for data transport purpose). Highly recommend using it.
I second SpreadsheetML; it's plain XML (not multi-part compressed .xslx) and it supports multiple sheets, formulas, formatting, and some other bits like named ranges, print settings, and such.
It only describes the data part and doesn't document Excel options such as print settings and filters; if you need them, create a sample in Excel, save it as XML Spreadsheet 2003 and examine the result.
(The 'big' Excel format that is a part of Office Open XML format is also called Spreadsheet ML, but they're different; from what I understand this one is a subset.)
It's a deprecated format that was essentially Microsoft's first experiment at an XML based format in the Office XP/Office 2003 days... whether it will continue to be supported in future versions is anybody's guess, given that the successor format OOXML is much more widely adopted.
My impression is that Microsoft is fairly reliable when it comes to supporting old formats and standards. E.g. I can still send Word and Excel DDE commands and this is a tech from 90s, Windows 3.1 and stuff. OOXML is certainly more powerful, but it's also more complex.
I know that they are totally different, wondering if xlsx is simpler so that it would be easier code, or whether it is just as complex really, and not suer what real world compatibility is.
Nearly 4 years ago I wrote a library in Go to read XLSX - that was trivial. It's grown over time, and this year it started growing feature that allow it to write XLSX format. This is a whole different ball game - Microsoft Excel is really picky about what it will and won't accept. In most cases Excel will simply moan and do an automated repair on files that don't match exactly it's expectation. LibreOffice is less annoying in its XLSX handling.
What responsibility to stand against proprietary formats do we have? That was never my goal when becoming a developer. I want to make simple and useful tools for people, and a .xls file is the most simple and useful format when the goal is to edit data in Excel (which is the goal 99% of the time when you're not dealing with another developer).
Of course open formats are best, but sometimes they're not a good option.
Our responsibility as developers is to deliver a working solution within the parameters that we are given, not to push an agenda (no matter how strongly we may feel about it).
Like it or not, most of the businesses in the developed world are still using MS and the associated file formats, and will be for quite a while yet.
Sorry but this would only keep the proprietary file format madness that is Excel. I would prefer to share the data in JSON and let the user decide what to do with it ...
The premise of the article is that the user will want to manipulate the data in Excel, so developers should provide an Excel export instead of / as well as CSV. In this case, JSON is much, much worse than CSV.
Unless your end user is a developer, that's a distinctly unfriendly way to treat your users. Did you read the article? The point is that non-developer end users will never use CSV in anything but a spreadsheet, and Excel is the most prominent of those.
To give it to them in JSON is, well, just useless.
The majority of users have no idea what JSON is or how to even open it. So "decide what to do with it" will equate to trash it and find another website.
A lovely sentiment, but one made in total denial of reality.
Handing your average user a JSON file doesn't "let them decide", it means you've decided that their needs are less important than your holy war. That's fine, and your choice to make as a developer (unless they're paying you), but don't pretend it's in the user's best interest.
Lossless encoding of doubles is a long solved problem. Use hexadecimal floating point format (%a with printf in C99, inherited by most high-level languages that sit on top of C). The problem is platforms that still don't support C99.
I was wondering why he said that CSV was not capable of supporting full precision for doubles. Are you saying that the problem is not with CSV per se, but instead the C libc printf function? I'd have thought that modern standard libraries would do this right with the usual decimal %f and a large enough precision: http://pubs.opengroup.org/onlinepubs/009695399/functions/pri...
The trouble is that (a) the standard recommends but does not require that all binary-to-decimal and decimal-to-binary conversions be correctly rounded and (b) even if it did require correctly-rounded conversions, the precision needs to be absolutely enormous to guarantee that every double is exactly representable in the chosen format (which makes it wildly wasteful), otherwise you'd get different results depending on the rounding mode in effect at the time of conversion back to double.
An alternative is to provide data as ressources that can be imported using Excel web queries. I don't thnik vanilla excel can be linked to JSON ressources, but a simple HTML table does the trick. Excel Web queries can use parameters, making the use of several ressources faster vs the download of several files. As a bonus you can set the Excel file for the client, with your data ready and up to date in it.
As a developer, it hasn't been easy to make an excel file in the past and it's not a core feature for the business so it gets relegated to a mediocre CSV implementation with minimal effort.
CSV can be consumed by anything (with the caveats mentioned in the article), XLS/XLSX by spreadsheets only )if you want to work with the data).
Excel files have row limitations, potential file format incompatibilities.
Depending on the language being used, it can be the same level of effort to emit XLS file as a CSV file, so unless we are talking about programmers how don't bother learning how to use output libraries, I suspect CSV is more common for reasons of compatibility.
99.99% is a made-up stat, and highly dependent on audience.
Noting Excel can open CSV natively (again with the caveats mentioned in the post), my users want a garage not limited to cars, but trucks and bicycles too.
Excel 2004 is basically the Mac version of Excel 2003. It's EoL'd too.
As for the article, personally I'd say drop XLS support and go for XLSX. The more that users are aware their version of Excel is no longer supported, the more noise they'll make about wanting upgrades. Plus, it's not limiting, in the sense you can get free tools that open XLSX (LibreOffice, etc...).
I would imagine there'd be XLSX-handling libraries for most mainstream programming languages. I've used XlsxWriter with Python, it's fairly intuitive...
https://xlsxwriter.readthedocs.org/
We do a ton of Excel import/export in our web app. The day we moved to XLSX, every one of our users cheered because the reliability of our Excel related features improved dramatically.
The problem with XLS is that its internal format (called BIFF8) has no official specification. It has been reverse engineered to a great degree, but every implementation I've used outside of actual "excel.exe" has show stopping bugs that you will encounter at some point.
XLSX, on the other hand, has an actual published spec (OOXML). There is plenty of political strife surrounding OOXML, but at least we have a spec we can develop against. It has also been my experience that "simple" XLSX files can be constructed more reliably than their BIFF8 counterparts. Because XLSX files are XML internally, there are a great number of libraries that can be used to construct the required XML structures, while avoiding edge-case errors in composition that are inherent to reverse-engineered binary formats. At a bare minimum, software authors can use something like libxml to construct valid XML, rather than some ad hoc BIFF8 serializer.
FWIW, we use the axlsx gem for our Ruby app, and it hasn't let us down yet. It even supports some pretty eccentric Excel features like data validations.
Just expose an odata endpoint than can be consumed by excel, various other analytic tools as well as whatever programming language you want since it is just rest.
So for users that we are thinking are going to have too much trouble converting a csv to an excel document, we will instead use a custom data source.
I dont think that solution really works for people using it at the level he is talking about.
"Data is ultimately meant to be consumed by people."
I disagree. Information is consumed by people. Data underlies information, and sometimes is mistaken for it. But raw data is absolutely parsed and processed before being consumed by the average audience. Some analytic folk will read raw data, but only to serve a higher level purpose - to answer some question or gain some knowledge.
But in general, data is analyzed and filtered and summarized and visualized before being consumed. This author may just work too close to the data on a day to day basis to realize where the average audience really fits.
If we want to talk about ease of use for the consumer, we need to ask what kind of person downloads datasets from websites.
Not normal people, that's for sure. The target demographic for that is probably 5% researchers and 95% idly curious developers and statisticians.
The researchers can take the time to deal with an XLS format, but why would they want to? The developers and statisticians are doing this in their off time, and are going to want or need (I wouldn't expect a statistician to be able to work with the XLS documentation) an easier format to deal with. So if you give it to them in XLS, they're just going to have to turn it into a CSV immediately before starting to hack up an interpreter for it.
So why not skip the middle man and deliver it in CSV? This author has the situation almost exactly 180 degrees backwards.
Apple has failed to support OpenDocument Spreadsheets, and their XLS importer regularly crashes altogether for the operations people at my office, though maybe it's super stable elsewhere.
"When they export their data as CSV, they’ll probably just bring it into Excel first to have a look around."
If the first thing the the user is doing is bringing the file into excel and you don't need multiple tables per file what is the difference to the end user other than the file having a csv instead of a xls or xlsx file extension?
its right there in the article: Dates, times, and durations; Percentages; Number formatting
the number of times i've opened a csv to find that excel has tried to determine the dates, and decided that the year thats being referred to is 1914 instead of 2014...
I've never had an issue like you describe with dates before. I've always output them in the csv in Y-m-d format and that has worked as expected.
I should have been more precise and said if your data isn't using the set of features described in the article there isn't a big difference. Not all data has dates, time durations, percentages, and number formatting.
I'm a long term enterprise user of Excel, and I taught myself to program using VB6 and have now moved onto Postgres/Python etc. For any serious data application, CSV is a must, because navigating data types in Excel is a nightmare. Over and over, colleagues of mine have lost the leading zero in a string because Excel automatically typed it as a number, or had a cell value interpreted as a formula, or had a VLOOKUP not work because the number is typed as a string or vice versa. These problems often have counterintuitive solutions, and for a basic user, these issues exist in both excel formats and in csv. Excel has pretty robust ODBC integration that drives data directly into Pivot Tables or through VBA. In this way you can provide informative (and pretty) reports to end users, with dynamic aggregations, filters etc. If your users are more technical than that or require more detailed atomic data, the widely compatible solution is generally csv. What I'm trying to say is that the excel format fills a gap that is very small, between completely non-technical and slightly technical data users, and honestly probably isn't worth implementing.
I have a product that lets our users (who are non-technical) export data. CSV exports were a source of many support requests. We switched to XLSX exports and everybody seems happy.
I've seen a weird hack where an export to excel option was provided, but what was actually exported was an html file of some sort with a .xls extension that happened to open fine in Excel. I think it even had different tabs.
If you just create a text file and put an old fashioned html table in it and rename it to .xls Excel will complain a little, and then open it, and even respect font tags like <b> and <i>
I use Google Refine (OpenRefine you name it) for exploring data, it's more powerful than excel for visualizations, have a lot of ways to order it. It's amazing.
62 comments
[ 3.4 ms ] story [ 125 ms ] threadhttp://msdn.microsoft.com/en-us/library/aa140066%28office.10...
It only describes the data part and doesn't document Excel options such as print settings and filters; if you need them, create a sample in Excel, save it as XML Spreadsheet 2003 and examine the result.
(The 'big' Excel format that is a part of Office Open XML format is also called Spreadsheet ML, but they're different; from what I understand this one is a subset.)
https://gist.github.com/troelskn/cf9df474662c88ba7951
Of course open formats are best, but sometimes they're not a good option.
Like it or not, most of the businesses in the developed world are still using MS and the associated file formats, and will be for quite a while yet.
To give it to them in JSON is, well, just useless.
Handing your average user a JSON file doesn't "let them decide", it means you've decided that their needs are less important than your holy war. That's fine, and your choice to make as a developer (unless they're paying you), but don't pretend it's in the user's best interest.
https://github.com/OfficeDev/Open-XML-SDK
Someone has also created a javascript version of the SDK - http://openxmlsdkjs.codeplex.com/
I've always assumed that programmers output to CSV because they are too lazy to implement an Excel output function.
Excel files have row limitations, potential file format incompatibilities.
Depending on the language being used, it can be the same level of effort to emit XLS file as a CSV file, so unless we are talking about programmers how don't bother learning how to use output libraries, I suspect CSV is more common for reasons of compatibility.
That's the thing. For 99.99% of users there is no anything. There is only Excel. And dealing with CSV files is headache for them.
It's as if you designed parking garages to accommodate both horses and cars - because someone out there might be coming on a horse.
Noting Excel can open CSV natively (again with the caveats mentioned in the post), my users want a garage not limited to cars, but trucks and bicycles too.
When did he write this? It says Nov 2014 at the top but that doesn't seem accurate.
Then again the WinXP EoL should have changed that, but watch it not have.
As for the article, personally I'd say drop XLS support and go for XLSX. The more that users are aware their version of Excel is no longer supported, the more noise they'll make about wanting upgrades. Plus, it's not limiting, in the sense you can get free tools that open XLSX (LibreOffice, etc...).
I would imagine there'd be XLSX-handling libraries for most mainstream programming languages. I've used XlsxWriter with Python, it's fairly intuitive... https://xlsxwriter.readthedocs.org/
The problem with XLS is that its internal format (called BIFF8) has no official specification. It has been reverse engineered to a great degree, but every implementation I've used outside of actual "excel.exe" has show stopping bugs that you will encounter at some point.
XLSX, on the other hand, has an actual published spec (OOXML). There is plenty of political strife surrounding OOXML, but at least we have a spec we can develop against. It has also been my experience that "simple" XLSX files can be constructed more reliably than their BIFF8 counterparts. Because XLSX files are XML internally, there are a great number of libraries that can be used to construct the required XML structures, while avoiding edge-case errors in composition that are inherent to reverse-engineered binary formats. At a bare minimum, software authors can use something like libxml to construct valid XML, rather than some ad hoc BIFF8 serializer.
FWIW, we use the axlsx gem for our Ruby app, and it hasn't let us down yet. It even supports some pretty eccentric Excel features like data validations.
I disagree. Information is consumed by people. Data underlies information, and sometimes is mistaken for it. But raw data is absolutely parsed and processed before being consumed by the average audience. Some analytic folk will read raw data, but only to serve a higher level purpose - to answer some question or gain some knowledge.
But in general, data is analyzed and filtered and summarized and visualized before being consumed. This author may just work too close to the data on a day to day basis to realize where the average audience really fits.
If we want to talk about ease of use for the consumer, we need to ask what kind of person downloads datasets from websites.
Not normal people, that's for sure. The target demographic for that is probably 5% researchers and 95% idly curious developers and statisticians.
The researchers can take the time to deal with an XLS format, but why would they want to? The developers and statisticians are doing this in their off time, and are going to want or need (I wouldn't expect a statistician to be able to work with the XLS documentation) an easier format to deal with. So if you give it to them in XLS, they're just going to have to turn it into a CSV immediately before starting to hack up an interpreter for it.
So why not skip the middle man and deliver it in CSV? This author has the situation almost exactly 180 degrees backwards.
I work in JSON mostly do wrapped the excellent XLSX library it was very useful https://goo.gl/S7rlFm
Apple has failed to support OpenDocument Spreadsheets, and their XLS importer regularly crashes altogether for the operations people at my office, though maybe it's super stable elsewhere.
XLSX works fine, though (for the features that Numbers supports)
If the first thing the the user is doing is bringing the file into excel and you don't need multiple tables per file what is the difference to the end user other than the file having a csv instead of a xls or xlsx file extension?
the number of times i've opened a csv to find that excel has tried to determine the dates, and decided that the year thats being referred to is 1914 instead of 2014...
I should have been more precise and said if your data isn't using the set of features described in the article there isn't a big difference. Not all data has dates, time durations, percentages, and number formatting.
If you just create a text file and put an old fashioned html table in it and rename it to .xls Excel will complain a little, and then open it, and even respect font tags like <b> and <i>