Excel-Writer-XLSX
view release on metacpan or search on metacpan
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
package Excel::Writer::XLSX::Chart;
###############################################################################
#
# Chart - A class for writing Excel Charts.
#
#
# Used in conjunction with Excel::Writer::XLSX.
#
# Copyright 2000-2025, John McNamara, jmcnamara@cpan.org
#
# SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#
# Documentation after __END__
#
# perltidy with the following options: -mbl=2 -pt=0 -nola
use 5.008002;
use strict;
use warnings;
use Carp;
use Excel::Writer::XLSX::Format;
use Excel::Writer::XLSX::Package::XMLwriter;
use Excel::Writer::XLSX::Utility qw(xl_cell_to_rowcol
xl_rowcol_to_cell
xl_col_to_name
xl_range_formula
quote_sheetname );
our @ISA = qw(Excel::Writer::XLSX::Package::XMLwriter);
our $VERSION = '1.15';
###############################################################################
#
# factory()
#
# Factory method for returning chart objects based on their class type.
#
sub factory {
my $current_class = shift;
my $chart_subclass = shift;
$chart_subclass = ucfirst lc $chart_subclass;
my $module = "Excel::Writer::XLSX::Chart::" . $chart_subclass;
eval "require $module";
# TODO. Need to re-raise this error from Workbook::add_chart().
die "Chart type '$chart_subclass' not supported in add_chart()\n" if $@;
my $fh = undef;
return $module->new( $fh, @_ );
}
###############################################################################
#
# new()
#
# Default constructor for sub-classes.
#
sub new {
my $class = shift;
my $fh = shift;
my $self = Excel::Writer::XLSX::Package::XMLwriter->new( $fh );
$self->{_subtype} = shift;
$self->{_sheet_type} = 0x0200;
$self->{_orientation} = 0x0;
$self->{_series} = [];
$self->{_embedded} = 0;
$self->{_id} = -1;
$self->{_series_index} = 0;
$self->{_style_id} = 2;
$self->{_axis_ids} = [];
$self->{_axis2_ids} = [];
$self->{_cat_has_num_fmt} = 0;
$self->{_requires_category} = 0;
$self->{_legend} = {};
$self->{_cat_axis_position} = 'b';
$self->{_val_axis_position} = 'l';
$self->{_formula_ids} = {};
$self->{_formula_data} = [];
$self->{_horiz_cat_axis} = 0;
$self->{_horiz_val_axis} = 1;
$self->{_protection} = 0;
$self->{_chartarea} = {};
$self->{_plotarea} = {};
$self->{_x_axis} = {};
$self->{_y_axis} = {};
$self->{_y2_axis} = {};
$self->{_x2_axis} = {};
$self->{_chart_name} = '';
$self->{_show_blanks} = 'gap';
$self->{_show_na_as_empty} = 0;
$self->{_show_hidden_data} = 0;
$self->{_show_crosses} = 1;
$self->{_width} = 480;
$self->{_height} = 288;
$self->{_x_scale} = 1;
$self->{_y_scale} = 1;
$self->{_x_offset} = 0;
$self->{_y_offset} = 0;
$self->{_table} = undef;
$self->{_smooth_allowed} = 0;
$self->{_cross_between} = 'between';
$self->{_date_category} = 0;
$self->{_already_inserted} = 0;
$self->{_combined} = undef;
$self->{_is_secondary} = 0;
$self->{_title} = {};
$self->{_label_positions} = {};
$self->{_label_position_default} = '';
bless $self, $class;
$self->_set_default_properties();
return $self;
}
###############################################################################
#
# _assemble_xml_file()
#
# Assemble and write the XML file.
#
sub _assemble_xml_file {
my $self = shift;
$self->xml_declaration();
# Write the c:chartSpace element.
$self->_write_chart_space();
# Write the c:lang element.
$self->_write_lang();
# Write the c:style element.
$self->_write_style();
# Write the c:protection element.
$self->_write_protection();
# Write the c:chart element.
$self->_write_chart();
# Write the c:spPr element for the chartarea formatting.
$self->_write_sp_pr( $self->{_chartarea} );
# Write the c:printSettings element.
$self->_write_print_settings() if $self->{_embedded};
# Close the worksheet tag.
$self->xml_end_tag( 'c:chartSpace' );
# Close the XML writer filehandle.
$self->xml_get_fh()->close();
}
###############################################################################
#
# Public methods.
#
###############################################################################
###############################################################################
#
# add_series()
#
# Add a series and it's properties to a chart.
#
sub add_series {
my $self = shift;
my %arg = @_;
# Check that the required input has been specified.
if ( !exists $arg{values} ) {
croak "Must specify 'values' in add_series()";
}
if ( $self->{_requires_category} && !exists $arg{categories} ) {
croak "Must specify 'categories' in add_series() for this chart type";
}
if ( @{ $self->{_series} } == 255 ) {
carp "The maximum number of series that can be added to an "
. "Excel Chart is 255";
return
}
# Convert aref params into a formula string.
my $values = $self->_aref_to_formula( $arg{values} );
my $categories = $self->_aref_to_formula( $arg{categories} );
# Switch name and name_formula parameters if required.
my ( $name, $name_formula ) =
$self->_process_names( $arg{name}, $arg{name_formula} );
# Get an id for the data equivalent to the range formula.
my $cat_id = $self->_get_data_id( $categories, $arg{categories_data} );
my $val_id = $self->_get_data_id( $values, $arg{values_data} );
my $name_id = $self->_get_data_id( $name_formula, $arg{name_data} );
# Set the line properties for the series.
my $line = $self->_get_line_properties( $arg{line} );
# Allow 'border' as a synonym for 'line' in bar/column style charts.
if ( $arg{border} ) {
$line = $self->_get_line_properties( $arg{border} );
}
# Set the fill properties for the series.
my $fill = $self->_get_fill_properties( $arg{fill} );
# Set the pattern properties for the series.
my $pattern = $self->_get_pattern_properties( $arg{pattern} );
# Set the gradient fill properties for the series.
my $gradient = $self->_get_gradient_properties( $arg{gradient} );
# Pattern fill overrides solid fill.
if ( $pattern ) {
$fill = undef;
}
# Gradient fill overrides solid and pattern fills.
if ( $gradient ) {
$pattern = undef;
$fill = undef;
}
# Set the marker properties for the series.
my $marker = $self->_get_marker_properties( $arg{marker} );
# Set the trendline properties for the series.
my $trendline = $self->_get_trendline_properties( $arg{trendline} );
# Set the line smooth property for the series.
my $smooth = $arg{smooth};
# Set the error bars properties for the series.
my $y_error_bars = $self->_get_error_bars_properties( $arg{y_error_bars} );
my $x_error_bars = $self->_get_error_bars_properties( $arg{x_error_bars} );
# Set the point properties for the series.
my $points = $self->_get_points_properties($arg{points});
# Set the labels properties for the series.
my $labels = $self->_get_labels_properties( $arg{data_labels} );
# Set the "invert if negative" fill property.
my $invert_if_neg = $arg{invert_if_negative};
my $inverted_color = $arg{invert_if_negative_color};
# Set the secondary axis properties.
my $x2_axis = $arg{x2_axis};
my $y2_axis = $arg{y2_axis};
# Store secondary status for combined charts.
if ($x2_axis || $y2_axis) {
$self->{_is_secondary} = 1;
}
# Set the gap for Bar/Column charts.
if ( defined $arg{gap} ) {
if ($y2_axis) {
$self->{_series_gap_2} = $arg{gap};
}
else {
$self->{_series_gap_1} = $arg{gap};
}
}
# Set the overlap for Bar/Column charts.
if ( defined $arg{overlap} ) {
if ($y2_axis) {
$self->{_series_overlap_2} = $arg{overlap};
}
else {
$self->{_series_overlap_1} = $arg{overlap};
}
}
# Add the user supplied data to the internal structures.
%arg = (
_values => $values,
_categories => $categories,
_name => $name,
_name_formula => $name_formula,
_name_id => $name_id,
_val_data_id => $val_id,
_cat_data_id => $cat_id,
_line => $line,
_fill => $fill,
_pattern => $pattern,
_gradient => $gradient,
_marker => $marker,
_trendline => $trendline,
_smooth => $smooth,
_labels => $labels,
_invert_if_neg => $invert_if_neg,
_inverted_color => $inverted_color,
_x2_axis => $x2_axis,
_y2_axis => $y2_axis,
_points => $points,
_error_bars =>
{ _x_error_bars => $x_error_bars, _y_error_bars => $y_error_bars },
);
push @{ $self->{_series} }, \%arg;
}
###############################################################################
#
# set_x_axis()
#
# Set the properties of the X-axis.
#
sub set_x_axis {
my $self = shift;
my $axis = $self->_convert_axis_args( $self->{_x_axis}, @_ );
$self->{_x_axis} = $axis;
}
###############################################################################
#
# set_y_axis()
#
# Set the properties of the Y-axis.
#
sub set_y_axis {
my $self = shift;
my $axis = $self->_convert_axis_args( $self->{_y_axis}, @_ );
$self->{_y_axis} = $axis;
}
###############################################################################
#
# set_x2_axis()
#
# Set the properties of the secondary X-axis.
#
sub set_x2_axis {
my $self = shift;
my $axis = $self->_convert_axis_args( $self->{_x2_axis}, @_ );
$self->{_x2_axis} = $axis;
}
###############################################################################
#
# set_y2_axis()
#
# Set the properties of the secondary Y-axis.
#
sub set_y2_axis {
my $self = shift;
my $axis = $self->_convert_axis_args( $self->{_y2_axis}, @_ );
$self->{_y2_axis} = $axis;
}
###############################################################################
#
# set_title()
#
# Set the properties of the chart title.
#
sub set_title {
my $self = shift;
my %arg = @_;
my $title = {};
my ( $name, $name_formula ) =
$self->_process_names( $arg{name}, $arg{name_formula} );
my $data_id = $self->_get_data_id( $name_formula, $arg{data} );
$title->{_name} = $name;
$title->{_formula} = $name_formula;
$title->{_data_id} = $data_id;
# Set the font properties if present.
$title->{_font} = $self->_convert_font_args( $arg{name_font} );
if ( $arg{font} ) {
$title->{_font} = $self->_convert_font_args( $arg{font} );
}
# Set the line properties.
$title->{_line} = $self->_get_line_properties( $arg{line} );
# Allow 'border' as a synonym for 'line'.
if ( $arg{border} ) {
$title->{_line} = $self->_get_line_properties( $arg{border} );
}
# Set the fill properties.
$title->{_fill} = $self->_get_fill_properties( $arg{fill} );
# Set the pattern properties.
$title->{_pattern} = $self->_get_pattern_properties( $arg{pattern} );
# Set the gradient fill properties.
$title->{_gradient} = $self->_get_gradient_properties( $arg{gradient} );
# Set the title layout.
$title->{_layout} = $self->_get_layout_properties( $arg{layout}, 1 );
# Set the title overlay option.
$title->{_overlay} = $arg{overlay};
# Set the no automatic title option.
$title->{_none} = $arg{none};
# Copy the title to the main chart object.
$self->{_title} = $title;
}
###############################################################################
#
# set_legend()
#
# Set the properties of the chart legend.
#
sub set_legend {
my $self = shift;
# Convert the user defined properties to internal properties.
$self->{_legend} = $self->_get_legend_properties( @_ );
}
###############################################################################
#
# set_plotarea()
#
# Set the properties of the chart plotarea.
#
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
# Map minor_gridlines properties.
if ( $arg{minor_gridlines} && $arg{minor_gridlines}->{visible} ) {
$axis->{_minor_gridlines} =
$self->_get_gridline_properties( $arg{minor_gridlines} );
}
# Convert the display units.
$axis->{_display_units} = $self->_get_display_units( $arg{display_units} );
if ( defined $arg{display_units_visible} ) {
$axis->{_display_units_visible} = $arg{display_units_visible};
}
else {
$axis->{_display_units_visible} = 1;
}
# Only use the first letter of bottom, top, left or right.
if ( defined $axis->{_position} ) {
$axis->{_position} = substr lc $axis->{_position}, 0, 1;
}
# Set the position for a category axis on or between the tick marks.
if ( defined $axis->{_position_axis} ) {
if ( $axis->{_position_axis} eq 'on_tick' ) {
$axis->{_position_axis} = 'midCat';
}
elsif ( $axis->{_position_axis} eq 'between' ) {
# Doesn't need to be modified.
}
else {
# Otherwise use the default value.
$axis->{_position_axis} = undef;
}
}
# Set the category axis as a date axis.
if ( $arg{date_axis} ) {
$self->{_date_category} = 1;
}
# Set the category axis as a text axis.
if ( $arg{text_axis} ) {
$self->{_date_category} = 0;
$axis->{_text_axis} = 1;
}
# Set the number font properties if present.
$axis->{_num_font} = $self->_convert_font_args( $arg{num_font} );
# Set the line properties for the axis.
$axis->{_line} = $self->_get_line_properties( $arg{line} );
# Set the fill properties for the axis.
$axis->{_fill} = $self->_get_fill_properties( $arg{fill} );
# Set the tick marker types.
$axis->{_minor_tick_mark} = $self->_get_tick_type($arg{minor_tick_mark});
$axis->{_major_tick_mark} = $self->_get_tick_type($arg{major_tick_mark});
# Set the axis title properties.
my ( $name, $name_formula ) =
$self->_process_names( $arg{name}, $arg{name_formula} );
my $data_id = $self->_get_data_id( $name_formula, $arg{data} );
$axis->{_title}{_name} = $name;
$axis->{_title}{_formula} = $name_formula;
$axis->{_title}{_data_id} = $data_id;
$axis->{_title}{_font} = $self->_convert_font_args( $arg{name_font} );
$axis->{_title}{_layout} = $self->_get_layout_properties( $arg{name_layout}, 1 );
# Set the format properties.
$axis->{_title}{_line} = $self->_get_line_properties( $arg{name_line} );
if ( $arg{name_border} ) {
$axis->{_title}{_line} = $self->_get_line_properties( $arg{name_border} );
}
$axis->{_title}{_fill} = $self->_get_fill_properties( $arg{name_fill} );
$axis->{_title}{_pattern} = $self->_get_pattern_properties( $arg{name_pattern} );
$axis->{_title}{_gradient} = $self->_get_gradient_properties( $arg{name_gradient} );
return $axis;
}
###############################################################################
#
# _convert_fonts_args()
#
# Convert user defined font values into private hash values.
#
sub _convert_font_args {
my $self = shift;
my $args = shift;
return unless $args;
my $font = {
_name => $args->{name},
_color => $args->{color},
_size => $args->{size},
_bold => $args->{bold},
_italic => $args->{italic},
_underline => $args->{underline},
_pitch_family => $args->{pitch_family},
_charset => $args->{charset},
_baseline => $args->{baseline} || 0,
_rotation => $args->{rotation},
};
# Convert font size units.
$font->{_size} *= 100 if $font->{_size};
# Convert rotation into 60,000ths of a degree.
if ( $font->{_rotation} ) {
$font->{_rotation} = 60_000 * int( $font->{_rotation} );
}
return $font;
}
###############################################################################
#
# _aref_to_formula()
#
# Convert and aref of row col values to a range formula.
#
sub _aref_to_formula {
my $self = shift;
my $data = shift;
# If it isn't an array ref it is probably a formula already.
return $data if !ref $data;
my $formula = xl_range_formula( @$data );
return $formula;
}
###############################################################################
#
# _process_names()
#
# Switch name and name_formula parameters if required.
#
sub _process_names {
my $self = shift;
my $name = shift;
my $name_formula = shift;
if ( defined $name ) {
if ( ref $name eq 'ARRAY' ) {
my $cell = xl_rowcol_to_cell( $name->[1], $name->[2], 1, 1 );
$name_formula = quote_sheetname( $name->[0] ) . '!' . $cell;
$name = '';
}
elsif ( $name =~ m/^=[^!]+!\$/ ) {
# Name looks like a formula, use it to set name_formula.
$name_formula = $name;
$name = '';
}
}
return ( $name, $name_formula );
}
###############################################################################
#
# _get_data_type()
#
# Find the overall type of the data associated with a series.
#
# TODO. Need to handle date type.
#
sub _get_data_type {
my $self = shift;
my $data = shift;
# Check for no data in the series.
return 'none' if !defined $data;
return 'none' if @$data == 0;
if (ref $data->[0] eq 'ARRAY') {
return 'multi_str'
}
# If the token isn't a number assume it is a string.
for my $token ( @$data ) {
next if !defined $token;
return 'str'
if $token !~ /^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/;
}
# The series data was all numeric.
return 'num';
}
###############################################################################
#
# _get_data_id()
#
# Assign an id to a each unique series formula or title/axis formula. Repeated
# formulas such as for categories get the same id. If the series or title
# has user specified data associated with it then that is also stored. This
# data is used to populate cached Excel data when creating a chart.
# If there is no user defined data then it will be populated by the parent
# workbook in Workbook::_add_chart_data()
#
sub _get_data_id {
my $self = shift;
my $formula = shift;
my $data = shift;
my $id;
# Ignore series without a range formula.
return unless $formula;
# Strip the leading '=' from the formula.
$formula =~ s/^=//;
# Store the data id in a hash keyed by the formula and store the data
# in a separate array with the same id.
if ( !exists $self->{_formula_ids}->{$formula} ) {
# Haven't seen this formula before.
$id = @{ $self->{_formula_data} };
push @{ $self->{_formula_data} }, $data;
$self->{_formula_ids}->{$formula} = $id;
}
else {
# Formula already seen. Return existing id.
$id = $self->{_formula_ids}->{$formula};
# Store user defined data if it isn't already there.
if ( !defined $self->{_formula_data}->[$id] ) {
$self->{_formula_data}->[$id] = $data;
}
}
return $id;
}
###############################################################################
#
# _get_color()
#
# Convert the user specified colour index or string to a rgb colour.
#
sub _get_color {
my $self = shift;
my $color = shift;
# Convert a HTML style #RRGGBB color.
if ( defined $color and $color =~ /^#[0-9a-fA-F]{6}$/ ) {
$color =~ s/^#//;
return uc $color;
}
my $index = &Excel::Writer::XLSX::Format::_get_color( $color );
# Set undefined colors to black.
if ( !$index ) {
$index = 0x08;
warn "Unknown color '$color' used in chart formatting. "
. "Converting to black.\n";
}
return $self->_get_palette_color( $index );
}
###############################################################################
#
# _get_color_or_undef()
#
# Convert the user specified colour index or string to a rgb colour.
#
sub _get_color_or_undef {
my $self = shift;
my $color = shift;
# Convert a HTML style #RRGGBB color.
if ( defined $color and $color =~ /^#[0-9a-fA-F]{6}$/ ) {
$color =~ s/^#//;
return uc $color;
}
my $index = &Excel::Writer::XLSX::Format::_get_color( $color );
if ( $index ) {
return $self->_get_palette_color( $index );
}
else {
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
if ( exists $separators{$separator} ) {
$labels->{separator} = $separators{$separator};
}
else {
carp "Unsupported label separator";
return undef
}
}
# Set the line properties for the data labels.
my $line = $self->_get_line_properties( $labels->{line} );
# Allow 'border' as a synonym for 'line'.
if ( $labels->{border} ) {
$line = $self->_get_line_properties( $labels->{border} );
}
# Set the fill properties for the labels.
my $fill = $self->_get_fill_properties( $labels->{fill} );
# Set the pattern properties for the labels.
my $pattern = $self->_get_pattern_properties( $labels->{pattern} );
# Set the gradient fill properties for the labels.
my $gradient = $self->_get_gradient_properties( $labels->{gradient} );
# Pattern fill overrides solid fill.
if ( $pattern ) {
$fill = undef;
}
# Gradient fill overrides solid and pattern fills.
if ( $gradient ) {
$pattern = undef;
$fill = undef;
}
$labels->{_line} = $line;
$labels->{_fill} = $fill;
$labels->{_pattern} = $pattern;
$labels->{_gradient} = $gradient;
if ($labels->{font}) {
$labels->{font} = $self->_convert_font_args( $labels->{font} );
}
if ( $labels->{custom} ) {
# Duplicate, and modify, the custom label properties.
my @custom = ();
for my $label ( @{ $labels->{custom} } ) {
if (! defined $label) {
push @custom, undef;
next;
}
my %property = %{$label};
# Convert formula.
if ( $property{value} && $property{value} =~ m/^=[^!]+!\$/) {
$property{formula} = $property{value};
}
if ( $property{formula} ) {
$property{formula} =~ s/^=//;
my $data_id = $self->_get_data_id( $property{formula},
$property{data} );
$property{data_id} = $data_id;
}
if ($property{font}) {
$property{font} = $self->_convert_font_args( $property{font} );
}
# Map user defined label positions to Excel positions.
if ( my $position = $property{position} ) {
if ( exists $self->{_label_positions}->{$position} ) {
if ( $position eq $self->{_label_position_default} ) {
$property{position} = undef;
}
else {
$property{position} = $self->{_label_positions}->{$position};
}
}
else {
carp "Unsupported label position '$position' for this chart type";
return undef;
}
}
# Set the line properties for the data labels.
my $line = $self->_get_line_properties( $property{line} );
# Allow 'border' as a synonym for 'line'.
if ( $property{border} ) {
$line = $self->_get_line_properties( $property{border} );
}
# Set the fill properties for the labels.
my $fill = $self->_get_fill_properties( $property{fill} );
# Set the pattern properties for the labels.
my $pattern = $self->_get_pattern_properties( $property{pattern} );
# Set the gradient fill properties for the labels.
my $gradient = $self->_get_gradient_properties( $property{gradient} );
# Pattern fill overrides solid fill.
if ( $pattern ) {
$fill = undef;
}
# Gradient fill overrides solid and pattern fills.
if ( $gradient ) {
$pattern = undef;
$fill = undef;
}
$property{_line} = $line;
$property{_fill} = $fill;
$property{_pattern} = $pattern;
$property{_gradient} = $gradient;
push @custom, \%property;
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
#
# Write the <c:style> element.
#
sub _write_style {
my $self = shift;
my $style_id = $self->{_style_id};
# Don't write an element for the default style, 2.
return if $style_id == 2;
my @attributes = ( 'val' => $style_id );
$self->xml_empty_tag( 'c:style', @attributes );
}
##############################################################################
#
# _write_chart()
#
# Write the <c:chart> element.
#
sub _write_chart {
my $self = shift;
$self->xml_start_tag( 'c:chart' );
# Write the chart title elements.
$self->_write_chart_title( $self->{_title} );
# Write the c:plotArea element.
$self->_write_plot_area();
# Write the c:legend element.
$self->_write_legend();
# Write the c:plotVisOnly element.
$self->_write_plot_vis_only();
# Write the c:dispBlanksAs element.
$self->_write_disp_blanks_as();
if ( $self->{_show_na_as_empty} ) {
# Write the c:extLst element.
$self->_write_ext_lst_display_na();
}
$self->xml_end_tag( 'c:chart' );
}
##############################################################################
#
# _write_chart_title()
#
# Write the <c:title> element. This applies to the main chart title and also
# axis titles.
#
# It differentiates between text, formula and formatting only titles
#
sub _write_chart_title {
my $self = shift;
my $title = shift;
my $is_y_axis = shift;
if ( $title->{_none} ) {
# Turn off the title. For main chart title only.
$self->_write_auto_title_deleted();
return
}
if ( $title->{_name} ) {
# Simple text title.
$self->_write_title_rich( $title, $is_y_axis );
}
elsif ( $title->{_formula} ) {
# Formula based title.
$self->_write_title_formula( $title, $is_y_axis );
}
elsif ( _has_formatting( $title ) ) {
# Formatting only title.
$self->_write_title_format_only( $title );
}
}
##############################################################################
#
# _write_disp_blanks_as()
#
# Write the <c:dispBlanksAs> element.
#
sub _write_disp_blanks_as {
my $self = shift;
my $val = $self->{_show_blanks};
# Ignore the default value.
return if $val eq 'gap';
my @attributes = ( 'val' => $val );
$self->xml_empty_tag( 'c:dispBlanksAs', @attributes );
}
##############################################################################
#
# _write_plot_area()
#
# Write the <c:plotArea> element.
#
sub _write_plot_area {
my $self = shift;
my $second_chart = $self->{_combined};
$self->xml_start_tag( 'c:plotArea' );
# Write the c:layout element.
$self->_write_layout( $self->{_plotarea}->{_layout}, 'plot' );
# Write the subclass chart type elements for primary and secondary axes.
$self->_write_chart_type( primary_axes => 1 );
$self->_write_chart_type( primary_axes => 0 );
# Configure a combined chart if present.
if ( $second_chart ) {
# Secondary axis has unique id otherwise use same as primary.
if ( $second_chart->{_is_secondary} ) {
$second_chart->{_id} = 1000 + $self->{_id};
}
else {
$second_chart->{_id} = $self->{_id};
}
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
);
my @attributes2 = ( 'val' => 1 );
$self->xml_start_tag( 'c:extLst' );
$self->xml_start_tag( 'c:ext', @attributes1 );
$self->xml_start_tag( 'c16r3:dataDisplayOptions16' );
$self->xml_empty_tag( 'c16r3:dispNaAsBlank', @attributes2 );
$self->xml_end_tag( 'c16r3:dataDisplayOptions16' );
$self->xml_end_tag( 'c:ext' );
$self->xml_end_tag( 'c:extLst' );
}
##############################################################################
#
# _write_idx()
#
# Write the <c:idx> element.
#
sub _write_idx {
my $self = shift;
my $val = shift;
my @attributes = ( 'val' => $val );
$self->xml_empty_tag( 'c:idx', @attributes );
}
##############################################################################
#
# _write_order()
#
# Write the <c:order> element.
#
sub _write_order {
my $self = shift;
my $val = shift;
my @attributes = ( 'val' => $val );
$self->xml_empty_tag( 'c:order', @attributes );
}
##############################################################################
#
# _write_series_name()
#
# Write the series name.
#
sub _write_series_name {
my $self = shift;
my $series = shift;
my $name;
if ( $name = $series->{_name_formula} ) {
$self->_write_tx_formula( $name, $series->{_name_id} );
}
elsif ( $name = $series->{_name} ) {
$self->_write_tx_value( $name );
}
}
##############################################################################
#
# _write_cat()
#
# Write the <c:cat> element.
#
sub _write_cat {
my $self = shift;
my $series = shift;
my $formula = $series->{_categories};
my $data_id = $series->{_cat_data_id};
my $data;
if ( defined $data_id ) {
$data = $self->{_formula_data}->[$data_id];
}
# Ignore <c:cat> elements for charts without category values.
return unless $formula;
$self->xml_start_tag( 'c:cat' );
# Check the type of cached data.
my $type = $self->_get_data_type( $data );
if ( $type eq 'str' ) {
$self->{_cat_has_num_fmt} = 0;
# Write the c:numRef element.
$self->_write_str_ref( $formula, $data, $type );
}
elsif ( $type eq 'multi_str') {
$self->{_cat_has_num_fmt} = 0;
# Write the c:multiLvLStrRef element.
$self->_write_multi_lvl_str_ref( $formula, $data );
}
else {
$self->{_cat_has_num_fmt} = 1;
# Write the c:numRef element.
$self->_write_num_ref( $formula, $data, $type );
}
$self->xml_end_tag( 'c:cat' );
}
##############################################################################
#
# _write_val()
#
# Write the <c:val> element.
#
sub _write_val {
my $self = shift;
my $series = shift;
my $formula = $series->{_values};
my $data_id = $series->{_val_data_id};
my $data = $self->{_formula_data}->[$data_id];
$self->xml_start_tag( 'c:val' );
# Unlike Cat axes data should only be numeric.
# Write the c:numRef element.
$self->_write_num_ref( $formula, $data, 'num' );
$self->xml_end_tag( 'c:val' );
}
##############################################################################
#
# _write_num_ref()
#
# Write the <c:numRef> element.
#
sub _write_num_ref {
my $self = shift;
my $formula = shift;
my $data = shift;
my $type = shift;
$self->xml_start_tag( 'c:numRef' );
# Write the c:f element.
$self->_write_series_formula( $formula );
if ( $type eq 'num' ) {
# Write the c:numCache element.
$self->_write_num_cache( $data );
}
elsif ( $type eq 'str' ) {
# Write the c:strCache element.
$self->_write_str_cache( $data );
}
$self->xml_end_tag( 'c:numRef' );
}
##############################################################################
#
# _write_str_ref()
#
# Write the <c:strRef> element.
#
sub _write_str_ref {
my $self = shift;
my $formula = shift;
my $data = shift;
my $type = shift;
$self->xml_start_tag( 'c:strRef' );
# Write the c:f element.
$self->_write_series_formula( $formula );
if ( $type eq 'num' ) {
# Write the c:numCache element.
$self->_write_num_cache( $data );
}
elsif ( $type eq 'str' ) {
# Write the c:strCache element.
$self->_write_str_cache( $data );
}
$self->xml_end_tag( 'c:strRef' );
}
##############################################################################
#
# _write_multi_lvl_str_ref()
#
# Write the <c:multiLvLStrRef> element.
#
sub _write_multi_lvl_str_ref {
my $self = shift;
my $formula = shift;
my $data = shift;
my $count = @$data;
return if !$count;
$self->xml_start_tag( 'c:multiLvlStrRef' );
# Write the c:f element.
$self->_write_series_formula( $formula );
$self->xml_start_tag( 'c:multiLvlStrCache' );
# Write the c:ptCount element.
$count = @{ $data->[-1] };
$self->_write_pt_count( $count );
# Write the data arrays in reverse order.
for my $aref ( reverse @$data ) {
$self->xml_start_tag( 'c:lvl' );
for my $i ( 0 .. @$aref - 1 ) {
# Write the c:pt element.
$self->_write_pt( $i, $aref->[$i] );
}
$self->xml_end_tag( 'c:lvl' );
}
$self->xml_end_tag( 'c:multiLvlStrCache' );
$self->xml_end_tag( 'c:multiLvlStrRef' );
}
##############################################################################
#
# _write_series_formula()
#
# Write the <c:f> element.
#
sub _write_series_formula {
my $self = shift;
my $formula = shift;
# Strip the leading '=' from the formula.
$formula =~ s/^=//;
$self->xml_data_element( 'c:f', $formula );
}
##############################################################################
#
# _write_axis_ids()
#
# Write the <c:axId> elements for the primary or secondary axes.
#
sub _write_axis_ids {
my $self = shift;
my %args = @_;
# Generate the axis ids.
$self->_add_axis_ids( %args );
if ( $args{primary_axes} ) {
# Write the axis ids for the primary axes.
$self->_write_axis_id( $self->{_axis_ids}->[0] );
$self->_write_axis_id( $self->{_axis_ids}->[1] );
}
else {
# Write the axis ids for the secondary axes.
$self->_write_axis_id( $self->{_axis2_ids}->[0] );
$self->_write_axis_id( $self->{_axis2_ids}->[1] );
}
}
##############################################################################
#
# _write_axis_id()
#
# Write the <c:axId> element.
#
sub _write_axis_id {
my $self = shift;
my $val = shift;
my @attributes = ( 'val' => $val );
$self->xml_empty_tag( 'c:axId', @attributes );
}
##############################################################################
#
# _write_cat_axis()
#
# Write the <c:catAx> element. Usually the X axis.
#
sub _write_cat_axis {
my $self = shift;
my %args = @_;
my $x_axis = $args{x_axis};
my $y_axis = $args{y_axis};
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
# _write_page_setup()
#
# Write the <c:pageSetup> element.
#
sub _write_page_setup {
my $self = shift;
$self->xml_empty_tag( 'c:pageSetup' );
}
##############################################################################
#
# _write_auto_title_deleted()
#
# Write the <c:autoTitleDeleted> element.
#
sub _write_auto_title_deleted {
my $self = shift;
my @attributes = ( 'val' => 1 );
$self->xml_empty_tag( 'c:autoTitleDeleted', @attributes );
}
##############################################################################
#
# _write_title_rich()
#
# Write the <c:title> element for a rich string.
#
sub _write_title_rich {
my $self = shift;
my $title = shift;
my $is_y_axis = shift;
$self->xml_start_tag( 'c:title' );
# Write the c:tx element.
$self->_write_tx_rich( $title->{_name}, $is_y_axis, $title->{_font} );
# Write the c:layout element.
$self->_write_layout( $title->{_layout}, 'text' );
# Write the c:overlay element.
$self->_write_overlay() if $title->{_overlay};
# Write the c:spPr element.
$self->_write_sp_pr( $title );
$self->xml_end_tag( 'c:title' );
}
##############################################################################
#
# _write_title_formula()
#
# Write the <c:title> element for a formulas
#
sub _write_title_formula {
my $self = shift;
my $title = shift;
my $is_y_axis = shift;
$self->xml_start_tag( 'c:title' );
# Write the c:tx element.
$self->_write_tx_formula( $title->{_formula}, $title->{_data_id} );
# Write the c:layout element.
$self->_write_layout( $title->{_layout}, 'text' );
# Write the c:overlay element.
$self->_write_overlay() if $title->{_overlay};
# Write the c:spPr element.
$self->_write_sp_pr( $title );
# Write the c:txPr element.
$self->_write_tx_pr( $title->{_font}, $is_y_axis );
$self->xml_end_tag( 'c:title' );
}
##############################################################################
#
# _write_title_format_only()
#
# Write the <c:title> for a title with formatting but not text change.
#
sub _write_title_format_only {
my $self = shift;
my $title = shift;
$self->xml_start_tag( 'c:title' );
# Write the c:layout element.
$self->_write_layout( $title->{_layout}, 'text' );
# Write the c:overlay element.
$self->_write_overlay() if $title->{_overlay};
# Write the c:spPr element.
$self->_write_sp_pr( $title );
$self->xml_end_tag( 'c:title' );
}
##############################################################################
#
# _write_tx_rich()
#
# Write the <c:tx> element.
#
sub _write_tx_rich {
my $self = shift;
my $title = shift;
my $is_y_axis = shift;
my $font = shift;
$self->xml_start_tag( 'c:tx' );
# Write the c:rich element.
$self->_write_rich( $title, $font, $is_y_axis );
$self->xml_end_tag( 'c:tx' );
}
##############################################################################
#
# _write_tx_value()
#
# Write the <c:tx> element with a simple value such as for series names.
#
sub _write_tx_value {
my $self = shift;
my $title = shift;
$self->xml_start_tag( 'c:tx' );
# Write the c:v element.
$self->_write_v( $title );
$self->xml_end_tag( 'c:tx' );
}
##############################################################################
#
# _write_tx_formula()
#
# Write the <c:tx> element.
#
sub _write_tx_formula {
my $self = shift;
my $title = shift;
my $data_id = shift;
my $data;
if ( defined $data_id ) {
$data = $self->{_formula_data}->[$data_id];
}
$self->xml_start_tag( 'c:tx' );
# Write the c:strRef element.
$self->_write_str_ref( $title, $data, 'str' );
$self->xml_end_tag( 'c:tx' );
}
##############################################################################
#
# _write_rich()
#
# Write the <c:rich> element.
#
sub _write_rich {
my $self = shift;
my $title = shift;
my $font = shift;
my $is_y_axis = shift;
my $ignore_rich_pr = shift;
my $rotation = undef;
if ( $font && exists $font->{_rotation} ) {
$rotation = $font->{_rotation};
}
$self->xml_start_tag( 'c:rich' );
# Write the a:bodyPr element.
$self->_write_a_body_pr( $rotation, $is_y_axis );
# Write the a:lstStyle element.
$self->_write_a_lst_style();
# Write the a:p element.
$self->_write_a_p_rich( $title, $font, $ignore_rich_pr );
$self->xml_end_tag( 'c:rich' );
}
##############################################################################
#
# _write_a_body_pr()
#
# Write the <a:bodyPr> element.
sub _write_a_body_pr {
my $self = shift;
my $rot = shift;
my $is_y_axis = shift;
my @attributes = ();
if ( !defined $rot && $is_y_axis ) {
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
}
elsif ($rot == 16_260_000) {
# 271 deg/East Asian vertical.
push @attributes, ( 'rot' => 0 );
push @attributes, ( 'vert' => 'eaVert' );
}
else {
push @attributes, ( 'rot' => $rot );
push @attributes, ( 'vert' => 'horz' );
}
}
$self->xml_empty_tag( 'a:bodyPr', @attributes );
}
##############################################################################
#
# _write_a_lst_style()
#
# Write the <a:lstStyle> element.
#
sub _write_a_lst_style {
my $self = shift;
$self->xml_empty_tag( 'a:lstStyle' );
}
##############################################################################
#
# _write_a_p_rich()
#
# Write the <a:p> element for rich string titles.
#
sub _write_a_p_rich {
my $self = shift;
my $title = shift;
my $font = shift;
my $ignore_rich_pr = shift;
$self->xml_start_tag( 'a:p' );
# Write the a:pPr element.
if ( !$ignore_rich_pr ) {
$self->_write_a_p_pr_rich( $font );
}
# Write the a:r element.
$self->_write_a_r( $title, $font );
$self->xml_end_tag( 'a:p' );
}
##############################################################################
#
# _write_a_p_formula()
#
# Write the <a:p> element for formula titles.
#
sub _write_a_p_formula {
my $self = shift;
my $font = shift;
$self->xml_start_tag( 'a:p' );
# Write the a:pPr element.
$self->_write_a_p_pr_formula( $font );
# Write the a:endParaRPr element.
$self->_write_a_end_para_rpr();
$self->xml_end_tag( 'a:p' );
}
##############################################################################
#
# _write_a_p_pr_rich()
#
# Write the <a:pPr> element for rich string titles.
#
sub _write_a_p_pr_rich {
my $self = shift;
my $font = shift;
$self->xml_start_tag( 'a:pPr' );
# Write the a:defRPr element.
$self->_write_a_def_rpr( $font );
$self->xml_end_tag( 'a:pPr' );
}
##############################################################################
#
# _write_a_p_pr_formula()
#
# Write the <a:pPr> element for formula titles.
#
sub _write_a_p_pr_formula {
my $self = shift;
my $font = shift;
$self->xml_start_tag( 'a:pPr' );
# Write the a:defRPr element.
$self->_write_a_def_rpr( $font );
$self->xml_end_tag( 'a:pPr' );
}
##############################################################################
#
# _write_a_def_rpr()
#
# Write the <a:defRPr> element.
#
sub _write_a_def_rpr {
my $self = shift;
my $font = shift;
my $has_color = 0;
my @style_attributes = $self->_get_font_style_attributes( $font );
my @latin_attributes = $self->_get_font_latin_attributes( $font );
$has_color = 1 if $font && $font->{_color};
if ( @latin_attributes || $has_color ) {
$self->xml_start_tag( 'a:defRPr', @style_attributes );
if ( $has_color ) {
$self->_write_a_solid_fill( { color => $font->{_color} } );
}
if ( @latin_attributes ) {
$self->_write_a_latin( @latin_attributes );
}
$self->xml_end_tag( 'a:defRPr' );
}
else {
$self->xml_empty_tag( 'a:defRPr', @style_attributes );
}
}
##############################################################################
#
# _write_a_end_para_rpr()
#
# Write the <a:endParaRPr> element.
#
sub _write_a_end_para_rpr {
my $self = shift;
my $lang = 'en-US';
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
if ( $has_color ) {
$self->_write_a_solid_fill( { color => $font->{_color} } );
}
if ( @latin_attributes ) {
$self->_write_a_latin( @latin_attributes );
}
$self->xml_end_tag( 'a:rPr' );
}
else {
$self->xml_empty_tag( 'a:rPr', @style_attributes );
}
}
##############################################################################
#
# _write_a_t()
#
# Write the <a:t> element.
#
sub _write_a_t {
my $self = shift;
my $title = shift;
$self->xml_data_element( 'a:t', $title );
}
##############################################################################
#
# _write_tx_pr()
#
# Write the <c:txPr> element.
#
sub _write_tx_pr {
my $self = shift;
my $font = shift;
my $is_y_axis = shift;
my $rotation = undef;
if ( $font && exists $font->{_rotation} ) {
$rotation = $font->{_rotation};
}
$self->xml_start_tag( 'c:txPr' );
# Write the a:bodyPr element.
$self->_write_a_body_pr( $rotation, $is_y_axis );
# Write the a:lstStyle element.
$self->_write_a_lst_style();
# Write the a:p element.
$self->_write_a_p_formula( $font );
$self->xml_end_tag( 'c:txPr' );
}
##############################################################################
#
# _write_marker()
#
# Write the <c:marker> element.
#
sub _write_marker {
my $self = shift;
my $marker = shift || $self->{_default_marker};
return unless $marker;
return if $marker->{automatic};
$self->xml_start_tag( 'c:marker' );
# Write the c:symbol element.
$self->_write_symbol( $marker->{type} );
# Write the c:size element.
my $size = $marker->{size};
$self->_write_marker_size( $size ) if $size;
# Write the c:spPr element.
$self->_write_sp_pr( $marker );
$self->xml_end_tag( 'c:marker' );
}
##############################################################################
#
# _write_marker_size()
#
# Write the <c:size> element.
#
sub _write_marker_size {
my $self = shift;
my $val = shift;
my @attributes = ( 'val' => $val );
$self->xml_empty_tag( 'c:size', @attributes );
}
##############################################################################
#
# _write_symbol()
#
# Write the <c:symbol> element.
#
sub _write_symbol {
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
$self->_write_axis_font( $labels->{font} );
}
# Write the c:dLblPos element.
$self->_write_d_lbl_pos( $labels->{position} ) if $labels->{position};
# Write the c:showLegendKey element.
$self->_write_show_legend_key() if $labels->{legend_key};
# Write the c:showVal element.
$self->_write_show_val() if $labels->{value};
# Write the c:showCatName element.
$self->_write_show_cat_name() if $labels->{category};
# Write the c:showSerName element.
$self->_write_show_ser_name() if $labels->{series_name};
# Write the c:showPercent element.
$self->_write_show_percent() if $labels->{percentage};
# Write the c:separator element.
$self->_write_separator($labels->{separator}) if $labels->{separator};
# Write the c:showLeaderLines element.
$self->_write_show_leader_lines() if $labels->{leader_lines};
$self->xml_end_tag( 'c:dLbls' );
}
##############################################################################
#
# _write_custom_labels()
#
# Write the <c:dLbl> element.
#
sub _write_custom_labels {
my $self = shift;
my $parent = shift;
my $labels = shift;
my $index = 0;
for my $label ( @$labels ) {
$index++;
next if !defined $label;
my $use_custom_formatting = 1;
$self->xml_start_tag( 'c:dLbl' );
# Write the c:idx element.
$self->_write_idx( $index - 1 );
if ( defined $label->{delete} && $label->{delete} ) {
# Delete/hide label.
$self->_write_delete( 1 );
}
elsif (defined $label->{formula}
|| defined $label->{value}
|| $label->{position} )
{
# Write the c:layout element.
$self->_write_layout();
if ( defined $label->{formula} ) {
$self->_write_custom_label_formula( $label );
}
elsif ( defined $label->{value} ) {
$self->_write_custom_label_str( $label );
# String values use spPr formatting.
$use_custom_formatting = 0;
}
if ( $use_custom_formatting ) {
$self->_write_custom_label_format( $label );
}
if ( my $position = $label->{position} || $parent->{position} ) {
$self->_write_d_lbl_pos( $position );
}
$self->_write_show_val() if $parent->{value};
$self->_write_show_cat_name() if $parent->{category};
$self->_write_show_ser_name() if $parent->{series_name};
}
else {
$self->_write_custom_label_format( $label );
}
$self->xml_end_tag( 'c:dLbl' );
}
}
##############################################################################
#
# _write_custom_label_str()
#
# Write parts of the <c:dLbl> element for strings.
#
sub _write_custom_label_str {
my $self = shift;
my $label = shift;
my $value = $label->{value};
my $font = $label->{font};
my $is_y_axis = 0;
my $has_formatting = _has_formatting($label);
$self->xml_start_tag( 'c:tx' );
# Write the c:rich element.
$self->_write_rich( $value, $font, $is_y_axis, !$has_formatting );
$self->xml_end_tag( 'c:tx' );
# Write the c:spPr element.
$self->_write_sp_pr( $label );
}
##############################################################################
#
# _write_custom_label_formula()
#
# Write parts of the <c:dLbl> element for formulas.
#
sub _write_custom_label_formula {
my $self = shift;
my $label = shift;
my $formula = $label->{formula};
my $data_id = $label->{data_id};
my $font = $label->{font};
my $has_formatting = _has_formatting($label);
my $data;
if ( defined $data_id ) {
$data = $self->{_formula_data}->[$data_id];
}
$self->xml_start_tag( 'c:tx' );
# Write the c:strRef element.
$self->_write_str_ref( $formula, $data, 'str' );
$self->xml_end_tag( 'c:tx' );
}
##############################################################################
#
# _write_custom_label_format()
#
# Write the formatting and font elements for the custom labels.
#
sub _write_custom_label_format {
my $self = shift;
my $label = shift;
my $font = $label->{font};
my $has_formatting = _has_formatting($label);
if ( $has_formatting ) {
# Write the c:spPr element.
$self->_write_sp_pr( $label );
$self->_write_tx_pr( $font );
}
elsif ( $font ) {
$self->xml_empty_tag( 'c:spPr' );
$self->_write_tx_pr( $font );
}
}
##############################################################################
#
# _write_show_legend_key()
#
# Write the <c:showLegendKey> element.
#
sub _write_show_legend_key {
my $self = shift;
my $val = 1;
my @attributes = ( 'val' => $val );
$self->xml_empty_tag( 'c:showLegendKey', @attributes );
}
##############################################################################
#
# _write_show_val()
#
# Write the <c:showVal> element.
#
sub _write_show_val {
my $self = shift;
my $val = 1;
my @attributes = ( 'val' => $val );
$self->xml_empty_tag( 'c:showVal', @attributes );
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
Creates a Radar style chart. See L<Excel::Writer::XLSX::Chart::Radar>.
=back
Chart subtypes are also supported in some cases:
$workbook->add_chart( type => 'bar', subtype => 'stacked' );
The currently available subtypes are:
area
stacked
percent_stacked
bar
stacked
percent_stacked
column
stacked
percent_stacked
scatter
straight_with_markers
straight
smooth_with_markers
smooth
line
stacked
percent_stacked
radar
with_markers
filled
=head1 CHART METHODS
Methods that are common to all chart types are documented below. See the documentation for each of the above chart modules for chart specific information.
=head2 add_series()
In an Excel chart a "series" is a collection of information such as values, X axis labels and the formatting that define which data is plotted.
With an Excel::Writer::XLSX chart object the C<add_series()> method is used to set the properties for a series:
$chart->add_series(
categories => '=Sheet1!$A$2:$A$10', # Optional.
values => '=Sheet1!$B$2:$B$10', # Required.
line => { color => 'blue' },
);
The properties that can be set are:
=over
=item * C<values>
This is the most important property of a series and must be set for every chart object. It links the chart with the worksheet data that it displays. A formula or array ref can be used for the data range, see below.
=item * C<categories>
This sets the chart category labels. The category is more or less the same as the X axis. In most chart types the C<categories> property is optional and the chart will just assume a sequential series from C<1 .. n>.
=item * C<name>
Set the name for the series. The name is displayed in the chart legend and in the formula bar. The name property is optional and if it isn't supplied it will default to C<Series 1 .. n>.
=item * C<line>
Set the properties of the series line type such as colour and width. See the L</CHART FORMATTING> section below.
=item * C<border>
Set the border properties of the series such as colour and style. See the L</CHART FORMATTING> section below.
=item * C<fill>
Set the fill properties of the series such as colour. See the L</CHART FORMATTING> section below.
=item * C<pattern>
Set the pattern properties of the series. See the L</CHART FORMATTING> section below.
=item * C<gradient>
Set the gradient properties of the series. See the L</CHART FORMATTING> section below.
=item * C<marker>
Set the properties of the series marker such as style and colour. See the L</SERIES OPTIONS> section below.
=item * C<trendline>
Set the properties of the series trendline such as linear, polynomial and moving average types. See the L</SERIES OPTIONS> section below.
=item * C<smooth>
The C<smooth> option is used to set the smooth property of a line series. See the L</SERIES OPTIONS> section below.
=item * C<y_error_bars>
Set vertical error bounds for a chart series. See the L</SERIES OPTIONS> section below.
=item * C<x_error_bars>
Set horizontal error bounds for a chart series. See the L</SERIES OPTIONS> section below.
=item * C<data_labels>
Set data labels for the series. See the L</SERIES OPTIONS> section below.
=item * C<points>
Set properties for individual points in a series. See the L</SERIES OPTIONS> section below.
=item * C<invert_if_negative>
Invert the fill colour for negative values. Usually only applicable to column and bar charts.
=item * C<overlap>
Set the overlap between series in a Bar/Column chart. The range is +/- 100. Default is 0.
overlap => 20,
Note, it is only necessary to apply this property to one series of the chart.
=item * C<gap>
Set the gap between series in a Bar/Column chart. The range is 0 to 500. Default is 150.
gap => 200,
Note, it is only necessary to apply this property to one series of the chart.
=back
The C<categories> and C<values> can take either a range formula such as C<=Sheet1!$A$2:$A$7> or, more usefully when generating the range programmatically, an array ref with zero indexed row/column values:
[ $sheetname, $row_start, $row_end, $col_start, $col_end ]
The following are equivalent:
$chart->add_series( categories => '=Sheet1!$A$2:$A$7' ); # Same as ...
$chart->add_series( categories => [ 'Sheet1', 1, 6, 0, 0 ] ); # Zero-indexed.
You can add more than one series to a chart. In fact, some chart types such as C<stock> require it. The series numbering and order in the Excel chart will be the same as the order in which they are added in Excel::Writer::XLSX.
# Add the first series.
$chart->add_series(
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$B$2:$B$7',
name => 'Test data series 1',
);
# Add another series. Same categories. Different range values.
$chart->add_series(
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$C$2:$C$7',
name => 'Test data series 2',
);
It is also possible to specify non-contiguous ranges:
$chart->add_series(
categories => '=(Sheet1!$A$1:$A$9,Sheet1!$A$14:$A$25)',
values => '=(Sheet1!$B$1:$B$9,Sheet1!$B$14:$B$25)',
);
=head2 set_x_axis()
The C<set_x_axis()> method is used to set properties of the X axis.
$chart->set_x_axis( name => 'Quarterly results' );
The properties that can be set are:
name
name_font
name_layout
name_line
name_fill
name_pattern
name_gradient
num_format
num_font
line
fill
pattern
gradient
min
max
minor_unit
major_unit
interval_unit
interval_tick
crossing
reverse
position_axis
log_base
label_position
major_gridlines
minor_gridlines
visible
date_axis
text_axis
minor_unit_type
major_unit_type
minor_tick_mark
major_tick_mark
display_units
display_units_visible
These are explained below. Some properties are only applicable to value or category axes, as indicated. See L<Value and Category Axes> for an explanation of Excel's distinction between the axis types.
=over
=item * C<name>
Set the name (title or caption) for the axis. The name is displayed below the X axis. The C<name> property is optional. The default is to have no axis name. (Applicable to category and value axes).
$chart->set_x_axis( name => 'Quarterly results' );
The name can also be a formula such as C<=Sheet1!$A$1>.
=item * C<name_font>
Set the font properties for the axis title. (Applicable to category and value axes).
$chart->set_x_axis( name_font => { name => 'Arial', size => 10 } );
=item * C<name_layout>
Set the C<(x, y)> position of the axis caption in chart relative units. (Applicable to category and value axes).
$chart->set_x_axis(
name => 'X axis',
name_layout => {
x => 0.34,
y => 0.85,
}
);
See the L</CHART LAYOUT> section below.
=item * C<num_font>
Set the font properties for the axis numbers. (Applicable to category and value axes).
$chart->set_x_axis( num_font => { bold => 1, italic => 1 } );
See the L</CHART FONTS> section below.
=item * C<num_format>
Set the number format for the axis. (Applicable to category and value axes).
$chart->set_x_axis( num_format => '#,##0.00' );
$chart->set_y_axis( num_format => '0.00%' );
The number format is similar to the Worksheet Cell Format C<num_format> apart from the fact that a format index cannot be used. The explicit format string must be used as shown above. See L<Excel::Writer::XLSX/set_num_format()> for more information.
=item * C<line>
Set the properties of the axis line/border type such as colour and width. See the L</CHART FORMATTING> section below.
$chart->set_x_axis( line => { none => 1 });
=item * C<fill>
Set the fill properties of the axis. See the L</CHART FORMATTING> section below. Note, in Excel the axis fill is applied to the area of the numbers of the axis and not to the area of the axis bounding box. That background is set from the chartarea fi...
=item * C<pattern>
Set the pattern properties of the axis. See the L</CHART FORMATTING> section below.
=item * C<gradient>
Set the gradient properties of the axis. See the L</CHART FORMATTING> section below.
=item * C<name_line>
Set the properties of the axis title/name line/border. See the L</CHART FORMATTING> section below.
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
The chart C<combine()> method is used to combine two charts of different
types, for example a column and line chart:
my $column_chart = $workbook->add_chart( type => 'column', embedded => 1 );
# Configure the data series for the primary chart.
$column_chart->add_series(...);
# Create a new column chart. This will use this as the secondary chart.
my $line_chart = $workbook->add_chart( type => 'line', embedded => 1 );
# Configure the data series for the secondary chart.
$line_chart->add_series(...);
# Combine the charts.
$column_chart->combine( $line_chart );
See L<Combined Charts> for more details.
=head2 set_size()
The C<set_size()> method is used to set the dimensions of the chart. The size properties that can be set are:
width
height
x_scale
y_scale
x_offset
y_offset
The C<width> and C<height> are in pixels. The default chart width is 480 pixels and the default height is 288 pixels. The size of the chart can be modified by setting the C<width> and C<height> or by setting the C<x_scale> and C<y_scale>:
$chart->set_size( width => 720, height => 576 );
# Same as:
$chart->set_size( x_scale => 1.5, y_scale => 2 );
The C<x_offset> and C<y_offset> position the top left corner of the chart in the cell that it is inserted into.
Note: the C<x_scale>, C<y_scale>, C<x_offset> and C<y_offset> parameters can also be set via the C<insert_chart()> method:
$worksheet->insert_chart( 'E2', $chart, { x_offset =>2, y_offset => 4,
x_scale => 1.5, y_scale => 2 } );
=head2 set_title()
The C<set_title()> method is used to set properties of the chart title.
$chart->set_title( name => 'Year End Results' );
The properties that can be set are:
=over
=item * C<name>
Set the name (title) for the chart. The name is displayed above the chart. The name can also be a formula such as C<=Sheet1!$A$1>. The name property is optional. The default is to have no chart title.
=item * C<font>
Set the font properties for the chart title. See the L</CHART FONTS> section below.
=item * C<fill>
Set the fill properties of the legend such as colour. See the L</CHART FORMATTING> section below.
=item * C<pattern>
Set the pattern fill properties of the legend. See the L</CHART FORMATTING> section below.
=item * C<gradient>
Set the gradient fill properties of the legend. See the L</CHART FORMATTING> section below.
=item * C<overlay>
Allow the title to be overlaid on the chart. Generally used with the layout property below.
=item * C<layout>
Set the C<(x, y)> position of the title in chart relative units:
$chart->set_title(
name => 'Title',
overlay => 1,
layout => {
x => 0.42,
y => 0.14,
}
);
See the L</CHART LAYOUT> section below.
=item * C<none>
By default Excel adds an automatic chart title to charts with a single series and a user defined series name. The C<none> option turns this default title off. It also turns off all other C<set_title()> options.
$chart->set_title( none => 1 );
=back
=head2 set_legend()
The C<set_legend()> method is used to set properties of the chart legend.
The properties that can be set are:
=over
=item * C<none>
The C<none> option turns off the chart legend. In Excel chart legends are on by default:
$chart->set_legend( none => 1 );
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
$chart->add_series(
values => '=Sheet1!$B$1:$B$5',
trendline => {
type => 'polynomial',
name => 'My trend name',
order => 2,
forward => 0.5,
backward => 0.5,
intercept => 1.5,
display_equation => 1,
display_r_squared => 1,
line => {
color => 'red',
width => 1,
dash_type => 'long_dash',
}
},
);
Trendlines cannot be added to series in a stacked chart or pie chart, radar chart, doughnut or (when implemented) to 3D, or surface charts.
=head2 Error Bars
Error bars can be added to a chart series to indicate error bounds in the data. The error bars can be vertical C<y_error_bars> (the most common type) or horizontal C<x_error_bars> (for Bar and Scatter charts only).
The following properties can be set for error bars in a chart series.
type
value (for all types except standard error and custom)
plus_values (for custom only)
minus_values (for custom only)
direction
end_style
line
The C<type> property sets the type of error bars in the series.
$chart->add_series(
values => '=Sheet1!$B$1:$B$5',
y_error_bars => { type => 'standard_error' },
);
The available error bars types are available:
fixed
percentage
standard_deviation
standard_error
custom
All error bar types, except for C<standard_error> and C<custom> must also have a value associated with it for the error bounds:
$chart->add_series(
values => '=Sheet1!$B$1:$B$5',
y_error_bars => {
type => 'percentage',
value => 5,
},
);
The C<custom> error bar type must specify C<plus_values> and C<minus_values> which should either by a C<Sheet1!$A$1:$A$5> type range formula or an arrayref of
values:
$chart->add_series(
categories => '=Sheet1!$A$1:$A$5',
values => '=Sheet1!$B$1:$B$5',
y_error_bars => {
type => 'custom',
plus_values => '=Sheet1!$C$1:$C$5',
minus_values => '=Sheet1!$D$1:$D$5',
},
);
# or
$chart->add_series(
categories => '=Sheet1!$A$1:$A$5',
values => '=Sheet1!$B$1:$B$5',
y_error_bars => {
type => 'custom',
plus_values => [1, 1, 1, 1, 1],
minus_values => [2, 2, 2, 2, 2],
},
);
Note, as in Excel the items in the C<minus_values> do not need to be negative.
The C<direction> property sets the direction of the error bars. It should be one of the following:
plus # Positive direction only.
minus # Negative direction only.
both # Plus and minus directions, The default.
The C<end_style> property sets the style of the error bar end cap. The options are 1 (the default) or 0 (for no end cap):
$chart->add_series(
values => '=Sheet1!$B$1:$B$5',
y_error_bars => {
type => 'fixed',
value => 2,
end_style => 0,
direction => 'minus'
},
);
=head2 Data Labels
Data labels can be added to a chart series to indicate the values of the plotted data points.
The following properties can be set for C<data_labels> formats in a chart.
value
category
series_name
position
percentage
leader_lines
separator
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
The C<border> property sets the border properties of the data labels such as colour and style. See the L</CHART FORMATTING> section below.
The C<fill> property sets the fill properties of the data labels such as colour. See the L</CHART FORMATTING> section below.
Example of setting data label formatting:
$chart->add_series(
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$B$2:$B$7',
data_labels => { value => 1,
border => {color => 'red'},
fill => {color => 'yellow'} },
);
The C<pattern> property sets the pattern properties of the data labels. See the L</CHART FORMATTING> section below.
The C<gradient> property sets the gradient properties of the data labels. See the L</CHART FORMATTING> section below.
The C<custom> property is used to set the properties of individual data labels, see below.
=head2 Custom Data Labels
The C<custom> property data label property is used to set the properties of individual data labels in a series. The most common use for this is to set custom text or number labels:
my $custom_labels = [
{ value => 'Jan' },
{ value => 'Feb' },
{ value => 'Mar' },
{ value => 'Apr' },
{ value => 'May' },
{ value => 'Jun' },
];
$chart->add_series(
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$B$2:$B$7',
data_labels => { value => 1, custom => $custom_labels },
);
As shown in the previous examples th C<custom> property should be a list of dicts. Any property dict that is set to C<undef> or not included in the list will be assigned the default data label value:
my $custom_labels = [
undef,
{ value => 'Feb' },
{ value => 'Mar' },
{ value => 'Apr' },
];
The property elements of the C<custom> lists should be dicts with the following allowable keys/sub-properties:
value
font
border
fill
pattern
gradient
delete
position
The C<value> property should be a string, number or formula string that refers to a cell from which the value will be taken:
$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' },
];
The C<font> property is used to set the font of the custom data label of a series (See the L</CHART FONTS> section below):
$custom_labels = [
{ value => '=Sheet1!$C$1', font => { color => 'red' } },
{ value => '=Sheet1!$C$2', font => { color => 'red' } },
{ value => '=Sheet1!$C$2', font => { color => 'red' } },
{ value => '=Sheet1!$C$4', font => { color => 'red' } },
{ value => '=Sheet1!$C$5', font => { color => 'red' } },
{ value => '=Sheet1!$C$6', font => { color => 'red' } },
];
The C<border> property sets the border properties of the data labels such as colour and style. See the L</CHART FORMATTING> section below.
The C<fill> property sets the fill properties of the data labels such as colour. See the L</CHART FORMATTING> section below.
Example of setting custom data label formatting:
$custom_labels = [
{ value => 'Jan', border => {color => 'blue'} },
{ value => 'Feb' },
{ value => 'Mar' },
{ value => 'Apr' },
{ value => 'May' },
{ value => 'Jun', fill => {color => 'green'} },
];
The C<pattern> property sets the pattern properties of the data labels. See the L</CHART FORMATTING> section below.
The C<gradient> property sets the gradient properties of the data labels. See the L</CHART FORMATTING> section below.
The C<delete> property can be used to delete labels in a series. This can be
useful if you want to highlight one or more cells in the series, for example
the maximum and the minimum:
$custom_labels = [
undef,
{ delete => 1 },
{ delete => 1 },
{ delete => 1 },
{ delete => 1 },
undef,
];
The C<position> property is used to position the custom data such as "center"
or "left" relative to the data point. See the explanation for the C<position>
property of series data labels above.
=head2 Points
( run in 1.030 second using v1.01-cache-2.11-cpan-39bf76dae61 )