Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
PerlMonks  

How to modify column in WriteExcel?

by vserzh (Novice)
on Mar 13, 2019 at 10:12 UTC ( [id://1231206]=perlquestion: print w/replies, xml ) Need Help??

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

Hello! I'm beginner in Perl and I need advice :) I use SpreadSheet::WriteExcel to create file from SQL query.

query code ###################################### .... #get data $sth->execute(); my $columns = $sth->{NAME}; my $data = $sth->fetchall_arrayref(); #create Spreadsheet my $workbook = Spreadsheet::WriteExcel->new('Report.xls'); my $worksheet = $workbook->add_worksheet("DTicket"); #add format my $format = $workbook->add_format(); $format->set_bold(); #write data $worksheet->write_row('A1', $columns, $format); $worksheet->write_col('A2', $data); .... ############################ other code

After write raw data I need modify first column text into http-links. First column contain numbers like 10005, I need modify every row with data to http-link like http://example.com/index.pl?TNumber=10005. HYPERLINK function is not support and I don't find any way to request whole column without additional $sth->execute(); but I can't use it and write_url method because I get just ref on array element like ARRAY(0x15530a0), but not value.

#Example for one cell $sth->execute(); my $url = $sth->fetchall_arrayref([0]); $worksheet->write_url(3,0, "http://example.com/index.pl?TNumber=${$url +}[0]", "${$url}[0]");

So, the question is how I can get value of column and make and write link for each row?

Replies are listed 'Best First'.
Re: How to modify column in WriteExcel?
by hdb (Monsignor) on Mar 13, 2019 at 10:16 UTC

    A useful tool in such a situation is Data::Dumper to investigate your data structures. For example you might want to try:

    use Data::Dumper; ... print Dumper $url;
      As I understand I get exactly what I need, but.. :)
      $VAR1 = [ [ '10000516' ], [ '10000512' ], [ '10000514' ], [ '10000513' ], [ '10000515' ] ];
        #!perl use strict; use Spreadsheet::WriteExcel; my $workbook = Spreadsheet::WriteExcel->new('Report.xls'); my $worksheet = $workbook->add_worksheet("DTicket"); #my $url = $sth->fetchall_arrayref([0]); my $url = [['10000516'],['10000512'], ['10000514'],['10000513'],['10000515']]; my $rowno = 2; for my $row (@$url ){ my $url = 'http://example.com/index.pl?TNumber='.$row->[0]; $worksheet->write_url($rowno++,0, $url, $row->[0]); }
        poj

        If this is the output of print Dumper $url; then you need to use $url->[0][0] instead of $url->[0] (the latter being the same as ${$url}[0]).

        Update: or ${${$url}[0]}[0] if you prefer.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://1231206]
Approved by marto
Front-paged by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others having a coffee break in the Monastery: (3)
As of 2025-06-15 09:25 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?
    erzuuliAnonymous Monks are no longer allowed to use Super Search, due to an excessive use of this resource by robots.