I've written some code to extract the data from an Excel file, which maintains order and headers. My co-worker said that the way I'm doing it only works if Excel is loaded on the test system. So, I'm looking for a way to do the same thing I've done, without using EXCEL (hence Win32::OLE). I've put my code below, so you can see what I've done.
Thanks
Code:
#!/usr/bin/perl
use OLE;
use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3; # die on errors...
# get already active Excel application or open new
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
|| Win32::OLE->new('Excel.Application', 'Quit');
# open Excel file
print("Opening Excel file\n");
my $Book = $Excel->Workbooks->Open("c:\\Master_DataFile.xls");
# select worksheet
print("Getting worksheet\n");
my $Method = "Group";
my $PassedIn1 = "Raid5";
my $PassedIn2 = "5";
my $Sheet = $Book->Worksheets(1);
my @DataHeaders; # array of data headers # should look like ("Row_Num", "TC_Name", "NavPath" ... )
my $Headers;
my @Headers1;
my @DataTable; # hash table for data
my @TempDataTable; # hash table for data
my @Col = ( "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S","T",
"U","V","W","X","Y","Z","AA","AB","AC","AD","AE","AF","AG","AH","AI","AJ");
my $NumHeaders;
print("Reading headers ...\n");
$Headers = $Sheet->Range("A2:T2")->{'Value'};
my $idx=0;
# Put the headers into a local array and print them out
for (@$Headers) {
for (@$_) {
@DataHeaders[$idx] = (defined($_) ? "$_" : "<undef>|");
$idx++;
}
}
# Determine the number of headers (actually this gives index of the last header read in)
$NumHeaders = $#DataHeaders;
print("NumHeaders = $NumHeaders\n");
# read in the first 2 rows of data and put them into the hash table using $Header as the key
my $j;
my $index;
my $Header;
my $k=0;
my $Cell;
my @Row;
my $Tidx=0;
my $RowName;
my %DT;
my %TempDT;
our $variable="Group";
our $row=0;
####ROW CHECK
if ($Method eq "Row"){
print "Here we go ROW \n";
for ($PassedIn1; $PassedIn1 <=$PassedIn2; $PassedIn1++) {
$k=0; # need to know how many rows are in Excel sheet ???
foreach $Header (@DataHeaders) { # this traverses the columns
$index = "$Col[$k]$PassedIn1"; # for example "A2" # this gives us the cell number
$Cell = $Sheet->Range($index)->{'Value'};
if ("TC_Name" eq /$Header/i) {
$RowName = $Cell;
}# End of if ("TC_Name" eq /$Header/i) {
$DataTable[$Tidx]{$Header} = $Cell;
$DT{$RowName}{$Header} = $Cell;
$k++;
}# End of foreach $Header (@DataHeaders) {
$Tidx++;
}# End of for ($j=3; $j <=400; $j++) {
our $row="0";
# To access a specific value, say the 3rd row of TC_Name
if ($DataTable[$row]{$variable} eq "Raid5"){
print"Pass\n";
print("$row row of $variable = ".$DataTable[$row]{$variable}."\n");
}
else {
print"Fail\n";
print($DataTable[$row]{$variable}."\n");
}
} #End of if ($Method == "Row"){
else {
print "We didn't go ROW \n";
}
our $n=0;
####GROUP CHECK
if ($Method eq "Group"){
print "Here we go Group \n";
for ($j=3; $j<=6; $j++) {
$k=0;
if ($j>0){ # need to know how many rows are in Excel sheet ???
foreach $Header (@DataHeaders) {
$index = "$Col[$k]$j"; # for example "A2" # this gives us the cell number
$Cell = $Sheet->Range($index)->{'Value'};
if ("TC_Name" eq $Header) {
$RowName = $Cell;
}# End of if ("TC_Name" eq /$Header/i) {
$TempDataTable[$Tidx]{$Header} = $Cell;
$TempDT{$RowName}{$Header} = $Cell;
$k++;
}# End of foreach $Header (@DataHeaders) {
$Tidx++;
}
if ($TempDataTable[$n]{$Method} eq $PassedIn1){
#$DataTable[$row]{$Method} = $TempDataTable[$n]{$Method};
print "found\n";
###
$k=0;
foreach $Header (@DataHeaders) {
$index = "$Col[$k]$j"; # for example "A2" # this gives us the cell number
$Cell = $Sheet->Range($index)->{'Value'};
if ("TC_Name" eq $Header) {
$RowName = $Cell;
}# End of if ("TC_Name" eq /$Header/i) {
$DataTable[$row]{$Header} = $Cell;
$DT{$RowName}{$Header} = $Cell;
$k++;
}# End of foreach $Header (@DataHeaders) {
$row++;
}
$n++;
}# End of for ($j=3; $j <=400; $j++) {
our $row1="1";
# To access a specific value, say the 3rd row of TC_Name
if ($DataTable[$row1]{"Stripe_Size"} eq "2"){
print"Pass\n";
print("$row1 row of $Method = ".$DataTable[$row1]{"Stripe_Size"}."\n");
}
else {
print"Fail\n";
print($DataTable[$row1]{"Stripe_Size"}."\n");
}
} #End of if ($Method == "Row"){
else {
print "We didn't go Group \n";
}
print("\nClosing book\n");
He is correct. OLE on windows only works if the correct dll's are available. So if excel is not installed, it won't work. However, there are several, and a little simpler, perl modules for reading and writing M$ Excel files. If you use Spreadsheet-ParseExcel or maybe Spreadsheet-SimpleExcel you should be able to do what you want.
Linear Mode
