exceljs read Date Cell with format

Question

I've a problem when I'm reading a Date value from columns in a excel file using exceljs package, I can't get the truth value in the correct format like a Date data type, instead I get a number.

My project is build with Nest, and I use exceljs to write/read xlsx files, the code is:

worksheet.eachRow({ includeEmpty: true }, (row, rowNumber) => {
    row.eachCell((cell, colNumber) => {

        ...

         const header = headers[colNumber - 1];
            switch (header) {
            ...
            case 'dateColumn':
                // Date format is dd/mm/yyyy
                // Original value in excel is '16/06/2020'
                const cellValue: Date = cell.value as Date; // When i get value from cell is 43998
                console.log(cellValue); // print 43998
                break;
            ...
    }
}

The cell format is 'dd/mm/yyyy'

score 4 · Answer 1 · answered Dec 09 '20 at 11:13

4

You will need to convert Excel Date Serial Number to JS Date.

Here is a very basic example:

const date0 = new Date(0);
const utcOffset = date0.getTimezoneOffset();

const cellValue = new Date(0, 0, cell.value - 1, 0, -utcOffset, 0);

answered Dec 09 '20 at 11:13

Alx Rodav

479
3
10

score 1 · Answer 2 · answered Mar 21 '21 at 08:47

1

To convert Excel Serial Date to normal you can use

new Date(Date.UTC(0, 0, cell.value - 1, 0, 0, 0))

answered Mar 21 '21 at 08:47

Vladimir

361
1
3
14

exceljs read Date Cell with format

2 Answers2