1Excel::Writer::XLSX::ChUasretr(3C)ontributed Perl DocumeEnxtcaetli:o:nWriter::XLSX::Chart(3)
2
6 Chart - A class for writing Excel Charts.
7
9 To create a simple Excel file with a chart using Excel::Writer::XLSX:
10 #!/usr/bin/perl
12 use strict;
14 use warnings;
15 use Excel::Writer::XLSX;
16 my $workbook = Excel::Writer::XLSX->new( 'chart.xlsx' );
18 my $worksheet = $workbook->add_worksheet();
19 # Add the worksheet data the chart refers to.
21 my $data = [
22 [ 'Category', 2, 3, 4, 5, 6, 7 ],
23 [ 'Value', 1, 4, 5, 2, 1, 5 ],
24 ];
26 $worksheet->write( 'A1', $data );
28 # Add a worksheet chart.
30 my $chart = $workbook->add_chart( type => 'column' );
31 # Configure the chart.
33 $chart->add_series(
34 categories => '=Sheet1!$A$2:$A$7',
35 values => '=Sheet1!$B$2:$B$7',
36 );
37 $workbook->close();
39 __END__
41
43 The "Chart" module is an abstract base class for modules that implement
44 charts in Excel::Writer::XLSX. The information below is applicable to
45 all of the available subclasses.
46 The "Chart" module isn't used directly. A chart object is created via
48 the Workbook add_chart() method where the chart type is specified:
49 my $chart = $workbook->add_chart( type => 'column' );
51 Currently the supported chart types are:
53 • "area"
55 Creates an Area (filled line) style chart. See
57 Excel::Writer::XLSX::Chart::Area.
58 • "bar"
60 Creates a Bar style (transposed histogram) chart. See
62 Excel::Writer::XLSX::Chart::Bar.
63 • "column"
65 Creates a column style (histogram) chart. See
67 Excel::Writer::XLSX::Chart::Column.
68 • "line"
70 Creates a Line style chart. See Excel::Writer::XLSX::Chart::Line.
72 • "pie"
74 Creates a Pie style chart. See Excel::Writer::XLSX::Chart::Pie.
76 • "doughnut"
78 Creates a Doughnut style chart. See
80 Excel::Writer::XLSX::Chart::Doughnut.
81 • "scatter"
83 Creates a Scatter style chart. See
85 Excel::Writer::XLSX::Chart::Scatter.
86 • "stock"
88 Creates a Stock style chart. See Excel::Writer::XLSX::Chart::Stock.
90 • "radar"
92 Creates a Radar style chart. See Excel::Writer::XLSX::Chart::Radar.
94 Chart subtypes are also supported in some cases:
96 $workbook->add_chart( type => 'bar', subtype => 'stacked' );
98 The currently available subtypes are:
100 area
102 stacked
103 percent_stacked
104 bar
106 stacked
107 percent_stacked
108 column
110 stacked
111 percent_stacked
112 scatter
114 straight_with_markers
115 straight
116 smooth_with_markers
117 smooth
118 line
120 stacked
121 percent_stacked
122 radar
124 with_markers
125 filled
126
128 Methods that are common to all chart types are documented below. See
129 the documentation for each of the above chart modules for chart
130 specific information.
131 add_series()
133 In an Excel chart a "series" is a collection of information such as
134 values, X axis labels and the formatting that define which data is
135 plotted.
136 With an Excel::Writer::XLSX chart object the add_series() method is
138 used to set the properties for a series:
139 $chart->add_series(
141 categories => '=Sheet1!$A$2:$A$10', # Optional.
142 values => '=Sheet1!$B$2:$B$10', # Required.
143 line => { color => 'blue' },
144 );
145 The properties that can be set are:
147 • "values"
149 This is the most important property of a series and must be set for
151 every chart object. It links the chart with the worksheet data that
152 it displays. A formula or array ref can be used for the data range,
153 see below.
154 • "categories"
156 This sets the chart category labels. The category is more or less
158 the same as the X axis. In most chart types the "categories"
159 property is optional and the chart will just assume a sequential
160 series from "1 .. n".
161 • "name"
163 Set the name for the series. The name is displayed in the chart
165 legend and in the formula bar. The name property is optional and if
166 it isn't supplied it will default to "Series 1 .. n".
167 • "line"
169 Set the properties of the series line type such as colour and
171 width. See the "CHART FORMATTING" section below.
172 • "border"
174 Set the border properties of the series such as colour and style.
176 See the "CHART FORMATTING" section below.
177 • "fill"
179 Set the fill properties of the series such as colour. See the
181 "CHART FORMATTING" section below.
182 • "pattern"
184 Set the pattern properties of the series. See the "CHART
186 FORMATTING" section below.
187 • "gradient"
189 Set the gradient properties of the series. See the "CHART
191 FORMATTING" section below.
192 • "marker"
194 Set the properties of the series marker such as style and colour.
196 See the "SERIES OPTIONS" section below.
197 • "trendline"
199 Set the properties of the series trendline such as linear,
201 polynomial and moving average types. See the "SERIES OPTIONS"
202 section below.
203 • "smooth"
205 The "smooth" option is used to set the smooth property of a line
207 series. See the "SERIES OPTIONS" section below.
208 • "y_error_bars"
210 Set vertical error bounds for a chart series. See the "SERIES
212 OPTIONS" section below.
213 • "x_error_bars"
215 Set horizontal error bounds for a chart series. See the "SERIES
217 OPTIONS" section below.
218 • "data_labels"
220 Set data labels for the series. See the "SERIES OPTIONS" section
222 below.
223 • "points"
225 Set properties for individual points in a series. See the "SERIES
227 OPTIONS" section below.
228 • "invert_if_negative"
230 Invert the fill colour for negative values. Usually only applicable
232 to column and bar charts.
233 • "overlap"
235 Set the overlap between series in a Bar/Column chart. The range is
237 +/- 100. Default is 0.
238 overlap => 20,
240 Note, it is only necessary to apply this property to one series of
242 the chart.
243 • "gap"
245 Set the gap between series in a Bar/Column chart. The range is 0 to
247 500. Default is 150.
248 gap => 200,
250 Note, it is only necessary to apply this property to one series of
252 the chart.
253 The "categories" and "values" can take either a range formula such as
255 "=Sheet1!$A$2:$A$7" or, more usefully when generating the range
256 programmatically, an array ref with zero indexed row/column values:
257 [ $sheetname, $row_start, $row_end, $col_start, $col_end ]
259 The following are equivalent:
261 $chart->add_series( categories => '=Sheet1!$A$2:$A$7' ); # Same as ...
263 $chart->add_series( categories => [ 'Sheet1', 1, 6, 0, 0 ] ); # Zero-indexed.
264 You can add more than one series to a chart. In fact, some chart types
266 such as "stock" require it. The series numbering and order in the Excel
267 chart will be the same as the order in which they are added in
268 Excel::Writer::XLSX.
269 # Add the first series.
271 $chart->add_series(
272 categories => '=Sheet1!$A$2:$A$7',
273 values => '=Sheet1!$B$2:$B$7',
274 name => 'Test data series 1',
275 );
276 # Add another series. Same categories. Different range values.
278 $chart->add_series(
279 categories => '=Sheet1!$A$2:$A$7',
280 values => '=Sheet1!$C$2:$C$7',
281 name => 'Test data series 2',
282 );
283 It is also possible to specify non-contiguous ranges:
285 $chart->add_series(
287 categories => '=(Sheet1!$A$1:$A$9,Sheet1!$A$14:$A$25)',
288 values => '=(Sheet1!$B$1:$B$9,Sheet1!$B$14:$B$25)',
289 );
290 set_x_axis()
292 The set_x_axis() method is used to set properties of the X axis.
293 $chart->set_x_axis( name => 'Quarterly results' );
295 The properties that can be set are:
297 name
299 name_font
300 name_layout
301 num_font
302 num_format
303 line
304 fill
305 pattern
306 gradient
307 min
308 max
309 minor_unit
310 major_unit
311 interval_unit
312 interval_tick
313 crossing
314 reverse
315 position_axis
316 log_base
317 label_position
318 major_gridlines
319 minor_gridlines
320 visible
321 date_axis
322 text_axis
323 minor_unit_type
324 major_unit_type
325 minor_tick_mark
326 major_tick_mark
327 display_units
328 display_units_visible
329 These are explained below. Some properties are only applicable to value
331 or category axes, as indicated. See "Value and Category Axes" for an
332 explanation of Excel's distinction between the axis types.
333 • "name"
335 Set the name (title or caption) for the axis. The name is displayed
337 below the X axis. The "name" property is optional. The default is
338 to have no axis name. (Applicable to category and value axes).
339 $chart->set_x_axis( name => 'Quarterly results' );
341 The name can also be a formula such as "=Sheet1!$A$1".
343 • "name_font"
345 Set the font properties for the axis title. (Applicable to category
347 and value axes).
348 $chart->set_x_axis( name_font => { name => 'Arial', size => 10 } );
350 • "name_layout"
352 Set the "(x, y)" position of the axis caption in chart relative
354 units. (Applicable to category and value axes).
355 $chart->set_x_axis(
357 name => 'X axis',
358 name_layout => {
359 x => 0.34,
360 y => 0.85,
361 }
362 );
363 See the "CHART LAYOUT" section below.
365 • "num_font"
367 Set the font properties for the axis numbers. (Applicable to
369 category and value axes).
370 $chart->set_x_axis( num_font => { bold => 1, italic => 1 } );
372 See the "CHART FONTS" section below.
374 • "num_format"
376 Set the number format for the axis. (Applicable to category and
378 value axes).
379 $chart->set_x_axis( num_format => '#,##0.00' );
381 $chart->set_y_axis( num_format => '0.00%' );
382 The number format is similar to the Worksheet Cell Format
384 "num_format" apart from the fact that a format index cannot be
385 used. The explicit format string must be used as shown above. See
386 "set_num_format()" in Excel::Writer::XLSX for more information.
387 • "line"
389 Set the properties of the axis line type such as colour and width.
391 See the "CHART FORMATTING" section below.
392 $chart->set_x_axis( line => { none => 1 });
394 • "fill"
396 Set the fill properties of the axis such as colour. See the "CHART
398 FORMATTING" section below. Note, in Excel the axis fill is applied
399 to the area of the numbers of the axis and not to the area of the
400 axis bounding box. That background is set from the chartarea fill.
401 • "pattern"
403 Set the pattern properties of the axis such as colour. See the
405 "CHART FORMATTING" section below.
406 • "gradient"
408 Set the gradient properties of the axis such as colour. See the
410 "CHART FORMATTING" section below.
411 • "min"
413 Set the minimum value for the axis range. (Applicable to value axes
415 only.)
416 $chart->set_x_axis( min => 20 );
418 • "max"
420 Set the maximum value for the axis range. (Applicable to value axes
422 only.)
423 $chart->set_x_axis( max => 80 );
425 • "minor_unit"
427 Set the increment of the minor units in the axis range. (Applicable
429 to value axes only.)
430 $chart->set_x_axis( minor_unit => 0.4 );
432 • "major_unit"
434 Set the increment of the major units in the axis range. (Applicable
436 to value axes only.)
437 $chart->set_x_axis( major_unit => 2 );
439 • "interval_unit"
441 Set the interval unit for a category axis. (Applicable to category
443 axes only.)
444 $chart->set_x_axis( interval_unit => 2 );
446 • "interval_tick"
448 Set the tick interval for a category axis. (Applicable to category
450 axes only.)
451 $chart->set_x_axis( interval_tick => 4 );
453 • "crossing"
455 Set the position where the y axis will cross the x axis.
457 (Applicable to category and value axes.)
458 The "crossing" value can either be a numeric value or the strings
460 'max' or 'min' to set the crossing at the maximum/minimum axis
461 value.
462 $chart->set_x_axis( crossing => 3 );
464 # or
465 $chart->set_x_axis( crossing => 'max' );
466 For category axes the numeric value must be an integer to represent
468 the category number that the axis crosses at. For value axes it can
469 have any value associated with the axis.
470 If crossing is omitted (the default) the crossing will be set
472 automatically by Excel based on the chart data.
473 • "position_axis"
475 Position the axis on or between the axis tick marks. (Applicable to
477 category axes only.)
478 There are two allowable values "on_tick" and "between":
480 $chart->set_x_axis( position_axis => 'on_tick' );
482 $chart->set_x_axis( position_axis => 'between' );
483 • "reverse"
485 Reverse the order of the axis categories or values. (Applicable to
487 category and value axes.)
488 $chart->set_x_axis( reverse => 1 );
490 • "log_base"
492 Set the log base of the axis range. (Applicable to value axes
494 only.)
495 $chart->set_x_axis( log_base => 10 );
497 • "label_position"
499 Set the "Axis labels" position for the axis. The following
501 positions are available:
502 next_to (the default)
504 high
505 low
506 none
507 • "major_gridlines"
509 Configure the major gridlines for the axis. The available
511 properties are:
512 visible
514 line
515 For example:
517 $chart->set_x_axis(
519 major_gridlines => {
520 visible => 1,
521 line => { color => 'red', width => 1.25, dash_type => 'dash' }
522 }
523 );
524 The "visible" property is usually on for the X-axis but it depends
526 on the type of chart.
527 The "line" property sets the gridline properties such as colour and
529 width. See the "CHART FORMATTING" section below.
530 • "minor_gridlines"
532 This takes the same options as "major_gridlines" above.
534 The minor gridline "visible" property is off by default for all
536 chart types.
537 • "visible"
539 Configure the visibility of the axis.
541 $chart->set_x_axis( visible => 0 );
543 • "date_axis"
545 This option is used to treat a category axis with date or time data
547 as a Date Axis. (Applicable to category axes only.)
548 $chart->set_x_axis( date_axis => 1 );
550 This option also allows you to set "max" and "min" values for a
552 category axis which isn't allowed by Excel for non-date category
553 axes.
554 See "Date Category Axes" for more details.
556 • "text_axis"
558 This option is used to treat a category axis explicitly as a Text
560 Axis. (Applicable to category axes only.)
561 $chart->set_x_axis( text_axis => 1 );
563 • "minor_unit_type"
565 For "date_axis" axes, see above, this option is used to set the
567 type of the minor units. (Applicable to date category axes only.)
568 $chart->set_x_axis(
570 date_axis => 1,
571 minor_unit => 4,
572 minor_unit_type => 'months',
573 );
574 The allowable values for this option are "days", "months" and
576 "years".
577 • "major_unit_type"
579 Same as "minor_unit_type", see above, but for major axes unit
581 types.
582 More than one property can be set in a call to set_x_axis():
584 $chart->set_x_axis(
586 name => 'Quarterly results',
587 min => 10,
588 max => 80,
589 );
590 • "major_tick_mark"
592 Set the axis major tick mark type to one of the following values:
594 none
596 inside
597 outside
598 cross (inside and outside)
599 For example:
601 $chart->set_x_axis( major_tick_mark => 'none',
603 minor_tick_mark => 'inside' );
604 • "minor_tick_mark"
606 Set the axis minor tick mark type. Same as "major_tick_mark", see
608 above.
609 • "display_units"
611 Set the display units for the axis. This can be useful if the axis
613 numbers are very large but you don't want to represent them in
614 scientific notation. (Applicable to value axes only.) The available
615 display units are:
616 hundreds
618 thousands
619 ten_thousands
620 hundred_thousands
621 millions
622 ten_millions
623 hundred_millions
624 billions
625 trillions
626 Example:
628 $chart->set_x_axis( display_units => 'thousands' )
630 $chart->set_y_axis( display_units => 'millions' )
631 * "display_units_visible"
633 Control the visibility of the display units turned on by the
635 previous option. This option is on by default. (Applicable to value
636 axes only.)::
637 $chart->set_x_axis( display_units => 'thousands',
639 display_units_visible => 0 )
640 set_y_axis()
642 The set_y_axis() method is used to set properties of the Y axis. The
643 properties that can be set are the same as for "set_x_axis", see above.
644 set_x2_axis()
646 The set_x2_axis() method is used to set properties of the secondary X
647 axis. The properties that can be set are the same as for "set_x_axis",
648 see above. The default properties for this axis are:
649 label_position => 'none',
651 crossing => 'max',
652 visible => 0,
653 set_y2_axis()
655 The set_y2_axis() method is used to set properties of the secondary Y
656 axis. The properties that can be set are the same as for "set_x_axis",
657 see above. The default properties for this axis are:
658 major_gridlines => { visible => 0 }
660 combine()
662 The chart combine() method is used to combine two charts of different
663 types, for example a column and line chart:
664 my $column_chart = $workbook->add_chart( type => 'column', embedded => 1 );
666 # Configure the data series for the primary chart.
668 $column_chart->add_series(...);
669 # Create a new column chart. This will use this as the secondary chart.
671 my $line_chart = $workbook->add_chart( type => 'line', embedded => 1 );
672 # Configure the data series for the secondary chart.
674 $line_chart->add_series(...);
675 # Combine the charts.
677 $column_chart->combine( $line_chart );
678 See "Combined Charts" for more details.
680 set_size()
682 The set_size() method is used to set the dimensions of the chart. The
683 size properties that can be set are:
684 width
686 height
687 x_scale
688 y_scale
689 x_offset
690 y_offset
691 The "width" and "height" are in pixels. The default chart width is 480
693 pixels and the default height is 288 pixels. The size of the chart can
694 be modified by setting the "width" and "height" or by setting the
695 "x_scale" and "y_scale":
696 $chart->set_size( width => 720, height => 576 );
698 # Same as:
700 $chart->set_size( x_scale => 1.5, y_scale => 2 );
702 The "x_offset" and "y_offset" position the top left corner of the chart
704 in the cell that it is inserted into.
705 Note: the "x_scale", "y_scale", "x_offset" and "y_offset" parameters
707 can also be set via the insert_chart() method:
708 $worksheet->insert_chart( 'E2', $chart, { x_offset =>2, y_offset => 4,
710 x_scale => 1.5, y_scale => 2 } );
711 set_title()
713 The set_title() method is used to set properties of the chart title.
714 $chart->set_title( name => 'Year End Results' );
716 The properties that can be set are:
718 • "name"
720 Set the name (title) for the chart. The name is displayed above the
722 chart. The name can also be a formula such as "=Sheet1!$A$1". The
723 name property is optional. The default is to have no chart title.
724 • "name_font"
726 Set the font properties for the chart title. See the "CHART FONTS"
728 section below.
729 • "overlay"
731 Allow the title to be overlaid on the chart. Generally used with
733 the layout property below.
734 • "layout"
736 Set the "(x, y)" position of the title in chart relative units:
738 $chart->set_title(
740 name => 'Title',
741 overlay => 1,
742 layout => {
743 x => 0.42,
744 y => 0.14,
745 }
746 );
747 See the "CHART LAYOUT" section below.
749 • "none"
751 By default Excel adds an automatic chart title to charts with a
753 single series and a user defined series name. The "none" option
754 turns this default title off. It also turns off all other
755 set_title() options.
756 $chart->set_title( none => 1 );
758 set_legend()
760 The set_legend() method is used to set properties of the chart legend.
761 The properties that can be set are:
763 • "none"
765 The "none" option turns off the chart legend. In Excel chart
767 legends are on by default:
768 $chart->set_legend( none => 1 );
770 Note, for backward compatibility, it is also possible to turn off
772 the legend via the "position" property:
773 $chart->set_legend( position => 'none' );
775 • "position"
777 Set the position of the chart legend.
779 $chart->set_legend( position => 'bottom' );
781 The default legend position is "right". The available positions
783 are:
784 top
786 bottom
787 left
788 right
789 top_right
790 overlay_left
791 overlay_right
792 overlay_top_right
793 none
794 • "border"
796 Set the border properties of the legend such as colour and style.
798 See the "CHART FORMATTING" section below.
799 • "fill"
801 Set the fill properties of the legend such as colour. See the
803 "CHART FORMATTING" section below.
804 • "pattern"
806 Set the pattern fill properties of the legend. See the "CHART
808 FORMATTING" section below.
809 • "gradient"
811 Set the gradient fill properties of the legend. See the "CHART
813 FORMATTING" section below.
814 • "font"
816 Set the font properties of the chart legend:
818 $chart->set_legend( font => { bold => 1, italic => 1 } );
820 See the "CHART FONTS" section below.
822 • "delete_series"
824 This allows you to remove 1 or more series from the legend (the
826 series will still display on the chart). This property takes an
827 array ref as an argument and the series are zero indexed:
828 # Delete/hide series index 0 and 2 from the legend.
830 $chart->set_legend( delete_series => [0, 2] );
831 • "layout"
833 Set the "(x, y)" position of the legend in chart relative units:
835 $chart->set_legend(
837 layout => {
838 x => 0.80,
839 y => 0.37,
840 width => 0.12,
841 height => 0.25,
842 }
843 );
844 See the "CHART LAYOUT" section below.
846 set_chartarea()
848 The set_chartarea() method is used to set the properties of the chart
849 area.
850 $chart->set_chartarea(
852 border => { none => 1 },
853 fill => { color => 'red' }
854 );
855 The properties that can be set are:
857 • "border"
859 Set the border properties of the chartarea such as colour and
861 style. See the "CHART FORMATTING" section below.
862 • "fill"
864 Set the fill properties of the chartarea such as colour. See the
866 "CHART FORMATTING" section below.
867 • "pattern"
869 Set the pattern fill properties of the chartarea. See the "CHART
871 FORMATTING" section below.
872 • "gradient"
874 Set the gradient fill properties of the chartarea. See the "CHART
876 FORMATTING" section below.
877 set_plotarea()
879 The set_plotarea() method is used to set properties of the plot area of
880 a chart.
881 $chart->set_plotarea(
883 border => { color => 'yellow', width => 1, dash_type => 'dash' },
884 fill => { color => '#92D050' }
885 );
886 The properties that can be set are:
888 • "border"
890 Set the border properties of the plotarea such as colour and style.
892 See the "CHART FORMATTING" section below.
893 • "fill"
895 Set the fill properties of the plotarea such as colour. See the
897 "CHART FORMATTING" section below.
898 • "pattern"
900 Set the pattern fill properties of the plotarea. See the "CHART
902 FORMATTING" section below.
903 • "gradient"
905 Set the gradient fill properties of the plotarea. See the "CHART
907 FORMATTING" section below.
908 • "layout"
910 Set the "(x, y)" position of the plotarea in chart relative units:
912 $chart->set_plotarea(
914 layout => {
915 x => 0.35,
916 y => 0.26,
917 width => 0.62,
918 height => 0.50,
919 }
920 );
921 See the "CHART LAYOUT" section below.
923 set_style()
925 The set_style() method is used to set the style of the chart to one of
926 the 42 built-in styles available on the 'Design' tab in Excel:
927 $chart->set_style( 4 );
929 The default style is 2.
931 set_table()
933 The set_table() method adds a data table below the horizontal axis with
934 the data used to plot the chart.
935 $chart->set_table();
937 The available options, with default values are:
939 vertical => 1 # Display vertical lines in the table.
941 horizontal => 1 # Display horizontal lines in the table.
942 outline => 1 # Display an outline in the table.
943 show_keys => 0 # Show the legend keys with the table data.
944 font => {} # Standard chart font properties.
945 The data table can only be shown with Bar, Column, Line, Area and stock
947 charts. For font properties see the "CHART FONTS" section below.
948 set_up_down_bars
950 The set_up_down_bars() method adds Up-Down bars to Line charts to
951 indicate the difference between the first and last data series.
952 $chart->set_up_down_bars();
954 It is possible to format the up and down bars to add "fill", "pattern",
956 "gradient" and "border" properties if required. See the "CHART
957 FORMATTING" section below.
958 $chart->set_up_down_bars(
960 up => { fill => { color => 'green' } },
961 down => { fill => { color => 'red' } },
962 );
963 Up-down bars can only be applied to Line charts and to Stock charts (by
965 default).
966 set_drop_lines
968 The set_drop_lines() method adds Drop Lines to charts to show the
969 Category value of points in the data.
970 $chart->set_drop_lines();
972 It is possible to format the Drop Line "line" properties if required.
974 See the "CHART FORMATTING" section below.
975 $chart->set_drop_lines( line => { color => 'red', dash_type => 'square_dot' } );
977 Drop Lines are only available in Line, Area and Stock charts.
979 set_high_low_lines
981 The set_high_low_lines() method adds High-Low lines to charts to show
982 the maximum and minimum values of points in a Category.
983 $chart->set_high_low_lines();
985 It is possible to format the High-Low Line "line" properties if
987 required. See the "CHART FORMATTING" section below.
988 $chart->set_high_low_lines( line => { color => 'red' } );
990 High-Low Lines are only available in Line and Stock charts.
992 show_blanks_as()
994 The show_blanks_as() method controls how blank data is displayed in a
995 chart.
996 $chart->show_blanks_as( 'span' );
998 The available options are:
1000 gap # Blank data is shown as a gap. The default.
1002 zero # Blank data is displayed as zero.
1003 span # Blank data is connected with a line.
1004 show_hidden_data()
1006 Display data in hidden rows or columns on the chart.
1007 $chart->show_hidden_data();
1009
1011 This section details the following properties of add_series() in more
1012 detail:
1013 marker
1015 trendline
1016 y_error_bars
1017 x_error_bars
1018 data_labels
1019 points
1020 smooth
1021 Marker
1023 The marker format specifies the properties of the markers used to
1024 distinguish series on a chart. In general only Line and Scatter chart
1025 types and trendlines use markers.
1026 The following properties can be set for "marker" formats in a chart.
1028 type
1030 size
1031 border
1032 fill
1033 pattern
1034 gradient
1035 The "type" property sets the type of marker that is used with a series.
1037 $chart->add_series(
1039 values => '=Sheet1!$B$1:$B$5',
1040 marker => { type => 'diamond' },
1041 );
1042 The following "type" properties can be set for "marker" formats in a
1044 chart. These are shown in the same order as in the Excel format dialog.
1045 automatic
1047 none
1048 square
1049 diamond
1050 triangle
1051 x
1052 star
1053 short_dash
1054 long_dash
1055 circle
1056 plus
1057 The "automatic" type is a special case which turns on a marker using
1059 the default marker style for the particular series number.
1060 $chart->add_series(
1062 values => '=Sheet1!$B$1:$B$5',
1063 marker => { type => 'automatic' },
1064 );
1065 If "automatic" is on then other marker properties such as size, border
1067 or fill cannot be set.
1068 The "size" property sets the size of the marker and is generally used
1070 in conjunction with "type".
1071 $chart->add_series(
1073 values => '=Sheet1!$B$1:$B$5',
1074 marker => { type => 'diamond', size => 7 },
1075 );
1076 Nested "border" and "fill" properties can also be set for a marker. See
1078 the "CHART FORMATTING" section below.
1079 $chart->add_series(
1081 values => '=Sheet1!$B$1:$B$5',
1082 marker => {
1083 type => 'square',
1084 size => 5,
1085 border => { color => 'red' },
1086 fill => { color => 'yellow' },
1087 },
1088 );
1089 Trendline
1091 A trendline can be added to a chart series to indicate trends in the
1092 data such as a moving average or a polynomial fit.
1093 The following properties can be set for trendlines in a chart series.
1095 type
1097 order (for polynomial trends)
1098 period (for moving average)
1099 forward (for all except moving average)
1100 backward (for all except moving average)
1101 name
1102 line
1103 intercept (for exponential, linear and polynomial only)
1104 display_equation (for all except moving average)
1105 display_r_squared (for all except moving average)
1106 The "type" property sets the type of trendline in the series.
1108 $chart->add_series(
1110 values => '=Sheet1!$B$1:$B$5',
1111 trendline => { type => 'linear' },
1112 );
1113 The available "trendline" types are:
1115 exponential
1117 linear
1118 log
1119 moving_average
1120 polynomial
1121 power
1122 A "polynomial" trendline can also specify the "order" of the
1124 polynomial. The default value is 2.
1125 $chart->add_series(
1127 values => '=Sheet1!$B$1:$B$5',
1128 trendline => {
1129 type => 'polynomial',
1130 order => 3,
1131 },
1132 );
1133 A "moving_average" trendline can also specify the "period" of the
1135 moving average. The default value is 2.
1136 $chart->add_series(
1138 values => '=Sheet1!$B$1:$B$5',
1139 trendline => {
1140 type => 'moving_average',
1141 period => 3,
1142 },
1143 );
1144 The "forward" and "backward" properties set the forecast period of the
1146 trendline.
1147 $chart->add_series(
1149 values => '=Sheet1!$B$1:$B$5',
1150 trendline => {
1151 type => 'linear',
1152 forward => 0.5,
1153 backward => 0.5,
1154 },
1155 );
1156 The "name" property sets an optional name for the trendline that will
1158 appear in the chart legend. If it isn't specified the Excel default
1159 name will be displayed. This is usually a combination of the trendline
1160 type and the series name.
1161 $chart->add_series(
1163 values => '=Sheet1!$B$1:$B$5',
1164 trendline => {
1165 type => 'linear',
1166 name => 'Interpolated trend',
1167 },
1168 );
1169 The "intercept" property sets the point where the trendline crosses the
1171 Y (value) axis:
1172 $chart->add_series(
1174 values => '=Sheet1!$B$1:$B$5',
1175 trendline => {
1176 type => 'linear',
1177 intercept => 0.8,
1178 },
1179 );
1180 The "display_equation" property displays the trendline equation on the
1182 chart.
1183 $chart->add_series(
1185 values => '=Sheet1!$B$1:$B$5',
1186 trendline => {
1187 type => 'linear',
1188 display_equation => 1,
1189 },
1190 );
1191 The "display_r_squared" property displays the R squared value of the
1193 trendline on the chart.
1194 $chart->add_series(
1196 values => '=Sheet1!$B$1:$B$5',
1197 trendline => {
1198 type => 'linear',
1199 display_r_squared => 1
1200 },
1201 );
1202 Several of these properties can be set in one go:
1204 $chart->add_series(
1206 values => '=Sheet1!$B$1:$B$5',
1207 trendline => {
1208 type => 'polynomial',
1209 name => 'My trend name',
1210 order => 2,
1211 forward => 0.5,
1212 backward => 0.5,
1213 intercept => 1.5,
1214 display_equation => 1,
1215 display_r_squared => 1,
1216 line => {
1217 color => 'red',
1218 width => 1,
1219 dash_type => 'long_dash',
1220 }
1221 },
1222 );
1223 Trendlines cannot be added to series in a stacked chart or pie chart,
1225 radar chart, doughnut or (when implemented) to 3D, or surface charts.
1226 Error Bars
1228 Error bars can be added to a chart series to indicate error bounds in
1229 the data. The error bars can be vertical "y_error_bars" (the most
1230 common type) or horizontal "x_error_bars" (for Bar and Scatter charts
1231 only).
1232 The following properties can be set for error bars in a chart series.
1234 type
1236 value (for all types except standard error and custom)
1237 plus_values (for custom only)
1238 minus_values (for custom only)
1239 direction
1240 end_style
1241 line
1242 The "type" property sets the type of error bars in the series.
1244 $chart->add_series(
1246 values => '=Sheet1!$B$1:$B$5',
1247 y_error_bars => { type => 'standard_error' },
1248 );
1249 The available error bars types are available:
1251 fixed
1253 percentage
1254 standard_deviation
1255 standard_error
1256 custom
1257 All error bar types, except for "standard_error" and "custom" must also
1259 have a value associated with it for the error bounds:
1260 $chart->add_series(
1262 values => '=Sheet1!$B$1:$B$5',
1263 y_error_bars => {
1264 type => 'percentage',
1265 value => 5,
1266 },
1267 );
1268 The "custom" error bar type must specify "plus_values" and
1270 "minus_values" which should either by a "Sheet1!$A$1:$A$5" type range
1271 formula or an arrayref of values:
1272 $chart->add_series(
1274 categories => '=Sheet1!$A$1:$A$5',
1275 values => '=Sheet1!$B$1:$B$5',
1276 y_error_bars => {
1277 type => 'custom',
1278 plus_values => '=Sheet1!$C$1:$C$5',
1279 minus_values => '=Sheet1!$D$1:$D$5',
1280 },
1281 );
1282 # or
1284 $chart->add_series(
1287 categories => '=Sheet1!$A$1:$A$5',
1288 values => '=Sheet1!$B$1:$B$5',
1289 y_error_bars => {
1290 type => 'custom',
1291 plus_values => [1, 1, 1, 1, 1],
1292 minus_values => [2, 2, 2, 2, 2],
1293 },
1294 );
1295 Note, as in Excel the items in the "minus_values" do not need to be
1297 negative.
1298 The "direction" property sets the direction of the error bars. It
1300 should be one of the following:
1301 plus # Positive direction only.
1303 minus # Negative direction only.
1304 both # Plus and minus directions, The default.
1305 The "end_style" property sets the style of the error bar end cap. The
1307 options are 1 (the default) or 0 (for no end cap):
1308 $chart->add_series(
1310 values => '=Sheet1!$B$1:$B$5',
1311 y_error_bars => {
1312 type => 'fixed',
1313 value => 2,
1314 end_style => 0,
1315 direction => 'minus'
1316 },
1317 );
1318 Data Labels
1320 Data labels can be added to a chart series to indicate the values of
1321 the plotted data points.
1322 The following properties can be set for "data_labels" formats in a
1324 chart.
1325 value
1327 category
1328 series_name
1329 position
1330 percentage
1331 leader_lines
1332 separator
1333 legend_key
1334 num_format
1335 font
1336 border
1337 fill
1338 pattern
1339 gradient
1340 custom
1341 The "value" property turns on the Value data label for a series.
1343 $chart->add_series(
1345 values => '=Sheet1!$B$1:$B$5',
1346 data_labels => { value => 1 },
1347 );
1348 The "category" property turns on the Category Name data label for a
1350 series.
1351 $chart->add_series(
1353 values => '=Sheet1!$B$1:$B$5',
1354 data_labels => { category => 1 },
1355 );
1356 The "series_name" property turns on the Series Name data label for a
1358 series.
1359 $chart->add_series(
1361 values => '=Sheet1!$B$1:$B$5',
1362 data_labels => { series_name => 1 },
1363 );
1364 The "position" property is used to position the data label for a
1366 series.
1367 $chart->add_series(
1369 values => '=Sheet1!$B$1:$B$5',
1370 data_labels => { value => 1, position => 'center' },
1371 );
1372 In Excel the data label positions vary for different chart types. The
1374 allowable positions are:
1375 | Position | Line | Bar | Pie | Area |
1377 | | Scatter | Column | Doughnut | Radar |
1378 | | Stock | | | |
1379 |---------------|-----------|-----------|-----------|-----------|
1380 | center | Yes | Yes | Yes | Yes* |
1381 | right | Yes* | | | |
1382 | left | Yes | | | |
1383 | above | Yes | | | |
1384 | below | Yes | | | |
1385 | inside_base | | Yes | | |
1386 | inside_end | | Yes | Yes | |
1387 | outside_end | | Yes* | Yes | |
1388 | best_fit | | | Yes* | |
1389 Note: The * indicates the default position for each chart type in
1391 Excel, if a position isn't specified.
1392 The "percentage" property is used to turn on the display of data labels
1394 as a Percentage for a series. It is mainly used for pie and doughnut
1395 charts.
1396 $chart->add_series(
1398 values => '=Sheet1!$B$1:$B$5',
1399 data_labels => { percentage => 1 },
1400 );
1401 The "leader_lines" property is used to turn on Leader Lines for the
1403 data label for a series. It is mainly used for pie charts.
1404 $chart->add_series(
1406 values => '=Sheet1!$B$1:$B$5',
1407 data_labels => { value => 1, leader_lines => 1 },
1408 );
1409 Note: Even when leader lines are turned on they aren't automatically
1411 visible in Excel or Excel::Writer::XLSX. Due to an Excel limitation (or
1412 design) leader lines only appear if the data label is moved manually or
1413 if the data labels are very close and need to be adjusted
1414 automatically.
1415 The "separator" property is used to change the separator between
1417 multiple data label items:
1418 $chart->add_series(
1420 values => '=Sheet1!$B$1:$B$5',
1421 data_labels => { percentage => 1 },
1422 data_labels => { value => 1, category => 1, separator => "\n" },
1423 );
1424 The separator value must be one of the following strings:
1426 ','
1428 ';'
1429 '.'
1430 "\n"
1431 ' '
1432 The "legend_key" property is used to turn on Legend Key for the data
1434 label for a series:
1435 $chart->add_series(
1437 values => '=Sheet1!$B$1:$B$5',
1438 data_labels => { value => 1, legend_key => 1 },
1439 );
1440 The "num_format" property is used to set the number format for the data
1442 labels.
1443 $chart->add_series(
1445 values => '=Sheet1!$A$1:$A$5',
1446 data_labels => { value => 1, num_format => '#,##0.00' },
1447 );
1448 The number format is similar to the Worksheet Cell Format "num_format"
1450 apart from the fact that a format index cannot be used. The explicit
1451 format string must be used as shown above. See "set_num_format()" in
1452 Excel::Writer::XLSX for more information.
1453 The "font" property is used to set the font properties of the data
1455 labels in a series:
1456 $chart->add_series(
1458 values => '=Sheet1!$A$1:$A$5',
1459 data_labels => {
1460 value => 1,
1461 font => { name => 'Consolas' }
1462 },
1463 );
1464 The "font" property is also used to rotate the data labels in a series:
1466 $chart->add_series(
1468 values => '=Sheet1!$A$1:$A$5',
1469 data_labels => {
1470 value => 1,
1471 font => { rotation => 45 }
1472 },
1473 );
1474 See the "CHART FONTS" section below.
1476 The "border" property sets the border properties of the data labels
1478 such as colour and style. See the "CHART FORMATTING" section below.
1479 The "fill" property sets the fill properties of the data labels such as
1481 colour. See the "CHART FORMATTING" section below.
1482 Example of setting data label formatting:
1484 $chart->add_series(
1486 categories => '=Sheet1!$A$2:$A$7',
1487 values => '=Sheet1!$B$2:$B$7',
1488 data_labels => { value => 1,
1489 border => {color => 'red'},
1490 fill => {color => 'yellow'} },
1491 );
1492 The "pattern" property sets the pattern properties of the data labels.
1494 See the "CHART FORMATTING" section below.
1495 The "gradient" property sets the gradient properties of the data
1497 labels. See the "CHART FORMATTING" section below.
1498 The "custom" property is used to set the properties of individual data
1500 labels, see below.
1501 Custom Data Labels
1503 The "custom" property data label property is used to set the properties
1504 of individual data labels in a series. The most common use for this is
1505 to set custom text or number labels:
1506 my $custom_labels = [
1508 { value => 'Jan' },
1509 { value => 'Feb' },
1510 { value => 'Mar' },
1511 { value => 'Apr' },
1512 { value => 'May' },
1513 { value => 'Jun' },
1514 ];
1515 $chart->add_series(
1517 categories => '=Sheet1!$A$2:$A$7',
1518 values => '=Sheet1!$B$2:$B$7',
1519 data_labels => { value => 1, custom => $custom_labels },
1520 );
1521 As shown in the previous examples th "custom" property should be a list
1523 of dicts. Any property dict that is set to "undef" or not included in
1524 the list will be assigned the default data label value:
1525 my $custom_labels = [
1527 undef,
1528 { value => 'Feb' },
1529 { value => 'Mar' },
1530 { value => 'Apr' },
1531 ];
1532 The property elements of the "custom" lists should be dicts with the
1534 following allowable keys/sub-properties:
1535 value
1537 font
1538 border
1539 fill
1540 pattern
1541 gradient
1542 delete
1543 The "value" property should be a string, number or formula string that
1545 refers to a cell from which the value will be taken:
1546 $custom_labels = [
1548 { value => '=Sheet1!$C$2' },
1549 { value => '=Sheet1!$C$3' },
1550 { value => '=Sheet1!$C$4' },
1551 { value => '=Sheet1!$C$5' },
1552 { value => '=Sheet1!$C$6' },
1553 { value => '=Sheet1!$C$7' },
1554 ];
1555 The "font" property is used to set the font of the custom data label of
1557 a series (See the "CHART FONTS" section below):
1558 $custom_labels = [
1560 { value => '=Sheet1!$C$1', font => { color => 'red' } },
1561 { value => '=Sheet1!$C$2', font => { color => 'red' } },
1562 { value => '=Sheet1!$C$2', font => { color => 'red' } },
1563 { value => '=Sheet1!$C$4', font => { color => 'red' } },
1564 { value => '=Sheet1!$C$5', font => { color => 'red' } },
1565 { value => '=Sheet1!$C$6', font => { color => 'red' } },
1566 ];
1567 The "border" property sets the border properties of the data labels
1569 such as colour and style. See the "CHART FORMATTING" section below.
1570 The "fill" property sets the fill properties of the data labels such as
1572 colour. See the "CHART FORMATTING" section below.
1573 Example of setting custom data label formatting:
1575 $custom_labels = [
1577 { value => 'Jan', border => {color => 'blue'} },
1578 { value => 'Feb' },
1579 { value => 'Mar' },
1580 { value => 'Apr' },
1581 { value => 'May' },
1582 { value => 'Jun', fill => {color => 'green'} },
1583 ];
1584 The "pattern" property sets the pattern properties of the data labels.
1586 See the "CHART FORMATTING" section below.
1587 The "gradient" property sets the gradient properties of the data
1589 labels. See the "CHART FORMATTING" section below.
1590 The "delete" property can be used to delete labels in a series. This
1592 can be useful if you want to highlight one or more cells in the series,
1593 for example the maximum and the minimum:
1594 $custom_labels = [
1596 undef,
1597 { delete => 1 },
1598 { delete => 1 },
1599 { delete => 1 },
1600 { delete => 1 },
1601 undef,
1602 ];
1603 Points
1605 In general formatting is applied to an entire series in a chart.
1606 However, it is occasionally required to format individual points in a
1607 series. In particular this is required for Pie and Doughnut charts
1608 where each segment is represented by a point.
1609 In these cases it is possible to use the "points" property of
1611 add_series():
1612 $chart->add_series(
1614 values => '=Sheet1!$A$1:$A$3',
1615 points => [
1616 { fill => { color => '#FF0000' } },
1617 { fill => { color => '#CC0000' } },
1618 { fill => { color => '#990000' } },
1619 ],
1620 );
1621 The "points" property takes an array ref of format options (see the
1623 "CHART FORMATTING" section below). To assign default properties to
1624 points in a series pass "undef" values in the array ref:
1625 # Format point 3 of 3 only.
1627 $chart->add_series(
1628 values => '=Sheet1!$A$1:$A$3',
1629 points => [
1630 undef,
1631 undef,
1632 { fill => { color => '#990000' } },
1633 ],
1634 );
1635 # Format the first point only.
1637 $chart->add_series(
1638 values => '=Sheet1!$A$1:$A$3',
1639 points => [ { fill => { color => '#FF0000' } } ],
1640 );
1641 Smooth
1643 The "smooth" option is used to set the smooth property of a line
1644 series. It is only applicable to the "Line" and "Scatter" chart types.
1645 $chart->add_series( values => '=Sheet1!$C$1:$C$5',
1647 smooth => 1 );
1648
1650 The following chart formatting properties can be set for any chart
1651 object that they apply to (and that are supported by
1652 Excel::Writer::XLSX) such as chart lines, column fill areas, plot area
1653 borders, markers, gridlines and other chart elements documented above.
1654 line
1656 border
1657 fill
1658 pattern
1659 gradient
1660 Chart formatting properties are generally set using hash refs.
1662 $chart->add_series(
1664 values => '=Sheet1!$B$1:$B$5',
1665 line => { color => 'blue' },
1666 );
1667 In some cases the format properties can be nested. For example a
1669 "marker" may contain "border" and "fill" sub-properties.
1670 $chart->add_series(
1672 values => '=Sheet1!$B$1:$B$5',
1673 line => { color => 'blue' },
1674 marker => {
1675 type => 'square',
1676 size => 5,
1677 border => { color => 'red' },
1678 fill => { color => 'yellow' },
1679 },
1680 );
1681 Line
1683 The line format is used to specify properties of line objects that
1684 appear in a chart such as a plotted line on a chart or a border.
1685 The following properties can be set for "line" formats in a chart.
1687 none
1689 color
1690 width
1691 dash_type
1692 transparency
1693 The "none" property is uses to turn the "line" off (it is always on by
1695 default except in Scatter charts). This is useful if you wish to plot a
1696 series with markers but without a line.
1697 $chart->add_series(
1699 values => '=Sheet1!$B$1:$B$5',
1700 line => { none => 1 },
1701 );
1702 The "color" property sets the color of the "line".
1704 $chart->add_series(
1706 values => '=Sheet1!$B$1:$B$5',
1707 line => { color => 'red' },
1708 );
1709 The available colours are shown in the main Excel::Writer::XLSX
1711 documentation. It is also possible to set the colour of a line with a
1712 HTML style RGB colour:
1713 $chart->add_series(
1715 line => { color => '#FF0000' },
1716 );
1717 The "width" property sets the width of the "line". It should be
1719 specified in increments of 0.25 of a point as in Excel.
1720 $chart->add_series(
1722 values => '=Sheet1!$B$1:$B$5',
1723 line => { width => 3.25 },
1724 );
1725 The "dash_type" property sets the dash style of the line.
1727 $chart->add_series(
1729 values => '=Sheet1!$B$1:$B$5',
1730 line => { dash_type => 'dash_dot' },
1731 );
1732 The following "dash_type" values are available. They are shown in the
1734 order that they appear in the Excel dialog.
1735 solid
1737 round_dot
1738 square_dot
1739 dash
1740 dash_dot
1741 long_dash
1742 long_dash_dot
1743 long_dash_dot_dot
1744 The default line style is "solid".
1746 The "transparency" property sets the transparency of the "line" color
1748 in the integer range 1 - 100. The color must be set for transparency to
1749 work, it doesn't work with an automatic/default color:
1750 $chart->add_series(
1752 values => '=Sheet1!$B$1:$B$5',
1753 line => { color => 'yellow', transparency => 50 },
1754 );
1755 More than one "line" property can be specified at a time:
1757 $chart->add_series(
1759 values => '=Sheet1!$B$1:$B$5',
1760 line => {
1761 color => 'red',
1762 width => 1.25,
1763 dash_type => 'square_dot',
1764 },
1765 );
1766 Border
1768 The "border" property is a synonym for "line".
1769 It can be used as a descriptive substitute for "line" in chart types
1771 such as Bar and Column that have a border and fill style rather than a
1772 line style. In general chart objects with a "border" property will also
1773 have a fill property.
1774 Solid Fill
1776 The fill format is used to specify filled areas of chart objects such
1777 as the interior of a column or the background of the chart itself.
1778 The following properties can be set for "fill" formats in a chart.
1780 none
1782 color
1783 transparency
1784 The "none" property is used to turn the "fill" property off (it is
1786 generally on by default).
1787 $chart->add_series(
1789 values => '=Sheet1!$B$1:$B$5',
1790 fill => { none => 1 },
1791 );
1792 The "color" property sets the colour of the "fill" area.
1794 $chart->add_series(
1796 values => '=Sheet1!$B$1:$B$5',
1797 fill => { color => 'red' },
1798 );
1799 The available colours are shown in the main Excel::Writer::XLSX
1801 documentation. It is also possible to set the colour of a fill with a
1802 HTML style RGB colour:
1803 $chart->add_series(
1805 fill => { color => '#FF0000' },
1806 );
1807 The "transparency" property sets the transparency of the solid fill
1809 color in the integer range 1 - 100. The color must be set for
1810 transparency to work, it doesn't work with an automatic/default color:
1811 $chart->set_chartarea( fill => { color => 'yellow', transparency => 75 } );
1813 The "fill" format is generally used in conjunction with a "border"
1815 format which has the same properties as a "line" format.
1816 $chart->add_series(
1818 values => '=Sheet1!$B$1:$B$5',
1819 border => { color => 'red' },
1820 fill => { color => 'yellow' },
1821 );
1822 Pattern Fill
1824 The pattern fill format is used to specify pattern filled areas of
1825 chart objects such as the interior of a column or the background of the
1826 chart itself.
1827 The following properties can be set for "pattern" fill formats in a
1829 chart:
1830 pattern: the pattern to be applied (required)
1832 fg_color: the foreground color of the pattern (required)
1833 bg_color: the background color (optional, defaults to white)
1834 For example:
1836 $chart->set_plotarea(
1838 pattern => {
1839 pattern => 'percent_5',
1840 fg_color => 'red',
1841 bg_color => 'yellow',
1842 }
1843 );
1844 The following patterns can be applied:
1846 percent_5
1848 percent_10
1849 percent_20
1850 percent_25
1851 percent_30
1852 percent_40
1853 percent_50
1854 percent_60
1855 percent_70
1856 percent_75
1857 percent_80
1858 percent_90
1859 light_downward_diagonal
1860 light_upward_diagonal
1861 dark_downward_diagonal
1862 dark_upward_diagonal
1863 wide_downward_diagonal
1864 wide_upward_diagonal
1865 light_vertical
1866 light_horizontal
1867 narrow_vertical
1868 narrow_horizontal
1869 dark_vertical
1870 dark_horizontal
1871 dashed_downward_diagonal
1872 dashed_upward_diagonal
1873 dashed_horizontal
1874 dashed_vertical
1875 small_confetti
1876 large_confetti
1877 zigzag
1878 wave
1879 diagonal_brick
1880 horizontal_brick
1881 weave
1882 plaid
1883 divot
1884 dotted_grid
1885 dotted_diamond
1886 shingle
1887 trellis
1888 sphere
1889 small_grid
1890 large_grid
1891 small_check
1892 large_check
1893 outlined_diamond
1894 solid_diamond
1895 The foreground color, "fg_color", is a required parameter and can be a
1897 Html style "#RRGGBB" string or a limited number of named colors. The
1898 available colours are shown in the main Excel::Writer::XLSX
1899 documentation.
1900 The background color, "bg_color", is optional and defaults to black.
1902 If a pattern fill is used on a chart object it overrides the solid fill
1904 properties of the object.
1905 Gradient Fill
1907 The gradient fill format is used to specify gradient filled areas of
1908 chart objects such as the interior of a column or the background of the
1909 chart itself.
1910 The following properties can be set for "gradient" fill formats in a
1912 chart:
1913 colors: a list of colors
1915 positions: an optional list of positions for the colors
1916 type: the optional type of gradient fill
1917 angle: the optional angle of the linear fill
1918 The "colors" property sets a list of colors that define the "gradient":
1920 $chart->set_plotarea(
1922 gradient => { colors => [ '#DDEBCF', '#9CB86E', '#156B13' ] }
1923 );
1924 Excel allows between 2 and 10 colors in a gradient but it is unlikely
1926 that you will require more than 2 or 3.
1927 As with solid or pattern fill it is also possible to set the colors of
1929 a gradient with a Html style "#RRGGBB" string or a limited number of
1930 named colors. The available colours are shown in the main
1931 Excel::Writer::XLSX documentation:
1932 $chart->add_series(
1934 values => '=Sheet1!$A$1:$A$5',
1935 gradient => { colors => [ 'red', 'green' ] }
1936 );
1937 The "positions" defines an optional list of positions, between 0 and
1939 100, of where the colors in the gradient are located. Default values
1940 are provided for "colors" lists of between 2 and 4 but they can be
1941 specified if required:
1942 $chart->add_series(
1944 values => '=Sheet1!$A$1:$A$5',
1945 gradient => {
1946 colors => [ '#DDEBCF', '#156B13' ],
1947 positions => [ 10, 90 ],
1948 }
1949 );
1950 The "type" property can have one of the following values:
1952 linear (the default)
1954 radial
1955 rectangular
1956 path
1957 For example:
1959 $chart->add_series(
1961 values => '=Sheet1!$A$1:$A$5',
1962 gradient => {
1963 colors => [ '#DDEBCF', '#9CB86E', '#156B13' ],
1964 type => 'radial'
1965 }
1966 );
1967 If "type" isn't specified it defaults to "linear".
1969 For a "linear" fill the angle of the gradient can also be specified:
1971 $chart->add_series(
1973 values => '=Sheet1!$A$1:$A$5',
1974 gradient => { colors => [ '#DDEBCF', '#9CB86E', '#156B13' ],
1975 angle => 30 }
1976 );
1977 The default angle is 90 degrees.
1979 If gradient fill is used on a chart object it overrides the solid fill
1981 and pattern fill properties of the object.
1982
1984 The following font properties can be set for any chart object that they
1985 apply to (and that are supported by Excel::Writer::XLSX) such as chart
1986 titles, axis labels, axis numbering and data labels. They correspond to
1987 the equivalent Worksheet cell Format object properties. See
1988 "FORMAT_METHODS" in Excel::Writer::XLSX for more information.
1989 name
1991 size
1992 bold
1993 italic
1994 underline
1995 rotation
1996 color
1997 The following explains the available font properties:
1999 • "name"
2001 Set the font name:
2003 $chart->set_x_axis( num_font => { name => 'Arial' } );
2005 • "size"
2007 Set the font size:
2009 $chart->set_x_axis( num_font => { name => 'Arial', size => 10 } );
2011 • "bold"
2013 Set the font bold property, should be 0 or 1:
2015 $chart->set_x_axis( num_font => { bold => 1 } );
2017 • "italic"
2019 Set the font italic property, should be 0 or 1:
2021 $chart->set_x_axis( num_font => { italic => 1 } );
2023 • "underline"
2025 Set the font underline property, should be 0 or 1:
2027 $chart->set_x_axis( num_font => { underline => 1 } );
2029 • "rotation"
2031 Set the font rotation in the integer range -90 to 90, and 270-271:
2033 $chart->set_x_axis( num_font => { rotation => 45 } );
2035 This is useful for displaying large axis data such as dates in a
2037 more compact format.
2038 There are 2 special case angles outside the range -90 to 90:
2040 • 270: Stacked text, where the text runs from top to bottom.
2042 • 271: A special variant of stacked text for East Asian fonts.
2044 • "color"
2046 Set the font color property. Can be a color index, a color name or
2048 HTML style RGB colour:
2049 $chart->set_x_axis( num_font => { color => 'red' } );
2051 $chart->set_y_axis( num_font => { color => '#92D050' } );
2052 Here is an example of Font formatting in a Chart program:
2054 # Format the chart title.
2056 $chart->set_title(
2057 name => 'Sales Results Chart',
2058 name_font => {
2059 name => 'Calibri',
2060 color => 'yellow',
2061 },
2062 );
2063 # Format the X-axis.
2065 $chart->set_x_axis(
2066 name => 'Month',
2067 name_font => {
2068 name => 'Arial',
2069 color => '#92D050'
2070 },
2071 num_font => {
2072 name => 'Courier New',
2073 color => '#00B0F0',
2074 },
2075 );
2076 # Format the Y-axis.
2078 $chart->set_y_axis(
2079 name => 'Sales (1000 units)',
2080 name_font => {
2081 name => 'Century',
2082 underline => 1,
2083 color => 'red'
2084 },
2085 num_font => {
2086 bold => 1,
2087 italic => 1,
2088 color => '#7030A0',
2089 },
2090 );
2091
2093 The position of the chart in the worksheet is controlled by the
2094 set_size() method shown above.
2095 It is also possible to change the layout of the following chart sub-
2097 objects:
2098 plotarea
2100 legend
2101 title
2102 x_axis caption
2103 y_axis caption
2104 Here are some examples:
2106 $chart->set_plotarea(
2108 layout => {
2109 x => 0.35,
2110 y => 0.26,
2111 width => 0.62,
2112 height => 0.50,
2113 }
2114 );
2115 $chart->set_legend(
2117 layout => {
2118 x => 0.80,
2119 y => 0.37,
2120 width => 0.12,
2121 height => 0.25,
2122 }
2123 );
2124 $chart->set_title(
2126 name => 'Title',
2127 layout => {
2128 x => 0.42,
2129 y => 0.14,
2130 }
2131 );
2132 $chart->set_x_axis(
2134 name => 'X axis',
2135 name_layout => {
2136 x => 0.34,
2137 y => 0.85,
2138 }
2139 );
2140 Note that it is only possible to change the width and height for the
2142 "plotarea" and "legend" objects. For the other text based objects the
2143 width and height are changed by the font dimensions.
2144 The layout units must be a float in the range "0 < x <= 1" and are
2146 expressed as a percentage of the chart dimensions as shown below:
2147 From this the layout units are calculated as follows:
2149 layout:
2151 width = w / W
2152 height = h / H
2153 x = a / W
2154 y = b / H
2155 These units are slightly cumbersome but are required by Excel so that
2157 the chart object positions remain relative to each other if the chart
2158 is resized by the user.
2159 Note that for "plotarea" the origin is the top left corner in the
2161 plotarea itself and does not take into account the axes.
2162
2164 In Excel a chartsheet (i.e, a chart that isn't embedded) shares
2165 properties with data worksheets such as tab selection, headers,
2166 footers, margins, and print properties.
2167 In Excel::Writer::XLSX you can set chartsheet properties using the same
2169 methods that are used for Worksheet objects.
2170 The following Worksheet methods are also available through a non-
2172 embedded Chart object:
2173 get_name()
2175 activate()
2176 select()
2177 hide()
2178 set_first_sheet()
2179 protect()
2180 set_zoom()
2181 set_tab_color()
2182 set_landscape()
2184 set_portrait()
2185 set_paper()
2186 set_margins()
2187 set_header()
2188 set_footer()
2189 See Excel::Writer::XLSX for a detailed explanation of these methods.
2191
2193 Here is a complete example that demonstrates some of the available
2194 features when creating a chart.
2195 #!/usr/bin/perl
2197 use strict;
2199 use warnings;
2200 use Excel::Writer::XLSX;
2201 my $workbook = Excel::Writer::XLSX->new( 'chart.xlsx' );
2203 my $worksheet = $workbook->add_worksheet();
2204 my $bold = $workbook->add_format( bold => 1 );
2205 # Add the worksheet data that the charts will refer to.
2207 my $headings = [ 'Number', 'Batch 1', 'Batch 2' ];
2208 my $data = [
2209 [ 2, 3, 4, 5, 6, 7 ],
2210 [ 10, 40, 50, 20, 10, 50 ],
2211 [ 30, 60, 70, 50, 40, 30 ],
2212 ];
2214 $worksheet->write( 'A1', $headings, $bold );
2216 $worksheet->write( 'A2', $data );
2217 # Create a new chart object. In this case an embedded chart.
2219 my $chart = $workbook->add_chart( type => 'column', embedded => 1 );
2220 # Configure the first series.
2222 $chart->add_series(
2223 name => '=Sheet1!$B$1',
2224 categories => '=Sheet1!$A$2:$A$7',
2225 values => '=Sheet1!$B$2:$B$7',
2226 );
2227 # Configure second series. Note alternative use of array ref to define
2229 # ranges: [ $sheetname, $row_start, $row_end, $col_start, $col_end ].
2230 $chart->add_series(
2231 name => '=Sheet1!$C$1',
2232 categories => [ 'Sheet1', 1, 6, 0, 0 ],
2233 values => [ 'Sheet1', 1, 6, 2, 2 ],
2234 );
2235 # Add a chart title and some axis labels.
2237 $chart->set_title ( name => 'Results of sample analysis' );
2238 $chart->set_x_axis( name => 'Test number' );
2239 $chart->set_y_axis( name => 'Sample length (mm)' );
2240 # Set an Excel chart style. Blue colors with white outline and shadow.
2242 $chart->set_style( 11 );
2243 # Insert the chart into the worksheet (with an offset).
2245 $worksheet->insert_chart( 'D2', $chart, { x_offset => 25, y_offset => 10 } );
2246 $workbook->close();
2248 __END__
2250
2252 Excel differentiates between a chart axis that is used for series
2253 categories and an axis that is used for series values.
2254 In the example above the X axis is the category axis and each of the
2256 values is evenly spaced. The Y axis (in this case) is the value axis
2257 and points are displayed according to their value.
2258 Since Excel treats the axes differently it also handles their
2260 formatting differently and exposes different properties for each.
2261 As such some of "Excel::Writer::XLSX" axis properties can be set for a
2263 value axis, some can be set for a category axis and some properties can
2264 be set for both.
2265 For example the "min" and "max" properties can only be set for value
2267 axes and "reverse" can be set for both. The type of axis that a
2268 property applies to is shown in the set_x_axis() section of the
2269 documentation above.
2270 Some charts such as "Scatter" and "Stock" have two value axes.
2272 Date Axes are a special type of category axis which are explained
2274 below.
2275
2277 Date Category Axes are category axes that display time or date
2278 information. In Excel::Writer::XLSX Date Category Axes are set using
2279 the "date_axis" option:
2280 $chart->set_x_axis( date_axis => 1 );
2282 In general you should also specify a number format for a date axis
2284 although Excel will usually default to the same format as the data
2285 being plotted:
2286 $chart->set_x_axis(
2288 date_axis => 1,
2289 num_format => 'dd/mm/yyyy',
2290 );
2291 Excel doesn't normally allow minimum and maximum values to be set for
2293 category axes. However, date axes are an exception. The "min" and "max"
2294 values should be set as Excel times or dates:
2295 $chart->set_x_axis(
2297 date_axis => 1,
2298 min => $worksheet->convert_date_time('2013-01-02T'),
2299 max => $worksheet->convert_date_time('2013-01-09T'),
2300 num_format => 'dd/mm/yyyy',
2301 );
2302 For date axes it is also possible to set the type of the major and
2304 minor units:
2305 $chart->set_x_axis(
2307 date_axis => 1,
2308 minor_unit => 4,
2309 minor_unit_type => 'months',
2310 major_unit => 1,
2311 major_unit_type => 'years',
2312 num_format => 'dd/mm/yyyy',
2313 );
2314
2316 It is possible to add a secondary axis of the same type to a chart by
2317 setting the "y2_axis" or "x2_axis" property of the series:
2318 #!/usr/bin/perl
2320 use strict;
2322 use warnings;
2323 use Excel::Writer::XLSX;
2324 my $workbook = Excel::Writer::XLSX->new( 'chart_secondary_axis.xlsx' );
2326 my $worksheet = $workbook->add_worksheet();
2327 # Add the worksheet data that the charts will refer to.
2329 my $data = [
2330 [ 2, 3, 4, 5, 6, 7 ],
2331 [ 10, 40, 50, 20, 10, 50 ],
2332 ];
2334 $worksheet->write( 'A1', $data );
2336 # Create a new chart object. In this case an embedded chart.
2338 my $chart = $workbook->add_chart( type => 'line', embedded => 1 );
2339 # Configure a series with a secondary axis
2341 $chart->add_series(
2342 values => '=Sheet1!$A$1:$A$6',
2343 y2_axis => 1,
2344 );
2345 $chart->add_series(
2347 values => '=Sheet1!$B$1:$B$6',
2348 );
2349 # Insert the chart into the worksheet.
2352 $worksheet->insert_chart( 'D2', $chart );
2353 $workbook->close();
2355 __END__
2357 It is also possible to have a secondary, combined, chart either with a
2359 shared or secondary axis, see below.
2360
2362 It is also possible to combine two different chart types, for example a
2363 column and line chart to create a Pareto chart using the Chart
2364 combine() method:
2365 Here is a simpler example:
2367 use strict;
2369 use warnings;
2370 use Excel::Writer::XLSX;
2371 my $workbook = Excel::Writer::XLSX->new( 'chart_combined.xlsx' );
2373 my $worksheet = $workbook->add_worksheet();
2374 my $bold = $workbook->add_format( bold => 1 );
2375 # Add the worksheet data that the charts will refer to.
2377 my $headings = [ 'Number', 'Batch 1', 'Batch 2' ];
2378 my $data = [
2379 [ 2, 3, 4, 5, 6, 7 ],
2380 [ 10, 40, 50, 20, 10, 50 ],
2381 [ 30, 60, 70, 50, 40, 30 ],
2382 ];
2384 $worksheet->write( 'A1', $headings, $bold );
2386 $worksheet->write( 'A2', $data );
2387 #
2389 # In the first example we will create a combined column and line chart.
2390 # They will share the same X and Y axes.
2391 #
2392 # Create a new column chart. This will use this as the primary chart.
2394 my $column_chart = $workbook->add_chart( type => 'column', embedded => 1 );
2395 # Configure the data series for the primary chart.
2397 $column_chart->add_series(
2398 name => '=Sheet1!$B$1',
2399 categories => '=Sheet1!$A$2:$A$7',
2400 values => '=Sheet1!$B$2:$B$7',
2401 );
2402 # Create a new column chart. This will use this as the secondary chart.
2404 my $line_chart = $workbook->add_chart( type => 'line', embedded => 1 );
2405 # Configure the data series for the secondary chart.
2407 $line_chart->add_series(
2408 name => '=Sheet1!$C$1',
2409 categories => '=Sheet1!$A$2:$A$7',
2410 values => '=Sheet1!$C$2:$C$7',
2411 );
2412 # Combine the charts.
2414 $column_chart->combine( $line_chart );
2415 # Add a chart title and some axis labels. Note, this is done via the
2417 # primary chart.
2418 $column_chart->set_title( name => 'Combined chart - same Y axis' );
2419 $column_chart->set_x_axis( name => 'Test number' );
2420 $column_chart->set_y_axis( name => 'Sample length (mm)' );
2421 # Insert the chart into the worksheet
2424 $worksheet->insert_chart( 'E2', $column_chart );
2425 $workbook->close();
2427 The secondary chart can also be placed on a secondary axis using the
2429 methods shown in the previous section.
2430 In this case it is just necessary to add a "y2_axis" parameter to the
2432 series and, if required, add a title using set_y2_axis() of the
2433 secondary chart. The following are the additions to the previous
2434 example to place the secondary chart on the secondary axis:
2435 ...
2437 $line_chart->add_series(
2439 name => '=Sheet1!$C$1',
2440 categories => '=Sheet1!$A$2:$A$7',
2441 values => '=Sheet1!$C$2:$C$7',
2442 y2_axis => 1,
2443 );
2444 ...
2446 # Note: the y2 properites are on the secondary chart.
2448 $line_chart2->set_y2_axis( name => 'Target length (mm)' );
2449 The examples above use the concept of a primary and secondary chart.
2451 The primary chart is the chart that defines the primary X and Y axis.
2452 It is also used for setting all chart properties apart from the
2453 secondary data series. For example the chart title and axes properties
2454 should be set via the primary chart (except for the the secondary "y2"
2455 axis properties which should be applied to the secondary chart).
2456 See also "chart_combined.pl" and "chart_pareto.pl" examples in the
2458 distro for more detailed examples.
2459 There are some limitations on combined charts:
2461 • Pie charts cannot currently be combined.
2463 • Scatter charts cannot currently be used as a primary chart but they
2465 can be used as a secondary chart.
2466 • Bar charts can only combined secondary charts on a secondary axis.
2468 This is an Excel limitation.
2469
2471 John McNamara jmcnamara@cpan.org
2472
2474 Copyright MM-MMXXI, John McNamara.
2475 All Rights Reserved. This module is free software. It may be used,
2477 redistributed and/or modified under the same terms as Perl itself.
2478perl v5.36.0 2023-01-20 Excel::Writer::XLSX::Chart(3)