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};
}
`

Comment onExcel - how do I format negative Costs in RedDownloadCode