Generate Excel output using Win32::OLE in Perl

Question

I am a beginner in Perl and have tried playing around with Perl much to understand its ways and working! I have a basic knowledge of arays, hashes and related topics. I have to develop a script for a topic and i am quite unsure how to go about it. I desperately need help and am very grateful to anyone who can explain the 'how to do' part!

I have a code with 3 parts in it which does the same thing thrice for 3 different lets say components. Basic idea is, it takes all the components marked 'A' from an excel file, iterates through the excel file, adds up its corresponding RAM and ROM values and prints out the output without duplicate entries. The 2nd and 3rd part are the same but for components 'B' and 'C'. So far i am able to print out the output of all 3 parts in a text file. But now i want all three outputs in an excel workbook as 3 separate worksheets!

I am not particularly sure how to go about it. Any ideas are really welcome!!!

PS: Please forgive me if i have not typed the code right in the forum! This is my first post!!

Here is how my code looks so far:

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

}

Answer 1

A workaround: You could use Excel::Writer::XLSX to create Excel files, it is working fine and quite robust. Here is how you could convert a tab separated file to Excel.

Reading excel: Spreadsheet::XLSX

use Text::Iconv;
my $converter = Text::Iconv -> new ("utf-8", "windows-1251");


use Spreadsheet::XLSX;
my $excel = Spreadsheet::XLSX -> new ('test.xlsx', $converter);
foreach my $sheet (@{$excel -> {Worksheet}}) {
        printf("Sheet: %s\n", $sheet->{Name});
        $sheet -> {MaxRow} ||= $sheet -> {MinRow};
         foreach my $row ($sheet -> {MinRow} .. $sheet -> {MaxRow}) {       
                $sheet -> {MaxCol} ||= $sheet -> {MinCol};              
                foreach my $col ($sheet -> {MinCol} ..  $sheet -> {MaxCol}) {     
                        my $cell = $sheet -> {Cells} [$row] [$col];
                        if ($cell) {
                            printf("( %s , %s ) => %s\n", $row, $col, $cell -> {Val});
                        }

                }
        }
 }

Writing excel: Excel::Writer::XLSX

my $workbook = Excel::Writer::XLSX->new( $xls_filename );
my $worksheet = $workbook->add_worksheet('data');
# Create a format for the headings
my $header_format = $workbook->add_format();
$header_format->set_bold(); 
$header_format->set_size( 18 );
$header_format->set_color( 'black' );
$header_format->set_align( 'center' );
my $row=0;
while (my $line = <$fh>){
    chomp($line);       
    my @cols = split(/\t/,$line);
    for(my $col=0;$col<@cols;$col++){
        if ($row == 0 ){
            $worksheet->write_string( $row, $col, $cols[$col],$header_format );
        } else {
            $worksheet->write_string( $row, $col, $cols[$col] );
        }
    }
    $row++;
}
close($fh); 

I hope this helps you.

Regards,