Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

Re^2: PROPERTYPUT error in Win32::OLE

by Anonymous Monk
on Jun 13, 2005 at 11:56 UTC ( #466124=note: print w/ replies, xml ) Need Help??


in reply to Re: PROPERTYPUT error in Win32::OLE
in thread PROPERTYPUT error in Win32::OLE

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");


Comment on Re^2: PROPERTYPUT error in Win32::OLE
Download Code

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://466124]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (7)
As of 2015-07-04 16:11 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (60 votes), past polls