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.

In reply to OT? Download to Excel number formats not working by Lori713

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.