Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris

Excel::Writer::XLSX write_comment and text color

by actionphotos (Initiate)
on Mar 03, 2018 at 17:48 UTC ( #1210289=perlquestion: print w/replies, xml ) Need Help??
actionphotos has asked for the wisdom of the Perl Monks concerning the following question:

Hello, I can see how to change the background color of a comment box, but I am trying to find out how can I change the text color in a comment box? Is this possible? I know it is possible in excel itself, but trying via perl script. thanks
  • Comment on Excel::Writer::XLSX write_comment and text color

Replies are listed 'Best First'.
Re: Excel::Writer::XLSX write_comment and text color
by vr (Chaplain) on Mar 04, 2018 at 16:10 UTC

    Reading the source: even though there is "a font format for cell comments" defined, with hard-coded attributes, it's probably never used. Comments' text font, size and color appear to be even more deeply hard-coded. So, ugly solution would be to hack source, or re-define a sub in Excel::Writer::XLSX::Package::Comments package.

    As to color index "81", I'm not sure but it looks like 64 and above is "default" i.e. black. Why 81 then? Don't know. To use different color for different comments, other than post-process XLSX files (which are ZIP archives, as you of course know), I have no other solutions.

Re: Excel::Writer::XLSX write_comment and text color
by poj (Monsignor) on Mar 04, 2018 at 17:05 UTC
    Is this possible?

    On windows using Win32::OLE

    #!perl use strict; use Win32::OLE 'in'; use Win32::OLE::Const 'Microsoft Excel'; $Win32::OLE::Warn = 3; # Die on Errors. # config my $dir = 'c:/temp/'; my $file = 'perl.xlsx'; my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit'); $Excel->{DisplayAlerts} = 0; #$Excel->{Visible} = 1; my $Book = $Excel->Workbooks->Open($dir.$file); my $ws = $Book->Worksheets(1); for my $c (in $ws->comments) { print $c->text."\n"; my $str = $c->{'Shape'}->{'TextFrame'}->Characters(); $str->{'Font'}->{'ColorIndex'} = 3; # red } # close $Book->Save; $Excel->Quit(); undef $Book; undef $Excel;
Re: Excel::Writer::XLSX write_comment and text color
by thanos1983 (Vicar) on Mar 04, 2018 at 11:48 UTC

    Hello actionphotos,

    Yes it can be done, see sample of code bellow:

    Sorry I just noticed that you mentioned the text color and not the background color.

    #!/usr/bin/perl use strict; use warnings; use Excel::Writer::XLSX; # Create a new Excel workbook my $workbook = Excel::Writer::XLSX->new( 'perl.xlsx' ); # Add a worksheet my $worksheet = $workbook->add_worksheet(); # Write a formatted and unformatted string, row and column notation. $worksheet->write( 'C3', 'Hi Excel!'); $worksheet->write_comment( 'C3', 'Comment with Color', color => 'green +' );

    You can read more about the other options here Excel::Writer::XLSX/write_comment( $row, $column, $string, ... ).

    Update: Reading again and again the documentation I do not think so it is possible to update the character color of the comment. You can update the character colors of the spreadsheet yes, but not the comments. In case you find a solution to your problem can you also share it here so someone else in the future might benefit from it.

    Hope this helps, BR.

    Seeking for Perl wisdom...on the process of learning...not there...yet!

Log In?

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

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (13)
As of 2019-01-17 14:46 GMT
Find Nodes?
    Voting Booth?
    After Perl5, I'm mostly interested in:

    Results (307 votes). Check out past polls.