PerlIO::via::csv - PerlIO layer to convert between Excel and CSV

  use PerlIO::via::csv sheet => 2;
  open my $fh, '<:via(csv)', 'file.xls'
    or die "Can't open file.xls: $!";

package PerlIO::via::csv;

use 5.008001;
use strict;
use warnings;

our $VERSION = '0.01';

use Spreadsheet::Read;   # I can't get this to `require' in OPEN
use Text::CSV;   # ::Encoded ?


BEGIN {
    # class methods
    my $sheet = 1;
    sub sheet {
        $sheet = $_[1] if defined $_[1];
        return $sheet;
    }
    my $debug = 0;
    sub debug {
        $debug = $_[1] if defined $_[1];
        return $debug;
    }

    sub import {
        my $pkg = shift;
        my %args = @_;

        # class methods passed to C<use>
        for (qw(sheet)) {
            $pkg->$_($args{$_}) if exists $args{$_};
        }

        # args to Spreadsheet::Read or ParseExcel?
    }

    # function
    sub damn (@) {
        require Carp;
        if (__PACKAGE__->debug) {
            Carp::confess(join("\n", @_));
        }
        else {
            Carp::croak(join("\n", @_));
        }
    }
}


### PerlIO::via interface

sub PUSHED {
    my ($pkg, $mode, $fh) = @_;

    bless {
        row  => 1,
        col  => 1,
        # meta => {},
        rows => [],
    }, $pkg;
}

sub OPEN {
    my ($self, $path, $mode, $fh) = @_;

    my $ref = Spreadsheet::Read::ReadData($path);
    __PACKAGE__->sheet(_lookup_sheet_number($ref, $path));
    $self->{rows} = [ Spreadsheet::Read::rows($ref->[__PACKAGE__->sheet]) ];

    return 1;
}
# need FDOPEN and SYSOPEN?

sub FILL {
    my ($self, $fh) = @_;

    my $buf = undef;
    while (my $row = $self->read_row()) {
        $buf .= $row . "\n";
        last if defined $/;
    }
    return $buf;
}

{
    my $csv = Text::CSV->new({binary => 1});

    sub read_row {
        my ($self) = @_;
        my $buf = undef;

        my $row_index = $self->{row} - 1;
        my $rows = $self->{rows};

        if ($row_index < @$rows) {
            my @cols = @{ $rows->[$row_index] };
            if ($csv->combine(@cols)) {
                $buf = $csv->string;
            }

            # on to the next row
            $self->{row}++;
            $self->{col} = 1;
        }

        return $buf;
    }
}

sub _lookup_sheet_number {
    my ($ref, $path) = @_;

    my $sheet = __PACKAGE__->sheet;

    if ($sheet =~ /^\d+$/) {
        my $max = $ref->[0]{sheets};
        if ($sheet < 1 or $sheet > $max) {
            damn "There's no worksheet '$sheet' in '$path' (numbers 1 .. $max)";
        }
    }
    else {
        # try to lookup sheet by name
        # (does it have to be UCS2 for Excel?)
        if (exists $ref->[0]{sheet}{$sheet}) {
            __PACKAGE__->sheet($ref->[0]{sheet}{$sheet});
        }
        else {
            damn "There's no worksheet '$sheet' in '$path'";
        }
    }

    return __PACKAGE__->sheet;
}

1;
__END__