spreadsheet XLSX

lpanokarren has asked for the wisdom of the Perl Monks concerning the following question:

This modules works well - but only sometimes - date conversions and large spreadsheets are the ones that cause it to act up!!!!!

Currently, I notice the dates are erratically being left as numbers - and converted other times to dates. When i troubleshooted the module code, I feel the problem is in this part -

This is the code
                        $member_styles -> contents =~ /numFmtId="$t1" 
+formatCode="([^"]*)/;
                        my $formatCode = $1 || '';
[download]

because the regex extraction doesnt work for some date formats. I have listed the content of the numFMT tags the REGEX was trying to extract formatCode substrings from. However, I cant tell, why the regex fails. Can someone point out why the Regex would fail extracting the substring?
Many thanks in advance!

This is the data
<numFmts count="7">
<numFmt numFmtId="43" formatCode="_(* #,##0.00_);_(* \(#,##0.00\);_(* 
+&quot;-&quot;??_);_(@_)"/>
<numFmt numFmtId="164" formatCode="[$-409]h:mm\ AM/PM;@"/>
<numFmt numFmtId="165" formatCode="m/d;@"/>
<numFmt numFmtId="166" formatCode="ddd\ mm/dd/yy"/>
<numFmt numFmtId="167" formatCode="0.0%"/>
<numFmt numFmtId="168" formatCode="&quot;$&quot;#,##0.00"/>
<numFmt numFmtId="169" formatCode="m/d/yy;@"/></numFmts>
[download]

Comment on spreadsheet XLSX Select or Download Code

Replies are listed 'Best First'.
Re: spreadsheet XLSX by bart (Canon) on Nov 15, 2013 at 12:06 UTC
`$t1` is a variable. If it doesn't contain the right id value, the regex won't work. I'd rather do the following instead: `my %fmt = $member_styles -> contents =~ /\bnumFmtId="(\d+)"\s+formatCo +de="([^"]*)/g; my $formatCode = $fmt{$t1} \|\| ''` [download] and this also gives you a chance to inspect which format codes it does recognize.	[reply] [d/l] [select]
Re^2: spreadsheet XLSX by lpanokarren (Novice) on Nov 15, 2013 at 21:44 UTC
thanks bart - I was able to determine that it is not working for format ids 43, 168 and 169 - and yes, I do recognize, that if 't1' does not resolve to a value in the data, regex extraction would fail - my question was mostly - why doesnt this seemingly correct regex - `<numFmts count="7"> <numFmt numFmtId="43" formatCode="_(* #,##0.00_);_(* $#,##0.00$;_(* +"-"??_);_(@_)"/> <numFmt numFmtId="164" formatCode="[$-409]h:mm\ AM/PM;@"/> <numFmt numFmtId="165" formatCode="m/d;@"/> <numFmt numFmtId="166" formatCode="ddd\ mm/dd/yy"/> <numFmt numFmtId="167" formatCode="0.0%"/> <numFmt numFmtId="168" formatCode=""$"#,##0.00"/> <numFmt numFmtId="169" formatCode="m/d/yy;@"/></numFmts>` [download] `$member_styles -> contents =~ /numFmtId="$t1" formatCode="([^"]*)/; my $formatCode = $1 \|\| '';` [download] I will give your regex a try and report back though - thanks again	[reply] [d/l] [select]