1Excel::Writer::XLSX::ChUasretr(3C)ontributed Perl DocumeEnxtcaetli:o:nWriter::XLSX::Chart(3)
2
3
4

NAME

6       Chart - A class for writing Excel Charts.
7

SYNOPSIS

9       To create a simple Excel file with a chart using Excel::Writer::XLSX:
10
11           #!/usr/bin/perl
12
13           use strict;
14           use warnings;
15           use Excel::Writer::XLSX;
16
17           my $workbook  = Excel::Writer::XLSX->new( 'chart.xlsx' );
18           my $worksheet = $workbook->add_worksheet();
19
20           # 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
25           ];
26
27           $worksheet->write( 'A1', $data );
28
29           # Add a worksheet chart.
30           my $chart = $workbook->add_chart( type => 'column' );
31
32           # Configure the chart.
33           $chart->add_series(
34               categories => '=Sheet1!$A$2:$A$7',
35               values     => '=Sheet1!$B$2:$B$7',
36           );
37
38           $workbook->close();
39
40           __END__
41

DESCRIPTION

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
47       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
50           my $chart = $workbook->add_chart( type => 'column' );
51
52       Currently the supported chart types are:
53
54       •   "area"
55
56           Creates an Area (filled line) style chart. See
57           Excel::Writer::XLSX::Chart::Area.
58
59       •   "bar"
60
61           Creates a Bar style (transposed histogram) chart. See
62           Excel::Writer::XLSX::Chart::Bar.
63
64       •   "column"
65
66           Creates a column style (histogram) chart. See
67           Excel::Writer::XLSX::Chart::Column.
68
69       •   "line"
70
71           Creates a Line style chart. See Excel::Writer::XLSX::Chart::Line.
72
73       •   "pie"
74
75           Creates a Pie style chart. See Excel::Writer::XLSX::Chart::Pie.
76
77       •   "doughnut"
78
79           Creates a Doughnut style chart. See
80           Excel::Writer::XLSX::Chart::Doughnut.
81
82       •   "scatter"
83
84           Creates a Scatter style chart. See
85           Excel::Writer::XLSX::Chart::Scatter.
86
87       •   "stock"
88
89           Creates a Stock style chart. See Excel::Writer::XLSX::Chart::Stock.
90
91       •   "radar"
92
93           Creates a Radar style chart. See Excel::Writer::XLSX::Chart::Radar.
94
95       Chart subtypes are also supported in some cases:
96
97           $workbook->add_chart( type => 'bar', subtype => 'stacked' );
98
99       The currently available subtypes are:
100
101           area
102               stacked
103               percent_stacked
104
105           bar
106               stacked
107               percent_stacked
108
109           column
110               stacked
111               percent_stacked
112
113           scatter
114               straight_with_markers
115               straight
116               smooth_with_markers
117               smooth
118
119           line
120               stacked
121               percent_stacked
122
123           radar
124               with_markers
125               filled
126

CHART METHODS

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
132   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
137       With an Excel::Writer::XLSX chart object the "add_series()" method is
138       used to set the properties for a series:
139
140           $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
146       The properties that can be set are:
147
148       •   "values"
149
150           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
155       •   "categories"
156
157           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
162       •   "name"
163
164           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
168       •   "line"
169
170           Set the properties of the series line type such as colour and
171           width. See the "CHART FORMATTING" section below.
172
173       •   "border"
174
175           Set the border properties of the series such as colour and style.
176           See the "CHART FORMATTING" section below.
177
178       •   "fill"
179
180           Set the fill properties of the series such as colour. See the
181           "CHART FORMATTING" section below.
182
183       •   "pattern"
184
185           Set the pattern properties of the series. See the "CHART
186           FORMATTING" section below.
187
188       •   "gradient"
189
190           Set the gradient properties of the series. See the "CHART
191           FORMATTING" section below.
192
193       •   "marker"
194
195           Set the properties of the series marker such as style and colour.
196           See the "SERIES OPTIONS" section below.
197
198       •   "trendline"
199
200           Set the properties of the series trendline such as linear,
201           polynomial and moving average types. See the "SERIES OPTIONS"
202           section below.
203
204       •   "smooth"
205
206           The "smooth" option is used to set the smooth property of a line
207           series. See the "SERIES OPTIONS" section below.
208
209       •   "y_error_bars"
210
211           Set vertical error bounds for a chart series. See the "SERIES
212           OPTIONS" section below.
213
214       •   "x_error_bars"
215
216           Set horizontal error bounds for a chart series. See the "SERIES
217           OPTIONS" section below.
218
219       •   "data_labels"
220
221           Set data labels for the series. See the "SERIES OPTIONS" section
222           below.
223
224       •   "points"
225
226           Set properties for individual points in a series. See the "SERIES
227           OPTIONS" section below.
228
229       •   "invert_if_negative"
230
231           Invert the fill colour for negative values. Usually only applicable
232           to column and bar charts.
233
234       •   "overlap"
235
236           Set the overlap between series in a Bar/Column chart. The range is
237           +/- 100. Default is 0.
238
239               overlap => 20,
240
241           Note, it is only necessary to apply this property to one series of
242           the chart.
243
244       •   "gap"
245
246           Set the gap between series in a Bar/Column chart. The range is 0 to
247           500. Default is 150.
248
249               gap => 200,
250
251           Note, it is only necessary to apply this property to one series of
252           the chart.
253
254       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
258            [ $sheetname, $row_start, $row_end, $col_start, $col_end ]
259
260       The following are equivalent:
261
262           $chart->add_series( categories => '=Sheet1!$A$2:$A$7'      ); # Same as ...
263           $chart->add_series( categories => [ 'Sheet1', 1, 6, 0, 0 ] ); # Zero-indexed.
264
265       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
270           # 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
277           # 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
284       It is also possible to specify non-contiguous ranges:
285
286           $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
291   set_x_axis()
292       The "set_x_axis()" method is used to set properties of the X axis.
293
294           $chart->set_x_axis( name => 'Quarterly results' );
295
296       The properties that can be set are:
297
298           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
330       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
334       •   "name"
335
336           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
340               $chart->set_x_axis( name => 'Quarterly results' );
341
342           The name can also be a formula such as "=Sheet1!$A$1".
343
344       •   "name_font"
345
346           Set the font properties for the axis title. (Applicable to category
347           and value axes).
348
349               $chart->set_x_axis( name_font => { name => 'Arial', size => 10 } );
350
351       •   "name_layout"
352
353           Set the "(x, y)" position of the axis caption in chart relative
354           units. (Applicable to category and value axes).
355
356               $chart->set_x_axis(
357                   name        => 'X axis',
358                   name_layout => {
359                       x => 0.34,
360                       y => 0.85,
361                   }
362               );
363
364           See the "CHART LAYOUT" section below.
365
366       •   "num_font"
367
368           Set the font properties for the axis numbers. (Applicable to
369           category and value axes).
370
371               $chart->set_x_axis( num_font => { bold => 1, italic => 1 } );
372
373           See the "CHART FONTS" section below.
374
375       •   "num_format"
376
377           Set the number format for the axis. (Applicable to category and
378           value axes).
379
380               $chart->set_x_axis( num_format => '#,##0.00' );
381               $chart->set_y_axis( num_format => '0.00%'    );
382
383           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
388       •   "line"
389
390           Set the properties of the axis line type such as colour and width.
391           See the "CHART FORMATTING" section below.
392
393               $chart->set_x_axis( line => { none => 1 });
394
395       •   "fill"
396
397           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
402       •   "pattern"
403
404           Set the pattern properties of the axis such as colour. See the
405           "CHART FORMATTING" section below.
406
407       •   "gradient"
408
409           Set the gradient properties of the axis such as colour. See the
410           "CHART FORMATTING" section below.
411
412       •   "min"
413
414           Set the minimum value for the axis range. (Applicable to value axes
415           only.)
416
417               $chart->set_x_axis( min => 20 );
418
419       •   "max"
420
421           Set the maximum value for the axis range. (Applicable to value axes
422           only.)
423
424               $chart->set_x_axis( max => 80 );
425
426       •   "minor_unit"
427
428           Set the increment of the minor units in the axis range. (Applicable
429           to value axes only.)
430
431               $chart->set_x_axis( minor_unit => 0.4 );
432
433       •   "major_unit"
434
435           Set the increment of the major units in the axis range. (Applicable
436           to value axes only.)
437
438               $chart->set_x_axis( major_unit => 2 );
439
440       •   "interval_unit"
441
442           Set the interval unit for a category axis. (Applicable to category
443           axes only.)
444
445               $chart->set_x_axis( interval_unit => 2 );
446
447       •   "interval_tick"
448
449           Set the tick interval for a category axis. (Applicable to category
450           axes only.)
451
452               $chart->set_x_axis( interval_tick => 4 );
453
454       •   "crossing"
455
456           Set the position where the y axis will cross the x axis.
457           (Applicable to category and value axes.)
458
459           The "crossing" value can either be the string 'max' to set the
460           crossing at the maximum axis value or a numeric value.
461
462               $chart->set_x_axis( crossing => 3 );
463               # or
464               $chart->set_x_axis( crossing => 'max' );
465
466           For category axes the numeric value must be an integer to represent
467           the category number that the axis crosses at. For value axes it can
468           have any value associated with the axis.
469
470           If crossing is omitted (the default) the crossing will be set
471           automatically by Excel based on the chart data.
472
473       •   "position_axis"
474
475           Position the axis on or between the axis tick marks. (Applicable to
476           category axes only.)
477
478           There are two allowable values "on_tick" and "between":
479
480               $chart->set_x_axis( position_axis => 'on_tick' );
481               $chart->set_x_axis( position_axis => 'between' );
482
483       •   "reverse"
484
485           Reverse the order of the axis categories or values. (Applicable to
486           category and value axes.)
487
488               $chart->set_x_axis( reverse => 1 );
489
490       •   "log_base"
491
492           Set the log base of the axis range. (Applicable to value axes
493           only.)
494
495               $chart->set_x_axis( log_base => 10 );
496
497       •   "label_position"
498
499           Set the "Axis labels" position for the axis. The following
500           positions are available:
501
502               next_to (the default)
503               high
504               low
505               none
506
507       •   "major_gridlines"
508
509           Configure the major gridlines for the axis. The available
510           properties are:
511
512               visible
513               line
514
515           For example:
516
517               $chart->set_x_axis(
518                   major_gridlines => {
519                       visible => 1,
520                       line    => { color => 'red', width => 1.25, dash_type => 'dash' }
521                   }
522               );
523
524           The "visible" property is usually on for the X-axis but it depends
525           on the type of chart.
526
527           The "line" property sets the gridline properties such as colour and
528           width. See the "CHART FORMATTING" section below.
529
530       •   "minor_gridlines"
531
532           This takes the same options as "major_gridlines" above.
533
534           The minor gridline "visible" property is off by default for all
535           chart types.
536
537       •   "visible"
538
539           Configure the visibility of the axis.
540
541               $chart->set_x_axis( visible => 0 );
542
543       •   "date_axis"
544
545           This option is used to treat a category axis with date or time data
546           as a Date Axis. (Applicable to category axes only.)
547
548               $chart->set_x_axis( date_axis => 1 );
549
550           This option also allows you to set "max" and "min" values for a
551           category axis which isn't allowed by Excel for non-date category
552           axes.
553
554           See "Date Category Axes" for more details.
555
556       •   "text_axis"
557
558           This option is used to treat a category axis explicitly as a Text
559           Axis. (Applicable to category axes only.)
560
561               $chart->set_x_axis( text_axis => 1 );
562
563       •   "minor_unit_type"
564
565           For "date_axis" axes, see above, this option is used to set the
566           type of the minor units. (Applicable to date category axes only.)
567
568               $chart->set_x_axis(
569                   date_axis         => 1,
570                   minor_unit        => 4,
571                   minor_unit_type   => 'months',
572               );
573
574           The allowable values for this option are "days", "months" and
575           "years".
576
577       •   "major_unit_type"
578
579           Same as "minor_unit_type", see above, but for major axes unit
580           types.
581
582           More than one property can be set in a call to "set_x_axis()":
583
584               $chart->set_x_axis(
585                   name => 'Quarterly results',
586                   min  => 10,
587                   max  => 80,
588               );
589
590       •   "major_tick_mark"
591
592           Set the axis major tick mark type to one of the following values:
593
594               none
595               inside
596               outside
597               cross   (inside and outside)
598
599           For example:
600
601               $chart->set_x_axis( major_tick_mark => 'none',
602                                   minor_tick_mark => 'inside' );
603
604       •   "minor_tick_mark"
605
606           Set the axis minor tick mark type. Same as "major_tick_mark", see
607           above.
608
609       •   "display_units"
610
611           Set the display units for the axis. This can be useful if the axis
612           numbers are very large but you don't want to represent them in
613           scientific notation. (Applicable to value axes only.) The available
614           display units are:
615
616               hundreds
617               thousands
618               ten_thousands
619               hundred_thousands
620               millions
621               ten_millions
622               hundred_millions
623               billions
624               trillions
625
626           Example:
627
628               $chart->set_x_axis( display_units => 'thousands' )
629               $chart->set_y_axis( display_units => 'millions' )
630
631           * "display_units_visible"
632
633           Control the visibility of the display units turned on by the
634           previous option. This option is on by default. (Applicable to value
635           axes only.)::
636
637               $chart->set_x_axis( display_units         => 'thousands',
638                                   display_units_visible => 0 )
639
640   set_y_axis()
641       The "set_y_axis()" method is used to set properties of the Y axis. The
642       properties that can be set are the same as for "set_x_axis", see above.
643
644   set_x2_axis()
645       The "set_x2_axis()" method is used to set properties of the secondary X
646       axis.  The properties that can be set are the same as for "set_x_axis",
647       see above.  The default properties for this axis are:
648
649           label_position => 'none',
650           crossing       => 'max',
651           visible        => 0,
652
653   set_y2_axis()
654       The "set_y2_axis()" method is used to set properties of the secondary Y
655       axis.  The properties that can be set are the same as for "set_x_axis",
656       see above.  The default properties for this axis are:
657
658           major_gridlines => { visible => 0 }
659
660   combine()
661       The chart "combine()" method is used to combine two charts of different
662       types, for example a column and line chart:
663
664           my $column_chart = $workbook->add_chart( type => 'column', embedded => 1 );
665
666           # Configure the data series for the primary chart.
667           $column_chart->add_series(...);
668
669           # Create a new column chart. This will use this as the secondary chart.
670           my $line_chart = $workbook->add_chart( type => 'line', embedded => 1 );
671
672           # Configure the data series for the secondary chart.
673           $line_chart->add_series(...);
674
675           # Combine the charts.
676           $column_chart->combine( $line_chart );
677
678       See "Combined Charts" for more details.
679
680   set_size()
681       The "set_size()" method is used to set the dimensions of the chart. The
682       size properties that can be set are:
683
684            width
685            height
686            x_scale
687            y_scale
688            x_offset
689            y_offset
690
691       The "width" and "height" are in pixels. The default chart width is 480
692       pixels and the default height is 288 pixels. The size of the chart can
693       be modified by setting the "width" and "height" or by setting the
694       "x_scale" and "y_scale":
695
696           $chart->set_size( width => 720, height => 576 );
697
698           # Same as:
699
700           $chart->set_size( x_scale => 1.5, y_scale => 2 );
701
702       The "x_offset" and "y_offset" position the top left corner of the chart
703       in the cell that it is inserted into.
704
705       Note: the "x_scale", "y_scale", "x_offset" and "y_offset" parameters
706       can also be set via the "insert_chart()" method:
707
708           $worksheet->insert_chart( 'E2', $chart, { x_offset =>2,    y_offset => 4,
709                                                     x_scale  => 1.5, y_scale  => 2 } );
710
711   set_title()
712       The "set_title()" method is used to set properties of the chart title.
713
714           $chart->set_title( name => 'Year End Results' );
715
716       The properties that can be set are:
717
718       •   "name"
719
720           Set the name (title) for the chart. The name is displayed above the
721           chart. The name can also be a formula such as "=Sheet1!$A$1". The
722           name property is optional. The default is to have no chart title.
723
724       •   "name_font"
725
726           Set the font properties for the chart title. See the "CHART FONTS"
727           section below.
728
729       •   "overlay"
730
731           Allow the title to be overlaid on the chart. Generally used with
732           the layout property below.
733
734       •   "layout"
735
736           Set the "(x, y)" position of the title in chart relative units:
737
738               $chart->set_title(
739                   name    => 'Title',
740                   overlay => 1,
741                   layout  => {
742                       x => 0.42,
743                       y => 0.14,
744                   }
745               );
746
747           See the "CHART LAYOUT" section below.
748
749       •   "none"
750
751           By default Excel adds an automatic chart title to charts with a
752           single series and a user defined series name. The "none" option
753           turns this default title off. It also turns off all other
754           "set_title()" options.
755
756               $chart->set_title( none => 1 );
757
758   set_legend()
759       The "set_legend()" method is used to set properties of the chart
760       legend.
761
762       The properties that can be set are:
763
764       •   "none"
765
766           The "none" option turns off the chart legend. In Excel chart
767           legends are on by default:
768
769               $chart->set_legend( none => 1 );
770
771           Note, for backward compatibility, it is also possible to turn off
772           the legend via the "position" property:
773
774               $chart->set_legend( position => 'none' );
775
776       •   "position"
777
778           Set the position of the chart legend.
779
780               $chart->set_legend( position => 'bottom' );
781
782           The default legend position is "right". The available positions
783           are:
784
785               top
786               bottom
787               left
788               right
789               top_right
790               overlay_left
791               overlay_right
792               overlay_top_right
793               none
794
795       •   "border"
796
797           Set the border properties of the legend such as colour and style.
798           See the "CHART FORMATTING" section below.
799
800       •   "fill"
801
802           Set the fill properties of the legend such as colour. See the
803           "CHART FORMATTING" section below.
804
805       •   "pattern"
806
807           Set the pattern fill properties of the legend. See the "CHART
808           FORMATTING" section below.
809
810       •   "gradient"
811
812           Set the gradient fill properties of the legend. See the "CHART
813           FORMATTING" section below.
814
815       •   "font"
816
817           Set the font properties of the chart legend:
818
819               $chart->set_legend( font => { bold => 1, italic => 1 } );
820
821           See the "CHART FONTS" section below.
822
823       •   "delete_series"
824
825           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
829               # Delete/hide series index 0 and 2 from the legend.
830               $chart->set_legend( delete_series => [0, 2] );
831
832       •   "layout"
833
834           Set the "(x, y)" position of the legend in chart relative units:
835
836               $chart->set_legend(
837                   layout => {
838                       x      => 0.80,
839                       y      => 0.37,
840                       width  => 0.12,
841                       height => 0.25,
842                   }
843               );
844
845           See the "CHART LAYOUT" section below.
846
847   set_chartarea()
848       The "set_chartarea()" method is used to set the properties of the chart
849       area.
850
851           $chart->set_chartarea(
852               border => { none  => 1 },
853               fill   => { color => 'red' }
854           );
855
856       The properties that can be set are:
857
858       •   "border"
859
860           Set the border properties of the chartarea such as colour and
861           style. See the "CHART FORMATTING" section below.
862
863       •   "fill"
864
865           Set the fill properties of the chartarea such as colour. See the
866           "CHART FORMATTING" section below.
867
868       •   "pattern"
869
870           Set the pattern fill properties of the chartarea. See the "CHART
871           FORMATTING" section below.
872
873       •   "gradient"
874
875           Set the gradient fill properties of the chartarea. See the "CHART
876           FORMATTING" section below.
877
878   set_plotarea()
879       The "set_plotarea()" method is used to set properties of the plot area
880       of a chart.
881
882           $chart->set_plotarea(
883               border => { color => 'yellow', width => 1, dash_type => 'dash' },
884               fill   => { color => '#92D050' }
885           );
886
887       The properties that can be set are:
888
889       •   "border"
890
891           Set the border properties of the plotarea such as colour and style.
892           See the "CHART FORMATTING" section below.
893
894       •   "fill"
895
896           Set the fill properties of the plotarea such as colour. See the
897           "CHART FORMATTING" section below.
898
899       •   "pattern"
900
901           Set the pattern fill properties of the plotarea. See the "CHART
902           FORMATTING" section below.
903
904       •   "gradient"
905
906           Set the gradient fill properties of the plotarea. See the "CHART
907           FORMATTING" section below.
908
909       •   "layout"
910
911           Set the "(x, y)" position of the plotarea in chart relative units:
912
913               $chart->set_plotarea(
914                   layout => {
915                       x      => 0.35,
916                       y      => 0.26,
917                       width  => 0.62,
918                       height => 0.50,
919                   }
920               );
921
922           See the "CHART LAYOUT" section below.
923
924   set_style()
925       The "set_style()" method is used to set the style of the chart to one
926       of the 42 built-in styles available on the 'Design' tab in Excel:
927
928           $chart->set_style( 4 );
929
930       The default style is 2.
931
932   set_table()
933       The "set_table()" method adds a data table below the horizontal axis
934       with the data used to plot the chart.
935
936           $chart->set_table();
937
938       The available options, with default values are:
939
940           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
946       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
949   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
953           $chart->set_up_down_bars();
954
955       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
959           $chart->set_up_down_bars(
960               up   => { fill => { color => 'green' } },
961               down => { fill => { color => 'red' } },
962           );
963
964       Up-down bars can only be applied to Line charts and to Stock charts (by
965       default).
966
967   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
971           $chart->set_drop_lines();
972
973       It is possible to format the Drop Line "line" properties if required.
974       See the "CHART FORMATTING" section below.
975
976           $chart->set_drop_lines( line => { color => 'red', dash_type => 'square_dot' } );
977
978       Drop Lines are only available in Line, Area and Stock charts.
979
980   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
984           $chart->set_high_low_lines();
985
986       It is possible to format the High-Low Line "line" properties if
987       required. See the "CHART FORMATTING" section below.
988
989           $chart->set_high_low_lines( line => { color => 'red' } );
990
991       High-Low Lines are only available in Line and Stock charts.
992
993   show_blanks_as()
994       The "show_blanks_as()" method controls how blank data is displayed in a
995       chart.
996
997           $chart->show_blanks_as( 'span' );
998
999       The available options are:
1000
1001               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
1005   show_hidden_data()
1006       Display data in hidden rows or columns on the chart.
1007
1008           $chart->show_hidden_data();
1009

SERIES OPTIONS

1011       This section details the following properties of "add_series()" in more
1012       detail:
1013
1014           marker
1015           trendline
1016           y_error_bars
1017           x_error_bars
1018           data_labels
1019           points
1020           smooth
1021
1022   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
1027       The following properties can be set for "marker" formats in a chart.
1028
1029           type
1030           size
1031           border
1032           fill
1033           pattern
1034           gradient
1035
1036       The "type" property sets the type of marker that is used with a series.
1037
1038           $chart->add_series(
1039               values     => '=Sheet1!$B$1:$B$5',
1040               marker     => { type => 'diamond' },
1041           );
1042
1043       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
1046           automatic
1047           none
1048           square
1049           diamond
1050           triangle
1051           x
1052           star
1053           short_dash
1054           long_dash
1055           circle
1056           plus
1057
1058       The "automatic" type is a special case which turns on a marker using
1059       the default marker style for the particular series number.
1060
1061           $chart->add_series(
1062               values     => '=Sheet1!$B$1:$B$5',
1063               marker     => { type => 'automatic' },
1064           );
1065
1066       If "automatic" is on then other marker properties such as size, border
1067       or fill cannot be set.
1068
1069       The "size" property sets the size of the marker and is generally used
1070       in conjunction with "type".
1071
1072           $chart->add_series(
1073               values     => '=Sheet1!$B$1:$B$5',
1074               marker     => { type => 'diamond', size => 7 },
1075           );
1076
1077       Nested "border" and "fill" properties can also be set for a marker. See
1078       the "CHART FORMATTING" section below.
1079
1080           $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
1090   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
1094       The following properties can be set for trendlines in a chart series.
1095
1096           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
1107       The "type" property sets the type of trendline in the series.
1108
1109           $chart->add_series(
1110               values     => '=Sheet1!$B$1:$B$5',
1111               trendline  => { type => 'linear' },
1112           );
1113
1114       The available "trendline" types are:
1115
1116           exponential
1117           linear
1118           log
1119           moving_average
1120           polynomial
1121           power
1122
1123       A "polynomial" trendline can also specify the "order" of the
1124       polynomial. The default value is 2.
1125
1126           $chart->add_series(
1127               values    => '=Sheet1!$B$1:$B$5',
1128               trendline => {
1129                   type  => 'polynomial',
1130                   order => 3,
1131               },
1132           );
1133
1134       A "moving_average" trendline can also specify the "period" of the
1135       moving average. The default value is 2.
1136
1137           $chart->add_series(
1138               values     => '=Sheet1!$B$1:$B$5',
1139               trendline  => {
1140                   type   => 'moving_average',
1141                   period => 3,
1142               },
1143           );
1144
1145       The "forward" and "backward" properties set the forecast period of the
1146       trendline.
1147
1148           $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
1157       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
1162           $chart->add_series(
1163               values    => '=Sheet1!$B$1:$B$5',
1164               trendline => {
1165                   type => 'linear',
1166                   name => 'Interpolated trend',
1167               },
1168           );
1169
1170       The "intercept" property sets the point where the trendline crosses the
1171       Y (value) axis:
1172
1173           $chart->add_series(
1174               values    => '=Sheet1!$B$1:$B$5',
1175               trendline => {
1176                   type      => 'linear',
1177                   intercept => 0.8,
1178               },
1179           );
1180
1181       The "display_equation" property displays the trendline equation on the
1182       chart.
1183
1184           $chart->add_series(
1185               values    => '=Sheet1!$B$1:$B$5',
1186               trendline => {
1187                   type             => 'linear',
1188                   display_equation => 1,
1189               },
1190           );
1191
1192       The "display_r_squared" property displays the R squared value of the
1193       trendline on the chart.
1194
1195           $chart->add_series(
1196               values    => '=Sheet1!$B$1:$B$5',
1197               trendline => {
1198                   type              => 'linear',
1199                   display_r_squared => 1
1200               },
1201           );
1202
1203       Several of these properties can be set in one go:
1204
1205           $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
1224       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
1227   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
1233       The following properties can be set for error bars in a chart series.
1234
1235           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
1243       The "type" property sets the type of error bars in the series.
1244
1245           $chart->add_series(
1246               values       => '=Sheet1!$B$1:$B$5',
1247               y_error_bars => { type => 'standard_error' },
1248           );
1249
1250       The available error bars types are available:
1251
1252           fixed
1253           percentage
1254           standard_deviation
1255           standard_error
1256           custom
1257
1258       All error bar types, except for "standard_error" and "custom" must also
1259       have a value associated with it for the error bounds:
1260
1261           $chart->add_series(
1262               values       => '=Sheet1!$B$1:$B$5',
1263               y_error_bars => {
1264                   type  => 'percentage',
1265                   value => 5,
1266               },
1267           );
1268
1269       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
1273           $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
1283           # or
1284
1285
1286           $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
1296       Note, as in Excel the items in the "minus_values" do not need to be
1297       negative.
1298
1299       The "direction" property sets the direction of the error bars. It
1300       should be one of the following:
1301
1302           plus    # Positive direction only.
1303           minus   # Negative direction only.
1304           both    # Plus and minus directions, The default.
1305
1306       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
1309           $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
1319   Data Labels
1320       Data labels can be added to a chart series to indicate the values of
1321       the plotted data points.
1322
1323       The following properties can be set for "data_labels" formats in a
1324       chart.
1325
1326           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
1342       The "value" property turns on the Value data label for a series.
1343
1344           $chart->add_series(
1345               values      => '=Sheet1!$B$1:$B$5',
1346               data_labels => { value => 1 },
1347           );
1348
1349       The "category" property turns on the Category Name data label for a
1350       series.
1351
1352           $chart->add_series(
1353               values      => '=Sheet1!$B$1:$B$5',
1354               data_labels => { category => 1 },
1355           );
1356
1357       The "series_name" property turns on the Series Name data label for a
1358       series.
1359
1360           $chart->add_series(
1361               values      => '=Sheet1!$B$1:$B$5',
1362               data_labels => { series_name => 1 },
1363           );
1364
1365       The "position" property is used to position the data label for a
1366       series.
1367
1368           $chart->add_series(
1369               values      => '=Sheet1!$B$1:$B$5',
1370               data_labels => { value => 1, position => 'center' },
1371           );
1372
1373       In Excel the data label positions vary for different chart types. The
1374       allowable positions are:
1375
1376           |  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
1390       Note: The * indicates the default position for each chart type in
1391       Excel, if a position isn't specified.
1392
1393       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
1397           $chart->add_series(
1398               values      => '=Sheet1!$B$1:$B$5',
1399               data_labels => { percentage => 1 },
1400           );
1401
1402       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
1405           $chart->add_series(
1406               values      => '=Sheet1!$B$1:$B$5',
1407               data_labels => { value => 1, leader_lines => 1 },
1408           );
1409
1410       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
1416       The "separator" property is used to change the separator between
1417       multiple data label items:
1418
1419           $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
1425       The separator value must be one of the following strings:
1426
1427                   ','
1428                   ';'
1429                   '.'
1430                   "\n"
1431                   ' '
1432
1433       The "legend_key" property is used to turn on  Legend Key for the data
1434       label for a series:
1435
1436           $chart->add_series(
1437               values      => '=Sheet1!$B$1:$B$5',
1438               data_labels => { value => 1, legend_key => 1 },
1439           );
1440
1441       The "num_format" property is used to set the number format for the data
1442       labels.
1443
1444           $chart->add_series(
1445               values      => '=Sheet1!$A$1:$A$5',
1446               data_labels => { value => 1, num_format => '#,##0.00' },
1447           );
1448
1449       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
1454       The "font" property is used to set the font properties of the data
1455       labels in a series:
1456
1457           $chart->add_series(
1458               values      => '=Sheet1!$A$1:$A$5',
1459               data_labels => {
1460                   value => 1,
1461                   font  => { name => 'Consolas' }
1462               },
1463           );
1464
1465       The "font" property is also used to rotate the data labels in a series:
1466
1467           $chart->add_series(
1468               values      => '=Sheet1!$A$1:$A$5',
1469               data_labels => {
1470                   value => 1,
1471                   font  => { rotation => 45 }
1472               },
1473           );
1474
1475       See the "CHART FONTS" section below.
1476
1477       The "border" property sets the border properties of the data labels
1478       such as colour and style. See the "CHART FORMATTING" section below.
1479
1480       The "fill" property sets the fill properties of the data labels such as
1481       colour. See the "CHART FORMATTING" section below.
1482
1483       Example of setting data label formatting:
1484
1485           $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
1493       The "pattern" property sets the pattern properties of the data labels.
1494       See the "CHART FORMATTING" section below.
1495
1496       The "gradient" property sets the gradient properties of the data
1497       labels. See the "CHART FORMATTING" section below.
1498
1499       The "custom" property is used to set the properties of individual data
1500       labels, see below.
1501
1502   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
1507           my $custom_labels = [
1508               { value => 'Jan' },
1509               { value => 'Feb' },
1510               { value => 'Mar' },
1511               { value => 'Apr' },
1512               { value => 'May' },
1513               { value => 'Jun' },
1514           ];
1515
1516           $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
1522       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
1526           my $custom_labels = [
1527               undef,
1528               { value => 'Feb' },
1529               { value => 'Mar' },
1530               { value => 'Apr' },
1531           ];
1532
1533       The property elements of the "custom" lists should be dicts with the
1534       following allowable keys/sub-properties:
1535
1536           value
1537           font
1538           border
1539           fill
1540           pattern
1541           gradient
1542           delete
1543
1544       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
1547           $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
1556       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
1559           $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
1568       The "border" property sets the border properties of the data labels
1569       such as colour and style. See the "CHART FORMATTING" section below.
1570
1571       The "fill" property sets the fill properties of the data labels such as
1572       colour. See the "CHART FORMATTING" section below.
1573
1574       Example of setting custom data label formatting:
1575
1576           $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
1585       The "pattern" property sets the pattern properties of the data labels.
1586       See the "CHART FORMATTING" section below.
1587
1588       The "gradient" property sets the gradient properties of the data
1589       labels. See the "CHART FORMATTING" section below.
1590
1591       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
1595           $custom_labels = [
1596               undef,
1597               { delete => 1 },
1598               { delete => 1 },
1599               { delete => 1 },
1600               { delete => 1 },
1601               undef,
1602           ];
1603
1604   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
1610       In these cases it is possible to use the "points" property of
1611       "add_series()":
1612
1613           $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
1622       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
1626           # 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
1636           # Format the first point only.
1637           $chart->add_series(
1638               values => '=Sheet1!$A$1:$A$3',
1639               points => [ { fill => { color => '#FF0000' } } ],
1640           );
1641
1642   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
1646           $chart->add_series( values => '=Sheet1!$C$1:$C$5',
1647                               smooth => 1 );
1648

CHART FORMATTING

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
1655           line
1656           border
1657           fill
1658           pattern
1659           gradient
1660
1661       Chart formatting properties are generally set using hash refs.
1662
1663           $chart->add_series(
1664               values     => '=Sheet1!$B$1:$B$5',
1665               line       => { color => 'blue' },
1666           );
1667
1668       In some cases the format properties can be nested. For example a
1669       "marker" may contain "border" and "fill" sub-properties.
1670
1671           $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
1682   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
1686       The following properties can be set for "line" formats in a chart.
1687
1688           none
1689           color
1690           width
1691           dash_type
1692           transparency
1693
1694       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
1698           $chart->add_series(
1699               values     => '=Sheet1!$B$1:$B$5',
1700               line       => { none => 1 },
1701           );
1702
1703       The "color" property sets the color of the "line".
1704
1705           $chart->add_series(
1706               values     => '=Sheet1!$B$1:$B$5',
1707               line       => { color => 'red' },
1708           );
1709
1710       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
1714           $chart->add_series(
1715               line       => { color => '#FF0000' },
1716           );
1717
1718       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
1721           $chart->add_series(
1722               values     => '=Sheet1!$B$1:$B$5',
1723               line       => { width => 3.25 },
1724           );
1725
1726       The "dash_type" property sets the dash style of the line.
1727
1728           $chart->add_series(
1729               values     => '=Sheet1!$B$1:$B$5',
1730               line       => { dash_type => 'dash_dot' },
1731           );
1732
1733       The following "dash_type" values are available. They are shown in the
1734       order that they appear in the Excel dialog.
1735
1736           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
1745       The default line style is "solid".
1746
1747       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
1751           $chart->add_series(
1752               values     => '=Sheet1!$B$1:$B$5',
1753               line       => { color => 'yellow', transparency => 50 },
1754           );
1755
1756       More than one "line" property can be specified at a time:
1757
1758           $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
1767   Border
1768       The "border" property is a synonym for "line".
1769
1770       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
1775   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
1779       The following properties can be set for "fill" formats in a chart.
1780
1781           none
1782           color
1783           transparency
1784
1785       The "none" property is used to turn the "fill" property off (it is
1786       generally on by default).
1787
1788           $chart->add_series(
1789               values     => '=Sheet1!$B$1:$B$5',
1790               fill       => { none => 1 },
1791           );
1792
1793       The "color" property sets the colour of the "fill" area.
1794
1795           $chart->add_series(
1796               values     => '=Sheet1!$B$1:$B$5',
1797               fill       => { color => 'red' },
1798           );
1799
1800       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
1804           $chart->add_series(
1805               fill       => { color => '#FF0000' },
1806           );
1807
1808       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
1812           $chart->set_chartarea( fill => { color => 'yellow', transparency => 75 } );
1813
1814       The "fill" format is generally used in conjunction with a "border"
1815       format which has the same properties as a "line" format.
1816
1817           $chart->add_series(
1818               values     => '=Sheet1!$B$1:$B$5',
1819               border     => { color => 'red' },
1820               fill       => { color => 'yellow' },
1821           );
1822
1823   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
1828       The following properties can be set for "pattern" fill formats in a
1829       chart:
1830
1831           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
1835       For example:
1836
1837           $chart->set_plotarea(
1838               pattern => {
1839                   pattern  => 'percent_5',
1840                   fg_color => 'red',
1841                   bg_color => 'yellow',
1842               }
1843           );
1844
1845       The following patterns can be applied:
1846
1847           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
1896       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
1901       The background color, "bg_color", is optional and defaults to black.
1902
1903       If a pattern fill is used on a chart object it overrides the solid fill
1904       properties of the object.
1905
1906   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
1911       The following properties can be set for "gradient" fill formats in a
1912       chart:
1913
1914           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
1919       The "colors" property sets a list of colors that define the "gradient":
1920
1921           $chart->set_plotarea(
1922               gradient => { colors => [ '#DDEBCF', '#9CB86E', '#156B13' ] }
1923           );
1924
1925       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
1928       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
1933           $chart->add_series(
1934               values   => '=Sheet1!$A$1:$A$5',
1935               gradient => { colors => [ 'red', 'green' ] }
1936           );
1937
1938       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
1943           $chart->add_series(
1944               values   => '=Sheet1!$A$1:$A$5',
1945               gradient => {
1946                   colors    => [ '#DDEBCF', '#156B13' ],
1947                   positions => [ 10,        90 ],
1948               }
1949           );
1950
1951       The "type" property can have one of the following values:
1952
1953           linear        (the default)
1954           radial
1955           rectangular
1956           path
1957
1958       For example:
1959
1960           $chart->add_series(
1961               values   => '=Sheet1!$A$1:$A$5',
1962               gradient => {
1963                   colors => [ '#DDEBCF', '#9CB86E', '#156B13' ],
1964                   type   => 'radial'
1965               }
1966           );
1967
1968       If "type" isn't specified it defaults to "linear".
1969
1970       For a "linear" fill the angle of the gradient can also be specified:
1971
1972           $chart->add_series(
1973               values   => '=Sheet1!$A$1:$A$5',
1974               gradient => { colors => [ '#DDEBCF', '#9CB86E', '#156B13' ],
1975                             angle => 30 }
1976           );
1977
1978       The default angle is 90 degrees.
1979
1980       If gradient fill is used on a chart object it overrides the solid fill
1981       and pattern fill properties of the object.
1982

CHART FONTS

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
1990           name
1991           size
1992           bold
1993           italic
1994           underline
1995           rotation
1996           color
1997
1998       The following explains the available font properties:
1999
2000       •   "name"
2001
2002           Set the font name:
2003
2004               $chart->set_x_axis( num_font => { name => 'Arial' } );
2005
2006       •   "size"
2007
2008           Set the font size:
2009
2010               $chart->set_x_axis( num_font => { name => 'Arial', size => 10 } );
2011
2012       •   "bold"
2013
2014           Set the font bold property, should be 0 or 1:
2015
2016               $chart->set_x_axis( num_font => { bold => 1 } );
2017
2018       •   "italic"
2019
2020           Set the font italic property, should be 0 or 1:
2021
2022               $chart->set_x_axis( num_font => { italic => 1 } );
2023
2024       •   "underline"
2025
2026           Set the font underline property, should be 0 or 1:
2027
2028               $chart->set_x_axis( num_font => { underline => 1 } );
2029
2030       •   "rotation"
2031
2032           Set the font rotation in the integer range -90 to 90, and 270-271:
2033
2034               $chart->set_x_axis( num_font => { rotation => 45 } );
2035
2036           This is useful for displaying large axis data such as dates in a
2037           more compact format.
2038
2039           There are 2 special case angles outside the range -90 to 90:
2040
2041           •   270: Stacked text, where the text runs from top to bottom.
2042
2043           •   271: A special variant of stacked text for East Asian fonts.
2044
2045       •   "color"
2046
2047           Set the font color property. Can be a color index, a color name or
2048           HTML style RGB colour:
2049
2050               $chart->set_x_axis( num_font => { color => 'red' } );
2051               $chart->set_y_axis( num_font => { color => '#92D050' } );
2052
2053       Here is an example of Font formatting in a Chart program:
2054
2055           # 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
2064           # 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
2077           # 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

CHART LAYOUT

2093       The position of the chart in the worksheet is controlled by the
2094       "set_size()" method shown above.
2095
2096       It is also possible to change the layout of the following chart sub-
2097       objects:
2098
2099           plotarea
2100           legend
2101           title
2102           x_axis caption
2103           y_axis caption
2104
2105       Here are some examples:
2106
2107           $chart->set_plotarea(
2108               layout => {
2109                   x      => 0.35,
2110                   y      => 0.26,
2111                   width  => 0.62,
2112                   height => 0.50,
2113               }
2114           );
2115
2116           $chart->set_legend(
2117               layout => {
2118                   x      => 0.80,
2119                   y      => 0.37,
2120                   width  => 0.12,
2121                   height => 0.25,
2122               }
2123           );
2124
2125           $chart->set_title(
2126               name   => 'Title',
2127               layout => {
2128                   x => 0.42,
2129                   y => 0.14,
2130               }
2131           );
2132
2133           $chart->set_x_axis(
2134               name        => 'X axis',
2135               name_layout => {
2136                   x => 0.34,
2137                   y => 0.85,
2138               }
2139           );
2140
2141       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
2145       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
2148       From this the layout units are calculated as follows:
2149
2150           layout:
2151               width  = w / W
2152               height = h / H
2153               x      = a / W
2154               y      = b / H
2155
2156       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
2160       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

WORKSHEET METHODS

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
2168       In Excel::Writer::XLSX you can set chartsheet properties using the same
2169       methods that are used for Worksheet objects.
2170
2171       The following Worksheet methods are also available through a non-
2172       embedded Chart object:
2173
2174           get_name()
2175           activate()
2176           select()
2177           hide()
2178           set_first_sheet()
2179           protect()
2180           set_zoom()
2181           set_tab_color()
2182
2183           set_landscape()
2184           set_portrait()
2185           set_paper()
2186           set_margins()
2187           set_header()
2188           set_footer()
2189
2190       See Excel::Writer::XLSX for a detailed explanation of these methods.
2191

EXAMPLE

2193       Here is a complete example that demonstrates some of the available
2194       features when creating a chart.
2195
2196           #!/usr/bin/perl
2197
2198           use strict;
2199           use warnings;
2200           use Excel::Writer::XLSX;
2201
2202           my $workbook  = Excel::Writer::XLSX->new( 'chart.xlsx' );
2203           my $worksheet = $workbook->add_worksheet();
2204           my $bold      = $workbook->add_format( bold => 1 );
2205
2206           # 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
2213           ];
2214
2215           $worksheet->write( 'A1', $headings, $bold );
2216           $worksheet->write( 'A2', $data );
2217
2218           # Create a new chart object. In this case an embedded chart.
2219           my $chart = $workbook->add_chart( type => 'column', embedded => 1 );
2220
2221           # 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
2228           # 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
2236           # 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
2241           # Set an Excel chart style. Blue colors with white outline and shadow.
2242           $chart->set_style( 11 );
2243
2244           # Insert the chart into the worksheet (with an offset).
2245           $worksheet->insert_chart( 'D2', $chart, { x_offset => 25, y_offset => 10 } );
2246
2247           $workbook->close();
2248
2249           __END__
2250

Value and Category Axes

2252       Excel differentiates between a chart axis that is used for series
2253       categories and an axis that is used for series values.
2254
2255       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
2259       Since Excel treats the axes differently it also handles their
2260       formatting differently and exposes different properties for each.
2261
2262       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
2266       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
2271       Some charts such as "Scatter" and "Stock" have two value axes.
2272
2273       Date Axes are a special type of category axis which are explained
2274       below.
2275

Date Category Axes

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
2281           $chart->set_x_axis( date_axis => 1 );
2282
2283       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
2287           $chart->set_x_axis(
2288               date_axis         => 1,
2289               num_format        => 'dd/mm/yyyy',
2290           );
2291
2292       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
2296           $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
2303       For date axes it is also possible to set the type of the major and
2304       minor units:
2305
2306           $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

Secondary Axes

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
2319           #!/usr/bin/perl
2320
2321           use strict;
2322           use warnings;
2323           use Excel::Writer::XLSX;
2324
2325           my $workbook  = Excel::Writer::XLSX->new( 'chart_secondary_axis.xlsx' );
2326           my $worksheet = $workbook->add_worksheet();
2327
2328           # 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
2333           ];
2334
2335           $worksheet->write( 'A1', $data );
2336
2337           # Create a new chart object. In this case an embedded chart.
2338           my $chart = $workbook->add_chart( type => 'line', embedded => 1 );
2339
2340           # Configure a series with a secondary axis
2341           $chart->add_series(
2342               values  => '=Sheet1!$A$1:$A$6',
2343               y2_axis => 1,
2344           );
2345
2346           $chart->add_series(
2347               values => '=Sheet1!$B$1:$B$6',
2348           );
2349
2350
2351           # Insert the chart into the worksheet.
2352           $worksheet->insert_chart( 'D2', $chart );
2353
2354           $workbook->close();
2355
2356           __END__
2357
2358       It is also possible to have a secondary, combined, chart either with a
2359       shared or secondary axis, see below.
2360

Combined Charts

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
2366       Here is a simpler example:
2367
2368           use strict;
2369           use warnings;
2370           use Excel::Writer::XLSX;
2371
2372           my $workbook  = Excel::Writer::XLSX->new( 'chart_combined.xlsx' );
2373           my $worksheet = $workbook->add_worksheet();
2374           my $bold      = $workbook->add_format( bold => 1 );
2375
2376           # 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
2383           ];
2384
2385           $worksheet->write( 'A1', $headings, $bold );
2386           $worksheet->write( 'A2', $data );
2387
2388           #
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
2393           # 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
2396           # 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
2403           # 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
2406           # 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
2413           # Combine the charts.
2414           $column_chart->combine( $line_chart );
2415
2416           # 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
2422
2423           # Insert the chart into the worksheet
2424           $worksheet->insert_chart( 'E2', $column_chart );
2425
2426           $workbook->close();
2427
2428       The secondary chart can also be placed on a secondary axis using the
2429       methods shown in the previous section.
2430
2431       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
2436           ...
2437
2438           $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
2445           ...
2446
2447           # Note: the y2 properites are on the secondary chart.
2448           $line_chart2->set_y2_axis( name => 'Target length (mm)' );
2449
2450       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
2457       See also "chart_combined.pl" and "chart_pareto.pl" examples in the
2458       distro for more detailed examples.
2459
2460       There are some limitations on combined charts:
2461
2462       •   Pie charts cannot currently be combined.
2463
2464       •   Scatter charts cannot currently be used as a primary chart but they
2465           can be used as a secondary chart.
2466
2467       •   Bar charts can only combined secondary charts on a secondary axis.
2468           This is an Excel limitation.
2469

AUTHOR

2471       John McNamara jmcnamara@cpan.org
2472
2474       Copyright MM-MMXX, John McNamara.
2475
2476       All Rights Reserved. This module is free software. It may be used,
2477       redistributed and/or modified under the same terms as Perl itself.
2478
2479
2480
2481perl v5.32.1                      2021-01-27     Excel::Writer::XLSX::Chart(3)
Impressum