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

I wanted to be able to format numbers in Excel spreadsheet with formats stored in a hash.
Using the ‘traditional’ method of recording macros in Excel I have done this for:
1. Numbers only
2. Numbers with negative numbers begin shown in Red
3. Costs with the Pound, Dollar and Euro and Euro sign.
Perl for this, which produces examples in a spreadsheet, is given below.
It would be good to be able to show negative costs in red. However, I could not find a way of doing this in Excel 2007 and therefore it may not be possible.
I have tried modifying the ‘ordinary’ costs formats but failed!
Does anyone know what the format should be?
use OLE;
use Win32::OLE::Const "Microsoft Excel";
use strict "vars";

my ($excel, $workbook, $sheet);   
$excel = CreateObject OLE "Excel.Application";
$excel -> {Visible} = 1;
$workbook = $excel    -> Workbooks -> Add;
$sheet    = $workbook -> Worksheets("Sheet1");
$sheet                -> Activate;

my (%dp_numfmt, @al, $range, $r, $c, $fm_str);

@al = qw (A B C D E F G H I J K L M N O P Q R S T U V W X Y Z);

$dp_numfmt{dp0} = "0";
$dp_numfmt{dp1} = "0.0";
$dp_numfmt{dp2} = "0.00";
$dp_numfmt{dp3} = "0.000";
$dp_numfmt{dp4} = "0.0000";
$dp_numfmt{dp5} = "0.00000";

$dp_numfmt{dp0Comma} = "#0";
$dp_numfmt{dp1Comma} = "#,#0.0";
$dp_numfmt{dp2Comma} = "#,##0.00";
$dp_numfmt{dp3Comma} = "#,###0.000";
$dp_numfmt{dp4Comma} = "#,####0.0000";
$dp_numfmt{dp5Comma} = "#,#####0.00000";

$dp_numfmt{dp0Pound}= "_-[\$£-809]* #,##0_-;-[\$£-809]* #,##0_-;_-[\$£
+-809]* \"\"-\"\"??_-;_-@_-";
$dp_numfmt{dp1Pound}= "_-[\$£-809]* #,##0.0_-;-[\$£-809]* #,##0.0_-;_-
+[\$£-809]* \"\"-\"\"??_-;_-@_-";
$dp_numfmt{dp2Pound}= "_-[\$£-809]* #,##0.00_-;-[\$£-809]* #,##0.00_-;
+_-[\$£-809]* \"\"-\"\"??_-;_-@_-";
$dp_numfmt{dp3Pound}= "_-[\$£-809]* #,##0.000_-;-[\$£-809]* #,##0.000_
+-;_-[\$£-809]* \"\"-\"\"??_-;_-@_-";
$dp_numfmt{dp4Pound}= "_-[\$£-809]* #,##0.0000_-;-[\$£-809]* #,##0.000
+0_-;_-[\$£-809]* \"\"-\"\"??_-;_-@_-";
$dp_numfmt{dp5Pound}= "_-[\$£-809]* #,##0.00000_-;-[\$£-809]* #,##0.00
+000_-;_-[\$£-809]* \"\"-\"\"??_-;_-@_-";

$dp_numfmt{dp0Dollar}= "_-[\$\$-809]* #,##0_-;-[\$\$-809]* #,##0_-;_-[
+\$\$-809]* \"\"-\"\"??_-;_-@_-";
$dp_numfmt{dp1Dollar}= "_-[\$\$-809]* #,##0.0_-;-[\$\$-809]* #,##0.0_-
+;_-[\$\$-809]* \"\"-\"\"??_-;_-@_-";
$dp_numfmt{dp2Dollar}= "_-[\$\$-809]* #,##0.00_-;-[\$\$-809]* #,##0.00
+_-;_-[\$\$-809]* \"\"-\"\"??_-;_-@_-";
$dp_numfmt{dp3Dollar}= "_-[\$\$-809]* #,##0.000_-;-[\$\$-809]* #,##0.0
+00_-;_-[\$\$-809]* \"\"-\"\"??_-;_-@_-";
$dp_numfmt{dp4Dollar}= "_-[\$\$-809]* #,##0.0000_-;-[\$\$-809]* #,##0.
+0000_-;_-[\$\$-809]* \"\"-\"\"??_-;_-@_-";
$dp_numfmt{dp5Dollar}= "_-[\$\$-809]* #,##0.00000_-;-[\$\$-809]* #,##0
+.00000_-;_-[\$\$-809]* \"\"-\"\"??_-;_-@_-";

$dp_numfmt{dp0Euro}= "_-[\$€-809]* #,##0_-;-[\$€-809]* #,##0_-;_-[\$€-
+809]* \"\"-\"\"??_-;_-@_-";
$dp_numfmt{dp1Euro}= "_-[\$€-809]* #,##0.0_-;-[\$€-809]* #,##0.0_-;_-[
+\$€-809]* \"\"-\"\"??_-;_-@_-";
$dp_numfmt{dp2Euro}= "_-[\$€-809]* #,##0.00_-;-[\$€-809]* #,##0.00_-;_
+-[\$€-809]* \"\"-\"\"??_-;_-@_-";
$dp_numfmt{dp3Euro}= "_-[\$€-809]* #,##0.000_-;-[\$€-809]* #,##0.000_-
+;_-[\$€-809]* \"\"-\"\"??_-;_-@_-";
$dp_numfmt{dp4Euro}= "_-[\$€-809]* #,##0.0000_-;-[\$€-809]* #,##0.0000
+_-;_-[\$€-809]* \"\"-\"\"??_-;_-@_-";
$dp_numfmt{dp5Euro}= "_-[\$€-809]* #,##0.00000_-;-[\$€-809]* #,##0.000
+00_-;_-[\$€-809]* \"\"-\"\"??_-;_-@_-";

$dp_numfmt{dp0Red} = "0;[Red](0)";
$dp_numfmt{dp1Red} = "0.0;[Red](0.0)";
$dp_numfmt{dp2Red} = "0.00;[Red](0.00)";
$dp_numfmt{dp3Red} = "0.000;[Red](0.000)";
$dp_numfmt{dp4Red} = "0.0000;[Red](0.0000)";
$dp_numfmt{dp5Red} = "0.00000;[Red](0.00000)";

$dp_numfmt{dp0RedComma} = "#0;[Red](#0)";
$dp_numfmt{dp1RedComma} = "#,#0.0;[Red](#,#0.0)";
$dp_numfmt{dp2RedComma} = "#,##0.00;[Red](#,##0.00)";
$dp_numfmt{dp3RedComma} = "#,###0.000;[Red](#,###0.000)";
$dp_numfmt{dp4RedComma} = "#,####0.0000;[Red](#,####0.0000)";
$dp_numfmt{dp5RedComma} = "#,#####0.00000;[Red](#,#####0.00000)";


for ($c = 0; $c <= 5; $c ++) {

    $range = $al[$c] . 1;
    
    $fm_str = 'dp' . $c;
    print "c <$c> range <$range> fm_str <$fm_str> format <$dp_numfmt{$
+fm_str}>\n";

    $sheet ->Range($range) -> {Value} = 1.24683579;
    $sheet ->Range($range) -> {NumberFormat} = $dp_numfmt{$fm_str};


    $range = $al[$c] . 2;
    
    $fm_str = 'dp' . $c . 'Red';
    print "c <$c> range <$range> fm_str <$fm_str> format <$dp_numfmt{$
+fm_str}>\n";

    $sheet ->Range($range) -> {Value} = -1.24683579;
    $sheet ->Range($range) -> {NumberFormat} = $dp_numfmt{$fm_str};

    $range = $al[$c] . 3;
    
    $fm_str = 'dp' . $c . 'Comma';
    print "c <$c> range <$range> fm_str <$fm_str> format <$dp_numfmt{$
+fm_str}>\n";

    $sheet ->Range($range) -> {Value} = 12345.24683579;
    $sheet ->Range($range) -> {NumberFormat} = $dp_numfmt{$fm_str};


    $range = $al[$c] . 4;
    
    $fm_str = 'dp' . $c . 'RedComma';
    print "c <$c> range <$range> fm_str <$fm_str> format <$dp_numfmt{$
+fm_str}>\n";

    $sheet ->Range($range) -> {Value} = -12345.24683579;
    $sheet ->Range($range) -> {NumberFormat} = $dp_numfmt{$fm_str};

    $range = $al[$c] . 5;
    
    $fm_str = 'dp' . $c . 'Pound';
    print "c <$c> range <$range> fm_str <$fm_str> format <$dp_numfmt{$
+fm_str}>\n";

    $sheet ->Range($range) -> {Value} = 12345.24683579;
    $sheet ->Range($range) -> {NumberFormat} = $dp_numfmt{$fm_str};

    $range = $al[$c] . 6;
    
    $fm_str = 'dp' . $c . 'Dollar';
    print "c <$c> range <$range> fm_str <$fm_str> format <$dp_numfmt{$
+fm_str}>\n";

    $sheet ->Range($range) -> {Value} = 12345.24683579;
    $sheet ->Range($range) -> {NumberFormat} = $dp_numfmt{$fm_str};

    $range = $al[$c] . 7;
    
    $fm_str = 'dp' . $c . 'Euro';
    print "c <$c> range <$range> fm_str <$fm_str> format <$dp_numfmt{$
+fm_str}>\n";

    $sheet ->Range($range) -> {Value} = 12345.24683579;
    $sheet ->Range($range) -> {NumberFormat} = $dp_numfmt{$fm_str};


}

[download]

Replies are listed 'Best First'.
Re: Excel - how do I format negative Costs in Red
by hominid (Priest) on Feb 02, 2010 at 13:52 UTC
    Not sure if this is the direction that you want to go, but I have done something similar using Excel's conditional formatting. Here is an example that sets the font to red for all cells in the selected range that have a value less than zero: 
    ....

$Sheet->Range("A1")->{Value} = '$5.00';
$Sheet->Range("B1")->{Value} = '$-3.51';
$Sheet->Range("C1")->{Value} = '$-0.01';

my $selection = $Sheet->Range("A1:C1");

$selection->{FormatConditions}->Delete;
$selection->{FormatConditions}->Add(xlCellValue, xlLess, "0");
$selection->FormatConditions(1)->Font->{ColorIndex} = 3;

...

    [download]
[reply]
[d/l]
      I could use that if all else fails.
      I guess I was hoping that a diffrent format definition would have done the trick but then perhaps not!
[reply]
Re: Excel - how do I format negative Costs in Red
by toolic (Bishop) on Feb 02, 2010 at 14:01 UTC
    I don't have an answer to your question, but I have a couple tips that should save you a bit of typing in the future. 
    @al = qw (A B C D E F G H I J K L M N O P Q R S T U V W X Y Z);

    [download]
    can be replaced by (see Range Operators): 
    @al = 'A' .. 'Z';

    [download]
    You could use the following to initialize your hash: 
    my %dp_numfmt = (
    dp0 => "0",
    dp1 => "0.0",
    dp2 => "0.00",
    # etc.
);

    [download]
[reply]
[d/l]
[select]

      If you're going for saving typing . . . 

      my %dp_numfmt;
$dp_numfmt{"dp$_"} = join( ".", "0", "0"x$_) for 0..5;

      [download]

      The cake is a lie.
      The cake is a lie.
      The cake is a lie.

[reply]
[d/l]
        hmmm - but then I really would have to know what I was doing!
[reply]
      Thanks for those. I am alwyas 'up' for saving typing!!
[reply]
Re: Excel - how do I format negative Costs in Red
by davies (Monsignor) on Feb 03, 2010 at 00:06 UTC
    It's too late at night to go through all your code, but you need something like: 
    $rng = your range;
$rng->{NumberFormat} = "#,##0.00_);[Red] (#,##0.00)"

    [download]
    An Excel format has four parts. I have never needed to know what the fourth does. The first is for positive numbers, the second for negative and the third for zeros. If missing, they default to the format for positive numbers. The string I have given you is one I use frequently, and a variant on my standard format. The underscore after the last zero of part 1 means "leave enough space after the number for the character that follows the underscore". [Red] does what I think you want, but note the space following it. It's critical.

    Note also that if you want this as a format for everything rather than a few cells, you are better off using styles.

    Regards,

    John Davies

    P.S. Is this the point at which I suggest a new section in PerlMonks for Excel posts? Everyone else seems to have been suggesting new sections recently. :-)
[reply]
[d/l]
Re: Excel - how do I format negative Costs in Red
by eric256 (Parson) on Feb 03, 2010 at 14:38 UTC

    Am I the only one that sees "negative cost" and thinks that should be considered a good thing? hehe. Sorry probably just a language thing, but it brings a smile to my face every time i see "negative cost".


    ___________
    Eric Hodges
[reply]

Back to Seekers of Perl Wisdom