perl - Spreadsheet::WriteExcel基于其他单元格值的条件格式

我试图在我的Excel工作表中添加条件格式。
不幸的是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',
        {
            type => 'formula',
            criteria => '=$A$1 = "yyyyyy"',
            format => $redFormat
        }
    );
    foreach my $row (0 .. $#$matrix) {
        foreach my $col (0 .. $#{$matrix->[$row]}) {
            $worksheet->write($row, $col, $matrix->[$row][$col] || '');
        }
    }
}

关于perl - Spreadsheet::WriteExcel基于其他单元格值的条件格式，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/11133403/