I am uploading a zip folder from ui which has a excel file. I have written following code
getZipData(file: File) {
const jsZip = require('jszip');
let excelRecord: any[] = [];
let images: { path: string, image: string }[] = [];
let fileList: any[] = [];
const reader = new FileReader();
jsZip.loadAsync(file).then((zip: any) => {
Object.keys(zip.files).forEach((filename) => {
if (!zip.files[filename].dir && filename.includes('xlsx')) {
zip.files[filename].async('ArrayBuffer').then((fileData: any) => {
const data = new Uint8Array(fileData);
const arr = new Array();
for (let i = 0; i != data.length; ++i) {
arr[i] = String.fromCharCode(data[i]);
}
const bstr = arr.join("");
const workbook = XLSX.read(fileData, { type: "binary" });
const first_sheet_name = workbook.SheetNames[0];
const worksheet = workbook.Sheets[first_sheet_name];
excelRecord = XLSX.utils.sheet_to_json(worksheet);
console.log(JSON.stringify(excelRecord));
//{name:"abcd",birthdate:36141} <---- this is the output
});
}
})
})
}
In the output date (12-12-1998) is not parsed correctly It should be {name:“abcd”,birthdate:“12-12-1998”} When i parse this date manually it creates 01/01/1970. How can i get exact date?
The issue you’re facing with Excel date parsing is due to Excel storing dates as serial numbers, which is the number of days since January 1, 1900 (or 1904, depending on the system). When you extract data directly as a number (such as 36141), it’s being interpreted as a number rather than a date.
To fix this, you need to ensure that the cell content is properly parsed as a date. The XLSX.utils.sheet_to_json method by default doesn’t automatically convert these serial date values to a human-readable date format. However, you can use the raw option to get the raw values and then manually convert any date values.
Here’s an updated version of your code to handle the conversion of Excel serial dates to actual date strings:
Solution:
You can convert the Excel serial date number to a JavaScript Date object using the XLSX.utils.format_cell() function and then format it as needed.
Updated Code:
getZipData(file: File) {
const jsZip = require('jszip');
let excelRecord: any[] = [];
let images: { path: string, image: string }[] = [];
let fileList: any[] = [];
const reader = new FileReader();
jsZip.loadAsync(file).then((zip: any) => {
Object.keys(zip.files).forEach((filename) => {
if (!zip.files[filename].dir && filename.includes('xlsx')) {
zip.files[filename].async('ArrayBuffer').then((fileData: any) => {
const workbook = XLSX.read(fileData, { type: 'array' });
// Get the first sheet
const first_sheet_name = workbook.SheetNames[0];
const worksheet = workbook.Sheets[first_sheet_name];
// Convert the sheet to JSON
excelRecord = XLSX.utils.sheet_to_json(worksheet, { raw: false });
// Optionally, manually format dates if necessary
excelRecord = excelRecord.map(row => {
for (const key in row) {
if (row[key] instanceof Date) {
// Format the date as 'DD-MM-YYYY' or whatever format you need
row[key] = row[key].toLocaleDateString('en-GB'); // For "DD/MM/YYYY" format
}
}
return row;
});
console.log(JSON.stringify(excelRecord));
});
}
})
})
}
Key Changes:
raw: false option in sheet_to_json: This tells the XLSX library to try to interpret the date values properly. This automatically converts Excel serial dates to JavaScript Date objects, which you can format as needed.
Date Conversion: After converting to JSON, I used the toLocaleDateString('en-GB') method to format the date as DD/MM/YYYY. You can change this format to whatever you need.
Explanation:
Excel stores dates as serial numbers, like 36141, which corresponds to 12/12/1998.
By setting raw: false, XLSX.utils.sheet_to_json will convert those serial numbers into JavaScript Date objects.
After converting to Date, I use the toLocaleDateString() method to format it in DD/MM/YYYY format (you can adjust the format for different locales or preferences).
This should give you the correct date format, like: