Creating Excel output using Win32 :: OLE in Perl

I am new to Perl and try to play with Perl to understand its methods and work! I have basic knowledge of arrays, hashes, and related topics. I need to develop a script for the theme, and I'm completely unsure how to do this. I desperately need help and am very grateful to everyone who can explain the “how to” part!

I have code with 3 parts in it that does the same thing three times for three different options, for example, for components. The main idea is that it takes all the components marked “A” from the excel file, iterates through the excel file, adds the corresponding RAM and ROM values ​​and displays the result without duplicate entries. The second and third parts are the same, but for components “B” and “C”. So far I can print the output of all three parts in a text file. But now I want all three exits in an Excel workbook to be 3 separate sheets!

I am not very sure how to do this. Any ideas are really welcome !!!

PS: Please forgive me if I did not type the code directly on the forum! This is my first post!

This is what my code looks like:

# This Test script was created to try out the possible methods to extract all the Names from the
# excel report without duplicate entries and find their corresponding RAM/ROM size sum

# -excel D:\Abc\Test.xlsx  -out D:\Abc\Output

sub usage($) 
{
   return shift(@_) . <<"END_USAGE";
   Usage: $0 -excel               Specify the file path.
                -out outputdirectory  Specify output directiory             
END_USAGE
}

use Getopt::Long;
use Win32::OLE;
use List::Util qw(sum);
use Data::Dumper qw(Dumper);

my $output_path = ();
my $excel_path = ();
my $no_rows = ();
my $lastCol = ();

GetOptions("excel=s"   =>   \$excel_path,
                 "out=s" =>   \$output_path,
           "h|help" =>   \$help,
          );

#help message
die usage("") if ($help);
system(cls);
print "\n*******************************************************************\n";
print "Component Overview \n";
print "*******************************************************************\n";
print "Please wait, Processing may take couple of minutes... \n";

##File handler for the script file.
$log_path = $output_path."\\log.txt";
$output_file_path = $output_path."\\TestExcel.xlsx";
open LogFile,">",$log_path or die "Cannot create the log file:$log_path !!!";
print LogFile "Start time :".localtime()."\n";

# Start Excel and make it visible
my $xlApp = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit');  
$xlApp->{Visible} = 0;

#Opening the work book
my $workBook = $xlApp->Workbooks->Open($excel_path);
#print "X: " . $workBook . " - " . $excel_path . "\n";
my $excelSheet = $workBook->Worksheets("Report");
$excelSheet->Activate();

print "Reading the file...\n";
&ReadExcel();

print LogFile "Completed time :".localtime()."\n";
print "\nCompleted.Please close this window...\n" ;
print "*******************************************************************\n";

# Sub routine to parse the cosipa file
sub ReadExcel()
{
my $row_index;
#Findings the number of valid rows
$no_rows = $excelSheet->UsedRange->Rows->{'Count'};
$lastCol = $excelSheet->UsedRange->Columns->{'Count'};
$row_index = findRowindex();
my @comp_array = ();

# Name => ResourceType => size
my $resultData = {};

for(my $index=($row_index+1);$index<=$no_rows;$index++)
                       {
                            my $X =  $excelSheet->Cells($index,6)->Value();
            my $Y =  $excelSheet->Cells($index,7)->Value();
            my $name = $excelSheet->Cells($index,9)->Value();
            my $resourceType = $excelSheet->Cells($index,3)->Value();
            my $size = $excelSheet->Cells($index,2)->Value();

            #Name Overview                              
            my $currNameTypeMap;

            if ( ! exists $resultNameData->{ $name } )          # ->: arrow operator is used to dereference reference to arrays or hashes.
                {
                    $resultNameData->{ $name } = {};
                }
            $currNameTypeMap = $resultNameData->{ $name };

            $currNameTypeMap->{ $resourceType } += $size;       

            # Y Overview
            my $currYTypeMap;

            if ( ! exists $resultYData->{ $Y } )            # ->: arrow operator is used to dereference reference to arrays or hashes.
                {
                    $resultYData->{ $cluster } = {};
                }
            $currYTypeMap = $resultYData->{ $Y };

            $currYTypeMap->{ $resourceType } += $size;  

            # X Overview
            my $currXTypeMap;

            if ( ! exists $resultXData->{ $X } )            # ->: arrow operator is used to dereference reference to arrays or hashes.
                {
                    $resultXData->{ $X } = {};
                }
            $currXTypeMap = $resultXData->{ $X };

            $currXTypeMap->{ $resourceType } += $size;      
        }   

    my @uniqNameArr = sort keys %$resultNameData;

    my @uniqYArr = sort keys %$resultYData;

    my @uniqXArr = sort keys %$resultXData;

            for my $currName ( @uniqNameArr )
            {
                print $currName . "\n". " RAM: " . $resultNameData->{ $currName }-> { "RAM" } . ", ROM: " . $resultNameData->{ $currName }-> { "ROM" } . "\n";
                #print Dumper %$resultData;
            }

            print "----------------------------------------------------------------------- \n";

            for my $currY ( @uniqYArr )
            {
                print $currY. "\n". " RAM: " . $resultYData->{ $currY }-> { "RAM" } . ", ROM: " . $resultYData->{ $currY }-> { "ROM" } . "\n";
            }

            print "------------------------------------------------------------------------ \n";

            for my $currX ( @uniqXArr )
            {
                print $currX . "\n". " RAM: " . $resultXData->{ $currX }-> { "RAM" } . ", ROM: " . $resultXData->{ $currX }-> { "ROM" } . "\n";
            }

}

#Sub routine to find the starting row index
sub findRowindex()
{
my $ret = ();
for(my $index=1;$index<$no_rows;$index++)
{
    if(defined($excelSheet->Cells($index,1)))
    {
        my $cel_value = $excelSheet->Cells($index,1)->Value();
        if($cel_value =~ m/^Name$/i)
        {
            $ret = $index;
            last;
        }
    }
}
return $ret;

}

#Trim function
sub trim {
(my $s = $_[0]) =~ s/^\s+|\s+$//g;
return $s;        

}