#!/usr/bin/perl
use utf8;
use strict;
use warnings;
use Data::Dumper;
use File::MMagic;
use Number::Latin;
use Excel::Writer::XLSX;
use List::Util qw( first );
use Spreadsheet::ParseXLSX;
use Spreadsheet::ParseExcel;

die "Usage: $0 Primary (excel sheet) Secondary (excel sheet) Name of n
+ew (excel sheet)\n"
    if (scalar @ARGV != 3);

sub _checkFilesExtensions {
    my ( $fileInput ) = @_;

    foreach my $file (@$fileInput) {
    # Match a dot, followed by any number of non-dots until the
    # end of the line.
    my ($input, $ext) = split('\.', $file);

    die "Please use striclty excel files format .xls or .xlsx '$file'\
+n"
        unless defined $ext;
    die "Please use striclty excel files format .xls or .xlsx '$file'\
+n"
        unless ( $ext eq 'xls' or $ext eq 'xlsx' );
    }
    return;
}

sub _getObjectSpreadSheet {
    my ( $FileToOpen ) = @_;
    my $ft = File::MMagic->new();
    my $type = $ft->checktype_filename( $FileToOpen );
    my $parser;

    if ($type =~ /zip/){
    $parser = Spreadsheet::ParseXLSX->new();
    } else {
    $parser = Spreadsheet::ParseExcel->new();
    }

    my $workbook = $parser->parse( $FileToOpen );
    if ( !defined $workbook ) {
    die $parser->error(), ".\n";
    }
    return $workbook->worksheet( 0 );
}

sub _getDataFromSpreadSheet {
    my ( $worksheet , $RouterIDColumn , $WanInterfaceColumn ) = @_;

    my ( $row_min, $row_max ) = $worksheet->row_range();

    my $name = $worksheet->get_name();

    my %HoH;
    my %hash;
    foreach my $row ( $row_min .. $row_max ) {
    my $cellWanInterface = $worksheet->get_cell( $row, $WanInterfaceCo
+lumn );
    next unless $cellWanInterface;
    if ($cellWanInterface->value()) {
        my $cellRouteID = $worksheet->get_cell( $row, $RouterIDColumn 
+);
        if ($WanInterfaceColumn) {
        $hash{$cellRouteID->value()} = $cellWanInterface->value();
        $row = $row+1; # Plus one because row starts from 0 and counti
+ng from 1
        $HoH{uc int2latin($RouterIDColumn +1).$row} =
        { 'RouterID' => $cellRouteID->value(),
              'Wan Interdace' => $cellWanInterface->value(),
        };
        }
        else {
        $hash{$cellWanInterface->value()} = undef;
        $row = $row+1; # Plus one because row starts from 0 and counti
+ng from 1
        $HoH{uc int2latin($WanInterfaceColumn +1).$row} =
        { 'RouterID' => $cellWanInterface->value(),
              'Wan Interdace' => undef,
        };
        }
    }
    next;
    }
    return \%HoH, \%hash;
}

sub _writeIntoCell {
    my ( $key,
     $hashOfHashesWanInterfaceDataValue,
     $hashTechnicalReportDataValue,
     $worksheet,
     $workbook ) = @_;

    my %formatHash = (
        -font   => 'Arial',
        -size   => 8,
    -border => 7,
    -align  => 'center',
    );

    #  Add and define a format
    my $format = $workbook->add_format( %formatHash );

    # Write some text $row, $column, $text, $format
    $worksheet->write( $key,
               $hashOfHashesWanInterfaceDataValue,
               $format );

    my @characters = split(//, $key);
    my @columnArray = grep /^[[:alpha:]]+$/, @characters;
    my @rowArray = grep /^[[:digit:]]+$/, @characters;
    my $column = join('', @columnArray);
    my $row = join('', @rowArray);
    $column++;
    $key = $column . $row;

    $worksheet->write( $key,
               $hashTechnicalReportDataValue,
               $format );
    return;
}

sub getDataTechicalReport {
    my ( $FileToOpen ) = @_;

    my $worksheet = _getObjectSpreadSheet( $FileToOpen );

    my $RouterIDColumn = 1; # RouterID
    my $WanInterfaceColumn = 25; # Wan Interface

    return _getDataFromSpreadSheet( $worksheet,
                    $RouterIDColumn,
                    $WanInterfaceColumn );
}

sub getDataWanInterface {
    my ( $FileToOpen ) = @_;

    my $worksheet = _getObjectSpreadSheet( $FileToOpen );

    my $RouterIDColumn = undef; # We do not care
    my $WanInterfaceColumn = 0; # A column

    return _getDataFromSpreadSheet( $worksheet,
                    $RouterIDColumn,
                    $WanInterfaceColumn );
}

sub spreadsheetToWrite {
    my ( $hashOfHashesWanInterfaceData,
     $hashTechnicalReportData ) = @_;

    # Create a new Excel workbook
    my $workbook = Excel::Writer::XLSX->new( $ARGV[2] );
    die "Problems creating new Excel file: $!" unless defined $workboo
+k;

    # Add a worksheet
    my $worksheet = $workbook->add_worksheet();

    foreach my $key ( keys %$hashOfHashesWanInterfaceData ) {
    my $RouterID =
        first {
        /$hashOfHashesWanInterfaceData->{$key}{'RouterID'}/
    } keys %$hashTechnicalReportData;

    if ( $RouterID ) {
        _writeIntoCell( $key,
                $hashOfHashesWanInterfaceData->{$key}{'RouterID'},
                $hashTechnicalReportData->{$RouterID},
                $worksheet,
                $workbook );
    }
    else {
        _writeIntoCell( $key,
                $hashOfHashesWanInterfaceData->{$key}{'RouterID'},
                "undef",
                $worksheet,
                $workbook );
    }
    #}
    }

    my %headerFormatHash = (
        -font   => 'Calibri',
        -size   => 11,
        -bg_color  => '#09c8eb',
        -bold   => 1,
    -border => 7,
    -align  => 'center',
    );

    my $headerFormat = $workbook->add_format( %headerFormatHash );

    $worksheet->write( 'A1',
               'DNS Name',
               $headerFormat );

    $worksheet->write( 'B1',
               'WAN Interface',
               $headerFormat );

    return;
}

#### main() ####

_checkFilesExtensions(\@ARGV);

my ( $hashOfHashesTechikalReport,
     $hashTechnicalReport ) = getDataTechicalReport( $ARGV[0] );

# print Dumper $hashOfHashesTechikalReport;
# print Dumper %$hashOfHashesTechikalReport{'B1376'};

my @keysTechicalReport = keys %$hashTechnicalReport;

# print Dumper \@keysTechicalReport;

my ( $hashOfHashesWanInterface,
     $hashWanInterface ) = getDataWanInterface( $ARGV[1] );

# print Dumper $hashOfHashesWanInterface;
# print Dumper $hashWanInterface;
# print Dumper %$hashOfHashesWanInterface{'A13'};

my @keysWanInterface = keys %$hashWanInterface;

# print Dumper \@keysWanInterface;

spreadsheetToWrite( $hashOfHashesWanInterface,
            $hashTechnicalReport );

__END__

$ perl test.pl prime.xlsx secondary.xlsx sample.xlsx
[download]