Why does the fillment of a bar chart in Excel::Writer::XLSX not work?

Question

I create an excel sheet including a stacked bar chart. Now I want to have no color in the first bar, but it is still blue. Code looks like this:

$chart->add_series(
    categories    => [ 'Sheet1', 1, $counter, 0, 0 ],
    values        => [ 'Sheet1', 1, $counter, 3, 3 ],
    name          => 'Waiting_time',
    fill          => { none => 1 },
    border        => { none => 1 },
);

I use version 0.5 of Excel::Writer::XLSX

Thanks for your help.

Answer 1

So, to turn my rather lengthy comment into an answer.

There seems to be something missing in Excel::Writer::XLSX.

If you look at your XLSX file in Excel's chart properties window, you'll notice that the fill color is 'blue', but that the border is set to 'none'. That is great, but you do not notice it because you cannot see that the border is missing or if it has the same color as the fill.

Anyway, the fill seems not to work. I checked the chart.xml inside XLSX archive produced by Excel::Writer::XLSX and compared it to one where I changed it to 'no fill' in Excel and saved. There's a lot of overhead created by Excel, but it boils down to this:

<!-- Perl -->
<c:spPr>
  <a:solidFill></a:solidFill>
  <a:ln>
    <a:noFill />
  </a:ln>
</c:spPr>

<!-- after Excel -->
<c:spPr>
  <a:noFill/>
  <a:ln>
    <a:noFill/>
  </a:ln>
</c:spPr>

We can clearly see that there should be a noFill element.

Now if we look at the code in Excel::Writer::XLSX::Chart, there is a method called _write_sp_pr. This makes a chart thingy. It calls two methods in turn, one for lines and one for fills.

# Write the a:solidFill element for solid charts such as pie and bar.
if ( $series->{_fill}->{_defined} ) {
    $self->_write_a_solid_fill( $series->{_fill} );
}

# Write the a:ln element.
if ( $series->{_line}->{_defined} ) {
    $self->_write_a_ln( $series->{_line} );
}

The second one, _write_a_ln, takes care of the none => 1:

sub _write_a_ln {
    # [...]

    # Write the line fill.
    if ( $line->{none} ) {

        # Write the a:noFill element.
        $self->_write_a_no_fill();
    }

    # [...]
}

This is great. So there is no line. But where is the check for the fill color?

sub _write_a_solid_fill {

    my $self = shift;
    my $line = shift;

    $self->{_writer}->startTag( 'a:solidFill' );

    if ( $line->{color} ) {

        my $color = $self->_get_color( $line->{color} );

        # Write the a:srgbClr element.
        $self->_write_a_srgb_clr( $color );
    }

    $self->{_writer}->endTag( 'a:solidFill' );
}

As it turns out, there is none. If there is no color set, it is omitted. But that leads Excel to use the default color, which must be blue.

---

To fix this for you, try the following monkeypatch. Add this stuff to the top of your program that creates the XLSX file with the chart. It will take care of the no-filling. It is the complete sub _write_sp_pr from the original module code with a bit of additional logic mixed it.

use Excel::Writer::XLSX;
{
  no warnings 'redefine';
  sub Excel::Writer::XLSX::Chart::_write_sp_pr {

      my $self   = shift;
      my $series = shift;

      if ( !$series->{_line}->{_defined} and !$series->{_fill}->{_defined} ) {
          return;
      }

      $self->{_writer}->startTag( 'c:spPr' );

      # Write the a:solidFill element for solid charts such as pie and bar.
      if ( $series->{_fill}->{_defined} ) {
          # Check if a noFill element is needed
          if ( $series->{_fill}->{none} ) {
              # Write the a:noFill element.
              $self->_write_a_no_fill();
          } else {
              # Write the line fill.
              $self->_write_a_solid_fill( $series->{_fill} );
          }
      }

      # Write the a:ln element.
      if ( $series->{_line}->{_defined} ) {
          $self->_write_a_ln( $series->{_line} );
      }

      $self->{_writer}->endTag( 'c:spPr' );
  }
}

Here's a little more code to try it out real quick:

use strict; use warnings;
#### put above code here 

# Create a new Excel workbook
my $workbook = Excel::Writer::XLSX->new( 'perl.xlsx' );

# Add a worksheet
my $worksheet = $workbook->add_worksheet();
foreach my $row (0 .. 9) {
  foreach my $col (0 .. 4) {
    $worksheet->write($row, $col, $row+1);
  }
}

my $chart = $workbook->add_chart( type => 'bar' );
$chart->add_series(
    categories    => [ 'Sheet1', 1, 5, 0, 0 ],
    values        => [ 'Sheet1', 1, 5, 3, 3 ],
    name          => 'Waiting_time',
    fill          => { none => 1 },
    border        => { color => 'red' },
);

Of course this is just a quick and dirty solution. I propose to write a bug report for the module and use this question/answer as a reference for the author.

Answer 2

As @simbabque says this is a bug.

I have fixed it on the master branch on Github (commit f4e4191e).

It is scheduled for the 0.51 release which will probably be in the next 2 weeks.

Update: Fixed in version 0.51 of Excel::Writer::XLSX.