Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
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 avoiding work at the Monastery: (6)
As of 2019-03-20 15:09 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    How do you Carpe diem?





    Results (101 votes). Check out past polls.

    Notices?
    • (Sep 10, 2018 at 22:53 UTC) Welcome new users!