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

Created on 4 Feb 2015 · 8Comments · Source: SheetJS/sheetjs

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!

Source

rdmurphy

Most helpful comment

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!

rdmurphy on 4 Feb 2015

👍8 🎉4

All 8 comments

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!

rdmurphy on 4 Feb 2015

👍8 🎉4

@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.

SheetJSDev on 4 Feb 2015

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

IgorShch on 18 Nov 2015

👍3

Agreed with @IgorShch

dinesh36 on 9 Sep 2016

Thank you very much sir! @rdmurphy

ghuroo on 3 Jan 2017

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. :)

sgibson21 on 10 Nov 2018

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

ToanTNM on 14 Nov 2018

👍1

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