Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
PerlMonks  

comment on

( [id://3333]=superdoc: print w/replies, xml ) Need Help??
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");

In reply to Re^2: PROPERTYPUT error in Win32::OLE by Anonymous Monk
in thread PROPERTYPUT error in Win32::OLE by Anonymous Monk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • 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.
Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others chanting in the Monastery: (5)
As of 2024-03-28 09:02 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found