Sheetjs: Convert date column in excel to normal format (yyyy-mm-dd), current format is 41934

[brunoqs]

My date column shows numbers like 45123 instead of showing yyyy-mm-dd, how can i read this excel file and generate a json with a correct date format.

thats my function to read xlsx:

const xlsxtojson = (path, headers) => {
    const workbook = XLSX.readFile(path);
    let jsonData = [];
    jsonData = XLSX.utils.sheet_to_json(workbook.Sheets[workbook.SheetNames[0]], { header: headers, range: 1 });
    return jsonData;
}

[brunoqs]

Heres the solution:

const xlsxtojson = (path, headers) => {
    const workbook = XLSX.readFile(path);
    let jsonData = [];
    const targetColumn = XLSX.utils.decode_col('N'); // data do auto - column
    const ws = workbook.Sheets[workbook.SheetNames[0]];
    const range = XLSX.utils.decode_range(ws['!ref']);
    for (let i = range.s.r + 1; i <= range.e.r; i += 1) {
      const ref = XLSX.utils.encode_cell({ r: i, c: targetColumn });
      if (!ws[ref]) {
        continue;
      }
      ws[ref].t = 'd';
      ws[ref].w = 'yyyy-mm-dd';
      let date = XLSX.SSF.parse_date_code(ws[ref].v, { date1904: workbook.Workbook.WBProps.date1904 });
      ws[ref].v = moment(`${date.y}-${date.m}-${date.d}`, 'YYYY-MM-DD').format('YYYY-MM-DD')
    }
    jsonData = XLSX.utils.sheet_to_json(ws, { header: headers, range: 1 });
    return jsonData;
}

[demon-zhonglin]

还可以试试在读文件(XLSX.read)的时候进行一些设置:
XLSX.read(data, { type: 'binary', cellDates: true })

cellDates: 将日期存储为类型d(默认为n)(类型详情: https://github.com/SheetJS/sheetjs#data-types)
设置完后，重新读取文件时，你会发现此时值为 js 的 Date 对象

===================================
You can try this when reading files(XLSX.read):
XLSX.read(data, { type: 'binary', cellDates: true })

cellDates: Store dates as type d (default is n) (Data Types: https://github.com/SheetJS/sheetjs#data-types)

You will find that the current value is a js Date object

[brunoqs]

@demon-zhonglin I tried this, but the dates continued with strange numbers

[cooljeffro]

The format is the number of days since Jan 1, 1900. However, Excel incorrectly counts 1900 as a leap year.

To get the date using moment:
moment('1900-01-01').add(45123 - 2, 'days').format('YYYY-MM-DD').

This will work for dates after 1900-02-28.

[zzhihang]

还可以试试在读文件(XLSX.read)的时候进行一些设置:
XLSX.read(data, { type: 'binary', cellDates: true })

cellDates: 将日期存储为类型d(默认为n)(类型详情: https://github.com/SheetJS/sheetjs#data-types)
设置完后，重新读取文件时，你会发现此时值为 js 的 Date 对象

===================================
You can try this when reading files(XLSX.read):
XLSX.read(data, { type: 'binary', cellDates: true })

cellDates: Store dates as type d (default is n) (Data Types: https://github.com/SheetJS/sheetjs#data-types)

You will find that the current value is a js Date object

我传入了2020/12/4，但是解析出来的是2020-12-03T15:59:17.000Z 少一天呢

[zzhihang]

The format is the number of days since Jan 1, 1900. However, Excel incorrectly counts 1900 as a leap year.

To get the date using moment:
moment('1900-01-01').add(45123 - 2, 'days').format('YYYY-MM-DD').

This will work for dates after 1900-02-28.

hi, I try to import the date of 2020/12/10, then I get 44175, when I use moment('1900-01-01').add(44175 - 2, 'days').format('YYYY-MM-DD') I got the result of "2020-12-12"