Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Date conversion loses 1 day #1470

Open
svenschaefer74 opened this issue Apr 1, 2019 · 12 comments

Comments

@svenschaefer74
Copy link

commented Apr 1, 2019

Hello,

when I import a specific xlsx file some of the dates are imported correctly and others get a wrong date which is [(date in file) - 1 day].

Attached you'll find a file (test.xlsx) that only contains two rows of data. The first one (row no. 4) imports fine and the second (row no. 5) leads to the error. You can see this in column J which is called 'Order date'.

If I look in the Chrome Javascript Debugger the imported field seems to have an associated timezone information. I've tried to transfer the number formating in Excel from a working cell to the non working cells but this had no effect. While importing the data it doesn't make any change if I use readtype.raw true or false.

I'm using Win10, Chrome 73.0.3683.86 and version 0.14.0 of js-xlsx.

@svenschaefer74

This comment has been minimized.

Copy link
Author

commented Apr 1, 2019

I just found out that I was blind and missed #1435.
I've updated to version 0.14.2 and tried to import my data but the result is still wrong.
I will try to implement the suggested fix #1212 in the xlsx.js.

@mareek

This comment has been minimized.

Copy link

commented Apr 2, 2019

This bug might be linked to #1212.
I've created a pull request ( SheetJS/ssf#38 ) to fix the problem

@svenschaefer74

This comment has been minimized.

Copy link
Author

commented Apr 2, 2019

I implemented all the changes that are proposed in #1212. My code to read in the xlsx file looks like

wb = XLSX.read(data, {type:'binary', cellDates:true, dateNF:'dd.mm.yyyy'});
var tableData = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]], {range:2});

My data is still not read correctly. The most obvious problem is that the code with the proposed change from #1212 is never executed. I've also tried to change the format string as it is proposed in #718 . The cell data is interpreted as a date as the following screenshot shows:

image

BTW: I'm using Excel 2016.

@shiny

This comment has been minimized.

Copy link

commented Apr 3, 2019

My date in excel is 2019-03-04 and finally i got 2019-03-03T15:59:17.000Z when XLSX.utils.sheet_to_json. It should be 2019-03-03T16:00:00.000Z , my timezone is +8

@13banda

This comment has been minimized.

Copy link

commented Apr 6, 2019

+1 i am also facing same problem

@lllllllai27

This comment has been minimized.

Copy link

commented Apr 15, 2019

My date in excel is 2019-03-04 and finally i got 2019-03-03T15:59:17.000Z when XLSX.utils.sheet_to_json. It should be 2019-03-03T16:00:00.000Z , my timezone is +8

Have you fixed this issue now? my timezone is +8 also

@shiny

This comment has been minimized.

Copy link

commented Apr 15, 2019

My date in excel is 2019-03-04 and finally i got 2019-03-03T15:59:17.000Z when XLSX.utils.sheet_to_json. It should be 2019-03-03T16:00:00.000Z , my timezone is +8

Have you fixed this issue now? my timezone is +8 also

My temporary solution is moment(date).add(1, 'hours');
Whatever this bug is fixed or no, we will get a correct date.

@chunsli

This comment has been minimized.

Copy link

commented May 29, 2019

I am also facing the same issue, its basically dedutcts 4x seconds from my time stamp

@bellotti92

This comment has been minimized.

Copy link

commented May 29, 2019

I am also facing the same issue, its basically dedutcts 4x seconds from my time stamp

I'm with the same problem, but deducts -3 hours from my time. @SheetJSDev can u help us?

@myfonj

This comment has been minimized.

Copy link

commented Jun 4, 2019

Perhaps stupid question, but isn't excel date by definition supposed to contain NO time information?

It seems all JS Date instances returned in XLSX.utils.sheet_to_json POJO representation should be set to "midnight" in local time, what I understand is quite reasonable way to mimic excel "timeless" dates.

I've ran into this issue as well and for now I am hotfixing it by looking at hours and shifting them to next (my timezone offset is -60 / -120) days midnight if they weren't set to midnight.

(Hotfix)
/**
 * Correct days shift https://github.com/SheetJS/js-xlsx/issues/1470
 * ! WARNING: not thoroughly verified, no leap days nor leap seconds are taken into account
 * @param {Array.<Object>} rowsList
 * @example
 * var workbook = XLSX.read(data, { type: "array", cellDates: true });
 * workbook.SheetNames.forEach(function(name){
 * 	var sheet = workbook.Sheets[name];
 * 	var pojo = XLSX.utils.sheet_to_json(sheet, { raw: true, defval: null })
 * 	hotfixXLSXdateCells(pojo);
 * 	// pojo contains shifted dates
 * })
 */
function hotfixXLSXdateCells(rowsList) {
	var key, dateKeys = [];
	// assuming we can watch just the first row for Date cells
	// if your table is scarce and contains Date cells anywhere, you have to check each cell
	for (key in rowsList[0]) {
		if (rowsList[0][key] && rowsList[0][key].constructor && rowsList[0][key].constructor === Date) {
			dateKeys.push(key);
		}
	}
	var i = -1, j, row;
	while (row = rowsList[++i]) {
		j = -1
		while (key = dateKeys[++j]) {
			hotfixDateCell(row, key);
		}
	}
	/**
	 * @param {Object} row 
	 * @param {string} key 
	 */
	function hotfixDateCell(row, key) {
		/**
		 * @type {Date}
		 */
		var d = row[key];
		if (!d || !row[key].constructor || row[key].constructor !== Date) {
			// in case some dates are missing compared to first row
			return;
		}
		if (d.getHours() != 0) {
			if (d.getTimezoneOffset() < 0) {
				d.setHours(24, 0, 0, 0);
			} else {
				//! WARNING: I have not verified this (Wrong hemisphere)
				d.setHours(-24, 0, 0, 0);
			}
		}
	}
}
@chendye

This comment has been minimized.

Copy link

commented Jun 12, 2019

My date in excel is 2019-03-04 and finally i got 2019-03-03T15:59:17.000Z when XLSX.utils.sheet_to_json. It should be 2019-03-03T16:00:00.000Z , my timezone is +8

excel 的时间从 1900/1/0(1899/12/30)开始 ,此时对中国用的上海时区(GMT+0805)+8:05:43,1800~1900年。https://www.timeanddate.com/time/zone/china/shanghai

getTimezoneOffset 获取的分钟数,忽略了43秒,所以会少 43 秒
https://github.com/SheetJS/ssf/blob/master/bits/35_datecode.js

@YaliixxG

This comment has been minimized.

Copy link

commented Aug 16, 2019

最快速的方法是把exce的日期那一栏的格式改成文本 ....

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
10 participants
You can’t perform that action at this time.