# Download to Excel number format not working



## Lori713 (Sep 8, 2003)

Howdy all,

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 the Perl module 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.

The two formats immediately below 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);"
```
However, the one immediately below does not work (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.00)[semicolon]_(* "-"??_)[semicolon]_(@_)"
```
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 "097" )
              { $row_data{num_style} = 'style="vnd.ms-excel.numberformat:$* #,##0.00_)[semicolon][Black]$* (#,##0.00);"';
              }
        else  { $row_data{num_style} = 'style="vnd.ms-excel.numberformat:_(* #,##0.00_)[semicolon]_(* (#,##0.00)[semicolon]_(* "-"??_)[semicolon]_(@_)"';
              }
```
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 tried to escape various characters (the double quotes and the question marks) but no luck, and also put the string in single tick marks to avoid Perl trying to interpret it.

Thanks for any insights you might have, or suggestions as to where I might find some hints.

Lori


----------

