Excel-Writer-XLSX
view release on metacpan or search on metacpan
since it expected the "[Content_types]" to be the first file in the zip
container.
0.99 2019-02-10
+ Added font and font_size parameters to write_comment().
+ Allow formulas in date field of data_validation().
+ Added top_left chart legend position.
+ Added legend formatting options.
+ Added set_tab_ratio() method to set the ratio between the worksheet tabs
and the horizontal slider.
+ Added worksheet hide_row_col_headers() method to turn off worksheet row
and column headings.
+ Add functionality to align chart category axis labels.
! Fix for issue with special characters in worksheet table functions.
0.81 2014-11-01
+ Added chart axis line and fill properties.
0.80 2014-10-29
+ Chart Data Label enhancements. Added number formatting, font handling
(issue #106), separator (issue #107) and legend key.
+ Added chart specific handling of data label positions since not all
positions are available for all chart types. Issue #110.
0.79 2014-10-16
+ Added option to add images to headers and footers.
+ Added option to not scale header/footer with page.
! Fix to remove duplicate set_column() entries.
0.75 2013-12-02
+ Added interval unit option for category axes.
! Fix for axis name font rotation. Issue #83.
! Fix for several minor issues with Pie chart legends.
0.74 2013-11-17
! Improved defined name validation.
Issue #82.
+ Added set_title() option to turn off automatic title.
Issue #81.
+ Allow positioning of plotarea, legend, title and axis names.
Issue #80.
! Fix for modification of user params in conditional_formatting().
Issue #79.
! Fix for star style markers.
0.73 2013-11-08
Added defined_name.pl example program.
! Fix for fit_to_pages() with zero values.
Reported by Aki Huttunen.
0.24 2011-06-11
+ Added data validation and data_validate.pl example.
+ Added the option to turn off data series in chart legends.
0.23 2011-05-26
! Fix for charts ranges containing empty values.
0.22 2011-05-22
+ Added 'reverse' option to set_x_axis() and set_y_axis() in
t/chart/sub_write_chart_space.t
t/chart/sub_write_cross_ax.t
t/chart/sub_write_crosses.t
t/chart/sub_write_d_lbls.t
t/chart/sub_write_format_code.t
t/chart/sub_write_idx.t
t/chart/sub_write_label_align.t
t/chart/sub_write_label_offset.t
t/chart/sub_write_lang.t
t/chart/sub_write_layout.t
t/chart/sub_write_legend.t
t/chart/sub_write_legend_pos.t
t/chart/sub_write_major_gridlines.t
t/chart/sub_write_marker.t
t/chart/sub_write_marker_size.t
t/chart/sub_write_marker_value.t
t/chart/sub_write_num_cache.t
t/chart/sub_write_number_format.t
t/chart/sub_write_order.t
t/chart/sub_write_orientation.t
t/chart/sub_write_page_margins.t
t/chart/sub_write_page_setup.t
t/regression/chart_high_low_lines01.t
t/regression/chart_high_low_lines02.t
t/regression/chart_layout01.t
t/regression/chart_layout02.t
t/regression/chart_layout03.t
t/regression/chart_layout04.t
t/regression/chart_layout05.t
t/regression/chart_layout06.t
t/regression/chart_layout07.t
t/regression/chart_layout08.t
t/regression/chart_legend01.t
t/regression/chart_legend02.t
t/regression/chart_legend03.t
t/regression/chart_legend04.t
t/regression/chart_legend05.t
t/regression/chart_legend06.t
t/regression/chart_legend07.t
t/regression/chart_line01.t
t/regression/chart_line02.t
t/regression/chart_line03.t
t/regression/chart_line04.t
t/regression/chart_line05.t
t/regression/chart_line06.t
t/regression/chart_line07.t
t/regression/chart_line08.t
t/regression/chart_name01.t
t/regression/chart_name02.t
t/regression/xlsx_files/chart_high_low_lines01.xlsx
t/regression/xlsx_files/chart_high_low_lines02.xlsx
t/regression/xlsx_files/chart_layout01.xlsx
t/regression/xlsx_files/chart_layout02.xlsx
t/regression/xlsx_files/chart_layout03.xlsx
t/regression/xlsx_files/chart_layout04.xlsx
t/regression/xlsx_files/chart_layout05.xlsx
t/regression/xlsx_files/chart_layout06.xlsx
t/regression/xlsx_files/chart_layout07.xlsx
t/regression/xlsx_files/chart_layout08.xlsx
t/regression/xlsx_files/chart_legend01.xlsx
t/regression/xlsx_files/chart_legend03.xlsx
t/regression/xlsx_files/chart_legend04.xlsx
t/regression/xlsx_files/chart_legend05.xlsx
t/regression/xlsx_files/chart_legend06.xlsx
t/regression/xlsx_files/chart_legend07.xlsx
t/regression/xlsx_files/chart_line01.xlsx
t/regression/xlsx_files/chart_line02.xlsx
t/regression/xlsx_files/chart_line03.xlsx
t/regression/xlsx_files/chart_line04.xlsx
t/regression/xlsx_files/chart_line05.xlsx
t/regression/xlsx_files/chart_line06.xlsx
t/regression/xlsx_files/chart_line07.xlsx
t/regression/xlsx_files/chart_line08.xlsx
t/regression/xlsx_files/chart_name01.xlsx
t/regression/xlsx_files/chart_name02.xlsx
examples/chart_clustered.pl view on Meta::CPAN
$chart->add_series(
name => '=Sheet1!$E$1',
categories => '=Sheet1!$A$2:$B$6',
values => '=Sheet1!$E$2:$E$6',
);
# Set the Excel chart style.
$chart->set_style( 37 );
# Turn off the legend.
$chart->set_legend( position => 'none' );
# Insert the chart into the worksheet.
$worksheet->insert_chart( 'G3', $chart );
$workbook->close();
__END__
examples/chart_data_labels.pl view on Meta::CPAN
# Configure the data series and add the data labels.
$chart1->add_series(
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$B$2:$B$7',
data_labels => { value => 1 },
);
# Add a chart title.
$chart1->set_title( name => 'Chart with standard data labels' );
# Turn off the chart legend.
$chart1->set_legend( none => 1 );
# Insert the chart into the worksheet (with an offset).
$worksheet->insert_chart( 'D2', $chart1, { x_offset => 25, y_offset => 10 } );
#######################################################################
#
# Example with value and category data labels.
#
examples/chart_data_labels.pl view on Meta::CPAN
# Configure the data series and add the data labels.
$chart2->add_series(
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$B$2:$B$7',
data_labels => { value => 1, category => 1 },
);
# Add a chart title.
$chart2->set_title( name => 'Category and Value data labels' );
# Turn off the chart legend.
$chart2->set_legend( none => 1 );
# Insert the chart into the worksheet (with an offset).
$worksheet->insert_chart( 'D18', $chart2, { x_offset => 25, y_offset => 10 } );
#######################################################################
#
# Example with standard data labels with different font.
#
examples/chart_data_labels.pl view on Meta::CPAN
values => '=Sheet1!$B$2:$B$7',
data_labels => { value => 1,
font => { bold => 1,
color => 'red',
rotation => -30} },
);
# Add a chart title.
$chart3->set_title( name => 'Data labels with user defined font' );
# Turn off the chart legend.
$chart3->set_legend( none => 1 );
# Insert the chart into the worksheet (with an offset).
$worksheet->insert_chart( 'D34', $chart3, { x_offset => 25, y_offset => 10 } );
#######################################################################
#
# Example with standard data labels and formatting.
#
examples/chart_data_labels.pl view on Meta::CPAN
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$B$2:$B$7',
data_labels => { value => 1,
border => {color => 'red'},
fill => {color => 'yellow'} },
);
# Add a chart title.
$chart4->set_title( name => 'Data labels with formatting' );
# Turn off the chart legend.
$chart4->set_legend( none => 1 );
# Insert the chart into the worksheet (with an offset).
$worksheet->insert_chart( 'D50', $chart4, { x_offset => 25, y_offset => 10 } );
#######################################################################
#
# Example with custom string data labels.
#
examples/chart_data_labels.pl view on Meta::CPAN
# Configure the data series and add the data labels.
$chart5->add_series(
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$B$2:$B$7',
data_labels => { value => 1, custom => $custom_labels },
);
# Add a chart title.
$chart5->set_title( name => 'Chart with custom string data labels' );
# Turn off the chart legend.
$chart5->set_legend( none => 1 );
# Insert the chart into the worksheet (with an offset).
$worksheet->insert_chart( 'D66', $chart5, { x_offset => 25, y_offset => 10 } );
#######################################################################
#
# Example with custom data labels from cells.
#
examples/chart_data_labels.pl view on Meta::CPAN
# Configure the data series and add the data labels.
$chart6->add_series(
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$B$2:$B$7',
data_labels => { value => 1, custom => $custom_labels },
);
# Add a chart title.
$chart6->set_title( name => 'Chart with custom data labels from cells' );
# Turn off the chart legend.
$chart6->set_legend( none => 1 );
# Insert the chart into the worksheet (with an offset).
$worksheet->insert_chart( 'D82', $chart6, { x_offset => 25, y_offset => 10 } );
#######################################################################
#
# Example with custom and default data labels.
#
examples/chart_data_labels.pl view on Meta::CPAN
# Configure the data series and add the data labels.
$chart7->add_series(
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$B$2:$B$7',
data_labels => { value => 1, custom => $custom_labels },
);
# Add a chart title.
$chart7->set_title( name => 'Mixed custom and default data labels' );
# Turn off the chart legend.
$chart7->set_legend( none => 1 );
# Insert the chart into the worksheet (with an offset).
$worksheet->insert_chart( 'D98', $chart7, { x_offset => 25, y_offset => 10 } );
#######################################################################
#
# Example with deleted custom data labels.
#
examples/chart_data_labels.pl view on Meta::CPAN
# Configure the data series and add the data labels.
$chart8->add_series(
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$B$2:$B$7',
data_labels => { value => 1, custom => $custom_labels },
);
# Add a chart title.
$chart8->set_title( name => 'Chart with deleted data labels' );
# Turn off the chart legend.
$chart8->set_legend( none => 1 );
# Insert the chart into the worksheet (with an offset).
$worksheet->insert_chart( 'D114', $chart8, { x_offset => 25, y_offset => 10 } );
#######################################################################
#
# Example with custom string data labels and formatting.
#
examples/chart_data_labels.pl view on Meta::CPAN
values => '=Sheet1!$B$2:$B$7',
data_labels => { value => 1,
custom => $custom_labels,
border => {color => 'red'},
fill => {color => 'yellow'} },
);
# Add a chart title.
$chart9->set_title( name => 'Chart with custom labels and formatting' );
# Turn off the chart legend.
$chart9->set_legend( none => 1 );
# Insert the chart into the worksheet (with an offset).
$worksheet->insert_chart( 'D130', $chart9, { x_offset => 25, y_offset => 10 } );
$workbook->close();
__END__
examples/chart_data_table.pl view on Meta::CPAN
);
# Configure second series.
$chart2->add_series(
name => '=Sheet1!$C$1',
categories => [ 'Sheet1', 1, 6, 0, 0 ],
values => [ 'Sheet1', 1, 6, 2, 2 ],
);
# Add a chart title and some axis labels.
$chart2->set_title( name => 'Data Table with legend keys' );
$chart2->set_x_axis( name => 'Test number' );
$chart2->set_y_axis( name => 'Sample length (mm)' );
# Set a data table on the X-Axis with the legend keys showm.
$chart2->set_table( show_keys => 1 );
# Hide the chart legend since the keys are show on the data table.
$chart2->set_legend( position => 'none' );
# Insert the chart into the worksheet (with an offset).
$worksheet->insert_chart( 'D18', $chart2, { x_offset => 25, y_offset => 10 } );
$workbook->close();
__END__
examples/chart_gauge.pl view on Meta::CPAN
{ fill => { color => 'green' } },
{ fill => { color => 'yellow' } },
{ fill => { color => 'red' } },
{ fill => { none => 1 } },
],
);
# Rotate chart so the gauge parts are above the horizontal.
$chart_doughnut->set_rotation( 270 );
# Turn off the chart legend.
$chart_doughnut->set_legend( none => 1 );
# Turn off the chart fill and border.
$chart_doughnut->set_chartarea(
border => { none => 1 },
fill => { none => 1 },
);
# Configure the pie chart as the needle for the gauge.
$chart_pie->add_series(
name => '=Sheet1!$I$2',
examples/chart_pareto.pl view on Meta::CPAN
# Add a series.
$column_chart->add_series(
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$B$2:$B$7',
);
# Add a chart title.
$column_chart->set_title( name => 'Reasons for lateness' );
# Turn off the chart legend.
$column_chart->set_legend( position => 'none' );
# Set the title and scale of the Y axes. Note, the secondary axis is set from
# the primary chart.
$column_chart->set_y_axis(
name => 'Respondents (number)',
min => 0,
max => 120
);
$column_chart->set_y2_axis( max => 1 );
examples/chart_secondary_axis.pl view on Meta::CPAN
name => '=Sheet1!$A$1',
values => '=Sheet1!$A$2:$A$7',
y2_axis => 1,
);
$chart->add_series(
name => '=Sheet1!$B$1',
values => '=Sheet1!$B$2:$B$7',
);
$chart->set_legend( position => 'right' );
# Add a chart title and some axis labels.
$chart->set_title( name => 'Survey results' );
$chart->set_x_axis( name => 'Days', );
$chart->set_y_axis( name => 'Population', major_gridlines => { visible => 0 } );
$chart->set_y2_axis( name => 'Laser wounds' );
# Insert the chart into the worksheet (with an offset).
$worksheet->insert_chart( 'D2', $chart, { x_offset => 25, y_offset => 10 } );
examples/chart_styles.pl view on Meta::CPAN
for ( my $row_num = 0 ; $row_num < 90 ; $row_num += 15 ) {
for ( my $col_num = 0 ; $col_num < 64 ; $col_num += 8 ) {
my $chart = $workbook->add_chart(
type => $chart_type,
embedded => 1
);
$chart->add_series( values => '=Data!$A$1:$A$6' );
$chart->set_title( name => 'Style ' . $style_number );
$chart->set_legend( none => 1 );
$chart->set_style( $style_number );
$worksheet->insert_chart( $row_num, $col_num, $chart );
$style_number++;
}
}
}
# Create a worksheet with data for the charts.
my $data = [ 10, 40, 50, 20, 10, 50 ];
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
$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} = {};
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
# Set the title overlay option.
$self->{_title_overlay} = $arg{overlay};
# Set the no automatic title option.
$self->{_title_none} = $arg{none};
}
###############################################################################
#
# 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 {
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
$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 {
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
$self->{_title_font},
$self->{_title_layout},
$self->{_title_overlay}
);
}
}
# 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.
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
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' );
}
##############################################################################
#
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
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 {
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
# 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};
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
}
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 );
}
##############################################################################
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
=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>
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
=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.
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
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();
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
$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:
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
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.
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
);
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' },
);
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
=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',
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
);
$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:
lib/Excel/Writer/XLSX/Chart/Pie.pm view on Meta::CPAN
# Write the c:spPr element for the plotarea formatting.
$self->_write_sp_pr( $self->{_plotarea} );
$self->xml_end_tag( 'c:plotArea' );
}
##############################################################################
#
# _write_legend().
#
# Over-ridden method to add <c:txPr> to 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. Over-ridden.
$self->_write_tx_pr_legend( 0, $font );
$self->xml_end_tag( 'c:legend' );
}
##############################################################################
#
# _write_tx_pr_legend()
#
# Write the <c:txPr> element for legends.
#
sub _write_tx_pr_legend {
my $self = shift;
my $horiz = shift;
my $font = 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, $horiz );
# Write the a:lstStyle element.
$self->_write_a_lst_style();
# Write the a:p element.
$self->_write_a_p_legend( $font );
$self->xml_end_tag( 'c:txPr' );
}
##############################################################################
#
# _write_a_p_legend()
#
# Write the <a:p> element for legends.
#
sub _write_a_p_legend {
my $self = shift;
my $font = shift;
$self->xml_start_tag( 'a:p' );
# Write the a:pPr element.
$self->_write_a_p_pr_legend( $font );
# Write the a:endParaRPr element.
$self->_write_a_end_para_rpr();
$self->xml_end_tag( 'a:p' );
}
##############################################################################
#
# _write_a_p_pr_legend()
#
# Write the <a:pPr> element for legends.
#
sub _write_a_p_pr_legend {
my $self = shift;
my $font = shift;
my $rtl = 0;
my @attributes = ( 'rtl' => $rtl );
$self->xml_start_tag( 'a:pPr', @attributes );
# Write the a:defRPr element.
lib/Excel/Writer/XLSX/Chartsheet.pm view on Meta::CPAN
#
###############################################################################
sub add_series { return shift->{_chart}->add_series( @_ ) }
sub combine { return shift->{_chart}->combine( @_ ) }
sub set_x_axis { return shift->{_chart}->set_x_axis( @_ ) }
sub set_y_axis { return shift->{_chart}->set_y_axis( @_ ) }
sub set_x2_axis { return shift->{_chart}->set_x2_axis( @_ ) }
sub set_y2_axis { return shift->{_chart}->set_y2_axis( @_ ) }
sub set_title { return shift->{_chart}->set_title( @_ ) }
sub set_legend { return shift->{_chart}->set_legend( @_ ) }
sub set_plotarea { return shift->{_chart}->set_plotarea( @_ ) }
sub set_chartarea { return shift->{_chart}->set_chartarea( @_ ) }
sub set_style { return shift->{_chart}->set_style( @_ ) }
sub show_blanks_as { return shift->{_chart}->show_blanks_as( @_ ) }
sub show_na_as_empty_cell { return shift->{_chart}->show_na_as_empty_cell( @_ ) }
sub show_hidden_data { return shift->{_chart}->show_hidden_data( @_ ) }
sub set_size { return shift->{_chart}->set_size( @_ ) }
sub set_table { return shift->{_chart}->set_table( @_ ) }
sub set_up_down_bars { return shift->{_chart}->set_up_down_bars( @_ ) }
sub set_drop_lines { return shift->{_chart}->set_drop_lines( @_ ) }
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
name => '=Sheet1!$A$1',
values => '=Sheet1!$A$2:$A$7',
y2_axis => 1,
);
$chart->add_series(
name => '=Sheet1!$B$1',
values => '=Sheet1!$B$2:$B$7',
);
$chart->set_legend( position => 'right' );
# Add a chart title and some axis labels.
$chart->set_title( name => 'Survey results' );
$chart->set_x_axis( name => 'Days', );
$chart->set_y_axis( name => 'Population', major_gridlines => { visible => 0 } );
$chart->set_y2_axis( name => 'Laser wounds' );
# Insert the chart into the worksheet (with an offset).
$worksheet->insert_chart( 'D2', $chart, { x_offset => 25, y_offset => 10 } );
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
# Add a series.
$column_chart->add_series(
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$B$2:$B$7',
);
# Add a chart title.
$column_chart->set_title( name => 'Reasons for lateness' );
# Turn off the chart legend.
$column_chart->set_legend( position => 'none' );
# Set the title and scale of the Y axes. Note, the secondary axis is set from
# the primary chart.
$column_chart->set_y_axis(
name => 'Respondents (number)',
min => 0,
max => 120
);
$column_chart->set_y2_axis( max => 1 );
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
);
# Configure second series.
$chart2->add_series(
name => '=Sheet1!$C$1',
categories => [ 'Sheet1', 1, 6, 0, 0 ],
values => [ 'Sheet1', 1, 6, 2, 2 ],
);
# Add a chart title and some axis labels.
$chart2->set_title( name => 'Data Table with legend keys' );
$chart2->set_x_axis( name => 'Test number' );
$chart2->set_y_axis( name => 'Sample length (mm)' );
# Set a data table on the X-Axis with the legend keys showm.
$chart2->set_table( show_keys => 1 );
# Hide the chart legend since the keys are show on the data table.
$chart2->set_legend( position => 'none' );
# Insert the chart into the worksheet (with an offset).
$worksheet->insert_chart( 'D18', $chart2, { x_offset => 25, y_offset => 10 } );
$workbook->close();
__END__
Download this example: L<http://cpansearch.perl.org/src/JMCNAMARA/Excel-Writer-XLSX-1.14/examples/chart_data_table.pl>
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
# Configure the data series and add the data labels.
$chart1->add_series(
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$B$2:$B$7',
data_labels => { value => 1 },
);
# Add a chart title.
$chart1->set_title( name => 'Chart with standard data labels' );
# Turn off the chart legend.
$chart1->set_legend( none => 1 );
# Insert the chart into the worksheet (with an offset).
$worksheet->insert_chart( 'D2', $chart1, { x_offset => 25, y_offset => 10 } );
#######################################################################
#
# Example with value and category data labels.
#
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
# Configure the data series and add the data labels.
$chart2->add_series(
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$B$2:$B$7',
data_labels => { value => 1, category => 1 },
);
# Add a chart title.
$chart2->set_title( name => 'Category and Value data labels' );
# Turn off the chart legend.
$chart2->set_legend( none => 1 );
# Insert the chart into the worksheet (with an offset).
$worksheet->insert_chart( 'D18', $chart2, { x_offset => 25, y_offset => 10 } );
#######################################################################
#
# Example with standard data labels with different font.
#
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
values => '=Sheet1!$B$2:$B$7',
data_labels => { value => 1,
font => { bold => 1,
color => 'red',
rotation => -30} },
);
# Add a chart title.
$chart3->set_title( name => 'Data labels with user defined font' );
# Turn off the chart legend.
$chart3->set_legend( none => 1 );
# Insert the chart into the worksheet (with an offset).
$worksheet->insert_chart( 'D34', $chart3, { x_offset => 25, y_offset => 10 } );
#######################################################################
#
# Example with standard data labels and formatting.
#
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$B$2:$B$7',
data_labels => { value => 1,
border => {color => 'red'},
fill => {color => 'yellow'} },
);
# Add a chart title.
$chart4->set_title( name => 'Data labels with formatting' );
# Turn off the chart legend.
$chart4->set_legend( none => 1 );
# Insert the chart into the worksheet (with an offset).
$worksheet->insert_chart( 'D50', $chart4, { x_offset => 25, y_offset => 10 } );
#######################################################################
#
# Example with custom string data labels.
#
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
# Configure the data series and add the data labels.
$chart5->add_series(
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$B$2:$B$7',
data_labels => { value => 1, custom => $custom_labels },
);
# Add a chart title.
$chart5->set_title( name => 'Chart with custom string data labels' );
# Turn off the chart legend.
$chart5->set_legend( none => 1 );
# Insert the chart into the worksheet (with an offset).
$worksheet->insert_chart( 'D66', $chart5, { x_offset => 25, y_offset => 10 } );
#######################################################################
#
# Example with custom data labels from cells.
#
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
# Configure the data series and add the data labels.
$chart6->add_series(
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$B$2:$B$7',
data_labels => { value => 1, custom => $custom_labels },
);
# Add a chart title.
$chart6->set_title( name => 'Chart with custom data labels from cells' );
# Turn off the chart legend.
$chart6->set_legend( none => 1 );
# Insert the chart into the worksheet (with an offset).
$worksheet->insert_chart( 'D82', $chart6, { x_offset => 25, y_offset => 10 } );
#######################################################################
#
# Example with custom and default data labels.
#
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
# Configure the data series and add the data labels.
$chart7->add_series(
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$B$2:$B$7',
data_labels => { value => 1, custom => $custom_labels },
);
# Add a chart title.
$chart7->set_title( name => 'Mixed custom and default data labels' );
# Turn off the chart legend.
$chart7->set_legend( none => 1 );
# Insert the chart into the worksheet (with an offset).
$worksheet->insert_chart( 'D98', $chart7, { x_offset => 25, y_offset => 10 } );
#######################################################################
#
# Example with deleted custom data labels.
#
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
# Configure the data series and add the data labels.
$chart8->add_series(
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$B$2:$B$7',
data_labels => { value => 1, custom => $custom_labels },
);
# Add a chart title.
$chart8->set_title( name => 'Chart with deleted data labels' );
# Turn off the chart legend.
$chart8->set_legend( none => 1 );
# Insert the chart into the worksheet (with an offset).
$worksheet->insert_chart( 'D114', $chart8, { x_offset => 25, y_offset => 10 } );
#######################################################################
#
# Example with custom string data labels and formatting.
#
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
values => '=Sheet1!$B$2:$B$7',
data_labels => { value => 1,
custom => $custom_labels,
border => {color => 'red'},
fill => {color => 'yellow'} },
);
# Add a chart title.
$chart9->set_title( name => 'Chart with custom labels and formatting' );
# Turn off the chart legend.
$chart9->set_legend( none => 1 );
# Insert the chart into the worksheet (with an offset).
$worksheet->insert_chart( 'D130', $chart9, { x_offset => 25, y_offset => 10 } );
$workbook->close();
__END__
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
$chart->add_series(
name => '=Sheet1!$E$1',
categories => '=Sheet1!$A$2:$B$6',
values => '=Sheet1!$E$2:$E$6',
);
# Set the Excel chart style.
$chart->set_style( 37 );
# Turn off the legend.
$chart->set_legend( position => 'none' );
# Insert the chart into the worksheet.
$worksheet->insert_chart( 'G3', $chart );
$workbook->close();
__END__
Download this example: L<http://cpansearch.perl.org/src/JMCNAMARA/Excel-Writer-XLSX-1.14/examples/chart_clustered.pl>
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
for ( my $row_num = 0 ; $row_num < 90 ; $row_num += 15 ) {
for ( my $col_num = 0 ; $col_num < 64 ; $col_num += 8 ) {
my $chart = $workbook->add_chart(
type => $chart_type,
embedded => 1
);
$chart->add_series( values => '=Data!$A$1:$A$6' );
$chart->set_title( name => 'Style ' . $style_number );
$chart->set_legend( none => 1 );
$chart->set_style( $style_number );
$worksheet->insert_chart( $row_num, $col_num, $chart );
$style_number++;
}
}
}
# Create a worksheet with data for the charts.
my $data = [ 10, 40, 50, 20, 10, 50 ];
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
{ fill => { color => 'green' } },
{ fill => { color => 'yellow' } },
{ fill => { color => 'red' } },
{ fill => { none => 1 } },
],
);
# Rotate chart so the gauge parts are above the horizontal.
$chart_doughnut->set_rotation( 270 );
# Turn off the chart legend.
$chart_doughnut->set_legend( none => 1 );
# Turn off the chart fill and border.
$chart_doughnut->set_chartarea(
border => { none => 1 },
fill => { none => 1 },
);
# Configure the pie chart as the needle for the gauge.
$chart_pie->add_series(
name => '=Sheet1!$I$2',
t/chart/sub_write_legend.t view on Meta::CPAN
# Tests setup.
#
my $expected;
my $got;
my $caption;
my $chart;
###############################################################################
#
# Test the _write_legend() method.
#
$caption = " \tChart: _write_legend()";
$expected = '<c:legend><c:legendPos val="r"/><c:layout/></c:legend>';
$chart = _new_object( \$got, 'Excel::Writer::XLSX::Chart' );
# Default.
$chart->_write_legend();
is( $got, $expected, $caption );
###############################################################################
#
# Test the _write_legend() method.
#
$caption = " \tChart: _write_legend()";
$expected = '<c:legend><c:legendPos val="r"/><c:layout/></c:legend>';
$chart = _new_object( \$got, 'Excel::Writer::XLSX::Chart' );
# Default.
$chart->set_legend();
$chart->_write_legend();
is( $got, $expected, $caption );
###############################################################################
#
# Test the _write_legend() method.
#
$caption = " \tChart: _write_legend()";
$expected = '<c:legend><c:legendPos val="r"/><c:layout/></c:legend>';
$chart = _new_object( \$got, 'Excel::Writer::XLSX::Chart' );
$chart->set_legend( position => 'right' );
$chart->_write_legend();
is( $got, $expected, $caption );
###############################################################################
#
# Test the _write_legend() method.
#
$caption = " \tChart: _write_legend()";
$expected = '<c:legend><c:legendPos val="t"/><c:layout/></c:legend>';
$chart = _new_object( \$got, 'Excel::Writer::XLSX::Chart' );
$chart->set_legend( position => 'top' );
$chart->_write_legend();
is( $got, $expected, $caption );
###############################################################################
#
# Test the _write_legend() method.
#
$caption = " \tChart: _write_legend()";
$expected = '<c:legend><c:legendPos val="l"/><c:layout/></c:legend>';
$chart = _new_object( \$got, 'Excel::Writer::XLSX::Chart' );
$chart->set_legend( position => 'left' );
$chart->_write_legend();
is( $got, $expected, $caption );
###############################################################################
#
# Test the _write_legend() method.
#
$caption = " \tChart: _write_legend()";
$expected = '<c:legend><c:legendPos val="b"/><c:layout/></c:legend>';
$chart = _new_object( \$got, 'Excel::Writer::XLSX::Chart' );
$chart->set_legend( position => 'bottom' );
$chart->_write_legend();
is( $got, $expected, $caption );
###############################################################################
#
# Test the _write_legend() method.
#
$caption = " \tChart: _write_legend()";
$expected = '';
$chart = _new_object( \$got, 'Excel::Writer::XLSX::Chart' );
$chart->set_legend( position => 'none' );
$chart->_write_legend();
is( $got, $expected, $caption );
###############################################################################
#
# Test the _write_legend() method.
#
$caption = " \tChart: _write_legend()";
$expected = '';
$chart = _new_object( \$got, 'Excel::Writer::XLSX::Chart' );
$chart->set_legend( position => 'some_non_existing_value' );
$chart->_write_legend();
is( $got, $expected, $caption );
###############################################################################
#
# Test the _write_legend() method.
#
$caption = " \tChart: _write_legend()";
$expected = '<c:legend><c:legendPos val="r"/><c:layout/><c:overlay val="1"/></c:legend>';
$chart = _new_object( \$got, 'Excel::Writer::XLSX::Chart' );
$chart->set_legend( position => 'overlay_right' );
$chart->_write_legend();
is( $got, $expected, $caption );
###############################################################################
#
# Test the _write_legend() method.
#
$caption = " \tChart: _write_legend()";
$expected = '<c:legend><c:legendPos val="l"/><c:layout/><c:overlay val="1"/></c:legend>';
$chart = _new_object( \$got, 'Excel::Writer::XLSX::Chart' );
$chart->set_legend( position => 'overlay_left' );
$chart->_write_legend();
is( $got, $expected, $caption );
###############################################################################
#
# Test the _write_legend() method.
#
$caption = " \tChart: _write_legend()";
$expected = '<c:legend><c:legendPos val="tr"/><c:layout/></c:legend>';
$chart = _new_object( \$got, 'Excel::Writer::XLSX::Chart' );
$chart->set_legend( position => 'top_right' );
$chart->_write_legend();
is( $got, $expected, $caption );
###############################################################################
#
# Test the _write_legend() method.
#
$caption = " \tChart: _write_legend()";
$expected = '<c:legend><c:legendPos val="tr"/><c:layout/><c:overlay val="1"/></c:legend>';
$chart = _new_object( \$got, 'Excel::Writer::XLSX::Chart' );
$chart->set_legend( position => 'overlay_top_right' );
$chart->_write_legend();
is( $got, $expected, $caption );
__END__
t/chart/sub_write_legend_pos.t view on Meta::CPAN
# Tests setup.
#
my $expected;
my $got;
my $caption;
my $chart;
###############################################################################
#
# Test the _write_legend_pos() method.
#
$caption = " \tChart: _write_legend_pos()";
$expected = '<c:legendPos val="r"/>';
$chart = _new_object( \$got, 'Excel::Writer::XLSX::Chart' );
$chart->_write_legend_pos( 'r' );
is( $got, $expected, $caption );
__END__
t/regression/chart_bar16.t view on Meta::CPAN
$worksheet->write( 'A1', $data );
$chart->add_series( values => '=Sheet1!$A$1:$A$5' );
$chart->add_series( values => '=Sheet1!$B$1:$B$5' );
$chart->add_series( values => '=Sheet1!$C$1:$C$5' );
$chart->set_x_axis( name => 'Apple' );
$chart->set_y_axis( name => 'Pear' );
$chart->set_title( name => 'Title' );
$chart->set_legend( position => 'bottom' );
$workbook->close();
###############################################################################
#
# Compare the generated and existing Excel files.
#
my ( $got, $expected, $caption ) = _compare_xlsx_files(
t/regression/chart_combined11.t view on Meta::CPAN
values => '=Sheet1!$H$3:$H$6',
points => [
{ fill => { color => '#FF0000' } },
{ fill => { color => '#FFC000' } },
{ fill => { color => '#00B050' } },
{ fill => { none => 1 } },
],
);
$chart_doughnut->set_rotation( 270 );
$chart_doughnut->set_legend( none => 1 );
$chart_doughnut->set_chartarea(
border => { none => 1 },
fill => { none => 1 },
);
$chart_pie->add_series(
name => '=Sheet1!$I$2',
values => '=Sheet1!$I$3:$I$6',
points => [
{ fill => { none => 1 } },
t/regression/chart_data_labels20.t view on Meta::CPAN
[ 1, 2, 3, 4, 5 ],
[ 2, 4, 6, 8, 10 ],
[ 3, 6, 9, 12, 15 ],
];
$worksheet->write( 'A1', $data );
$chart->add_series(
values => '=Sheet1!$A$1:$A$5',
data_labels => { value => 1, legend_key => 1 },
);
$chart->add_series( values => '=Sheet1!$B$1:$B$5' );
$chart->add_series( values => '=Sheet1!$C$1:$C$5' );
$worksheet->insert_chart( 'E9', $chart );
$workbook->close();
t/regression/chart_data_labels21.t view on Meta::CPAN
$chart->add_series(
values => '=Sheet1!$A$1:$A$5',
data_labels => {
value => 1,
category => 1,
series_name => 1,
percentage => 1,
separator => ';',
leader_lines => 1,
position => 'inside_end',
legend_key => 1,
num_format => '#,##0.00',
font => { name => 'Consolas', baseline => -1, pitch_family => 49, charset => 0 }
},
);
$worksheet->insert_chart( 'E9', $chart );
$workbook->close();
t/regression/chart_data_labels36.t view on Meta::CPAN
$chart->add_series(
values => '=Sheet1!$A$1:$A$5',
data_labels => {
value => 1,
category => 1,
series_name => 1,
percentage => 1,
separator => ';',
leader_lines => 1,
position => 'inside_end',
legend_key => 1,
num_format => '#,##0.00',
font => { name => 'Consolas', baseline => -1, pitch_family => 49, charset => 0 },
border => { color => 'red'}
},
);
$worksheet->insert_chart( 'E9', $chart );
$workbook->close();
t/regression/chart_font07.t view on Meta::CPAN
];
$worksheet->write( 'A1', $data );
$chart->add_series( values => '=Sheet1!$A$1:$A$5' );
$chart->add_series( values => '=Sheet1!$B$1:$B$5' );
$chart->add_series( values => '=Sheet1!$C$1:$C$5' );
$chart->set_legend( font => { size => 9, baseline => -1 } );
$worksheet->insert_chart( 'E9', $chart );
$workbook->close();
###############################################################################
#
# Compare the generated and existing Excel files.
#
t/regression/chart_font08.t view on Meta::CPAN
];
$worksheet->write( 'A1', $data );
$chart->add_series( values => '=Sheet1!$A$1:$A$5' );
$chart->add_series( values => '=Sheet1!$B$1:$B$5' );
$chart->add_series( values => '=Sheet1!$C$1:$C$5' );
$chart->set_legend( font => { bold => 1, italic => 1, baseline => -1 } );
$worksheet->insert_chart( 'E9', $chart );
$workbook->close();
###############################################################################
#
# Compare the generated and existing Excel files.
#
t/regression/chart_format15.t view on Meta::CPAN
categories => '=Sheet1!$A$1:$A$5',
values => '=Sheet1!$B$1:$B$5',
trendline => { type => 'linear' },
);
$chart->add_series(
categories => '=Sheet1!$A$1:$A$5',
values => '=Sheet1!$C$1:$C$5',
);
$chart->set_legend( delete_series => [2, 0] );
$worksheet->insert_chart( 'E9', $chart );
$workbook->close();
###############################################################################
#
# Compare the generated and existing Excel files.
#
t/regression/chart_format26.t view on Meta::CPAN
categories => '=Sheet1!$A$1:$A$5',
values => '=Sheet1!$B$1:$B$5',
trendline => { type => 'linear', 'display_equation' => 1 },
);
$chart->add_series(
categories => '=Sheet1!$A$1:$A$5',
values => '=Sheet1!$C$1:$C$5',
);
$chart->set_legend( delete_series => [2, 0] );
$worksheet->insert_chart( 'E9', $chart );
$workbook->close();
###############################################################################
#
# Compare the generated and existing Excel files.
#
t/regression/chart_format28.t view on Meta::CPAN
type => 'linear',
display_equation => 1,
display_r_squared => 1 },
);
$chart->add_series(
categories => '=Sheet1!$A$1:$A$5',
values => '=Sheet1!$C$1:$C$5',
);
$chart->set_legend( delete_series => [0, 2] );
$worksheet->insert_chart( 'E9', $chart );
$workbook->close();
###############################################################################
#
# Compare the generated and existing Excel files.
#
t/regression/chart_format30.t view on Meta::CPAN
display_equation => 1,
display_r_squared => 1
},
);
$chart->add_series(
categories => '=Sheet1!$A$1:$A$5',
values => '=Sheet1!$C$1:$C$5',
);
$chart->set_legend( delete_series => [0, 2] );
$worksheet->insert_chart( 'E9', $chart );
$workbook->close();
###############################################################################
#
# Compare the generated and existing Excel files.
#
t/regression/chart_layout02.t view on Meta::CPAN
];
$worksheet->write( 'A1', $data );
$chart->add_series( values => '=Sheet1!$A$1:$A$5' );
$chart->add_series( values => '=Sheet1!$B$1:$B$5' );
$chart->add_series( values => '=Sheet1!$C$1:$C$5' );
$chart->set_legend(
layout => {
x => 0.80197353455818021,
y => 0.37442403032954213,
width => 0.12858202099737534,
height => 0.25115157480314959,
}
);
$worksheet->insert_chart( 'E9', $chart );
t/regression/chart_layout03.t view on Meta::CPAN
];
$worksheet->write( 'A1', $data );
$chart->add_series( values => '=Sheet1!$A$1:$A$5' );
$chart->add_series( values => '=Sheet1!$B$1:$B$5' );
$chart->add_series( values => '=Sheet1!$C$1:$C$5' );
$chart->set_legend(
position => 'overlay_right',
layout => {
x => 0.80197353455818043,
y => 0.3744240303295423,
width => 0.12858202099737534,
height => 0.25115157480314959,
}
);
$worksheet->insert_chart( 'E9', $chart );
t/regression/chart_legend01.t view on Meta::CPAN
use TestFunctions qw(_compare_xlsx_files _is_deep_diff);
use strict;
use warnings;
use Test::More tests => 1;
###############################################################################
#
# Tests setup.
#
my $filename = 'chart_legend01.xlsx';
my $dir = 't/regression/';
my $got_filename = $dir . "ewx_$filename";
my $exp_filename = $dir . 'xlsx_files/' . $filename;
my $ignore_members = [];
my $ignore_elements = {};
###############################################################################
#
# Test the creation of an Excel::Writer::XLSX file with legend options.
#
use Excel::Writer::XLSX;
my $workbook = Excel::Writer::XLSX->new( $got_filename );
my $worksheet = $workbook->add_worksheet();
my $chart = $workbook->add_chart( type => 'column', embedded => 1 );
# For testing, copy the randomly generated axis ids in the target xlsx file.
$chart->{_axis_ids} = [ 54461952, 54463872 ];
t/regression/chart_legend01.t view on Meta::CPAN
[ 3, 6, 9, 12, 15 ],
];
$worksheet->write( 'A1', $data );
$chart->add_series( values => '=Sheet1!$A$1:$A$5' );
$chart->add_series( values => '=Sheet1!$B$1:$B$5' );
$chart->add_series( values => '=Sheet1!$C$1:$C$5' );
$chart->set_legend( position => 'none' );
$worksheet->insert_chart( 'E9', $chart );
$workbook->close();
###############################################################################
#
# Compare the generated and existing Excel files.
#
t/regression/chart_legend02.t view on Meta::CPAN
use TestFunctions qw(_compare_xlsx_files _is_deep_diff);
use strict;
use warnings;
use Test::More tests => 1;
###############################################################################
#
# Tests setup.
#
my $filename = 'chart_legend01.xlsx';
my $dir = 't/regression/';
my $got_filename = $dir . "ewx2_$filename";
my $exp_filename = $dir . 'xlsx_files/' . $filename;
my $ignore_members = [];
my $ignore_elements = {};
###############################################################################
#
# Test the creation of an Excel::Writer::XLSX file with legend options.
#
use Excel::Writer::XLSX;
my $workbook = Excel::Writer::XLSX->new( $got_filename );
my $worksheet = $workbook->add_worksheet();
my $chart = $workbook->add_chart( type => 'column', embedded => 1 );
# For testing, copy the randomly generated axis ids in the target xlsx file.
$chart->{_axis_ids} = [ 54461952, 54463872 ];
t/regression/chart_legend02.t view on Meta::CPAN
[ 3, 6, 9, 12, 15 ],
];
$worksheet->write( 'A1', $data );
$chart->add_series( values => '=Sheet1!$A$1:$A$5' );
$chart->add_series( values => '=Sheet1!$B$1:$B$5' );
$chart->add_series( values => '=Sheet1!$C$1:$C$5' );
$chart->set_legend( none => 1 );
$worksheet->insert_chart( 'E9', $chart );
$workbook->close();
###############################################################################
#
# Compare the generated and existing Excel files.
#
( run in 0.960 second using v1.01-cache-2.11-cpan-49f99fa48dc )