Excel-Writer-XLSX
view release on metacpan or search on metacpan
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
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();
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
###############################################################################
#
# 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.
#
sub set_plotarea {
my $self = shift;
# Convert the user defined properties to internal properties.
$self->{_plotarea} = $self->_get_area_properties( @_ );
}
###############################################################################
#
# set_chartarea()
#
# Set the properties of the chart chartarea.
#
sub set_chartarea {
my $self = shift;
# Convert the user defined properties to internal properties.
$self->{_chartarea} = $self->_get_area_properties( @_ );
}
###############################################################################
#
# set_style()
#
# Set on of the 48 built-in Excel chart styles. The default style is 2.
#
sub set_style {
my $self = shift;
my $style_id = defined $_[0] ? $_[0] : 2;
if ( $style_id < 1 || $style_id > 48 ) {
$style_id = 2;
}
$self->{_style_id} = $style_id;
}
###############################################################################
#
# show_blanks_as()
#
# Set the option for displaying blank data in a chart. The default is 'gap'.
#
sub show_blanks_as {
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
if ( $pattern eq 'none' ) {
$arg{border}->{none} = 1;
}
else {
$arg{border}->{dash_type} = $pattern;
}
}
# Map deprecated Spreadsheet::WriteExcel line colour.
if ( $arg{line_color} ) {
$arg{border}->{color} = $arg{line_color};
}
# Handle Excel::Writer::XLSX style properties.
# Set the line properties for the chartarea.
my $line = $self->_get_line_properties( $arg{line} );
# Allow 'border' as a synonym for 'line'.
if ( $arg{border} ) {
$line = $self->_get_line_properties( $arg{border} );
}
# Set the fill properties for the chartarea.
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 plotarea layout.
my $layout = $self->_get_layout_properties( $arg{layout} );
$area->{_line} = $line;
$area->{_fill} = $fill;
$area->{_pattern} = $pattern;
$area->{_gradient} = $gradient;
$area->{_layout} = $layout;
return $area;
}
###############################################################################
#
# _get_legend_properties()
#
# Convert user defined legend properties to the structure required internally.
#
sub _get_legend_properties {
my $self = shift;
my %arg = @_;
my $legend = {};
$legend->{_position} = $arg{position} || 'right';
$legend->{_delete_series} = $arg{delete_series};
$legend->{_font} = $self->_convert_font_args( $arg{font} );
# Set the legend layout.
$legend->{_layout} = $self->_get_layout_properties( $arg{layout} );
# Turn off the legend.
if ( $arg{none} ) {
$legend->{_position} = 'none';
}
# Set the line properties for the legend.
my $line = $self->_get_line_properties( $arg{line} );
# Allow 'border' as a synonym for 'line'.
if ( $arg{border} ) {
$line = $self->_get_line_properties( $arg{border} );
}
# Set the fill properties for the legend.
my $fill = $self->_get_fill_properties( $arg{fill} );
# Set the pattern properties for the legend.
my $pattern = $self->_get_pattern_properties( $arg{pattern} );
# Set the gradient fill properties for the legend.
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 legend layout.
my $layout = $self->_get_layout_properties( $arg{layout} );
$legend->{_line} = $line;
$legend->{_fill} = $fill;
$legend->{_pattern} = $pattern;
$legend->{_gradient} = $gradient;
$legend->{_layout} = $layout;
return $legend;
}
###############################################################################
#
# _get_layout_properties()
#
# Convert user defined layout properties to the format required internally.
#
sub _get_layout_properties {
my $self = shift;
my $args = shift;
my $is_text = shift;
my $layout = {};
my @properties;
my %allowable;
return if !$args;
if ( $is_text ) {
@properties = ( 'x', 'y' );
}
else {
@properties = ( 'x', 'y', 'width', 'height' );
}
# Check for valid properties.
@allowable{@properties} = undef;
for my $key ( keys %$args ) {
if ( !exists $allowable{$key} ) {
warn "Property '$key' not allowed in layout options\n";
return;
}
}
# Set the layout properties.
for my $property ( @properties ) {
if ( !exists $args->{$property} ) {
warn "Property '$property' must be specified in layout options\n";
return;
}
my $value = $args->{$property};
if ( $value !~ /^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/ ) {
warn "Property '$property' value '$value' must be numeric"
. " in layout options\n";
return;
}
if ( $value < 0 || $value > 1 ) {
warn "Property '$property' value '$value' must be in range "
. "0 < x <= 1 in layout options\n";
return;
}
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
$self->xml_start_tag( 'c:chartSpace', @attributes );
}
##############################################################################
#
# _write_lang()
#
# Write the <c:lang> element.
#
sub _write_lang {
my $self = shift;
my $val = 'en-US';
my @attributes = ( 'val' => $val );
$self->xml_empty_tag( 'c:lang', @attributes );
}
##############################################################################
#
# _write_style()
#
# 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 {
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
# Write the c:dTable element.
$self->_write_d_table();
# Write the c:spPr element for the plotarea formatting.
$self->_write_sp_pr( $self->{_plotarea} );
$self->xml_end_tag( 'c:plotArea' );
}
##############################################################################
#
# _write_layout()
#
# Write the <c:layout> element.
#
sub _write_layout {
my $self = shift;
my $layout = shift;
my $type = shift;
if ( !$layout ) {
# Automatic layout.
$self->xml_empty_tag( 'c:layout' );
}
else {
# User defined manual layout.
$self->xml_start_tag( 'c:layout' );
$self->_write_manual_layout( $layout, $type );
$self->xml_end_tag( 'c:layout' );
}
}
##############################################################################
#
# _write_manual_layout()
#
# Write the <c:manualLayout> element.
#
sub _write_manual_layout {
my $self = shift;
my $layout = shift;
my $type = shift;
$self->xml_start_tag( 'c:manualLayout' );
# Plotarea has a layoutTarget element.
if ( $type eq 'plot' ) {
$self->xml_empty_tag( 'c:layoutTarget', ( 'val' => 'inner' ) );
}
# Set the x, y positions.
$self->xml_empty_tag( 'c:xMode', ( 'val' => 'edge' ) );
$self->xml_empty_tag( 'c:yMode', ( 'val' => 'edge' ) );
$self->xml_empty_tag( 'c:x', ( 'val' => $layout->{x} ) );
$self->xml_empty_tag( 'c:y', ( 'val' => $layout->{y} ) );
# For plotarea and legend set the width and height.
if ( $type ne 'text' ) {
$self->xml_empty_tag( 'c:w', ( 'val' => $layout->{width} ) );
$self->xml_empty_tag( 'c:h', ( 'val' => $layout->{height} ) );
}
$self->xml_end_tag( 'c:manualLayout' );
}
##############################################################################
#
# _write_chart_type()
#
# Write the chart type element. This method should be overridden by the
# subclasses.
#
sub _write_chart_type {
my $self = shift;
}
##############################################################################
#
# _write_grouping()
#
# Write the <c:grouping> element.
#
sub _write_grouping {
my $self = shift;
my $val = shift;
my @attributes = ( 'val' => $val );
$self->xml_empty_tag( 'c:grouping', @attributes );
}
##############################################################################
#
# _write_series()
#
# Write the series elements.
#
sub _write_series {
my $self = shift;
my $series = shift;
$self->_write_ser( $series );
}
##############################################################################
#
# _write_ser()
#
# Write the <c:ser> element.
#
sub _write_ser {
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
$self->xml_empty_tag( 'c:majorUnit', @attributes );
}
##############################################################################
#
# _write_c_minor_unit()
#
# Write the <c:minorUnit> element.
#
sub _write_c_minor_unit {
my $self = shift;
my $val = shift;
return unless $val;
my @attributes = ( 'val' => $val );
$self->xml_empty_tag( 'c:minorUnit', @attributes );
}
##############################################################################
#
# _write_c_major_time_unit()
#
# Write the <c:majorTimeUnit> element.
#
sub _write_c_major_time_unit {
my $self = shift;
my $val = shift || 'days';
my @attributes = ( 'val' => $val );
$self->xml_empty_tag( 'c:majorTimeUnit', @attributes );
}
##############################################################################
#
# _write_c_minor_time_unit()
#
# Write the <c:minorTimeUnit> element.
#
sub _write_c_minor_time_unit {
my $self = shift;
my $val = shift || 'days';
my @attributes = ( 'val' => $val );
$self->xml_empty_tag( 'c:minorTimeUnit', @attributes );
}
##############################################################################
#
# _write_legend()
#
# Write the <c:legend> element.
#
sub _write_legend {
my $self = shift;
my $legend = $self->{_legend};
my $position = $legend->{_position} || 'right';
my $font = $legend->{_font};
my @delete_series = ();
my $overlay = 0;
if ( defined $legend->{_delete_series}
&& ref $legend->{_delete_series} eq 'ARRAY' )
{
@delete_series = @{ $legend->{_delete_series} };
}
if ( $position =~ s/^overlay_// ) {
$overlay = 1;
}
my %allowed = (
right => 'r',
left => 'l',
top => 't',
bottom => 'b',
top_right => 'tr',
);
return if $position eq 'none';
return unless exists $allowed{$position};
$position = $allowed{$position};
$self->xml_start_tag( 'c:legend' );
# Write the c:legendPos element.
$self->_write_legend_pos( $position );
# Remove series labels from the legend.
for my $index ( @delete_series ) {
# Write the c:legendEntry element.
$self->_write_legend_entry( $index );
}
# Write the c:layout element.
$self->_write_layout( $legend->{_layout}, 'legend' );
# Write the c:overlay element.
$self->_write_overlay() if $overlay;
# Write the c:spPr element.
$self->_write_sp_pr( $legend );
# Write the c:txPr element.
if ( $font ) {
$self->_write_tx_pr( $font );
}
$self->xml_end_tag( 'c:legend' );
}
##############################################################################
#
# _write_legend_pos()
#
# Write the <c:legendPos> element.
#
sub _write_legend_pos {
my $self = shift;
my $val = shift;
my @attributes = ( 'val' => $val );
$self->xml_empty_tag( 'c:legendPos', @attributes );
}
##############################################################################
#
# _write_legend_entry()
#
# Write the <c:legendEntry> element.
#
sub _write_legend_entry {
my $self = shift;
my $index = shift;
$self->xml_start_tag( 'c:legendEntry' );
# Write the c:idx element.
$self->_write_idx( $index );
# Write the c:delete element.
$self->_write_delete( 1 );
$self->xml_end_tag( 'c:legendEntry' );
}
##############################################################################
#
# _write_overlay()
#
# Write the <c:overlay> element.
#
sub _write_overlay {
my $self = shift;
my $val = 1;
my @attributes = ( 'val' => $val );
$self->xml_empty_tag( 'c:overlay', @attributes );
}
##############################################################################
#
# _write_plot_vis_only()
#
# Write the <c:plotVisOnly> element.
#
sub _write_plot_vis_only {
my $self = shift;
my $val = 1;
# Ignore this element if we are plotting hidden data.
return if $self->{_show_hidden_data};
my @attributes = ( 'val' => $val );
$self->xml_empty_tag( 'c:plotVisOnly', @attributes );
}
##############################################################################
#
# _write_print_settings()
#
# Write the <c:printSettings> element.
#
sub _write_print_settings {
my $self = shift;
$self->xml_start_tag( 'c:printSettings' );
# Write the c:headerFooter element.
$self->_write_header_footer();
# Write the c:pageMargins element.
$self->_write_page_margins();
# Write the c:pageSetup element.
$self->_write_page_setup();
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
# _write_d_pt_point()
#
# Write an individual <c:dPt> element.
#
sub _write_d_pt_point {
my $self = shift;
my $index = shift;
my $point = shift;
$self->xml_start_tag( 'c:dPt' );
# Write the c:idx element.
$self->_write_idx( $index );
# Write the c:spPr element.
$self->_write_sp_pr( $point );
$self->xml_end_tag( 'c:dPt' );
}
##############################################################################
#
# _write_d_lbls()
#
# Write the <c:dLbls> element.
#
sub _write_d_lbls {
my $self = shift;
my $labels = shift;
return unless $labels;
$self->xml_start_tag( 'c:dLbls' );
# Write the custom c:dLbl elements.
if ( $labels->{custom} ) {
$self->_write_custom_labels( $labels, $labels->{custom} );
}
# Write the c:numFmt element.
if ( $labels->{num_format} ) {
$self->_write_data_label_number_format( $labels->{num_format} );
}
# Write the c:spPr element.
$self->_write_sp_pr( $labels );
# Write the data label font elements.
if ($labels->{font} ) {
$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();
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
# 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 );
}
##############################################################################
#
# _write_show_cat_name()
#
# Write the <c:showCatName> element.
#
sub _write_show_cat_name {
my $self = shift;
my $val = 1;
my @attributes = ( 'val' => $val );
$self->xml_empty_tag( 'c:showCatName', @attributes );
}
##############################################################################
#
# _write_show_ser_name()
#
# Write the <c:showSerName> element.
#
sub _write_show_ser_name {
my $self = shift;
my $val = 1;
my @attributes = ( 'val' => $val );
$self->xml_empty_tag( 'c:showSerName', @attributes );
}
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
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.
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
# 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 );
Note, for backward compatibility, it is also possible to turn off the legend via the C<position> property:
$chart->set_legend( position => 'none' );
=item * C<position>
Set the position of the chart legend.
$chart->set_legend( position => 'bottom' );
The default legend position is C<right>. The available positions are:
top
bottom
left
right
top_right
overlay_left
overlay_right
overlay_top_right
none
=item * C<border>
Set the border properties of the legend such as colour and style. See the L</CHART FORMATTING> 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<font>
Set the font properties of the chart legend:
$chart->set_legend( font => { bold => 1, italic => 1 } );
See the L</CHART FONTS> section below.
=item * C<delete_series>
This allows you to remove 1 or more series from the legend (the series will still display on the chart). This property takes an array ref as an argument and the series are zero indexed:
# Delete/hide series index 0 and 2 from the legend.
$chart->set_legend( delete_series => [0, 2] );
=item * C<layout>
Set the C<(x, y)> position of the legend in chart relative units:
$chart->set_legend(
layout => {
x => 0.80,
y => 0.37,
width => 0.12,
height => 0.25,
}
);
See the L</CHART LAYOUT> section below.
=back
=head2 set_chartarea()
The C<set_chartarea()> method is used to set the properties of the chart area.
$chart->set_chartarea(
border => { none => 1 },
fill => { color => 'red' }
);
The properties that can be set are:
=over
=item * C<border>
Set the border properties of the chartarea such as colour and style. See the L</CHART FORMATTING> section below.
=item * C<fill>
Set the fill properties of the chartarea such as colour. See the L</CHART FORMATTING> section below.
=item * C<pattern>
Set the pattern fill properties of the chartarea. See the L</CHART FORMATTING> section below.
=item * C<gradient>
Set the gradient fill properties of the chartarea. See the L</CHART FORMATTING> section below.
=back
=head2 set_plotarea()
The C<set_plotarea()> method is used to set properties of the plot area of a chart.
$chart->set_plotarea(
border => { color => 'yellow', width => 1, dash_type => 'dash' },
fill => { color => '#92D050' }
);
The properties that can be set are:
=over
=item * C<border>
Set the border properties of the plotarea such as colour and style. See the L</CHART FORMATTING> section below.
=item * C<fill>
Set the fill properties of the plotarea such as colour. See the L</CHART FORMATTING> section below.
=item * C<pattern>
Set the pattern fill properties of the plotarea. See the L</CHART FORMATTING> section below.
=item * C<gradient>
Set the gradient fill properties of the plotarea. See the L</CHART FORMATTING> section below.
=item * C<layout>
Set the C<(x, y)> position of the plotarea in chart relative units:
$chart->set_plotarea(
layout => {
x => 0.35,
y => 0.26,
width => 0.62,
height => 0.50,
}
);
See the L</CHART LAYOUT> section below.
=back
=head2 set_style()
The C<set_style()> method is used to set the style of the chart to one of the 42 built-in styles available on the 'Design' tab in Excel:
$chart->set_style( 4 );
The default style is 2.
=head2 set_table()
The C<set_table()> method adds a data table below the horizontal axis with the data used to plot the chart.
$chart->set_table();
The available options, with default values are:
vertical => 1 # Display vertical lines in the table.
horizontal => 1 # Display horizontal lines in the table.
outline => 1 # Display an outline in the table.
show_keys => 0 # Show the legend keys with the table data.
font => {} # Standard chart font properties.
The data table can only be shown with Bar, Column, Line, Area and stock charts. For font properties see the L</CHART FONTS> section below.
=head2 set_up_down_bars
The C<set_up_down_bars()> method adds Up-Down bars to Line charts to indicate the difference between the first and last data series.
$chart->set_up_down_bars();
It is possible to format the up and down bars to add C<fill>, C<pattern>, C<gradient> and C<border> properties if required. See the L</CHART FORMATTING> section below.
$chart->set_up_down_bars(
up => { fill => { color => 'green' } },
down => { fill => { color => 'red' } },
);
Up-down bars can only be applied to Line charts and to Stock charts (by default).
=head2 set_drop_lines
The C<set_drop_lines()> method adds Drop Lines to charts to show the Category value of points in the data.
$chart->set_drop_lines();
It is possible to format the Drop Line C<line> properties if required. See the L</CHART FORMATTING> section below.
$chart->set_drop_lines( line => { color => 'red', dash_type => 'square_dot' } );
Drop Lines are only available in Line, Area and Stock charts.
=head2 set_high_low_lines
The C<set_high_low_lines()> method adds High-Low lines to charts to show the maximum and minimum values of points in a Category.
$chart->set_high_low_lines();
It is possible to format the High-Low Line C<line> properties if required. See the L</CHART FORMATTING> section below.
$chart->set_high_low_lines( line => { color => 'red' } );
High-Low Lines are only available in Line and Stock charts.
=head2 show_blanks_as()
The C<show_blanks_as()> method controls how blank data is displayed in a chart.
$chart->show_blanks_as( 'span' );
The available options are:
gap # Blank data is shown as a gap. The default.
zero # Blank data is displayed as zero.
span # Blank data is connected with a line.
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
type
order (for polynomial trends)
period (for moving average)
forward (for all except moving average)
backward (for all except moving average)
name
line
intercept (for exponential, linear and polynomial only)
display_equation (for all except moving average)
display_r_squared (for all except moving average)
The C<type> property sets the type of trendline in the series.
$chart->add_series(
values => '=Sheet1!$B$1:$B$5',
trendline => { type => 'linear' },
);
The available C<trendline> types are:
exponential
linear
log
moving_average
polynomial
power
A C<polynomial> trendline can also specify the C<order> of the polynomial. The default value is 2.
$chart->add_series(
values => '=Sheet1!$B$1:$B$5',
trendline => {
type => 'polynomial',
order => 3,
},
);
A C<moving_average> trendline can also specify the C<period> of the moving average. The default value is 2.
$chart->add_series(
values => '=Sheet1!$B$1:$B$5',
trendline => {
type => 'moving_average',
period => 3,
},
);
The C<forward> and C<backward> properties set the forecast period of the trendline.
$chart->add_series(
values => '=Sheet1!$B$1:$B$5',
trendline => {
type => 'linear',
forward => 0.5,
backward => 0.5,
},
);
The C<name> property sets an optional name for the trendline that will appear in the chart legend. If it isn't specified the Excel default name will be displayed. This is usually a combination of the trendline type and the series name.
$chart->add_series(
values => '=Sheet1!$B$1:$B$5',
trendline => {
type => 'linear',
name => 'Interpolated trend',
},
);
The C<intercept> property sets the point where the trendline crosses the Y (value) axis:
$chart->add_series(
values => '=Sheet1!$B$1:$B$5',
trendline => {
type => 'linear',
intercept => 0.8,
},
);
The C<display_equation> property displays the trendline equation on the chart.
$chart->add_series(
values => '=Sheet1!$B$1:$B$5',
trendline => {
type => 'linear',
display_equation => 1,
},
);
The C<display_r_squared> property displays the R squared value of the trendline on the chart.
$chart->add_series(
values => '=Sheet1!$B$1:$B$5',
trendline => {
type => 'linear',
display_r_squared => 1
},
);
Several of these properties can be set in one go:
$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',
}
},
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
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
legend_key
num_format
font
border
fill
pattern
gradient
custom
The C<value> property turns on the I<Value> data label for a series.
$chart->add_series(
values => '=Sheet1!$B$1:$B$5',
data_labels => { value => 1 },
);
The C<category> property turns on the I<Category Name> data label for a series.
$chart->add_series(
values => '=Sheet1!$B$1:$B$5',
data_labels => { category => 1 },
);
The C<series_name> property turns on the I<Series Name> data label for a series.
$chart->add_series(
values => '=Sheet1!$B$1:$B$5',
data_labels => { series_name => 1 },
);
The C<position> property is used to position the data label for a series.
$chart->add_series(
values => '=Sheet1!$B$1:$B$5',
data_labels => { value => 1, position => 'center' },
);
In Excel the data label positions vary for different chart types. The allowable positions are:
| Position | Line | Bar | Pie | Area |
| | Scatter | Column | Doughnut | Radar |
| | Stock | | | |
|---------------|-----------|-----------|-----------|-----------|
| center | Yes | Yes | Yes | Yes* |
| right | Yes* | | | |
| left | Yes | | | |
| above | Yes | | | |
| below | Yes | | | |
| inside_base | | Yes | | |
| inside_end | | Yes | Yes | |
| outside_end | | Yes* | Yes | |
| best_fit | | | Yes* | |
Note: The * indicates the default position for each chart type in Excel, if a position isn't specified.
The C<percentage> property is used to turn on the display of data labels as a I<Percentage> for a series. It is mainly used for pie and doughnut charts.
$chart->add_series(
values => '=Sheet1!$B$1:$B$5',
data_labels => { percentage => 1 },
);
The C<leader_lines> property is used to turn on I<Leader Lines> for the data label for a series. It is mainly used for pie charts.
$chart->add_series(
values => '=Sheet1!$B$1:$B$5',
data_labels => { value => 1, leader_lines => 1 },
);
Note: Even when leader lines are turned on they aren't automatically visible in Excel or Excel::Writer::XLSX. Due to an Excel limitation (or design) leader lines only appear if the data label is moved manually or if the data labels are very close and...
The C<separator> property is used to change the separator between multiple data label items:
$chart->add_series(
values => '=Sheet1!$B$1:$B$5',
data_labels => { percentage => 1 },
data_labels => { value => 1, category => 1, separator => "\n" },
);
The separator value must be one of the following strings:
','
';'
'.'
"\n"
' '
The C<legend_key> property is used to turn on I<Legend Key> for the data label for a series:
$chart->add_series(
values => '=Sheet1!$B$1:$B$5',
data_labels => { value => 1, legend_key => 1 },
);
The C<num_format> property is used to set the number format for the data labels.
$chart->add_series(
values => '=Sheet1!$A$1:$A$5',
data_labels => { value => 1, 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.
The C<font> property is used to set the font properties of the data labels in a series:
$chart->add_series(
values => '=Sheet1!$A$1:$A$5',
data_labels => {
value => 1,
font => { name => 'Consolas' }
},
);
The C<font> property is also used to rotate the data labels in a series:
$chart->add_series(
values => '=Sheet1!$A$1:$A$5',
data_labels => {
value => 1,
font => { rotation => 45 }
},
);
See the L</CHART FONTS> section below.
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 = [
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
=back
=item * C<color>
Set the font color property. Can be a color index, a color name or HTML style RGB colour:
$chart->set_x_axis( num_font => { color => 'red' } );
$chart->set_y_axis( num_font => { color => '#92D050' } );
=back
Here is an example of Font formatting in a Chart program:
# Format the chart title.
$chart->set_title(
name => 'Sales Results Chart',
name_font => {
name => 'Calibri',
color => 'yellow',
},
);
# Format the X-axis.
$chart->set_x_axis(
name => 'Month',
name_font => {
name => 'Arial',
color => '#92D050'
},
num_font => {
name => 'Courier New',
color => '#00B0F0',
},
);
# Format the Y-axis.
$chart->set_y_axis(
name => 'Sales (1000 units)',
name_font => {
name => 'Century',
underline => 1,
color => 'red'
},
num_font => {
bold => 1,
italic => 1,
color => '#7030A0',
},
);
=head1 CHART LAYOUT
The position of the chart in the worksheet is controlled by the C<set_size()> method shown above.
It is also possible to change the layout of the following chart sub-objects:
plotarea
legend
title
x_axis caption
y_axis caption
Here are some examples:
$chart->set_plotarea(
layout => {
x => 0.35,
y => 0.26,
width => 0.62,
height => 0.50,
}
);
$chart->set_legend(
layout => {
x => 0.80,
y => 0.37,
width => 0.12,
height => 0.25,
}
);
$chart->set_title(
name => 'Title',
layout => {
x => 0.42,
y => 0.14,
}
);
$chart->set_x_axis(
name => 'X axis',
name_layout => {
x => 0.34,
y => 0.85,
}
);
Note that it is only possible to change the width and height for the C<plotarea> and C<legend> objects. For the other text based objects the width and height are changed by the font dimensions.
The layout units must be a float in the range C<0 < x <= 1> and are expressed as a percentage of the chart dimensions as shown below:
=begin html
<p><center><img src="http://jmcnamara.github.io/excel-writer-xlsx/images/examples/layout.png" width="826" height="423" alt="Chart object layout." /></center></p>
=end html
From this the layout units are calculated as follows:
layout:
width = w / W
height = h / H
x = a / W
y = b / H
These units are slightly cumbersome but are required by Excel so that the chart object positions remain relative to each other if the chart is resized by the user.
Note that for C<plotarea> the origin is the top left corner in the plotarea itself and does not take into account the axes.
=head1 WORKSHEET METHODS
In Excel a chartsheet (i.e, a chart that isn't embedded) shares properties with data worksheets such as tab selection, headers, footers, margins, and print properties.
In Excel::Writer::XLSX you can set chartsheet properties using the same methods that are used for Worksheet objects.
The following Worksheet methods are also available through a non-embedded Chart object:
get_name()
activate()
select()
hide()
set_first_sheet()
protect()
set_zoom()
set_tab_color()
set_landscape()
set_portrait()
set_paper()
set_margins()
set_header()
set_footer()
See L<Excel::Writer::XLSX> for a detailed explanation of these methods.
=head1 EXAMPLE
Here is a complete example that demonstrates some of the available features when creating a chart.
#!/usr/bin/perl
use strict;
use warnings;
use Excel::Writer::XLSX;
my $workbook = Excel::Writer::XLSX->new( 'chart.xlsx' );
my $worksheet = $workbook->add_worksheet();
( run in 3.316 seconds using v1.01-cache-2.11-cpan-39bf76dae61 )