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           radar
120               with_markers
121               filled
122
123       More charts and sub-types will be supported in time. See the "TODO"
124       section.
125

CHART METHODS

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

SERIES OPTIONS

1009       This section details the following properties of "add_series()" in more
1010       detail:
1011
1012           marker
1013           trendline
1014           y_error_bars
1015           x_error_bars
1016           data_labels
1017           points
1018           smooth
1019
1020   Marker
1021       The marker format specifies the properties of the markers used to
1022       distinguish series on a chart. In general only Line and Scatter chart
1023       types and trendlines use markers.
1024
1025       The following properties can be set for "marker" formats in a chart.
1026
1027           type
1028           size
1029           border
1030           fill
1031           pattern
1032           gradient
1033
1034       The "type" property sets the type of marker that is used with a series.
1035
1036           $chart->add_series(
1037               values     => '=Sheet1!$B$1:$B$5',
1038               marker     => { type => 'diamond' },
1039           );
1040
1041       The following "type" properties can be set for "marker" formats in a
1042       chart. These are shown in the same order as in the Excel format dialog.
1043
1044           automatic
1045           none
1046           square
1047           diamond
1048           triangle
1049           x
1050           star
1051           short_dash
1052           long_dash
1053           circle
1054           plus
1055
1056       The "automatic" type is a special case which turns on a marker using
1057       the default marker style for the particular series number.
1058
1059           $chart->add_series(
1060               values     => '=Sheet1!$B$1:$B$5',
1061               marker     => { type => 'automatic' },
1062           );
1063
1064       If "automatic" is on then other marker properties such as size, border
1065       or fill cannot be set.
1066
1067       The "size" property sets the size of the marker and is generally used
1068       in conjunction with "type".
1069
1070           $chart->add_series(
1071               values     => '=Sheet1!$B$1:$B$5',
1072               marker     => { type => 'diamond', size => 7 },
1073           );
1074
1075       Nested "border" and "fill" properties can also be set for a marker. See
1076       the "CHART FORMATTING" section below.
1077
1078           $chart->add_series(
1079               values     => '=Sheet1!$B$1:$B$5',
1080               marker     => {
1081                   type    => 'square',
1082                   size    => 5,
1083                   border  => { color => 'red' },
1084                   fill    => { color => 'yellow' },
1085               },
1086           );
1087
1088   Trendline
1089       A trendline can be added to a chart series to indicate trends in the
1090       data such as a moving average or a polynomial fit.
1091
1092       The following properties can be set for trendlines in a chart series.
1093
1094           type
1095           order               (for polynomial trends)
1096           period              (for moving average)
1097           forward             (for all except moving average)
1098           backward            (for all except moving average)
1099           name
1100           line
1101           intercept           (for exponential, linear and polynomial only)
1102           display_equation    (for all except moving average)
1103           display_r_squared   (for all except moving average)
1104
1105       The "type" property sets the type of trendline in the series.
1106
1107           $chart->add_series(
1108               values     => '=Sheet1!$B$1:$B$5',
1109               trendline  => { type => 'linear' },
1110           );
1111
1112       The available "trendline" types are:
1113
1114           exponential
1115           linear
1116           log
1117           moving_average
1118           polynomial
1119           power
1120
1121       A "polynomial" trendline can also specify the "order" of the
1122       polynomial. The default value is 2.
1123
1124           $chart->add_series(
1125               values    => '=Sheet1!$B$1:$B$5',
1126               trendline => {
1127                   type  => 'polynomial',
1128                   order => 3,
1129               },
1130           );
1131
1132       A "moving_average" trendline can also specify the "period" of the
1133       moving average. The default value is 2.
1134
1135           $chart->add_series(
1136               values     => '=Sheet1!$B$1:$B$5',
1137               trendline  => {
1138                   type   => 'moving_average',
1139                   period => 3,
1140               },
1141           );
1142
1143       The "forward" and "backward" properties set the forecast period of the
1144       trendline.
1145
1146           $chart->add_series(
1147               values    => '=Sheet1!$B$1:$B$5',
1148               trendline => {
1149                   type     => 'linear',
1150                   forward  => 0.5,
1151                   backward => 0.5,
1152               },
1153           );
1154
1155       The "name" property sets an optional name for the trendline that will
1156       appear in the chart legend. If it isn't specified the Excel default
1157       name will be displayed. This is usually a combination of the trendline
1158       type and the series name.
1159
1160           $chart->add_series(
1161               values    => '=Sheet1!$B$1:$B$5',
1162               trendline => {
1163                   type => 'linear',
1164                   name => 'Interpolated trend',
1165               },
1166           );
1167
1168       The "intercept" property sets the point where the trendline crosses the
1169       Y (value) axis:
1170
1171           $chart->add_series(
1172               values    => '=Sheet1!$B$1:$B$5',
1173               trendline => {
1174                   type      => 'linear',
1175                   intercept => 0.8,
1176               },
1177           );
1178
1179       The "display_equation" property displays the trendline equation on the
1180       chart.
1181
1182           $chart->add_series(
1183               values    => '=Sheet1!$B$1:$B$5',
1184               trendline => {
1185                   type             => 'linear',
1186                   display_equation => 1,
1187               },
1188           );
1189
1190       The "display_r_squared" property displays the R squared value of the
1191       trendline on the chart.
1192
1193           $chart->add_series(
1194               values    => '=Sheet1!$B$1:$B$5',
1195               trendline => {
1196                   type              => 'linear',
1197                   display_r_squared => 1
1198               },
1199           );
1200
1201       Several of these properties can be set in one go:
1202
1203           $chart->add_series(
1204               values     => '=Sheet1!$B$1:$B$5',
1205               trendline  => {
1206                   type              => 'polynomial',
1207                   name              => 'My trend name',
1208                   order             => 2,
1209                   forward           => 0.5,
1210                   backward          => 0.5,
1211                   intercept         => 1.5,
1212                   display_equation  => 1,
1213                   display_r_squared => 1,
1214                   line              => {
1215                       color     => 'red',
1216                       width     => 1,
1217                       dash_type => 'long_dash',
1218                   }
1219               },
1220           );
1221
1222       Trendlines cannot be added to series in a stacked chart or pie chart,
1223       radar chart, doughnut or (when implemented) to 3D, or surface charts.
1224
1225   Error Bars
1226       Error bars can be added to a chart series to indicate error bounds in
1227       the data. The error bars can be vertical "y_error_bars" (the most
1228       common type) or horizontal "x_error_bars" (for Bar and Scatter charts
1229       only).
1230
1231       The following properties can be set for error bars in a chart series.
1232
1233           type
1234           value        (for all types except standard error and custom)
1235           plus_values  (for custom only)
1236           minus_values (for custom only)
1237           direction
1238           end_style
1239           line
1240
1241       The "type" property sets the type of error bars in the series.
1242
1243           $chart->add_series(
1244               values       => '=Sheet1!$B$1:$B$5',
1245               y_error_bars => { type => 'standard_error' },
1246           );
1247
1248       The available error bars types are available:
1249
1250           fixed
1251           percentage
1252           standard_deviation
1253           standard_error
1254           custom
1255
1256       All error bar types, except for "standard_error" and "custom" must also
1257       have a value associated with it for the error bounds:
1258
1259           $chart->add_series(
1260               values       => '=Sheet1!$B$1:$B$5',
1261               y_error_bars => {
1262                   type  => 'percentage',
1263                   value => 5,
1264               },
1265           );
1266
1267       The "custom" error bar type must specify "plus_values" and
1268       "minus_values" which should either by a "Sheet1!$A$1:$A$5" type range
1269       formula or an arrayref of values:
1270
1271           $chart->add_series(
1272               categories   => '=Sheet1!$A$1:$A$5',
1273               values       => '=Sheet1!$B$1:$B$5',
1274               y_error_bars => {
1275                   type         => 'custom',
1276                   plus_values  => '=Sheet1!$C$1:$C$5',
1277                   minus_values => '=Sheet1!$D$1:$D$5',
1278               },
1279           );
1280
1281           # or
1282
1283
1284           $chart->add_series(
1285               categories   => '=Sheet1!$A$1:$A$5',
1286               values       => '=Sheet1!$B$1:$B$5',
1287               y_error_bars => {
1288                   type         => 'custom',
1289                   plus_values  => [1, 1, 1, 1, 1],
1290                   minus_values => [2, 2, 2, 2, 2],
1291               },
1292           );
1293
1294       Note, as in Excel the items in the "minus_values" do not need to be
1295       negative.
1296
1297       The "direction" property sets the direction of the error bars. It
1298       should be one of the following:
1299
1300           plus    # Positive direction only.
1301           minus   # Negative direction only.
1302           both    # Plus and minus directions, The default.
1303
1304       The "end_style" property sets the style of the error bar end cap. The
1305       options are 1 (the default) or 0 (for no end cap):
1306
1307           $chart->add_series(
1308               values       => '=Sheet1!$B$1:$B$5',
1309               y_error_bars => {
1310                   type      => 'fixed',
1311                   value     => 2,
1312                   end_style => 0,
1313                   direction => 'minus'
1314               },
1315           );
1316
1317   Data Labels
1318       Data labels can be added to a chart series to indicate the values of
1319       the plotted data points.
1320
1321       The following properties can be set for "data_labels" formats in a
1322       chart.
1323
1324           value
1325           category
1326           series_name
1327           position
1328           percentage
1329           leader_lines
1330           separator
1331           legend_key
1332           num_format
1333           font
1334
1335       The "value" property turns on the Value data label for a series.
1336
1337           $chart->add_series(
1338               values      => '=Sheet1!$B$1:$B$5',
1339               data_labels => { value => 1 },
1340           );
1341
1342       The "category" property turns on the Category Name data label for a
1343       series.
1344
1345           $chart->add_series(
1346               values      => '=Sheet1!$B$1:$B$5',
1347               data_labels => { category => 1 },
1348           );
1349
1350       The "series_name" property turns on the Series Name data label for a
1351       series.
1352
1353           $chart->add_series(
1354               values      => '=Sheet1!$B$1:$B$5',
1355               data_labels => { series_name => 1 },
1356           );
1357
1358       The "position" property is used to position the data label for a
1359       series.
1360
1361           $chart->add_series(
1362               values      => '=Sheet1!$B$1:$B$5',
1363               data_labels => { value => 1, position => 'center' },
1364           );
1365
1366       In Excel the data label positions vary for different chart types. The
1367       allowable positions are:
1368
1369           |  Position     |  Line     |  Bar      |  Pie      |  Area     |
1370           |               |  Scatter  |  Column   |  Doughnut |  Radar    |
1371           |               |  Stock    |           |           |           |
1372           |---------------|-----------|-----------|-----------|-----------|
1373           |  center       |  Yes      |  Yes      |  Yes      |  Yes*     |
1374           |  right        |  Yes*     |           |           |           |
1375           |  left         |  Yes      |           |           |           |
1376           |  above        |  Yes      |           |           |           |
1377           |  below        |  Yes      |           |           |           |
1378           |  inside_base  |           |  Yes      |           |           |
1379           |  inside_end   |           |  Yes      |  Yes      |           |
1380           |  outside_end  |           |  Yes*     |  Yes      |           |
1381           |  best_fit     |           |           |  Yes*     |           |
1382
1383       Note: The * indicates the default position for each chart type in
1384       Excel, if a position isn't specified.
1385
1386       The "percentage" property is used to turn on the display of data labels
1387       as a Percentage for a series. It is mainly used for pie and doughnut
1388       charts.
1389
1390           $chart->add_series(
1391               values      => '=Sheet1!$B$1:$B$5',
1392               data_labels => { percentage => 1 },
1393           );
1394
1395       The "leader_lines" property is used to turn on  Leader Lines for the
1396       data label for a series. It is mainly used for pie charts.
1397
1398           $chart->add_series(
1399               values      => '=Sheet1!$B$1:$B$5',
1400               data_labels => { value => 1, leader_lines => 1 },
1401           );
1402
1403       Note: Even when leader lines are turned on they aren't automatically
1404       visible in Excel or Excel::Writer::XLSX. Due to an Excel limitation (or
1405       design) leader lines only appear if the data label is moved manually or
1406       if the data labels are very close and need to be adjusted
1407       automatically.
1408
1409       The "separator" property is used to change the separator between
1410       multiple data label items:
1411
1412           $chart->add_series(
1413               values      => '=Sheet1!$B$1:$B$5',
1414               data_labels => { percentage => 1 },
1415               data_labels => { value => 1, category => 1, separator => "\n" },
1416           );
1417
1418       The separator value must be one of the following strings:
1419
1420                   ','
1421                   ';'
1422                   '.'
1423                   "\n"
1424                   ' '
1425
1426       The "legend_key" property is used to turn on  Legend Key for the data
1427       label for a series:
1428
1429           $chart->add_series(
1430               values      => '=Sheet1!$B$1:$B$5',
1431               data_labels => { value => 1, legend_key => 1 },
1432           );
1433
1434       The "num_format" property is used to set the number format for the data
1435       labels.
1436
1437           $chart->add_series(
1438               values      => '=Sheet1!$A$1:$A$5',
1439               data_labels => { value => 1, num_format => '#,##0.00' },
1440           );
1441
1442       The number format is similar to the Worksheet Cell Format "num_format"
1443       apart from the fact that a format index cannot be used. The explicit
1444       format string must be used as shown above. See "set_num_format()" in
1445       Excel::Writer::XLSX for more information.
1446
1447       The "font" property is used to set the font properties of the data
1448       labels in a series:
1449
1450           $chart->add_series(
1451               values      => '=Sheet1!$A$1:$A$5',
1452               data_labels => {
1453                   value => 1,
1454                   font  => { name => 'Consolas' }
1455               },
1456           );
1457
1458       The "font" property is also used to rotate the data labels in a series:
1459
1460           $chart->add_series(
1461               values      => '=Sheet1!$A$1:$A$5',
1462               data_labels => {
1463                   value => 1,
1464                   font  => { rotation => 45 }
1465               },
1466           );
1467
1468       See the "CHART FONTS" section below.
1469
1470   Points
1471       In general formatting is applied to an entire series in a chart.
1472       However, it is occasionally required to format individual points in a
1473       series. In particular this is required for Pie and Doughnut charts
1474       where each segment is represented by a point.
1475
1476       In these cases it is possible to use the "points" property of
1477       "add_series()":
1478
1479           $chart->add_series(
1480               values => '=Sheet1!$A$1:$A$3',
1481               points => [
1482                   { fill => { color => '#FF0000' } },
1483                   { fill => { color => '#CC0000' } },
1484                   { fill => { color => '#990000' } },
1485               ],
1486           );
1487
1488       The "points" property takes an array ref of format options (see the
1489       "CHART FORMATTING" section below). To assign default properties to
1490       points in a series pass "undef" values in the array ref:
1491
1492           # Format point 3 of 3 only.
1493           $chart->add_series(
1494               values => '=Sheet1!$A$1:$A$3',
1495               points => [
1496                   undef,
1497                   undef,
1498                   { fill => { color => '#990000' } },
1499               ],
1500           );
1501
1502           # Format the first point only.
1503           $chart->add_series(
1504               values => '=Sheet1!$A$1:$A$3',
1505               points => [ { fill => { color => '#FF0000' } } ],
1506           );
1507
1508   Smooth
1509       The "smooth" option is used to set the smooth property of a line
1510       series. It is only applicable to the "Line" and "Scatter" chart types.
1511
1512           $chart->add_series( values => '=Sheet1!$C$1:$C$5',
1513                               smooth => 1 );
1514

CHART FORMATTING

1516       The following chart formatting properties can be set for any chart
1517       object that they apply to (and that are supported by
1518       Excel::Writer::XLSX) such as chart lines, column fill areas, plot area
1519       borders, markers, gridlines and other chart elements documented above.
1520
1521           line
1522           border
1523           fill
1524           pattern
1525           gradient
1526
1527       Chart formatting properties are generally set using hash refs.
1528
1529           $chart->add_series(
1530               values     => '=Sheet1!$B$1:$B$5',
1531               line       => { color => 'blue' },
1532           );
1533
1534       In some cases the format properties can be nested. For example a
1535       "marker" may contain "border" and "fill" sub-properties.
1536
1537           $chart->add_series(
1538               values     => '=Sheet1!$B$1:$B$5',
1539               line       => { color => 'blue' },
1540               marker     => {
1541                   type    => 'square',
1542                   size    => 5,
1543                   border  => { color => 'red' },
1544                   fill    => { color => 'yellow' },
1545               },
1546           );
1547
1548   Line
1549       The line format is used to specify properties of line objects that
1550       appear in a chart such as a plotted line on a chart or a border.
1551
1552       The following properties can be set for "line" formats in a chart.
1553
1554           none
1555           color
1556           width
1557           dash_type
1558           transparency
1559
1560       The "none" property is uses to turn the "line" off (it is always on by
1561       default except in Scatter charts). This is useful if you wish to plot a
1562       series with markers but without a line.
1563
1564           $chart->add_series(
1565               values     => '=Sheet1!$B$1:$B$5',
1566               line       => { none => 1 },
1567           );
1568
1569       The "color" property sets the color of the "line".
1570
1571           $chart->add_series(
1572               values     => '=Sheet1!$B$1:$B$5',
1573               line       => { color => 'red' },
1574           );
1575
1576       The available colours are shown in the main Excel::Writer::XLSX
1577       documentation. It is also possible to set the colour of a line with a
1578       HTML style RGB colour:
1579
1580           $chart->add_series(
1581               line       => { color => '#FF0000' },
1582           );
1583
1584       The "width" property sets the width of the "line". It should be
1585       specified in increments of 0.25 of a point as in Excel.
1586
1587           $chart->add_series(
1588               values     => '=Sheet1!$B$1:$B$5',
1589               line       => { width => 3.25 },
1590           );
1591
1592       The "dash_type" property sets the dash style of the line.
1593
1594           $chart->add_series(
1595               values     => '=Sheet1!$B$1:$B$5',
1596               line       => { dash_type => 'dash_dot' },
1597           );
1598
1599       The following "dash_type" values are available. They are shown in the
1600       order that they appear in the Excel dialog.
1601
1602           solid
1603           round_dot
1604           square_dot
1605           dash
1606           dash_dot
1607           long_dash
1608           long_dash_dot
1609           long_dash_dot_dot
1610
1611       The default line style is "solid".
1612
1613       The "transparency" property sets the transparency of the "line" color
1614       in the integer range 1 - 100. The color must be set for transparency to
1615       work, it doesn't work with an automatic/default color:
1616
1617           $chart->add_series(
1618               values     => '=Sheet1!$B$1:$B$5',
1619               line       => { color => 'yellow', transparency => 50 },
1620           );
1621
1622       More than one "line" property can be specified at a time:
1623
1624           $chart->add_series(
1625               values     => '=Sheet1!$B$1:$B$5',
1626               line       => {
1627                   color     => 'red',
1628                   width     => 1.25,
1629                   dash_type => 'square_dot',
1630               },
1631           );
1632
1633   Border
1634       The "border" property is a synonym for "line".
1635
1636       It can be used as a descriptive substitute for "line" in chart types
1637       such as Bar and Column that have a border and fill style rather than a
1638       line style. In general chart objects with a "border" property will also
1639       have a fill property.
1640
1641   Solid Fill
1642       The fill format is used to specify filled areas of chart objects such
1643       as the interior of a column or the background of the chart itself.
1644
1645       The following properties can be set for "fill" formats in a chart.
1646
1647           none
1648           color
1649           transparency
1650
1651       The "none" property is used to turn the "fill" property off (it is
1652       generally on by default).
1653
1654           $chart->add_series(
1655               values     => '=Sheet1!$B$1:$B$5',
1656               fill       => { none => 1 },
1657           );
1658
1659       The "color" property sets the colour of the "fill" area.
1660
1661           $chart->add_series(
1662               values     => '=Sheet1!$B$1:$B$5',
1663               fill       => { color => 'red' },
1664           );
1665
1666       The available colours are shown in the main Excel::Writer::XLSX
1667       documentation. It is also possible to set the colour of a fill with a
1668       HTML style RGB colour:
1669
1670           $chart->add_series(
1671               fill       => { color => '#FF0000' },
1672           );
1673
1674       The "transparency" property sets the transparency of the solid fill
1675       color in the integer range 1 - 100. The color must be set for
1676       transparency to work, it doesn't work with an automatic/default color:
1677
1678           $chart->set_chartarea( fill => { color => 'yellow', transparency => 75 } );
1679
1680       The "fill" format is generally used in conjunction with a "border"
1681       format which has the same properties as a "line" format.
1682
1683           $chart->add_series(
1684               values     => '=Sheet1!$B$1:$B$5',
1685               border     => { color => 'red' },
1686               fill       => { color => 'yellow' },
1687           );
1688
1689   Pattern Fill
1690       The pattern fill format is used to specify pattern filled areas of
1691       chart objects such as the interior of a column or the background of the
1692       chart itself.
1693
1694       The following properties can be set for "pattern" fill formats in a
1695       chart:
1696
1697           pattern:   the pattern to be applied (required)
1698           fg_color:  the foreground color of the pattern (required)
1699           bg_color:  the background color (optional, defaults to white)
1700
1701       For example:
1702
1703           $chart->set_plotarea(
1704               pattern => {
1705                   pattern  => 'percent_5',
1706                   fg_color => 'red',
1707                   bg_color => 'yellow',
1708               }
1709           );
1710
1711       The following patterns can be applied:
1712
1713           percent_5
1714           percent_10
1715           percent_20
1716           percent_25
1717           percent_30
1718           percent_40
1719           percent_50
1720           percent_60
1721           percent_70
1722           percent_75
1723           percent_80
1724           percent_90
1725           light_downward_diagonal
1726           light_upward_diagonal
1727           dark_downward_diagonal
1728           dark_upward_diagonal
1729           wide_downward_diagonal
1730           wide_upward_diagonal
1731           light_vertical
1732           light_horizontal
1733           narrow_vertical
1734           narrow_horizontal
1735           dark_vertical
1736           dark_horizontal
1737           dashed_downward_diagonal
1738           dashed_upward_diagonal
1739           dashed_horizontal
1740           dashed_vertical
1741           small_confetti
1742           large_confetti
1743           zigzag
1744           wave
1745           diagonal_brick
1746           horizontal_brick
1747           weave
1748           plaid
1749           divot
1750           dotted_grid
1751           dotted_diamond
1752           shingle
1753           trellis
1754           sphere
1755           small_grid
1756           large_grid
1757           small_check
1758           large_check
1759           outlined_diamond
1760           solid_diamond
1761
1762       The foreground color, "fg_color", is a required parameter and can be a
1763       Html style "#RRGGBB" string or a limited number of named colors. The
1764       available colours are shown in the main Excel::Writer::XLSX
1765       documentation.
1766
1767       The background color, "bg_color", is optional and defaults to black.
1768
1769       If a pattern fill is used on a chart object it overrides the solid fill
1770       properties of the object.
1771
1772   Gradient Fill
1773       The gradient fill format is used to specify gradient filled areas of
1774       chart objects such as the interior of a column or the background of the
1775       chart itself.
1776
1777       The following properties can be set for "gradient" fill formats in a
1778       chart:
1779
1780           colors:    a list of colors
1781           positions: an optional list of positions for the colors
1782           type:      the optional type of gradient fill
1783           angle:     the optional angle of the linear fill
1784
1785       The "colors" property sets a list of colors that define the "gradient":
1786
1787           $chart->set_plotarea(
1788               gradient => { colors => [ '#DDEBCF', '#9CB86E', '#156B13' ] }
1789           );
1790
1791       Excel allows between 2 and 10 colors in a gradient but it is unlikely
1792       that you will require more than 2 or 3.
1793
1794       As with solid or pattern fill it is also possible to set the colors of
1795       a gradient with a Html style "#RRGGBB" string or a limited number of
1796       named colors. The available colours are shown in the main
1797       Excel::Writer::XLSX documentation:
1798
1799           $chart->add_series(
1800               values   => '=Sheet1!$A$1:$A$5',
1801               gradient => { colors => [ 'red', 'green' ] }
1802           );
1803
1804       The "positions" defines an optional list of positions, between 0 and
1805       100, of where the colors in the gradient are located. Default values
1806       are provided for "colors" lists of between 2 and 4 but they can be
1807       specified if required:
1808
1809           $chart->add_series(
1810               values   => '=Sheet1!$A$1:$A$5',
1811               gradient => {
1812                   colors    => [ '#DDEBCF', '#156B13' ],
1813                   positions => [ 10,        90 ],
1814               }
1815           );
1816
1817       The "type" property can have one of the following values:
1818
1819           linear        (the default)
1820           radial
1821           rectangular
1822           path
1823
1824       For example:
1825
1826           $chart->add_series(
1827               values   => '=Sheet1!$A$1:$A$5',
1828               gradient => {
1829                   colors => [ '#DDEBCF', '#9CB86E', '#156B13' ],
1830                   type   => 'radial'
1831               }
1832           );
1833
1834       If "type" isn't specified it defaults to "linear".
1835
1836       For a "linear" fill the angle of the gradient can also be specified:
1837
1838           $chart->add_series(
1839               values   => '=Sheet1!$A$1:$A$5',
1840               gradient => { colors => [ '#DDEBCF', '#9CB86E', '#156B13' ],
1841                             angle => 30 }
1842           );
1843
1844       The default angle is 90 degrees.
1845
1846       If gradient fill is used on a chart object it overrides the solid fill
1847       and pattern fill properties of the object.
1848

CHART FONTS

1850       The following font properties can be set for any chart object that they
1851       apply to (and that are supported by Excel::Writer::XLSX) such as chart
1852       titles, axis labels, axis numbering and data labels. They correspond to
1853       the equivalent Worksheet cell Format object properties. See
1854       "FORMAT_METHODS" in Excel::Writer::XLSX for more information.
1855
1856           name
1857           size
1858           bold
1859           italic
1860           underline
1861           rotation
1862           color
1863
1864       The following explains the available font properties:
1865
1866       ·   "name"
1867
1868           Set the font name:
1869
1870               $chart->set_x_axis( num_font => { name => 'Arial' } );
1871
1872       ·   "size"
1873
1874           Set the font size:
1875
1876               $chart->set_x_axis( num_font => { name => 'Arial', size => 10 } );
1877
1878       ·   "bold"
1879
1880           Set the font bold property, should be 0 or 1:
1881
1882               $chart->set_x_axis( num_font => { bold => 1 } );
1883
1884       ·   "italic"
1885
1886           Set the font italic property, should be 0 or 1:
1887
1888               $chart->set_x_axis( num_font => { italic => 1 } );
1889
1890       ·   "underline"
1891
1892           Set the font underline property, should be 0 or 1:
1893
1894               $chart->set_x_axis( num_font => { underline => 1 } );
1895
1896       ·   "rotation"
1897
1898           Set the font rotation in the range -90 to 90:
1899
1900               $chart->set_x_axis( num_font => { rotation => 45 } );
1901
1902           This is useful for displaying large axis data such as dates in a
1903           more compact format.
1904
1905       ·   "color"
1906
1907           Set the font color property. Can be a color index, a color name or
1908           HTML style RGB colour:
1909
1910               $chart->set_x_axis( num_font => { color => 'red' } );
1911               $chart->set_y_axis( num_font => { color => '#92D050' } );
1912
1913       Here is an example of Font formatting in a Chart program:
1914
1915           # Format the chart title.
1916           $chart->set_title(
1917               name      => 'Sales Results Chart',
1918               name_font => {
1919                   name  => 'Calibri',
1920                   color => 'yellow',
1921               },
1922           );
1923
1924           # Format the X-axis.
1925           $chart->set_x_axis(
1926               name      => 'Month',
1927               name_font => {
1928                   name  => 'Arial',
1929                   color => '#92D050'
1930               },
1931               num_font => {
1932                   name  => 'Courier New',
1933                   color => '#00B0F0',
1934               },
1935           );
1936
1937           # Format the Y-axis.
1938           $chart->set_y_axis(
1939               name      => 'Sales (1000 units)',
1940               name_font => {
1941                   name      => 'Century',
1942                   underline => 1,
1943                   color     => 'red'
1944               },
1945               num_font => {
1946                   bold   => 1,
1947                   italic => 1,
1948                   color  => '#7030A0',
1949               },
1950           );
1951

CHART LAYOUT

1953       The position of the chart in the worksheet is controlled by the
1954       "set_size()" method shown above.
1955
1956       It is also possible to change the layout of the following chart sub-
1957       objects:
1958
1959           plotarea
1960           legend
1961           title
1962           x_axis caption
1963           y_axis caption
1964
1965       Here are some examples:
1966
1967           $chart->set_plotarea(
1968               layout => {
1969                   x      => 0.35,
1970                   y      => 0.26,
1971                   width  => 0.62,
1972                   height => 0.50,
1973               }
1974           );
1975
1976           $chart->set_legend(
1977               layout => {
1978                   x      => 0.80,
1979                   y      => 0.37,
1980                   width  => 0.12,
1981                   height => 0.25,
1982               }
1983           );
1984
1985           $chart->set_title(
1986               name   => 'Title',
1987               layout => {
1988                   x => 0.42,
1989                   y => 0.14,
1990               }
1991           );
1992
1993           $chart->set_x_axis(
1994               name        => 'X axis',
1995               name_layout => {
1996                   x => 0.34,
1997                   y => 0.85,
1998               }
1999           );
2000
2001       Note that it is only possible to change the width and height for the
2002       "plotarea" and "legend" objects. For the other text based objects the
2003       width and height are changed by the font dimensions.
2004
2005       The layout units must be a float in the range "0 < x <= 1" and are
2006       expressed as a percentage of the chart dimensions as shown below:
2007
2008       From this the layout units are calculated as follows:
2009
2010           layout:
2011               width  = w / W
2012               height = h / H
2013               x      = a / W
2014               y      = b / H
2015
2016       These units are slightly cumbersome but are required by Excel so that
2017       the chart object positions remain relative to each other if the chart
2018       is resized by the user.
2019
2020       Note that for "plotarea" the origin is the top left corner in the
2021       plotarea itself and does not take into account the axes.
2022

WORKSHEET METHODS

2024       In Excel a chartsheet (i.e, a chart that isn't embedded) shares
2025       properties with data worksheets such as tab selection, headers,
2026       footers, margins, and print properties.
2027
2028       In Excel::Writer::XLSX you can set chartsheet properties using the same
2029       methods that are used for Worksheet objects.
2030
2031       The following Worksheet methods are also available through a non-
2032       embedded Chart object:
2033
2034           get_name()
2035           activate()
2036           select()
2037           hide()
2038           set_first_sheet()
2039           protect()
2040           set_zoom()
2041           set_tab_color()
2042
2043           set_landscape()
2044           set_portrait()
2045           set_paper()
2046           set_margins()
2047           set_header()
2048           set_footer()
2049
2050       See Excel::Writer::XLSX for a detailed explanation of these methods.
2051

EXAMPLE

2053       Here is a complete example that demonstrates some of the available
2054       features when creating a chart.
2055
2056           #!/usr/bin/perl
2057
2058           use strict;
2059           use warnings;
2060           use Excel::Writer::XLSX;
2061
2062           my $workbook  = Excel::Writer::XLSX->new( 'chart.xlsx' );
2063           my $worksheet = $workbook->add_worksheet();
2064           my $bold      = $workbook->add_format( bold => 1 );
2065
2066           # Add the worksheet data that the charts will refer to.
2067           my $headings = [ 'Number', 'Batch 1', 'Batch 2' ];
2068           my $data = [
2069               [ 2,  3,  4,  5,  6,  7 ],
2070               [ 10, 40, 50, 20, 10, 50 ],
2071               [ 30, 60, 70, 50, 40, 30 ],
2072
2073           ];
2074
2075           $worksheet->write( 'A1', $headings, $bold );
2076           $worksheet->write( 'A2', $data );
2077
2078           # Create a new chart object. In this case an embedded chart.
2079           my $chart = $workbook->add_chart( type => 'column', embedded => 1 );
2080
2081           # Configure the first series.
2082           $chart->add_series(
2083               name       => '=Sheet1!$B$1',
2084               categories => '=Sheet1!$A$2:$A$7',
2085               values     => '=Sheet1!$B$2:$B$7',
2086           );
2087
2088           # Configure second series. Note alternative use of array ref to define
2089           # ranges: [ $sheetname, $row_start, $row_end, $col_start, $col_end ].
2090           $chart->add_series(
2091               name       => '=Sheet1!$C$1',
2092               categories => [ 'Sheet1', 1, 6, 0, 0 ],
2093               values     => [ 'Sheet1', 1, 6, 2, 2 ],
2094           );
2095
2096           # Add a chart title and some axis labels.
2097           $chart->set_title ( name => 'Results of sample analysis' );
2098           $chart->set_x_axis( name => 'Test number' );
2099           $chart->set_y_axis( name => 'Sample length (mm)' );
2100
2101           # Set an Excel chart style. Blue colors with white outline and shadow.
2102           $chart->set_style( 11 );
2103
2104           # Insert the chart into the worksheet (with an offset).
2105           $worksheet->insert_chart( 'D2', $chart, 25, 10 );
2106
2107           $workbook->close();
2108
2109           __END__
2110

Value and Category Axes

2112       Excel differentiates between a chart axis that is used for series
2113       categories and an axis that is used for series values.
2114
2115       In the example above the X axis is the category axis and each of the
2116       values is evenly spaced. The Y axis (in this case) is the value axis
2117       and points are displayed according to their value.
2118
2119       Since Excel treats the axes differently it also handles their
2120       formatting differently and exposes different properties for each.
2121
2122       As such some of "Excel::Writer::XLSX" axis properties can be set for a
2123       value axis, some can be set for a category axis and some properties can
2124       be set for both.
2125
2126       For example the "min" and "max" properties can only be set for value
2127       axes and "reverse" can be set for both. The type of axis that a
2128       property applies to is shown in the "set_x_axis()" section of the
2129       documentation above.
2130
2131       Some charts such as "Scatter" and "Stock" have two value axes.
2132
2133       Date Axes are a special type of category axis which are explained
2134       below.
2135

Date Category Axes

2137       Date Category Axes are category axes that display time or date
2138       information. In Excel::Writer::XLSX Date Category Axes are set using
2139       the "date_axis" option:
2140
2141           $chart->set_x_axis( date_axis => 1 );
2142
2143       In general you should also specify a number format for a date axis
2144       although Excel will usually default to the same format as the data
2145       being plotted:
2146
2147           $chart->set_x_axis(
2148               date_axis         => 1,
2149               num_format        => 'dd/mm/yyyy',
2150           );
2151
2152       Excel doesn't normally allow minimum and maximum values to be set for
2153       category axes. However, date axes are an exception. The "min" and "max"
2154       values should be set as Excel times or dates:
2155
2156           $chart->set_x_axis(
2157               date_axis         => 1,
2158               min               => $worksheet->convert_date_time('2013-01-02T'),
2159               max               => $worksheet->convert_date_time('2013-01-09T'),
2160               num_format        => 'dd/mm/yyyy',
2161           );
2162
2163       For date axes it is also possible to set the type of the major and
2164       minor units:
2165
2166           $chart->set_x_axis(
2167               date_axis         => 1,
2168               minor_unit        => 4,
2169               minor_unit_type   => 'months',
2170               major_unit        => 1,
2171               major_unit_type   => 'years',
2172               num_format        => 'dd/mm/yyyy',
2173           );
2174

Secondary Axes

2176       It is possible to add a secondary axis of the same type to a chart by
2177       setting the "y2_axis" or "x2_axis" property of the series:
2178
2179           #!/usr/bin/perl
2180
2181           use strict;
2182           use warnings;
2183           use Excel::Writer::XLSX;
2184
2185           my $workbook  = Excel::Writer::XLSX->new( 'chart_secondary_axis.xlsx' );
2186           my $worksheet = $workbook->add_worksheet();
2187
2188           # Add the worksheet data that the charts will refer to.
2189           my $data = [
2190               [ 2,  3,  4,  5,  6,  7 ],
2191               [ 10, 40, 50, 20, 10, 50 ],
2192
2193           ];
2194
2195           $worksheet->write( 'A1', $data );
2196
2197           # Create a new chart object. In this case an embedded chart.
2198           my $chart = $workbook->add_chart( type => 'line', embedded => 1 );
2199
2200           # Configure a series with a secondary axis
2201           $chart->add_series(
2202               values  => '=Sheet1!$A$1:$A$6',
2203               y2_axis => 1,
2204           );
2205
2206           $chart->add_series(
2207               values => '=Sheet1!$B$1:$B$6',
2208           );
2209
2210
2211           # Insert the chart into the worksheet.
2212           $worksheet->insert_chart( 'D2', $chart );
2213
2214           $workbook->close();
2215
2216           __END__
2217
2218       It is also possible to have a secondary, combined, chart either with a
2219       shared or secondary axis, see below.
2220

Combined Charts

2222       It is also possible to combine two different chart types, for example a
2223       column and line chart to create a Pareto chart using the Chart
2224       "combine()" method:
2225
2226       Here is a simpler example:
2227
2228           use strict;
2229           use warnings;
2230           use Excel::Writer::XLSX;
2231
2232           my $workbook  = Excel::Writer::XLSX->new( 'chart_combined.xlsx' );
2233           my $worksheet = $workbook->add_worksheet();
2234           my $bold      = $workbook->add_format( bold => 1 );
2235
2236           # Add the worksheet data that the charts will refer to.
2237           my $headings = [ 'Number', 'Batch 1', 'Batch 2' ];
2238           my $data = [
2239               [ 2,  3,  4,  5,  6,  7 ],
2240               [ 10, 40, 50, 20, 10, 50 ],
2241               [ 30, 60, 70, 50, 40, 30 ],
2242
2243           ];
2244
2245           $worksheet->write( 'A1', $headings, $bold );
2246           $worksheet->write( 'A2', $data );
2247
2248           #
2249           # In the first example we will create a combined column and line chart.
2250           # They will share the same X and Y axes.
2251           #
2252
2253           # Create a new column chart. This will use this as the primary chart.
2254           my $column_chart = $workbook->add_chart( type => 'column', embedded => 1 );
2255
2256           # Configure the data series for the primary chart.
2257           $column_chart->add_series(
2258               name       => '=Sheet1!$B$1',
2259               categories => '=Sheet1!$A$2:$A$7',
2260               values     => '=Sheet1!$B$2:$B$7',
2261           );
2262
2263           # Create a new column chart. This will use this as the secondary chart.
2264           my $line_chart = $workbook->add_chart( type => 'line', embedded => 1 );
2265
2266           # Configure the data series for the secondary chart.
2267           $line_chart->add_series(
2268               name       => '=Sheet1!$C$1',
2269               categories => '=Sheet1!$A$2:$A$7',
2270               values     => '=Sheet1!$C$2:$C$7',
2271           );
2272
2273           # Combine the charts.
2274           $column_chart->combine( $line_chart );
2275
2276           # Add a chart title and some axis labels. Note, this is done via the
2277           # primary chart.
2278           $column_chart->set_title( name => 'Combined chart - same Y axis' );
2279           $column_chart->set_x_axis( name => 'Test number' );
2280           $column_chart->set_y_axis( name => 'Sample length (mm)' );
2281
2282
2283           # Insert the chart into the worksheet
2284           $worksheet->insert_chart( 'E2', $column_chart );
2285
2286           $workbook->close();
2287
2288       The secondary chart can also be placed on a secondary axis using the
2289       methods shown in the previous section.
2290
2291       In this case it is just necessary to add a "y2_axis" parameter to the
2292       series and, if required, add a title using "set_y2_axis()" of the
2293       secondary chart. The following are the additions to the previous
2294       example to place the secondary chart on the secondary axis:
2295
2296           ...
2297
2298           $line_chart->add_series(
2299               name       => '=Sheet1!$C$1',
2300               categories => '=Sheet1!$A$2:$A$7',
2301               values     => '=Sheet1!$C$2:$C$7',
2302               y2_axis    => 1,
2303           );
2304
2305           ...
2306
2307           # Note: the y2 properites are on the secondary chart.
2308           $line_chart2->set_y2_axis( name => 'Target length (mm)' );
2309
2310       The examples above use the concept of a primary and secondary chart.
2311       The primary chart is the chart that defines the primary X and Y axis.
2312       It is also used for setting all chart properties apart from the
2313       secondary data series. For example the chart title and axes properties
2314       should be set via the primary chart (except for the the secondary "y2"
2315       axis properties which should be applied to the secondary chart).
2316
2317       See also "chart_combined.pl" and "chart_pareto.pl" examples in the
2318       distro for more detailed examples.
2319
2320       There are some limitations on combined charts:
2321
2322       ·   Pie charts cannot currently be combined.
2323
2324       ·   Scatter charts cannot currently be used as a primary chart but they
2325           can be used as a secondary chart.
2326
2327       ·   Bar charts can only combined secondary charts on a secondary axis.
2328           This is an Excel limitation.
2329

TODO

2331       Chart features that are on the TODO list and will hopefully be added
2332       are:
2333
2334       ·   Add more chart sub-types.
2335
2336       ·   Additional formatting options.
2337
2338       ·   More axis controls.
2339
2340       ·   3D charts.
2341
2342       ·   Additional chart types.
2343
2344       If you are interested in sponsoring a feature to have it implemented or
2345       expedited let me know.
2346

AUTHOR

2348       John McNamara jmcnamara@cpan.org
2349
2351       Copyright MM-MMXIX, John McNamara.
2352
2353       All Rights Reserved. This module is free software. It may be used,
2354       redistributed and/or modified under the same terms as Perl itself.
2355
2356
2357
2358perl v5.30.0                      2019-07-26     Excel::Writer::XLSX::Chart(3)
Impressum