Excel File Problem

joelnackman has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks,

I am writing a program that extracts data from a contact list on the web. I am using the Spreadsheet::WriteExcel module to write the data to an Excel file. The section of code that writes to the file looks like this, where @people is an array of anonymous hashes:

    foreach my $person (@people) {
        my @personData;
        push @personData, $person->{"name"};
        push @personData, $person->{"email"};
        push @personData, $person->{"phone"};
        $worksheet->write_row($excelRow, 0, \@personData) or die "Writ
+e failed: $!\n";
        $excelRow++;
    }
[download]

The write_row seems to fail every time, but I haven't been able to figure out why. I've checked to make sure that the $worksheet variable actually exists when the write_row is executing, and as far as I can tell, it is. I think that possibly the problem might have something to do with the Tk in the program, because I was able to run the program successfully before I put in the interface code. Does the MainLoop() have some sort of effect on the rest (possibly variables) of the program?

Any help is very much appreciated. Thanks!
Full code:

#!/usr/bin/perl
use warnings;
use strict;

use LWP::Simple;
use Spreadsheet::WriteExcel;
use Tk;

my $excelRow = 0;
my $URL;
my @people;
my $workbook = Spreadsheet::WriteExcel->new("names.xls");
my $worksheet = $workbook->add_worksheet("Names");

my $mw = MainWindow->new();
$mw->title("Data Extractor");

#Instructions
$mw->Label(-text => "Enter a URL, and hit Extract.")->grid(-row => 0,
                                                            -column =>
+ 0,
                                                            -columnspa
+n => 3);
#Interface
$mw->Label(-text => "URL:")->grid(-row => 1,
                                    -column => 0,
                                    -sticky => 'w');
$mw->Entry(-textvariable => \$URL)->grid(-row => 1,
                                                        -column => 1,
                                                        -columnspan =>
+ 3,
                                                        -ipadx => '30'
+);
                                            
$mw->Button(-text => "Extract",
            -command => \&getData)->grid(-row => 2,
                                            -column => 0,
                                            -columnspan => 3,
                                            -pady => '10');
MainLoop();

sub getData() {
    my @data = split /\n/, get($URL);
    foreach my $line (@data) {
        if ($line =~ /<td/) {
            my @people  = split /<\/td>/, $line;
            foreach my $person (@people) {
                if ($person =~ /mailto:/) {
                    &extractData($person);
                }
            }
        }
    }
    
    foreach my $person (@people) {
        my @personData;
        push @personData, $person->{"name"};
        push @personData, $person->{"email"};
        push @personData, $person->{"phone"};
        $worksheet->write_row($excelRow, 0, \@personData) or die "Writ
+e failed: $!\n";
        $excelRow++;
    }
    @people = undef;
}

sub extractData() {
    my $person = shift @_;
    my ($name, $email, $phone);
    if ($person =~ /<strong>(.*)<\/strong>/) {
        $name = $1;
    }
    
    if ($person =~ /\"mailto:(.*)\">\1/) {
        $email = convertHTMLEmail($1);
    }
    
    if ($person =~ /(\(\d{3}\) ?\d{3}-?\d{4})/) {
        $phone = $1;
    }
    
    push @people, {"name" => $name, "email" => $email, "phone" => $pho
+ne};
}

sub convertHTMLEmail() {
    my $emailString = shift @_;
    $emailString =~ s/&#//g;
    
    my @chars = split /;/, $emailString;
    my @convertedChars;
    
    foreach (@chars) {
        push @convertedChars, chr($_);
    }
    
    return join "", @convertedChars;
}
[download]

Comment on Excel File Problem Select or Download Code

Replies are listed 'Best First'.
Re: Excel File Problem by liverpole (Monsignor) on May 20, 2006 at 15:50 UTC
Hi joelnackman, If you read the documentation for write_row, you'll see that it generates the same return code as write, which is: `0 for success. -1 for insufficient number of arguments. -2 for row or column out of bounds. -3 for string too long.` [download] So I believe you are incorrectly exiting with your code: `$worksheet->write_row($excelRow, 0, \@personData) or die "Write failed +: $!\n";` [download] That is to say, try changing or to and in the above line, and that section of your code should work. You don't need to check the value of "$!", because it's not a system call, which may thus be giving you misleading results. When I ran it, after "doctoring" the @personData array to hold some valid values, I was getting the error Write Failed: Bad file descriptor, which is a red herring. When the logic was changed to interpret zero as success (and I added a line workbook->close() immediately afterwords), the Excel spreadsheet correctly contained the data I had written. s''(q.S:$/9=(T1';s;(..)(..);$..=substr+crypt($1,$2),2,3;eg;print$..$/	[reply] [d/l] [select]
Re^2: Excel File Problem by joelnackman (Beadle) on May 20, 2006 at 16:03 UTC
Thanks, that seems to work much better. So I need to close the workbook after I write to it in order to save the changes? The write dosen't work even with the `or` fixed unless I close the workbook. Is there a way to continue editing the workbook (allow for multiple URLs to be used) after the write?	[reply] [d/l]
Re^3: Excel File Problem by liverpole (Monsignor) on May 20, 2006 at 16:12 UTC
You can read the the documentation, but I'm guessing you will need to close the workbook before the changes take effect. So your choices are probably limited to: Closing the workbook at end of the program Closing it after writing, and (if need be) reading it back with Spreadsheet::ParseExcel. s''(q.S:$/9=(T1';s;(..)(..);$..=substr+crypt($1,$2),2,3;eg;print$..$/	[reply]
Re: Excel File Problem by dragonchild (Archbishop) on May 20, 2006 at 15:38 UTC
The error message, if any, would be helpful. Have you considered passing the $worksheet into getData()? `$mw->Button(-text => "Extract", -command => sub { getData( $worksheet ) })-> ...` [download] My criteria for good software: Does it work? Can someone else come in, make a change, and be reasonably certain no bugs were introduced?	[reply] [d/l]
Re^2: Excel File Problem by joelnackman (Beadle) on May 20, 2006 at 15:53 UTC
Yes, I've tried to pass `$worksheet` to `getData()`, but it dosen't work. Also, there is unfortunately no error message except for the die. In case it's helpful, this is what it prints out: `Tk::Error: Write row failed: main::getData at person_list.pl line 57 main::__ANON__ at person_list.pl line 32 Tk callback for .button Tk::__ANON__ at /Library/Perl/5.8.6/darwin-thread-multi-2level/Tk.pm +line 247 Tk::Button::butUp at /Library/Perl/5.8.6/darwin-thread-multi-2level/T +k/Button.pm line 111 <ButtonRelease-1> (command bound to event)` [download]	[reply] [d/l] [select]