英文:
xlsx convert date format to random number
问题
我正在将文件传递给这个函数，并希望将数据转换为JSON，但对于特定的列DOB 05-04-2001，要将其转换为随机数，如36986。我希望从这个函数中得到输入的确切日期作为响应，该怎么做？有人可以帮我吗？
英文:
I am passing a file to this function and i want to cnvert the data to json but for the specific column DOB 05-04-2001 to random number such as 36986. I want the exact date entered as a response from this function, How to do this ? Can anyone help me out yrr.
static convertExcelFileToJsonUsingXlsxTrimSpaces = (path: string) => {
// Read the file using pathname
const file = xlsx.readFile(path);
// Grab the sheet info from the file
const sheetNames = file.SheetNames;
const totalSheets = sheetNames.length;
// Variable to store our data
const parsedData: any[] = [];
// Loop through sheets
for (let i = 0; i < totalSheets; i += 1) {
// Convert to json using xlsx
const sheetData = xlsx.utils.sheet_to_json(file.Sheets[sheetNames[i]], { raw: true });
console.log(sheetData);
// Process each row to handle tab-separated values
const processedData: any[] = [];
sheetData.forEach((row: any) => {
const processedRow: any = {};
Object.keys(row).forEach((key) => {
processedRow[key] = row[key].toString().trim(); // Convert to string and trim whitespace
});
processedData.push(processedRow);
});
// Add the sheet's processed data to our data array
parsedData.push(...processedData);
}
return parsedData;
};
答案1
得分: 1
这不是随机数，而是日期单元格的默认解析方式。
要获取日期值，请在解析选项中添加 cellDates: true 标志：
尝试这样做：
const sheetData = xlsx.utils.sheet_to_json(file.Sheets[sheetNames[i]], { raw: true, cellDates: true });
查看文档：
默认情况下，Excel 将日期存储为带有指定日期处理格式的数字。例如，日期 19-Feb-17 存储为数字 42785，并带有日期格式为 d-mmm-yy。
所有解析器的默认行为都是生成数字单元格。将 cellDates 设置为 true 将强制生成器存储日期。
英文:
It's not random number, it's default parsing for date cells.
To get date value, add cellDates: true flag to parsing options:
Try this:
const sheetData = xlsx.utils.sheet_to_json(file.Sheets[sheetNames[i]], { raw: true, cellDates: true });
see the docs:
> By default, Excel stores dates as numbers with a format code that
> specifies date processing. For example, the date 19-Feb-17 is stored
> as the number 42785 with a number format of d-mmm-yy
>
> The default behavior for all parsers is to generate number cells.
> Setting cellDates to true will force the generators to store dates.
>
> https://www.npmjs.com/package/xlsx#dates
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论