I've been working on a simple script file in Perl to open a series of
excel files, and extract specific data from each file to place into a
single new excel file. The only problem is I'm getting an error I
really can't seem to fix.
retrying default method at C:/Perl/site/lib/Win32/OLE/Lite.pm line 156
Win32::OLE(0.1403) error 0x80020003: "Member not found"
in METHOD/PROPERTYGET "" at C:\mini-FNDTN\automation.pl line 107
I've tried to pinpoint the problem by trying out more simple test
programs, and it seems I'm unable to even to simply just open any excel
files (ones even I created on the fly for testing). This is my first
time really working with excel through Perl, so I'm stumped. What's
going on you think, any suggestions?
Imma rookie for excel stuff.
Here is my first attempt. I takes an excel spreadsheet (row 1 is
assumed to be column names and not data) and puts it into a perl hash
I can either be called as a perl module or as a standalone script
#!/usr/local/bin/perl
######################################################################
# Purpose : To parse a .xls file and store into a perl hash
# Author : Billy N. Patton
# Original : 16AUG2006
# Notes : Each sheet will be a top level hash
# Input :
# Changes :
######################################################################
use Data:
umper; $Data:
umper::Indent = 1;
use Carp;
use Spreadsheet:
arseExcel;
use strict;
use warnings;
$| = 1; # output auto flush
######################################################################
## use module as a standalone script
######################################################################
if ( !defined caller )
{
XHelp("2 arguments are required, recieved none") if scalar(@ARGV) == 0;
XHelp("2 arguments are required, Only recieved " . scalar(@ARGV))
if scalar(@ARGV) < 2;
my $ifile = $ARGV[0];
my $ofile = $ARGV[1];
my %hash;
XHelp("'$ifile' does not exist!") unless -f $ifile;
my $ret = ExcelToHash::ExcelToHash($ifile,\%hash);
croak "Unable to open file '$ofile' for writing!\n" unless open OUT ,
">$ofile";
print OUT Dumper(\%hash);
close OUT;
exit $ret;
sub XHelp
{
my ($msg) = @_;
$_ = `pod2text $0`;
print "\n\n$_\n\n";
print "$msg\n" if defined $msg;
exit 1;
}
}
######################################################################
## Module ExcelToHash
######################################################################
package ExcelToHash;
use Exporter;
use vars qw(@EXPORT @ISA $VERSION);
$VERSION = sprintf("%d.%02d", q$Revision: 1.1 $ =~ /(\d+)\.(\d+)/);
@ISA = qw ( Exporter );
@EXPORT = qw ( ExcelToHash );
sub ExcelToHash
{
my ($ifile,$hash) = @_;
my $oBook = Spreadsheet:
arseExcel::Workbook->Parse($ifile);
print("Spreadsheet:
arseExcel::Workbook->Parse($ifile)
failed!\n"),return(0) unless defined $oBook;
my($iR, $iC, $oWkC);
my $sheet_p = undef; # hold pointer to the current sheet
my $sheet_n = undef; # hold name of current sheet
my @column_names = (); # hold the column names
foreach $sheet_p (@{$oBook->{Worksheet}})
{
$sheet_n = $sheet_p->{Name};
#warn $sheet_n;
$hash->{$sheet_n} = undef;
$iR = $sheet_p->{MinRow};
@column_names = ();
for($iC = $sheet_p->{MinCol} ; defined $sheet_p->{MaxCol} && $iC <=
$sheet_p->{MaxCol} ; $iC++)
{
$oWkC = $sheet_p->{Cells}[$iR][$iC];
push @column_names , $oWkC->Value if defined $oWkC;
}
#print Dumper(\@column_names);
for($iR = $sheet_p->{MinRow} + 1; defined $sheet_p->{MaxRow} && $iR
<= $sheet_p->{MaxRow} ; $iR++)
{
for ($iC = 0; $iC < scalar(@column_names); $iC++)
{
$oWkC = $sheet_p->{Cells}[$iR][$iC];
$hash->{$sheet_n}->{$iR+1}->{$column_names[$iC]} = $oWkC->Value
if($oWkC);
}
}
}
return 1;
}
__END__
=head1 NAME
ExcelToHash.pm - converts an excel spread sheet to a perl hash As a
module or standalone
=head1 SYNOPSIS
As a standalone script
ExcelToHash.pm my_sheets.xls my_hash.hash
or
as a callable module
use Data:
umper; $Data:
umper::Indent = 1;
use ExcelToHash;
my %hash;
my $ifile = 'something.xls';
croak "ExcelToHash failed\n" unless ExcelToHash($ifile,\%hash);
print Dumper(\%hash);
=head1 INPUT
As a standalone
Both are required
1. .xls file
2. output file for hash
As a module
1. .xls file
2. pointer to hash table
=head1 DESCRIPTION
NOTE : Row 1 of each sheet is presumed to be header information and
those column names
are used as keyword of the hash
The following is an example of the output.
The first layer are the sheet names
The second layer are the row numbers
the third layer is the column name
The values are sheet->row->column
$VAR1 = {
'DrawnLayers' => {
'2' => {
'Design Rule Number' => '2',
'Design Rule Level' => 'ACTIVE'
}
},
'DesignRules' => {
'4' => {
'Layer' => 'NWELL',
'Relation' => 'space with square corner',
'Image' => 'Rule-1-NWELL-drawing.doc',
'Rule Code' => '1B.',
'Description' => '*NWELL spacing',
'Drawing Size' => '420'
},
'3' => {
'Layer' => 'SELF',
'Relation' => 'width with square corner ',
'Image' => 'Rule-1-NWELL-drawing.doc',
'Rule Code' => '1A.',
'Description' => '*NWELL width',
'Drawing Size' => '420'
},
This is as generic as possible. No names are hard coded.
=head1 AUTHOR
Billy N. Patton
(e-mail address removed)
=head1 ORIGINAL
17AUG2006
=head1 CHANGES
=cut
