#!/usr/bin/env perl
use strict;
no warnings;
use Spreadsheet::XLSX;
use Spreadsheet::ParseXLSX;
use Excel::Writer::XLSX;

my $appsshhetname = "syslog_-prod";
my $ADdata = "AD-Data";
my $no = "NOt exist";
my $cellA;
my $cellB;
my $cellC;
our @cellA;
our @cellC;
our @redi;
my $responseid;
my $row;
my %params;

my $workbook  = Excel::Writer::XLSX->new( 'C:/PERL/SYNC-OKTA-User-Profile-Apps-Analysis-Details.xlsx' );
my $worksheet = $workbook->add_worksheet();
my $r = 1;

my $my_format  = $workbook->add_format(
                                            bold    => 1,
                                            color   => 'blue',
                                            size    => 18,
                                                                 );
																 
																  my $parser   = Spreadsheet::ParseXLSX->new();
		    my $workbook1 = $parser->parse('C:/Users/Documents/Work.xlsx');
			
			 if ( !defined $workbook1 ) {
                                         die $parser->error(), ".\n";
                                        }

    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 $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;
				$cellA = $cell->value();
				#$cellA =~ s/@(.*)//g;
				$cellA =~ s/@(AD.ABC.COM)//g;
				
				if ( $cellA=~ /(\d{9})/ ) {
					
					push @cellA, $cellA;
					
					#print "DUMP: @cellA";
                } 
				
				
		}
		
		#print "$cellA  \n";
		
		%params = map { $_ => 1 } @cellA;
			
			#print Dumper %params;
			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;
				$cellC = $cellB->value();
				#$cellC =~ s/@(AD.ABC.COM)//g;
				#print "2nd Entry :$cellC\n";
				#push @cellC , $cellC;
				if(exists($params{$cellC})) {
					
					$worksheet->write($r, 0, $cellC);
				} else {
					#$worksheet->write($r, 0, $no);
					}
		
		}
}
	$r += 1;
	}