Fixing a conversion bug and adding ability to output json #1

2013-02-17T22:05:39Z

nathanathan commented

2013-02-17 22:05:39 +00:00

(Migrated from github.com)

Here's a link where you can see the changes in action.

[Here's a link where you can see the changes in action.](http://nathanathan.github.com/js-xlsx/)

Niggler commented

2013-02-18 01:06:32 +00:00

(Migrated from github.com)

I like the general idea of JSON output, but here are a few things to mull over (I'm not saying these are wrong, but JSON forces a certain type of structure which doesn't fit all XLSX sheets):

If the first row is a sheet title (not the column headers), then the result seems to lose information. For example, convert this to XLSX and try it:

sheet title,,
col a,col b,col c
a,b,c
,,
,,
d,e,f
g,h,
,,i

yields

{
  "Sheet1": [
    {
      "sheet title": "col a",
      "undefined": "col c"
    },
    {
      "sheet title": "a",
      "undefined": "c"
    },
    {
      "sheet title": "d",
      "undefined": "f"
    },
    {
      "sheet title": "g",
      "undefined": "h"
    },
    {
      "undefined": "i"
    }
  ]
}

BTW: It's not wrong to require the structure you are assuming here

sheets with numbers throw an error (but that's a minor fix)
In the aforementioned example, gaps in the sheet are not retained (there are two empty rows, but the JSON output doesn't reflect that). Again, it's not wrong to explicitly require that the data is contiguous

I guess these lead to the general question: What are the advantages of having the script do the work rather than using a CSV to JSON converter (aside from additional work and additional dependencies)?

I like the general idea of JSON output, but here are a few things to mull over (I'm not saying these are wrong, but JSON forces a certain type of structure which doesn't fit all XLSX sheets): 1) If the first row is a sheet title (not the column headers), then the result seems to lose information. For example, convert this to XLSX and try it: ``` sheet title,, col a,col b,col c a,b,c ,, ,, d,e,f g,h, ,,i ``` yields ``` { "Sheet1": [ { "sheet title": "col a", "undefined": "col c" }, { "sheet title": "a", "undefined": "c" }, { "sheet title": "d", "undefined": "f" }, { "sheet title": "g", "undefined": "h" }, { "undefined": "i" } ] } ``` BTW: It's not wrong to require the structure you are assuming here 2) sheets with numbers throw an error (but that's a minor fix) 3) In the aforementioned example, gaps in the sheet are not retained (there are two empty rows, but the JSON output doesn't reflect that). Again, it's not wrong to explicitly require that the data is contiguous I guess these lead to the general question: What are the advantages of having the script do the work rather than using a CSV to JSON converter (aside from additional work and additional dependencies)?

nathanathan commented

2013-02-18 01:44:55 +00:00

(Migrated from github.com)

sheet_to_row_object_array is just a convenience function, but it's a really nice one to have. I don't know of a better format for dealing with tabular data in Javascript. For example, I can use underscore collection functions to essentially query my tables (e.g.

_.where(row_object_array, {name:"foo"}); //Returns all the entries for people named named foo

).

W.r.t. 2: It would be nice to preserve the type of numbers, bools and datetimes from excel, however strings are sufficient for the project I'm working on right now.

sheet_to_row_object_array is just a convenience function, but it's a really nice one to have. I don't know of a better format for dealing with tabular data in Javascript. For example, I can use [underscore collection functions](http://underscorejs.org/#collections) to essentially query my tables (e.g. ``` javascript _.where(row_object_array, {name:"foo"}); //Returns all the entries for people named named foo ``` ). W.r.t. 2: It would be nice to preserve the type of numbers, bools and datetimes from excel, however strings are sufficient for the project I'm working on right now.