Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript and Excel DB

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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.


  2. 0

    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 of sheet_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 the dateNF field set to your date format, it may resolve your issue.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search