Combine more than two charts? #157

rdboisvert · 2016-01-25T16:47:50Z

I ran into a problem creating a combination chart. Perhaps I'm doing it wrong or this feature is not yet supported. Can you help?

The chart has 3 series and I can create it in Excel (I have a sample but don't know how to display it).

Quality measurement with vertical bars and the primary Y axis
Volume as a line using the secondary Y axis
Average quality as a line using the primary Y axis

I tried using the code below to create the chart but when I opened the file it had an error and was unable to display the chart.

my $oChart = $oOut->add_chart (type => 'column', embedded => 1);
$oChart->add_series
  (
  name => 'Quality',
  categories => "='$oRpt->{sheet}'!\$A\$2:\$A\$$nRow",
  values => "='$oRpt->{sheet}'!\$B\$2:\$B\$$nRow",
  );
my $oLine = $oOut->add_chart (type => 'line', embedded => 1);
$oLine->add_series
  (
  name => 'Volume',
  categories => "='$oRpt->{sheet}'!\$A\$2:\$A\$$nRow",
  values => "='$oRpt->{sheet}'!\$C\$2:\$C\$$nRow",
  y2_axis => 1,
  );
$oLine->add_series
  (
  name => 'Average',
  categories => "='$oRpt->{sheet}'!\$A\$2:\$A\$$nRow",
  values => "='$oRpt->{sheet}'!\$D\$2:\$D\$$nRow",
  y_axis => 1,
  );
$oChart->combine ($oLine);

Changing the third series into a third chart doesn't work either. It shows the Average series but the Volume is missing.

my $oLine2 = $oOut->add_chart (type => 'line', embedded => 1);
$oLine2->add_series
  (
  name => 'Average',
  categories => "='$oRpt->{sheet}'!\$A\$2:\$A\$$nRow",
  values => "='$oRpt->{sheet}'!\$D\$2:\$D\$$nRow",
  y_axis => 1,
  );
$oChart->combine ($oLine);
$oChart->combine ($oLine2);

Am I missing something? Is there a correct way to do this?

The text was updated successfully, but these errors were encountered:

jmcnamara · 2016-01-25T16:56:07Z

I thought it wasn't possible to combine more than 2 charts in Excel.

Perhaps you could attach a screenshot of what you are trying to do or attach an Excel file (Github allows that).

John

rdboisvert · 2016-01-25T17:27:25Z

Sample of desired output.
CombineCharts.xlsx

jmcnamara · 2016-01-25T17:45:29Z

That is only 2 charts combined so it should be possible. Post a small working example and I'll look into it.

rdboisvert · 2016-01-25T18:28:32Z

Here is the code and what is it should create.
CombineCharts.xlsx

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

my $workbook  = Excel::Writer::XLSX->new( 'CombineCharts.xlsx' );
my $worksheet = $workbook->add_worksheet();
my $bold      = $workbook->add_format( bold => 1 );

# Add the worksheet data that the charts will refer to.
my $headings = [ 'Team', 'Quality', 'Volume', 'Average' ];
my $data = [
    [ 'A',  'B',  'C',  'D',  'E',  'F' ],
    [ 0.13, 0.18, 0.25, 0.39, 0.60, 0.97 ],
    [ 312, 145, 555, 445, 78, 156 ],
    [ 0.341218214, 0.341218214, 0.341218214, 0.341218214, 0.341218214, 0.341218214 ],
];

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

#
# In the first example we will create a combined column and line chart.
# They will share the same X and Y axes.
#

# Create column chart.
my $column_chart = $workbook->add_chart( type => 'column', embedded => 1 );

# Quality series.
$column_chart->add_series(
    name       => '=Sheet1!$B$1',
    categories => '=Sheet1!$A$2:$A$7',
    values     => '=Sheet1!$B$2:$B$7',
);

# Create line chart.
my $line_chart = $workbook->add_chart( type => 'line', embedded => 1 );

# Volume series.
$line_chart->add_series(
    name       => '=Sheet1!$C$1',
    categories => '=Sheet1!$A$2:$A$7',
    values     => '=Sheet1!$C$2:$C$7',
    y2_axis => 1,
);

# Average series.
$line_chart->add_series(
    name       => '=Sheet1!$D$1',
    categories => '=Sheet1!$A$2:$A$7',
    values     => '=Sheet1!$D$2:$D$7',
);

# Combine the charts.
$column_chart->combine( $line_chart );

# Label chart.
$column_chart->set_title( name => 'Combined chart' );
$column_chart->set_legend( position => 'bottom' );
$column_chart->set_y_axis ( num_format => '0.00', num_font => {bold => 1} );
$line_chart->set_y2_axis ( num_format => '###,0', num_font => {bold => 1} );

# Insert the chart into the worksheet
$worksheet->insert_chart( 'F2', $column_chart );

rdboisvert · 2016-04-27T18:48:37Z

Just looking for an update. Do you think this can be done? If so, do you have a target date?

jmcnamara · 2016-04-27T19:52:38Z

Do you think this can be done?

I think it probably cannot be done using the current API and implementation. It looks like a case that I hadn't considered.

rdboisvert · 2016-08-17T18:05:04Z

FYI, we are still looking for this feature. In fact, we would like to have a chart with 3 lines and 1 bar. It would be nice if you were able to do this.

jmcnamara · 2016-08-17T20:21:05Z

As it turns out I was looking at this recently. I got a similar, but simpler, example and it turns out that it doesn't work due to a bug.

I partially fixed it, on the axis_ids branch but there is still a fairly hard to fix bug that prevents it from working.

You can workaround your issue by using the axis_ids branch and manually setting the axis ids (like at the end of the example below, based on you example):

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

my $workbook  = Excel::Writer::XLSX->new( 'CombineCharts.xlsx' );
my $worksheet = $workbook->add_worksheet();
my $bold      = $workbook->add_format( bold => 1 );

# Add the worksheet data that the charts will refer to.
my $headings = [ 'Team', 'Quality', 'Volume', 'Average' ];
my $data = [
    [ 'A',  'B',  'C',  'D',  'E',  'F' ],
    [ 0.13, 0.18, 0.25, 0.39, 0.60, 0.97 ],
    [ 312, 145, 555, 445, 78, 156 ],
    [ 0.341218214, 0.341218214, 0.341218214, 0.341218214, 0.341218214, 0.341218214 ],
];

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

#
# In the first example we will create a combined column and line chart.
# They will share the same X and Y axes.
#

# Create column chart.
my $column_chart = $workbook->add_chart( type => 'column', embedded => 1 );

# Quality series.
$column_chart->add_series(
    name       => '=Sheet1!$B$1',
    categories => '=Sheet1!$A$2:$A$7',
    values     => '=Sheet1!$B$2:$B$7',
);

# Create line chart.
my $line_chart = $workbook->add_chart( type => 'line', embedded => 1 );

# Volume series.
$line_chart->add_series(
    name       => '=Sheet1!$C$1',
    categories => '=Sheet1!$A$2:$A$7',
    values     => '=Sheet1!$C$2:$C$7',
    y2_axis => 1,
);

# Average series.
$line_chart->add_series(
    name       => '=Sheet1!$D$1',
    categories => '=Sheet1!$A$2:$A$7',
    values     => '=Sheet1!$D$2:$D$7',
);

# Combine the charts.
$column_chart->combine( $line_chart );

# Label chart.
$column_chart->set_title( name => 'Combined chart' );
$column_chart->set_legend( position => 'bottom' );
$column_chart->set_y_axis ( num_format => '0.00', num_font => {bold => 1} );
$line_chart->set_y2_axis ( num_format => '###,0', num_font => {bold => 1} );

# Workaround for axis_id bug gh157.
$column_chart->{_axis_ids} = [ 50010001, 50010002 ];
$line_chart->{_axis_ids}   = [ 50010001, 50010002 ];
$line_chart->{_axis2_ids}  = [ 60010001, 60010002 ];

# Insert the chart into the worksheet
$worksheet->insert_chart( 'F2', $column_chart );

If you have more than 1 chart like this, then use similar, but unique, axis ids.

rdboisvert · 2016-08-18T12:43:05Z

How do I determine what values to put in the axis IDs?

jmcnamara · 2016-08-18T13:38:24Z

The axis_ids just need to be unique and not conflict with other chart ids in the workbook.

To be safe you can use something like:

$column_chart1->{_axis_ids} = [ 70010001, 70010002 ];
$line_chart1->{_axis_ids}   = [ 70010001, 70010002 ];
$line_chart1->{_axis2_ids}  = [ 80010001, 80010002 ];

$column_chart2->{_axis_ids} = [ 70010003, 70010004 ];
$line_chart2->{_axis_ids}   = [ 70010003, 70010004 ];
$line_chart2->{_axis2_ids}  = [ 80010003, 80010004 ];

... etc

This only needs to be done for charts with mixed combined axes and only if you really need this now and can't wait until it is fixed properly within the module.

rdboisvert · 2016-08-18T14:24:06Z

Thanks, this workaround will help.

carsten-becker · 2022-10-11T13:26:16Z

As I steped into this problem recently: has this workaround been implemented into the official release?

jmcnamara · 2022-10-11T23:16:13Z

No. There has been no change to this.

true-alex · 2022-11-25T14:58:12Z

As part of solving one of my tasks, I modified the XlsxWriter module so that it could combine more than two charts in one. The solution passes all basic tests, Excel 2007 files open without errors. I would like to propose changes I made to be included in the library code, however I cannot figure out which tests I need to add. Now the combine method is checked only by regression tests. Is it enough for me to add some regression tests?

jmcnamara · 2022-11-25T20:30:38Z

@true-alex

As part of solving one of my tasks, I modified the XlsxWriter module so that it could combine more than two charts in one.

Do you propose to do it for XlsxWriter or Excel::Writer::Xlsx, I maintain both. Although either one is fine.

Now the combine method is checked only by regression tests. Is it enough for me to add some regression tests?

Yes, regression tests that compare against Excel generated file are the best. Ideally against Excel 2007 files since later versions add a lot of additional fields that aren't always required. I can probably generate some suitable test files for you but it will take me a few weeks to get to it.

jmcnamara self-assigned this Jan 25, 2016

jmcnamara added the question label Jan 25, 2016

jmcnamara added the under investigation label Feb 1, 2016

This was referenced Feb 16, 2017

Feature request: Add possibility to set x-axis for scatter as value axis in combined chart jmcnamara/XlsxWriter#415

Open

Allow ExcelWriter() to add sheets to existing workbook pandas-dev/pandas#3441

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Combine more than two charts? #157

Combine more than two charts? #157

rdboisvert commented Jan 25, 2016

jmcnamara commented Jan 25, 2016

rdboisvert commented Jan 25, 2016

jmcnamara commented Jan 25, 2016

rdboisvert commented Jan 25, 2016

rdboisvert commented Apr 27, 2016

jmcnamara commented Apr 27, 2016

rdboisvert commented Aug 17, 2016

jmcnamara commented Aug 17, 2016

rdboisvert commented Aug 18, 2016

jmcnamara commented Aug 18, 2016 •

edited

Loading

rdboisvert commented Aug 18, 2016

carsten-becker commented Oct 11, 2022

jmcnamara commented Oct 11, 2022

true-alex commented Nov 25, 2022

jmcnamara commented Nov 25, 2022 •

edited

Loading

Combine more than two charts? #157

Combine more than two charts? #157

Comments

rdboisvert commented Jan 25, 2016

jmcnamara commented Jan 25, 2016

rdboisvert commented Jan 25, 2016

jmcnamara commented Jan 25, 2016

rdboisvert commented Jan 25, 2016

rdboisvert commented Apr 27, 2016

jmcnamara commented Apr 27, 2016

rdboisvert commented Aug 17, 2016

jmcnamara commented Aug 17, 2016

rdboisvert commented Aug 18, 2016

jmcnamara commented Aug 18, 2016 • edited Loading

rdboisvert commented Aug 18, 2016

carsten-becker commented Oct 11, 2022

jmcnamara commented Oct 11, 2022

true-alex commented Nov 25, 2022

jmcnamara commented Nov 25, 2022 • edited Loading

jmcnamara commented Aug 18, 2016 •

edited

Loading

jmcnamara commented Nov 25, 2022 •

edited

Loading