Excel-Writer-XLSX
view release on metacpan or search on metacpan
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
A demo of a Line chart with a secondary axis in Excel::Writer::XLSX.
SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
=begin html
<p><center><img src="http://jmcnamara.github.io/excel-writer-xlsx/images/examples/chart_secondary_axis.jpg" width="640" height="420" alt="Output from chart_secondary_axis.pl" /></center></p>
=end html
Source code for this example:
#!/usr/bin/perl
#######################################################################
#
# A demo of a Line chart with a secondary axis in Excel::Writer::XLSX.
#
# Copyright 2000-2025, John McNamara, jmcnamara@cpan.org
#
# SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#
use strict;
use warnings;
use Excel::Writer::XLSX;
my $workbook = Excel::Writer::XLSX->new( 'chart_secondary_axis.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 = [ 'Aliens', 'Humans', ];
my $data = [
[ 2, 3, 4, 5, 6, 7 ],
[ 10, 40, 50, 20, 10, 50 ],
];
$worksheet->write( 'A1', $headings, $bold );
$worksheet->write( 'A2', $data );
# Create a new chart object. In this case an embedded chart.
my $chart = $workbook->add_chart( type => 'line', embedded => 1 );
# Configure a series with a secondary axis
$chart->add_series(
name => '=Sheet1!$A$1',
values => '=Sheet1!$A$2:$A$7',
y2_axis => 1,
);
$chart->add_series(
name => '=Sheet1!$B$1',
values => '=Sheet1!$B$2:$B$7',
);
$chart->set_legend( position => 'right' );
# Add a chart title and some axis labels.
$chart->set_title( name => 'Survey results' );
$chart->set_x_axis( name => 'Days', );
$chart->set_y_axis( name => 'Population', major_gridlines => { visible => 0 } );
$chart->set_y2_axis( name => 'Laser wounds' );
# Insert the chart into the worksheet (with an offset).
$worksheet->insert_chart( 'D2', $chart, { x_offset => 25, y_offset => 10 } );
$workbook->close();
__END__
Download this example: L<http://cpansearch.perl.org/src/JMCNAMARA/Excel-Writer-XLSX-1.15/examples/chart_secondary_axis.pl>
=head2 Example: chart_combined.pl
An example of a Combined chart in Excel::Writer::XLSX.
SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#!/usr/bin/perl
#######################################################################
#
# An example of a Combined chart in Excel::Writer::XLSX.
#
# Copyright 2000-2025, John McNamara, jmcnamara@cpan.org
#
# SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#
use strict;
use warnings;
use Excel::Writer::XLSX;
my $workbook = Excel::Writer::XLSX->new( 'chart_combined.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 = [ '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 );
#
# In the first example we will create a combined column and line chart.
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#!/usr/bin/perl
#######################################################################
#
# A demo of a Pareto chart in Excel::Writer::XLSX.
#
# Copyright 2000-2025, John McNamara, jmcnamara@cpan.org
#
# SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#
use strict;
use warnings;
use Excel::Writer::XLSX;
my $workbook = Excel::Writer::XLSX->new( 'chart_pareto.xlsx' );
my $worksheet = $workbook->add_worksheet();
# Formats used in the workbook.
my $bold = $workbook->add_format( bold => 1 );
my $percent_format = $workbook->add_format( num_format => '0.0%' );
# Widen the columns for visibility.
$worksheet->set_column( 'A:A', 15 );
$worksheet->set_column( 'B:C', 10 );
# Add the worksheet data that the charts will refer to.
my $headings = [ 'Reason', 'Number', 'Percentage' ];
my $reasons = [
'Traffic', 'Child care', 'Public Transport', 'Weather',
'Overslept', 'Emergency',
];
my $numbers = [ 60, 40, 20, 15, 10, 5 ];
my $percents = [ 0.44, 0.667, 0.8, 0.9, 0.967, 1 ];
$worksheet->write_row( 'A1', $headings, $bold );
$worksheet->write_col( 'A2', $reasons );
$worksheet->write_col( 'B2', $numbers );
$worksheet->write_col( 'C2', $percents, $percent_format );
# Create a new column chart. This will be the primary chart.
my $column_chart = $workbook->add_chart( type => 'column', embedded => 1 );
# Add a series.
$column_chart->add_series(
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$B$2:$B$7',
);
# Add a chart title.
$column_chart->set_title( name => 'Reasons for lateness' );
# Turn off the chart legend.
$column_chart->set_legend( position => 'none' );
# Set the title and scale of the Y axes. Note, the secondary axis is set from
# the primary chart.
$column_chart->set_y_axis(
name => 'Respondents (number)',
min => 0,
max => 120
);
$column_chart->set_y2_axis( max => 1 );
# Create a new line chart. This will be the secondary chart.
my $line_chart = $workbook->add_chart( type => 'line', embedded => 1 );
# Add a series, on the secondary axis.
$line_chart->add_series(
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$C$2:$C$7',
marker => { type => 'automatic' },
y2_axis => 1,
);
# Combine the charts.
$column_chart->combine( $line_chart );
# Insert the chart into the worksheet.
$worksheet->insert_chart( 'F2', $column_chart );
$workbook->close();
__END__
Download this example: L<http://cpansearch.perl.org/src/JMCNAMARA/Excel-Writer-XLSX-1.15/examples/chart_pareto.pl>
=head2 Example: chart_stock.pl
A demo of a Stock chart in Excel::Writer::XLSX.
SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
=begin html
<p><center><img src="http://jmcnamara.github.io/excel-writer-xlsx/images/examples/chart_stock.jpg" width="640" height="420" alt="Output from chart_stock.pl" /></center></p>
=end html
Source code for this example:
#!/usr/bin/perl
#######################################################################
#
# A demo of a Stock chart in Excel::Writer::XLSX.
#
# Copyright 2000-2025, John McNamara, jmcnamara@cpan.org
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
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 );
# Create a column chart with a data table.
my $chart1 = $workbook->add_chart( type => 'column', embedded => 1 );
# Configure the first series.
$chart1->add_series(
name => '=Sheet1!$B$1',
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$B$2:$B$7',
);
# Configure second series. Note alternative use of array ref to define
# ranges: [ $sheetname, $row_start, $row_end, $col_start, $col_end ].
$chart1->add_series(
name => '=Sheet1!$C$1',
categories => [ 'Sheet1', 1, 6, 0, 0 ],
values => [ 'Sheet1', 1, 6, 2, 2 ],
);
# Add a chart title and some axis labels.
$chart1->set_title( name => 'Chart with Data Table' );
$chart1->set_x_axis( name => 'Test number' );
$chart1->set_y_axis( name => 'Sample length (mm)' );
# Set a default data table on the X-Axis.
$chart1->set_table();
# Insert the chart into the worksheet (with an offset).
$worksheet->insert_chart( 'D2', $chart1, { x_offset => 25, y_offset => 10 } );
#
# Create a second chart.
#
my $chart2 = $workbook->add_chart( type => 'column', embedded => 1 );
# Configure the first series.
$chart2->add_series(
name => '=Sheet1!$B$1',
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$B$2:$B$7',
);
# Configure second series.
$chart2->add_series(
name => '=Sheet1!$C$1',
categories => [ 'Sheet1', 1, 6, 0, 0 ],
values => [ 'Sheet1', 1, 6, 2, 2 ],
);
# Add a chart title and some axis labels.
$chart2->set_title( name => 'Data Table with legend keys' );
$chart2->set_x_axis( name => 'Test number' );
$chart2->set_y_axis( name => 'Sample length (mm)' );
# Set a data table on the X-Axis with the legend keys showm.
$chart2->set_table( show_keys => 1 );
# Hide the chart legend since the keys are show on the data table.
$chart2->set_legend( position => 'none' );
# Insert the chart into the worksheet (with an offset).
$worksheet->insert_chart( 'D18', $chart2, { x_offset => 25, y_offset => 10 } );
$workbook->close();
__END__
Download this example: L<http://cpansearch.perl.org/src/JMCNAMARA/Excel-Writer-XLSX-1.15/examples/chart_data_table.pl>
=head2 Example: chart_data_tools.pl
A demo of an various Excel chart data tools that are available via
an Excel::Writer::XLSX chart.
These include, Trendlines, Data Labels, Error Bars, Drop Lines,
High-Low Lines and Up-Down Bars.
SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
=begin html
<p><center><img src="http://jmcnamara.github.io/excel-writer-xlsx/images/examples/chart_data_tools.jpg" width="640" height="420" alt="Output from chart_data_tools.pl" /></center></p>
=end html
Source code for this example:
#!/usr/bin/perl
#######################################################################
#
# A demo of an various Excel chart data tools that are available via
# an Excel::Writer::XLSX chart.
#
# These include, Trendlines, Data Labels, Error Bars, Drop Lines,
# High-Low Lines and Up-Down Bars.
#
# Copyright 2000-2025, John McNamara, jmcnamara@cpan.org
#
# SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#
use strict;
use warnings;
use Excel::Writer::XLSX;
my $workbook = Excel::Writer::XLSX->new( 'chart_data_tools.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 = [ 'Number', 'Data 1', 'Data 2' ];
my $data = [
[ 2, 3, 4, 5, 6, 7 ],
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
A demo of an various Excel chart data label features that are available
via an Excel::Writer::XLSX chart.
SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#!/usr/bin/perl
#######################################################################
#
# A demo of an various Excel chart data label features that are available
# via an Excel::Writer::XLSX chart.
#
# Copyright 2000-2025, John McNamara, jmcnamara@cpan.org
#
# SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#
use strict;
use warnings;
use Excel::Writer::XLSX;
my $workbook = Excel::Writer::XLSX->new( 'chart_data_labels.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 = [ 'Number', 'Data', 'Text' ];
my $data = [
[ 2, 3, 4, 5, 6, 7 ],
[ 20, 10, 20, 30, 40, 30 ],
[ 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun' ],
];
$worksheet->write( 'A1', $headings, $bold );
$worksheet->write( 'A2', $data );
#######################################################################
#
# Example with standard data labels.
#
# Create a Column chart.
my $chart1 = $workbook->add_chart( type => 'column', embedded => 1 );
# Configure the data series and add the data labels.
$chart1->add_series(
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$B$2:$B$7',
data_labels => { value => 1 },
);
# Add a chart title.
$chart1->set_title( name => 'Chart with standard data labels' );
# Turn off the chart legend.
$chart1->set_legend( none => 1 );
# Insert the chart into the worksheet (with an offset).
$worksheet->insert_chart( 'D2', $chart1, { x_offset => 25, y_offset => 10 } );
#######################################################################
#
# Example with value and category data labels.
#
# Create a Column chart.
my $chart2 = $workbook->add_chart( type => 'column', embedded => 1 );
# Configure the data series and add the data labels.
$chart2->add_series(
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$B$2:$B$7',
data_labels => { value => 1, category => 1 },
);
# Add a chart title.
$chart2->set_title( name => 'Category and Value data labels' );
# Turn off the chart legend.
$chart2->set_legend( none => 1 );
# Insert the chart into the worksheet (with an offset).
$worksheet->insert_chart( 'D18', $chart2, { x_offset => 25, y_offset => 10 } );
#######################################################################
#
# Example with standard data labels with different font.
#
# Create a Column chart.
my $chart3 = $workbook->add_chart( type => 'column', embedded => 1 );
# Configure the data series and add the data labels.
$chart3->add_series(
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$B$2:$B$7',
data_labels => { value => 1,
font => { bold => 1,
color => 'red',
rotation => -30} },
);
# Add a chart title.
$chart3->set_title( name => 'Data labels with user defined font' );
# Turn off the chart legend.
$chart3->set_legend( none => 1 );
# Insert the chart into the worksheet (with an offset).
$worksheet->insert_chart( 'D34', $chart3, { x_offset => 25, y_offset => 10 } );
#######################################################################
#
# Example with standard data labels and formatting.
#
# Create a Column chart.
my $chart4 = $workbook->add_chart( type => 'column', embedded => 1 );
# Configure the data series and add the data labels.
$chart4->add_series(
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$B$2:$B$7',
data_labels => { value => 1,
border => {color => 'red'},
fill => {color => 'yellow'} },
);
# Add a chart title.
$chart4->set_title( name => 'Data labels with formatting' );
# Turn off the chart legend.
$chart4->set_legend( none => 1 );
# Insert the chart into the worksheet (with an offset).
$worksheet->insert_chart( 'D50', $chart4, { x_offset => 25, y_offset => 10 } );
#######################################################################
#
# Example with custom string data labels.
#
# Create a Column chart.
my $chart5 = $workbook->add_chart( type => 'column', embedded => 1 );
# Some custom labels.
my $custom_labels = [
{ value => 'Amy' },
{ value => 'Bea' },
{ value => 'Eva' },
{ value => 'Fay' },
{ value => 'Liv' },
{ value => 'Una' },
];
# Configure the data series and add the data labels.
$chart5->add_series(
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$B$2:$B$7',
data_labels => { value => 1, custom => $custom_labels },
);
# Add a chart title.
$chart5->set_title( name => 'Chart with custom string data labels' );
# Turn off the chart legend.
$chart5->set_legend( none => 1 );
# Insert the chart into the worksheet (with an offset).
$worksheet->insert_chart( 'D66', $chart5, { x_offset => 25, y_offset => 10 } );
#######################################################################
#
# Example with custom data labels from cells.
#
# Create a Column chart.
my $chart6 = $workbook->add_chart( type => 'column', embedded => 1 );
# Some custom labels.
$custom_labels = [
{ value => '=Sheet1!$C$2' },
{ value => '=Sheet1!$C$3' },
{ value => '=Sheet1!$C$4' },
{ value => '=Sheet1!$C$5' },
{ value => '=Sheet1!$C$6' },
{ value => '=Sheet1!$C$7' },
];
# Configure the data series and add the data labels.
$chart6->add_series(
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$B$2:$B$7',
data_labels => { value => 1, custom => $custom_labels },
);
# Add a chart title.
$chart6->set_title( name => 'Chart with custom data labels from cells' );
# Turn off the chart legend.
$chart6->set_legend( none => 1 );
# Insert the chart into the worksheet (with an offset).
$worksheet->insert_chart( 'D82', $chart6, { x_offset => 25, y_offset => 10 } );
#######################################################################
#
# Example with custom and default data labels.
#
# Create a Column chart.
my $chart7 = $workbook->add_chart( type => 'column', embedded => 1 );
# Some custom labels. The undef items will get the default value.
# We also set a font for the custom items as an extra example.
$custom_labels = [
{ value => '=Sheet1!$C$2', font => { color => 'red' } },
undef,
{ value => '=Sheet1!$C$4', font => { color => 'red' } },
{ value => '=Sheet1!$C$5', font => { color => 'red' } },
];
# Configure the data series and add the data labels.
$chart7->add_series(
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$B$2:$B$7',
data_labels => { value => 1, custom => $custom_labels },
);
# Add a chart title.
$chart7->set_title( name => 'Mixed custom and default data labels' );
# Turn off the chart legend.
$chart7->set_legend( none => 1 );
# Insert the chart into the worksheet (with an offset).
$worksheet->insert_chart( 'D98', $chart7, { x_offset => 25, y_offset => 10 } );
#######################################################################
#
# Example with deleted custom data labels.
#
# Create a Column chart.
my $chart8 = $workbook->add_chart( type => 'column', embedded => 1 );
# Some deleted custom labels and defaults (undef). This allows us to
# highlight certain values such as the minimum and maximum.
$custom_labels = [
{ delete => 1 },
undef,
{ delete => 1 },
{ delete => 1 },
undef,
{ delete => 1 },
];
# Configure the data series and add the data labels.
$chart8->add_series(
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$B$2:$B$7',
data_labels => { value => 1, custom => $custom_labels },
);
# Add a chart title.
$chart8->set_title( name => 'Chart with deleted data labels' );
# Turn off the chart legend.
$chart8->set_legend( none => 1 );
# Insert the chart into the worksheet (with an offset).
$worksheet->insert_chart( 'D114', $chart8, { x_offset => 25, y_offset => 10 } );
#######################################################################
#
# Example with custom string data labels and formatting.
#
# Create a Column chart.
my $chart9 = $workbook->add_chart( type => 'column', embedded => 1 );
# Some custom labels.
$custom_labels = [
{ value => 'Amy', border => {color => 'blue'} },
{ value => 'Bea' },
{ value => 'Eva' },
{ value => 'Fay' },
{ value => 'Liv' },
{ value => 'Una', fill => {color => 'green'} },
];
# Configure the data series and add the data labels.
$chart9->add_series(
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$B$2:$B$7',
data_labels => { value => 1,
custom => $custom_labels,
border => {color => 'red'},
fill => {color => 'yellow'} },
);
# Add a chart title.
$chart9->set_title( name => 'Chart with custom labels and formatting' );
# Turn off the chart legend.
$chart9->set_legend( none => 1 );
# Insert the chart into the worksheet (with an offset).
$worksheet->insert_chart( 'D130', $chart9, { x_offset => 25, y_offset => 10 } );
$workbook->close();
__END__
Download this example: L<http://cpansearch.perl.org/src/JMCNAMARA/Excel-Writer-XLSX-1.15/examples/chart_data_labels.pl>
=head2 Example: chart_clustered.pl
A demo of a clustered category chart in Excel::Writer::XLSX.
SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#!/usr/bin/perl
#######################################################################
#
# A demo of a clustered category chart in Excel::Writer::XLSX.
#
# Copyright 2000-2025, John McNamara, jmcnamara@cpan.org
#
# SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#
use strict;
use warnings;
use Excel::Writer::XLSX;
my $workbook = Excel::Writer::XLSX->new( 'chart_clustered.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 = [ 'Types', 'Sub Type', 'Value 1', 'Value 2', 'Value 3' ];
my $data = [
[ 'Type 1', 'Sub Type A', 5000, 8000, 6000 ],
[ '', 'Sub Type B', 2000, 3000, 4000 ],
[ '', 'Sub Type C', 250, 1000, 2000 ],
[ 'Type 2', 'Sub Type D', 6000, 6000, 6500 ],
[ '', 'Sub Type E', 500, 300, 200 ],
];
$worksheet->write( 'A1', $headings, $bold );
$worksheet->write_col( 'A2', $data );
# Create a new chart object. In this case an embedded chart.
my $chart = $workbook->add_chart( type => 'column', embedded => 1 );
# Configure the series. Note, that the categories are 2D ranges (from column A
# to column B). This creates the clusters. The series are shown as formula
# strings for clarity but you can also use the array syntax. See the docs.
$chart->add_series(
name => '=Sheet1!$C$1',
categories => '=Sheet1!$A$2:$B$6',
values => '=Sheet1!$C$2:$C$6',
);
$chart->add_series(
name => '=Sheet1!$D$1',
categories => '=Sheet1!$A$2:$B$6',
values => '=Sheet1!$D$2:$D$6',
);
$chart->add_series(
name => '=Sheet1!$E$1',
categories => '=Sheet1!$A$2:$B$6',
values => '=Sheet1!$E$2:$E$6',
);
# Set the Excel chart style.
$chart->set_style( 37 );
# Turn off the legend.
$chart->set_legend( position => 'none' );
# Insert the chart into the worksheet.
$worksheet->insert_chart( 'G3', $chart );
$workbook->close();
__END__
Download this example: L<http://cpansearch.perl.org/src/JMCNAMARA/Excel-Writer-XLSX-1.15/examples/chart_clustered.pl>
=head2 Example: chart_styles.pl
An example showing all 48 default chart styles available in Excel 2007
using Excel::Writer::XLSX.. Note, these styles are not the same as the
styles available in Excel 2013.
SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#!/usr/bin/perl
#######################################################################
#
# An example showing all 48 default chart styles available in Excel 2007
# using Excel::Writer::XLSX.. Note, these styles are not the same as the
# styles available in Excel 2013.
#
# Copyright 2000-2025, John McNamara, jmcnamara@cpan.org
#
# SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#
use strict;
use warnings;
use Excel::Writer::XLSX;
my $workbook = Excel::Writer::XLSX->new( 'chart_styles.xlsx' );
# Show the styles for all of these chart types.
my @chart_types = ( 'column', 'area', 'line', 'pie' );
for my $chart_type ( @chart_types ) {
# Add a worksheet for each chart type.
my $worksheet = $workbook->add_worksheet( ucfirst( $chart_type ) );
$worksheet->set_zoom( 30 );
my $style_number = 1;
# Create 48 charts, each with a different style.
for ( my $row_num = 0 ; $row_num < 90 ; $row_num += 15 ) {
for ( my $col_num = 0 ; $col_num < 64 ; $col_num += 8 ) {
my $chart = $workbook->add_chart(
type => $chart_type,
embedded => 1
);
$chart->add_series( values => '=Data!$A$1:$A$6' );
$chart->set_title( name => 'Style ' . $style_number );
$chart->set_legend( none => 1 );
$chart->set_style( $style_number );
$worksheet->insert_chart( $row_num, $col_num, $chart );
$style_number++;
}
}
}
# Create a worksheet with data for the charts.
my $data = [ 10, 40, 50, 20, 10, 50 ];
my $data_worksheet = $workbook->add_worksheet( 'Data' );
$data_worksheet->write_col( 'A1', $data );
$data_worksheet->hide();
$workbook->close();
__END__
Download this example: L<http://cpansearch.perl.org/src/JMCNAMARA/Excel-Writer-XLSX-1.15/examples/chart_styles.pl>
=head2 Example: chart_gauge.pl
A demo of an Gauge Chart in Excel::Writer::XLSX.
A Gauge Chart isn't a native chart type in Excel. It is constructed by
combining a doughnut chart and a pie chart and by using some non-filled
elements. This example follows the following online example of how to create
a Gauge Chart in Excel: https://www.excel-easy.com/examples/gauge-chart.html
SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#!/usr/bin/perl
#######################################################################
#
# A demo of an Gauge Chart in Excel::Writer::XLSX.
#
# A Gauge Chart isn't a native chart type in Excel. It is constructed by
# combining a doughnut chart and a pie chart and by using some non-filled
# elements. This example follows the following online example of how to create
# a Gauge Chart in Excel: https://www.excel-easy.com/examples/gauge-chart.html
#
# Copyright 2000-2025, John McNamara, jmcnamara@cpan.org
#
# SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#
use strict;
use warnings;
use Excel::Writer::XLSX;
my $workbook = Excel::Writer::XLSX->new( 'chart_gauge.xlsx' );
my $worksheet = $workbook->add_worksheet();
my $chart_doughnut = $workbook->add_chart( type => 'doughnut', embedded => 1 );
my $chart_pie = $workbook->add_chart( type => 'pie', embedded => 1 );
# Add some data for the Doughnut and Pie charts. This is set up so the
# gauge goes from 0-100. It is initially set at 75%.
$worksheet->write_col( 'H2', ['Donut', 25, 50, 25, 100] );
$worksheet->write_col( 'I2', ['Pie', 75, 1, '=200-I4-I3'] );
# Configure the doughnut chart as the background for the gauge.
$chart_doughnut->add_series(
name => '=Sheet1!$H$2',
values => '=Sheet1!$H$3:$H$6',
points => [
{ fill => { color => 'green' } },
{ fill => { color => 'yellow' } },
{ fill => { color => 'red' } },
{ fill => { none => 1 } },
],
);
# Rotate chart so the gauge parts are above the horizontal.
$chart_doughnut->set_rotation( 270 );
# Turn off the chart legend.
$chart_doughnut->set_legend( none => 1 );
# Turn off the chart fill and border.
$chart_doughnut->set_chartarea(
border => { none => 1 },
fill => { none => 1 },
);
# Configure the pie chart as the needle for the gauge.
$chart_pie->add_series(
name => '=Sheet1!$I$2',
values => '=Sheet1!$I$3:$I$6',
points => [
{ fill => { none => 1 } },
{ fill => { color => 'black' } },
{ fill => { none => 1 } },
],
);
# Rotate the pie chart/needle to align with the doughnut/gauge.
$chart_pie->set_rotation( 270 );
# Combine the pie and doughnut charts.
$chart_doughnut->combine($chart_pie);
# Insert the chart into the worksheet.
$worksheet->insert_chart( 'A1', $chart_doughnut );
$workbook->close();
Download this example: L<http://cpansearch.perl.org/src/JMCNAMARA/Excel-Writer-XLSX-1.15/examples/chart_gauge.pl>
=head2 Example: colors.pl
Demonstrates Excel::Writer::XLSX's named colours and the Excel colour
palette.
The set_custom_color() Worksheet method can be used to override one of the
built-in palette values with a more suitable colour. See the main docs.
SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
=begin html
<p><center><img src="http://jmcnamara.github.io/excel-writer-xlsx/images/examples/colors.jpg" width="640" height="420" alt="Output from colors.pl" /></center></p>
=end html
Source code for this example:
#!/usr/bin/perl -w
################################################################################
#
# Demonstrates Excel::Writer::XLSX's named colours and the Excel colour
# palette.
( run in 1.832 second using v1.01-cache-2.11-cpan-39bf76dae61 )