perl – 基于其他单元格值的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] || '');
        }
    }
}