File Coverage

blib/lib/Catmandu/Importer/XLSX.pm

Criterion	Covered	Total	%
statement	10	12	83.3
branch			n/a
condition			n/a
subroutine	4	4	100.0
pod			n/a
total	14	16	87.5

package Catmandu::Importer::XLSX;

our $VERSION = '0.08';

use namespace::clean;
use Catmandu::Sane;
use Encode qw(decode);
use Spreadsheet::ParseXLSX;
use Spreadsheet::ParseExcel::Utility qw(int2col);
use Moo;

with 'Catmandu::Importer';

has xlsx => (is => 'ro', builder => '_build_xlsx');
has header => (is => 'ro', default => sub { 1 });
has columns => (is => 'ro' , default => sub { 0 });
has fields => (
    is     => 'rw',
    coerce => sub {
        my $fields = $_[0];
        if (ref $fields eq 'ARRAY') { return $fields }
        if (ref $fields eq 'HASH')  { return [sort keys %$fields] }
        return [split ',', $fields];
    },
);
has worksheet => (is => 'ro' , default => sub { 0 });
has _n => (is => 'rw', default => sub { 0 });
has _row_min => (is => 'rw');
has _row_max => (is => 'rw');
has _col_min => (is => 'rw');
has _col_max => (is => 'rw');

sub BUILD {
    my $self = shift;

    if ( $self->header ) {
        if ( $self->fields ) {
            $self->{_n}++;
        }
        elsif ( $self->columns ) {
            $self->fields([$self->_get_cols]);
            $self->{_n}++;
        }
        else {
            $self->fields([$self->_get_row]);
            $self->{_n}++;
        }
    }
    else {
        if ( !$self->fields || $self->columns ) {
            $self->fields([$self->_get_cols]);
        }
    }
}

sub _build_xlsx {
    my ($self) = @_;
    my $parser   = Spreadsheet::ParseXLSX->new();
    my $xlsx = $parser->parse( $self->file ) or  Catmandu::Error->throw("could not parse file \"$self->{file}\": " . $parser->error());

    $xlsx = $xlsx->worksheet($self->worksheet) or Catmandu::Error->throw("worksheet $self->{worksheet} does not exist.");
    ($self->{_row_min}, $self->{_row_max}) = $xlsx->row_range();
    ($self->{_col_min}, $self->{_col_max}) = $xlsx->col_range();
    return $xlsx;
}

sub generator {
    my ($self) = @_;
    sub {
        while ($self->_n <= $self->_row_max) {
            my @data = $self->_get_row();
            $self->{_n}++;
            my @fields = @{$self->fields()};
            my %hash = map {
                my $key = shift @fields;
                defined $_  ? ($key => $_) : ()
                } @data;
            return \%hash;
        }
        return;
    }
}

sub _get_row {
    my ($self) = @_;
    my @row;
    for my $col ($self->_col_min .. $self->_col_max) {
        my $cell = $self->xlsx->get_cell($self->_n, $col);
        if ($cell) {
            push(@row,$cell->value());
        }
        else{
            push(@row, undef);
        }
    }
    return @row;
}

sub _get_cols {
    my ($self) = @_;
    my @row;
    for my $col ( $self->_col_min .. $self->_col_max ) {

        if (!$self->header || $self->columns) {
            push(@row,int2col($col));
        }
        else {
            my $cell = $self->xlsx->get_cell($self->_n, $col);
            if ($cell) {
                push(@row,$cell->value());
            }
            else{
                push(@row, undef);
            }
        }
    }
    return @row;
}


=head1 NAME

Catmandu::Importer::XLSX - Package that imports XLSX files

=head1 SYNOPSIS

    # On the command line
    $ catmandu convert XLSX < ./t/test.xlsx
    $ catmandu convert XLSX --header 0 < ./t/test.xlsx
    $ catmandu convert XLSX --fields 1,2,3 < ./t/test.xlsx
    $ catmandu convert XLSX --columns 1 < ./t/test.xlsx
    $ catmandu convert XLSX --worksheet 1 < ./t/test.xlsx

    # Or in Perl
    use Catmandu::Importer::XLSX;

    my $importer = Catmandu::Importer::XLSX->new(file => "./t/test.xlsx");

    my $n = $importer->each(sub {
        my $hashref = $_[0];
        # ...
    });

=head1 DESCRIPTION

L importer for XLSX files.

=head1 METHODS

This module inherits all methods of L and by this
L.

=head1 CONFIGURATION

In addition to the configuration provided by L (C,
C, etc.) the importer can be configured with the following parameters:

=over

=item header

By default object fields are read from the XLS header line. If no header
line is avaiable object fields are named as column coordinates (A,B,C,...). Default: 1.

=item fields

Provide custom object field names as array, hash reference or comma-
separated list.

=item columns

When the 'columns' option is provided, then the object fields are named as
column coordinates (A,B,C,...). Default: 0.

=item worksheet

If the Excel workbook contains more than one worksheet, you can select a specific worksheet by its index number (0,1,2,...). Default: 0.

=back

=head1 SEE ALSO

L, L, L, L.

=cut

1;