Getting all the data from Excel file

blackadder has asked for the wisdom of the Perl Monks concerning the following question:
GODS PLEASE HELP

I have this code;
#
# Script by me - June 2005
#
use strict;
use Win32;
use Win32::OLE;
use File::Find;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn =2;           
use vars qw/%data %Exl/;
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')|| Win32::
+OLE->new('Excel.Application');  
my $Book_tmpl = $Excel->Workbooks->Add;
my $Sheet_tmpl = $Book_tmpl->Worksheets(1);
$Excel->{Visible}=1;
$Sheet_tmpl->Range("A:Z")->Font->{FontStyle}="Bold";

$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";
my $counter = 0;
my $new_book = $Excel->Workbooks->Open("c:\\work\\USA.xls");
my $new_sheet = $new_book->Worksheets("Sheet1");

my $counter = 0;
my @data_array;
my $LastRow = $new_sheet->UsedRange->Find({What=>"*",
    SearchDirection=>xlPrevious,
    SearchOrder=>xlByRows})->{Row};

my $LastCol = $new_sheet->UsedRange->Find({What=>"*", 
                  SearchDirection=>xlPrevious,
                  SearchOrder=>xlByColumns})->{Column};
                      
my $array = $new_sheet->Range("A1:AQ1")->{'Value'};
                       
foreach my $ref_array (@$array) 
{
    my $col =0;    
    my $row =2;    
    my $tcol = 1;
    my $trow = 2;
    foreach my $col_heads (@$ref_array) 
    {        
         if ($col_heads eq "")
         {
             $counter++;
             next;             
        }
        print ++$counter. ") $col_heads : ".$new_sheet->Cells($row,$co
+unter)->{'Value'}."\n";
        if ($col_heads eq "Application" )
        {
            $Sheet_tmpl->Cells($trow,1)->{'Value'} = $new_sheet->Cells
+($row,$counter)->{'Value'};            
        }
        if ($col_heads eq "Integrity" )
        {
            $Sheet_tmpl->Cells($trow,2)->{'Value'} = $new_sheet->Cells
+($row,$counter)->{'Value'};            
        }
        if ($col_heads eq "Availability" )
        {
            $Sheet_tmpl->Cells($trow,3)->{'Value'} = $new_sheet->Cells
+($row,$counter)->{'Value'};            
        }
        if ($col_heads eq "RTO" )
        {            
            $Sheet_tmpl->Cells($trow,4)->{'Value'} = $new_sheet->Cells
+($row,$counter)->{'Value'};                    
        }
        if ($col_heads eq "RPO" )
        {            
            $Sheet_tmpl->Cells($trow,5)->{'Value'} = $new_sheet->Cells
+($row,$counter)->{'Value'};                    
        }
        if ($col_heads eq "SLA" )
        {            
            $Sheet_tmpl->Cells($trow,6)->{'Value'} = $new_sheet->Cells
+($row,$counter)->{'Value'};                    
        }
        if ($col_heads eq "Host Name" )
        {            
            $Sheet_tmpl->Cells($trow,7)->{'Value'} = $new_sheet->Cells
+($row,$counter)->{'Value'};                    
        }
        if ($col_heads eq "Location" )
        {            
            $Sheet_tmpl->Cells($trow,8)->{'Value'} = $new_sheet->Cells
+($row,$counter)->{'Value'};                    
        }
        if ($col_heads eq "SAN" )
        {            
            $Sheet_tmpl->Cells($trow,9)->{'Value'} = $new_sheet->Cells
+($row,$counter)->{'Value'};                    
        }
        if ($col_heads eq "NAS" )
        {            
            $Sheet_tmpl->Cells($trow,10)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
        if ($col_heads eq "DAS" )
        {            
            $Sheet_tmpl->Cells($trow,11)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
        if ($col_heads eq "Current Tier" )
        {            
            $Sheet_tmpl->Cells($trow,12)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
        if ($col_heads eq "Allocated GB" )
        {            
            $Sheet_tmpl->Cells($trow,13)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
        if ($col_heads eq "Actual GB" )
        {            
            $Sheet_tmpl->Cells($trow,14)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
        if ($col_heads eq "Storage Raw (GB)" )
        {            
            $Sheet_tmpl->Cells($trow,15)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
        if ($col_heads eq "Storage Growth (% per year)" )
        {            
            $Sheet_tmpl->Cells($trow,16)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
        if ($col_heads eq "Application Type" )
        {            
            $Sheet_tmpl->Cells($trow,17)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                        
        }
        if ($col_heads eq "Business Unit" )
        {            
            $Sheet_tmpl->Cells($trow,18)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
        if ($col_heads eq "Business Unit Manager" )
        {            
            $Sheet_tmpl->Cells($trow,19)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
        if ($col_heads eq "Support Person" )
        {            
            $Sheet_tmpl->Cells($trow,20)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
        if ($col_heads eq "Relationship Manager" )
        {            
            $Sheet_tmpl->Cells($trow,21)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
        if ($col_heads eq "Country" )
        {            
            $Sheet_tmpl->Cells($trow,22)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
        if ($col_heads eq "OLA" )
        {            
            $Sheet_tmpl->Cells($trow,23)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
        if ($col_heads eq "Confidentiality" )
        {            
            $Sheet_tmpl->Cells($trow,24)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
        if ($col_heads eq "Server Role" )
        {            
            $Sheet_tmpl->Cells($trow,25)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
        if ($col_heads eq "Platform" )
        {            
            $Sheet_tmpl->Cells($trow,26)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
        if ($col_heads eq "HBA Card" )
        {            
            $Sheet_tmpl->Cells($trow,27)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
        if ($col_heads eq "HBA Card Type" )
        {            
            $Sheet_tmpl->Cells($trow,28)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
        if ($col_heads eq "HBA Card Total" )
        {            
            $Sheet_tmpl->Cells($trow,29)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
        if ($col_heads eq "LUNs Available" )
        {            
            $Sheet_tmpl->Cells($trow,30)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
        if ($col_heads eq "Number of Server Processors" )
        {            
            $Sheet_tmpl->Cells($trow,31)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
        if ($col_heads eq "Server Memory" )
        {            
            $Sheet_tmpl->Cells($trow,32)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
            if ($col_heads eq "Server Type" )
        {            
            $Sheet_tmpl->Cells($trow,33)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
            if ($col_heads eq "Frame" )
        {            
            $Sheet_tmpl->Cells($trow,34)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
            if ($col_heads eq "I/O Throughput" )
        {            
            $Sheet_tmpl->Cells($trow,35)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
            if ($col_heads eq "Data Protection Method" )
        {            
            $Sheet_tmpl->Cells($trow,36)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
            if ($col_heads eq "Number of Replicated Copies" )
        {            
            $Sheet_tmpl->Cells($trow,37)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
            if ($col_heads eq "Location of Replicated Copies" )
        {            
            $Sheet_tmpl->Cells($trow,38)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
            if ($col_heads eq "Backup" )
        {            
            $Sheet_tmpl->Cells($trow,39)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
            if ($col_heads eq "Backup Frequency" )
        {            
            $Sheet_tmpl->Cells($trow,40)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
            if ($col_heads eq "Backup Type" )
        {            
            $Sheet_tmpl->Cells($trow,41)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
            if ($col_heads eq "Service Window" )
        {            
            $Sheet_tmpl->Cells($trow,42)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
            if ($col_heads eq "Operation Type" )
        {            
            $Sheet_tmpl->Cells($trow,43)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
            if ($col_heads eq "Server Commission Date" )
        {            
            $Sheet_tmpl->Cells($trow,44)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
            if ($col_heads eq "Server Decommission Date" )
        {            
            $Sheet_tmpl->Cells($trow,45)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
            if ($col_heads eq "Storage Commission Date" )
        {            
            $Sheet_tmpl->Cells($trow,46)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
            if ($col_heads eq "Storage Decommission Date" )
        {            
            $Sheet_tmpl->Cells($trow,47)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
            if ($col_heads eq "Application Expiration Date" )
        {            
            $Sheet_tmpl->Cells($trow,48)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
            if ($col_heads eq "Recommended Tier" )
        {            
            $Sheet_tmpl->Cells($trow,49)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
            if ($col_heads eq "Date_Source" )
        {            
            $Sheet_tmpl->Cells($trow,50)->{'Value'} = $new_sheet->Cell
+s($row,$counter)->{'Value'};                    
        }
        $row++;    
    $trow++;    
    }    
    
}
[download]
And I need to get all the data from the USA.Xls sheel, however because of the range I have selected I only get one line of data. the Lastcol, and Last row valus are numeric so I can't substitute it in Xsheel->Range("xx:xx").

Can someone please please help me out here.
Blackadder
Comment on Getting all the data from Excel file Download Code
Replies are listed 'Best First'.
Re: Getting all the data from Excel file by Enlil (Parson) on Jun 07, 2005 at 15:41 UTC
And I need to get all the data from the USA.Xls sheel Have you looked at: Spreadsheet::ParseExcel or Spreadsheet::ParseExcel::Simple -Enlil	[reply]
Re: Getting all the data from Excel file by davidrw (Prior) on Jun 07, 2005 at 16:18 UTC
Since you're using Win32::OLE, is just using excel's built-in exporting functions to dump to a csv file an option for you? Then a perl script can just read that in and parse it (or even just use Text::CSV from there).	[reply]
Re: Getting all the data from Excel file by tphyahoo (Vicar) on Jun 08, 2005 at 08:14 UTC
I get stomach cramps from Win32::OLE. Not because it's bad, just because I don't understand it ;) When I was in your situation I wound up just saving out the .xls as csv and using tilly's Text::xSV and then processing the text. OF course, this only works if you have just values, no formulas or other excel-specific stuff. Good luck.	[reply]