package Common::Spreadsheet;

require Exporter;
@ISA    = qw(Exporter);
@EXPORT = qw( get_cell perl_index_to_spreadsheet_loc spreadsheet_loc_to_perl_index
  letters_to_perl_index perl_index_to_letters );
use strict;
use warnings;

# Terminologym used in this module:
# aoa               : A perl arrayref of arrayrefs, containing the contents of a worksheet
# rownum            : Index 0 based row location on a worksheet...
#                     e.g., row 1 in spreadsheet would be rownum 0, 2 would be 1, etc.
# colnum            : Index 0 based column location on a worksheet...
#                     e.g., column A in spreadsheet would be 0, column B is 2, column AA would
#                     be 26, etc.
# perl (index)      : In terms of this module "to perl" or "from perl" is meaning going to or
#                     from zero-indexed positions as per "rownum" and "colnum"
# spreadsheet (loc) : Spreadsheet style notation for a cell location on a worksheet...
#                     e.g., A1, B2, ZZ99, etc.
# row               : Index 1 based row location (as per spreadsheet)
# letters / col     : Letter-based column location (as per spreadsheet)

# Get a value for a cell within the primary worksheet of the XLS file
sub get_cell {
    my ( $rownum, $colnum ) = spreadsheet_loc_to_perl_index(shift);
    my $aoa = shift;
    return $aoa->[$rownum][$colnum];
}

# Translate A1 to 0,0... B2 to 1,1
sub spreadsheet_loc_to_perl_index {
    my $loc = shift;    # spreadsheet-syntax cell location (e.g., A1, AZ99
    if ( ( not defined $loc ) || ( $loc !~ m/^([A-Za-z]?[A-Za-z])(\d+)$/ ) ) {
        die "Invalid spreadsheet location '$loc'";
    }
    return ( ( $2 - 1 ), letters_to_perl_index($1) );
}

# Translate 0,0 to A1... 1,1 to B2
sub perl_index_to_spreadsheet_loc {
    my $rownum = shift;    # Index 0 row specification
    my $colnum = shift;    # Index 0 column specification
    if ( ( not defined $colnum ) || ( $colnum !~ m/^\d+$/ ) ) {
        die "Invalid colnum '$colnum'";
    }
    if ( ( not defined $rownum ) || ( $rownum !~ m/^\d+$/ ) ) {
        die "Invalid rownum '$rownum'";
    }
    return perl_index_to_letters($colnum) . ( $rownum + 1 );
}

# Translates an alphabetic column letter (or double letter) into a
# 0 indexed number
sub letters_to_perl_index {
    my $letters = defined( $_[0] ) ? uc(shift) : '';    # Letters-based column spec
    if ( $letters =~ m/^[A-Z]$/ ) {

        # Single letter
        return ord($letters) - 65;
    } elsif ( $letters =~ m/^([A-Z])([A-Z])$/ ) {

        # Double letters
        # Yes it's supposed to be 64 for one and 65 for the other
        # one's base 0 and the other is base 1
        return ( ( ord($1) - 64 ) * 26 ) + ord($2) - 65;
    }
    die "Invalid letter-based column '$letters'";
}

# Translates a 0 indexed numeric column number into a text column
# letter or double letter
sub perl_index_to_letters {
    my $colnum = shift;    # Index 0 column number spec
    if ( ( not defined $colnum ) || ( $colnum !~ m/^\d+$/ ) ) {
        die "Invalid colnum '$colnum'";
    }
    if ( $colnum < 26 ) {

        # Single letter
        return chr( $colnum + 65 );
    } elsif ( $colnum < 676 ) {

        # Double letters
        # Yes it's supposed to be 64 for one and 65 for the other
        # one's base 0 and the other is base 1
        return chr( int( $colnum / 26 ) + 64 ) . chr( ( $colnum % 26 ) + 65 );
    }
    die "Colnum out of range: '$colnum'";
}

1;