Re: How to dump a webpage content into a excel

[hdb]

This how the spreadsheet part could look like:

use strict; use warnings;
use HTML::TableExtract;
use Spreadsheet::WriteExcel;

my $filename = "table.xls";
my $workbook = Spreadsheet::WriteExcel->new($filename);

my $response;
{
    local $/ = undef;
    $response = <DATA>;
}
my $te = HTML::TableExtract->new( keep_html => 0 );
$te->parse($response);
my $counter;
foreach my $ts ($te->tables) {
    my $worksheet = $workbook->add_worksheet("Table ".$counter++);
    my $nrow=0;
    foreach my $row ($ts->rows) {
    my $ncol=0;
    foreach my $col (@$row) {
        $worksheet->write($nrow,$ncol++,$col) if defined $col;
    }
    $nrow++;
    }
 }

[ghosh123]

Hi
Thanks for the solution. It is working.
But what if I want to dump these 3 tables (in this current html content)in one worksheet instead of 3 worksheets.
I removed the $workbook-> add_worksheet line from the loop and dumping these all in one work sheet . But the 1st and last tables are not properly shown. How can I get rid of that ?
Also how can I show the column headers in bold ?

[hdb]

Did you put the 3 tables elsewhere on the sheet? Or do you overwrite them? For bold format you need to look into the documentation of Spreadsheet::WriteExcel.

[ghosh123]

Hi
Yes , I commented out the add_worksheet line inside the foreach loop and created only one worksheet before the for each loop (please see below).
Also, iterating the $nrow++ one more time after the $ts->rows foreach. Please correct me .


my $worksheet = $workbook->add_worksheet("Table");
my $te = HTML::TableExtract->new( keep_html => 0 );
$te->parse($response);
my $counter;
foreach my $ts ($te->tables) {
   # my $worksheet = $workbook->add_worksheet("Table ".$counter++);
    my $nrow=0;
    foreach my $row ($ts->rows) {
    my $ncol=0;
    foreach my $col (@$row) {
        $worksheet->write($nrow,$ncol++,$col) if defined $col;
    }
    $nrow++;
    }
    $nrow++;
 }