How to operate on multiple Excel worksheets with variable array

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

Hello.
I'm still learing Perl, and am trying to perform the following task -- and am miserably stuck.
Currently I have the following.
1. Open the necessary Excel file 2. Store Workbooks info under var $workbook1 3. Store Wortsheets info under var $worksheets1

my $excel = Win32::OLE -> new("Excel.Application");
$excel->{Visible} = 0;
$excel->{DisplayAlerts}=0;
$excel->{SheetsInNewWorkbook} = 1;

my $workbook1    = $excel->Workbooks->Open($OutFileName);

my $worksheet1  = $workbook1->Worksheets(1);
[download]

I later take this info and have Perl to write in the cells of the Excel file (the necessary $Row and $Col are initialized accordingly).

    $worksheet1->Cells($Row, $Col)->{'Value'} = $var1;
    $worksheet1->Cells($Row, $Col+1)->{'Value'} = $var2;
    $worksheet1->Cells($Row, $Col+2)->{'Value'} = $var3;
    $worksheet1->Cells($Row, $Col+3)->{'Value'} = $var4;
[download]

So far this works fine.
What I intend to do is operate on multiple worksheets instead of just one.

1. Open the necessary Excel file
2. Store Workbooks info under var $workbook1
3. Store Wortsheets info under var(s) $worksheets1, $worksheets2, $worksheets3, $worksheets4, $worksheets5 (or store it into an array if it's possible?

I have a total of 5 worksheets I need to operate on in the Excel file.
I'm trying to for loop the latter process (where I write in the cells) for each worksheet, but I'm trying to find an easier way to recall the $worksheet1, $worksheet2, ..., $worksheet5 variables. Cuz, for now without the proper array variable -- which I yet learned how to in Perl -- I need to do an if statement for each every for loop and write the according code, which is very ineffective.. Is there a way to perform this with an array or something else for the worksheet variable?
I'm more familiar with how Matlab works.. and here's what I would like Perl to do if it were a Matlab script with arrays.

    for i = 1:num_worksheets
    # write in worksheet[i] cell (j,k) with variable var1
    end
[download]

Any help is appreciated!

Comment on How to operate on multiple Excel worksheets with variable array Select or Download Code

Replies are listed 'Best First'.
Re: How to operate on multiple Excel worksheets with variable array by davies (Prior) on Aug 08, 2016 at 11:39 UTC
Excel maintains all these arrays for you. You don't have to do the work yourself, whether in Perl or anywhere else. `use strict; use warnings; use Win32::OLE; my $excel = Win32::OLE -> new("Excel.Application"); $excel->{Visible} = 1; $excel->{DisplayAlerts}=0; my $oldsheets = $excel->{SheetsInNewWorkbook}; $excel->{SheetsInNewWorkbook} = 5; my $wb = $excel->Workbooks->Add; $excel->{SheetsInNewWorkbook} = $oldsheets; for my $i (1..$wb->Sheets->{Count}) { $wb->Sheets($i)->Cells(1, 1)->{Value} = "Sheet $i"; }` [download] I think the last 3 lines are the equivalent of your matlab code. Note, however, the work I have done on sheet numbers. I prefer not to use SheetsInNewWorkbook as it gets saved whenever Excel closes normally. You might have some users who have bothered to set up Excel sensibly. They won't take kindly to you changing their default number of sheets. And if your Perl crashes with an Excel instance open, closing that instance will save the current setting, which is why I prefer not to risk it. But that's just my personal paranoia. The saving & restoring I do in my code should be fine. Regards, John Davies	[reply] [d/l]
Re^2: How to operate on multiple Excel worksheets with variable array by bill5262 (Acolyte) on Aug 09, 2016 at 07:36 UTC
Thank you so much.	[reply]
Re: How to operate on multiple Excel worksheets with variable array by Gangabass (Vicar) on Aug 08, 2016 at 05:10 UTC
`foreach my $worksheet_num ( 1 .. 5 ) { my $worksheet = $workbook1->Worksheets($worksheet_num); $worksheet->Cells($Row, $Col)->{'Value'} = $var1; $worksheet->Cells($Row, $Col+1)->{'Value'} = $var2; $worksheet->Cells($Row, $Col+2)->{'Value'} = $var3; $worksheet->Cells($Row, $Col+3)->{'Value'} = $var4; }` [download]	[reply] [d/l]
Re: How to operate on multiple Excel worksheets with variable array by Laurent_R (Canon) on Aug 08, 2016 at 06:19 UTC
I think you should use an array of workbooks. You'll end up with an array of arrays of hashrefs, or something similar, but I suppose it should not be a big problem since you're already using hashrefs in your code. Whenever you start naming variables $var1, $var2, ..., $var5, this is signal that there is probably something wrong with your code and that you should probably have a @var array instead, with the values stored in $var[0], $var1, etc.	[reply]
Re: How to operate on multiple Excel worksheets with variable array by duyet (Friar) on Aug 08, 2016 at 09:38 UTC
You might want to try other package like Spreadsheet::ParseExcel http://search.cpan.org/~dougw/Spreadsheet-ParseExcel-0.65/lib/Spreadsheet/ParseExcel.pm or other excel packages available at http://search.cpan.org	[reply]
Re: How to operate on multiple Excel worksheets with variable array by jandrew (Chaplain) on Aug 09, 2016 at 02:31 UTC
I would like to second duyet's++ suggestion to not read Excel files using Win32::OLE. While the Win32::OLE module is well written and powerful you are tied to using the Microsoft Excel application as an intermediary to read the file. As a minimum the sheet order and sheet number are not always consistent in the background of Excel files (especially in Excel 2007 and newer) so when you use sheet number to call a sheet it doesn't always return the one you expected. Additionally if you are multitasking and accidentally click on the Excel application window while the Win32::OLE is reading it you can cause other unintended consequences. The other suggested solutions are generally robust, well documented, do not have these same pitfalls, and do not require a Microsoft Excel licence either!	[reply]
Re^2: How to operate on multiple Excel worksheets with variable array by davies (Prior) on Aug 09, 2016 at 12:12 UTC
"Additionally if you are multitasking and accidentally click on the Excel application window while the Win32::OLE is reading it you can cause other unintended consequences." True, but the OP code included `$excel->{Visible} = 0;`, which obviates this danger. Regards, John Davies	[reply] [d/l]
Re^3: How to operate on multiple Excel worksheets with variable array by jandrew (Chaplain) on Aug 09, 2016 at 12:34 UTC
It's been awhile since I went down this road but doesn't is still show up in the task bar? Sure it's more than one key stroke or if you are an 'alt-tab focus' person then that, but my recollection (and personal frustrating experience) is that the application is still running in a keystroke/mouse accessible way to the user. For any long running parsing your risk of clicking on Excel goes up especially if you use Excel for other purposes. This wasn't really part of my initial answer but the other risk of using Win32::OLE is that you have available both read and write capabilities for the file. In general this won't be a problem but you risk the case where you overwrite data before you have read it.	[reply]
Re^4: How to operate on multiple Excel worksheets with variable array by davies (Prior) on Aug 09, 2016 at 12:47 UTC


Think about Loose Coupling
	PerlMonks