我正在尝试在Excel工作表中添加条件格式.
不幸的是,Spreadsheet :: WriteExcel页面上的示例太简单了,我不知道该怎么做.
不幸的是,Spreadsheet :: WriteExcel页面上的示例太简单了,我不知道该怎么做.
我想通过RC10单元格值更改行背景颜色.
在excel中,我将添加格式化公式
=IF(RC10="xxxx";1;0)
我试过在Spreadsheet :: WriteExcel中做类似的事情：
my $detail_rest_fmt = $excel->add_format(font => "Calibri",size => 11,valign => "vcenter",align => "right",border => 1);
$detail_rest_fmt->set_num_format("[Green]=IF(RC10=\"xxxx\";1;0);[Red]=IF(RC10=\"yyyyyy\";1;0)");
但没有任何影响.
解决方法
坏消息是我认为使用Spreadsheet :: WriteExcel几乎无法做到.
好消息是它可以使用Excel :: Writer :: XLSX轻松完成.这恰好是Spreadsheet :: WriteExcel的一种后代.请阅读文章：Spreadsheet::WriteExcel is dead. Long live Excel::Writer::XLSX
下面的代码完全符合您想要的格式(仅基于单元格A1而不是RC10,当然可以更改)：
#!/usr/bin/perl -w
use strict;
use Excel::Writer::XLSX;
my @matrix = (
['xxxx','<-- Change the value in cell A1 to change the colour of row 4'],[qw(Redyard Kipling)],[qw(If--)],[qw(If you can keep your head when all about you)],[qw(Are losing theirs and blaming it on you;)],);
writeSpreadsheet('conditional.formatting.xlsx',\@matrix);
sub writeSpreadsheet {
my ($outFile,$matrix) = @_;
my $MIN_COL_WIDTH = 5;
my $MAX_COL_WIDTH = 35;
my $workbook = Excel::Writer::XLSX->new($outFile);
my $worksheet = $workbook->add_worksheet();
my $redFormat = $workbook->add_format(font => 'Arial',color => 'red');
my $greenFormat = $workbook->add_format(font => 'Arial',color => 'green',bold => 1);
$worksheet->set_row(0,undef,$workbook->add_format(font => 'Arial',align => 'center',bold => 1));
$worksheet->conditional_formatting('A4:Z4',{
type => 'formula',criteria => '=$A$1 = "xxxx"',format => $greenFormat
}
);
$worksheet->conditional_formatting('A4:Z4',criteria => '=$A$1 = "yyyyyy"',format => $redFormat
}
);
foreach my $row (0 .. $#$matrix) {
foreach my $col (0 .. $#{$matrix->[$row]}) {
$worksheet->write($row,$col,$matrix->[$row][$col] || '');
}
}
}