我有一个从 .xls 文件中提取的 perl 代码。我的 .xls 文件如下
NUMBER NAME ALPHABET
one Jane a
two Adam b
three Josh c
;four
five Agnes e
six Mary f
;seven
eight Lara h
我想提取信息,只取第 1 列和第 2 列。我的 perl 代码如下。
#!/usr/bin/perl
use warnings;
use strict;
use Spreadsheet::ParseExcel;
main ();
sub main {
my $filename = 'Book1.xls';
my $parser = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse( $filename );
if ( !defined $workbook ) {
die "-E-: cannot parse <$filename>.\n ", $parser->error(), ".\n";
}
my $worksheet = $workbook -> Worksheet ( 'a' ) || die "-E-: cannot parse family pin list.\n";
my ( $row_min, $row_max ) = $worksheet-> row_range();
open ( my $file,"> output.txt");
for my $row ( 1 .. $row_max ) {
my @data;
for my $col ( 0 ) {
my $number = $worksheet-> get_cell( $row, $col );
if ( $number ) {
push @data, $number-> value();
}
else {
push @data, '';
}
}
for my $col ( 2 ) {
my $alphabet = $worksheet->get_cell( $row, $col );
if ( $alphabet ) {
push @data, $alphabet->value();
print $file "@data\n";
}
else {
push @data, '';
}
}
}
close $file;
print "done\n";
}
结果是
one a
two b
three c
;four
five e
six f
;seven
eight h
我想删除以字符串“;”开头的整个数组。我像下面一样扩展我的代码
open ( my $file,"> output.txt");
for my $row ( 1 .. $row_max ) {
my @data;
for my $col ( 0 ) {
my $number = $worksheet-> get_cell( $row, $col );
if ( $number ) {
push @data, $number-> value();
}
else {
push @data, '';
}
}
for my $col ( 11 ) {
my $alphabet = $worksheet->get_cell( $row, $col );
if ( $alphabet ) {
push @data, $alphabet->value();
}
else {
push @data, '';
}
}
my @new_data = grep(!/;/, @data);
my @latest_data = grep ( $_ ne '', @new_data);
print $file "@latest_data\n";
}
close $file;
print "done\n";
}
输出结果如下所示。
one a
two b
three c
five e
six f
eight h
我不想成为一片空白。我想如何消除产生如下结果的空白区域?
one a
two b
three c
five e
six f
eight h
我也尝试这样做,但结果是一样的。
for my $index (reverse 0..$#data) {
if ( $data[$index] =~ /^;/ ) {
splice(@data, $index, 1);
}
}
print $file "@data\n";
答案1
您可能会考虑在迭代其余列之前检查当前行的第 0 列;如果值以分号开头,则跳到下一行:
# ...
for my $row ( 1 .. $row_max ) {
my @data;
next if $worksheet->get_cell($row, 0) =~ /^;/;
# ...
这样,您就可以避免将任何新(行)数据推送到数组中。
答案2
如果你只是想获得这个结果,那么你可以使用awk
:
awk '$1 !~ "NUMBER" && $1 !~ "^;" {print $1,$3}' file.xls
这告诉它不要打印任何包含该字符串NUMBER
或以;
.然后它打印第一列和第三列并给出您的预期输出。
one a
two b
three c
five e
six f
eight h