#! 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'} = "$data" if ($cell_info->{$count} eq 'Integrity'); $Sheet_tmpl->Cells($trow,3)->{'Value'} = "$data" if ($cell_info->{$count} eq 'Availability'); $Sheet_tmpl->Cells($trow,4)->{'Value'} = "$data" if ($cell_info->{$count} eq 'RTO'); $Sheet_tmpl->Cells($trow,5)->{'Value'} = "$data" if ($cell_info->{$count} eq 'RPO'); $Sheet_tmpl->Cells($trow,6)->{'Value'} = "$data" if ($cell_info->{$count} eq 'SLA'); $Sheet_tmpl->Cells($trow,7)->{'Value'} = "$data" if ($cell_info->{$count} eq 'Host Name'); $Sheet_tmpl->Cells($trow,8)->{'Value'} = "$data" if ($cell_info->{$count} eq 'Location'); $Sheet_tmpl->Cells($trow,9)->{'Value'} = "$data" if ($cell_info->{$count} eq 'SAN'); $Sheet_tmpl->Cells($trow,10)->{'Value'} = "$data" if ($cell_info->{$count} eq 'NAS'); $Sheet_tmpl->Cells($trow,11)->{'Value'} = "$data" if ($cell_info->{$count} eq 'DAS'); $Sheet_tmpl->Cells($trow,12)->{'Value'} = "$data" if ($cell_info->{$count} eq 'Current Tier'); $Sheet_tmpl->Cells($trow,13)->{'Value'} = "$data" if ($cell_info->{$count} eq 'Allocated GB'); $Sheet_tmpl->Cells($trow,14)->{'Value'} = "$data" if ($cell_info->{$count} eq 'Actual GB'); $Sheet_tmpl->Cells($trow,15)->{'Value'} = "$data" if ($cell_info->{$count} eq 'Storage Raw (GB)'); $Sheet_tmpl->Cells($trow,16)->{'Value'} = "$data" if ($cell_info->{$count} eq 'Storage Growth (% per year)'); $Sheet_tmpl->Cells($trow,17)->{'Value'} = "$data" if ($cell_info->{$count} eq 'Application Type'); $Sheet_tmpl->Cells($trow,18)->{'Value'} = "$data" if ($cell_info->{$count} eq 'Business Unit'); $Sheet_tmpl->Cells($trow,19)->{'Value'} = "$data" if ($cell_info->{$count} eq 'Business Unit Manager'); $Sheet_tmpl->Cells($trow,20)->{'Value'} = "$data" if ($cell_info->{$count} eq 'Support Person'); $Sheet_tmpl->Cells($trow,21)->{'Value'} = "$data" if ($cell_info->{$count} eq 'Relationship Manager'); $Sheet_tmpl->Cells($trow,22)->{'Value'} = "$data" if ($cell_info->{$count} eq 'Country'); $Sheet_tmpl->Cells($trow,23)->{'Value'} = "$data" if ($cell_info->{$count} eq 'OLA'); $Sheet_tmpl->Cells($trow,24)->{'Value'} = "$data" if ($cell_info->{$count} eq 'Confidentiality'); $Sheet_tmpl->Cells($trow,25)->{'Value'} = "$data" if ($cell_info->{$count} eq 'Server Role'); $Sheet_tmpl->Cells($trow,26)->{'Value'} = "$data" if ($cell_info->{$count} eq 'Platform'); $Sheet_tmpl->Cells($trow,27)->{'Value'} = "$data" if ($cell_info->{$count} eq 'HBA Card'); $Sheet_tmpl->Cells($trow,28)->{'Value'} = "$data" if ($cell_info->{$count} eq 'HBA Card Type'); $Sheet_tmpl->Cells($trow,29)->{'Value'} = "$data" if ($cell_info->{$count} eq 'HBA Card Total'); $Sheet_tmpl->Cells($trow,30)->{'Value'} = "$data" if ($cell_info->{$count} eq 'LUNs Available'); $Sheet_tmpl->Cells($trow,31)->{'Value'} = "$data" if ($cell_info->{$count} eq 'Number of Server Processors'); $Sheet_tmpl->Cells($trow,32)->{'Value'} = "$data" if ($cell_info->{$count} eq 'Server Memory'); $Sheet_tmpl->Cells($trow,33)->{'Value'} = "$data" if ($cell_info->{$count} eq 'Server Type'); $Sheet_tmpl->Cells($trow,34)->{'Value'} = "$data" if ($cell_info->{$count} eq 'Frame'); $Sheet_tmpl->Cells($trow,35)->{'Value'} = "$data" if ($cell_info->{$count} eq 'I/O Throughput'); $Sheet_tmpl->Cells($trow,36)->{'Value'} = "$data" if ($cell_info->{$count} eq 'Data Protection Method'); $Sheet_tmpl->Cells($trow,37)->{'Value'} = "$data" if ($cell_info->{$count} eq 'Number of Replicated Copies'); $Sheet_tmpl->Cells($trow,38)->{'Value'} = "$data" if ($cell_info->{$count} eq 'Location of Replicated Copies'); $Sheet_tmpl->Cells($trow,39)->{'Value'} = "$data" if ($cell_info->{$count} eq 'Backup'); $Sheet_tmpl->Cells($trow,40)->{'Value'} = "$data" if ($cell_info->{$count} eq 'Backup Frequency'); $Sheet_tmpl->Cells($trow,41)->{'Value'} = "$data" if ($cell_info->{$count} eq 'Backup Type'); $Sheet_tmpl->Cells($trow,42)->{'Value'} = "$data" if ($cell_info->{$count} eq 'Service Window'); $Sheet_tmpl->Cells($trow,43)->{'Value'} = "$data" if ($cell_info->{$count} eq 'Operation Type'); $Sheet_tmpl->Cells($trow,44)->{'Value'} = "$data" if ($cell_info->{$count} eq 'Server Commission Date'); $Sheet_tmpl->Cells($trow,45)->{'Value'} = "$data" if ($cell_info->{$count} eq 'Server Decommission Date'); $Sheet_tmpl->Cells($trow,46)->{'Value'} = "$data" if ($cell_info->{$count} eq 'Storage Commission Date'); $Sheet_tmpl->Cells($trow,47)->{'Value'} = "$data" if ($cell_info->{$count} eq 'Storage Decommission Date'); $Sheet_tmpl->Cells($trow,48)->{'Value'} = "$data" if ($cell_info->{$count} eq 'Application Expiration Date'); $Sheet_tmpl->Cells($trow,49)->{'Value'} = "$data" if ($cell_info->{$count} eq 'Recommended Tier'); $Sheet_tmpl->Cells($trow,50)->{'Value'} = "$data" 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 Insertion Manager");