Parsing Excel Dates with Node.js xlsx Library

Parsing Dates in Excel with Node.js xlsx Library

When working with Excel files in a Node.js environment, one common task is to parse dates correctly. Excel stores dates as serial numbers where each integer represents a day, with day 0 being January 0, 1900. Time is represented by the fractional part of the number. This can complicate things when you're trying to convert these serial numbers to JavaScript Date objects, which are based on a time value that is the number of milliseconds since 1 January 1970 UTC.

Thankfully, with the xlsx library, we can read Excel files and convert these serial numbers to JavaScript Date objects. Here's a step-by-step guide on how to do it.

Step 1: Read the Excel File

First, you need to read the Excel file using the xlsx library. This is done by loading the file and parsing it into a workbook object.

import * as XLSX from 'xlsx';

const workbook = XLSX.readFile('your_excel_file.xlsx');
const sheetName = workbook.SheetNames[0];
const worksheet = workbook.Sheets[sheetName];

Step 2: Parse the Data

After you have your worksheet, you can convert it to a JSON object for easier manipulation.

const data = XLSX.utils.sheet_to_json(worksheet);

Step 3: Convert Excel Dates to JavaScript Dates

Now comes the tricky part: converting the Excel date serial numbers to JavaScript Date objects. Here's a simple function to do that:

function excelDateToJSDate(date: number) {
  const jsDate = new Date((date - 25569) * 86400000);
  return jsDate;
}

In the function above, date is the Excel date serial number. The number 25569 is the number of days between January 1, 1900, and January 1, 1970 (Excel's base date and Unix time's base date, respectively). The number 86400000 is the number of milliseconds in a day (24 hours * 60 minutes * 60 seconds * 1000 milliseconds).

Step 4: Apply the Conversion to Your Data

Now that you have the conversion function, you can apply it to each date in your dataset.

data.forEach(row => {
  const excelDate = row['YourDateColumnName'];
  if (typeof excelDate === 'number') {
    const jsDate = excelDateToJSDate(excelDate);
    row['YourDateColumnName'] = jsDate;
  }
});

Replace YourDateColumnName with the actual field key of your date column in the Excel file.

Conclusion

Parsing dates from Excel files in a Node.js environment can be a bit confusing due to the difference in how Excel and JavaScript handle dates. However, with the xlsx library and a simple conversion function, it becomes a straightforward task.

Remember to always check the actual format of the dates in your Excel files, as different settings or versions of Excel might affect the way dates are stored and displayed.

By following these steps, you can accurately parse dates from Excel files and use them as JavaScript Date objects in your Node.js applications, ensuring that your data is handled correctly and efficiently.