Spreadsheet::ParseExcel构建数组或散列

Question

我是Spreadsheet::ParseExcel的新手。我在Microsoft Excel中打开了一个以空格分隔的文件，并将其另存为XLS文件。

我安装了Spreadsheet::ParseExcel并使用文档中的示例代码来打印文件的内容。我的目标是构建一个由一些数据组成的数组，以便写入数据库。我只需要一点帮助来构建数组--写入一个数据库，我稍后会弄清楚。

我很难理解这个模块--我确实读过文档，但由于缺乏经验，我无法理解它。

下面是我用于输出的代码。

#!/usr/bin/perl

use warnings;
use strict;

use Data::Dumper;
use Spreadsheet::ParseExcel;

my $parser   = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse( 'file.xls' );

if ( !defined $workbook ) {
    die $parser->error(), ".\n";
}

for my $worksheet ( $workbook->worksheets() ) {

    my ( $row_min, $row_max ) = $worksheet->row_range();
    my ( $col_min, $col_max ) = $worksheet->col_range();

    for my $row ( $row_min .. $row_max ) {

        for my $col ( $col_min .. $col_max ) {

            my $cell = $worksheet->get_cell( $row, $col );
            next unless $cell;

            print "Row, Col    = ($row, $col)\n";
            print "Value       = ", $cell->value(),       "\n";
            print "Unformatted = ", $cell->unformatted(), "\n";
            print "\n";
        }
    }
}

下面是一些输出

Row, Col    = (0, 0)
Value       = NewRecordFlag
Unformatted = NewRecordFlag

Row, Col    = (0, 1)
Value       = AgencyName
Unformatted = AgencyName

Row, Col    = (0, 2)
Value       = CredentialIdnt
Unformatted = CredentialIdnt

Row, Col    = (0, 3)
Value       = ContactIdnt
Unformatted = ContactIdnt

Row, Col    = (0, 4)
Value       = AgencyRegistryCardNumber
Unformatted = AgencyRegistryCardNumber

Row, Col    = (0, 5)
Value       = Description
Unformatted = Description

Row, Col    = (0, 6)
Value       = CredentialStatusDescription
Unformatted = CredentialStatusDescription

Row, Col    = (0, 7)
Value       = CredentialStatusDate
Unformatted = CredentialStatusDate

Row, Col    = (0, 8)
Value       = CredentialIssuedDate
Unformatted = CredentialIssuedDate

我的目标是构建一个包含CredentialIssuedDate、AgencyRegistryCardNumber和AgencyName的数组。一旦我掌握了这样做的概念，我就可以使用这个很棒的模块了。

Answer 1

这里有一个快速示例，说明它应该对您有效。它为每个工作表构建一个数组@rows，其中包含您想要的三个字段值的数组，并使用Data::Dumper显示每个结果。我还没能对其进行测试，但它看起来是正确的，并且可以编译

它首先根据每个工作表中的第一行构建一个将列标题字符串与列号相关联的散列%headers。

然后处理第二行，提取在@wanted数组中命名的列中的单元格，并将它们的值放入数组@row中，在累加每个值时将其推送到@rows上

#!/usr/bin/perl

use strict;
use warnings;

use Spreadsheet::ParseExcel;
use Data::Dumper;

my @wanted = qw/
    CredentialIssuedDate
    AgencyRegistryCardNumber
    AgencyName
/;

my $parser   = Spreadsheet::ParseExcel->new;
my $workbook = $parser->parse('file.xls');

if ( not defined $workbook ) {
    die $parser->error, ".\n";
}

for my $worksheet ( $workbook->worksheets ) {

    my ( $row_min, $row_max ) = $worksheet->row_range;
    my ( $col_min, $col_max ) = $worksheet->col_range;

    my %headers;

    for my $col ( $col_min, $col_max ) {
        my $header = $worksheet->get_cell($row_min, $col)->value;
        $headers{$header} = $col;
    }

    my @rows;

    for my $row ( $row_min + 1 .. $row_max ) {

        my @row;

        for my $name ( @wanted ) {
            my $col = $headers{$name};
            my $cell = $worksheet->get_cell($row, $col);
            push @row, $cell ? $cell->value : "";
        }

        push @rows, \@row;
    }

    print Dumper \@rows;
}

Answer 2

我可以通过使用Spreadsheet::BasicReadNamedCol模块来解决这个问题

#!/usr/bin/perl
use warnings;
use strict;
use Data::Dumper;
use Spreadsheet::BasicReadNamedCol;

my $xlsFileName = 'shit.xls';
my @columnHeadings = (
'AgencyName',
'eMail',
'PhysicalAddress1',
'PhysicalAddress2'
);

my $ss = new Spreadsheet::BasicReadNamedCol($xlsFileName) ||
die "Could not open '$xlsFileName': $!";
$ss->setColumns(@columnHeadings);

# Print each row of the spreadsheet in the order defined in
# the columnHeadings array
my $row = 0;
while (my $data = $ss->getNextRow())
{
   $row++;
   print join('|', $row, @$data), "\n";
}