Hi Monks,
I wrote a script using Win32::OLE module to parse an excel file. I am able to extract the value of the particular cell using the function "$Sheet->Cells($row,$col)->{'Value'}". But I am not able to extract one full row value at one shot.
Example
Input File : test.xls
EmpName EmpNumber EmpLocation
Shittal 90786 NY
Xiang 20874 NJ
Here I want to extract the complete row 1 value to one variable.
Is it possible? please advise.
Expected output:
Row two val = > Xiang 20874 NJ
My code is here,
use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
# get already active Excel application or open new
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
|| Win32::OLE->new('Excel.Application', 'Quit');
# open Excel file
my $Book = $Excel->Workbooks->Open("D:/test.xls");
my $Sheet = $Book->Worksheets(2);
my $Tot_Rows= $Sheet->UsedRange->Rows->{'Count'};
my $Tot_Cols= $Sheet->UsedRange->Columns->{'Count'};
print"Number of Rows=> $Tot_Rows\n";
print"Number of Cols=> $Tot_Cols\n";
foreach my $row (1..10)
{
foreach my $col (1..10)
{
# skip empty cells
next unless defined $Sheet->Cells($row,$col)->{'Value'};
# print out the contents of a cell
printf "At ($row, $col) the value is %s and the formula is %s\n",
$Sheet->Cells($row,$col)->{'Value'},
$Sheet->Cells($row,$col)->{'Formula'};
my $newval = $Sheet->Cells($row,$col)->{'Value'};
;
print"Row two val = $sheet->{'value'};
}
}
# clean up after ourselves
$Book->Close;
Thanks,
madtoperl