use strict;
use warnings;

use Excel::Writer::XLSX;
use Cwd;

my $directory = &getcwd;   # copy the main dierctory path (where the script has been saved)
my $Excel_file = $directory . "\\Template" . ".xlsx";



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

	# my colors names
	my %colors = (
					0x08, 'black',
					0x0C, 'blue',
					0x10, 'brown',
					0x0F, 'cyan',
					0x17, 'gray',
					0x11, 'green',
					0x0B, 'lime',
					0x0E, 'magenta',
					0x12, 'navy',
					0x35, 'orange',
					0x21, 'pink',
					0x14, 'purple',
					0x0A, 'red',
					0x16, 'silver',
					0x09, 'white',
					0x0D, 'yellow',
				 );
				 
	my $i = 0;
	my $j = 0;	
	my $col = 0; # my column
	my $row;	 # my row	
	my $block = 0;

			 
#-------------set the column width ($col, $row, width)--------------------------
	my $sheet = $workbook -> add_worksheet('My Sheet');

	for $block (0..3)
	{
		
			$sheet -> set_column ($col,   0, 40);  
			$sheet -> set_column ($col+1, 0, 40);
			$sheet -> set_column ($col+2, 0, 40);
			$sheet -> set_column ($col+3, 0, 12);
			$sheet -> set_column ($col+4, 0, 12);
			$sheet -> set_column ($col+5, 0, 15);
			$sheet -> set_column ($col+6, 0, 60);
			$sheet -> set_column ($col+7, 0, 60);
			$sheet -> set_column ($col+8, 0,  1);
		$col = $col+9;
		
	}


	my $format0 = $workbook -> add_format();
	$format0 -> set_bg_color('black');
	$format0 -> set_border(1);
	$format0 -> set_border_color('black');

	my $format1 = $workbook -> add_format();
	$format1 -> set_bold();
	$format1 -> set_size(10);
	$format1 -> set_color('white');
	$format1 -> set_bg_color('purple');
	$format1 -> set_align('left');
	$format1 -> set_border(1);
	$format1 -> set_border_color('black');

	my $format2 = $workbook -> add_format();
	$format2 -> set_bold();
	$format2 -> set_size(10);
	$format2 -> set_color('black');
	$format2 -> set_bg_color('red');
	$format2 -> set_align('left');
	$format2 -> set_border(1);
	$format2 -> set_border_color('black');

	my $format3 = $workbook -> add_format();
	$format3 -> set_bold();
	$format3 -> set_size(10);
	$format3 -> set_color('black');
	$format3 -> set_bg_color('lime');
	$format3 -> set_align('left');
	$format3 -> set_border(1);
	$format3 -> set_border_color('black');

	my $format4 = $workbook -> add_format();
	$format4 -> set_bold();
	$format4 -> set_size(10);
	$format4 -> set_color('black');
	$format4 -> set_bg_color('yellow');
	$format4 -> set_align('left');
	$format4 -> set_border(1);
	$format4 -> set_border_color('black');

	my $format5 = $workbook -> add_format();
	$format5 -> set_bold();
	$format5 -> set_size(10);
	$format5 -> set_color('black');
	$format5 -> set_bg_color('white');
	$format5 -> set_align('left');
	$format5 -> set_border(1);
	$format5 -> set_border_color('black');

	my $format6 = $workbook -> add_format();
	$format6 -> set_bg_color('blue');
	$format6 -> set_size(10);
	$format6 -> set_color('white');
	$format6 -> set_border(1);
	$format6 -> set_border_color('black');

	my $format7 = $workbook -> add_format();
	$format7 -> set_bg_color('black');
	$format7 -> set_border(1);
	$format7 -> set_border_color('black');

	my $format8 = $workbook -> add_format();
	$format8 -> set_bg_color('white');
	$format8 -> set_border(1);
	$format8 -> set_border_color('black');

	my $format9 = $workbook -> add_format( font => 'Arial',
										   bg_color => 'white',
										   border => 3,
	
										 );
		
	my $format10 = $workbook -> add_format( font => 'Arial', 
										    bg_color => 'yellow', 
										    color => 'black', 
										    bold => 1
										   ); 
	
	my $format11 = $workbook -> add_format( font => 'Arial', 
										    color => 'red', 
										    bold => 1
										   );

	my $format12 = $workbook -> add_format( font => 'Arial', 
										    color => 'green', 
										    bold => 1
										   );										   
										   


			#my $format = $workbook->add_format();
			#$format->set_border(02);
			#$format->set_border_color('black');
			
	$col = 0;

	for $block (0..3)
	{
		for  $j ($col+2..$col+4)
		{
			$sheet -> write (7,  $j, " ", $format1);
			$sheet -> write (8,  $j, " ", $format5);
			$sheet -> write (9,  $j, " ", $format3);
			$sheet -> write (10, $j, " ", $format4);
			$sheet -> write (11, $j, " ", $format5);
		}


		for $j ($col..$col+7)
		{
			$sheet -> write (12, $j, " ", $format1);
			$sheet -> write (13, $j, " ", $format6);
			$sheet -> write (14, $j, " ", $format7);
			$sheet -> write (15, $j, " ", $format8);
			$sheet -> write (16, $j, " ", $format8);
			$sheet -> write (17, $j, " ", $format8);
		}

		for $i (0..17)
		{
			$sheet -> write ($i, $col+8, " ", $format0);
		}
		
		$sheet -> autofilter('A14:F300');
		$col = $col+9;			
	}
		
#-----------Conditional formatting: bg_color, string color according to cell value 	
		$sheet -> conditional_formatting ( 'C16:AF18',
					{
						type     => 'cell',
						criteria => '=',
						value    => '"No internal variable given."',
						format   => $format10,
					}
				);
				
		$sheet -> conditional_formatting ( 'C16:AF18',
					{
						type     => 'cell',
						criteria => '=',
						value    => '"warning"',
						format   => $format10,
					}
				);
				
				$sheet -> conditional_formatting ( 'C16:AF18',
					{
						type     => 'cell',
						criteria => '=',
						value    => '"failed"',
						format   => $format11,
					}
				);
				
				$sheet -> conditional_formatting ( 'C16:AF18',
					{
						type     => 'cell',
						criteria => '=',
						value    => '"passed"',
						format   => $format12,
					}
				);

$workbook->close();
	

sub terminate
{
    print "\a\a\nCould not @_ for the following reason:\n\n$!\n";
    print "This program will now terminate.  Press <ENTER> to exit.";
    <STDIN>;
    exit;
}