Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses
 
PerlMonks  

Excel on screen

by esr (Scribe)
on Nov 22, 2004 at 20:31 UTC ( #409724=perlquestion: print w/ replies, xml ) Need Help??
esr has asked for the wisdom of the Perl Monks concerning the following question:

I am not new to Perl but I am new to the use of the Win32::OLE morass. I have managed to get some basic things to work in opening an Excel spreadsheet etc. but have not yet found how to get the Excel spreadsheet to open and display on the screen. What I am trying to accomplish is to do some preliminary things from the Perl script, display the spreadsheet for manual editing, then return to the Perl script to read and process the spreadsheet data. I seem to be able to display the spreadsheet on the screen by going to the Windows Explorer and clicking on the file but I would like to do it from the Perl script.

Comment on Excel on screen
Re: Excel on screen
by dragonchild (Archbishop) on Nov 22, 2004 at 20:35 UTC
    What's wrong with system( "excel.exe $filename" );?

    Being right, does not endow the right to be rude; politeness costs nothing.
    Being unknowing, is not the same as being stupid.
    Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence.
    Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.

Re: Excel on screen
by Rhose (Priest) on Nov 22, 2004 at 20:55 UTC
Re: Excel on screen
by Anonymous Monk on Nov 22, 2004 at 21:58 UTC
    Maybe this could help you get started:
    use Win32::OLE;
    use Win32::OLE::Const 'Microsoft Excel';
    
    $Excel = Win32::OLE->new("Excel.Application"); 
    $Excel->{Visible} = 1;
    $Book = $Excel->Workbooks->Open("C:/spreadsheet.xls"); 
    
      Thanks. It was the "Visible" line that I needed to know about.
Re: Excel on screen
by Anonymous Monk on Nov 22, 2004 at 22:11 UTC
    use OLE; $file = 'c:\path\to\file.xls'; if(!-e $file) { print "File does not exist"; exit 0; } $app = CreateObject OLE 'Excel.Application' || die "Can't open EXCEL $ +!\n"; $book = $app->Workbooks->Open($file); $sheet = $book->Worksheets(1); # Retrieve $cellA1 = $sheet->Range("A1")->{'Value'}; $cellB1 = $sheet->Range("B1")->{'Value'}; print "|$cellA1|$cellB1|\n"; # Do pre-processing $sheet->Range("A1")->{'Value'} = 'pre'; $sheet->Range("B1")->{'Value'} = 'process'; $cellA1 = $sheet->Range("A1")->{'Value'}; $cellB1 = $sheet->Range("B1")->{'Value'}; print "|$cellA1|$cellB1|\n"; $app->{'Visible'} = 1; # USER manual input happens here # You can let them close when done (ctrl-f4 is quick) while($app->ActiveWorkbook) { sleep(1); } # Or you can give them time on time sleep(10); # Close it up if you do time if($app->ActiveWorkbook) { $app->ActiveWorkbook->Close(0); $app->Quit(); } # Do post-processing $app = CreateObject OLE 'Excel.Application' || die "Can't open EXCEL $ +!\n"; $book = $app->Workbooks->Open($file); $sheet = $book->Worksheets(1); # Retrieve $cellA1 = $sheet->Range("A1")->{'Value'}; $cellB1 = $sheet->Range("B1")->{'Value'}; print "|$cellA1|$cellB1|\n"; # Do post-processing work $sheet->Range("A1")->{'Value'} = 'post'; $sheet->Range("B1")->{'Value'} = 'process'; $cellA1 = $sheet->Range("A1")->{'Value'}; $cellB1 = $sheet->Range("B1")->{'Value'}; print "|$cellA1|$cellB1|\n"; $app->save(); $app->ActiveWorkbook->Close(0); $app->Quit(); # ThinMonk (TM)
Re: Excel on screen
by quinkan (Monk) on Nov 23, 2004 at 05:27 UTC
    For all this and even more still, check out Win32::GuiTest in its latest version, found in Files at http://groups.yahoo.com/group/perlguitest/ And the recorder that comes with it should be able to generate your opening code for you.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (10)
As of 2014-07-14 09:12 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    When choosing user names for websites, I prefer to use:








    Results (257 votes), past polls