Howdy all,

This may be off-topic (not too Perl-y) but wasn't sure where else to go with it so I'm going to start here. Feel free to move, reap or ridicule as befitting the post. :-)

I have a Perl application that allows users to download their financial reports to Excel. Some rows need the numbers formatted one way, and others another way. I'm using HTML:Template (and a TMPL_VAR called num_style) to accomplish this. My problem is that I can get certain number formats to work, but not one in particular.

All these formats are put in the td tag as a style and they work. (The formatting syntax can be snatched from within Excel if you are in a cell and click Format cells and then Custom to see what the code is behind it, just in case anyone was wondering).

style="vnd.ms-excel.numberformat:#,##0.00_)[semicolon](#,##0.00);"
style="vnd.ms-excel.numberformat:$* #,##0.00_)[semicolon][Black]$* (#,
+##0.00);"
[download]

However, this one does not (this one would put a dash (or hyphen) if the amount were 0). I get a message during the download that it doesn't like the number format.

style="vnd.ms-excel.numberformat:_(* #,##0.00_)[semicolon]_(* (#,##0.0
+0)[semicolon]_(* "-"??_)[semicolon]_(@_)"
[download]

Note: There are two question marks after the "-" in that last line but they are somehow getting dropped out of the code snippet above. The same is true of the code snippet below in the else clause. Grrrrr... In Perl, the code is set up like this:

    # Format numbers style for download to Excel
    foreach my $c (qw(col_endow col_gfund col_restr col_total))
      { if    ( substr($key,0,3) eq "010" || substr($key,0,3) eq "023"
+ ||
                substr($key,0,3) eq "025" || substr($key,0,3) eq "027"
+ ||
                substr($key,0,3) eq "030" || substr($key,0,3) eq "043"
+ ||
                substr($key,0,3) eq "045" || substr($key,0,3) eq "066"
+ ||
                substr($key,0,3) eq "097" )
              { $row_data{num_style} = 'style="vnd.ms-excel.numberform
+at:$* #,##0.00_)[semicolon][Black]$* (#,##0.00);"';
              }
        else  { $row_data{num_style} = 'style="vnd.ms-excel.numberform
+at:_(* #,##0.00_)[semicolon]_(* (#,##0.00)[semicolon]_(* "-"_)[semico
+lon]_(@_)"';
              }
[download]

Has anyone run into problems getting downloads to Excel to format numbers, or is there another way I can accomplish this? Is this an HTML-Template issue, Excel issue, or a S.O.L issue? I don't believe there are any characters I need to escape, and I used a single tick mark instead of double ticks so Perl won't try to interpret it (grasping at straws now...).

Thanks for any insights you might have, or suggestions as to where I might go if this is not an appropriate place to ask a not-so-Perl-y question. I'm hoping someone else in the Perl world has run into this and knows a trick or two.

Lori

Edited to add note about the error dialog box that appears in Excel about the number format being invalid.