Simple yet peculiar bug

R

rthompson414

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?
 
B

Billy Patton

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::Dumper; $Data::Dumper::Indent = 1;
use Carp;
use Spreadsheet::parseExcel;
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::parseExcel::Workbook->Parse($ifile);
print("Spreadsheet::parseExcel::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::Dumper; $Data::Dumper::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
 
J

J. Gleixner

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?

Either post a simple example or use your favorite search engine to
see if it's been reported before (it has) and see if any of those
are similar to what you're doing.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top