Search code examples
node.jsxlsx

Nodejs-xlsx: reading time issue

I am trying to read an excel file that has two columns 'Start Time' and 'End Time', and the data is in the form '8:00 AM','10:15 AM', etc.

The value in the field is 8:30 AM

When I try to read the excel file using xlsx module with no options specified

var workbook = XLSX.readFile('sample-excel.xlsx');

I get this.

output

When I try this

var workbook = XLSX.readFile('sample-excel.xlsx',{cellDates:true});

I get this.

output

Please help, I want to store the text as it is that is json = {time:'8:30 AM'}

Solution

  • The time field in excel is of date type, so you need to parse the output.

    Here is the code to parse date time

    const moment = require('moment')

var momentDate = moment('1899-12-30T03:00:00.000Z')

var hour = momentDate.hours();
var minutes = momentDate.minutes();
var seconds = momentDate.seconds();
console.log(hour,minutes,seconds);

// or you can use `.format`:
console.log(momentDate.format("hh:mm:ss A"));

    You refer this thread for details