Sheetjs: date format in sheet_to_json

[zhangge3992513]

How do I get the content in the excel table without converting the format?
I don't want to convert it.
in my excel,has a auto format cell : 2017/6/13 14:13:12
xlsx get that: 6/13/17 14:13
I need the data: 2017/6/13 14:13:12
and I need to save the data to postgresql

[zhangge3992513]

set
cellText:false,
dateNF:'yyyy/mm/dd h:mm:ss'

maybe I solve my question,
but,the dateNF convert '16:12:55' to '1900/01/00 16:12:55'
I just need '16:12:55'.

I wanna cry!😭

[SheetJSDev]

@zhangge3992513 set cellDates:true in the read function, then every cell encoding a date will have a JS Date value.

If you know that certain dates are really times, then change those cells! Here's how you change the format of cell B2 in sheet Sheet1:

var worksheet = workbook.Sheets['Sheet1'];
var cell = worksheet['B2'];
delete cell.w; // remove old formatted text
cell.z = 'h:mm:ss'; // set cell format
/* OPTIONAL */
XLSX.utils.format_cell(cell); // this refreshes the formatted text.

[zhangge3992513]

Well, thank you. That solves my problem.But I don't think it's a very good way.
I have a suggestion:
Add a time type and add timeNF in Parsing Options,
maybe it will be very convenient to format time .

[SheetJSDev]

The real problem is the default date format is localized. See https://github.com/SheetJS/js-xlsx/issues/326#issuecomment-286014758 for a larger discussion. If you set your computer settings to US English, you will see 6/13/17 14:13, but in other locales you will see different date formats.

This is a known issue with our formatter library https://github.com/SheetJS/ssf, so we'll mark this issue as closed and update once we figure out international support

[jcalfee]

Is there a way to get the formatted date and time strings (unaltered just as recorded in the spreadsheet) in the JSON output?

The cellDates formatter option converts it to UTC and changes the date or time. It appears that cellText: true is what I need, but when I have that enabled I just get numbers in the JSON Date and Time fields.

My code is coded to sheet_to_json but due to the logic I need, I'm finding it very hard to lookup the cell from any point in the JSON. If I could, I'm after the w value from any place in the JSON.

> wb.Sheets.WebhookTest.R2
{ t: 'n', v: 44084, w: '10/09/2020' }

> wb.Sheets.WebhookTest.S2
{ t: 'n', v: 44091.3958333333, w: '9:30 AM' }