I made a Javascript nodejs project that stores data in Excel.
I can access all columns in the entire file without any problems, but the value of the cells containing time information(12:30) is given as numbers(0.6423611111111112)
.
Is it possible to import this time value in time format(12:30) into the Javascript environment?
const XLSX = require('xlsx');
const workbook = XLSX.readFile('file.xlsx');
var allPages= []
workbook.SheetNames.forEach(function(sheetName)
{
var XL_row_object =
XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);
allPages.push(XL_row_object)
})
console.log(allPages[0][1].time)
These codes did not produce the results I wanted.
2
Answers
I cannot import the time information as 12:30:00 in Excel to the javascript-nodejs side in the same format. It comes as 0.520833333.
So it doesn’t seem that the
sheet_to_row_object_array
is part of the api?Anyways, the issue you’re seeing is probably due to that very functions way of decoding rows. Let’s say it treats all rows as ints/numbers, and it then encounters ":". Granted, I don’t know if the library is clever enough, such that it makes type assertions, but my guess is that there’s some common algorithm somewhere and that it doesn’t.
I think a better bet is to use
sheet_to_csv
and then manually split on "," and cast to the desired type (Date in your case).*Edit*
You may want to look at array object method map. It’ll do the
forEach
and assignment in one command.*Edit*
Ah, I found the
sheet_to_row_object_array
function in the source code, however it’s just an alias ofsheet_to_json
. If we read the documentation, we can pass a options object to that function. So if you pass it an options object with thedateNF
field set to your date format, it may resolve your issue.