Creating formats within formats

[macik81]

Here is what I am trying to do. I have a spreadsheet that contains
rows of various data. I would like to set the columns that contain
dates to a date format of m/d/yyyy as well as set the background color
(for the entire row) to a different color based on the value of a cell
within that row. I am having trouble setting this up. I tried doing
this with two different formats, but the first format gets overwritten
by the latter. I need a way to combine the two, if possible. Here is
a little snippet of what I have now.

$format3 = $workbook->add_format(); # Add a format
$format3->set_num_format('m/d/yyyy');

$workbook->set_column('E:E', 10, $format3);
$workbook->set_column('L:L', 10, $format3);
$workbook->set_column('S:S', 10, $format3);

$format16 = $workbook->add_format(); # Add a format
$format16->set_bg_color('light blue');

I want to be able to combine all of this into one format of having
columns E, L, and S to have the date format, and the entire row to
have a background color of light blue.

Any help is greatly appreciated.

Thanks,
Keith

[Wardy]

Hi Keith
I'm not 100% sure but I think you need to make a separate format for
each unique combination.
So in your case you'd need to build a format for every combination,
rather than merging existing formats.

[macik81]

Okay, that is fine. However when I try to create the new combined
format, the set_column attribute is not found. Do you know of a way
to incorporate the set_column attribute?

Thanks for the response.

> > Keith- Hide quoted text -
>
> - Show quoted text -

[jmcnamara]

Hi,

Combining formats in Spreadsheet::WriteExcel takes a little bit of
work. As Wardy pointed out above each unique format required a unique
format object.

The best way to achieve this is to store the properties that you wish
to set in hashes and then pass these hashes to the add_format()
constructor. An example of this is shown in the chess.pl program in
the distro:

http://search.cpan.org/src/JMCNAMARA/Spreadsheet-WriteExcel-2.18/
examples/chess.pl

There are a few things to remember:

1. A cell format over-rides a row format overrides a column format.
2. You must call set_row and set_column before calling write();

Here is another small example. Note that the row and column format
properties are combined for a cell format:

#!/usr/bin/perl -w

use strict;
use Spreadsheet::WriteExcel;

my $workbook = Spreadsheet::WriteExcel->new('reload.xls');
my $worksheet = $workbook->add_worksheet();

my %col_formats = (num_format => 'm/d/yyyy', fg_color => 'red');
my %row_formats = (italic => 1, fg_color =>
'yellow');

my $col_format = $workbook->add_format(%col_formats);
my $row_format = $workbook->add_format(%row_formats);
my $cell_format = $workbook->add_format(%col_formats,
%row_formats);

my $data = [
['Foo', 1, 39115, 1],
['Bar', 12, 39116, 2],
['Ace', 31, 39117, 3],
['Moo', 33, 39118, 4],
['Cow', 34, 39119, 5],
['Apt', 8, 39120, 6],
['Rag', 1, 39121, 7],
];

$worksheet->set_column('C:C', 20, $col_format);
$worksheet->set_row(4, undef, $row_format);

$worksheet->write_col('A1', $data);

# Overwrite Cell C5
$worksheet->write('C5', 39119, $cell_format);

__END__

John.
--