emptying a hash into a spreadsheet

basm101 has asked for the wisdom of the Perl Monks concerning the following question:

Hello,
I have a hash that points to values in an array like this:

key1 -> 1 2 3 4 5 6 7 8
key2-> 4 6 7 8 3 2 9 7
key3-> 2 3 5 8 9 1 1 2
[download]

I can print out the values like this:

for my $hash_count ( sort {$a<=>$b} keys %hash){
print  "($hash_count)-> @{$hash{$hash_count}}\n";
}
[download]

I really want to empty this data into a spreadsheet using the Spreadsheet::WriteExcel module.

I hoped the following might work but it doesnt:



for $hash_count ( sort {$a<=>$b} keys %hash){
      push @spread_array,"$hash{$hash_count}";
}
$worksheet->write_col(1,1,\@spread_array);
[download]

The idea was to push a load of references to arrays into @spread_array. Can anyone help with getting a hash of arrays into a spreadsheet ? I know 2D arrays can be written in one go.

thanks,
basm101

Comment on emptying a hash into a spreadsheet Select or Download Code

Replies are listed 'Best First'.
Re: emptying a hash into a spreadsheet by jeffa (Bishop) on Aug 04, 2003 at 14:14 UTC
`write_col` wants an array of scalars, not an array of anonymous array references. Since your values in that hash are already such, try this: `my $i = 1; for my $key (sort keys %hash) { $worksheet->write_col(1,$i++,$hash{$key}); }` [download] And yes, `write_col` will insert entire 2D arrays, my question is why do store your rows in a hash? Quick lookups is the only reason i can ascertain - if you really don't need that, then store them in a 2D array and you can use: `$worksheet->write_col('A1',\@2D_array);` [download] UPDATE: I should test before posting, not after ... i realized that you can use something like: `$worksheet->write_row('A1',[values %hash]);` [download] Notice that i used `write_row` and not `write_col` ... i let you figure that one out. ;) 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)	[reply] [d/l] [select]
Re: emptying a hash into a spreadsheet by dragonchild (Archbishop) on Aug 04, 2003 at 14:14 UTC
Why are you treating them any differently? Deal with output layers as closely to similar as possible, especially in the way you conceive of them. While I'm not a Spreadsheet::WriteExcel expert, I would expect that write_col() writes a column. But, you're writing a bunch of stringified reference names, not a column of values. It looks like you have a 2-D array and you're not thinking it through. Try something like: `# COMPLETELY UNTESTED!!! my @values; foreach my $hash_count (sort { $a <=> $b } keys %hash) { foreach my $i (0 .. $#{$hash{$hash_count}}) { push @{$values[$i]}, $hash{$hash_count}[$i]; } } foreach my $i (0 .. $#values) { $worksheet->write_col($i + 1, 1, $values[$i]); }` [download] In other words, transform your width-first group of arrays into a group of depth-first arrays, then write them out. (I hope I got the write_col() syntax correct.) ------ We are the carpenters and bricklayers of the Information Age. The idea is a little like C++ templates, except not quite so brain-meltingly complicated. -- TheDamian, Exegesis 6 Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.	[reply] [d/l]
Re: emptying a hash into a spreadsheet by rupesh (Hermit) on Aug 05, 2003 at 10:20 UTC
Try this use Win32::OLE; my ($ex, $book, $sheet, $pwdr); # # your code... # # use existing instance if Excel is already running eval {$ex = Win32::OLE->GetActiveObject('Excel.Application')}; die "Excel not installed" if $@; unless (defined $ex) { $ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;}) or die "Oops, cannot start Excel";} # get a new workbook $book = $ex->Workbooks->Add; # write to a particular cell $sheet = $book->Worksheets(1); for $hash_count ( sort {$a<=>$b} keys %hash){ push @spread_array,"$hash{$hash_count}"; } foreach (@text) { $sheet->Cells($ctr2,$ctr3)->{Value} = $_; $ctr3++; } $book->SaveAs("<some file name>"); undef $book; undef $ex; [download] You can increment the rows and columns cubsequently in a loop. Hope it helps! we're born with our eyes closed and our mouths wide open, and we spend our entire life trying to rectify that mistake of nature. - anonymous.	[reply] [d/l]