Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

Excel Text parsing problem

by reaper9187 (Scribe)
on Nov 08, 2012 at 06:58 UTC ( #1002825=perlquestion: print w/ replies, xml ) Need Help??
reaper9187 has asked for the wisdom of the Perl Monks concerning the following question:

Hi evryone ,
I'm back again with my queries to seek enlightenment from this forum. I'm trying to read an excel file and parse the values, meanwhile perform checks and display values. I have written the code and while executing it, i'm able to print the values in the command prompt using the command
print "$cell->value()";
Everything works perfectly in the command prompt workspace. Now comes the problem.

I'm also using perl Tk to create a user interface to display values that do not meet checks. But when i try to print this value on to the text area by using the command :
$txt->insert('end',"$worksheet->{Cells}[$row][$col]->{Val}");
it does seem to perform the checks on the numeric values correctly, but it does not print the actual name of the corresponding value(eg Name1, name2,etc as in the below eg). The excel file is in this format:
1. Name1 1 2 40
2. Name2 45 7 6
..
What should i do ??/
PS: i tried running both the print options simultaneously and i could observe the value on the command prompt but not on the text window .. help ..!!

Upadated : The Entire Code
#!/usr/local/bin/perl use Tk; # Main Window my $mw = new MainWindow; $mw->Frame(-background => 'red')->pack(-ipadx =>50, -side => "left", - +fill => "y"); #Making a text area my $txt = $mw -> Scrolled('Text',-width => 150, height => 40, -scrollb +ars=>'e', -font => "fixed 8 bold" ) -> pack (); $txt->insert('end', ""); #Declare that there is a menu my $mbar = $mw -> Menu(); $mw -> configure(-menu => $mbar); #The Main Buttons my $file = $mbar -> cascade(-label=>"File", -underline=>0, -tearoff => + 0); my $open = $mbar -> cascade(-label=>"Open", -underline=>0, -tearoff => + 0); my $tool= $mbar -> cascade(-label =>"Tools", -underline=>0, -tearoff = +> 0); my $parser = $mbar -> cascade(-label =>"RL ", -underline=>0, -tearoff +=> 0); my $help = $mbar -> cascade(-label =>"Help", -underline=>0, -tearoff = +> 0); ## File Menu ## $file -> command(-label => "Home", -underline=>0, -command=> \&home ); $file -> command(-label => "Check ", -underline=>0, -command=> \&define ); $file -> checkbutton(-label =>"faults", -underline => 0, -command => \&faults); $file -> separator(); $file -> command(-label =>"Exit", -underline => 1, -command => sub { exit } ); ##Open Menu## $open->command( -label => 'Open', -underline => 0, -command => \&f_ope +n ); $open->command( -label => 'Save', -underline => 0, -command => \&f_sav +e ); $open->separator; $open->command( -label => 'Exit', -underline => 1, -command => sub{ +exit} ); ## Tools Menu ## $tool -> command(-label => "Ne", -underline=>0, -command=> \&nwplan ); $tool -> command(-label => " ", -underline=>0, -command=> \&optimize ); $tool -> separator(); ## Parse Menu ## $parser -> command(-label => "P", -underline=>0, -command=> \&parse ); ## Help Menu ## $help -> command(-label => "About", -underline=>0, -command=> \&help ); MainLoop; sub help { my ($opt) = @_; $mw->messageBox(-message=>"This function is not available yet"); } Mainloop; sub f_open { my $filename = $mw->getOpenFile( -title => 'Open File:', -defaultextension => '.xls', -initialdir => '.' ); warn "Opened $filename\n"; open(MYFILE,$filename); # Text::Iconv is not really required. # This can be any object with the convert method. Or nothing. use Spreadsheet::ParseExcel; my $parser = Spreadsheet::ParseExcel->new(); my $workbook = $parser->parse($filename); if ( !defined $workbook ) { die $parser->error(), ".\n"; } for my $worksheet ( $workbook->worksheet(1) ) { my ( $row_min, $row_max ) = $worksheet->row_range(); my ( $col_min, $col_max ) = $worksheet->col_range(); for my $row ( $row_min .. 20 ) { for my $col ( $col_min .. $col_max ) { my $cell = $worksheet->get_cell( $row, $col ); next unless $cell; print "Row, Col = ($row, $col)\n"; print "Value = ", $cell->value(), "\n"; print "\n"; $txt->insert('end',"$row:$col----$worksheet->{Cells +}[$row][$col]->{Val}\n"); } } } }

Another thing i noticed is that this script seems to run fine on smaller files (worksheets with less rows) .... It displays all data in text area as i expect but acts weirdly for larger files

Comment on Excel Text parsing problem
Select or Download Code
Re: Excel Text parsing problem
by grizzley (Chaplain) on Nov 08, 2012 at 08:11 UTC

    If $cell->value() is printing correct value why not replace not working $worksheet->{Cells}[$row][$col]->{Val} with working $cell->value()?

    It is anyway hard to give better advice as you present two completely different objects and one cannot tell what those contain and how those were created and therefore no way to tell how to fix "broken" line of code. Can you extract smallest possible part of your code that is complete application and presents your problem and paste it here so that we can run it on sample data?

    Update: maybe you will be even able to locate the problem yourself while preparing such minimal app...

      Here you go
      use Spreadsheet::ParseExcel; my $parser = Spreadsheet::ParseExcel->new(); my $workbook = $parser->parse($filename); if ( !defined $workbook ) { die $parser->error(), ".\n"; } for my $worksheet ( $workbook->worksheet(1) ) { my ( $row_min, $row_max ) = $worksheet->row_range(); my ( $col_min, $col_max ) = $worksheet->col_range(); for my $row ( $row_min .. $row_max ) { for my $col ( $col_min .. $col_max ) { my $cell = $worksheet->get_cell( $row, $col ); next unless $cell; print "Row, Col = ($row, $col)\n"; print "Value = ", $cell->value(), "\n"; print "\n"; $txt->insert('end', "$row:$col----$worksheet->{Cells}[$row][$col]->{ +Val}\n"); } } }
      When the print line is executed it gives an output type: Row, Col = (8, 1) Value = LWSHI1C Row, Col = (8, 2) Value = 900 Row, Col = (8, 4) Value = 2 Row, Col = (8, 5) Value = Row, Col = (8, 6) Value = 3
      But the text line gives an output as below
      8:1----8:2---- 8:3---- 8:4---- 8:7----8:8----
      For the same code, the text line is not able to read the name (LWSHI1C) or any of the above values, it only reads certain columns .. Dont know why...

        Well...

        I've replaced worksheet(1) with worksheet(0) (because had data in first worksheet) and replaced $txt->insert with print 'end: ', "$row:$col----$worksheet->{Cells}[$row][$col]->{Val}\n"; statement and got proper results:

        c:\d\test>bla.pl Book1.xls Row, Col = (7, 0) Value = LWSHI1C end: 7:0----LWSHI1C Row, Col = (7, 1) Value = 900 end: 7:1----900 Row, Col = (7, 3) Value = 2 end: 7:3----2 Row, Col = (7, 5) Value = 3 end: 7:5----3 c:\d\test>

        What I can advice in that situation is: please make the same corrections in your script and run it in console to make sure you have data in worksheet(1).

Re: Excel Text parsing problem
by Anonymous Monk on Nov 08, 2012 at 09:40 UTC
    print "$cell->value" will never call value, interpolation doesn't work like that
      What do you suggest ??
        no i think you didnt understand the problem .. When i use the print command , it only displays the text in the command prompt window ( i have absolutely no problem in doing that evn now)... What i'm looking for is a way to print this text on a text area in a GUI window using perl Tk... I have defined the main widnow , text areas, alignment etc.. i have to display this text on the main GUI window..I use the $txt ->insert command to print it in the text area... but somehow it prints only some of the values(rather than all) ...
Reaped: Re: Excel Text parsing problem
by NodeReaper (Curate) on Nov 08, 2012 at 13:52 UTC

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (8)
As of 2014-08-01 00:38 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (256 votes), past polls