1Excel::Writer::XLSX::ChUasretr(3C)ontributed Perl DocumeEnxtcaetli:o:nWriter::XLSX::Chart(3)
2
6 Chart - A class for writing Excel Charts.
7
9 To create a simple Excel file with a chart using Excel::Writer::XLSX:
10 #!/usr/bin/perl
12 use strict;
14 use warnings;
15 use Excel::Writer::XLSX;
16 my $workbook = Excel::Writer::XLSX->new( 'chart.xlsx' );
18 my $worksheet = $workbook->add_worksheet();
19 # Add the worksheet data the chart refers to.
21 my $data = [
22 [ 'Category', 2, 3, 4, 5, 6, 7 ],
23 [ 'Value', 1, 4, 5, 2, 1, 5 ],
24 ];
26 $worksheet->write( 'A1', $data );
28 # Add a worksheet chart.
30 my $chart = $workbook->add_chart( type => 'column' );
31 # Configure the chart.
33 $chart->add_series(
34 categories => '=Sheet1!$A$2:$A$7',
35 values => '=Sheet1!$B$2:$B$7',
36 );
37 $workbook->close();
39 __END__
41
43 The "Chart" module is an abstract base class for modules that implement
44 charts in Excel::Writer::XLSX. The information below is applicable to
45 all of the available subclasses.
46 The "Chart" module isn't used directly. A chart object is created via
48 the Workbook "add_chart()" method where the chart type is specified:
49 my $chart = $workbook->add_chart( type => 'column' );
51 Currently the supported chart types are:
53 · "area"
55 Creates an Area (filled line) style chart. See
57 Excel::Writer::XLSX::Chart::Area.
58 · "bar"
60 Creates a Bar style (transposed histogram) chart. See
62 Excel::Writer::XLSX::Chart::Bar.
63 · "column"
65 Creates a column style (histogram) chart. See
67 Excel::Writer::XLSX::Chart::Column.
68 · "line"
70 Creates a Line style chart. See Excel::Writer::XLSX::Chart::Line.
72 · "pie"
74 Creates a Pie style chart. See Excel::Writer::XLSX::Chart::Pie.
76 · "doughnut"
78 Creates a Doughnut style chart. See
80 Excel::Writer::XLSX::Chart::Doughnut.
81 · "scatter"
83 Creates a Scatter style chart. See
85 Excel::Writer::XLSX::Chart::Scatter.
86 · "stock"
88 Creates a Stock style chart. See Excel::Writer::XLSX::Chart::Stock.
90 · "radar"
92 Creates a Radar style chart. See Excel::Writer::XLSX::Chart::Radar.
94 Chart subtypes are also supported in some cases:
96 $workbook->add_chart( type => 'bar', subtype => 'stacked' );
98 The currently available subtypes are:
100 area
102 stacked
103 percent_stacked
104 bar
106 stacked
107 percent_stacked
108 column
110 stacked
111 percent_stacked
112 scatter
114 straight_with_markers
115 straight
116 smooth_with_markers
117 smooth
118 radar
120 with_markers
121 filled
122 More charts and sub-types will be supported in time. See the "TODO"
124 section.
125
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 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 With an Excel::Writer::XLSX chart object the "add_series()" method is
137 used to set the properties for a series:
138 $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 The properties that can be set are:
146 · "values"
148 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 · "categories"
155 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 · "name"
162 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 · "line"
168 Set the properties of the series line type such as colour and
170 width. See the "CHART FORMATTING" section below.
171 · "border"
173 Set the border properties of the series such as colour and style.
175 See the "CHART FORMATTING" section below.
176 · "fill"
178 Set the fill properties of the series such as colour. See the
180 "CHART FORMATTING" section below.
181 · "pattern"
183 Set the pattern properties of the series. See the "CHART
185 FORMATTING" section below.
186 · "gradien"
188 Set the gradient properties of the series. See the "CHART
190 FORMATTING" section below.
191 · "marker"
193 Set the properties of the series marker such as style and colour.
195 See the "SERIES OPTIONS" section below.
196 · "trendline"
198 Set the properties of the series trendline such as linear,
200 polynomial and moving average types. See the "SERIES OPTIONS"
201 section below.
202 · "smooth"
204 The "smooth" option is used to set the smooth property of a line
206 series. See the "SERIES OPTIONS" section below.
207 · "y_error_bars"
209 Set vertical error bounds for a chart series. See the "SERIES
211 OPTIONS" section below.
212 · "x_error_bars"
214 Set horizontal error bounds for a chart series. See the "SERIES
216 OPTIONS" section below.
217 · "data_labels"
219 Set data labels for the series. See the "SERIES OPTIONS" section
221 below.
222 · "points"
224 Set properties for individual points in a series. See the "SERIES
226 OPTIONS" section below.
227 · "invert_if_negative"
229 Invert the fill colour for negative values. Usually only applicable
231 to column and bar charts.
232 · "overlap"
234 Set the overlap between series in a Bar/Column chart. The range is
236 +/- 100. Default is 0.
237 overlap => 20,
239 Note, it is only necessary to apply this property to one series of
241 the chart.
242 · "gap"
244 Set the gap between series in a Bar/Column chart. The range is 0 to
246 500. Default is 150.
247 gap => 200,
249 Note, it is only necessary to apply this property to one series of
251 the chart.
252 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 [ $sheetname, $row_start, $row_end, $col_start, $col_end ]
258 The following are equivalent:
260 $chart->add_series( categories => '=Sheet1!$A$2:$A$7' ); # Same as ...
262 $chart->add_series( categories => [ 'Sheet1', 1, 6, 0, 0 ] ); # Zero-indexed.
263 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 # 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 # 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 It is also possible to specify non-contiguous ranges:
284 $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 set_x_axis()
291 The "set_x_axis()" method is used to set properties of the X axis.
292 $chart->set_x_axis( name => 'Quarterly results' );
294 The properties that can be set are:
296 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 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 · "name"
334 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 $chart->set_x_axis( name => 'Quarterly results' );
340 The name can also be a formula such as "=Sheet1!$A$1".
342 · "name_font"
344 Set the font properties for the axis title. (Applicable to category
346 and value axes).
347 $chart->set_x_axis( name_font => { name => 'Arial', size => 10 } );
349 · "name_layout"
351 Set the "(x, y)" position of the axis caption in chart relative
353 units. (Applicable to category and value axes).
354 $chart->set_x_axis(
356 name => 'X axis',
357 name_layout => {
358 x => 0.34,
359 y => 0.85,
360 }
361 );
362 See the "CHART LAYOUT" section below.
364 · "num_font"
366 Set the font properties for the axis numbers. (Applicable to
368 category and value axes).
369 $chart->set_x_axis( num_font => { bold => 1, italic => 1 } );
371 See the "CHART FONTS" section below.
373 · "num_format"
375 Set the number format for the axis. (Applicable to category and
377 value axes).
378 $chart->set_x_axis( num_format => '#,##0.00' );
380 $chart->set_y_axis( num_format => '0.00%' );
381 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 · "line"
388 Set the properties of the axis line type such as colour and width.
390 See the "CHART FORMATTING" section below.
391 $chart->set_x_axis( line => { none => 1 });
393 · "fill"
395 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 · "pattern"
402 Set the pattern properties of the axis such as colour. See the
404 "CHART FORMATTING" section below.
405 · "gradient"
407 Set the gradient properties of the axis such as colour. See the
409 "CHART FORMATTING" section below.
410 · "min"
412 Set the minimum value for the axis range. (Applicable to value axes
414 only.)
415 $chart->set_x_axis( min => 20 );
417 · "max"
419 Set the maximum value for the axis range. (Applicable to value axes
421 only.)
422 $chart->set_x_axis( max => 80 );
424 · "minor_unit"
426 Set the increment of the minor units in the axis range. (Applicable
428 to value axes only.)
429 $chart->set_x_axis( minor_unit => 0.4 );
431 · "major_unit"
433 Set the increment of the major units in the axis range. (Applicable
435 to value axes only.)
436 $chart->set_x_axis( major_unit => 2 );
438 · "interval_unit"
440 Set the interval unit for a category axis. (Applicable to category
442 axes only.)
443 $chart->set_x_axis( interval_unit => 2 );
445 · "interval_tick"
447 Set the tick interval for a category axis. (Applicable to category
449 axes only.)
450 $chart->set_x_axis( interval_tick => 4 );
452 · "crossing"
454 Set the position where the y axis will cross the x axis.
456 (Applicable to category and value axes.)
457 The "crossing" value can either be the string 'max' to set the
459 crossing at the maximum axis value or a numeric value.
460 $chart->set_x_axis( crossing => 3 );
462 # or
463 $chart->set_x_axis( crossing => 'max' );
464 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 If crossing is omitted (the default) the crossing will be set
470 automatically by Excel based on the chart data.
471 · "position_axis"
473 Position the axis on or between the axis tick marks. (Applicable to
475 category axes only.)
476 There are two allowable values "on_tick" and "between":
478 $chart->set_x_axis( position_axis => 'on_tick' );
480 $chart->set_x_axis( position_axis => 'between' );
481 · "reverse"
483 Reverse the order of the axis categories or values. (Applicable to
485 category and value axes.)
486 $chart->set_x_axis( reverse => 1 );
488 · "log_base"
490 Set the log base of the axis range. (Applicable to value axes
492 only.)
493 $chart->set_x_axis( log_base => 10 );
495 · "label_position"
497 Set the "Axis labels" position for the axis. The following
499 positions are available:
500 next_to (the default)
502 high
503 low
504 none
505 · "major_gridlines"
507 Configure the major gridlines for the axis. The available
509 properties are:
510 visible
512 line
513 For example:
515 $chart->set_x_axis(
517 major_gridlines => {
518 visible => 1,
519 line => { color => 'red', width => 1.25, dash_type => 'dash' }
520 }
521 );
522 The "visible" property is usually on for the X-axis but it depends
524 on the type of chart.
525 The "line" property sets the gridline properties such as colour and
527 width. See the "CHART FORMATTING" section below.
528 · "minor_gridlines"
530 This takes the same options as "major_gridlines" above.
532 The minor gridline "visible" property is off by default for all
534 chart types.
535 · "visible"
537 Configure the visibility of the axis.
539 $chart->set_x_axis( visible => 0 );
541 · "date_axis"
543 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 $chart->set_x_axis( date_axis => 1 );
548 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 See "Date Category Axes" for more details.
554 · "text_axis"
556 This option is used to treat a category axis explicitly as a Text
558 Axis. (Applicable to category axes only.)
559 $chart->set_x_axis( text_axis => 1 );
561 · "minor_unit_type"
563 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 $chart->set_x_axis(
568 date_axis => 1,
569 minor_unit => 4,
570 minor_unit_type => 'months',
571 );
572 The allowable values for this option are "days", "months" and
574 "years".
575 · "major_unit_type"
577 Same as "minor_unit_type", see above, but for major axes unit
579 types.
580 More than one property can be set in a call to "set_x_axis()":
582 $chart->set_x_axis(
584 name => 'Quarterly results',
585 min => 10,
586 max => 80,
587 );
588 · "major_tick_mark"
590 Set the axis major tick mark type to one of the following values:
592 none
594 inside
595 outside
596 cross (inside and outside)
597 For example:
599 $chart->set_x_axis( major_tick_mark => 'none',
601 minor_tick_mark => 'inside' );
602 · "minor_tick_mark"
604 Set the axis minor tick mark type. Same as "major_tick_mark", see
606 above.
607 · "display_units"
609 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 hundreds
616 thousands
617 ten_thousands
618 hundred_thousands
619 millions
620 ten_millions
621 hundred_millions
622 billions
623 trillions
624 Example:
626 $chart->set_x_axis( display_units => 'thousands' )
628 $chart->set_y_axis( display_units => 'millions' )
629 * "display_units_visible"
631 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 $chart->set_x_axis( display_units => 'thousands',
637 display_units_visible => 0 )
638 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 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 label_position => 'none',
649 crossing => 'max',
650 visible => 0,
651 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 major_gridlines => { visible => 0 }
658 combine()
660 The chart "combine()" method is used to combine two charts of different
661 types, for example a column and line chart:
662 my $column_chart = $workbook->add_chart( type => 'column', embedded => 1 );
664 # Configure the data series for the primary chart.
666 $column_chart->add_series(...);
667 # 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 # Configure the data series for the secondary chart.
672 $line_chart->add_series(...);
673 # Combine the charts.
675 $column_chart->combine( $line_chart );
676 See "Combined Charts" for more details.
678 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 width
684 height
685 x_scale
686 y_scale
687 x_offset
688 y_offset
689 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 $chart->set_size( width => 720, height => 576 );
696 # Same as:
698 $chart->set_size( x_scale => 1.5, y_scale => 2 );
700 The "x_offset" and "y_offset" position the top left corner of the chart
702 in the cell that it is inserted into.
703 Note: the "x_scale", "y_scale", "x_offset" and "y_offset" parameters
705 can also be set via the "insert_chart()" method:
706 $worksheet->insert_chart( 'E2', $chart, 2, 4, 1.5, 2 );
708 set_title()
710 The "set_title()" method is used to set properties of the chart title.
711 $chart->set_title( name => 'Year End Results' );
713 The properties that can be set are:
715 · "name"
717 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 · "name_font"
723 Set the font properties for the chart title. See the "CHART FONTS"
725 section below.
726 · "overlay"
728 Allow the title to be overlaid on the chart. Generally used with
730 the layout property below.
731 · "layout"
733 Set the "(x, y)" position of the title in chart relative units:
735 $chart->set_title(
737 name => 'Title',
738 overlay => 1,
739 layout => {
740 x => 0.42,
741 y => 0.14,
742 }
743 );
744 See the "CHART LAYOUT" section below.
746 · "none"
748 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 $chart->set_title( none => 1 );
755 set_legend()
757 The "set_legend()" method is used to set properties of the chart
758 legend.
759 The properties that can be set are:
761 · "none"
763 The "none" option turns off the chart legend. In Excel chart
765 legends are on by default:
766 $chart->set_legend( none => 1 );
768 Note, for backward compatibility, it is also possible to turn off
770 the legend via the "position" property:
771 $chart->set_legend( position => 'none' );
773 · "position"
775 Set the position of the chart legend.
777 $chart->set_legend( position => 'bottom' );
779 The default legend position is "right". The available positions
781 are:
782 top
784 bottom
785 left
786 right
787 overlay_left
788 overlay_right
789 none
790 · "layout"
792 Set the "(x, y)" position of the legend in chart relative units:
794 $chart->set_legend(
796 layout => {
797 x => 0.80,
798 y => 0.37,
799 width => 0.12,
800 height => 0.25,
801 }
802 );
803 See the "CHART LAYOUT" section below.
805 · "delete_series"
807 This allows you to remove 1 or more series from the legend (the
809 series will still display on the chart). This property takes an
810 array ref as an argument and the series are zero indexed:
811 # Delete/hide series index 0 and 2 from the legend.
813 $chart->set_legend( delete_series => [0, 2] );
814 · "font"
816 Set the font properties of the chart legend:
818 $chart->set_legend( font => { bold => 1, italic => 1 } );
820 See the "CHART FONTS" section below.
822 set_chartarea()
824 The "set_chartarea()" method is used to set the properties of the chart
825 area.
826 $chart->set_chartarea(
828 border => { none => 1 },
829 fill => { color => 'red' }
830 );
831 The properties that can be set are:
833 · "border"
835 Set the border properties of the chartarea such as colour and
837 style. See the "CHART FORMATTING" section below.
838 · "fill"
840 Set the fill properties of the chartarea such as colour. See the
842 "CHART FORMATTING" section below.
843 · "pattern"
845 Set the pattern fill properties of the chartarea. See the "CHART
847 FORMATTING" section below.
848 · "gradient"
850 Set the gradient fill properties of the chartarea. See the "CHART
852 FORMATTING" section below.
853 set_plotarea()
855 The "set_plotarea()" method is used to set properties of the plot area
856 of a chart.
857 $chart->set_plotarea(
859 border => { color => 'yellow', width => 1, dash_type => 'dash' },
860 fill => { color => '#92D050' }
861 );
862 The properties that can be set are:
864 · "border"
866 Set the border properties of the plotarea such as colour and style.
868 See the "CHART FORMATTING" section below.
869 · "fill"
871 Set the fill properties of the plotarea such as colour. See the
873 "CHART FORMATTING" section below.
874 · "pattern"
876 Set the pattern fill properties of the plotarea. See the "CHART
878 FORMATTING" section below.
879 · "gradient"
881 Set the gradient fill properties of the plotarea. See the "CHART
883 FORMATTING" section below.
884 · "layout"
886 Set the "(x, y)" position of the plotarea in chart relative units:
888 $chart->set_plotarea(
890 layout => {
891 x => 0.35,
892 y => 0.26,
893 width => 0.62,
894 height => 0.50,
895 }
896 );
897 See the "CHART LAYOUT" section below.
899 set_style()
901 The "set_style()" method is used to set the style of the chart to one
902 of the 42 built-in styles available on the 'Design' tab in Excel:
903 $chart->set_style( 4 );
905 The default style is 2.
907 set_table()
909 The "set_table()" method adds a data table below the horizontal axis
910 with the data used to plot the chart.
911 $chart->set_table();
913 The available options, with default values are:
915 vertical => 1 # Display vertical lines in the table.
917 horizontal => 1 # Display horizontal lines in the table.
918 outline => 1 # Display an outline in the table.
919 show_keys => 0 # Show the legend keys with the table data.
920 font => {} # Standard chart font properties.
921 The data table can only be shown with Bar, Column, Line, Area and stock
923 charts. For font properties see the "CHART FONTS" section below.
924 set_up_down_bars
926 The "set_up_down_bars()" method adds Up-Down bars to Line charts to
927 indicate the difference between the first and last data series.
928 $chart->set_up_down_bars();
930 It is possible to format the up and down bars to add "fill", "pattern",
932 "gradient" and "border" properties if required. See the "CHART
933 FORMATTING" section below.
934 $chart->set_up_down_bars(
936 up => { fill => { color => 'green' } },
937 down => { fill => { color => 'red' } },
938 );
939 Up-down bars can only be applied to Line charts and to Stock charts (by
941 default).
942 set_drop_lines
944 The "set_drop_lines()" method adds Drop Lines to charts to show the
945 Category value of points in the data.
946 $chart->set_drop_lines();
948 It is possible to format the Drop Line "line" properties if required.
950 See the "CHART FORMATTING" section below.
951 $chart->set_drop_lines( line => { color => 'red', dash_type => 'square_dot' } );
953 Drop Lines are only available in Line, Area and Stock charts.
955 set_high_low_lines
957 The "set_high_low_lines()" method adds High-Low lines to charts to show
958 the maximum and minimum values of points in a Category.
959 $chart->set_high_low_lines();
961 It is possible to format the High-Low Line "line" properties if
963 required. See the "CHART FORMATTING" section below.
964 $chart->set_high_low_lines( line => { color => 'red' } );
966 High-Low Lines are only available in Line and Stock charts.
968 show_blanks_as()
970 The "show_blanks_as()" method controls how blank data is displayed in a
971 chart.
972 $chart->show_blanks_as( 'span' );
974 The available options are:
976 gap # Blank data is shown as a gap. The default.
978 zero # Blank data is displayed as zero.
979 span # Blank data is connected with a line.
980 show_hidden_data()
982 Display data in hidden rows or columns on the chart.
983 $chart->show_hidden_data();
985
987 This section details the following properties of "add_series()" in more
988 detail:
989 marker
991 trendline
992 y_error_bars
993 x_error_bars
994 data_labels
995 points
996 smooth
997 Marker
999 The marker format specifies the properties of the markers used to
1000 distinguish series on a chart. In general only Line and Scatter chart
1001 types and trendlines use markers.
1002 The following properties can be set for "marker" formats in a chart.
1004 type
1006 size
1007 border
1008 fill
1009 pattern
1010 gradient
1011 The "type" property sets the type of marker that is used with a series.
1013 $chart->add_series(
1015 values => '=Sheet1!$B$1:$B$5',
1016 marker => { type => 'diamond' },
1017 );
1018 The following "type" properties can be set for "marker" formats in a
1020 chart. These are shown in the same order as in the Excel format dialog.
1021 automatic
1023 none
1024 square
1025 diamond
1026 triangle
1027 x
1028 star
1029 short_dash
1030 long_dash
1031 circle
1032 plus
1033 The "automatic" type is a special case which turns on a marker using
1035 the default marker style for the particular series number.
1036 $chart->add_series(
1038 values => '=Sheet1!$B$1:$B$5',
1039 marker => { type => 'automatic' },
1040 );
1041 If "automatic" is on then other marker properties such as size, border
1043 or fill cannot be set.
1044 The "size" property sets the size of the marker and is generally used
1046 in conjunction with "type".
1047 $chart->add_series(
1049 values => '=Sheet1!$B$1:$B$5',
1050 marker => { type => 'diamond', size => 7 },
1051 );
1052 Nested "border" and "fill" properties can also be set for a marker. See
1054 the "CHART FORMATTING" section below.
1055 $chart->add_series(
1057 values => '=Sheet1!$B$1:$B$5',
1058 marker => {
1059 type => 'square',
1060 size => 5,
1061 border => { color => 'red' },
1062 fill => { color => 'yellow' },
1063 },
1064 );
1065 Trendline
1067 A trendline can be added to a chart series to indicate trends in the
1068 data such as a moving average or a polynomial fit.
1069 The following properties can be set for trendlines in a chart series.
1071 type
1073 order (for polynomial trends)
1074 period (for moving average)
1075 forward (for all except moving average)
1076 backward (for all except moving average)
1077 name
1078 line
1079 intercept (for exponential, linear and polynomial only)
1080 display_equation (for all except moving average)
1081 display_r_squared (for all except moving average)
1082 The "type" property sets the type of trendline in the series.
1084 $chart->add_series(
1086 values => '=Sheet1!$B$1:$B$5',
1087 trendline => { type => 'linear' },
1088 );
1089 The available "trendline" types are:
1091 exponential
1093 linear
1094 log
1095 moving_average
1096 polynomial
1097 power
1098 A "polynomial" trendline can also specify the "order" of the
1100 polynomial. The default value is 2.
1101 $chart->add_series(
1103 values => '=Sheet1!$B$1:$B$5',
1104 trendline => {
1105 type => 'polynomial',
1106 order => 3,
1107 },
1108 );
1109 A "moving_average" trendline can also specify the "period" of the
1111 moving average. The default value is 2.
1112 $chart->add_series(
1114 values => '=Sheet1!$B$1:$B$5',
1115 trendline => {
1116 type => 'moving_average',
1117 period => 3,
1118 },
1119 );
1120 The "forward" and "backward" properties set the forecast period of the
1122 trendline.
1123 $chart->add_series(
1125 values => '=Sheet1!$B$1:$B$5',
1126 trendline => {
1127 type => 'linear',
1128 forward => 0.5,
1129 backward => 0.5,
1130 },
1131 );
1132 The "name" property sets an optional name for the trendline that will
1134 appear in the chart legend. If it isn't specified the Excel default
1135 name will be displayed. This is usually a combination of the trendline
1136 type and the series name.
1137 $chart->add_series(
1139 values => '=Sheet1!$B$1:$B$5',
1140 trendline => {
1141 type => 'linear',
1142 name => 'Interpolated trend',
1143 },
1144 );
1145 The "intercept" property sets the point where the trendline crosses the
1147 Y (value) axis:
1148 $chart->add_series(
1150 values => '=Sheet1!$B$1:$B$5',
1151 trendline => {
1152 type => 'linear',
1153 intercept => 0.8,
1154 },
1155 );
1156 The "display_equation" property displays the trendline equation on the
1158 chart.
1159 $chart->add_series(
1161 values => '=Sheet1!$B$1:$B$5',
1162 trendline => {
1163 type => 'linear',
1164 display_equation => 1,
1165 },
1166 );
1167 The "display_r_squared" property displays the R squared value of the
1169 trendline on the chart.
1170 $chart->add_series(
1172 values => '=Sheet1!$B$1:$B$5',
1173 trendline => {
1174 type => 'linear',
1175 display_r_squared => 1
1176 },
1177 );
1178 Several of these properties can be set in one go:
1180 $chart->add_series(
1182 values => '=Sheet1!$B$1:$B$5',
1183 trendline => {
1184 type => 'polynomial',
1185 name => 'My trend name',
1186 order => 2,
1187 forward => 0.5,
1188 backward => 0.5,
1189 intercept => 1.5,
1190 display_equation => 1,
1191 display_r_squared => 1,
1192 line => {
1193 color => 'red',
1194 width => 1,
1195 dash_type => 'long_dash',
1196 }
1197 },
1198 );
1199 Trendlines cannot be added to series in a stacked chart or pie chart,
1201 radar chart, doughnut or (when implemented) to 3D, or surface charts.
1202 Error Bars
1204 Error bars can be added to a chart series to indicate error bounds in
1205 the data. The error bars can be vertical "y_error_bars" (the most
1206 common type) or horizontal "x_error_bars" (for Bar and Scatter charts
1207 only).
1208 The following properties can be set for error bars in a chart series.
1210 type
1212 value (for all types except standard error and custom)
1213 plus_values (for custom only)
1214 minus_values (for custom only)
1215 direction
1216 end_style
1217 line
1218 The "type" property sets the type of error bars in the series.
1220 $chart->add_series(
1222 values => '=Sheet1!$B$1:$B$5',
1223 y_error_bars => { type => 'standard_error' },
1224 );
1225 The available error bars types are available:
1227 fixed
1229 percentage
1230 standard_deviation
1231 standard_error
1232 custom
1233 All error bar types, except for "standard_error" and "custom" must also
1235 have a value associated with it for the error bounds:
1236 $chart->add_series(
1238 values => '=Sheet1!$B$1:$B$5',
1239 y_error_bars => {
1240 type => 'percentage',
1241 value => 5,
1242 },
1243 );
1244 The "custom" error bar type must specify "plus_values" and
1246 "minus_values" which should either by a "Sheet1!$A$1:$A$5" type range
1247 formula or an arrayref of values:
1248 $chart->add_series(
1250 categories => '=Sheet1!$A$1:$A$5',
1251 values => '=Sheet1!$B$1:$B$5',
1252 y_error_bars => {
1253 type => 'custom',
1254 plus_values => '=Sheet1!$C$1:$C$5',
1255 minus_values => '=Sheet1!$D$1:$D$5',
1256 },
1257 );
1258 # or
1260 $chart->add_series(
1263 categories => '=Sheet1!$A$1:$A$5',
1264 values => '=Sheet1!$B$1:$B$5',
1265 y_error_bars => {
1266 type => 'custom',
1267 plus_values => [1, 1, 1, 1, 1],
1268 minus_values => [2, 2, 2, 2, 2],
1269 },
1270 );
1271 Note, as in Excel the items in the "minus_values" do not need to be
1273 negative.
1274 The "direction" property sets the direction of the error bars. It
1276 should be one of the following:
1277 plus # Positive direction only.
1279 minus # Negative direction only.
1280 both # Plus and minus directions, The default.
1281 The "end_style" property sets the style of the error bar end cap. The
1283 options are 1 (the default) or 0 (for no end cap):
1284 $chart->add_series(
1286 values => '=Sheet1!$B$1:$B$5',
1287 y_error_bars => {
1288 type => 'fixed',
1289 value => 2,
1290 end_style => 0,
1291 direction => 'minus'
1292 },
1293 );
1294 Data Labels
1296 Data labels can be added to a chart series to indicate the values of
1297 the plotted data points.
1298 The following properties can be set for "data_labels" formats in a
1300 chart.
1301 value
1303 category
1304 series_name
1305 position
1306 percentage
1307 leader_lines
1308 separator
1309 legend_key
1310 num_format
1311 font
1312 The "value" property turns on the Value data label for a series.
1314 $chart->add_series(
1316 values => '=Sheet1!$B$1:$B$5',
1317 data_labels => { value => 1 },
1318 );
1319 The "category" property turns on the Category Name data label for a
1321 series.
1322 $chart->add_series(
1324 values => '=Sheet1!$B$1:$B$5',
1325 data_labels => { category => 1 },
1326 );
1327 The "series_name" property turns on the Series Name data label for a
1329 series.
1330 $chart->add_series(
1332 values => '=Sheet1!$B$1:$B$5',
1333 data_labels => { series_name => 1 },
1334 );
1335 The "position" property is used to position the data label for a
1337 series.
1338 $chart->add_series(
1340 values => '=Sheet1!$B$1:$B$5',
1341 data_labels => { value => 1, position => 'center' },
1342 );
1343 In Excel the data label positions vary for different chart types. The
1345 allowable positions are:
1346 | Position | Line | Bar | Pie | Area |
1348 | | Scatter | Column | Doughnut | Radar |
1349 | | Stock | | | |
1350 |---------------|-----------|-----------|-----------|-----------|
1351 | center | Yes | Yes | Yes | Yes* |
1352 | right | Yes* | | | |
1353 | left | Yes | | | |
1354 | above | Yes | | | |
1355 | below | Yes | | | |
1356 | inside_base | | Yes | | |
1357 | inside_end | | Yes | Yes | |
1358 | outside_end | | Yes* | Yes | |
1359 | best_fit | | | Yes* | |
1360 Note: The * indicates the default position for each chart type in
1362 Excel, if a position isn't specified.
1363 The "percentage" property is used to turn on the display of data labels
1365 as a Percentage for a series. It is mainly used for pie and doughnut
1366 charts.
1367 $chart->add_series(
1369 values => '=Sheet1!$B$1:$B$5',
1370 data_labels => { percentage => 1 },
1371 );
1372 The "leader_lines" property is used to turn on Leader Lines for the
1374 data label for a series. It is mainly used for pie charts.
1375 $chart->add_series(
1377 values => '=Sheet1!$B$1:$B$5',
1378 data_labels => { value => 1, leader_lines => 1 },
1379 );
1380 Note: Even when leader lines are turned on they aren't automatically
1382 visible in Excel or Excel::Writer::XLSX. Due to an Excel limitation (or
1383 design) leader lines only appear if the data label is moved manually or
1384 if the data labels are very close and need to be adjusted
1385 automatically.
1386 The "separator" property is used to change the separator between
1388 multiple data label items:
1389 $chart->add_series(
1391 values => '=Sheet1!$B$1:$B$5',
1392 data_labels => { percentage => 1 },
1393 data_labels => { value => 1, category => 1, separator => "\n" },
1394 );
1395 The separator value must be one of the following strings:
1397 ','
1399 ';'
1400 '.'
1401 "\n"
1402 ' '
1403 The "legend_key" property is used to turn on Legend Key for the data
1405 label for a series:
1406 $chart->add_series(
1408 values => '=Sheet1!$B$1:$B$5',
1409 data_labels => { value => 1, legend_key => 1 },
1410 );
1411 The "num_format" property is used to set the number format for the data
1413 labels.
1414 $chart->add_series(
1416 values => '=Sheet1!$A$1:$A$5',
1417 data_labels => { value => 1, num_format => '#,##0.00' },
1418 );
1419 The number format is similar to the Worksheet Cell Format "num_format"
1421 apart from the fact that a format index cannot be used. The explicit
1422 format string must be used as shown above. See "set_num_format()" in
1423 Excel::Writer::XLSX for more information.
1424 The "font" property is used to set the font properties of the data
1426 labels in a series:
1427 $chart->add_series(
1429 values => '=Sheet1!$A$1:$A$5',
1430 data_labels => {
1431 value => 1,
1432 font => { name => 'Consolas' }
1433 },
1434 );
1435 The "font" property is also used to rotate the data labels in a series:
1437 $chart->add_series(
1439 values => '=Sheet1!$A$1:$A$5',
1440 data_labels => {
1441 value => 1,
1442 font => { rotation => 45 }
1443 },
1444 );
1445 See the "CHART FONTS" section below.
1447 Points
1449 In general formatting is applied to an entire series in a chart.
1450 However, it is occasionally required to format individual points in a
1451 series. In particular this is required for Pie and Doughnut charts
1452 where each segment is represented by a point.
1453 In these cases it is possible to use the "points" property of
1455 "add_series()":
1456 $chart->add_series(
1458 values => '=Sheet1!$A$1:$A$3',
1459 points => [
1460 { fill => { color => '#FF0000' } },
1461 { fill => { color => '#CC0000' } },
1462 { fill => { color => '#990000' } },
1463 ],
1464 );
1465 The "points" property takes an array ref of format options (see the
1467 "CHART FORMATTING" section below). To assign default properties to
1468 points in a series pass "undef" values in the array ref:
1469 # Format point 3 of 3 only.
1471 $chart->add_series(
1472 values => '=Sheet1!$A$1:$A$3',
1473 points => [
1474 undef,
1475 undef,
1476 { fill => { color => '#990000' } },
1477 ],
1478 );
1479 # Format the first point only.
1481 $chart->add_series(
1482 values => '=Sheet1!$A$1:$A$3',
1483 points => [ { fill => { color => '#FF0000' } } ],
1484 );
1485 Smooth
1487 The "smooth" option is used to set the smooth property of a line
1488 series. It is only applicable to the "Line" and "Scatter" chart types.
1489 $chart->add_series( values => '=Sheet1!$C$1:$C$5',
1491 smooth => 1 );
1492
1494 The following chart formatting properties can be set for any chart
1495 object that they apply to (and that are supported by
1496 Excel::Writer::XLSX) such as chart lines, column fill areas, plot area
1497 borders, markers, gridlines and other chart elements documented above.
1498 line
1500 border
1501 fill
1502 pattern
1503 gradient
1504 Chart formatting properties are generally set using hash refs.
1506 $chart->add_series(
1508 values => '=Sheet1!$B$1:$B$5',
1509 line => { color => 'blue' },
1510 );
1511 In some cases the format properties can be nested. For example a
1513 "marker" may contain "border" and "fill" sub-properties.
1514 $chart->add_series(
1516 values => '=Sheet1!$B$1:$B$5',
1517 line => { color => 'blue' },
1518 marker => {
1519 type => 'square',
1520 size => 5,
1521 border => { color => 'red' },
1522 fill => { color => 'yellow' },
1523 },
1524 );
1525 Line
1527 The line format is used to specify properties of line objects that
1528 appear in a chart such as a plotted line on a chart or a border.
1529 The following properties can be set for "line" formats in a chart.
1531 none
1533 color
1534 width
1535 dash_type
1536 The "none" property is uses to turn the "line" off (it is always on by
1538 default except in Scatter charts). This is useful if you wish to plot a
1539 series with markers but without a line.
1540 $chart->add_series(
1542 values => '=Sheet1!$B$1:$B$5',
1543 line => { none => 1 },
1544 );
1545 The "color" property sets the color of the "line".
1547 $chart->add_series(
1549 values => '=Sheet1!$B$1:$B$5',
1550 line => { color => 'red' },
1551 );
1552 The available colours are shown in the main Excel::Writer::XLSX
1554 documentation. It is also possible to set the colour of a line with a
1555 HTML style RGB colour:
1556 $chart->add_series(
1558 line => { color => '#FF0000' },
1559 );
1560 The "width" property sets the width of the "line". It should be
1562 specified in increments of 0.25 of a point as in Excel.
1563 $chart->add_series(
1565 values => '=Sheet1!$B$1:$B$5',
1566 line => { width => 3.25 },
1567 );
1568 The "dash_type" property sets the dash style of the line.
1570 $chart->add_series(
1572 values => '=Sheet1!$B$1:$B$5',
1573 line => { dash_type => 'dash_dot' },
1574 );
1575 The following "dash_type" values are available. They are shown in the
1577 order that they appear in the Excel dialog.
1578 solid
1580 round_dot
1581 square_dot
1582 dash
1583 dash_dot
1584 long_dash
1585 long_dash_dot
1586 long_dash_dot_dot
1587 The default line style is "solid".
1589 More than one "line" property can be specified at a time:
1591 $chart->add_series(
1593 values => '=Sheet1!$B$1:$B$5',
1594 line => {
1595 color => 'red',
1596 width => 1.25,
1597 dash_type => 'square_dot',
1598 },
1599 );
1600 Border
1602 The "border" property is a synonym for "line".
1603 It can be used as a descriptive substitute for "line" in chart types
1605 such as Bar and Column that have a border and fill style rather than a
1606 line style. In general chart objects with a "border" property will also
1607 have a fill property.
1608 Solid Fill
1610 The fill format is used to specify filled areas of chart objects such
1611 as the interior of a column or the background of the chart itself.
1612 The following properties can be set for "fill" formats in a chart.
1614 none
1616 color
1617 transparency
1618 The "none" property is used to turn the "fill" property off (it is
1620 generally on by default).
1621 $chart->add_series(
1623 values => '=Sheet1!$B$1:$B$5',
1624 fill => { none => 1 },
1625 );
1626 The "color" property sets the colour of the "fill" area.
1628 $chart->add_series(
1630 values => '=Sheet1!$B$1:$B$5',
1631 fill => { color => 'red' },
1632 );
1633 The available colours are shown in the main Excel::Writer::XLSX
1635 documentation. It is also possible to set the colour of a fill with a
1636 HTML style RGB colour:
1637 $chart->add_series(
1639 fill => { color => '#FF0000' },
1640 );
1641 The "transparency" property sets the transparency of the solid fill
1643 color in the integer range 1 - 100:
1644 $chart->set_chartarea( fill => { color => 'yellow', transparency => 75 } );
1646 The "fill" format is generally used in conjunction with a "border"
1648 format which has the same properties as a "line" format.
1649 $chart->add_series(
1651 values => '=Sheet1!$B$1:$B$5',
1652 border => { color => 'red' },
1653 fill => { color => 'yellow' },
1654 );
1655 Pattern Fill
1657 The pattern fill format is used to specify pattern filled areas of
1658 chart objects such as the interior of a column or the background of the
1659 chart itself.
1660 The following properties can be set for "pattern" fill formats in a
1662 chart:
1663 pattern: the pattern to be applied (required)
1665 fg_color: the foreground color of the pattern (required)
1666 bg_color: the background color (optional, defaults to white)
1667 For example:
1669 $chart->set_plotarea(
1671 pattern => {
1672 pattern => 'percent_5',
1673 fg_color => 'red',
1674 bg_color => 'yellow',
1675 }
1676 );
1677 The following patterns can be applied:
1679 percent_5
1681 percent_10
1682 percent_20
1683 percent_25
1684 percent_30
1685 percent_40
1686 percent_50
1687 percent_60
1688 percent_70
1689 percent_75
1690 percent_80
1691 percent_90
1692 light_downward_diagonal
1693 light_upward_diagonal
1694 dark_downward_diagonal
1695 dark_upward_diagonal
1696 wide_downward_diagonal
1697 wide_upward_diagonal
1698 light_vertical
1699 light_horizontal
1700 narrow_vertical
1701 narrow_horizontal
1702 dark_vertical
1703 dark_horizontal
1704 dashed_downward_diagonal
1705 dashed_upward_diagonal
1706 dashed_horizontal
1707 dashed_vertical
1708 small_confetti
1709 large_confetti
1710 zigzag
1711 wave
1712 diagonal_brick
1713 horizontal_brick
1714 weave
1715 plaid
1716 divot
1717 dotted_grid
1718 dotted_diamond
1719 shingle
1720 trellis
1721 sphere
1722 small_grid
1723 large_grid
1724 small_check
1725 large_check
1726 outlined_diamond
1727 solid_diamond
1728 The foreground color, "fg_color", is a required parameter and can be a
1730 Html style "#RRGGBB" string or a limited number of named colors. The
1731 available colours are shown in the main Excel::Writer::XLSX
1732 documentation.
1733 The background color, "bg_color", is optional and defaults to black.
1735 If a pattern fill is used on a chart object it overrides the solid fill
1737 properties of the object.
1738 Gradient Fill
1740 The gradient fill format is used to specify gradient filled areas of
1741 chart objects such as the interior of a column or the background of the
1742 chart itself.
1743 The following properties can be set for "gradient" fill formats in a
1745 chart:
1746 colors: a list of colors
1748 positions: an optional list of positions for the colors
1749 type: the optional type of gradient fill
1750 angle: the optional angle of the linear fill
1751 The "colors" property sets a list of colors that define the "gradient":
1753 $chart->set_plotarea(
1755 gradient => { colors => [ '#DDEBCF', '#9CB86E', '#156B13' ] }
1756 );
1757 Excel allows between 2 and 10 colors in a gradient but it is unlikely
1759 that you will require more than 2 or 3.
1760 As with solid or pattern fill it is also possible to set the colors of
1762 a gradient with a Html style "#RRGGBB" string or a limited number of
1763 named colors. The available colours are shown in the main
1764 Excel::Writer::XLSX documentation:
1765 $chart->add_series(
1767 values => '=Sheet1!$A$1:$A$5',
1768 gradient => { colors => [ 'red', 'green' ] }
1769 );
1770 The "positions" defines an optional list of positions, between 0 and
1772 100, of where the colors in the gradient are located. Default values
1773 are provided for "colors" lists of between 2 and 4 but they can be
1774 specified if required:
1775 $chart->add_series(
1777 values => '=Sheet1!$A$1:$A$5',
1778 gradient => {
1779 colors => [ '#DDEBCF', '#156B13' ],
1780 positions => [ 10, 90 ],
1781 }
1782 );
1783 The "type" property can have one of the following values:
1785 linear (the default)
1787 radial
1788 rectangular
1789 path
1790 For example:
1792 $chart->add_series(
1794 values => '=Sheet1!$A$1:$A$5',
1795 gradient => {
1796 colors => [ '#DDEBCF', '#9CB86E', '#156B13' ],
1797 type => 'radial'
1798 }
1799 );
1800 If "type" isn't specified it defaults to "linear".
1802 For a "linear" fill the angle of the gradient can also be specified:
1804 $chart->add_series(
1806 values => '=Sheet1!$A$1:$A$5',
1807 gradient => { colors => [ '#DDEBCF', '#9CB86E', '#156B13' ],
1808 angle => 30 }
1809 );
1810 The default angle is 90 degrees.
1812 If gradient fill is used on a chart object it overrides the solid fill
1814 and pattern fill properties of the object.
1815
1817 The following font properties can be set for any chart object that they
1818 apply to (and that are supported by Excel::Writer::XLSX) such as chart
1819 titles, axis labels, axis numbering and data labels. They correspond to
1820 the equivalent Worksheet cell Format object properties. See
1821 "FORMAT_METHODS" in Excel::Writer::XLSX for more information.
1822 name
1824 size
1825 bold
1826 italic
1827 underline
1828 rotation
1829 color
1830 The following explains the available font properties:
1832 · "name"
1834 Set the font name:
1836 $chart->set_x_axis( num_font => { name => 'Arial' } );
1838 · "size"
1840 Set the font size:
1842 $chart->set_x_axis( num_font => { name => 'Arial', size => 10 } );
1844 · "bold"
1846 Set the font bold property, should be 0 or 1:
1848 $chart->set_x_axis( num_font => { bold => 1 } );
1850 · "italic"
1852 Set the font italic property, should be 0 or 1:
1854 $chart->set_x_axis( num_font => { italic => 1 } );
1856 · "underline"
1858 Set the font underline property, should be 0 or 1:
1860 $chart->set_x_axis( num_font => { underline => 1 } );
1862 · "rotation"
1864 Set the font rotation in the range -90 to 90:
1866 $chart->set_x_axis( num_font => { rotation => 45 } );
1868 This is useful for displaying large axis data such as dates in a
1870 more compact format.
1871 · "color"
1873 Set the font color property. Can be a color index, a color name or
1875 HTML style RGB colour:
1876 $chart->set_x_axis( num_font => { color => 'red' } );
1878 $chart->set_y_axis( num_font => { color => '#92D050' } );
1879 Here is an example of Font formatting in a Chart program:
1881 # Format the chart title.
1883 $chart->set_title(
1884 name => 'Sales Results Chart',
1885 name_font => {
1886 name => 'Calibri',
1887 color => 'yellow',
1888 },
1889 );
1890 # Format the X-axis.
1892 $chart->set_x_axis(
1893 name => 'Month',
1894 name_font => {
1895 name => 'Arial',
1896 color => '#92D050'
1897 },
1898 num_font => {
1899 name => 'Courier New',
1900 color => '#00B0F0',
1901 },
1902 );
1903 # Format the Y-axis.
1905 $chart->set_y_axis(
1906 name => 'Sales (1000 units)',
1907 name_font => {
1908 name => 'Century',
1909 underline => 1,
1910 color => 'red'
1911 },
1912 num_font => {
1913 bold => 1,
1914 italic => 1,
1915 color => '#7030A0',
1916 },
1917 );
1918
1920 The position of the chart in the worksheet is controlled by the
1921 "set_size()" method shown above.
1922 It is also possible to change the layout of the following chart sub-
1924 objects:
1925 plotarea
1927 legend
1928 title
1929 x_axis caption
1930 y_axis caption
1931 Here are some examples:
1933 $chart->set_plotarea(
1935 layout => {
1936 x => 0.35,
1937 y => 0.26,
1938 width => 0.62,
1939 height => 0.50,
1940 }
1941 );
1942 $chart->set_legend(
1944 layout => {
1945 x => 0.80,
1946 y => 0.37,
1947 width => 0.12,
1948 height => 0.25,
1949 }
1950 );
1951 $chart->set_title(
1953 name => 'Title',
1954 layout => {
1955 x => 0.42,
1956 y => 0.14,
1957 }
1958 );
1959 $chart->set_x_axis(
1961 name => 'X axis',
1962 name_layout => {
1963 x => 0.34,
1964 y => 0.85,
1965 }
1966 );
1967 Note that it is only possible to change the width and height for the
1969 "plotarea" and "legend" objects. For the other text based objects the
1970 width and height are changed by the font dimensions.
1971 The layout units must be a float in the range "0 < x <= 1" and are
1973 expressed as a percentage of the chart dimensions as shown below:
1974 From this the layout units are calculated as follows:
1976 layout:
1978 width = w / W
1979 height = h / H
1980 x = a / W
1981 y = b / H
1982 These units are slightly cumbersome but are required by Excel so that
1984 the chart object positions remain relative to each other if the chart
1985 is resized by the user.
1986 Note that for "plotarea" the origin is the top left corner in the
1988 plotarea itself and does not take into account the axes.
1989
1991 In Excel a chartsheet (i.e, a chart that isn't embedded) shares
1992 properties with data worksheets such as tab selection, headers,
1993 footers, margins, and print properties.
1994 In Excel::Writer::XLSX you can set chartsheet properties using the same
1996 methods that are used for Worksheet objects.
1997 The following Worksheet methods are also available through a non-
1999 embedded Chart object:
2000 get_name()
2002 activate()
2003 select()
2004 hide()
2005 set_first_sheet()
2006 protect()
2007 set_zoom()
2008 set_tab_color()
2009 set_landscape()
2011 set_portrait()
2012 set_paper()
2013 set_margins()
2014 set_header()
2015 set_footer()
2016 See Excel::Writer::XLSX for a detailed explanation of these methods.
2018
2020 Here is a complete example that demonstrates some of the available
2021 features when creating a chart.
2022 #!/usr/bin/perl
2024 use strict;
2026 use warnings;
2027 use Excel::Writer::XLSX;
2028 my $workbook = Excel::Writer::XLSX->new( 'chart.xlsx' );
2030 my $worksheet = $workbook->add_worksheet();
2031 my $bold = $workbook->add_format( bold => 1 );
2032 # Add the worksheet data that the charts will refer to.
2034 my $headings = [ 'Number', 'Batch 1', 'Batch 2' ];
2035 my $data = [
2036 [ 2, 3, 4, 5, 6, 7 ],
2037 [ 10, 40, 50, 20, 10, 50 ],
2038 [ 30, 60, 70, 50, 40, 30 ],
2039 ];
2041 $worksheet->write( 'A1', $headings, $bold );
2043 $worksheet->write( 'A2', $data );
2044 # Create a new chart object. In this case an embedded chart.
2046 my $chart = $workbook->add_chart( type => 'column', embedded => 1 );
2047 # Configure the first series.
2049 $chart->add_series(
2050 name => '=Sheet1!$B$1',
2051 categories => '=Sheet1!$A$2:$A$7',
2052 values => '=Sheet1!$B$2:$B$7',
2053 );
2054 # Configure second series. Note alternative use of array ref to define
2056 # ranges: [ $sheetname, $row_start, $row_end, $col_start, $col_end ].
2057 $chart->add_series(
2058 name => '=Sheet1!$C$1',
2059 categories => [ 'Sheet1', 1, 6, 0, 0 ],
2060 values => [ 'Sheet1', 1, 6, 2, 2 ],
2061 );
2062 # Add a chart title and some axis labels.
2064 $chart->set_title ( name => 'Results of sample analysis' );
2065 $chart->set_x_axis( name => 'Test number' );
2066 $chart->set_y_axis( name => 'Sample length (mm)' );
2067 # Set an Excel chart style. Blue colors with white outline and shadow.
2069 $chart->set_style( 11 );
2070 # Insert the chart into the worksheet (with an offset).
2072 $worksheet->insert_chart( 'D2', $chart, 25, 10 );
2073 $workbook->close();
2075 __END__
2077
2079 Excel differentiates between a chart axis that is used for series
2080 categories and an axis that is used for series values.
2081 In the example above the X axis is the category axis and each of the
2083 values is evenly spaced. The Y axis (in this case) is the value axis
2084 and points are displayed according to their value.
2085 Since Excel treats the axes differently it also handles their
2087 formatting differently and exposes different properties for each.
2088 As such some of "Excel::Writer::XLSX" axis properties can be set for a
2090 value axis, some can be set for a category axis and some properties can
2091 be set for both.
2092 For example the "min" and "max" properties can only be set for value
2094 axes and "reverse" can be set for both. The type of axis that a
2095 property applies to is shown in the "set_x_axis()" section of the
2096 documentation above.
2097 Some charts such as "Scatter" and "Stock" have two value axes.
2099 Date Axes are a special type of category axis which are explained
2101 below.
2102
2104 Date Category Axes are category axes that display time or date
2105 information. In Excel::Writer::XLSX Date Category Axes are set using
2106 the "date_axis" option:
2107 $chart->set_x_axis( date_axis => 1 );
2109 In general you should also specify a number format for a date axis
2111 although Excel will usually default to the same format as the data
2112 being plotted:
2113 $chart->set_x_axis(
2115 date_axis => 1,
2116 num_format => 'dd/mm/yyyy',
2117 );
2118 Excel doesn't normally allow minimum and maximum values to be set for
2120 category axes. However, date axes are an exception. The "min" and "max"
2121 values should be set as Excel times or dates:
2122 $chart->set_x_axis(
2124 date_axis => 1,
2125 min => $worksheet->convert_date_time('2013-01-02T'),
2126 max => $worksheet->convert_date_time('2013-01-09T'),
2127 num_format => 'dd/mm/yyyy',
2128 );
2129 For date axes it is also possible to set the type of the major and
2131 minor units:
2132 $chart->set_x_axis(
2134 date_axis => 1,
2135 minor_unit => 4,
2136 minor_unit_type => 'months',
2137 major_unit => 1,
2138 major_unit_type => 'years',
2139 num_format => 'dd/mm/yyyy',
2140 );
2141
2143 It is possible to add a secondary axis of the same type to a chart by
2144 setting the "y2_axis" or "x2_axis" property of the series:
2145 #!/usr/bin/perl
2147 use strict;
2149 use warnings;
2150 use Excel::Writer::XLSX;
2151 my $workbook = Excel::Writer::XLSX->new( 'chart_secondary_axis.xlsx' );
2153 my $worksheet = $workbook->add_worksheet();
2154 # Add the worksheet data that the charts will refer to.
2156 my $data = [
2157 [ 2, 3, 4, 5, 6, 7 ],
2158 [ 10, 40, 50, 20, 10, 50 ],
2159 ];
2161 $worksheet->write( 'A1', $data );
2163 # Create a new chart object. In this case an embedded chart.
2165 my $chart = $workbook->add_chart( type => 'line', embedded => 1 );
2166 # Configure a series with a secondary axis
2168 $chart->add_series(
2169 values => '=Sheet1!$A$1:$A$6',
2170 y2_axis => 1,
2171 );
2172 $chart->add_series(
2174 values => '=Sheet1!$B$1:$B$6',
2175 );
2176 # Insert the chart into the worksheet.
2179 $worksheet->insert_chart( 'D2', $chart );
2180 $workbook->close();
2182 __END__
2184 It is also possible to have a secondary, combined, chart either with a
2186 shared or secondary axis, see below.
2187
2189 It is also possible to combine two different chart types, for example a
2190 column and line chart to create a Pareto chart using the Chart
2191 "combine()" method:
2192 Here is a simpler example:
2194 use strict;
2196 use warnings;
2197 use Excel::Writer::XLSX;
2198 my $workbook = Excel::Writer::XLSX->new( 'chart_combined.xlsx' );
2200 my $worksheet = $workbook->add_worksheet();
2201 my $bold = $workbook->add_format( bold => 1 );
2202 # Add the worksheet data that the charts will refer to.
2204 my $headings = [ 'Number', 'Batch 1', 'Batch 2' ];
2205 my $data = [
2206 [ 2, 3, 4, 5, 6, 7 ],
2207 [ 10, 40, 50, 20, 10, 50 ],
2208 [ 30, 60, 70, 50, 40, 30 ],
2209 ];
2211 $worksheet->write( 'A1', $headings, $bold );
2213 $worksheet->write( 'A2', $data );
2214 #
2216 # In the first example we will create a combined column and line chart.
2217 # They will share the same X and Y axes.
2218 #
2219 # Create a new column chart. This will use this as the primary chart.
2221 my $column_chart = $workbook->add_chart( type => 'column', embedded => 1 );
2222 # Configure the data series for the primary chart.
2224 $column_chart->add_series(
2225 name => '=Sheet1!$B$1',
2226 categories => '=Sheet1!$A$2:$A$7',
2227 values => '=Sheet1!$B$2:$B$7',
2228 );
2229 # Create a new column chart. This will use this as the secondary chart.
2231 my $line_chart = $workbook->add_chart( type => 'line', embedded => 1 );
2232 # Configure the data series for the secondary chart.
2234 $line_chart->add_series(
2235 name => '=Sheet1!$C$1',
2236 categories => '=Sheet1!$A$2:$A$7',
2237 values => '=Sheet1!$C$2:$C$7',
2238 );
2239 # Combine the charts.
2241 $column_chart->combine( $line_chart );
2242 # Add a chart title and some axis labels. Note, this is done via the
2244 # primary chart.
2245 $column_chart->set_title( name => 'Combined chart - same Y axis' );
2246 $column_chart->set_x_axis( name => 'Test number' );
2247 $column_chart->set_y_axis( name => 'Sample length (mm)' );
2248 # Insert the chart into the worksheet
2251 $worksheet->insert_chart( 'E2', $column_chart );
2252 $workbook->close();
2254 The secondary chart can also be placed on a secondary axis using the
2256 methods shown in the previous section.
2257 In this case it is just necessary to add a "y2_axis" parameter to the
2259 series and, if required, add a title using "set_y2_axis()" of the
2260 secondary chart. The following are the additions to the previous
2261 example to place the secondary chart on the secondary axis:
2262 ...
2264 $line_chart->add_series(
2266 name => '=Sheet1!$C$1',
2267 categories => '=Sheet1!$A$2:$A$7',
2268 values => '=Sheet1!$C$2:$C$7',
2269 y2_axis => 1,
2270 );
2271 ...
2273 # Note: the y2 properites are on the secondary chart.
2275 $line_chart2->set_y2_axis( name => 'Target length (mm)' );
2276 The examples above use the concept of a primary and secondary chart.
2278 The primary chart is the chart that defines the primary X and Y axis.
2279 It is also used for setting all chart properties apart from the
2280 secondary data series. For example the chart title and axes properties
2281 should be set via the primary chart (except for the the secondary "y2"
2282 axis properties which should be applied to the secondary chart).
2283 See also "chart_combined.pl" and "chart_pareto.pl" examples in the
2285 distro for more detailed examples.
2286 There are some limitations on combined charts:
2288 · Pie charts cannot currently be combined.
2290 · Scatter charts cannot currently be used as a primary chart but they
2292 can be used as a secondary chart.
2293 · Bar charts can only combined secondary charts on a secondary axis.
2295 This is an Excel limitation.
2296
2298 Chart features that are on the TODO list and will hopefully be added
2299 are:
2300 · Add more chart sub-types.
2302 · Additional formatting options.
2304 · More axis controls.
2306 · 3D charts.
2308 · Additional chart types.
2310 If you are interested in sponsoring a feature to have it implemented or
2312 expedited let me know.
2313
2315 John McNamara jmcnamara@cpan.org
2316
2318 Copyright MM-MMXVIII, John McNamara.
2319 All Rights Reserved. This module is free software. It may be used,
2321 redistributed and/or modified under the same terms as Perl itself.
2322perl v5.28.0 2018-04-14 Excel::Writer::XLSX::Chart(3)