Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw

Parse Excel File

by vinoth.ree (Monsignor)
on Jun 24, 2013 at 06:48 UTC ( #1040382=perlquestion: print w/replies, xml ) Need Help??
vinoth.ree has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks,

Here its my requirement, I need to parse an excel sheet and update the content into database. For this i wrote the following code

sub Parse_Excel { my $Excel_File = shift; unless(defined($Excel_File)){print "Invalid File Name\n";} my $Parser_OBJ = new Spreadsheet::ParseExcel; my $WorkBook = $Parser_OBJ->Parse($Excel_File); if ( !defined $WorkBook ) { die $Parser_OBJ->error(), ".\n"; } my($iR, $iC, $oWkS, $oWkC); print "FILE :", $WorkBook->{File} , "\n"; print "COUNT :", $WorkBook->{SheetCount} , "\n"; print "AUTHOR:", $WorkBook->{Author} , "\n\n" if defined $WorkBoo +k->{Author}; for(my $iSheet=0; $iSheet < $WorkBook->{SheetCount} ; $iSheet++) # +Sheet { $oWkS = $WorkBook->{Worksheet}[$iSheet]; print "SHEET:", $oWkS->{Name}, "\n"; for(my $iR = $oWkS->{MinRow};defined $oWkS->{MaxRow} && $iR <= + $oWkS->{MaxRow};$iR++) # Row { print $oWkS->{MaxRow}."\n"; # prints 12 for(my $iC = $oWkS->{MinCol};defined $oWkS->{MaxCol} && $i +C <= $oWkS->{MaxCol};$iC++) #Column { $oWkC = $oWkS->{Cells}[$iR][$iC]; next if($iR == 0); if($iC == 0) { if ($oWkC){ push(@{$Time_Sheet{Data}->{'date'}},$o +WkC->Value);} else{ push(@{$Time_Sheet{Data}->{'date'}}, " ");} } elsif($iC == 1) { if ($oWkC){push(@{$Time_Sheet{Data}->{'project'}}, +$oWkC->Value);} else{ push(@{$Time_Sheet{Data}->{'project'}}, " ") +;} } elsif($iC == 2) { if($oWkC){push(@{$Time_Sheet{Data}->{'desc'}},$oWk +C->Value);} else{ push(@{$Time_Sheet{Data}->{'desc'}}, " ");} } elsif($iC == 3) { if($oWkC){push(@{$Time_Sheet{Data}->{'hrs'}},$oWkC +->Value);} else{ push(@{$Time_Sheet{Data}->{'hrs'}}, "H");} } } } } }

Here the line  print $oWkS->{MaxRow}."\n"; prints 12, but actually in my excel sheet i have only 6 rows.

All is well

Replies are listed 'Best First'.
Re: Parse Excel File
by Tux (Abbot) on Jun 24, 2013 at 06:53 UTC

    You see data in just 6 rows, but the other rows might still be there, but without real data. This can happen is the sheet had some data that was deleted, or if it has a space for example, that doesn't show to your eyes.

    Maybe Spreadsheet::Read is an option to you, it has the feature to strip empty rows and columns from the ends

    Enjoy, Have FUN! H.Merijn
Re: Parse Excel File
by sundialsvc4 (Abbot) on Jun 24, 2013 at 13:24 UTC

    If you are running in a Windows environment, also seriously consider using OLE to engage Microsoft Excel, itself, in the task of reading its file and handing the data to you.   The reason why I say this is that, well, real-life spreadsheets are complicated beasts and people tend to fiddle with them ... in one-off ways that can fuddle logic that endeavors to read the files directly.   (The result is usually “answers that are incorrect, but you don’t know it.)   Excel can hand you rows-and-columns, and it can locate named-ranges within the workbook (a big plus for future-proofing), and so on, and do it in a “tolerant” way.   Plus, the performance is quite reasonable given all that is going on.   I find that I spend, overall, “less time and less stress” with that approach.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1040382]
Approved by Happy-the-monk
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (4)
As of 2018-06-23 16:02 GMT
Find Nodes?
    Voting Booth?
    Should cpanminus be part of the standard Perl release?

    Results (125 votes). Check out past polls.