Simple yet peculiar bug

Discussion in 'Perl Misc' started by rthompson414@gmail.com, Jan 8, 2007.

  1. Guest

    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?
     
    , Jan 8, 2007
    #1
    1. Advertising

  2. Billy Patton Guest

    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::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


    =head1 ORIGINAL

    17AUG2006

    =head1 CHANGES

    =cut
     
    Billy Patton, Jan 8, 2007
    #2
    1. Advertising

  3. J. Gleixner Guest

    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.
     
    J. Gleixner, Jan 8, 2007
    #3
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. mattias
    Replies:
    0
    Views:
    412
    mattias
    Jul 1, 2004
  2. Richard K Bethell
    Replies:
    4
    Views:
    405
  3. Randy Smith
    Replies:
    0
    Views:
    659
    Randy Smith
    Apr 28, 2005
  4. Randy Smith
    Replies:
    0
    Views:
    442
    Randy Smith
    Apr 28, 2005
  5. Berehem
    Replies:
    4
    Views:
    593
    Lawrence Kirby
    Apr 28, 2005
Loading...

Share This Page