Skip Menu |
 

This queue is for tickets about the XLSperl CPAN distribution.

Report information
The Basics
Id: 63689
Status: open
Priority: 0/
Queue: XLSperl

People
Owner: Nobody in particular
Requestors: jpierce [...] cpan.org
Cc:
AdminCc:

Bug Information
Severity: Wishlist
Broken in: 0.7
Fixed in: (no value)



Subject: Use headers for autosplit hash keys
Download (untitled) / with headers
text/plain 257b
It would be pretty nifty if XLSperl could detect/guess at header rows, and use the cell values as column headers in auto-split mode. This would provide some level of future-proofing when spreadsheets formats change e.g; a column is inserted between C and D.
Add -A to make %F keys use first defined row as keys
Subject: header.patch
Download header.patch
text/x-diff 1.8k
--- /usr/local/bin/XLSperl 2010-12-06 22:33:57.000000000 -0500 +++ XLSperl 2010-12-07 00:20:55.000000000 -0500 @@ -40,7 +40,7 @@ MAIN: { my %options; Getopt::Long::Configure('bundling'); - GetOptions( \%options, qw/e=s l n p x a F=s w/, + GetOptions( \%options, qw/e=s l n p x a A F=s w/, 'm=s' => sub{ eval qq{use $_[1] ()}; die $@ if $@;}, 'M=s' => sub{ my ($m,$a) = split /=/,$_[1],2; if ($a) { @@ -64,6 +64,7 @@ if ($options{n} or $options{p}) { $eval_function = make_loop( perl => $eval_function, autosplit => $options{a}, + AutoSplit => $options{A}, splitregex => $options{F}, autoline => $options{l} ); } @@ -120,19 +121,31 @@ sub loop_xls { my %args = @_; + + my @header; + foreach my $worksheet (@{$args{xls}->{Worksheet}}) { $WS = $worksheet->{Name}; my ($row_min,$row_max) = $worksheet->RowRange(); my ($col_min,$col_max) = $worksheet->ColRange(); foreach my $row ($row_min .. $row_max) { $ROW = $row + 1; - if ($args{autosplit}) { + if ($args{autosplit} || $args{AutoSplit}) { @F = map {$_ && $_->Value() || undef} @{$worksheet->{Cells}[$row]}; @F = map {$_ && chomp;$_} @F if ($args{autoline}); + + if( $args{AutoSplit} && !@header && grep {defined} @F ){ + @header = @F; + } - # Make the %F hash an alias for @F, i.e. $F{A} == $F[0] + # Make the %F hash an alias for @F for (0 .. $#F) { - alias($F[$_],$F{int2col($_)}); + # $F{$header[0]} == $F[0] + if ( $args{AutoSplit}) { + alias($F[$_],$F{$header[$_]}); } + # $F{A} == $F[0] + else{ + alias($F[$_],$F{int2col($_)}); } } undef $_; # What should $_ be if autosplitting an Excel file?


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.