Beefy Boxes and Bandwidth Generously Provided by pair Networks
Come for the quick hacks, stay for the epiphanies.
 
PerlMonks  

Re: switching two columns under conditions (Spreadsheet::Engine)

by jeffa (Bishop)
on Aug 13, 2015 at 18:59 UTC ( [id://1138482]=note: print w/replies, xml ) Need Help??


in reply to switching two columns under conditions

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)

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://1138482]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others wandering the Monastery: (3)
As of 2024-03-28 18:05 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found