Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Perl > Perl Misc > Simple yet peculiar bug

Reply
Thread Tools

Simple yet peculiar bug

 
 
rthompson414@gmail.com
Guest
Posts: n/a
 
      01-08-2007
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?

 
Reply With Quote
 
 
 
 
Billy Patton
Guest
Posts: n/a
 
      01-08-2007
http://www.velocityreviews.com/forums/(E-Mail Removed) wrote:
> 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 Removed)

=head1 ORIGINAL

17AUG2006

=head1 CHANGES

=cut
 
Reply With Quote
 
 
 
 
J. Gleixner
Guest
Posts: n/a
 
      01-08-2007
(E-Mail Removed) wrote:
> 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.
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Peculiar File Transfer Problems Blaine Hamrick Wireless Networking 1 06-12-2005 10:47 AM
peculiar SQL Data Adapter Configuration Wizard Refresh problem Randy Smith ASP .Net 0 04-28-2005 07:52 PM
Peculiar problem with refresh of sqldataadapter re: stored procedures Randy Smith ASP .Net 0 04-28-2005 05:44 PM
Peculiar error when starting the Visual Studio.net mattias ASP .Net 0 07-01-2004 11:59 AM
peculiar ViewState corruption problem when clients are FrameWork 1.0 Richard K Bethell ASP .Net 4 12-17-2003 08:44 AM



Advertisments