#!/usr/bin/perl
use strict;
use warnings;

use Spreadsheet::ParseXLSX;
use Excel::Writer::XLSX;

my $parser   = Spreadsheet::ParseXLSX->new;
my $workbook1 = $parser->parse('input.xlsx');

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

my $workbook  = Excel::Writer::XLSX->new( 'output.xlsx' );
my $worksheet = $workbook->add_worksheet;

my $Synchrony_format = $workbook->add_format(
    bold    => 0,
    color   => 'green',
    size    => 16,
);

my $my_format  = $workbook->add_format(
    bold    => 1,
    color   => 'blue',
    size    => 18,
);

my $appsshhetname = "syslog_-prod";
my $ADdata = "AD-Data";

my $row_min = 1;
my $row_max = 5;
my $col_min = 1;
my $col_max = 3;

my $r = 1;
my @cellA;
for my $worksheet1 ($workbook1->worksheets) {
    $worksheet->write(0, 0, 'AD Match' , $my_format);
    $worksheet->write(0, 2, 'AD MATCH' , $Synchrony_format);
    $worksheet->write(0, 3, 'Creation Date' , $Synchrony_format);
    my $appssheet = $worksheet1->get_name;
    for my $row ( $row_min .. $row_max ) {
        for my $col ( $col_min = 0) {
            if ( $appssheet eq $appsshhetname ) {
                my $cell = $worksheet1->get_cell( $row, 1 );
                next unless $cell;

                my $cellA = $cell->value;
                $cellA =~ s/@(AD.ABC.COM)//g;
                push @cellA, $cellA if $cellA=~ /(\d{9})/;
            }
            my %params = map { $_ => 1 } @cellA;
            my @uniq = keys %params;
            if ( $appssheet eq $ADdata ) {
                for my $row1 ( $row_min .. $row_max ) {
                    for my $col1 ( $col_min = 1) {
                        my $cellB = $worksheet1->get_cell( $row, 1 );
                        next unless $cellB;

                        my $cellC = $cellB->value;
                        $worksheet->write($r, 0, $cellC) if exists $params{$cellC};
                    }
                }
                $r += 1;
            }
        }
    }
}