How to use Perl to assign different fill colors to columns in the same series of an Excel chart?

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

Not a Perl question per-se: This code creates an Excel file containing a column chart with one series of 36 points. The color of each column in this series is the same color.

My question is: how can one use Perl to assign the fill color '#ED7D31' to the first 12 columns, '#4472C4' to the following 12 columns, and '#00B050' to the final 12 columns?

In case it matters: perl 5.24 running on Windows 7.

#!  perl -w

use strict;
use warnings;

use Excel::Writer::XLSX;

my $workbook     = Excel::Writer::XLSX->new( 'test.xlsx' );
my $worksheet    = $workbook->add_worksheet( 'C' );

my $a_fill_color = $workbook->add_format( bg_color => '#ED7D31' );
my $b_fill_color = $workbook->add_format( bg_color => '#4472C4' );
my $c_fill_color = $workbook->add_format( bg_color => '#00B050' );

my $headings = [ 'FY 2024 Big-3', 'Total' ];
my $data     = [
   [ 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec', 'Jan', 'Feb', 'Mar', 'A
+pr', 'May', 'Jun',
     'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec', 'Jan', 'Feb', 'Mar', 'A
+pr', 'May', 'Jun',
     'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec', 'Jan', 'Feb', 'Mar', 'A
+pr', 'May', 'Jun',
   ],
   [ 15, 18, 17, 16, 13, 12, 20, 16, 35, 10, 22, 21,
     10, 22, 20, 28, 24, 28, 23, 34, 39, 27, 56, 35,
      5,  7,  7,  2,  7,  5,  7,  3,  6, 12,  3,  2,
   ],
];
$worksheet->write( 'A1', $headings );
$worksheet->write( 'A2', $data );

my $chart = $workbook->add_chart(
  type     => 'column',
  subtype  => 'clustered',
  embedded => 1,
  name     => 'CHART03' );
$chart->add_series(
  name        => '=C!$B$1',
  categories  => '=C!$A$2:$A$37',
  values      => '=C!$B$2:$B$37',
  data_labels => { value => 1 },
  gap         =>  40,
);

$chart->set_title ( name     => 'FY 2024 Big-3' );
$chart->set_legend( position => 'none' );
$chart->set_style( 10 );
$chart->set_x_axis( name => '', minor_unit => 1, major_unit => 1 );
$chart->set_y_axis( name => '' );
$chart->set_size(width => 1200, height => 600);
$worksheet->insert_chart( '=C!$D$1', $chart, 10, 10 );
$workbook->close() or die "XLSX: Error closing file: $!";
exit(0);
[download]

Searched for donut and crumpit. Found donate and stumbit instead.

Comment on How to use Perl to assign different fill colors to columns in the same series of an Excel chart? Download Code

Replies are listed 'Best First'.
Re: How to use Perl to assign different fill colors to columns in the same series of an Excel chart? by choroba (Cardinal) on Sep 05, 2025 at 07:54 UTC
You need to add three separate series, each configured with a different colour: `my @colors = ('#ED7D31', '#4472C4', '#00B050'); for my $series (1 .. 3) { my $from = 12 * $series - 10; my $to = $series * 12 + 1; $chart->add_series( name => '=C!$B$1', # Not sure about this one. categories => '=C!$A$' . $from . ':$A$' . $to, values => '=C!$B$' . $from . ':$B$' . $to, fill => {color => $colors[ $series - 1 ]}, data_labels => {value => 1}, gap => 40, ); }` [download] `map{substr$_->[0],$_->[1]\|\|0,1}[\\|\|{},3],[[]],[ref qr-1,-,-1],[{}],[sub{}^ARGV,3]`	[reply] [d/l] [select]
Re: How to use Perl to assign different fill colors to columns in the same series of an Excel chart? by kcott (Archbishop) on Sep 05, 2025 at 07:52 UTC
G'day CoVAX, [Disclaimer:* I'm not a regular user of Excel::Writer::XLSX — possibly last used a decade ago. Check any solutions by other monks; they may be much better.]* As your question revolved around colouring columns, I've just focussed on that aspect. I think the easiest thing would be to group the data by the required colours; that would avoid colouring individual cells. I believe the following does what you describe in terms of layout — I tested it by running this code as a standalone script and checking the `pm_11166178.xlsx` spreadsheet it created. #!/usr/bin/env perl use v5.24; use warnings; use Excel::Writer::XLSX; my $workbook = Excel::Writer::XLSX::->new('pm_11166178.xlsx'); my $worksheet = $workbook->add_worksheet('C'); my @formats; { no warnings 'qw'; for my $colour (qw{#ED7D31 #4472C4 #00B050}) { push @formats, $workbook->add_format(bg_color => $colour); } } my @data = ( { token => [[qw{a b c}],[1,2,3]], row => 1, col => 0, format => $formats[0], }, { token => [[qw{d e f}],[4,5,6]], row => 1, col => 3, format => $formats[1], }, { token => [[qw{x y z}],[24,25,26]], row => 1, col => 6, format => $formats[2], }, ); for my $datum (@data) { $worksheet->write_col(@$datum{qw{row col token format}}); } [download] A hint for future reference: providing some ASCII art to describe the wanted layout can be better than a prosaic description (a picture paints a thousand words :-) — Ken	[reply] [d/l] [select]