Time format in Excel using excel4node

Question

I am trying to print the number of hours in excel sheet using excel4node application. although the number of hours is getting printed in the excel, I am not able to get the sum or average while selecting that column.

Its only counting the columns not calculating the sum of the hours.

I am expecting the average while selecting the columns (Marked in RED color)

But what I am actually getting is counting of columns(Marked in RED color)

 ws.cell(i+2,j+2).string(value).style(bodystyle);

value here contains the number of hours.

Ask me for more Clarification.

Answer 1

1. turn the value into a date object and use setUTCHours & setUTCMinutes functions to set the desired time.
2. In the workbook settings set dateFormat attribute to hh:mm .. something like

const ws = new xl.Workbook({ dateFormat: 'hh:mm' });

3. Use .date() function instead of .string()

Example

const [hours, minutes] = value.split(':');
const date = new Date();
date.setUTCHours(hours);
date.setUTCMinutes(minutes);
-----
const ws = new xl.Workbook({ dateFormat: 'hh:mm' });
-----
ws.cell(i+2,j+2).date(date).style(bodystyle);

There will be a problem though ... this will not work as expected if the sum of the hours is 24 or more.