Transpose Excel Data

I recently needed to transpose an Excel doc from "portrait" to "landscape" in a quick and dirty fashion. This little script did the trick, but YMMV if you need finer grained control on formatting and such (that is, use Spreadsheet::ParseExcel and Spreadsheet::WriteExcel instead of Spreadsheet::ParseExcel::Simple and Spreadsheet::WriteExcel::Simple).

use strict;
use warnings;
use Math::Matrix qw(transpose);
use Spreadsheet::ParseExcel::Simple;
use Spreadsheet::WriteExcel::Simple;

my $xls = Spreadsheet::ParseExcel::Simple->read('old.xls');
my @data;
for ($xls->sheets) {
   while ($_->has_data) {
      push @data, [$_->next_row];
   }
}

my $matrix = Math::Matrix->new(@data);
my $ss     = Spreadsheet::WriteExcel::Simple->new;
$ss->write_row($_) for @{$matrix->transpose};

open OUT,'>','new.xls';
binmode OUT;
print OUT $ss->data;
[download]

UPDATE: thanks gmax! :)

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)

Comment on Transpose Excel Data Download Code

Replies are listed 'Best First'.
Re: Transpose Excel Data by gmax (Abbot) on Dec 10, 2003 at 18:11 UTC
A quicker alternative, which I found out by chance a few months ago. Simpler without ::Simple. :-) `#!/usr/bin/perl -w use strict; use Spreadsheet::ParseExcel::Simple; use Spreadsheet::WriteExcel; # <--- my $xls = Spreadsheet::ParseExcel::Simple->read('old.xls'); my @data; for ($xls->sheets) { while ($_->has_data) { push @data, [$_->next_row]; } } my $ss1 = Spreadsheet::WriteExcel->new('new.xls'); my $ws = $ss1->add_worksheet('transposed'); $ws->write('A1', \@data); # Yes. IT IS transposed!` [download] As a side note, in your example, instead of Math::Matrix you can use tye's Algorithm::Loops. `use Algorithm::Loops qw(MapCar); # .... $ss->write_row($_) for MapCar {[@_]} @data ;` [download] _ _ _ _ (_\|\| \| \|(_\|>< _\|	[reply] [d/l] [select]
Re: Transpose Excel Data by zentara (Cardinal) on Dec 10, 2003 at 15:47 UTC
I love Matrix solutions. When I was in school, I thought that using linear programming (utilizing matrix methods) to solve problems , especially in Operations Research, was the neatest thing I ever saw. Well almost, I'm still in total awe of the D'alambertian(sp?) 4 dimensional matrix, which equated the electric and magnetic fields in space-time. I think Feynman came up with that. Up with Matrix Mechanics!!!!	[reply]
Re: Transpose Excel Data by ambrus (Abbot) on Dec 16, 2003 at 09:08 UTC
Excel, some versions at least, do have a built in tranpose option in the paste special dialog box, so you don't need a script for that one.	[reply]