Creating an excel document from hashes

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

I need to create an excel document from the hash I have created. Could the monks provide insight to where and how should I begin with. I want the keys as the title and values with the keys as the values under the title in excel

Created the Hash

#!/usr/bin/perl

use strict;
use warnings;
use File::Slurp;
use Data::Dumper;

my %graph_point_hash;
my @report_graph_point = `sed -e 's/data//g' -e 's/[\<\/\>]//g' -e 's/
+^ *//' -e 's/* \$//' -e '/^\$/d' -e '1d;\$d' $ARGV[0]`;
my @split_graph_points;



foreach (@report_graph_point) {

        @split_graph_points = split (' ', $_);
        foreach my $graph_point (@split_graph_points) {
                $graph_point =~ s/"//g;
                my ($key, $val) = split ("=", $graph_point);
                push (@{$graph_point_hash{$key}}, "$val");
        } #foreach loop ends here

} #foreach loop ends here

#print Dumper \%graph_point_hash;
[download]

Comment on Creating an excel document from hashes Download Code

Replies are listed 'Best First'.
Re: Creating an excel document from hashes by Ratazong (Monsignor) on Sep 11, 2013 at 07:19 UTC
Hi rahulruns! If you are in a windows environment, you could use Win32::OLE. Your code could look similar to the following: `use Win32::OLE::Const 'Microsoft Excel'; my $Excel = Win32::OLE->new('Excel.Application', 'Quit'); my $book = $Excel->Workbooks->Open($template); my $Sheet = $book->Worksheets($destinationSheet); my $y = 2; foreach my $k (keys %yourHash) { $Sheet->Range("B$y")->{Value} = $k; # title $Sheet->Range("C$y")->{Value} = $yourHash{$k}; # value $y++; }` [download] See Using Win32::OLE and Excel - Tips and Tricks for more information. HTH, Rata	[reply] [d/l]
Re: Creating an excel document from hashes by hdb (Monsignor) on Sep 11, 2013 at 07:02 UTC
Your question is difficult to understand (for me at least) and I cannot see how the code relates to the question. Do you just want to know how to turn a hash into tabular form? Or is creating an Excel sheet the problem? In any case, a small example of your data (the hash) would be useful to illustrate what you want to achieve.	[reply]
Re^2: Creating an excel document from hashes by rahulruns (Scribe) on Sep 11, 2013 at 08:10 UTC
Here is what the hash produces THE OUTPUT IS `$VAR1 = { 'w' => [ '0', '0', '0.00', '5.50', '0.00', '2.50', '0.00', '489.50', '0.00', '0.00', '0.00', '0.00', '0.00', '242.00', '0.00', '0.00', '0.00', '5.50', '0.00', '4.00',` [download] I need to convert the hash where one key has multiple values into an excel sheet	[reply] [d/l]
Re^3: Creating an excel document from hashes by roboticus (Chancellor) on Sep 11, 2013 at 10:29 UTC
rahulruns: You should be able to do it with something like: `use Spreadsheet::WriteExcel; . . . . code to build hash . . . . my $XL = Spreadsheet::WriteExcel->new('output_file.xls'); my $WS = $XL->add_worksheet('my data'); my ($ROW, $COL) = (0, 0); for my $colname (keys %graph_point_hash) { # Add column header $WS->write_string($ROW, $COL, $colname); # Add column $WS->write_col($ROW+1, $COL, $graph_point_hash{$colname}); # Move on to next column ++$COL; }` [download] ...roboticus When your only tool is a hammer, all problems look like your thumb.	[reply] [d/l]
Re: Creating an excel document from hashes by Laurent_R (Canon) on Sep 11, 2013 at 10:20 UTC
For all those who want to understand better the requirement, this post is a follow-up on this one: I need to create a graph from a hash using GD else GD::Graph. As I told you, if I understood correctly what you are looking for, your hash does not contain the proper data structure, since it contains only one single array with all the data, whereas you needed, from what you said, an array of arrays. I have shown you in the other thread a way to obtain an array of arrays, but it seems you did not see it (you may have to click on the readmore tag).	[reply]