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

STDIN Format

by Anonymous Monk
on Mar 31, 2008 at 18:10 UTC ( [id://677598]=perlquestion: print w/replies, xml ) Need Help??

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

So I am trying to find a cell in a spreadsheet. I entered "findme" in the cell and I want to return the cell next to it. So if "findme" was in A1, I want to return B1. If I hard code the script to find "findme" it works just fine. I want to prompt the user so it can be more versatile, but it will not work correctly. Any Ideas?
#use warnings; #use strict; use Cwd; use OLE; use Win32::OLE::Const "Microsoft Excel"; #-----------------------"Copy From" Destination----------------------- +-- my ( $xl_report ) = ( "c:/perl/energy.xls" ); my ( $excel, $workbook, $sheet, $row, @data,$i, $j,$char,$char2,$pri +ntme,$FindMe, @FindMe ); $excel = CreateObject OLE 'Excel.Application'; $excel -> {Visible} = 1; $excel -> {EnableEvents} = "false"; #-----------------------Formating and Setup---------------------- +----- $workbook = $excel -> Workbooks -> Open ( "$xl_report" ) or &te +rminate ( "open $xl_report" ); $sheet = $workbook -> Worksheets( "info" ); $sheet -> Activate; #---------------------Search for Text----------------------------- +--- #print "Find what !?\n"; #$FindMe = <STDIN>;#I also tried multiple types for ($j=1; $j<27; $j++)#loop to go through Columns { $FindMe= findme; $char= chr($j+64); $char2= chr($j+65); for ($i=1; $i<30; $i++)#loop to go through Rows { if (($sheet -> Range ( $char . $i ) -> {Value}) eq $FindMe) { $data = $sheet -> Range ( $char2 . "$i" ) -> {Value}; } } } $sheet -> Range ( "Z" . 1 ) -> {Value} = $data; print "ENDDATA: $data";

Replies are listed 'Best First'.
Re: STDIN Format
by walto (Pilgrim) on Mar 31, 2008 at 18:47 UTC
    $FindMe = <STDIN>;#I also tried multiple types for ($j=1; $j<27; $j++)#loop to go through Columns
    $FindMe = <STDIN>; chomp ($FindMe); for ($j=1; $j<27; $j++)#loop to go through Columns
    $FindMe contains the newline character. chomp eliminates the trailing newline.
Re: STDIN Format
by NetWallah (Canon) on Mar 31, 2008 at 19:24 UTC
    The chomp suggestions above should resolve your immediate issues.

    I offer the following style suggestions, as an alternative I consider superior:

    1. For more idomatic perl, consider using the following alternative way to write your "for $j" and "for $i" loops.

    for my $letter("A".."B"){ for my $num(1..5){ print qq[$letter$num ]; } } # Prints the sequence -- # A1 A2 A3 A4 A5 B1 B2 B3 B4 B5

    2. Instead of writing code to do your search, you could make Excel do the work for you, by using the Find method of the Range object.

    Details at MSDN.
    (FYI: I had previously posted this in response to Simple Question - Simple Answer, I hope)

         "As you get older three things happen. The first is your memory goes, and I can't remember the other two... " - Sir Norman Wisdom

Re: STDIN Format
by tcf03 (Deacon) on Mar 31, 2008 at 18:48 UTC
    try this in your code.
    my $FindMe = <STDIN>; chomp ($FindMe);

    "That which we persist in doing becomes easier, not that the task itself has become easier, but that our ability to perform it has improved."
      --Ralph Waldo Emerson
Re: STDIN Format
by Anonymous Monk on Apr 01, 2008 at 12:54 UTC
    Hey Thanks for all the help, it was the chomp() that did it. I will look into the FIND and Range functions, thanks for the suggestions.

Log In?

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

How do I use this?Last hourOther CB clients
Other Users?
Others romping around the Monastery: (4)
As of 2024-06-13 08:28 GMT
Find Nodes?
    Voting Booth?

    No recent polls found

    erzuuli‥ 🛈The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.