| Subject: | Spreadsheet::ParseExcel does not cope with XP SP@ "extra" formats |
We've had a case where a spreadsheet had a date cell with the
following extended format:
M/d/yyyy
This seems to be a "feature" of the new XP SP2 service pack working
with Excel 2003.
Spreadsheet::ParseExcel did not convert this successfully. I have
included a patch file that fixes this one instance. If you'd like I
can fix the remainder, if you can agree to release these changes
quickly.
THANKS!!
Brian Kelly
http://blogs.msdn.com/michkap/archive/2007/11/01/5807844.aspx
Okay, let's modify the code a bit to test out the full scenario....
And let's take a look ast the extra formats to see what they are:
using System;
using System.Globalization;
namespace WeirdDateStuff {
class Crap {
[STAThread]
static void Main(string[] args) {
foreach (CultureInfo ci in CultureInfo.GetCultures
(CultureTypes.SpecificCultures | CultureTypes.WindowsOnlyCultures)) {
CultureInfo gci = CultureInfo.GetCultureInfo(ci.Name);
string[] rgstci =
ci.DateTimeFormat.GetAllDateTimePatterns();
string[] rgstgci =
gci.DateTimeFormat.GetAllDateTimePatterns();
if (rgstci.Length != rgstgci.Length) {
Console.WriteLine(ci.Name + " " +
ci.EnglishName);
Console.WriteLine(" {0} from ci out of
GetCultures() call.", rgstci.Length);
foreach(string st in rgstci) {
Console.WriteLine(" " + st);
}
Console.WriteLine(" {0} from ci out of
GetCultureInfo() call.", rgstgci.Length);
foreach(string st in rgstgci) {
Console.WriteLine(" " + st);
}
}
}
}
}
}
The output (on XP SP2), with all of the "extra" formats marked in red:
fa-IR Persian (Iran)
48 from ci out of GetCultures() call.
M/d/yyyy
M/d/yy
MM/dd/yy
MM/dd/yyyy
dddd, MMMM dd, yyyy
MMMM dd, yyyy
dddd, MMMM dd, yyyy hh:mm tt
dddd, MMMM dd, yyyy HH:mm
MMMM dd, yyyy hh:mm tt
MMMM dd, yyyy HH:mm
dddd, MMMM dd, yyyy hh:mm:ss tt
dddd, MMMM dd, yyyy HH:mm:ss
MMMM dd, yyyy hh:mm:ss tt
MMMM dd, yyyy HH:mm:ss
M/d/yyyy hh:mm tt
M/d/yyyy HH:mm
M/d/yy hh:mm tt
M/d/yy HH:mm
MM/dd/yy hh:mm tt
MM/dd/yy HH:mm
MM/dd/yyyy hh:mm tt
MM/dd/yyyy HH:mm
M/d/yyyy hh:mm:ss tt
M/d/yyyy HH:mm:ss
M/d/yy hh:mm:ss tt
M/d/yy HH:mm:ss
MM/dd/yy hh:mm:ss tt
MM/dd/yy HH:mm:ss
MM/dd/yyyy hh:mm:ss tt
MM/dd/yyyy HH:mm:ss
MMMM dd
MMMM dd
yyyy'-'MM'-'dd'T'HH':'mm':'ss.fffffffK
yyyy'-'MM'-'dd'T'HH':'mm':'ss.fffffffK
ddd, dd MMM yyyy HH':'mm':'ss 'GMT'
ddd, dd MMM yyyy HH':'mm':'ss 'GMT'
yyyy'-'MM'-'dd'T'HH':'mm':'ss
hh:mm tt
HH:mm
hh:mm:ss tt
HH:mm:ss
yyyy'-'MM'-'dd HH':'mm':'ss'Z'
dddd, MMMM dd, yyyy hh:mm:ss tt
dddd, MMMM dd, yyyy HH:mm:ss
MMMM dd, yyyy hh:mm:ss tt
MMMM dd, yyyy HH:mm:ss
MMMM, yyyy
MMMM, yyyy
38 from ci out of GetCultureInfo() call.
MM/dd/yyyy
MM/dd/yy
dddd, MMMM dd, yyyy
MMMM dd, yyyy
dddd, MMMM dd, yyyy hh:mm tt
dddd, MMMM dd, yyyy HH:mm
MMMM dd, yyyy hh:mm tt
MMMM dd, yyyy HH:mm
dddd, MMMM dd, yyyy hh:mm:ss tt
dddd, MMMM dd, yyyy HH:mm:ss
MMMM dd, yyyy hh:mm:ss tt
MMMM dd, yyyy HH:mm:ss
MM/dd/yyyy hh:mm tt
MM/dd/yyyy HH:mm
MM/dd/yy hh:mm tt
MM/dd/yy HH:mm
MM/dd/yyyy hh:mm:ss tt
MM/dd/yyyy HH:mm:ss
MM/dd/yy hh:mm:ss tt
MM/dd/yy HH:mm:ss
MMMM dd
MMMM dd
yyyy'-'MM'-'dd'T'HH':'mm':'ss.fffffffK
yyyy'-'MM'-'dd'T'HH':'mm':'ss.fffffffK
ddd, dd MMM yyyy HH':'mm':'ss 'GMT'
ddd, dd MMM yyyy HH':'mm':'ss 'GMT'
yyyy'-'MM'-'dd'T'HH':'mm':'ss
hh:mm tt
HH:mm
hh:mm:ss tt
HH:mm:ss
yyyy'-'MM'-'dd HH':'mm':'ss'Z'
dddd, MMMM dd, yyyy hh:mm:ss tt
dddd, MMMM dd, yyyy HH:mm:ss
MMMM dd, yyyy hh:mm:ss tt
MMMM dd, yyyy HH:mm:ss
MMMM, yyyy
MMMM, yyyy
And like Oli said, only for Persian -- those 10 extra entries.
M/d/yyyy
M/d/yy
M/d/yyyy hh:mm tt
M/d/yyyy HH:mm
M/d/yy hh:mm tt
M/d/yy HH:mm
M/d/yyyy hh:mm:ss tt
M/d/yyyy HH:mm:ss
M/d/yy hh:mm:ss tt
M/d/yy HH:mm:ss
Seems like a bug to me, too....
| Subject: | patchfile |
--- Utility.good 2007-12-04 13:18:50.000034000 -0600
+++ Utility.pm 2007-12-04 13:20:25.000489000 -0600
@@ -258,7 +258,7 @@
}
$i++;
}
- elsif($sWk =~ /[ymdhsapg]/) {
+ elsif($sWk =~ /[yMmdhsapg]/) {
$iFmtMode = 2 unless($iFmtMode);
if(substr($sFmtObj, $i, 5) =~ /am\/pm/i) {
push @aRep, ['am/pm', length($sFmtRes), 5];
@@ -310,6 +310,7 @@
$i+=2;
}
elsif((substr($sFmtObj, $i, 1) eq 'm') ||
+ (substr($sFmtObj, $i, 1) eq 'M') ||
(substr($sFmtObj, $i, 1) eq 'd') ||
(substr($sFmtObj, $i, 1) eq 'h') ||
(substr($sFmtObj, $i, 1) eq 's')){
@@ -407,7 +408,7 @@
elsif($rItem->[0] eq 'mm') {
$sRep = sprintf('%02d', $aTime[4]);
}
- elsif($rItem->[0] eq 'm') {
+ elsif($rItem->[0] eq 'm' || $rItem->[0] eq 'M') {
$sRep = sprintf('%d', $aTime[4]);
}
#Day