#!/usr/bin/perl

use strict;

# Include All the Libraries
use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::SaveParser; 
use Data::Dumper;

# Define Variables

my @xls1;
my @xls2;
my @bold_array;
my @dx;
my @dx_array;
my $oldvalue;
my $newvalue;
my $newcol;
my $newrow;
my $oldcol;
my $oldrow;
my $r;
my $c;
my $v;

my $i = 0; 

# Define a variable for load n times
my $var = 35;

# Define the file names
my $old_file = 'EU-G1_Old.xls';
my $new_file = 'EU-G1_New.xls';

# Print the Start time
print scalar(localtime) . ": Start  \n";
my $parser1 = Spreadsheet::ParseExcel->new(
    CellHandler => \&cell_handler1,
    NotSetCell  => 1
);

# Print the Start Time to read first excel file
print scalar(localtime) . ": Start Reading Excel1 \n";

my $workbook1 = $parser1->Parse($old_file);
sub cell_handler1 {

        my $workbook    = $_[0];
        my $sheet_index = $_[1];
        my $row         = $_[2];
        my $col         = $_[3];
        my $cell        = $_[4];
	 
	push @xls1 , {
			'row1' => $row,
			'col1' => $col,
			'value1' => $cell->value(),	
	};
       return @xls1;
}
#print Dumper(@xls1);
# Print the End Time to read first excel file
print scalar(localtime) . ": End Reading Excel1 \n";
my $parser2 = Spreadsheet::ParseExcel->new(
    CellHandler => \&cell_handler2,
    NotSetCell  => 1
);

# Print the Start Time to read second excel file
print scalar(localtime) . ": Start Reading Excel2 \n";
my $workbook2 = $parser2->Parse($new_file);

sub cell_handler2 {

        my $workbook    = $_[0];
        my $sheet_index = $_[1];
        my $row         = $_[2];
        my $col         = $_[3];
        my $cell        = $_[4];
	 # Construct array for Dx code
	 
        push @xls2 , {
                        'row2' => $row,
                        'col2' => $col,
                        'value2' => $cell->value(),
        };
        return @xls2;

}
#print Dumper(@xls2);
# Print the End Time to read second excel file
print scalar(localtime) . ": End Reading Excel2 \n";

print scalar(localtime) . ": Start Populating Bolding Array \n";
# Inefficient way of looping
# Please change this as it could boost the performance significantly 
for(@xls2) {
	if($_->{'col2'} > 32 && $_->{'row2'} > '0' ) {
			$newvalue = $_->{'value2'};
			$newrow = $_->{'row2'};
			$newcol = $_->{'col2'};
			for(@xls1) {
				$oldrow = $_->{'row1'};
				$oldcol = $_->{'col1'};	
				if($_->{'col1'} > 32 && $_->{'row1'} > 0 && $_->{'row1'} eq $newrow && $_->{'col1'} eq $newcol) {
						$oldvalue = $_->{'value1'};
						last;
				}
			}
			print "New Row : " . $newrow . "\n";
			print "Old Row : " . $oldrow . "\n";
			print "New Col : " . $newcol . "\n";
			print "Old Col : " . $oldcol . "\n";
			print "NewValue:" . $newvalue . "\n"; 
		       print "OldValue:" . $oldvalue . "\n"; 
			if($oldvalue ne $newvalue) {
				push @bold_array , {
					'row' => $_->{'row2'},
					'col' => $_->{'col2'},
					'value' => $_->{'value2'},
				};
				# Only push the rows 
				push (@dx, $_->{'row2'});  
			}
	}
}
print "DX CODE" ."\n";
print Dumper(@dx);
print "DX CODE END" ."\n";
my $prev = 'nonesuch';
my @unique = grep($_ ne $prev && ($prev = $_), @dx);
print Dumper(@unique);
# Using the row array get the values
for(@xls2) {
	$r = $_->{'row2'};
	$c = $_->{'col2'};
	$v = $_->{'value2'};
	for(@unique) {
		if($r eq $_ && $c eq '26' ) {
			print "R:" . $r . "\n";
			print "C:" . $c . "\n";
			print "V:" . $v . "\n";
			print Dumper($_);
			push @dx_array , {
				'rowdx' => $r,
				'coldx' => $c,
				'valuedx' => $v,
			};
			#last;
		}
	}	
}
# End
# Get the Dx code value
print Dumper(@dx_array);
print scalar(localtime) . ": End Populating Bolding Array \n";
if(scalar(@bold_array) > 0) {
	 # Logic to bold
	 print scalar(localtime) . ": Start Opening Excel File  \n";
	 my $parser   = new Spreadsheet::ParseExcel::SaveParser;
        my $template = $parser->Parse($new_file);
      
        my $workbook;
    	 {
        	# SaveAs generates a lot of harmless warnings about unset
        	# Worksheet properties. You can ignore them if you wish.
        	local $^W = 0;

        	# Rewrite the file or save as a new file
		# For now rewrrite into a temp file
        	$workbook = $template->SaveAs('temp.xls');
    	 }

    	 # Use Spreadsheet::WriteExcel methods
    	 my $worksheet  = $workbook->sheets(0);
	 # Set the format to bold
	 my $format = $workbook->add_format();
	 $format->set_bold();	
	 print scalar(localtime) . ": Start Excel File Write \n";
	 foreach(@bold_array) {
			print Dumper($_);
			print scalar(localtime) . ": Start Time Taken to write one cell \n";
			$worksheet->write($_->{'row'}, $_->{'col'} , $_->{'value'} , $format);
			print scalar(localtime) . ": End Time Taken to write one cell \n";
		
	  }
	  foreach(@dx_array) {
		print Dumper($_);
		print scalar(localtime) . ": Start Time Taken to write DX code one cell \n";
		$worksheet->write($_->{'rowdx'}, $_->{'coldx'} , $_->{'valuedx'} , $format);
		print scalar(localtime) . ": End Time Taken to write DX code one cell \n";
         }

	 print scalar(localtime) . ": End Excel File Write \n";
	 $workbook->close();
	 print scalar(localtime) . ": Close Excel File  \n";
	
} else {
	print scalar(localtime) . ": Error Populating Bolding Array Please check\n";	
}
#print "End:" . scalar(localtime) . "\n";
print scalar(localtime) . ": End \n";