#!/usr/bin/perl
use strict;
use warnings;
use Spreadsheet::ParseExcel; 
use Spreadsheet::WriteExcel;
use Text::Levenshtein::Damerau qw(edistance);

my $parser = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse ( 'input.xls' ); #Choose input file

if ( !defined $workbook ) {
   die $parser->error(), ".\n";
}

#Create output file
my $workbook1  = Spreadsheet::WriteExcel->new('phon.xls'); #name of output file
my $worksheet1 = $workbook1->add_worksheet('data');
$worksheet1->set_column(0, 4, 18);
my $header_format = $workbook1->add_format(
                                             bold   => 1,
                                             valign => 'vcenter',
                                          );
my $heading1 = 'Target';
my $heading2 = 'Response';
my $heading3 = 'Lev-Dam Distance';
$worksheet1->write('A1', $heading1, $header_format);
$worksheet1->write('B1', $heading2, $header_format);
$worksheet1->write('C1', $heading3, $header_format);

WORKSHEET: #this allows perl to read Excel
for my $worksheet ( $workbook->worksheet('PerlInput') ) {

    my $sheetname = $worksheet->get_name('PerlInput');
    my ( $row_min, $row_max ) = $worksheet->row_range();
    my ( $col_min, $col_max ) = $worksheet->col_range();
    my $target_col;
    my $response_col;

# Skip worksheet if it doesn't contain data
    if ( $row_min > $row_max ) {
       warn "\tWorksheet $sheetname doesn't contain data. \n";
       next WORKSHEET;
    }

# Check for column headers
    COLUMN:
    for my $col ( $col_min .. $col_max ) {

        my $cell = $worksheet->get_cell( $row_min, $col );
        next COLUMN unless $cell;

        $target_col   = $col if $cell->value() eq 'Target';
        $response_col = $col if $cell->value() eq 'Response';
    }

    if ( defined $target_col && defined $response_col ) {

# Pull values from cells
        ROW:
        for my $row ( $row_min + 1 .. $row_max ) {
            my $target_cell   = $worksheet->get_cell( $row, $target_col);
            my $response_cell = $worksheet->get_cell( $row, $response_col);

            if ( defined $target_cell && defined $response_cell ) {
                my $target   = $target_cell->value();
                my $response = $response_cell->value();

# Calculate Levenshtein-Damerau distance
		my $distance = edistance("$target","$response"); 

# Copy output to Excel spreadhseet, 'phon.xls'
                             
                foreach ($target) {
                $row++;
                $worksheet1->write( $row-1, 0, "$target\n");
                $worksheet1->write( $row-1, 1, "$response\n");
                $worksheet1->write( $row-1, 2, "$distance\n");
                }
            }

# Error messages if something goes wrong
            else {

                warn "\tWorksheet $sheetname, Row = $row doesn't contain target and response data.\n";
                next ROW;
            }
        }    
    }
    else {
 
        warn "\tWorksheet $sheetname: Didn't find Target and Response headings.\n";
        next WORKSHEET;
    }  
}