Date support #17

New Issue

2013-03-22T17:30:27Z

wbrandongeorge commented

2013-03-22 17:30:27 +00:00

(Migrated from github.com)

I created a simple spreadsheet with a few dates in a few of the cells (ie, "12/01/2012"). It looks like the value (cell.v param) returns a number (it appears to be the number of days since 1/1/1900). Is there anyway that xlsx.js could do this date conversion automatically so that cell.v returns a date string? I know there is date formatting, etc, but even if it's just a M/D/Y date string, that would be great. In the raw sheet xml there is a "s=1" on all of my date cells.

This library is great, BTW - thanks for your work.

I created a simple spreadsheet with a few dates in a few of the cells (ie, "12/01/2012"). It looks like the value (cell.v param) returns a number (it appears to be the number of days since 1/1/1900). Is there anyway that xlsx.js could do this date conversion automatically so that cell.v returns a date string? I know there is date formatting, etc, but even if it's just a M/D/Y date string, that would be great. In the raw sheet xml there is a "s=1" on all of my date cells. This library is great, BTW - thanks for your work.

Niggler commented

2013-03-22 19:58:04 +00:00

(Migrated from github.com)

@wbrandongeorge sorry for the delay in getting back.

The reason I haven't put in dates yet is because of an excel bug: 1900 was not a leap year, but Excel treats 2/29/1900 as an existing date (Try creating a cell with value 60 and then changing the format to date). I think it may be safe to just assume all date values are beyond 60 (in which case translating is easy) but I held off until someone asked for it :)

If you aren't using dates from 1900 (please confirm that is the case) then I will add JS date parsing.

@wbrandongeorge sorry for the delay in getting back. The reason I haven't put in dates yet is because of an excel bug: 1900 was not a leap year, but Excel treats 2/29/1900 as an existing date (Try creating a cell with value 60 and then changing the format to date). I _think_ it may be safe to just assume all date values are beyond 60 (in which case translating is easy) but I held off until someone asked for it :) If you aren't using dates from 1900 (please confirm that is the case) then I will add JS date parsing.

wbrandongeorge commented

2013-03-22 20:11:38 +00:00

(Migrated from github.com)

Interesting bug :) I'm not worried at all about dates from 1900. It would be awesome if you added JS date parsing - thanks again.

Niggler commented

2013-03-26 19:10:17 +00:00

(Migrated from github.com)

@wbrandongeorge I haven't forgotten :) I explored the problem further:

there are standard date formats (which you get if you change the format to 'Date' or if you enter something that looks like a date) -- those can easily be hardcoded and processed
there are custom formats (when you go to "format cells" and choose "custom"). There are quite a few nonstandard date formats you could use from that list, or you could create your own.

It makes more sense (because it would have to happen at one point) to solve the general problem of processing format codes. That is being developed as a separate module and will be hooked into this module when it is done. Will keep you posted :)

@wbrandongeorge I haven't forgotten :) I explored the problem further: - there are standard date formats (which you get if you change the format to 'Date' or if you enter something that looks like a date) -- those can easily be hardcoded and processed - there are custom formats (when you go to "format cells" and choose "custom"). There are quite a few nonstandard date formats you could use from that list, or you could create your own. It makes more sense (because it would have to happen at one point) to solve the general problem of processing format codes. That is being developed as a separate module and will be hooked into this module when it is done. Will keep you posted :)

wbrandongeorge commented

2013-03-26 19:54:26 +00:00

(Migrated from github.com)

Good to know. The general processing of format codes makes sense. I don't know what your priorities are, but the standard date formats are probably my biggest need. Which do you foresee as more important on your end?

Your focus and progress are exciting. Thanks for your work on this.

Good to know. The general processing of format codes makes sense. I don't know what your priorities are, but the standard date formats are probably my biggest need. Which do you foresee as more important on your end? Your focus and progress are exciting. Thanks for your work on this.

Niggler commented

2013-03-26 20:04:49 +00:00

(Migrated from github.com)

I should have the general processing code ready to go (and integrated with
both this and the js-xls project) by Friday afternoon (Good Friday presents
an opportunity to hammer this out :) If you need something sooner I could
put in a quick patch later today.

On Tue, Mar 26, 2013 at 3:54 PM, wbrandongeorge notifications@github.comwrote:

Good to know. The general processing of format codes makes sense. I don't
know what your priorities are, but the standard date formats are probably
my biggest need. Which do you foresee as more important on your end?

Your focus and progress are exciting. Thanks for your work on this.

—
Reply to this email directly or view it on GitHubhttps://github.com/Niggler/js-xlsx/issues/17#issuecomment-15484412
.

I should have the general processing code ready to go (and integrated with both this and the js-xls project) by Friday afternoon (Good Friday presents an opportunity to hammer this out :) If you need something sooner I could put in a quick patch later today. On Tue, Mar 26, 2013 at 3:54 PM, wbrandongeorge notifications@github.comwrote: > Good to know. The general processing of format codes makes sense. I don't > know what your priorities are, but the standard date formats are probably > my biggest need. Which do you foresee as more important on your end? > > Your focus and progress are exciting. Thanks for your work on this. > > — > Reply to this email directly or view it on GitHubhttps://github.com/Niggler/js-xlsx/issues/17#issuecomment-15484412 > .

wbrandongeorge commented

2013-03-26 20:23:15 +00:00

(Migrated from github.com)

That sounds great. If it's possible to get the standard formats done sooner that would be wonderful. However, I completely understand if you have other priorities you need to address. Thank you.

Niggler commented

2013-03-27 00:05:05 +00:00

(Migrated from github.com)

@wbrandongeorge this should handle the most common date formats. Lemme know if there are any problems

wbrandongeorge commented

2013-03-27 15:04:14 +00:00

(Migrated from github.com)

This is fantastic - thanks for such a quick turnaround! One quick question - I created a few dates in a spreadsheet that use forward slashes (ie, "5/10/12"). When parsing the spreadsheet in JS, the string values contain dashes instead (ie, "05-10-12"). Is that expected behavior?

Niggler commented

2013-03-27 15:27:19 +00:00

(Migrated from github.com)

That's definitely not expected. Special characters (like - and /) should be
translated in verbatim.

I'm not in front of a computer at the moment, but can you put up a sample
file somewhere (like on dropbox) and mention how you generated the file
(you would think every generator would use the spec, but I noticed a few
issues with how google docs handles dates -- they don't emit the proper
format code for AM/PM). I have mm/dd/yyyy in my test file and I thought
that generated the correct output.

On Wednesday, March 27, 2013, wbrandongeorge wrote:

This is fantastic - thanks for such a quick turnaround! One quick question

I created a few dates in a spreadsheet that use forward slashes (ie,
"5/10/12"). When parsing the spreadsheet in JS, the string values contain
dashes instead (ie, "05-10-12"). Is that expected behavior?

—
Reply to this email directly or view it on GitHubhttps://github.com/Niggler/js-xlsx/issues/17#issuecomment-15528562
.

That's definitely not expected. Special characters (like - and /) should be translated in verbatim. I'm not in front of a computer at the moment, but can you put up a sample file somewhere (like on dropbox) and mention how you generated the file (you would think every generator would use the spec, but I noticed a few issues with how google docs handles dates -- they don't emit the proper format code for AM/PM). I have mm/dd/yyyy in my test file and I thought that generated the correct output. On Wednesday, March 27, 2013, wbrandongeorge wrote: > This is fantastic - thanks for such a quick turnaround! One quick question > - I created a few dates in a spreadsheet that use forward slashes (ie, > "5/10/12"). When parsing the spreadsheet in JS, the string values contain > dashes instead (ie, "05-10-12"). Is that expected behavior? > > — > Reply to this email directly or view it on GitHubhttps://github.com/Niggler/js-xlsx/issues/17#issuecomment-15528562 > .

wbrandongeorge commented

2013-03-27 16:01:39 +00:00

(Migrated from github.com)

Here is a link to the sample file: http://dl.dropbox.com/u/15285248/date_test.xlsx
I created the file with Excel 2011 (Mac). I created three dates in mm/dd/yyyy format. Thanks for looking at it.

Here is a link to the sample file: http://dl.dropbox.com/u/15285248/date_test.xlsx I created the file with Excel 2011 (Mac). I created three dates in mm/dd/yyyy format. Thanks for looking at it.

Niggler commented

2013-03-27 16:57:59 +00:00

(Migrated from github.com)

@wbrandongeorge the spec is not agreeing with excel on that particular format.

format ID 14 is stated as "mm-dd-yy" (part 1, page 1768) for some strange reason. I'll go with excel on this one

@wbrandongeorge the spec is not agreeing with excel on that particular format. format ID 14 is stated as "mm-dd-yy" (part 1, page 1768) for some strange reason. I'll go with excel on this one

Niggler commented

2013-03-27 18:39:31 +00:00

(Migrated from github.com)

@wbrandongeorge I pushed a change to force it to use the excel format rather than the ecma format. Check it out

wbrandongeorge commented

2013-03-27 19:56:44 +00:00

(Migrated from github.com)

Worked great - thanks!

wbrandongeorge commented

2013-04-03 22:01:46 +00:00

(Migrated from github.com)

I have a different spreadsheet with some dates that look like they are 4 years off (when read by xlsx.js). I converted a CSV file to XLSX (by just using Save As...) in Excel for Mac. I'm guessing that for some reason that workflow (saving from CSV to XLSX) causes the offset for dates to be 1904 instead of 1900?

If you have a minute to look at it that would be great.

Here is the link to the file on dropbox: http://dl.dropbox.com/u/15285248/date_test2.xlsx

I have a different spreadsheet with some dates that look like they are 4 years off (when read by xlsx.js). I converted a CSV file to XLSX (by just using Save As...) in Excel for Mac. I'm guessing that for some reason that workflow (saving from CSV to XLSX) causes the offset for dates to be 1904 instead of 1900? If you have a minute to look at it that would be great. Here is the link to the file on dropbox: http://dl.dropbox.com/u/15285248/date_test2.xlsx

Niggler commented

2013-04-03 23:07:55 +00:00

(Migrated from github.com)

@wbrandongeorge I pushed a new commit. Check the latest version

(it appears that the formatting logic supported the 1904 dates, but the relevant flag from the XLSX sheet wasn't being passed to it)

@wbrandongeorge I pushed a new commit. Check the latest version (it appears that the formatting logic supported the 1904 dates, but the relevant flag from the XLSX sheet wasn't being passed to it)

wbrandongeorge commented

2013-04-03 23:26:10 +00:00

(Migrated from github.com)

It looks like date_test2.xlsx works now, but date_test.xlsx does not (here is the file: http://dl.dropbox.com/u/15285248/date_test.xlsx) - these dates now are 4 years too late.

Thanks for working on this.

It looks like date_test2.xlsx works now, but date_test.xlsx does not (here is the file: http://dl.dropbox.com/u/15285248/date_test.xlsx) - these dates now are 4 years too late. Thanks for working on this.

Niggler commented

2013-04-04 00:56:28 +00:00

(Migrated from github.com)

@wbrandongeorge take a peek

https://www.dropbox.com/s/nduavi5hpmpf40y/date_test_1900.xlsx

https://www.dropbox.com/s/ob44bwwfej256h2/date_test_1904.xlsx

@wbrandongeorge take a peek https://www.dropbox.com/s/nduavi5hpmpf40y/date_test_1900.xlsx https://www.dropbox.com/s/ob44bwwfej256h2/date_test_1904.xlsx

wbrandongeorge commented

2013-04-04 16:41:47 +00:00

(Migrated from github.com)

Works like a champ. Thank you!

Sign in to join this conversation.