Beefy Boxes and Bandwidth Generously Provided by pair Networks
Come for the quick hacks, stay for the epiphanies.
 
PerlMonks  

How to print row of excel file in perl

by madtoperl (Hermit)
on Nov 11, 2008 at 06:34 UTC ( [id://722790]=perlquestion: print w/replies, xml ) Need Help??

madtoperl has asked for the wisdom of the Perl Monks concerning the following question:

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

Replies are listed 'Best First'.
Re: How to print row of excel file in perl
by Andrew Coolman (Hermit) on Nov 11, 2008 at 10:03 UTC
    What you can use is the Value from Range method. It could be something like this:
    my $cols_pos = 'c'; my $row = 3; my $selected_range = $Sheet->Range("a${row}:${cols_pos}${row}")->{Valu +e}; $onerow = join (' ', @{$selected_range->[0]}); # 0 just because we kno +w its only one row which we want
    The Range parameter is the start cell and the end cell like if you selected in Excel. So how you set row and cell position is up to you. Moreover you can get the whole table and process it in Perl array of arrays which has significant time impact then process it by rows or cells. And also the write by Range works also.
    For some huge tables you can reduce the processing from several minutes to few seconds. The OLE cell access is really slow for huge tables.

    Regards,
    s++ą  ł˝ ął. Ş ş şą Żľ ľą˛ş ą ŻĽąş.}++y~-~?-{~/s**$_*ee
Re: How to print row of excel file in perl
by CountZero (Bishop) on Nov 11, 2008 at 20:40 UTC
    Spreadsheet::ParseExcel::Simple has the very handy
    my @data = $sheet->next_row;
    method to read an entire row of cells into an array.

    CountZero

    A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

Re: How to print row of excel file in perl
by sanku (Beadle) on Nov 11, 2008 at 11:45 UTC
    hi, Try out this one actually i am using linux machine so i tried it in linux. Here is the code
    #!/usr/bin/perl use strict; use Spreadsheet::ParseExcel; my $file='/tmp/test.xls'; my $excel = Spreadsheet::ParseExcel::Workbook->Parse($file); foreach my $sheet (@{$excel->{Worksheet}}) { $sheet->{MaxRow} ||= $sheet->{MinRow}; foreach my $row ($sheet->{MinRow} .. $sheet->{MaxRow}) { if($row >= 1){ print "Row", $row ,"value =>";} $sheet->{MaxCol} ||= $sheet->{MinCol}; foreach my $col ($sheet->{MinCol} .. $sheet->{MaxCol}) { my $cell = $sheet->{Cells}[$row][$col]; if ($cell) { if($row >= 1){ printf ("%s ", $cell->{Val});} } } if($row >= 1){ print "\n";} } }
Re: How to print row of excel file in perl
by Anonymous Monk on Jan 17, 2013 at 19:22 UTC
    Here one The problem is you have used $sheet ,, instead of $sheet use $Sheet.. you will get all the data..

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://722790]
Approved by Sixtease
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others taking refuge in the Monastery: (11)
As of 2024-03-28 09:07 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found