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;            
        }       
}