CODEWITHSUNDEEP
javascriptexceldatetimeexceljs
Lucas M. N. Xavier
Lucas M. N. Xavier

Reputation: 1

Excel generated using ExcelJS lib is not treating HH:MM column as hour

I'm applying the mask "HH:MM" on every cell of a specific column, but the Excel generated doesn't consider the cells as HH:MM unless I select it, so it doesn't sum the hours, it only count the cells, how can I fix it?

worksheet1.getColumn(5).eachCell({ includeEmpty: false }, function(cell, rowNumber) {
    if(rowNumber > 1) {
        cell.style = {
            numRmt: "[h]:mm"
        };
    }
});
worksheet1.autoFilter = {
    from: { row: 1, column: 1 },
    to: { row: 1, column: 7 }
};

How it is treated on the Excel:

How it is treated on the Excel

How it is treated after I clicked on the cells:

How it is treated after I clicked on the cells

I tried to create a new Date object and set it's hours and minutes with what I wanted and feeding this object into the cell value instead. This made Excel correctly consider the value as a Date, but the auto sum got messed up.

Upvotes: 0

Views: 880

Answers (2)

andy
andy

Reputation: 13

edited

In order for Excel to treat a date type cell as a time-only type cell you need to put in a certain date (also make sure you put in your timezones gmt offset)

For a non-1904-based workbook this would be Sat Dec 30 1899:

const gmt = 1 // GMT+0100
yourRow.getCell(yourCell).value = new Date(1899, 11, 30, 8 + gmt, 52)

this will give you 08:52:00 in your spreadsheet cell, formated as time cell type

beware: when using a non-1904-based workbook there seems to be some rounding error so you might want to add at least one millisecond:

const gmt = 1 // GMT+0100
let date = new Date(1899, 11, 30, 8 + gmt, 52);
date.setSeconds(0, 1); // zero seconds, one millisecond
yourRow.getCell(yourCell).value = date;

that doesn't fix it completely, it only gives you a correct formating for a single time cell - as soon as you start doing math with multiple of those the error will come into play again...

also in a non-1904-based workbook there can't be any negative times!

therefor I would suggest: use a 1904-based workbook!

For a 1904-based workbook the date in question would be Fri Jan 01 1904:

yourWorkbook.properties.date1904 = true;

const gmt = 1 // GMT+0100
yourRow.getCell(yourCell).value = new Date(1904, 0, 1, 8 + gmt, 52)

Now, that should do the trick and give you a "correct" 08:52:00 time cell type value

Upvotes: 0

AngYC
AngYC

Reputation: 3903

Based on the documentation: https://github.com/exceljs/exceljs#styles

It seems like the correct way to apply styles to a cell instead of column is by using the numFmt property directly instead of style.numFmt. Try the following and see whether it solves your issue:

worksheet1
  .getColumn(5)
  .eachCell({ includeEmpty: false }, function (cell, rowNumber) {
    if (rowNumber > 1) {
      cell.numFmt = '[h]:mm';
    }
  });

In addition, based on the code, it seems like you are trying to override the internal style property, which can have unexpected side effects: https://github.com/exceljs/exceljs/blob/2ab468b8bae2c8d5844f922d61ac7d960fee26be/lib/doc/cell.js#L55-L57

Upvotes: 0

Related Questions