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

Hello

I'm trying use write_rich_string to write a mixed format string passed to it as a scalar variable.

What I would like to see are red brackets in the following 

[128-A-G-snp;147-CTGTCA-GAGTCC-complex 41.0]

[download]

However, what I do see is 

$red,'[','128-A-G-snp;147-CTGTCA-GAGTCC-complex 41.0',$red,'] '

[download]

my$hyperlink_format = $workbook->add_format(
  color     => 'blue',
  underline => 1,
);
my$red=$workbook->add_format( color => 'red' );
my$row_cnt=@$rows;
my$col_cnt=@{$$rows[0]};
for (my$i=0;$i<$row_cnt;$i++){
  for (my$j=0;$j<$col_cnt;$j++){
    if (($j == 3) && ($i != 0)){
      $worksheet->write( $i, $j, $$rows[$i][$j] , $hyperlink_format, '
+alignment' );
      print LOG "makeExcel: \$\$rows[$i][$j]\t$$rows[$i][$j]\n";
    }elsif((($j == 6)||($j == 8)||($j == 10)) && ($i != 0)){
      my@row=split "]",$$rows[$i][$j];
      my@row_markup=();
      for my$group (@row){
        if ($group=~/^\[(.+)/){
          push(@row_markup,"\$red,'[','$1',\$red,'] '");
        }
      }
      my$row_markup=join '',@row_markup;


      print LOG "\$worksheet->write_rich_string($i, $j, $row_markup)\n
+";


      $worksheet->write_rich_string($i, $j, $row_markup);
      #$worksheet->write_rich_string($i, $j, $red,'[','128-A-G-snp;147
+-CTGTCA-GAGTCC-complex 41.0',$red,']');


    }else{
      $worksheet->write( $i, $j, $$rows[$i][$j] );
    }
  }
}

[download]

From my log file: 

$ grep write_rich_string log
$worksheet->write_rich_string(1, 6, $red,'[','1 100.0',$red,'] ')
$worksheet->write_rich_string(1, 8, $red,'[','147-CTGTCA-GAGTCC-comple
+x 44.0',$red,'] ')
$worksheet->write_rich_string(1, 10, $red,'[','128-A-G-snp;147-CTGTCA-
+GAGTCC-complex 41.5',$red,'] ')

[download]

Note that when I uncomment the hard-coded line in the code (and comment out the line above it) I get the result I desire, namely read brackets.

So it seems like write_rich_string is not interpolating the interpolating $row_markup.

Is there an error in my syntax, a different function for this purpose, or does Excel::Writer::XLSX simly not do it?

Thanks very much for your help.

Replies are listed 'Best First'.
Re: write_rich_string Excel::Writer::XLSX
by kevbot (Vicar) on Jul 04, 2016 at 00:27 UTC
    You seem to be treating $red like it is a scalar value that contains a string. This is not the case. Your $red variable contains a Excel::Writer::XLSX::Format object. The write_rich_string function takes a mixed list of strings and Excel::Writer::XLSX::Format references as arguments and then constructs the formatted string that is placed in the worksheet.

    In your code, this line: 

    $worksheet->write_rich_string($i, $j, $row_markup);

    [download]

    will result in the string contained in $row_markup being placed in the worksheet verbatim.

    Here is a bit of code that might help clear things up: 
    #!/usr/bin/env perl

use strict;
use warnings;

use Excel::Writer::XLSX;

my $workbook = Excel::Writer::XLSX->new( 'test.xlsx' );
my $red = $workbook->add_format( color => 'red' );

print "\$red is a: ", ref($red), "\n";
print "\$red stringifies to: ", $red, "\n";
exit;

    [download]
    Output: 
    $red is a: Excel::Writer::XLSX::Format
$red stringifies to: Excel::Writer::XLSX::Format=HASH(0x7f8c741a4f78)

    [download]
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom