Anyway to tell sheet_to_json to output with the cell's value instead of text?

[rdmurphy]

Hello!

Been using XLSX.utils.sheet_to_json, but hit a snag. The Excel workbook has a sheet with cells filled with integers. But when I run sheet_to_json, it spits them out as strings.

Using this as the example:

A2: { t: 'n', v: 1995, w: '1995' },
B2: { t: 'n', v: 27, w: '27' },
C2: { t: 'n', v: 4, w: '4' },

I assume this is because it fills out object with the cell's text representation – w – instead of v. Is there anyway for me to tell it to use v instead?

I'm currently looping through the generated object to alter the keys I know should be numbers, but this tool would ideally not require the user to customize the converter per sheet. That could quickly get out of hand.

Thanks!

[rdmurphy]

Ha, went and poked on the code, and found it pretty quickly.

XLSX.utils.sheet_to_json(worksheet, {raw: true});

It'd be good to document this!

[SheetJSDev]

@rdmurphy the default behavior is driven by the original problem, which was to produce a CSV identical to what you would get if you saved as CSV in Excel. It might actually make sense to flip the default here.

[IgorShch]

Raw option is actually very good. It's a shame it's not documented.

[dinesh36]

Agreed with @IgorShch

[ghuroo]

Thank you very much sir! @rdmurphy

[sgibson21]

I had this problem only the other way around. sheet_to_json was giving me numbers when I wanted strings. Looks like the raw option defaulted to true for me. So I explicitly set it to false and now I get strings. :)

[ToanTNM]

Thanks sgibson2. It's seem raw is true by default in newest version. I've get datetime values with correct formatted text.

[cemremengu]

I was pulling my hair for the last two days when my unit tests started to blow up 😄 I first suspected DST change but turns out that it was the raw option

With raw: true I was getting incorrect date values like

  O2: { t: 'd', v: 2016-03-31T20:59:04.000Z, w: '2016-04-01' },
  R2: { t: 'd', v: 2016-03-31T20:59:04.000Z, w: '2016-04-01' },
  S2: { t: 'd', v: 2017-10-30T20:59:04.000Z, w: '2017-10-31' },

The correct ones are the w ones so setting raw: false fixed it

cc @SheetJSDev this may be a bug actually