use strict;
use warnings;
use Cwd;
use OLE;
use Win32::OLE::Const "Microsoft Excel";
use constant SCRIPT_VERSION => "1.0.0";
use Data::Dumper;
use Excel::Writer::XLSX;


my $directory = &getcwd;
my $sheet;
my $sheet1;
my $alt;
my $column = 0;
my $tbCnt = 1;
my $workbook;
my $workbook1;
my $excel;
my $input;
my $block;
my $Excel_file = $directory . "\\Test_Excel" . ".xlsx";

#my $DIR = $ARGV[0];

#($DIR =~ s/^..// or $DIR =~ s/^.//);

#--if excel file doesn't exist create it------
if (!-e $Excel_file)
{
	print"\n";
	#sleep (1);
	$excel = CreateObject OLE 'Excel.Application';
	$excel -> {Visible} = 1;
	$Excel_file = $directory . "\\Test_Excel" . ".xlsx";
	$workbook = Excel::Writer::XLSX -> new('Test_Excel.xlsx');	
	$sheet = $workbook -> add_worksheet('Test Report1');
	$sheet1 = $sheet;
	$workbook1 = $workbook;
	&template;
}

#---otherwise open it-------------------------
else
{
	$excel = CreateObject OLE 'Excel.Application';
	$excel -> {Visible} = 1;
			  
	$workbook = $excel    -> Workbooks  -> Open ( "$Excel_file" ) or die ( " can't open $Excel_file" );
	#$sheet    = $workbook -> Worksheets(1);
	$sheet    = $workbook -> Worksheets("Test Report" . $workbook -> Worksheets -> {Count});
		
	$sheet -> Activate();      
	$excel -> ActiveWindow    -> {FreezePanes} = "True";
	$excel    -> {DisplayAlerts} = 0;
}

#---blocs delimitation-------------------------------
print "\nIs this a new Excel Document? (y/n): ";
$input = <STDIN>;
chomp $input;
#$input_2 = 1;
print"\n";


my @alphabet = ("A".."ZZ");

if ($input =~ /y/i)
{	
	$column = 0;
	#$sheet -> Range ( "D:E" ) -> {HorizontalAlignment} = xlHAlignCenter;

	#$input_2 = 1;	
}

elsif($input =~ /n/i)
{
	$alt = $sheet -> Range( 'A14'  )-> {Value};
	print"value A14: $alt\n";
	print"table's number: $tbCnt \n";
	
	if($alt eq '')
	{
		$column = 0;	
	}
	else
	{		
		do
		{ 			
			$column = $column+9;						
			$alt = $sheet -> Range( $alphabet[$column]. '14' ) -> {Value};
			$tbCnt = $tbCnt + 1;
			print"value $alphabet[$column]14: $alt\n";
			print"table's number: $tbCnt \n";
		}
			
		until( $alt ne 'Frame');		
	}
}

#---new worksheet if the current already has 4 tables-------
if($tbCnt == 5)
{
	$tbCnt = 1;
	$column = 0;
	
	$sheet = $workbook -> Worksheets -> Add({after =>
			 $workbook -> Worksheets($workbook -> Worksheets -> {count})});
			 
	$sheet -> {Name} = "Test Report" . $workbook -> Worksheets -> {Count};	
	&template;	
}	

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