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 top_right
788 overlay_left
789 overlay_right
790 overlay_top_right
791 none
792 · "border"
794 Set the border properties of the legend such as colour and style.
796 See the "CHART FORMATTING" section below.
797 · "fill"
799 Set the fill properties of the legend such as colour. See the
801 "CHART FORMATTING" section below.
802 · "pattern"
804 Set the pattern fill properties of the legend. See the "CHART
806 FORMATTING" section below.
807 · "gradient"
809 Set the gradient fill properties of the legend. See the "CHART
811 FORMATTING" section below.
812 · "font"
814 Set the font properties of the chart legend:
816 $chart->set_legend( font => { bold => 1, italic => 1 } );
818 See the "CHART FONTS" section below.
820 · "delete_series"
822 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 # Delete/hide series index 0 and 2 from the legend.
828 $chart->set_legend( delete_series => [0, 2] );
829 · "layout"
831 Set the "(x, y)" position of the legend in chart relative units:
833 $chart->set_legend(
835 layout => {
836 x => 0.80,
837 y => 0.37,
838 width => 0.12,
839 height => 0.25,
840 }
841 );
842 See the "CHART LAYOUT" section below.
844 set_chartarea()
846 The "set_chartarea()" method is used to set the properties of the chart
847 area.
848 $chart->set_chartarea(
850 border => { none => 1 },
851 fill => { color => 'red' }
852 );
853 The properties that can be set are:
855 · "border"
857 Set the border properties of the chartarea such as colour and
859 style. See the "CHART FORMATTING" section below.
860 · "fill"
862 Set the fill properties of the chartarea such as colour. See the
864 "CHART FORMATTING" section below.
865 · "pattern"
867 Set the pattern fill properties of the chartarea. See the "CHART
869 FORMATTING" section below.
870 · "gradient"
872 Set the gradient fill properties of the chartarea. See the "CHART
874 FORMATTING" section below.
875 set_plotarea()
877 The "set_plotarea()" method is used to set properties of the plot area
878 of a chart.
879 $chart->set_plotarea(
881 border => { color => 'yellow', width => 1, dash_type => 'dash' },
882 fill => { color => '#92D050' }
883 );
884 The properties that can be set are:
886 · "border"
888 Set the border properties of the plotarea such as colour and style.
890 See the "CHART FORMATTING" section below.
891 · "fill"
893 Set the fill properties of the plotarea such as colour. See the
895 "CHART FORMATTING" section below.
896 · "pattern"
898 Set the pattern fill properties of the plotarea. See the "CHART
900 FORMATTING" section below.
901 · "gradient"
903 Set the gradient fill properties of the plotarea. See the "CHART
905 FORMATTING" section below.
906 · "layout"
908 Set the "(x, y)" position of the plotarea in chart relative units:
910 $chart->set_plotarea(
912 layout => {
913 x => 0.35,
914 y => 0.26,
915 width => 0.62,
916 height => 0.50,
917 }
918 );
919 See the "CHART LAYOUT" section below.
921 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 $chart->set_style( 4 );
927 The default style is 2.
929 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 $chart->set_table();
935 The available options, with default values are:
937 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 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 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 $chart->set_up_down_bars();
952 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 $chart->set_up_down_bars(
958 up => { fill => { color => 'green' } },
959 down => { fill => { color => 'red' } },
960 );
961 Up-down bars can only be applied to Line charts and to Stock charts (by
963 default).
964 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 $chart->set_drop_lines();
970 It is possible to format the Drop Line "line" properties if required.
972 See the "CHART FORMATTING" section below.
973 $chart->set_drop_lines( line => { color => 'red', dash_type => 'square_dot' } );
975 Drop Lines are only available in Line, Area and Stock charts.
977 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 $chart->set_high_low_lines();
983 It is possible to format the High-Low Line "line" properties if
985 required. See the "CHART FORMATTING" section below.
986 $chart->set_high_low_lines( line => { color => 'red' } );
988 High-Low Lines are only available in Line and Stock charts.
990 show_blanks_as()
992 The "show_blanks_as()" method controls how blank data is displayed in a
993 chart.
994 $chart->show_blanks_as( 'span' );
996 The available options are:
998 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 show_hidden_data()
1004 Display data in hidden rows or columns on the chart.
1005 $chart->show_hidden_data();
1007
1009 This section details the following properties of "add_series()" in more
1010 detail:
1011 marker
1013 trendline
1014 y_error_bars
1015 x_error_bars
1016 data_labels
1017 points
1018 smooth
1019 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 The following properties can be set for "marker" formats in a chart.
1026 type
1028 size
1029 border
1030 fill
1031 pattern
1032 gradient
1033 The "type" property sets the type of marker that is used with a series.
1035 $chart->add_series(
1037 values => '=Sheet1!$B$1:$B$5',
1038 marker => { type => 'diamond' },
1039 );
1040 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 automatic
1045 none
1046 square
1047 diamond
1048 triangle
1049 x
1050 star
1051 short_dash
1052 long_dash
1053 circle
1054 plus
1055 The "automatic" type is a special case which turns on a marker using
1057 the default marker style for the particular series number.
1058 $chart->add_series(
1060 values => '=Sheet1!$B$1:$B$5',
1061 marker => { type => 'automatic' },
1062 );
1063 If "automatic" is on then other marker properties such as size, border
1065 or fill cannot be set.
1066 The "size" property sets the size of the marker and is generally used
1068 in conjunction with "type".
1069 $chart->add_series(
1071 values => '=Sheet1!$B$1:$B$5',
1072 marker => { type => 'diamond', size => 7 },
1073 );
1074 Nested "border" and "fill" properties can also be set for a marker. See
1076 the "CHART FORMATTING" section below.
1077 $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 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 The following properties can be set for trendlines in a chart series.
1093 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 The "type" property sets the type of trendline in the series.
1106 $chart->add_series(
1108 values => '=Sheet1!$B$1:$B$5',
1109 trendline => { type => 'linear' },
1110 );
1111 The available "trendline" types are:
1113 exponential
1115 linear
1116 log
1117 moving_average
1118 polynomial
1119 power
1120 A "polynomial" trendline can also specify the "order" of the
1122 polynomial. The default value is 2.
1123 $chart->add_series(
1125 values => '=Sheet1!$B$1:$B$5',
1126 trendline => {
1127 type => 'polynomial',
1128 order => 3,
1129 },
1130 );
1131 A "moving_average" trendline can also specify the "period" of the
1133 moving average. The default value is 2.
1134 $chart->add_series(
1136 values => '=Sheet1!$B$1:$B$5',
1137 trendline => {
1138 type => 'moving_average',
1139 period => 3,
1140 },
1141 );
1142 The "forward" and "backward" properties set the forecast period of the
1144 trendline.
1145 $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 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 $chart->add_series(
1161 values => '=Sheet1!$B$1:$B$5',
1162 trendline => {
1163 type => 'linear',
1164 name => 'Interpolated trend',
1165 },
1166 );
1167 The "intercept" property sets the point where the trendline crosses the
1169 Y (value) axis:
1170 $chart->add_series(
1172 values => '=Sheet1!$B$1:$B$5',
1173 trendline => {
1174 type => 'linear',
1175 intercept => 0.8,
1176 },
1177 );
1178 The "display_equation" property displays the trendline equation on the
1180 chart.
1181 $chart->add_series(
1183 values => '=Sheet1!$B$1:$B$5',
1184 trendline => {
1185 type => 'linear',
1186 display_equation => 1,
1187 },
1188 );
1189 The "display_r_squared" property displays the R squared value of the
1191 trendline on the chart.
1192 $chart->add_series(
1194 values => '=Sheet1!$B$1:$B$5',
1195 trendline => {
1196 type => 'linear',
1197 display_r_squared => 1
1198 },
1199 );
1200 Several of these properties can be set in one go:
1202 $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 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 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 The following properties can be set for error bars in a chart series.
1232 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 The "type" property sets the type of error bars in the series.
1242 $chart->add_series(
1244 values => '=Sheet1!$B$1:$B$5',
1245 y_error_bars => { type => 'standard_error' },
1246 );
1247 The available error bars types are available:
1249 fixed
1251 percentage
1252 standard_deviation
1253 standard_error
1254 custom
1255 All error bar types, except for "standard_error" and "custom" must also
1257 have a value associated with it for the error bounds:
1258 $chart->add_series(
1260 values => '=Sheet1!$B$1:$B$5',
1261 y_error_bars => {
1262 type => 'percentage',
1263 value => 5,
1264 },
1265 );
1266 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 $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 # or
1282 $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 Note, as in Excel the items in the "minus_values" do not need to be
1295 negative.
1296 The "direction" property sets the direction of the error bars. It
1298 should be one of the following:
1299 plus # Positive direction only.
1301 minus # Negative direction only.
1302 both # Plus and minus directions, The default.
1303 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 $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 Data Labels
1318 Data labels can be added to a chart series to indicate the values of
1319 the plotted data points.
1320 The following properties can be set for "data_labels" formats in a
1322 chart.
1323 value
1325 category
1326 series_name
1327 position
1328 percentage
1329 leader_lines
1330 separator
1331 legend_key
1332 num_format
1333 font
1334 The "value" property turns on the Value data label for a series.
1336 $chart->add_series(
1338 values => '=Sheet1!$B$1:$B$5',
1339 data_labels => { value => 1 },
1340 );
1341 The "category" property turns on the Category Name data label for a
1343 series.
1344 $chart->add_series(
1346 values => '=Sheet1!$B$1:$B$5',
1347 data_labels => { category => 1 },
1348 );
1349 The "series_name" property turns on the Series Name data label for a
1351 series.
1352 $chart->add_series(
1354 values => '=Sheet1!$B$1:$B$5',
1355 data_labels => { series_name => 1 },
1356 );
1357 The "position" property is used to position the data label for a
1359 series.
1360 $chart->add_series(
1362 values => '=Sheet1!$B$1:$B$5',
1363 data_labels => { value => 1, position => 'center' },
1364 );
1365 In Excel the data label positions vary for different chart types. The
1367 allowable positions are:
1368 | 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 Note: The * indicates the default position for each chart type in
1384 Excel, if a position isn't specified.
1385 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 $chart->add_series(
1391 values => '=Sheet1!$B$1:$B$5',
1392 data_labels => { percentage => 1 },
1393 );
1394 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 $chart->add_series(
1399 values => '=Sheet1!$B$1:$B$5',
1400 data_labels => { value => 1, leader_lines => 1 },
1401 );
1402 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 The "separator" property is used to change the separator between
1410 multiple data label items:
1411 $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 The separator value must be one of the following strings:
1419 ','
1421 ';'
1422 '.'
1423 "\n"
1424 ' '
1425 The "legend_key" property is used to turn on Legend Key for the data
1427 label for a series:
1428 $chart->add_series(
1430 values => '=Sheet1!$B$1:$B$5',
1431 data_labels => { value => 1, legend_key => 1 },
1432 );
1433 The "num_format" property is used to set the number format for the data
1435 labels.
1436 $chart->add_series(
1438 values => '=Sheet1!$A$1:$A$5',
1439 data_labels => { value => 1, num_format => '#,##0.00' },
1440 );
1441 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 The "font" property is used to set the font properties of the data
1448 labels in a series:
1449 $chart->add_series(
1451 values => '=Sheet1!$A$1:$A$5',
1452 data_labels => {
1453 value => 1,
1454 font => { name => 'Consolas' }
1455 },
1456 );
1457 The "font" property is also used to rotate the data labels in a series:
1459 $chart->add_series(
1461 values => '=Sheet1!$A$1:$A$5',
1462 data_labels => {
1463 value => 1,
1464 font => { rotation => 45 }
1465 },
1466 );
1467 See the "CHART FONTS" section below.
1469 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 In these cases it is possible to use the "points" property of
1477 "add_series()":
1478 $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 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 # 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 # Format the first point only.
1503 $chart->add_series(
1504 values => '=Sheet1!$A$1:$A$3',
1505 points => [ { fill => { color => '#FF0000' } } ],
1506 );
1507 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 $chart->add_series( values => '=Sheet1!$C$1:$C$5',
1513 smooth => 1 );
1514
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 line
1522 border
1523 fill
1524 pattern
1525 gradient
1526 Chart formatting properties are generally set using hash refs.
1528 $chart->add_series(
1530 values => '=Sheet1!$B$1:$B$5',
1531 line => { color => 'blue' },
1532 );
1533 In some cases the format properties can be nested. For example a
1535 "marker" may contain "border" and "fill" sub-properties.
1536 $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 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 The following properties can be set for "line" formats in a chart.
1553 none
1555 color
1556 width
1557 dash_type
1558 transparency
1559 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 $chart->add_series(
1565 values => '=Sheet1!$B$1:$B$5',
1566 line => { none => 1 },
1567 );
1568 The "color" property sets the color of the "line".
1570 $chart->add_series(
1572 values => '=Sheet1!$B$1:$B$5',
1573 line => { color => 'red' },
1574 );
1575 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 $chart->add_series(
1581 line => { color => '#FF0000' },
1582 );
1583 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 $chart->add_series(
1588 values => '=Sheet1!$B$1:$B$5',
1589 line => { width => 3.25 },
1590 );
1591 The "dash_type" property sets the dash style of the line.
1593 $chart->add_series(
1595 values => '=Sheet1!$B$1:$B$5',
1596 line => { dash_type => 'dash_dot' },
1597 );
1598 The following "dash_type" values are available. They are shown in the
1600 order that they appear in the Excel dialog.
1601 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 The default line style is "solid".
1612 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 $chart->add_series(
1618 values => '=Sheet1!$B$1:$B$5',
1619 line => { color => 'yellow', transparency => 50 },
1620 );
1621 More than one "line" property can be specified at a time:
1623 $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 Border
1634 The "border" property is a synonym for "line".
1635 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 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 The following properties can be set for "fill" formats in a chart.
1646 none
1648 color
1649 transparency
1650 The "none" property is used to turn the "fill" property off (it is
1652 generally on by default).
1653 $chart->add_series(
1655 values => '=Sheet1!$B$1:$B$5',
1656 fill => { none => 1 },
1657 );
1658 The "color" property sets the colour of the "fill" area.
1660 $chart->add_series(
1662 values => '=Sheet1!$B$1:$B$5',
1663 fill => { color => 'red' },
1664 );
1665 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 $chart->add_series(
1671 fill => { color => '#FF0000' },
1672 );
1673 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 $chart->set_chartarea( fill => { color => 'yellow', transparency => 75 } );
1679 The "fill" format is generally used in conjunction with a "border"
1681 format which has the same properties as a "line" format.
1682 $chart->add_series(
1684 values => '=Sheet1!$B$1:$B$5',
1685 border => { color => 'red' },
1686 fill => { color => 'yellow' },
1687 );
1688 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 The following properties can be set for "pattern" fill formats in a
1695 chart:
1696 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 For example:
1702 $chart->set_plotarea(
1704 pattern => {
1705 pattern => 'percent_5',
1706 fg_color => 'red',
1707 bg_color => 'yellow',
1708 }
1709 );
1710 The following patterns can be applied:
1712 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 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 The background color, "bg_color", is optional and defaults to black.
1768 If a pattern fill is used on a chart object it overrides the solid fill
1770 properties of the object.
1771 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 The following properties can be set for "gradient" fill formats in a
1778 chart:
1779 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 The "colors" property sets a list of colors that define the "gradient":
1786 $chart->set_plotarea(
1788 gradient => { colors => [ '#DDEBCF', '#9CB86E', '#156B13' ] }
1789 );
1790 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 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 $chart->add_series(
1800 values => '=Sheet1!$A$1:$A$5',
1801 gradient => { colors => [ 'red', 'green' ] }
1802 );
1803 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 $chart->add_series(
1810 values => '=Sheet1!$A$1:$A$5',
1811 gradient => {
1812 colors => [ '#DDEBCF', '#156B13' ],
1813 positions => [ 10, 90 ],
1814 }
1815 );
1816 The "type" property can have one of the following values:
1818 linear (the default)
1820 radial
1821 rectangular
1822 path
1823 For example:
1825 $chart->add_series(
1827 values => '=Sheet1!$A$1:$A$5',
1828 gradient => {
1829 colors => [ '#DDEBCF', '#9CB86E', '#156B13' ],
1830 type => 'radial'
1831 }
1832 );
1833 If "type" isn't specified it defaults to "linear".
1835 For a "linear" fill the angle of the gradient can also be specified:
1837 $chart->add_series(
1839 values => '=Sheet1!$A$1:$A$5',
1840 gradient => { colors => [ '#DDEBCF', '#9CB86E', '#156B13' ],
1841 angle => 30 }
1842 );
1843 The default angle is 90 degrees.
1845 If gradient fill is used on a chart object it overrides the solid fill
1847 and pattern fill properties of the object.
1848
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 name
1857 size
1858 bold
1859 italic
1860 underline
1861 rotation
1862 color
1863 The following explains the available font properties:
1865 · "name"
1867 Set the font name:
1869 $chart->set_x_axis( num_font => { name => 'Arial' } );
1871 · "size"
1873 Set the font size:
1875 $chart->set_x_axis( num_font => { name => 'Arial', size => 10 } );
1877 · "bold"
1879 Set the font bold property, should be 0 or 1:
1881 $chart->set_x_axis( num_font => { bold => 1 } );
1883 · "italic"
1885 Set the font italic property, should be 0 or 1:
1887 $chart->set_x_axis( num_font => { italic => 1 } );
1889 · "underline"
1891 Set the font underline property, should be 0 or 1:
1893 $chart->set_x_axis( num_font => { underline => 1 } );
1895 · "rotation"
1897 Set the font rotation in the range -90 to 90:
1899 $chart->set_x_axis( num_font => { rotation => 45 } );
1901 This is useful for displaying large axis data such as dates in a
1903 more compact format.
1904 · "color"
1906 Set the font color property. Can be a color index, a color name or
1908 HTML style RGB colour:
1909 $chart->set_x_axis( num_font => { color => 'red' } );
1911 $chart->set_y_axis( num_font => { color => '#92D050' } );
1912 Here is an example of Font formatting in a Chart program:
1914 # 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 # 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 # 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
1953 The position of the chart in the worksheet is controlled by the
1954 "set_size()" method shown above.
1955 It is also possible to change the layout of the following chart sub-
1957 objects:
1958 plotarea
1960 legend
1961 title
1962 x_axis caption
1963 y_axis caption
1964 Here are some examples:
1966 $chart->set_plotarea(
1968 layout => {
1969 x => 0.35,
1970 y => 0.26,
1971 width => 0.62,
1972 height => 0.50,
1973 }
1974 );
1975 $chart->set_legend(
1977 layout => {
1978 x => 0.80,
1979 y => 0.37,
1980 width => 0.12,
1981 height => 0.25,
1982 }
1983 );
1984 $chart->set_title(
1986 name => 'Title',
1987 layout => {
1988 x => 0.42,
1989 y => 0.14,
1990 }
1991 );
1992 $chart->set_x_axis(
1994 name => 'X axis',
1995 name_layout => {
1996 x => 0.34,
1997 y => 0.85,
1998 }
1999 );
2000 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 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 From this the layout units are calculated as follows:
2009 layout:
2011 width = w / W
2012 height = h / H
2013 x = a / W
2014 y = b / H
2015 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 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
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 In Excel::Writer::XLSX you can set chartsheet properties using the same
2029 methods that are used for Worksheet objects.
2030 The following Worksheet methods are also available through a non-
2032 embedded Chart object:
2033 get_name()
2035 activate()
2036 select()
2037 hide()
2038 set_first_sheet()
2039 protect()
2040 set_zoom()
2041 set_tab_color()
2042 set_landscape()
2044 set_portrait()
2045 set_paper()
2046 set_margins()
2047 set_header()
2048 set_footer()
2049 See Excel::Writer::XLSX for a detailed explanation of these methods.
2051
2053 Here is a complete example that demonstrates some of the available
2054 features when creating a chart.
2055 #!/usr/bin/perl
2057 use strict;
2059 use warnings;
2060 use Excel::Writer::XLSX;
2061 my $workbook = Excel::Writer::XLSX->new( 'chart.xlsx' );
2063 my $worksheet = $workbook->add_worksheet();
2064 my $bold = $workbook->add_format( bold => 1 );
2065 # 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 ];
2074 $worksheet->write( 'A1', $headings, $bold );
2076 $worksheet->write( 'A2', $data );
2077 # Create a new chart object. In this case an embedded chart.
2079 my $chart = $workbook->add_chart( type => 'column', embedded => 1 );
2080 # 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 # 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 # 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 # Set an Excel chart style. Blue colors with white outline and shadow.
2102 $chart->set_style( 11 );
2103 # Insert the chart into the worksheet (with an offset).
2105 $worksheet->insert_chart( 'D2', $chart, 25, 10 );
2106 $workbook->close();
2108 __END__
2110
2112 Excel differentiates between a chart axis that is used for series
2113 categories and an axis that is used for series values.
2114 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 Since Excel treats the axes differently it also handles their
2120 formatting differently and exposes different properties for each.
2121 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 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 Some charts such as "Scatter" and "Stock" have two value axes.
2132 Date Axes are a special type of category axis which are explained
2134 below.
2135
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 $chart->set_x_axis( date_axis => 1 );
2142 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 $chart->set_x_axis(
2148 date_axis => 1,
2149 num_format => 'dd/mm/yyyy',
2150 );
2151 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 $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 For date axes it is also possible to set the type of the major and
2164 minor units:
2165 $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
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 #!/usr/bin/perl
2180 use strict;
2182 use warnings;
2183 use Excel::Writer::XLSX;
2184 my $workbook = Excel::Writer::XLSX->new( 'chart_secondary_axis.xlsx' );
2186 my $worksheet = $workbook->add_worksheet();
2187 # 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 ];
2194 $worksheet->write( 'A1', $data );
2196 # Create a new chart object. In this case an embedded chart.
2198 my $chart = $workbook->add_chart( type => 'line', embedded => 1 );
2199 # Configure a series with a secondary axis
2201 $chart->add_series(
2202 values => '=Sheet1!$A$1:$A$6',
2203 y2_axis => 1,
2204 );
2205 $chart->add_series(
2207 values => '=Sheet1!$B$1:$B$6',
2208 );
2209 # Insert the chart into the worksheet.
2212 $worksheet->insert_chart( 'D2', $chart );
2213 $workbook->close();
2215 __END__
2217 It is also possible to have a secondary, combined, chart either with a
2219 shared or secondary axis, see below.
2220
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 Here is a simpler example:
2227 use strict;
2229 use warnings;
2230 use Excel::Writer::XLSX;
2231 my $workbook = Excel::Writer::XLSX->new( 'chart_combined.xlsx' );
2233 my $worksheet = $workbook->add_worksheet();
2234 my $bold = $workbook->add_format( bold => 1 );
2235 # 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 ];
2244 $worksheet->write( 'A1', $headings, $bold );
2246 $worksheet->write( 'A2', $data );
2247 #
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 # 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 # 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 # 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 # 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 # Combine the charts.
2274 $column_chart->combine( $line_chart );
2275 # 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 # Insert the chart into the worksheet
2284 $worksheet->insert_chart( 'E2', $column_chart );
2285 $workbook->close();
2287 The secondary chart can also be placed on a secondary axis using the
2289 methods shown in the previous section.
2290 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 ...
2297 $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 ...
2306 # Note: the y2 properites are on the secondary chart.
2308 $line_chart2->set_y2_axis( name => 'Target length (mm)' );
2309 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 See also "chart_combined.pl" and "chart_pareto.pl" examples in the
2318 distro for more detailed examples.
2319 There are some limitations on combined charts:
2321 · Pie charts cannot currently be combined.
2323 · Scatter charts cannot currently be used as a primary chart but they
2325 can be used as a secondary chart.
2326 · Bar charts can only combined secondary charts on a secondary axis.
2328 This is an Excel limitation.
2329
2331 Chart features that are on the TODO list and will hopefully be added
2332 are:
2333 · Add more chart sub-types.
2335 · Additional formatting options.
2337 · More axis controls.
2339 · 3D charts.
2341 · Additional chart types.
2343 If you are interested in sponsoring a feature to have it implemented or
2345 expedited let me know.
2346
2348 John McNamara jmcnamara@cpan.org
2349
2351 Copyright MM-MMXIX, John McNamara.
2352 All Rights Reserved. This module is free software. It may be used,
2354 redistributed and/or modified under the same terms as Perl itself.
2355perl v5.30.0 2019-07-26 Excel::Writer::XLSX::Chart(3)