Gap property for each serie

[temoon]

Hi! Excel format allows me to use specific 'gap' property for each bar/column on chart, but Excel::Writer::XLSX don't. Is it possible to use many 'gap' options?

[jmcnamara]

Hi Tema,

That is currently possible. See the gap and overlap properties of add_series()

Regards,

John

[jmcnamara]

Example:

use strict;
use warnings;
use Excel::Writer::XLSX;

my $filename     = 'chart_gap01.xlsx';

my $workbook  = Excel::Writer::XLSX->new( $filename );
my $worksheet = $workbook->add_worksheet();
my $chart     = $workbook->add_chart( type => 'column', embedded => 1 );

my $data = [
    [ 1, 2, 3, 4,  5 ],
    [ 2, 4, 6, 8,  10 ],
    [ 3, 6, 9, 12, 15 ],

];

$worksheet->write( 'A1', $data );

$chart->add_series(
    categories => '=Sheet1!$A$1:$A$5',
    values     => '=Sheet1!$B$1:$B$5',
    overlap    => 5,
    gap        => 157,
);

$chart->add_series(
    categories => '=Sheet1!$A$1:$A$5',
    values     => '=Sheet1!$C$1:$C$5',
);

$worksheet->insert_chart( 'E9', $chart );

$workbook->close();

[jmcnamara]

Hi,

Sorry, re-reading your question I see that you may already know about the gap option but are asking if it is possible to change it for each series.

As far as I know that isn't possible in Excel. You can change them for each series but Excel just changes it for all series.

John

[temoon]

Hi!

It is possible for each axis on chart, not chart at all. For example, I have 4 columns on chart. Three of them on the y2_axis and one on y_axis. In the Excel I can change gap for each of axis! :)

[jmcnamara]

Hi Tema,

Thanks for the example. In that case this is a bug since the gap should be supported on each axis.

I'll look into fixing it.

Regards,

John

[temoon]

Thanks! I hope it would be easy for you.

[jmcnamara]

Hi Tema,

I've pushed a fix for this to master. Here is an example:

use strict;
use warnings;
use Excel::Writer::XLSX;


my $filename  = 'chart_gap04.xlsx';
my $workbook  = Excel::Writer::XLSX->new( $filename );
my $worksheet = $workbook->add_worksheet();
my $chart     = $workbook->add_chart(type => 'column', embedded => 1);

my $data = [ [ 1, 2, 3, 4, 5 ],
             [ 6, 8, 6, 4, 2 ] ];

$worksheet->write( 'A1', $data );

$chart->add_series(
    values  => '=Sheet1!$A$1:$A$5',
    gap     => 51,
    overlap => 12,
);

$chart->add_series(
    values  => '=Sheet1!$B$1:$B$5',
    gap     => 251,
    overlap => -27,
    y2_axis => 1,
);

$worksheet->insert_chart( 'E9', $chart );

$workbook->close();

Try it if you get a chance. I'll push it to CPAN in the next day or two.

John

[temoon]

Yes, it works!

[jmcnamara]

Fixed in version 0.82.

Thanks for the report and input.

John.