arrays - Perl:将数据从哈希转储到 Excel 中

Question

我有一个带有键和值(数组)的哈希。我想将它们转储到电子表格中，但很难排列它们。

%哈希
key1 -> foo bar
key2-> 约翰·亚当·吉尔
key3->苹果香蕉芒果橙

代码:

use strict;
use warnings;
use Excel::Writer::XLSX;

my $pattern = "BEGIN_";
my $format;
my @keys = qw(key1 key2 key3);
foreach my $key(@keys){
     open my $fh, "<","filename.txt" or die $!;
      while ( <$fh> ) {
        if (/$pattern/) {
        push(@matching_lines, $_);
      }
    }
    $hash{$key} = [@matching_lines] ; 
   for (@matching_lines) { $_ = undef } ; #Emptying the array contents,to reuse it for for all the other keys
}

my $workbook = Excel::Writer::XLSX->new( 'c:\TEMP\filename.xlsx' ); 
 if (not defined $workbook)
{
    die "Failed to create spreadsheet: $!";
}
my $worksheet = $workbook->add_worksheet();

#  Add and define a format
$format = $workbook->add_format();
$format->set_bg_color( 'yellow' );

my $row = 1;
my $col = 0;

foreach my $k (keys %hash)
{
     $worksheet->write($row, $col, $k, $format); # title
     $worksheet->write_col($row+1, $col, $hash{$k}); #value
     $col++;
}
$workbook->close() or die "Error closing file: $!";   

电流输出

期望输出

Answer 1

编辑:现在您实际上已经更新了程序，以澄清问题在于您如何读取数据，以下内容没有实际意义。但它确实说明了一种替代方法。

好吧，这里的核心问题是你想要做的是“翻转”哈希。您正在逐行打印，但散列是按列组织的。

使用逗号 sep 作为打印实际 Excel 的快速代理:

#!/usr/bin/env perl
use strict;
use warnings;

use Data::Dumper;

#initialise
my %hash = (
    key1 => [qw (  foo bar  )],
    key2 => [qw (  john adam gill  )],
    key3 => [qw (  apple banana mango orange )],
);

#print for debug
print Dumper \%hash;


#get header row. Sort it, because hashes are unordered.
#could instead:
#my @keys = qw ( key1 key2 key3 );
my @keys = sort keys %hash;
#print header row
print join ",", @keys, "\n";

#iterate until every element of the hash is gone
while ( map { @{ $hash{$_} } } @keys ) {
    #cycle the keys, shifting a value of the top of each array. 
    #replace any undefined values with ''. 
    print shift( @{ $hash{$_} } ) // '', "," for @keys;
    print "\n";
}

打印:

key1,key2,key3,
foo,john,apple,
bar,adam,banana,
,gill,mango,
,,orange,

如果您将其作为 csv 加载到 Excel 中，应该会给出您想要的结果。我很确定您可以在该模块中使用类似的“写入行”。

所以这实际上似乎可以满足您的要求:

#!/usr/env/perl
use strict;
use warnings;

use Excel::Writer::XLSX;

#initialise
my %hash = (
    key1 => [qw (  foo bar  )],
    key2 => [qw (  john adam gill  )],
    key3 => [qw (  apple banana mango orange )],
);


my $workbook = Excel::Writer::XLSX->new('c:\TEMP\filename.xlsx');
if ( not defined $workbook ) {
    die "Failed to create spreadsheet: $!";
}
my $worksheet = $workbook->add_worksheet();

#  Add and define a format
my $format = $workbook->add_format();
$format->set_bg_color('yellow');

my @keys = sort keys %hash;
my $row  = 0;
$worksheet->write_row( $row++, 0, \@keys, $format );
while ( map { @{ $hash{$_} } } @keys ) {
    my $col = 0;
    $worksheet->write( $row, $col++, shift( @{ $hash{$_} } ) // '' )
        for @keys;
    $row++;
}
$workbook->close() or die "Error closing file: $!";