This would be a very silly way to accomplish this, but it works. :)
use strict;
use warnings;
use Data::Dumper;
use Spreadsheet::Engine;
my @data = (
[qw( red 15 2 smith )],
[qw( blue 4 10 walter )],
[qw( blue 4 10 walter )],
[qw( red 15 2 smith )],
);
my @commands = (
'set E_ formula IF(B_>C_,B_,"")',
'set F_ formula IF(B_<C_,B_,"")',
'set B_ formula IF(E_,C_,F_)',
'set F_ formula IF(B_<C_,C_,"")',
'set C_ formula IF(E_,E_,F_)',
'set E_ formula IF(F_,"","+")',
'set F_ value v',
);
my $sheet = import( @data );
for my $row (0 .. $#data) {
$sheet = apply( $sheet, $_, $row + 1 ) for @commands;
}
print Dumper [ export( $sheet ) ];
sub apply {
my ($sheet, $formula, $row) = @_;
$formula =~ s/_/$row/g;
$sheet->execute( $formula );
$sheet->recalc;
import( export( $sheet ) );
}
sub import {
my @data = @_;
my $sheet = Spreadsheet::Engine->new;
for my $row (0 .. $#data) {
for my $col (0 .. $#{ $data[$row] }) {
my $key = Spreadsheet::Engine::Sheet::number_to_col( $col
++ 1 ) . ( $row + 1 );
my $val = $data[$row][$col] || '';
my $type = $val =~ /\D/ ? 'v' : 'n';
$sheet->execute( "set $key value $type $val" );
}
}
return $sheet;
}
sub export {
my $sheet = shift;
my @data = ();
for my $row (0 .. $sheet->raw->{sheetattribs}{lastrow} - 1) {
my @tmp;
for my $col (0 .. $sheet->raw->{sheetattribs}{lastcol} - 1) {
my $key = Spreadsheet::Engine::Sheet::number_to_col( $col
++ 1 ) . ( $row + 1 );
push @tmp, $sheet->raw->{datavalues}{$key};
}
push @data, [@tmp];
@tmp = ();
}
return @data;
}
__DATA__
$VAR1 = [
[
'red',
'2',
'15',
'smith',
'+',
''
],
[
'blue',
'4',
'10',
'walter',
'',
''
],
[
'blue',
'4',
'10',
'walter',
'',
''
],
[
'red',
'2',
'15',
'smith',
'+',
''
]
];
jeffa
L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)