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

How to modify column in WriteExcel?

by vserzh (Novice)
on Mar 13, 2019 at 10:12 UTC ( #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
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? | Other CB clients
Other Users?
Others surveying the Monastery: (5)
As of 2019-10-19 04:19 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Notices?