XLS 表中的日期未正确解析 [英] Date in XLS sheet not parsing correctly
问题描述
我正在尝试使用带有日期的列的XLSX"节点模块读取 XLS 文件.解析文件后,我发现日期与工作表中的日期相差很少.这就是我所做的.
I am trying to read a XLS file using 'XLSX' node-module with a column having dates. After parsing the file what I found is that the dates are of few dates back from that of the dates in the sheet. This is what I a doing.
var workbook = XLSX.readFile(filePath);
var grossPayoutSheet = workbook.Sheets[worksheets[1]];
for (var i in grossPayoutSheet) {
if (i[0] === "!") continue;
var col = (!isNaN(parseInt(i.substring(1)))) ? i.substring(0,1) : i.substring(0,2);
var row = (!isNaN(parseInt(i.substring(1)))) ? parseInt(i.substring(1)) : parseInt(i.substring(2));
var value = grossPayoutSheet[i].v;
if (row === 2) {
var value = grossPayoutSheet[i].v;
headers[col] = value.trim();
continue;
}
if (row !== 1 && !data[row]) {
data[row] = {};
} else if (row !== 1){
data[row][headers[col]] = value;
}
}
单元格B3中的值为
05/07/2017
但解析后的值是
B3: { t: 'n', v: 42921, w: '42921' }
B3: { t: 'n', v: 42921, w: '42921' }
我想要字符串格式的日期,即我想将单元格格式从n"更改为s"
I want the date in string format that is I want to change the cell format from 'n' to 's'
有人可以取悦我吗?
推荐答案
所以我昨天让它工作了:
So I got it working yesterday:
import * as XLSX from 'xlsx'
import * as df from 'dateformat';
// Typescript but easy to convert
const workbook: any = XLSX.read(source, { 'type': type, cellDates: true });
// passing the option 'cellDates': true is important
// ...
// ... until you start reading cells
const cellAddress: any = { c: 5, r: 5 }; // column 5 / row 5 which is a date and formatted like that in xlsx
const cell_ref: string = XLSX.utils.encode_cell(cellAddress);
const cell: any = worksheet[cell_ref];
if (cell) {
let value: any = cell.v;
const parsedDate: Date = new Date(value);
parsedDate.setHours(parsedDate.getHours() + timezoneOffset); // utc-dates
value = df(parsedDate, "dd/mm/yyyy");
}
这只是一个片段,但必需品在那里:-) 希望这可以帮助你!顺便说一句,我使用的是 xlsx 文件.
This is just a snippet but the essentials are in there :-) Hope this might help you! Btw I am using a xlsx-file.
诀窍是传递选项cellDates: true",然后从数字值中实例化一个日期,它应该可以解决.由于 JS 中的 utc 日期,您的日期可能会延迟几个小时,您可以通过将偏移量添加到日期来平衡.
The trick is to pass the option 'cellDates: true' and then to just instantiate a date from the numbers value and it should work out. Your date will probably be a few hours off because of the utc-dates in JS, which you can even out by adding the offset to the date.
这篇关于XLS 表中的日期未正确解析的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!