I see, I will print out the values of $trow, which is just a pointer to the excel row.
Here is the full code
#! c:/perl/bin/perl.exe
# good working version
use strict;
use Win32;
use Win32::OLE;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
use time::localtime;
use Tk;
use Tk::ProgressBar;
my $percent_done=0;
my $mw = MainWindow->new(-title => 'Processing Excel Extraction');
my $Progress = $mw->ProgressBar(
-width => 30,
-from => 0,
-to => 100,
-blocks => 50,
-colors => [0,'blue',100,'blue'],
-variable => \$percent_done
)->pack(-fill => 'x');
$Win32::OLE::Warn =2;
my $excel = Win32::OLE->GetActiveObject('Excel.Application')
|| Win32::OLE->new('Excel.Application');
$excel->{Visible} = 1;
my $Book_tmpl = $excel->Workbooks->Add;
my $Sheet_tmpl = $Book_tmpl->Worksheets(1);
$Sheet_tmpl->{Name}="Template Results";
$Sheet_tmpl->Cells(1,1)->{Value}="Application";
$Sheet_tmpl->Cells(1,2)->{Value}="Integrity";
$Sheet_tmpl->Cells(1,3)->{Value}="Availability";
$Sheet_tmpl->Cells(1,4)->{Value}="RTO";
$Sheet_tmpl->Cells(1,5)->{Value}="RPO";
$Sheet_tmpl->Cells(1,6)->{Value}="SLA";
$Sheet_tmpl->Cells(1,7)->{Value}="Host Name";
$Sheet_tmpl->Cells(1,8)->{Value}="Location";
$Sheet_tmpl->Cells(1,9)->{Value}="SAN";
$Sheet_tmpl->Cells(1,10)->{Value}="NAS";
$Sheet_tmpl->Cells(1,11)->{Value}="DAS";
$Sheet_tmpl->Cells(1,12)->{Value}="Current Tier";
$Sheet_tmpl->Cells(1,13)->{Value}="Allocated GB";
$Sheet_tmpl->Cells(1,14)->{Value}="Actual GB";
$Sheet_tmpl->Cells(1,15)->{Value}="Storage Raw (GB)";
$Sheet_tmpl->Cells(1,16)->{Value}="Storage Growth (% per year)";
$Sheet_tmpl->Cells(1,17)->{Value}="Application Type";
$Sheet_tmpl->Cells(1,18)->{Value}="Business Unit";
$Sheet_tmpl->Cells(1,19)->{Value}="Business Unit Manager";
$Sheet_tmpl->Cells(1,20)->{Value}="Support Person";
$Sheet_tmpl->Cells(1,21)->{Value}="Relationship Manager";
$Sheet_tmpl->Cells(1,22)->{Value}="Country";
$Sheet_tmpl->Cells(1,23)->{Value}="OLA";
$Sheet_tmpl->Cells(1,24)->{Value}="Confidentiality";
$Sheet_tmpl->Cells(1,25)->{Value}="Server Role";
$Sheet_tmpl->Cells(1,26)->{Value}="Platform";
$Sheet_tmpl->Cells(1,27)->{Value}="HBA Card";
$Sheet_tmpl->Cells(1,28)->{Value}="HBA Card Type";
$Sheet_tmpl->Cells(1,29)->{Value}="HBA Card Total";
$Sheet_tmpl->Cells(1,30)->{Value}="LUNs Available";
$Sheet_tmpl->Cells(1,31)->{Value}="Number of Server Processors";
$Sheet_tmpl->Cells(1,32)->{Value}="Server Memory";
$Sheet_tmpl->Cells(1,33)->{Value}="Server Type";
$Sheet_tmpl->Cells(1,34)->{Value}="Frame";
$Sheet_tmpl->Cells(1,35)->{Value}="I/O Throughput (p/sec)";
$Sheet_tmpl->Cells(1,36)->{Value}="Data Protection Method";
$Sheet_tmpl->Cells(1,37)->{Value}="Number of Replicated Copies";
$Sheet_tmpl->Cells(1,38)->{Value}="Location of Replicated Copies";
$Sheet_tmpl->Cells(1,39)->{Value}="Backup";
$Sheet_tmpl->Cells(1,40)->{Value}="Backup Frequency";
$Sheet_tmpl->Cells(1,41)->{Value}="Backup Type";
$Sheet_tmpl->Cells(1,42)->{Value}="Service Windows";
$Sheet_tmpl->Cells(1,43)->{Value}="Operation Type";
$Sheet_tmpl->Cells(1,44)->{Value}="Server Commission Date";
$Sheet_tmpl->Cells(1,45)->{Value}="Server Decommission Date";
$Sheet_tmpl->Cells(1,46)->{Value}="Storage Commission Date";
$Sheet_tmpl->Cells(1,47)->{Value}="Storage Decommission Date";
$Sheet_tmpl->Cells(1,48)->{Value}="Application Expritaion Date";
$Sheet_tmpl->Cells(1,49)->{Value}="Recommended Tier";
$Sheet_tmpl->Cells(1,50)->{Value}="Data_Source";
$Sheet_tmpl->Range("A1:AY1")->Font->{FontStyle}="Bold";
$Sheet_tmpl->Range("A1:AY1")->Interior->{ColorIndex}="6";
$Sheet_tmpl->Columns("A:AY")->AutoFit();
my $work_dir = 'c:/Work';
opendir (DIR,$work_dir) || die "\n\nError=>\t$!\n\n";
my @work_files = readdir (DIR);
my $trow =2;
my $counter=0;
foreach my $file (@work_files)
{
$percent_done = $counter * 100 / $#work_files;
$counter++;
$mw->update;
next if ($file eq '.' || $file eq '..');
my $file_path = $work_dir.'/'.$file;
print "\n$file_path\n";
my $xls = $excel->Workbooks->Open("$file_path");
foreach my $sheet ( in $xls->Worksheets )
{
my $count =0;
my @head_array;
my $current_sheet = $sheet->Name, $/;
print "$current_sheet\n";
my $new_sheet = $xls->Worksheets("$current_sheet");
my $everything = $new_sheet->UsedRange->{'Value'};
my $array_head = shift @$everything;
foreach my $col_heads (@$array_head)
{
my $rec;
if ($col_heads eq "")
{
$count++;
next;
}
++$count;
#print ++$count. ") $col_heads : \n";
$rec->{$count} = $col_heads;
push (@head_array,$rec);
}
foreach my $ref_array (@$everything)
{
#print "$ref_array\n";
$count = 0;
foreach my $data (@$ref_array)
{
if ($data eq "")
{
++$count;
next;
}
++$count;
#print ++$count.") $data\t";
for my $cell_info (@head_array)
{
for my $ref_a (keys %$cell_info)
{
#print "$cell_info->{$count}\n";
eval {$Sheet_tmpl->Cells($trow,1)->{'Value'} =
+ "$data" if ($cell_info->{$count} eq 'Application');
$Sheet_tmpl->Cells($trow,2)->{'Value'} = "$dat
+a" if ($cell_info->{$count} eq 'Integrity');
$Sheet_tmpl->Cells($trow,3)->{'Value'} = "$dat
+a" if ($cell_info->{$count} eq 'Availability');
$Sheet_tmpl->Cells($trow,4)->{'Value'} = "$dat
+a" if ($cell_info->{$count} eq 'RTO');
$Sheet_tmpl->Cells($trow,5)->{'Value'} = "$dat
+a" if ($cell_info->{$count} eq 'RPO');
$Sheet_tmpl->Cells($trow,6)->{'Value'} = "$dat
+a" if ($cell_info->{$count} eq 'SLA');
$Sheet_tmpl->Cells($trow,7)->{'Value'} = "$dat
+a" if ($cell_info->{$count} eq 'Host Name');
$Sheet_tmpl->Cells($trow,8)->{'Value'} = "$dat
+a" if ($cell_info->{$count} eq 'Location');
$Sheet_tmpl->Cells($trow,9)->{'Value'} = "$dat
+a" if ($cell_info->{$count} eq 'SAN');
$Sheet_tmpl->Cells($trow,10)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'NAS');
$Sheet_tmpl->Cells($trow,11)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'DAS');
$Sheet_tmpl->Cells($trow,12)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'Current Tier');
$Sheet_tmpl->Cells($trow,13)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'Allocated GB');
$Sheet_tmpl->Cells($trow,14)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'Actual GB');
$Sheet_tmpl->Cells($trow,15)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'Storage Raw (GB)');
$Sheet_tmpl->Cells($trow,16)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'Storage Growth (% per year)');
$Sheet_tmpl->Cells($trow,17)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'Application Type');
$Sheet_tmpl->Cells($trow,18)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'Business Unit');
$Sheet_tmpl->Cells($trow,19)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'Business Unit Manager');
$Sheet_tmpl->Cells($trow,20)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'Support Person');
$Sheet_tmpl->Cells($trow,21)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'Relationship Manager');
$Sheet_tmpl->Cells($trow,22)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'Country');
$Sheet_tmpl->Cells($trow,23)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'OLA');
$Sheet_tmpl->Cells($trow,24)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'Confidentiality');
$Sheet_tmpl->Cells($trow,25)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'Server Role');
$Sheet_tmpl->Cells($trow,26)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'Platform');
$Sheet_tmpl->Cells($trow,27)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'HBA Card');
$Sheet_tmpl->Cells($trow,28)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'HBA Card Type');
$Sheet_tmpl->Cells($trow,29)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'HBA Card Total');
$Sheet_tmpl->Cells($trow,30)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'LUNs Available');
$Sheet_tmpl->Cells($trow,31)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'Number of Server Processors');
$Sheet_tmpl->Cells($trow,32)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'Server Memory');
$Sheet_tmpl->Cells($trow,33)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'Server Type');
$Sheet_tmpl->Cells($trow,34)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'Frame');
$Sheet_tmpl->Cells($trow,35)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'I/O Throughput');
$Sheet_tmpl->Cells($trow,36)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'Data Protection Method');
$Sheet_tmpl->Cells($trow,37)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'Number of Replicated Copies');
$Sheet_tmpl->Cells($trow,38)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'Location of Replicated Copies');
$Sheet_tmpl->Cells($trow,39)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'Backup');
$Sheet_tmpl->Cells($trow,40)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'Backup Frequency');
$Sheet_tmpl->Cells($trow,41)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'Backup Type');
$Sheet_tmpl->Cells($trow,42)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'Service Window');
$Sheet_tmpl->Cells($trow,43)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'Operation Type');
$Sheet_tmpl->Cells($trow,44)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'Server Commission Date');
$Sheet_tmpl->Cells($trow,45)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'Server Decommission Date');
$Sheet_tmpl->Cells($trow,46)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'Storage Commission Date');
$Sheet_tmpl->Cells($trow,47)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'Storage Decommission Date');
$Sheet_tmpl->Cells($trow,48)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'Application Expiration Date');
$Sheet_tmpl->Cells($trow,49)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'Recommended Tier');
$Sheet_tmpl->Cells($trow,50)->{'Value'} = "$da
+ta" if ($cell_info->{$count} eq 'Data_Source');};
}
}
}
$trow++;
}
#print "\nLast Row: $LastRow, Last Column: $LastCol\n";
}
print "\n____________________________________________\n";
$xls->Close;
}
Win32::MsgBox("The Operation Completed Successfully\n",0,"Table Insert
+ion Manager");
-
Are you posting in the right place? Check out Where do I post X? to know for sure.
-
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big>
<blockquote> <br /> <dd>
<dl> <dt> <em> <font>
<h1> <h2> <h3> <h4>
<h5> <h6> <hr /> <i>
<li> <nbsp> <ol> <p>
<small> <strike> <strong>
<sub> <sup> <table>
<td> <th> <tr> <tt>
<u> <ul>
-
Snippets of code should be wrapped in
<code> tags not
<pre> tags. In fact, <pre>
tags should generally be avoided. If they must
be used, extreme care should be
taken to ensure that their contents do not
have long lines (<70 chars), in order to prevent
horizontal scrolling (and possible janitor
intervention).
-
Want more info? How to link
or How to display code and escape characters
are good places to start.