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

[CoVAX]

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);

[choroba]

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,
    );
}

[kcott]

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}});
}

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 :-)