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, { x_offset =>2, y_offset => 4,
708 x_scale => 1.5, y_scale => 2 } );
709 set_title()
711 The "set_title()" method is used to set properties of the chart title.
712 $chart->set_title( name => 'Year End Results' );
714 The properties that can be set are:
716 · "name"
718 Set the name (title) for the chart. The name is displayed above the
720 chart. The name can also be a formula such as "=Sheet1!$A$1". The
721 name property is optional. The default is to have no chart title.
722 · "name_font"
724 Set the font properties for the chart title. See the "CHART FONTS"
726 section below.
727 · "overlay"
729 Allow the title to be overlaid on the chart. Generally used with
731 the layout property below.
732 · "layout"
734 Set the "(x, y)" position of the title in chart relative units:
736 $chart->set_title(
738 name => 'Title',
739 overlay => 1,
740 layout => {
741 x => 0.42,
742 y => 0.14,
743 }
744 );
745 See the "CHART LAYOUT" section below.
747 · "none"
749 By default Excel adds an automatic chart title to charts with a
751 single series and a user defined series name. The "none" option
752 turns this default title off. It also turns off all other
753 "set_title()" options.
754 $chart->set_title( none => 1 );
756 set_legend()
758 The "set_legend()" method is used to set properties of the chart
759 legend.
760 The properties that can be set are:
762 · "none"
764 The "none" option turns off the chart legend. In Excel chart
766 legends are on by default:
767 $chart->set_legend( none => 1 );
769 Note, for backward compatibility, it is also possible to turn off
771 the legend via the "position" property:
772 $chart->set_legend( position => 'none' );
774 · "position"
776 Set the position of the chart legend.
778 $chart->set_legend( position => 'bottom' );
780 The default legend position is "right". The available positions
782 are:
783 top
785 bottom
786 left
787 right
788 top_right
789 overlay_left
790 overlay_right
791 overlay_top_right
792 none
793 · "border"
795 Set the border properties of the legend such as colour and style.
797 See the "CHART FORMATTING" section below.
798 · "fill"
800 Set the fill properties of the legend such as colour. See the
802 "CHART FORMATTING" section below.
803 · "pattern"
805 Set the pattern fill properties of the legend. See the "CHART
807 FORMATTING" section below.
808 · "gradient"
810 Set the gradient fill properties of the legend. See the "CHART
812 FORMATTING" section below.
813 · "font"
815 Set the font properties of the chart legend:
817 $chart->set_legend( font => { bold => 1, italic => 1 } );
819 See the "CHART FONTS" section below.
821 · "delete_series"
823 This allows you to remove 1 or more series from the legend (the
825 series will still display on the chart). This property takes an
826 array ref as an argument and the series are zero indexed:
827 # Delete/hide series index 0 and 2 from the legend.
829 $chart->set_legend( delete_series => [0, 2] );
830 · "layout"
832 Set the "(x, y)" position of the legend in chart relative units:
834 $chart->set_legend(
836 layout => {
837 x => 0.80,
838 y => 0.37,
839 width => 0.12,
840 height => 0.25,
841 }
842 );
843 See the "CHART LAYOUT" section below.
845 set_chartarea()
847 The "set_chartarea()" method is used to set the properties of the chart
848 area.
849 $chart->set_chartarea(
851 border => { none => 1 },
852 fill => { color => 'red' }
853 );
854 The properties that can be set are:
856 · "border"
858 Set the border properties of the chartarea such as colour and
860 style. See the "CHART FORMATTING" section below.
861 · "fill"
863 Set the fill properties of the chartarea such as colour. See the
865 "CHART FORMATTING" section below.
866 · "pattern"
868 Set the pattern fill properties of the chartarea. See the "CHART
870 FORMATTING" section below.
871 · "gradient"
873 Set the gradient fill properties of the chartarea. See the "CHART
875 FORMATTING" section below.
876 set_plotarea()
878 The "set_plotarea()" method is used to set properties of the plot area
879 of a chart.
880 $chart->set_plotarea(
882 border => { color => 'yellow', width => 1, dash_type => 'dash' },
883 fill => { color => '#92D050' }
884 );
885 The properties that can be set are:
887 · "border"
889 Set the border properties of the plotarea such as colour and style.
891 See the "CHART FORMATTING" section below.
892 · "fill"
894 Set the fill properties of the plotarea such as colour. See the
896 "CHART FORMATTING" section below.
897 · "pattern"
899 Set the pattern fill properties of the plotarea. See the "CHART
901 FORMATTING" section below.
902 · "gradient"
904 Set the gradient fill properties of the plotarea. See the "CHART
906 FORMATTING" section below.
907 · "layout"
909 Set the "(x, y)" position of the plotarea in chart relative units:
911 $chart->set_plotarea(
913 layout => {
914 x => 0.35,
915 y => 0.26,
916 width => 0.62,
917 height => 0.50,
918 }
919 );
920 See the "CHART LAYOUT" section below.
922 set_style()
924 The "set_style()" method is used to set the style of the chart to one
925 of the 42 built-in styles available on the 'Design' tab in Excel:
926 $chart->set_style( 4 );
928 The default style is 2.
930 set_table()
932 The "set_table()" method adds a data table below the horizontal axis
933 with the data used to plot the chart.
934 $chart->set_table();
936 The available options, with default values are:
938 vertical => 1 # Display vertical lines in the table.
940 horizontal => 1 # Display horizontal lines in the table.
941 outline => 1 # Display an outline in the table.
942 show_keys => 0 # Show the legend keys with the table data.
943 font => {} # Standard chart font properties.
944 The data table can only be shown with Bar, Column, Line, Area and stock
946 charts. For font properties see the "CHART FONTS" section below.
947 set_up_down_bars
949 The "set_up_down_bars()" method adds Up-Down bars to Line charts to
950 indicate the difference between the first and last data series.
951 $chart->set_up_down_bars();
953 It is possible to format the up and down bars to add "fill", "pattern",
955 "gradient" and "border" properties if required. See the "CHART
956 FORMATTING" section below.
957 $chart->set_up_down_bars(
959 up => { fill => { color => 'green' } },
960 down => { fill => { color => 'red' } },
961 );
962 Up-down bars can only be applied to Line charts and to Stock charts (by
964 default).
965 set_drop_lines
967 The "set_drop_lines()" method adds Drop Lines to charts to show the
968 Category value of points in the data.
969 $chart->set_drop_lines();
971 It is possible to format the Drop Line "line" properties if required.
973 See the "CHART FORMATTING" section below.
974 $chart->set_drop_lines( line => { color => 'red', dash_type => 'square_dot' } );
976 Drop Lines are only available in Line, Area and Stock charts.
978 set_high_low_lines
980 The "set_high_low_lines()" method adds High-Low lines to charts to show
981 the maximum and minimum values of points in a Category.
982 $chart->set_high_low_lines();
984 It is possible to format the High-Low Line "line" properties if
986 required. See the "CHART FORMATTING" section below.
987 $chart->set_high_low_lines( line => { color => 'red' } );
989 High-Low Lines are only available in Line and Stock charts.
991 show_blanks_as()
993 The "show_blanks_as()" method controls how blank data is displayed in a
994 chart.
995 $chart->show_blanks_as( 'span' );
997 The available options are:
999 gap # Blank data is shown as a gap. The default.
1001 zero # Blank data is displayed as zero.
1002 span # Blank data is connected with a line.
1003 show_hidden_data()
1005 Display data in hidden rows or columns on the chart.
1006 $chart->show_hidden_data();
1008
1010 This section details the following properties of "add_series()" in more
1011 detail:
1012 marker
1014 trendline
1015 y_error_bars
1016 x_error_bars
1017 data_labels
1018 points
1019 smooth
1020 Marker
1022 The marker format specifies the properties of the markers used to
1023 distinguish series on a chart. In general only Line and Scatter chart
1024 types and trendlines use markers.
1025 The following properties can be set for "marker" formats in a chart.
1027 type
1029 size
1030 border
1031 fill
1032 pattern
1033 gradient
1034 The "type" property sets the type of marker that is used with a series.
1036 $chart->add_series(
1038 values => '=Sheet1!$B$1:$B$5',
1039 marker => { type => 'diamond' },
1040 );
1041 The following "type" properties can be set for "marker" formats in a
1043 chart. These are shown in the same order as in the Excel format dialog.
1044 automatic
1046 none
1047 square
1048 diamond
1049 triangle
1050 x
1051 star
1052 short_dash
1053 long_dash
1054 circle
1055 plus
1056 The "automatic" type is a special case which turns on a marker using
1058 the default marker style for the particular series number.
1059 $chart->add_series(
1061 values => '=Sheet1!$B$1:$B$5',
1062 marker => { type => 'automatic' },
1063 );
1064 If "automatic" is on then other marker properties such as size, border
1066 or fill cannot be set.
1067 The "size" property sets the size of the marker and is generally used
1069 in conjunction with "type".
1070 $chart->add_series(
1072 values => '=Sheet1!$B$1:$B$5',
1073 marker => { type => 'diamond', size => 7 },
1074 );
1075 Nested "border" and "fill" properties can also be set for a marker. See
1077 the "CHART FORMATTING" section below.
1078 $chart->add_series(
1080 values => '=Sheet1!$B$1:$B$5',
1081 marker => {
1082 type => 'square',
1083 size => 5,
1084 border => { color => 'red' },
1085 fill => { color => 'yellow' },
1086 },
1087 );
1088 Trendline
1090 A trendline can be added to a chart series to indicate trends in the
1091 data such as a moving average or a polynomial fit.
1092 The following properties can be set for trendlines in a chart series.
1094 type
1096 order (for polynomial trends)
1097 period (for moving average)
1098 forward (for all except moving average)
1099 backward (for all except moving average)
1100 name
1101 line
1102 intercept (for exponential, linear and polynomial only)
1103 display_equation (for all except moving average)
1104 display_r_squared (for all except moving average)
1105 The "type" property sets the type of trendline in the series.
1107 $chart->add_series(
1109 values => '=Sheet1!$B$1:$B$5',
1110 trendline => { type => 'linear' },
1111 );
1112 The available "trendline" types are:
1114 exponential
1116 linear
1117 log
1118 moving_average
1119 polynomial
1120 power
1121 A "polynomial" trendline can also specify the "order" of the
1123 polynomial. The default value is 2.
1124 $chart->add_series(
1126 values => '=Sheet1!$B$1:$B$5',
1127 trendline => {
1128 type => 'polynomial',
1129 order => 3,
1130 },
1131 );
1132 A "moving_average" trendline can also specify the "period" of the
1134 moving average. The default value is 2.
1135 $chart->add_series(
1137 values => '=Sheet1!$B$1:$B$5',
1138 trendline => {
1139 type => 'moving_average',
1140 period => 3,
1141 },
1142 );
1143 The "forward" and "backward" properties set the forecast period of the
1145 trendline.
1146 $chart->add_series(
1148 values => '=Sheet1!$B$1:$B$5',
1149 trendline => {
1150 type => 'linear',
1151 forward => 0.5,
1152 backward => 0.5,
1153 },
1154 );
1155 The "name" property sets an optional name for the trendline that will
1157 appear in the chart legend. If it isn't specified the Excel default
1158 name will be displayed. This is usually a combination of the trendline
1159 type and the series name.
1160 $chart->add_series(
1162 values => '=Sheet1!$B$1:$B$5',
1163 trendline => {
1164 type => 'linear',
1165 name => 'Interpolated trend',
1166 },
1167 );
1168 The "intercept" property sets the point where the trendline crosses the
1170 Y (value) axis:
1171 $chart->add_series(
1173 values => '=Sheet1!$B$1:$B$5',
1174 trendline => {
1175 type => 'linear',
1176 intercept => 0.8,
1177 },
1178 );
1179 The "display_equation" property displays the trendline equation on the
1181 chart.
1182 $chart->add_series(
1184 values => '=Sheet1!$B$1:$B$5',
1185 trendline => {
1186 type => 'linear',
1187 display_equation => 1,
1188 },
1189 );
1190 The "display_r_squared" property displays the R squared value of the
1192 trendline on the chart.
1193 $chart->add_series(
1195 values => '=Sheet1!$B$1:$B$5',
1196 trendline => {
1197 type => 'linear',
1198 display_r_squared => 1
1199 },
1200 );
1201 Several of these properties can be set in one go:
1203 $chart->add_series(
1205 values => '=Sheet1!$B$1:$B$5',
1206 trendline => {
1207 type => 'polynomial',
1208 name => 'My trend name',
1209 order => 2,
1210 forward => 0.5,
1211 backward => 0.5,
1212 intercept => 1.5,
1213 display_equation => 1,
1214 display_r_squared => 1,
1215 line => {
1216 color => 'red',
1217 width => 1,
1218 dash_type => 'long_dash',
1219 }
1220 },
1221 );
1222 Trendlines cannot be added to series in a stacked chart or pie chart,
1224 radar chart, doughnut or (when implemented) to 3D, or surface charts.
1225 Error Bars
1227 Error bars can be added to a chart series to indicate error bounds in
1228 the data. The error bars can be vertical "y_error_bars" (the most
1229 common type) or horizontal "x_error_bars" (for Bar and Scatter charts
1230 only).
1231 The following properties can be set for error bars in a chart series.
1233 type
1235 value (for all types except standard error and custom)
1236 plus_values (for custom only)
1237 minus_values (for custom only)
1238 direction
1239 end_style
1240 line
1241 The "type" property sets the type of error bars in the series.
1243 $chart->add_series(
1245 values => '=Sheet1!$B$1:$B$5',
1246 y_error_bars => { type => 'standard_error' },
1247 );
1248 The available error bars types are available:
1250 fixed
1252 percentage
1253 standard_deviation
1254 standard_error
1255 custom
1256 All error bar types, except for "standard_error" and "custom" must also
1258 have a value associated with it for the error bounds:
1259 $chart->add_series(
1261 values => '=Sheet1!$B$1:$B$5',
1262 y_error_bars => {
1263 type => 'percentage',
1264 value => 5,
1265 },
1266 );
1267 The "custom" error bar type must specify "plus_values" and
1269 "minus_values" which should either by a "Sheet1!$A$1:$A$5" type range
1270 formula or an arrayref of values:
1271 $chart->add_series(
1273 categories => '=Sheet1!$A$1:$A$5',
1274 values => '=Sheet1!$B$1:$B$5',
1275 y_error_bars => {
1276 type => 'custom',
1277 plus_values => '=Sheet1!$C$1:$C$5',
1278 minus_values => '=Sheet1!$D$1:$D$5',
1279 },
1280 );
1281 # or
1283 $chart->add_series(
1286 categories => '=Sheet1!$A$1:$A$5',
1287 values => '=Sheet1!$B$1:$B$5',
1288 y_error_bars => {
1289 type => 'custom',
1290 plus_values => [1, 1, 1, 1, 1],
1291 minus_values => [2, 2, 2, 2, 2],
1292 },
1293 );
1294 Note, as in Excel the items in the "minus_values" do not need to be
1296 negative.
1297 The "direction" property sets the direction of the error bars. It
1299 should be one of the following:
1300 plus # Positive direction only.
1302 minus # Negative direction only.
1303 both # Plus and minus directions, The default.
1304 The "end_style" property sets the style of the error bar end cap. The
1306 options are 1 (the default) or 0 (for no end cap):
1307 $chart->add_series(
1309 values => '=Sheet1!$B$1:$B$5',
1310 y_error_bars => {
1311 type => 'fixed',
1312 value => 2,
1313 end_style => 0,
1314 direction => 'minus'
1315 },
1316 );
1317 Data Labels
1319 Data labels can be added to a chart series to indicate the values of
1320 the plotted data points.
1321 The following properties can be set for "data_labels" formats in a
1323 chart.
1324 value
1326 category
1327 series_name
1328 position
1329 percentage
1330 leader_lines
1331 separator
1332 legend_key
1333 num_format
1334 font
1335 The "value" property turns on the Value data label for a series.
1337 $chart->add_series(
1339 values => '=Sheet1!$B$1:$B$5',
1340 data_labels => { value => 1 },
1341 );
1342 The "category" property turns on the Category Name data label for a
1344 series.
1345 $chart->add_series(
1347 values => '=Sheet1!$B$1:$B$5',
1348 data_labels => { category => 1 },
1349 );
1350 The "series_name" property turns on the Series Name data label for a
1352 series.
1353 $chart->add_series(
1355 values => '=Sheet1!$B$1:$B$5',
1356 data_labels => { series_name => 1 },
1357 );
1358 The "position" property is used to position the data label for a
1360 series.
1361 $chart->add_series(
1363 values => '=Sheet1!$B$1:$B$5',
1364 data_labels => { value => 1, position => 'center' },
1365 );
1366 In Excel the data label positions vary for different chart types. The
1368 allowable positions are:
1369 | Position | Line | Bar | Pie | Area |
1371 | | Scatter | Column | Doughnut | Radar |
1372 | | Stock | | | |
1373 |---------------|-----------|-----------|-----------|-----------|
1374 | center | Yes | Yes | Yes | Yes* |
1375 | right | Yes* | | | |
1376 | left | Yes | | | |
1377 | above | Yes | | | |
1378 | below | Yes | | | |
1379 | inside_base | | Yes | | |
1380 | inside_end | | Yes | Yes | |
1381 | outside_end | | Yes* | Yes | |
1382 | best_fit | | | Yes* | |
1383 Note: The * indicates the default position for each chart type in
1385 Excel, if a position isn't specified.
1386 The "percentage" property is used to turn on the display of data labels
1388 as a Percentage for a series. It is mainly used for pie and doughnut
1389 charts.
1390 $chart->add_series(
1392 values => '=Sheet1!$B$1:$B$5',
1393 data_labels => { percentage => 1 },
1394 );
1395 The "leader_lines" property is used to turn on Leader Lines for the
1397 data label for a series. It is mainly used for pie charts.
1398 $chart->add_series(
1400 values => '=Sheet1!$B$1:$B$5',
1401 data_labels => { value => 1, leader_lines => 1 },
1402 );
1403 Note: Even when leader lines are turned on they aren't automatically
1405 visible in Excel or Excel::Writer::XLSX. Due to an Excel limitation (or
1406 design) leader lines only appear if the data label is moved manually or
1407 if the data labels are very close and need to be adjusted
1408 automatically.
1409 The "separator" property is used to change the separator between
1411 multiple data label items:
1412 $chart->add_series(
1414 values => '=Sheet1!$B$1:$B$5',
1415 data_labels => { percentage => 1 },
1416 data_labels => { value => 1, category => 1, separator => "\n" },
1417 );
1418 The separator value must be one of the following strings:
1420 ','
1422 ';'
1423 '.'
1424 "\n"
1425 ' '
1426 The "legend_key" property is used to turn on Legend Key for the data
1428 label for a series:
1429 $chart->add_series(
1431 values => '=Sheet1!$B$1:$B$5',
1432 data_labels => { value => 1, legend_key => 1 },
1433 );
1434 The "num_format" property is used to set the number format for the data
1436 labels.
1437 $chart->add_series(
1439 values => '=Sheet1!$A$1:$A$5',
1440 data_labels => { value => 1, num_format => '#,##0.00' },
1441 );
1442 The number format is similar to the Worksheet Cell Format "num_format"
1444 apart from the fact that a format index cannot be used. The explicit
1445 format string must be used as shown above. See "set_num_format()" in
1446 Excel::Writer::XLSX for more information.
1447 The "font" property is used to set the font properties of the data
1449 labels in a series:
1450 $chart->add_series(
1452 values => '=Sheet1!$A$1:$A$5',
1453 data_labels => {
1454 value => 1,
1455 font => { name => 'Consolas' }
1456 },
1457 );
1458 The "font" property is also used to rotate the data labels in a series:
1460 $chart->add_series(
1462 values => '=Sheet1!$A$1:$A$5',
1463 data_labels => {
1464 value => 1,
1465 font => { rotation => 45 }
1466 },
1467 );
1468 See the "CHART FONTS" section below.
1470 Points
1472 In general formatting is applied to an entire series in a chart.
1473 However, it is occasionally required to format individual points in a
1474 series. In particular this is required for Pie and Doughnut charts
1475 where each segment is represented by a point.
1476 In these cases it is possible to use the "points" property of
1478 "add_series()":
1479 $chart->add_series(
1481 values => '=Sheet1!$A$1:$A$3',
1482 points => [
1483 { fill => { color => '#FF0000' } },
1484 { fill => { color => '#CC0000' } },
1485 { fill => { color => '#990000' } },
1486 ],
1487 );
1488 The "points" property takes an array ref of format options (see the
1490 "CHART FORMATTING" section below). To assign default properties to
1491 points in a series pass "undef" values in the array ref:
1492 # Format point 3 of 3 only.
1494 $chart->add_series(
1495 values => '=Sheet1!$A$1:$A$3',
1496 points => [
1497 undef,
1498 undef,
1499 { fill => { color => '#990000' } },
1500 ],
1501 );
1502 # Format the first point only.
1504 $chart->add_series(
1505 values => '=Sheet1!$A$1:$A$3',
1506 points => [ { fill => { color => '#FF0000' } } ],
1507 );
1508 Smooth
1510 The "smooth" option is used to set the smooth property of a line
1511 series. It is only applicable to the "Line" and "Scatter" chart types.
1512 $chart->add_series( values => '=Sheet1!$C$1:$C$5',
1514 smooth => 1 );
1515
1517 The following chart formatting properties can be set for any chart
1518 object that they apply to (and that are supported by
1519 Excel::Writer::XLSX) such as chart lines, column fill areas, plot area
1520 borders, markers, gridlines and other chart elements documented above.
1521 line
1523 border
1524 fill
1525 pattern
1526 gradient
1527 Chart formatting properties are generally set using hash refs.
1529 $chart->add_series(
1531 values => '=Sheet1!$B$1:$B$5',
1532 line => { color => 'blue' },
1533 );
1534 In some cases the format properties can be nested. For example a
1536 "marker" may contain "border" and "fill" sub-properties.
1537 $chart->add_series(
1539 values => '=Sheet1!$B$1:$B$5',
1540 line => { color => 'blue' },
1541 marker => {
1542 type => 'square',
1543 size => 5,
1544 border => { color => 'red' },
1545 fill => { color => 'yellow' },
1546 },
1547 );
1548 Line
1550 The line format is used to specify properties of line objects that
1551 appear in a chart such as a plotted line on a chart or a border.
1552 The following properties can be set for "line" formats in a chart.
1554 none
1556 color
1557 width
1558 dash_type
1559 transparency
1560 The "none" property is uses to turn the "line" off (it is always on by
1562 default except in Scatter charts). This is useful if you wish to plot a
1563 series with markers but without a line.
1564 $chart->add_series(
1566 values => '=Sheet1!$B$1:$B$5',
1567 line => { none => 1 },
1568 );
1569 The "color" property sets the color of the "line".
1571 $chart->add_series(
1573 values => '=Sheet1!$B$1:$B$5',
1574 line => { color => 'red' },
1575 );
1576 The available colours are shown in the main Excel::Writer::XLSX
1578 documentation. It is also possible to set the colour of a line with a
1579 HTML style RGB colour:
1580 $chart->add_series(
1582 line => { color => '#FF0000' },
1583 );
1584 The "width" property sets the width of the "line". It should be
1586 specified in increments of 0.25 of a point as in Excel.
1587 $chart->add_series(
1589 values => '=Sheet1!$B$1:$B$5',
1590 line => { width => 3.25 },
1591 );
1592 The "dash_type" property sets the dash style of the line.
1594 $chart->add_series(
1596 values => '=Sheet1!$B$1:$B$5',
1597 line => { dash_type => 'dash_dot' },
1598 );
1599 The following "dash_type" values are available. They are shown in the
1601 order that they appear in the Excel dialog.
1602 solid
1604 round_dot
1605 square_dot
1606 dash
1607 dash_dot
1608 long_dash
1609 long_dash_dot
1610 long_dash_dot_dot
1611 The default line style is "solid".
1613 The "transparency" property sets the transparency of the "line" color
1615 in the integer range 1 - 100. The color must be set for transparency to
1616 work, it doesn't work with an automatic/default color:
1617 $chart->add_series(
1619 values => '=Sheet1!$B$1:$B$5',
1620 line => { color => 'yellow', transparency => 50 },
1621 );
1622 More than one "line" property can be specified at a time:
1624 $chart->add_series(
1626 values => '=Sheet1!$B$1:$B$5',
1627 line => {
1628 color => 'red',
1629 width => 1.25,
1630 dash_type => 'square_dot',
1631 },
1632 );
1633 Border
1635 The "border" property is a synonym for "line".
1636 It can be used as a descriptive substitute for "line" in chart types
1638 such as Bar and Column that have a border and fill style rather than a
1639 line style. In general chart objects with a "border" property will also
1640 have a fill property.
1641 Solid Fill
1643 The fill format is used to specify filled areas of chart objects such
1644 as the interior of a column or the background of the chart itself.
1645 The following properties can be set for "fill" formats in a chart.
1647 none
1649 color
1650 transparency
1651 The "none" property is used to turn the "fill" property off (it is
1653 generally on by default).
1654 $chart->add_series(
1656 values => '=Sheet1!$B$1:$B$5',
1657 fill => { none => 1 },
1658 );
1659 The "color" property sets the colour of the "fill" area.
1661 $chart->add_series(
1663 values => '=Sheet1!$B$1:$B$5',
1664 fill => { color => 'red' },
1665 );
1666 The available colours are shown in the main Excel::Writer::XLSX
1668 documentation. It is also possible to set the colour of a fill with a
1669 HTML style RGB colour:
1670 $chart->add_series(
1672 fill => { color => '#FF0000' },
1673 );
1674 The "transparency" property sets the transparency of the solid fill
1676 color in the integer range 1 - 100. The color must be set for
1677 transparency to work, it doesn't work with an automatic/default color:
1678 $chart->set_chartarea( fill => { color => 'yellow', transparency => 75 } );
1680 The "fill" format is generally used in conjunction with a "border"
1682 format which has the same properties as a "line" format.
1683 $chart->add_series(
1685 values => '=Sheet1!$B$1:$B$5',
1686 border => { color => 'red' },
1687 fill => { color => 'yellow' },
1688 );
1689 Pattern Fill
1691 The pattern fill format is used to specify pattern filled areas of
1692 chart objects such as the interior of a column or the background of the
1693 chart itself.
1694 The following properties can be set for "pattern" fill formats in a
1696 chart:
1697 pattern: the pattern to be applied (required)
1699 fg_color: the foreground color of the pattern (required)
1700 bg_color: the background color (optional, defaults to white)
1701 For example:
1703 $chart->set_plotarea(
1705 pattern => {
1706 pattern => 'percent_5',
1707 fg_color => 'red',
1708 bg_color => 'yellow',
1709 }
1710 );
1711 The following patterns can be applied:
1713 percent_5
1715 percent_10
1716 percent_20
1717 percent_25
1718 percent_30
1719 percent_40
1720 percent_50
1721 percent_60
1722 percent_70
1723 percent_75
1724 percent_80
1725 percent_90
1726 light_downward_diagonal
1727 light_upward_diagonal
1728 dark_downward_diagonal
1729 dark_upward_diagonal
1730 wide_downward_diagonal
1731 wide_upward_diagonal
1732 light_vertical
1733 light_horizontal
1734 narrow_vertical
1735 narrow_horizontal
1736 dark_vertical
1737 dark_horizontal
1738 dashed_downward_diagonal
1739 dashed_upward_diagonal
1740 dashed_horizontal
1741 dashed_vertical
1742 small_confetti
1743 large_confetti
1744 zigzag
1745 wave
1746 diagonal_brick
1747 horizontal_brick
1748 weave
1749 plaid
1750 divot
1751 dotted_grid
1752 dotted_diamond
1753 shingle
1754 trellis
1755 sphere
1756 small_grid
1757 large_grid
1758 small_check
1759 large_check
1760 outlined_diamond
1761 solid_diamond
1762 The foreground color, "fg_color", is a required parameter and can be a
1764 Html style "#RRGGBB" string or a limited number of named colors. The
1765 available colours are shown in the main Excel::Writer::XLSX
1766 documentation.
1767 The background color, "bg_color", is optional and defaults to black.
1769 If a pattern fill is used on a chart object it overrides the solid fill
1771 properties of the object.
1772 Gradient Fill
1774 The gradient fill format is used to specify gradient filled areas of
1775 chart objects such as the interior of a column or the background of the
1776 chart itself.
1777 The following properties can be set for "gradient" fill formats in a
1779 chart:
1780 colors: a list of colors
1782 positions: an optional list of positions for the colors
1783 type: the optional type of gradient fill
1784 angle: the optional angle of the linear fill
1785 The "colors" property sets a list of colors that define the "gradient":
1787 $chart->set_plotarea(
1789 gradient => { colors => [ '#DDEBCF', '#9CB86E', '#156B13' ] }
1790 );
1791 Excel allows between 2 and 10 colors in a gradient but it is unlikely
1793 that you will require more than 2 or 3.
1794 As with solid or pattern fill it is also possible to set the colors of
1796 a gradient with a Html style "#RRGGBB" string or a limited number of
1797 named colors. The available colours are shown in the main
1798 Excel::Writer::XLSX documentation:
1799 $chart->add_series(
1801 values => '=Sheet1!$A$1:$A$5',
1802 gradient => { colors => [ 'red', 'green' ] }
1803 );
1804 The "positions" defines an optional list of positions, between 0 and
1806 100, of where the colors in the gradient are located. Default values
1807 are provided for "colors" lists of between 2 and 4 but they can be
1808 specified if required:
1809 $chart->add_series(
1811 values => '=Sheet1!$A$1:$A$5',
1812 gradient => {
1813 colors => [ '#DDEBCF', '#156B13' ],
1814 positions => [ 10, 90 ],
1815 }
1816 );
1817 The "type" property can have one of the following values:
1819 linear (the default)
1821 radial
1822 rectangular
1823 path
1824 For example:
1826 $chart->add_series(
1828 values => '=Sheet1!$A$1:$A$5',
1829 gradient => {
1830 colors => [ '#DDEBCF', '#9CB86E', '#156B13' ],
1831 type => 'radial'
1832 }
1833 );
1834 If "type" isn't specified it defaults to "linear".
1836 For a "linear" fill the angle of the gradient can also be specified:
1838 $chart->add_series(
1840 values => '=Sheet1!$A$1:$A$5',
1841 gradient => { colors => [ '#DDEBCF', '#9CB86E', '#156B13' ],
1842 angle => 30 }
1843 );
1844 The default angle is 90 degrees.
1846 If gradient fill is used on a chart object it overrides the solid fill
1848 and pattern fill properties of the object.
1849
1851 The following font properties can be set for any chart object that they
1852 apply to (and that are supported by Excel::Writer::XLSX) such as chart
1853 titles, axis labels, axis numbering and data labels. They correspond to
1854 the equivalent Worksheet cell Format object properties. See
1855 "FORMAT_METHODS" in Excel::Writer::XLSX for more information.
1856 name
1858 size
1859 bold
1860 italic
1861 underline
1862 rotation
1863 color
1864 The following explains the available font properties:
1866 · "name"
1868 Set the font name:
1870 $chart->set_x_axis( num_font => { name => 'Arial' } );
1872 · "size"
1874 Set the font size:
1876 $chart->set_x_axis( num_font => { name => 'Arial', size => 10 } );
1878 · "bold"
1880 Set the font bold property, should be 0 or 1:
1882 $chart->set_x_axis( num_font => { bold => 1 } );
1884 · "italic"
1886 Set the font italic property, should be 0 or 1:
1888 $chart->set_x_axis( num_font => { italic => 1 } );
1890 · "underline"
1892 Set the font underline property, should be 0 or 1:
1894 $chart->set_x_axis( num_font => { underline => 1 } );
1896 · "rotation"
1898 Set the font rotation in the integer range -90 to 90, and 270-271:
1900 $chart->set_x_axis( num_font => { rotation => 45 } );
1902 This is useful for displaying large axis data such as dates in a
1904 more compact format.
1905 There are 2 special case angles outside the range -90 to 90:
1907 · 270: Stacked text, where the text runs from top to bottom.
1909 · 271: A special variant of stacked text for East Asian fonts.
1911 · "color"
1913 Set the font color property. Can be a color index, a color name or
1915 HTML style RGB colour:
1916 $chart->set_x_axis( num_font => { color => 'red' } );
1918 $chart->set_y_axis( num_font => { color => '#92D050' } );
1919 Here is an example of Font formatting in a Chart program:
1921 # Format the chart title.
1923 $chart->set_title(
1924 name => 'Sales Results Chart',
1925 name_font => {
1926 name => 'Calibri',
1927 color => 'yellow',
1928 },
1929 );
1930 # Format the X-axis.
1932 $chart->set_x_axis(
1933 name => 'Month',
1934 name_font => {
1935 name => 'Arial',
1936 color => '#92D050'
1937 },
1938 num_font => {
1939 name => 'Courier New',
1940 color => '#00B0F0',
1941 },
1942 );
1943 # Format the Y-axis.
1945 $chart->set_y_axis(
1946 name => 'Sales (1000 units)',
1947 name_font => {
1948 name => 'Century',
1949 underline => 1,
1950 color => 'red'
1951 },
1952 num_font => {
1953 bold => 1,
1954 italic => 1,
1955 color => '#7030A0',
1956 },
1957 );
1958
1960 The position of the chart in the worksheet is controlled by the
1961 "set_size()" method shown above.
1962 It is also possible to change the layout of the following chart sub-
1964 objects:
1965 plotarea
1967 legend
1968 title
1969 x_axis caption
1970 y_axis caption
1971 Here are some examples:
1973 $chart->set_plotarea(
1975 layout => {
1976 x => 0.35,
1977 y => 0.26,
1978 width => 0.62,
1979 height => 0.50,
1980 }
1981 );
1982 $chart->set_legend(
1984 layout => {
1985 x => 0.80,
1986 y => 0.37,
1987 width => 0.12,
1988 height => 0.25,
1989 }
1990 );
1991 $chart->set_title(
1993 name => 'Title',
1994 layout => {
1995 x => 0.42,
1996 y => 0.14,
1997 }
1998 );
1999 $chart->set_x_axis(
2001 name => 'X axis',
2002 name_layout => {
2003 x => 0.34,
2004 y => 0.85,
2005 }
2006 );
2007 Note that it is only possible to change the width and height for the
2009 "plotarea" and "legend" objects. For the other text based objects the
2010 width and height are changed by the font dimensions.
2011 The layout units must be a float in the range "0 < x <= 1" and are
2013 expressed as a percentage of the chart dimensions as shown below:
2014 From this the layout units are calculated as follows:
2016 layout:
2018 width = w / W
2019 height = h / H
2020 x = a / W
2021 y = b / H
2022 These units are slightly cumbersome but are required by Excel so that
2024 the chart object positions remain relative to each other if the chart
2025 is resized by the user.
2026 Note that for "plotarea" the origin is the top left corner in the
2028 plotarea itself and does not take into account the axes.
2029
2031 In Excel a chartsheet (i.e, a chart that isn't embedded) shares
2032 properties with data worksheets such as tab selection, headers,
2033 footers, margins, and print properties.
2034 In Excel::Writer::XLSX you can set chartsheet properties using the same
2036 methods that are used for Worksheet objects.
2037 The following Worksheet methods are also available through a non-
2039 embedded Chart object:
2040 get_name()
2042 activate()
2043 select()
2044 hide()
2045 set_first_sheet()
2046 protect()
2047 set_zoom()
2048 set_tab_color()
2049 set_landscape()
2051 set_portrait()
2052 set_paper()
2053 set_margins()
2054 set_header()
2055 set_footer()
2056 See Excel::Writer::XLSX for a detailed explanation of these methods.
2058
2060 Here is a complete example that demonstrates some of the available
2061 features when creating a chart.
2062 #!/usr/bin/perl
2064 use strict;
2066 use warnings;
2067 use Excel::Writer::XLSX;
2068 my $workbook = Excel::Writer::XLSX->new( 'chart.xlsx' );
2070 my $worksheet = $workbook->add_worksheet();
2071 my $bold = $workbook->add_format( bold => 1 );
2072 # Add the worksheet data that the charts will refer to.
2074 my $headings = [ 'Number', 'Batch 1', 'Batch 2' ];
2075 my $data = [
2076 [ 2, 3, 4, 5, 6, 7 ],
2077 [ 10, 40, 50, 20, 10, 50 ],
2078 [ 30, 60, 70, 50, 40, 30 ],
2079 ];
2081 $worksheet->write( 'A1', $headings, $bold );
2083 $worksheet->write( 'A2', $data );
2084 # Create a new chart object. In this case an embedded chart.
2086 my $chart = $workbook->add_chart( type => 'column', embedded => 1 );
2087 # Configure the first series.
2089 $chart->add_series(
2090 name => '=Sheet1!$B$1',
2091 categories => '=Sheet1!$A$2:$A$7',
2092 values => '=Sheet1!$B$2:$B$7',
2093 );
2094 # Configure second series. Note alternative use of array ref to define
2096 # ranges: [ $sheetname, $row_start, $row_end, $col_start, $col_end ].
2097 $chart->add_series(
2098 name => '=Sheet1!$C$1',
2099 categories => [ 'Sheet1', 1, 6, 0, 0 ],
2100 values => [ 'Sheet1', 1, 6, 2, 2 ],
2101 );
2102 # Add a chart title and some axis labels.
2104 $chart->set_title ( name => 'Results of sample analysis' );
2105 $chart->set_x_axis( name => 'Test number' );
2106 $chart->set_y_axis( name => 'Sample length (mm)' );
2107 # Set an Excel chart style. Blue colors with white outline and shadow.
2109 $chart->set_style( 11 );
2110 # Insert the chart into the worksheet (with an offset).
2112 $worksheet->insert_chart( 'D2', $chart, { x_offset => 25, y_offset => 10 } );
2113 $workbook->close();
2115 __END__
2117
2119 Excel differentiates between a chart axis that is used for series
2120 categories and an axis that is used for series values.
2121 In the example above the X axis is the category axis and each of the
2123 values is evenly spaced. The Y axis (in this case) is the value axis
2124 and points are displayed according to their value.
2125 Since Excel treats the axes differently it also handles their
2127 formatting differently and exposes different properties for each.
2128 As such some of "Excel::Writer::XLSX" axis properties can be set for a
2130 value axis, some can be set for a category axis and some properties can
2131 be set for both.
2132 For example the "min" and "max" properties can only be set for value
2134 axes and "reverse" can be set for both. The type of axis that a
2135 property applies to is shown in the "set_x_axis()" section of the
2136 documentation above.
2137 Some charts such as "Scatter" and "Stock" have two value axes.
2139 Date Axes are a special type of category axis which are explained
2141 below.
2142
2144 Date Category Axes are category axes that display time or date
2145 information. In Excel::Writer::XLSX Date Category Axes are set using
2146 the "date_axis" option:
2147 $chart->set_x_axis( date_axis => 1 );
2149 In general you should also specify a number format for a date axis
2151 although Excel will usually default to the same format as the data
2152 being plotted:
2153 $chart->set_x_axis(
2155 date_axis => 1,
2156 num_format => 'dd/mm/yyyy',
2157 );
2158 Excel doesn't normally allow minimum and maximum values to be set for
2160 category axes. However, date axes are an exception. The "min" and "max"
2161 values should be set as Excel times or dates:
2162 $chart->set_x_axis(
2164 date_axis => 1,
2165 min => $worksheet->convert_date_time('2013-01-02T'),
2166 max => $worksheet->convert_date_time('2013-01-09T'),
2167 num_format => 'dd/mm/yyyy',
2168 );
2169 For date axes it is also possible to set the type of the major and
2171 minor units:
2172 $chart->set_x_axis(
2174 date_axis => 1,
2175 minor_unit => 4,
2176 minor_unit_type => 'months',
2177 major_unit => 1,
2178 major_unit_type => 'years',
2179 num_format => 'dd/mm/yyyy',
2180 );
2181
2183 It is possible to add a secondary axis of the same type to a chart by
2184 setting the "y2_axis" or "x2_axis" property of the series:
2185 #!/usr/bin/perl
2187 use strict;
2189 use warnings;
2190 use Excel::Writer::XLSX;
2191 my $workbook = Excel::Writer::XLSX->new( 'chart_secondary_axis.xlsx' );
2193 my $worksheet = $workbook->add_worksheet();
2194 # Add the worksheet data that the charts will refer to.
2196 my $data = [
2197 [ 2, 3, 4, 5, 6, 7 ],
2198 [ 10, 40, 50, 20, 10, 50 ],
2199 ];
2201 $worksheet->write( 'A1', $data );
2203 # Create a new chart object. In this case an embedded chart.
2205 my $chart = $workbook->add_chart( type => 'line', embedded => 1 );
2206 # Configure a series with a secondary axis
2208 $chart->add_series(
2209 values => '=Sheet1!$A$1:$A$6',
2210 y2_axis => 1,
2211 );
2212 $chart->add_series(
2214 values => '=Sheet1!$B$1:$B$6',
2215 );
2216 # Insert the chart into the worksheet.
2219 $worksheet->insert_chart( 'D2', $chart );
2220 $workbook->close();
2222 __END__
2224 It is also possible to have a secondary, combined, chart either with a
2226 shared or secondary axis, see below.
2227
2229 It is also possible to combine two different chart types, for example a
2230 column and line chart to create a Pareto chart using the Chart
2231 "combine()" method:
2232 Here is a simpler example:
2234 use strict;
2236 use warnings;
2237 use Excel::Writer::XLSX;
2238 my $workbook = Excel::Writer::XLSX->new( 'chart_combined.xlsx' );
2240 my $worksheet = $workbook->add_worksheet();
2241 my $bold = $workbook->add_format( bold => 1 );
2242 # Add the worksheet data that the charts will refer to.
2244 my $headings = [ 'Number', 'Batch 1', 'Batch 2' ];
2245 my $data = [
2246 [ 2, 3, 4, 5, 6, 7 ],
2247 [ 10, 40, 50, 20, 10, 50 ],
2248 [ 30, 60, 70, 50, 40, 30 ],
2249 ];
2251 $worksheet->write( 'A1', $headings, $bold );
2253 $worksheet->write( 'A2', $data );
2254 #
2256 # In the first example we will create a combined column and line chart.
2257 # They will share the same X and Y axes.
2258 #
2259 # Create a new column chart. This will use this as the primary chart.
2261 my $column_chart = $workbook->add_chart( type => 'column', embedded => 1 );
2262 # Configure the data series for the primary chart.
2264 $column_chart->add_series(
2265 name => '=Sheet1!$B$1',
2266 categories => '=Sheet1!$A$2:$A$7',
2267 values => '=Sheet1!$B$2:$B$7',
2268 );
2269 # Create a new column chart. This will use this as the secondary chart.
2271 my $line_chart = $workbook->add_chart( type => 'line', embedded => 1 );
2272 # Configure the data series for the secondary chart.
2274 $line_chart->add_series(
2275 name => '=Sheet1!$C$1',
2276 categories => '=Sheet1!$A$2:$A$7',
2277 values => '=Sheet1!$C$2:$C$7',
2278 );
2279 # Combine the charts.
2281 $column_chart->combine( $line_chart );
2282 # Add a chart title and some axis labels. Note, this is done via the
2284 # primary chart.
2285 $column_chart->set_title( name => 'Combined chart - same Y axis' );
2286 $column_chart->set_x_axis( name => 'Test number' );
2287 $column_chart->set_y_axis( name => 'Sample length (mm)' );
2288 # Insert the chart into the worksheet
2291 $worksheet->insert_chart( 'E2', $column_chart );
2292 $workbook->close();
2294 The secondary chart can also be placed on a secondary axis using the
2296 methods shown in the previous section.
2297 In this case it is just necessary to add a "y2_axis" parameter to the
2299 series and, if required, add a title using "set_y2_axis()" of the
2300 secondary chart. The following are the additions to the previous
2301 example to place the secondary chart on the secondary axis:
2302 ...
2304 $line_chart->add_series(
2306 name => '=Sheet1!$C$1',
2307 categories => '=Sheet1!$A$2:$A$7',
2308 values => '=Sheet1!$C$2:$C$7',
2309 y2_axis => 1,
2310 );
2311 ...
2313 # Note: the y2 properites are on the secondary chart.
2315 $line_chart2->set_y2_axis( name => 'Target length (mm)' );
2316 The examples above use the concept of a primary and secondary chart.
2318 The primary chart is the chart that defines the primary X and Y axis.
2319 It is also used for setting all chart properties apart from the
2320 secondary data series. For example the chart title and axes properties
2321 should be set via the primary chart (except for the the secondary "y2"
2322 axis properties which should be applied to the secondary chart).
2323 See also "chart_combined.pl" and "chart_pareto.pl" examples in the
2325 distro for more detailed examples.
2326 There are some limitations on combined charts:
2328 · Pie charts cannot currently be combined.
2330 · Scatter charts cannot currently be used as a primary chart but they
2332 can be used as a secondary chart.
2333 · Bar charts can only combined secondary charts on a secondary axis.
2335 This is an Excel limitation.
2336
2338 Chart features that are on the TODO list and will hopefully be added
2339 are:
2340 · Add more chart sub-types.
2342 · Additional formatting options.
2344 · More axis controls.
2346 · 3D charts.
2348 · Additional chart types.
2350 If you are interested in sponsoring a feature to have it implemented or
2352 expedited let me know.
2353
2355 John McNamara jmcnamara@cpan.org
2356
2358 Copyright MM-MMXIX, John McNamara.
2359 All Rights Reserved. This module is free software. It may be used,
2361 redistributed and/or modified under the same terms as Perl itself.
2362perl v5.30.1 2020-01-30 Excel::Writer::XLSX::Chart(3)