|
in reply to Re: Perl r/w Excel with OLE
in thread Perl r/w Excel with OLE
To expand on this, the EXAMPLES section of the Win32::OLE documentation gives a complete walk-through example that shows the quit command for the destructor:
use Win32::OLE;
# use existing instance if Excel is already running
eval {$ex = Win32::OLE->GetActiveObject('Excel.Application')};
die "Excel not installed" if $@;
unless (defined $ex) {
$ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;})
or die "Oops, cannot start Excel";
}
# get a new workbook
$book = $ex->Workbooks->Add;
# write to a particular cell
$sheet = $book->Worksheets(1);
$sheet->Cells(1,1)->{Value} = "foo";
# write a 2 rows by 3 columns range
$sheet->Range("A8:C9")->{Value} = [[ undef, 'Xyzzy', 'Plugh' ],
[ 42, 'Perl', 3.1415 ]];
# print "XyzzyPerl"
$array = $sheet->Range("A8:C9")->{Value};
for (@$array) {
for (@$_) {
print defined($_) ? "$_|" : "<undef>|";
}
print "\n";
}
# save and exit
$book->SaveAs( 'test.xls' );
undef $book;
undef $ex;
Note that the visible=1 is great for debugging, but shouldn't be used in a production environment once your script is complete. Note also that unless you do a clean quit, then it's possible to have many instances of Excel running, in which case you should make them exit with Task Manager. For cleanly working scripts using the quit idiom shown above, there should only be Excel running when the script is doing whatever it needs to do. |
Re^3: Perl r/w Excel with OLE
by dasgar (Priest) on Jun 28, 2012 at 17:07 UTC
|
The first thing that I personally don't like about the sample code you provided is the attempt to check to see if an instance of Excel is running and to take control of it if there is one. Personally, I prefer my script to open a new instance of Excel so that the script does not interfere with the person/script that is using the other instance. Just a personal preference, but wanted to share my thoughts on that topic.
The sample code's method for closing Excel works fine for Excel 2003 and I believe Excel 2007 too. However, Excel 2010 introduces a few new wrinkles. First, you can specify what version to save the file as (Excel 2007 and older format, Excel 2010 format, etc.). Also, if the format and file extension don't match, Excel likes to prompt the user about what to do. If you have Excel 2010, just manually try saving a new file as different formats and extensions to see what I'm talking about. Also, you can record a macros to see what the VB code looks like to handle some of these "save as" scenarios and convert that to Perl for your script. Just wanted to mention this information in case the OP (or any future reader) is using Excel 2010 (or newer) and finds the information useful.
Note that the visible=1 is great for debugging, but shouldn't be used in a production environment once your script is complete.
Ummm...again I disagree and think that this is a matter of personal preference. Although I typical use the visibility setting as you described, I have a coworker who prefers to leave visible set to 1 so that he can visually watch the script as a method to verify that the script really is running and doing what it is supposed to do. Despite disagreeing with him, I personally am not going to tell someone that they shouldn't be doing that. Basically, just applying the concept of TIMTOWTDI.
| [reply] |
|
|
Good points. I guess my model of this kind of code is not that it's going to be running on an individual's PC while they are actively doing other things. I was thinking of a headless Windows Server machine that also has MS Office on it, and Perl is doing automation of that Office. If the Win32::OLE code is an alternative to using the built in VBA code of the Office suite, then all of the advice given contrary to mine is absolutely reasonable.
I have used the visibility setting to see what's happening when debugging, but turning it off when doing a "production" run means that there is no possibility of me interfering with the automated operation (which, for complex multi-setp OLE operations, could happen). I agree it's a personal preference, and that's why I personally prefer it to be invisible once I've got something running properly. :)
| [reply] |
Re^3: Perl r/w Excel with OLE
by davies (Prior) on Jun 28, 2012 at 20:37 UTC
|
I'm afraid I have to disagree extremely strongly with some of the advice you've given here. Some of the issues are minor, such as always hiding the spreadsheet in production code. What if I want to present the user with the spreadsheet? Usually I hide, but it's definitely horses for courses.
More serious is taking control of an existing instance. For a one-off script, this can be a useful club to have in your bag, but I've written elsewhere (Re: Manipulating open excel sheet) about why this is a bad idea.
However, the thing that really terrifies me is that your code not only takes control of one of my instances of Excel (I frequently run several - it can be essential to do this), but then closes it. My work! Where has all my work gone? And there's not even an autosave, as those files are automatically deleted when the creating instance exits normally. Now I'll grant you that it can be useful to loop through all instances of Excel and close them if you know for sure that they are all orphans, but the thought of this getting into production code turns me cold even in this weather.
Regards,
John Davies
| [reply] |
|
|
As said in response to dasgar, these are good points. I am not the original author, but it seemed reasonable to include code provided by the authors of Win32::OLE. It's good to point out to future viewers the possible pitfalls of using such code, including the possibility of "hijacking" an existing Excel instance doing other things. As I said, my mental model of Win32::OLE automation has always been a dedicated single instance of Excel running without intervention (or possibility of intervention, in the case of a Server). If I were to do simple macro type automation, then I'd record it within Excel or use the existing VBA and save myself the pain of OLE.
As for this specific example, if you had an open file already loaded in the Excel instance, what would happen is that you'd get a new sheet added, there'd be data in that, and the result would be saved in test.xls. So if you had a complex workbook already open, those changes could be brought back. It's a fair point, though, you wouldn't want to have any open instances before running such a script.
| [reply] |
|
|
# get a new workbook
$book = $ex->Workbooks->Add;
This creates a new book, i.e. a new file, in the Excel instance. Then, when the code saves test.xls, it's only that book that gets saved. No sheets are added. $sheet is created by the line
$sheet = $book->Worksheets(1);
Again, this is in $book, not in anything that might have been open in the same instance. This can't fail, as the creation of $book will have the default number of sheets from SheetsInNewWorkbook, which can never be less than 1.
Now, I accept that the code given,
# save and exit
$book->SaveAs( 'test.xls' );
undef $book;
undef $ex;
doesn't actually close the instance of Excel, but merely undefines it in Perl. But this thread is, to a large extent, about closing instances of Excel, so my fear is undiminished.
If you don't have any instances of Excel open, there's no point even trying to take control of an open instance. And if you do, we return to the dangers on which we agree. If the code creates its own instance without trying to take control of an existing instance, you are safe either way.
Regards,
John Davies | [reply]
[d/l]
[select] |
|
|
If I were to do simple macro type automation, then I'd record it within Excel or use the existing VBA and save myself the pain of OLE.
I don't want to sound overcritical, but wanted to point out two potential problems with having Perl calling macros in Excel.
First, Excel 2010 defaults to not enable macros. That means you'll probably have to add code to enable the execution of macros. Since I don't have Perl calling macros, I personally have not encountered this issue myself. That default behavior might be changeable, but I personally think that it's not a good idea to make assumptions about what settings are for a program like Excel when developing code and manipulates that program.
The second problem is related to something actually happened at work. My first dive into learning Perl was to generate Excel charts. A coworker previously had a script that did this and his called macros. My coworkers were emphatically telling me to not use macros when they found out what I was working on. What happened was my coworker's template Excel file got corrupted with a virus. So when folks used his script, the macros was deleting files, which wreaked havoc on projects. Therefore, I made sure that my code was not using macros and was not relying on a template Excel file.
Again, it's a matter of personal preference. However, I would strongly urge folks to not call macros from Perl, but rather convert the macros into Perl code for your Perl program to use. I realize that doing so means more work, but I personally feel the benefits of doing so are well worth it.
| [reply] |
|
|
|
|
|
|
|
|
