subtype percentage stacked is not working

[kan-1]

i tried using the subtype percentage stacked for the column chart but it is not working it would simply create a cloumn chart.

[jmcnamara]

Can you give a small, complete, working example that demonstrates the issue.

[jmcnamara]

It looks like it is working okay:

use Excel::Writer::XLSX;

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

my $chart = $workbook->add_chart(
    type     => 'column',
    subtype  => 'percent_stacked',
    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( values => '=Sheet1!$A$1:$A$5' );
$chart->add_series( values => '=Sheet1!$B$1:$B$5' );
$chart->add_series( values => '=Sheet1!$C$1:$C$5' );

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

$workbook->close();

Output:

[jmcnamara]

@kan-1 Any update on this?

[kan-1]

!/usr/bin/perl

use strict https://metacpan.org/pod/strict; use warnings https://metacpan.org/pod/warnings; use Excel::Writer::XLSX https://metacpan.org/pod/Excel::Writer::XLSX;

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

my $headings = [ 'Number', 'Batch 1', 'Batch 2' ]; my $data = [ [ 2, 3, 4, 5, 6, 7 ], [ 10, 40, 50, 20, 10, 50 ], [ 30, 60, 70, 50, 40, 30 ],

];

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

my $chart = $workbook->add_chart( type => 'column', subtype =>'percentage_stacked', embedded => 1 );

$chart->add_series( name => '=Sheet1!$B$1', categories => '=Sheet1!$A$2:$A$7', values => '=Sheet1!$B$2:$B$7', );

$chart->add_series( name => '=Sheet1!$C$1', categories => [ 'Sheet1', 1, 6, 0, 0 ], values => [ 'Sheet1', 1, 6, 2, 2 ], );

$chart->set_title ( name => 'Results of sample analysis' ); $chart->set_x_axis( name => 'Test number' ); $chart->set_y_axis( name => 'Sample length (mm)' );

$chart->set_style( 11 ); $worksheet->insert_chart( 'D2', $chart, 25, 10 ); The above code generates an excel sheet but on opening that excel sheet Microsoft office shows an error saying " We found a problem with some content in chart_column.xlsx. DO you want us to try to recover as much as we can? If you trust the source of this workbook,click yes."

On clicking yes it wouldn't show the chart but excel sheet containing data is generated fine. On Tue, Jun 25, 2019, 4:42 PM John McNamara notifications@github.com wrote:

Any update on this?

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/jmcnamara/excel-writer-xlsx/issues/235?email_source=notifications&email_token=AMNYTYFHCAMOBI3IOF3LVCTP4H4S3A5CNFSM4H2ZO4N2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODYP4JSA#issuecomment-505398472, or mute the thread https://github.com/notifications/unsubscribe-auth/AMNYTYHZKRQ3K4BKUDLOJ5LP4H4S3ANCNFSM4H2ZO4NQ .

[jmcnamara]

You have a typo in your code. It should be percent_stacked instead of percentage_stacked.

I ran your example (with that change and a close()) and it opened without issue:

#!/usr/bin/perl

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

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

my $headings = [ 'Number', 'Batch 1', 'Batch 2' ];
my $data = [
    [ 2, 3, 4, 5, 6, 7 ],
    [ 10, 40, 50, 20, 10, 50 ],
    [ 30, 60, 70, 50, 40, 30 ],

];

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

my $chart = $workbook->add_chart( type => 'column', subtype
=>'percent_stacked', embedded => 1 );

$chart->add_series(
    name       => '=Sheet1!$B$1',
    categories => '=Sheet1!$A$2:$A$7',
    values     => '=Sheet1!$B$2:$B$7',
);

$chart->add_series(
    name       => '=Sheet1!$C$1',
    categories => [ 'Sheet1', 1, 6, 0, 0 ],
    values     => [ 'Sheet1', 1, 6, 2, 2 ],
);

$chart->set_title ( name => 'Results of sample analysis' );
$chart->set_x_axis( name => 'Test number' );
$chart->set_y_axis( name => 'Sample length (mm)' );

$chart->set_style( 11 );
$worksheet->insert_chart( 'D2', $chart, 25, 10 );

$workbook->close();

Output:

[kan-1]

Sir, With the changes that you suggested the excel sheet is opening fine but the percent_stacked graph is still not made. The output chart is a normal column chart Regards Kanishka

On Wed, Jun 26, 2019, 2:26 AM John McNamara notifications@github.com wrote:

You have a typo in your code. It should be percent_stacked instead of percentage_stacked.

I ran your example (with that change and a close()) and it opened without issue:

!/usr/bin/perl

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

my $headings = [ 'Number', 'Batch 1', 'Batch 2' ];my $data = [ [ 2, 3, 4, 5, 6, 7 ], [ 10, 40, 50, 20, 10, 50 ], [ 30, 60, 70, 50, 40, 30 ],

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

my $chart = $workbook->add_chart( type => 'column', subtype=>'percent_stacked', embedded => 1 );

$chart->add_series( name => '=Sheet1!$B$1', categories => '=Sheet1!$A$2:$A$7', values => '=Sheet1!$B$2:$B$7', );

$chart->add_series( name => '=Sheet1!$C$1', categories => [ 'Sheet1', 1, 6, 0, 0 ], values => [ 'Sheet1', 1, 6, 2, 2 ], );

$chart->set_title ( name => 'Results of sample analysis' );$chart->set_x_axis( name => 'Test number' );$chart->set_y_axis( name => 'Sample length (mm)' ); $chart->set_style( 11 );$worksheet->insert_chart( 'D2', $chart, 25, 10 ); $workbook->close();

Output:

[image: screenshot] https://user-images.githubusercontent.com/94267/60132764-15e82480-9794-11e9-897a-2855f22e3b3e.png

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/jmcnamara/excel-writer-xlsx/issues/235?email_source=notifications&email_token=AMNYTYA4VJV3BDVR25Q7Z4TP4KBARA5CNFSM4H2ZO4N2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODYRSIZY#issuecomment-505619559, or mute the thread https://github.com/notifications/unsubscribe-auth/AMNYTYCDLE3U2OJL46JMTLLP4KBARANCNFSM4H2ZO4NQ .

[jmcnamara]

• What version of Excel::Writer::XLSX are you using, and
• What version of Excel are you using?

[kan-1]

Ya i got it working thankyou very much! Also the Metacpan site have this typo -percentage by percent Regards Kanishka

On Wed, Jun 26, 2019, 2:29 PM John McNamara notifications@github.com wrote:

• What version of Excel::Writer::XLSX are you using, and
• What version of Excel are you using?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/jmcnamara/excel-writer-xlsx/issues/235?email_source=notifications&email_token=AMNYTYBSMRU5S7W2J73BFCLP4MVWFA5CNFSM4H2ZO4N2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODYS3CII#issuecomment-505786657, or mute the thread https://github.com/notifications/unsubscribe-auth/AMNYTYDWY33TA4TZ7SFEPZ3P4MVWFANCNFSM4H2ZO4NQ .

[kan-1]

Hey..! Can you please help me with one more case. I am creating an excel sheet and then converting the data to a chart...but i want the chart only and delete the excel sheet which was created as an intermediate step. Is there any way of doing it? Thankyou Kanishka

On Thu, Jun 27, 2019, 11:09 AM Kanishka Aggarwal < kanishkaaggarwal03@gmail.com> wrote:

Ya i got it working thankyou very much! Also the Metacpan site have this typo -percentage by percent Regards Kanishka

On Wed, Jun 26, 2019, 2:29 PM John McNamara notifications@github.com wrote:

• What version of Excel::Writer::XLSX are you using, and
• What version of Excel are you using?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/jmcnamara/excel-writer-xlsx/issues/235?email_source=notifications&email_token=AMNYTYBSMRU5S7W2J73BFCLP4MVWFA5CNFSM4H2ZO4N2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODYS3CII#issuecomment-505786657, or mute the thread https://github.com/notifications/unsubscribe-auth/AMNYTYDWY33TA4TZ7SFEPZ3P4MVWFANCNFSM4H2ZO4NQ .

[jmcnamara]

Ya i got it working thankyou very much!

Good. I'll close the issue.

Also the Metacpan site have this typo -percentage by percent

I couldn't find it but if you could point it out that would be helpful.

i want the chart only and delete the excel sheet which was created as an intermediate step. Is there any way of doing it?

No. As a workaround you could hide the worksheet with the data on it. https://metacpan.org/pod/Excel::Writer::XLSX#hide()

[kan-1]

Thabkyou so much for all you help. Also can you please tell me why does add_table() function shows the error "Can't locate object method "add_table" via package "Excel::Writer::XLSX::Worksheet"

On Thu, Jun 27, 2019, 2:29 PM John McNamara notifications@github.com wrote:

Closed #235 https://github.com/jmcnamara/excel-writer-xlsx/issues/235.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/jmcnamara/excel-writer-xlsx/issues/235?email_source=notifications&email_token=AMNYTYB7ASNY7P32DOCZ4MLP4R6N5A5CNFSM4H2ZO4N2YY3PNVWWK3TUL52HS4DFWZEXG43VMVCXMZLOORHG65DJMZUWGYLUNFXW5KTDN5WW2ZLOORPWSZGOSGSWW5Q#event-2443537270, or mute the thread https://github.com/notifications/unsubscribe-auth/AMNYTYADGI4WQ6Q74PSSMD3P4R6N5ANCNFSM4H2ZO4NQ .

[jmcnamara]

Also can you please tell me why does add_table() function shows the error

That usually means that you have an old version of E::W::X.