4 ms·

XLS and XLSX are neither clear nor unambiguous and I would take 12 CSV formats over either any day. I actually haven't ever met anyone who seriously had to extr

XLS and XLSX are neither clear nor unambiguous and I would take 12 CSV formats over either any day. I actually haven't ever met anyone who seriously had to extract data from XLS/X frequently that didn't revile it for its ambiguities. Not to mention the ways that the application munges the user input prior to saving it, leaving the biggest ambiguity of all, what the heck is ACTUALLY in that cell. EDIT: I will add that EXPORTING to XLS/X is usually quite straightforward and makes users happier than getting a CSV often times. Until they start wanting to get crazy with template design and then you get into Excel styling application and you're back to hating it all over again.

arethuza 10y ago
Actually, there are some commercial libraries that make working with even complex Excel spreadsheets pretty straightforward. I've used Aspose.Cells a lot and was very happy with it: http://www.aspose.com/products/cells http://www.aspose.com/products/cells [NB I have no connection with Aspose, just been a happy customer for many years].
barrkel 10y ago
The single biggest problem is date / time formats. Excel encodes dates and times as floating point numbers. The date / time-ness of a cell is encoded in its format. Some of the formats are hard-coded, some float depending on locale, and some use explicit format-pictures embedded in the styles. But of course there's no guarantee that the user has formatted a cell with a date as a date. Sometimes the user has pasted a US-format date into a copy of Excel running in a UK locale, so dates that fit have day and month swapped, while dates that don't fit show up as strings. This is non-trivial to handle in the general case. I've had to add customer-specific workarounds for this case in particular.
- mathw 10y ago
  You've unleashed a flood of horrible memories of reading XLS and XLSX files with dates in. Thanks a bunch. You'd think when Microsoft came up with their new shiny file formats they'd actually make them new and shiny and good. But nope.

arethuza 10y ago

Actually, there are some commercial libraries that make working with even complex Excel spreadsheets pretty straightforward. I've used Aspose.Cells a lot and was very happy with it: http://www.aspose.com/products/cells http://www.aspose.com/products/cells [NB I have no connection with Aspose, just been a happy customer for many years].

barrkel 10y ago

The single biggest problem is date / time formats. Excel encodes dates and times as floating point numbers. The date / time-ness of a cell is encoded in its format. Some of the formats are hard-coded, some float depending on locale, and some use explicit format-pictures embedded in the styles. But of course there's no guarantee that the user has formatted a cell with a date as a date. Sometimes the user has pasted a US-format date into a copy of Excel running in a UK locale, so dates that fit have day and month swapped, while dates that don't fit show up as strings. This is non-trivial to handle in the general case. I've had to add customer-specific workarounds for this case in particular.

mathw 10y ago
You've unleashed a flood of horrible memories of reading XLS and XLSX files with dates in. Thanks a bunch. You'd think when Microsoft came up with their new shiny file formats they'd actually make them new and shiny and good. But nope.