On Thursday 22 Jul 2010 12:45:02 Meir Guttman wrote:
> Hello all Perl savvy folks!
>
>
>
> This is very much a newbie question, but I am sorry to say that I couldn't
> find an answer to it on the net.
>
>
>
> My application is to query a relational DB and put the resulting output
> table, TRANSPOSED, into an Excel "range".
>
>
>
> Doing it one-cell-at-a-time using Win32::OLE's (Actually MS VBA's)
> "Cells()" method is easy, but excruciatingly slow. A much faster way is to
> use OLE's "Range()" method. This method, as implemented in the WIN32::OLE
> package expects a _reference_ to a 2D Perl array. (An Excel "range" such
> as "B5:E17" is in general a 2D object.)
>
>
>
> From the DBI package I can fetch one row at a time and get either an array
> or an array-reference (there are other possibilities, but these two seem to
> be the most relevant):
>
>
>
> * @array = $sth->fetchrow_array;
>
> * $array_ref = $sth->fetchrow_arrayref;
>
>
>
> The second is stated as the fastest way, which makes sense. Rather than
> copying the whole array, just pass back a single reference.
>
>
>
> Is there a way by which I can _directly_ use the above 1D DB-row
> array-references (plural...) to pass a single reference to a transposed 2D
> array, structured as follows:
>
>
>
> $array_2D_ref =
>
> [ [row0_elem0, row1_elem0, ..., rowj_elem0],
>
> [row0_elem1, row1_elem1, ..., rowj_elem1],
>
> [... ],
>
> [... ],
>
> [row0_elemi, row1_elemi, ..., rowj_elemi] ];
>
You can use a map:
<<<
#!/usr/bin/perl
use strict;
use warnings;
use Data::Dumper;
my $a_ref = [[1,2,3],[4,5,6],[7,8,9]];
my $transposed =
[
map { my $x = $_; [map { $_->[$x] } @$a_ref]}
(0 .. $#{$a_ref->[0]})
];
print Dumper($transposed);
>>>
There may be a better way to do it using one of the List:: modules
(List::Util, List::MoreUtils, List::By, etc.) and naturally , there's
http://pdl.perl.org/ .
Regards,
Shlomi Fish
P.S: hope to see you in today's Tel Aviv Open Source Club meeting:
http://wiki.osdc.org.il/index.php/Tel_Aviv_Meeting_on_22_July_2010
--
-----------------------------------------------------------------
Shlomi Fish http://www.shlomifish.org/
The Case for File Swapping - http://shlom.in/file-swap
God considered inflicting XSLT as the tenth plague of Egypt, but then
decided against it because he thought it would be too evil.
Please reply to list if it's a mailing list post - http://shlom.in/reply .
_______________________________________________
Perl mailing list
[email protected]
http://mail.perl.org.il/mailman/listinfo/perl
