Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change
 
PerlMonks  

Re: How to dump a webpage content into a excel

by hdb (Prior)
on Apr 09, 2013 at 11:41 UTC ( #1027710=note: print w/ replies, xml ) Need Help??


in reply to How to dump a webpage content into a excel

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++; } }


Comment on Re: How to dump a webpage content into a excel
Download Code
Re^2: How to dump a webpage content into a excel
by ghosh123 (Monk) on Apr 23, 2013 at 09:52 UTC

    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 ?

      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.

        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++; }

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1027710]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (8)
As of 2015-07-04 15:23 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (60 votes), past polls