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 line
120 stacked
121 percent_stacked
122 radar
124 with_markers
125 filled
126
128 Methods that are common to all chart types are documented below. See
129 the documentation for each of the above chart modules for chart
130 specific information.
131 add_series()
133 In an Excel chart a "series" is a collection of information such as
134 values, X axis labels and the formatting that define which data is
135 plotted.
136 With an Excel::Writer::XLSX chart object the "add_series()" method is
138 used to set the properties for a series:
139 $chart->add_series(
141 categories => '=Sheet1!$A$2:$A$10', # Optional.
142 values => '=Sheet1!$B$2:$B$10', # Required.
143 line => { color => 'blue' },
144 );
145 The properties that can be set are:
147 • "values"
149 This is the most important property of a series and must be set for
151 every chart object. It links the chart with the worksheet data that
152 it displays. A formula or array ref can be used for the data range,
153 see below.
154 • "categories"
156 This sets the chart category labels. The category is more or less
158 the same as the X axis. In most chart types the "categories"
159 property is optional and the chart will just assume a sequential
160 series from "1 .. n".
161 • "name"
163 Set the name for the series. The name is displayed in the chart
165 legend and in the formula bar. The name property is optional and if
166 it isn't supplied it will default to "Series 1 .. n".
167 • "line"
169 Set the properties of the series line type such as colour and
171 width. See the "CHART FORMATTING" section below.
172 • "border"
174 Set the border properties of the series such as colour and style.
176 See the "CHART FORMATTING" section below.
177 • "fill"
179 Set the fill properties of the series such as colour. See the
181 "CHART FORMATTING" section below.
182 • "pattern"
184 Set the pattern properties of the series. See the "CHART
186 FORMATTING" section below.
187 • "gradient"
189 Set the gradient properties of the series. See the "CHART
191 FORMATTING" section below.
192 • "marker"
194 Set the properties of the series marker such as style and colour.
196 See the "SERIES OPTIONS" section below.
197 • "trendline"
199 Set the properties of the series trendline such as linear,
201 polynomial and moving average types. See the "SERIES OPTIONS"
202 section below.
203 • "smooth"
205 The "smooth" option is used to set the smooth property of a line
207 series. See the "SERIES OPTIONS" section below.
208 • "y_error_bars"
210 Set vertical error bounds for a chart series. See the "SERIES
212 OPTIONS" section below.
213 • "x_error_bars"
215 Set horizontal error bounds for a chart series. See the "SERIES
217 OPTIONS" section below.
218 • "data_labels"
220 Set data labels for the series. See the "SERIES OPTIONS" section
222 below.
223 • "points"
225 Set properties for individual points in a series. See the "SERIES
227 OPTIONS" section below.
228 • "invert_if_negative"
230 Invert the fill colour for negative values. Usually only applicable
232 to column and bar charts.
233 • "overlap"
235 Set the overlap between series in a Bar/Column chart. The range is
237 +/- 100. Default is 0.
238 overlap => 20,
240 Note, it is only necessary to apply this property to one series of
242 the chart.
243 • "gap"
245 Set the gap between series in a Bar/Column chart. The range is 0 to
247 500. Default is 150.
248 gap => 200,
250 Note, it is only necessary to apply this property to one series of
252 the chart.
253 The "categories" and "values" can take either a range formula such as
255 "=Sheet1!$A$2:$A$7" or, more usefully when generating the range
256 programmatically, an array ref with zero indexed row/column values:
257 [ $sheetname, $row_start, $row_end, $col_start, $col_end ]
259 The following are equivalent:
261 $chart->add_series( categories => '=Sheet1!$A$2:$A$7' ); # Same as ...
263 $chart->add_series( categories => [ 'Sheet1', 1, 6, 0, 0 ] ); # Zero-indexed.
264 You can add more than one series to a chart. In fact, some chart types
266 such as "stock" require it. The series numbering and order in the Excel
267 chart will be the same as the order in which they are added in
268 Excel::Writer::XLSX.
269 # Add the first series.
271 $chart->add_series(
272 categories => '=Sheet1!$A$2:$A$7',
273 values => '=Sheet1!$B$2:$B$7',
274 name => 'Test data series 1',
275 );
276 # Add another series. Same categories. Different range values.
278 $chart->add_series(
279 categories => '=Sheet1!$A$2:$A$7',
280 values => '=Sheet1!$C$2:$C$7',
281 name => 'Test data series 2',
282 );
283 It is also possible to specify non-contiguous ranges:
285 $chart->add_series(
287 categories => '=(Sheet1!$A$1:$A$9,Sheet1!$A$14:$A$25)',
288 values => '=(Sheet1!$B$1:$B$9,Sheet1!$B$14:$B$25)',
289 );
290 set_x_axis()
292 The "set_x_axis()" method is used to set properties of the X axis.
293 $chart->set_x_axis( name => 'Quarterly results' );
295 The properties that can be set are:
297 name
299 name_font
300 name_layout
301 num_font
302 num_format
303 line
304 fill
305 pattern
306 gradient
307 min
308 max
309 minor_unit
310 major_unit
311 interval_unit
312 interval_tick
313 crossing
314 reverse
315 position_axis
316 log_base
317 label_position
318 major_gridlines
319 minor_gridlines
320 visible
321 date_axis
322 text_axis
323 minor_unit_type
324 major_unit_type
325 minor_tick_mark
326 major_tick_mark
327 display_units
328 display_units_visible
329 These are explained below. Some properties are only applicable to value
331 or category axes, as indicated. See "Value and Category Axes" for an
332 explanation of Excel's distinction between the axis types.
333 • "name"
335 Set the name (title or caption) for the axis. The name is displayed
337 below the X axis. The "name" property is optional. The default is
338 to have no axis name. (Applicable to category and value axes).
339 $chart->set_x_axis( name => 'Quarterly results' );
341 The name can also be a formula such as "=Sheet1!$A$1".
343 • "name_font"
345 Set the font properties for the axis title. (Applicable to category
347 and value axes).
348 $chart->set_x_axis( name_font => { name => 'Arial', size => 10 } );
350 • "name_layout"
352 Set the "(x, y)" position of the axis caption in chart relative
354 units. (Applicable to category and value axes).
355 $chart->set_x_axis(
357 name => 'X axis',
358 name_layout => {
359 x => 0.34,
360 y => 0.85,
361 }
362 );
363 See the "CHART LAYOUT" section below.
365 • "num_font"
367 Set the font properties for the axis numbers. (Applicable to
369 category and value axes).
370 $chart->set_x_axis( num_font => { bold => 1, italic => 1 } );
372 See the "CHART FONTS" section below.
374 • "num_format"
376 Set the number format for the axis. (Applicable to category and
378 value axes).
379 $chart->set_x_axis( num_format => '#,##0.00' );
381 $chart->set_y_axis( num_format => '0.00%' );
382 The number format is similar to the Worksheet Cell Format
384 "num_format" apart from the fact that a format index cannot be
385 used. The explicit format string must be used as shown above. See
386 "set_num_format()" in Excel::Writer::XLSX for more information.
387 • "line"
389 Set the properties of the axis line type such as colour and width.
391 See the "CHART FORMATTING" section below.
392 $chart->set_x_axis( line => { none => 1 });
394 • "fill"
396 Set the fill properties of the axis such as colour. See the "CHART
398 FORMATTING" section below. Note, in Excel the axis fill is applied
399 to the area of the numbers of the axis and not to the area of the
400 axis bounding box. That background is set from the chartarea fill.
401 • "pattern"
403 Set the pattern properties of the axis such as colour. See the
405 "CHART FORMATTING" section below.
406 • "gradient"
408 Set the gradient properties of the axis such as colour. See the
410 "CHART FORMATTING" section below.
411 • "min"
413 Set the minimum value for the axis range. (Applicable to value axes
415 only.)
416 $chart->set_x_axis( min => 20 );
418 • "max"
420 Set the maximum value for the axis range. (Applicable to value axes
422 only.)
423 $chart->set_x_axis( max => 80 );
425 • "minor_unit"
427 Set the increment of the minor units in the axis range. (Applicable
429 to value axes only.)
430 $chart->set_x_axis( minor_unit => 0.4 );
432 • "major_unit"
434 Set the increment of the major units in the axis range. (Applicable
436 to value axes only.)
437 $chart->set_x_axis( major_unit => 2 );
439 • "interval_unit"
441 Set the interval unit for a category axis. (Applicable to category
443 axes only.)
444 $chart->set_x_axis( interval_unit => 2 );
446 • "interval_tick"
448 Set the tick interval for a category axis. (Applicable to category
450 axes only.)
451 $chart->set_x_axis( interval_tick => 4 );
453 • "crossing"
455 Set the position where the y axis will cross the x axis.
457 (Applicable to category and value axes.)
458 The "crossing" value can either be a numeric value or the strings
460 'max' or 'min' to set the crossing at the maximum/minimum axis
461 value.
462 $chart->set_x_axis( crossing => 3 );
464 # or
465 $chart->set_x_axis( crossing => 'max' );
466 For category axes the numeric value must be an integer to represent
468 the category number that the axis crosses at. For value axes it can
469 have any value associated with the axis.
470 If crossing is omitted (the default) the crossing will be set
472 automatically by Excel based on the chart data.
473 • "position_axis"
475 Position the axis on or between the axis tick marks. (Applicable to
477 category axes only.)
478 There are two allowable values "on_tick" and "between":
480 $chart->set_x_axis( position_axis => 'on_tick' );
482 $chart->set_x_axis( position_axis => 'between' );
483 • "reverse"
485 Reverse the order of the axis categories or values. (Applicable to
487 category and value axes.)
488 $chart->set_x_axis( reverse => 1 );
490 • "log_base"
492 Set the log base of the axis range. (Applicable to value axes
494 only.)
495 $chart->set_x_axis( log_base => 10 );
497 • "label_position"
499 Set the "Axis labels" position for the axis. The following
501 positions are available:
502 next_to (the default)
504 high
505 low
506 none
507 • "major_gridlines"
509 Configure the major gridlines for the axis. The available
511 properties are:
512 visible
514 line
515 For example:
517 $chart->set_x_axis(
519 major_gridlines => {
520 visible => 1,
521 line => { color => 'red', width => 1.25, dash_type => 'dash' }
522 }
523 );
524 The "visible" property is usually on for the X-axis but it depends
526 on the type of chart.
527 The "line" property sets the gridline properties such as colour and
529 width. See the "CHART FORMATTING" section below.
530 • "minor_gridlines"
532 This takes the same options as "major_gridlines" above.
534 The minor gridline "visible" property is off by default for all
536 chart types.
537 • "visible"
539 Configure the visibility of the axis.
541 $chart->set_x_axis( visible => 0 );
543 • "date_axis"
545 This option is used to treat a category axis with date or time data
547 as a Date Axis. (Applicable to category axes only.)
548 $chart->set_x_axis( date_axis => 1 );
550 This option also allows you to set "max" and "min" values for a
552 category axis which isn't allowed by Excel for non-date category
553 axes.
554 See "Date Category Axes" for more details.
556 • "text_axis"
558 This option is used to treat a category axis explicitly as a Text
560 Axis. (Applicable to category axes only.)
561 $chart->set_x_axis( text_axis => 1 );
563 • "minor_unit_type"
565 For "date_axis" axes, see above, this option is used to set the
567 type of the minor units. (Applicable to date category axes only.)
568 $chart->set_x_axis(
570 date_axis => 1,
571 minor_unit => 4,
572 minor_unit_type => 'months',
573 );
574 The allowable values for this option are "days", "months" and
576 "years".
577 • "major_unit_type"
579 Same as "minor_unit_type", see above, but for major axes unit
581 types.
582 More than one property can be set in a call to "set_x_axis()":
584 $chart->set_x_axis(
586 name => 'Quarterly results',
587 min => 10,
588 max => 80,
589 );
590 • "major_tick_mark"
592 Set the axis major tick mark type to one of the following values:
594 none
596 inside
597 outside
598 cross (inside and outside)
599 For example:
601 $chart->set_x_axis( major_tick_mark => 'none',
603 minor_tick_mark => 'inside' );
604 • "minor_tick_mark"
606 Set the axis minor tick mark type. Same as "major_tick_mark", see
608 above.
609 • "display_units"
611 Set the display units for the axis. This can be useful if the axis
613 numbers are very large but you don't want to represent them in
614 scientific notation. (Applicable to value axes only.) The available
615 display units are:
616 hundreds
618 thousands
619 ten_thousands
620 hundred_thousands
621 millions
622 ten_millions
623 hundred_millions
624 billions
625 trillions
626 Example:
628 $chart->set_x_axis( display_units => 'thousands' )
630 $chart->set_y_axis( display_units => 'millions' )
631 * "display_units_visible"
633 Control the visibility of the display units turned on by the
635 previous option. This option is on by default. (Applicable to value
636 axes only.)::
637 $chart->set_x_axis( display_units => 'thousands',
639 display_units_visible => 0 )
640 set_y_axis()
642 The "set_y_axis()" method is used to set properties of the Y axis. The
643 properties that can be set are the same as for "set_x_axis", see above.
644 set_x2_axis()
646 The "set_x2_axis()" method is used to set properties of the secondary X
647 axis. The properties that can be set are the same as for "set_x_axis",
648 see above. The default properties for this axis are:
649 label_position => 'none',
651 crossing => 'max',
652 visible => 0,
653 set_y2_axis()
655 The "set_y2_axis()" method is used to set properties of the secondary Y
656 axis. The properties that can be set are the same as for "set_x_axis",
657 see above. The default properties for this axis are:
658 major_gridlines => { visible => 0 }
660 combine()
662 The chart "combine()" method is used to combine two charts of different
663 types, for example a column and line chart:
664 my $column_chart = $workbook->add_chart( type => 'column', embedded => 1 );
666 # Configure the data series for the primary chart.
668 $column_chart->add_series(...);
669 # Create a new column chart. This will use this as the secondary chart.
671 my $line_chart = $workbook->add_chart( type => 'line', embedded => 1 );
672 # Configure the data series for the secondary chart.
674 $line_chart->add_series(...);
675 # Combine the charts.
677 $column_chart->combine( $line_chart );
678 See "Combined Charts" for more details.
680 set_size()
682 The "set_size()" method is used to set the dimensions of the chart. The
683 size properties that can be set are:
684 width
686 height
687 x_scale
688 y_scale
689 x_offset
690 y_offset
691 The "width" and "height" are in pixels. The default chart width is 480
693 pixels and the default height is 288 pixels. The size of the chart can
694 be modified by setting the "width" and "height" or by setting the
695 "x_scale" and "y_scale":
696 $chart->set_size( width => 720, height => 576 );
698 # Same as:
700 $chart->set_size( x_scale => 1.5, y_scale => 2 );
702 The "x_offset" and "y_offset" position the top left corner of the chart
704 in the cell that it is inserted into.
705 Note: the "x_scale", "y_scale", "x_offset" and "y_offset" parameters
707 can also be set via the "insert_chart()" method:
708 $worksheet->insert_chart( 'E2', $chart, { x_offset =>2, y_offset => 4,
710 x_scale => 1.5, y_scale => 2 } );
711 set_title()
713 The "set_title()" method is used to set properties of the chart title.
714 $chart->set_title( name => 'Year End Results' );
716 The properties that can be set are:
718 • "name"
720 Set the name (title) for the chart. The name is displayed above the
722 chart. The name can also be a formula such as "=Sheet1!$A$1". The
723 name property is optional. The default is to have no chart title.
724 • "name_font"
726 Set the font properties for the chart title. See the "CHART FONTS"
728 section below.
729 • "overlay"
731 Allow the title to be overlaid on the chart. Generally used with
733 the layout property below.
734 • "layout"
736 Set the "(x, y)" position of the title in chart relative units:
738 $chart->set_title(
740 name => 'Title',
741 overlay => 1,
742 layout => {
743 x => 0.42,
744 y => 0.14,
745 }
746 );
747 See the "CHART LAYOUT" section below.
749 • "none"
751 By default Excel adds an automatic chart title to charts with a
753 single series and a user defined series name. The "none" option
754 turns this default title off. It also turns off all other
755 "set_title()" options.
756 $chart->set_title( none => 1 );
758 set_legend()
760 The "set_legend()" method is used to set properties of the chart
761 legend.
762 The properties that can be set are:
764 • "none"
766 The "none" option turns off the chart legend. In Excel chart
768 legends are on by default:
769 $chart->set_legend( none => 1 );
771 Note, for backward compatibility, it is also possible to turn off
773 the legend via the "position" property:
774 $chart->set_legend( position => 'none' );
776 • "position"
778 Set the position of the chart legend.
780 $chart->set_legend( position => 'bottom' );
782 The default legend position is "right". The available positions
784 are:
785 top
787 bottom
788 left
789 right
790 top_right
791 overlay_left
792 overlay_right
793 overlay_top_right
794 none
795 • "border"
797 Set the border properties of the legend such as colour and style.
799 See the "CHART FORMATTING" section below.
800 • "fill"
802 Set the fill properties of the legend such as colour. See the
804 "CHART FORMATTING" section below.
805 • "pattern"
807 Set the pattern fill properties of the legend. See the "CHART
809 FORMATTING" section below.
810 • "gradient"
812 Set the gradient fill properties of the legend. See the "CHART
814 FORMATTING" section below.
815 • "font"
817 Set the font properties of the chart legend:
819 $chart->set_legend( font => { bold => 1, italic => 1 } );
821 See the "CHART FONTS" section below.
823 • "delete_series"
825 This allows you to remove 1 or more series from the legend (the
827 series will still display on the chart). This property takes an
828 array ref as an argument and the series are zero indexed:
829 # Delete/hide series index 0 and 2 from the legend.
831 $chart->set_legend( delete_series => [0, 2] );
832 • "layout"
834 Set the "(x, y)" position of the legend in chart relative units:
836 $chart->set_legend(
838 layout => {
839 x => 0.80,
840 y => 0.37,
841 width => 0.12,
842 height => 0.25,
843 }
844 );
845 See the "CHART LAYOUT" section below.
847 set_chartarea()
849 The "set_chartarea()" method is used to set the properties of the chart
850 area.
851 $chart->set_chartarea(
853 border => { none => 1 },
854 fill => { color => 'red' }
855 );
856 The properties that can be set are:
858 • "border"
860 Set the border properties of the chartarea such as colour and
862 style. See the "CHART FORMATTING" section below.
863 • "fill"
865 Set the fill properties of the chartarea such as colour. See the
867 "CHART FORMATTING" section below.
868 • "pattern"
870 Set the pattern fill properties of the chartarea. See the "CHART
872 FORMATTING" section below.
873 • "gradient"
875 Set the gradient fill properties of the chartarea. See the "CHART
877 FORMATTING" section below.
878 set_plotarea()
880 The "set_plotarea()" method is used to set properties of the plot area
881 of a chart.
882 $chart->set_plotarea(
884 border => { color => 'yellow', width => 1, dash_type => 'dash' },
885 fill => { color => '#92D050' }
886 );
887 The properties that can be set are:
889 • "border"
891 Set the border properties of the plotarea such as colour and style.
893 See the "CHART FORMATTING" section below.
894 • "fill"
896 Set the fill properties of the plotarea such as colour. See the
898 "CHART FORMATTING" section below.
899 • "pattern"
901 Set the pattern fill properties of the plotarea. See the "CHART
903 FORMATTING" section below.
904 • "gradient"
906 Set the gradient fill properties of the plotarea. See the "CHART
908 FORMATTING" section below.
909 • "layout"
911 Set the "(x, y)" position of the plotarea in chart relative units:
913 $chart->set_plotarea(
915 layout => {
916 x => 0.35,
917 y => 0.26,
918 width => 0.62,
919 height => 0.50,
920 }
921 );
922 See the "CHART LAYOUT" section below.
924 set_style()
926 The "set_style()" method is used to set the style of the chart to one
927 of the 42 built-in styles available on the 'Design' tab in Excel:
928 $chart->set_style( 4 );
930 The default style is 2.
932 set_table()
934 The "set_table()" method adds a data table below the horizontal axis
935 with the data used to plot the chart.
936 $chart->set_table();
938 The available options, with default values are:
940 vertical => 1 # Display vertical lines in the table.
942 horizontal => 1 # Display horizontal lines in the table.
943 outline => 1 # Display an outline in the table.
944 show_keys => 0 # Show the legend keys with the table data.
945 font => {} # Standard chart font properties.
946 The data table can only be shown with Bar, Column, Line, Area and stock
948 charts. For font properties see the "CHART FONTS" section below.
949 set_up_down_bars
951 The "set_up_down_bars()" method adds Up-Down bars to Line charts to
952 indicate the difference between the first and last data series.
953 $chart->set_up_down_bars();
955 It is possible to format the up and down bars to add "fill", "pattern",
957 "gradient" and "border" properties if required. See the "CHART
958 FORMATTING" section below.
959 $chart->set_up_down_bars(
961 up => { fill => { color => 'green' } },
962 down => { fill => { color => 'red' } },
963 );
964 Up-down bars can only be applied to Line charts and to Stock charts (by
966 default).
967 set_drop_lines
969 The "set_drop_lines()" method adds Drop Lines to charts to show the
970 Category value of points in the data.
971 $chart->set_drop_lines();
973 It is possible to format the Drop Line "line" properties if required.
975 See the "CHART FORMATTING" section below.
976 $chart->set_drop_lines( line => { color => 'red', dash_type => 'square_dot' } );
978 Drop Lines are only available in Line, Area and Stock charts.
980 set_high_low_lines
982 The "set_high_low_lines()" method adds High-Low lines to charts to show
983 the maximum and minimum values of points in a Category.
984 $chart->set_high_low_lines();
986 It is possible to format the High-Low Line "line" properties if
988 required. See the "CHART FORMATTING" section below.
989 $chart->set_high_low_lines( line => { color => 'red' } );
991 High-Low Lines are only available in Line and Stock charts.
993 show_blanks_as()
995 The "show_blanks_as()" method controls how blank data is displayed in a
996 chart.
997 $chart->show_blanks_as( 'span' );
999 The available options are:
1001 gap # Blank data is shown as a gap. The default.
1003 zero # Blank data is displayed as zero.
1004 span # Blank data is connected with a line.
1005 show_hidden_data()
1007 Display data in hidden rows or columns on the chart.
1008 $chart->show_hidden_data();
1010
1012 This section details the following properties of "add_series()" in more
1013 detail:
1014 marker
1016 trendline
1017 y_error_bars
1018 x_error_bars
1019 data_labels
1020 points
1021 smooth
1022 Marker
1024 The marker format specifies the properties of the markers used to
1025 distinguish series on a chart. In general only Line and Scatter chart
1026 types and trendlines use markers.
1027 The following properties can be set for "marker" formats in a chart.
1029 type
1031 size
1032 border
1033 fill
1034 pattern
1035 gradient
1036 The "type" property sets the type of marker that is used with a series.
1038 $chart->add_series(
1040 values => '=Sheet1!$B$1:$B$5',
1041 marker => { type => 'diamond' },
1042 );
1043 The following "type" properties can be set for "marker" formats in a
1045 chart. These are shown in the same order as in the Excel format dialog.
1046 automatic
1048 none
1049 square
1050 diamond
1051 triangle
1052 x
1053 star
1054 short_dash
1055 long_dash
1056 circle
1057 plus
1058 The "automatic" type is a special case which turns on a marker using
1060 the default marker style for the particular series number.
1061 $chart->add_series(
1063 values => '=Sheet1!$B$1:$B$5',
1064 marker => { type => 'automatic' },
1065 );
1066 If "automatic" is on then other marker properties such as size, border
1068 or fill cannot be set.
1069 The "size" property sets the size of the marker and is generally used
1071 in conjunction with "type".
1072 $chart->add_series(
1074 values => '=Sheet1!$B$1:$B$5',
1075 marker => { type => 'diamond', size => 7 },
1076 );
1077 Nested "border" and "fill" properties can also be set for a marker. See
1079 the "CHART FORMATTING" section below.
1080 $chart->add_series(
1082 values => '=Sheet1!$B$1:$B$5',
1083 marker => {
1084 type => 'square',
1085 size => 5,
1086 border => { color => 'red' },
1087 fill => { color => 'yellow' },
1088 },
1089 );
1090 Trendline
1092 A trendline can be added to a chart series to indicate trends in the
1093 data such as a moving average or a polynomial fit.
1094 The following properties can be set for trendlines in a chart series.
1096 type
1098 order (for polynomial trends)
1099 period (for moving average)
1100 forward (for all except moving average)
1101 backward (for all except moving average)
1102 name
1103 line
1104 intercept (for exponential, linear and polynomial only)
1105 display_equation (for all except moving average)
1106 display_r_squared (for all except moving average)
1107 The "type" property sets the type of trendline in the series.
1109 $chart->add_series(
1111 values => '=Sheet1!$B$1:$B$5',
1112 trendline => { type => 'linear' },
1113 );
1114 The available "trendline" types are:
1116 exponential
1118 linear
1119 log
1120 moving_average
1121 polynomial
1122 power
1123 A "polynomial" trendline can also specify the "order" of the
1125 polynomial. The default value is 2.
1126 $chart->add_series(
1128 values => '=Sheet1!$B$1:$B$5',
1129 trendline => {
1130 type => 'polynomial',
1131 order => 3,
1132 },
1133 );
1134 A "moving_average" trendline can also specify the "period" of the
1136 moving average. The default value is 2.
1137 $chart->add_series(
1139 values => '=Sheet1!$B$1:$B$5',
1140 trendline => {
1141 type => 'moving_average',
1142 period => 3,
1143 },
1144 );
1145 The "forward" and "backward" properties set the forecast period of the
1147 trendline.
1148 $chart->add_series(
1150 values => '=Sheet1!$B$1:$B$5',
1151 trendline => {
1152 type => 'linear',
1153 forward => 0.5,
1154 backward => 0.5,
1155 },
1156 );
1157 The "name" property sets an optional name for the trendline that will
1159 appear in the chart legend. If it isn't specified the Excel default
1160 name will be displayed. This is usually a combination of the trendline
1161 type and the series name.
1162 $chart->add_series(
1164 values => '=Sheet1!$B$1:$B$5',
1165 trendline => {
1166 type => 'linear',
1167 name => 'Interpolated trend',
1168 },
1169 );
1170 The "intercept" property sets the point where the trendline crosses the
1172 Y (value) axis:
1173 $chart->add_series(
1175 values => '=Sheet1!$B$1:$B$5',
1176 trendline => {
1177 type => 'linear',
1178 intercept => 0.8,
1179 },
1180 );
1181 The "display_equation" property displays the trendline equation on the
1183 chart.
1184 $chart->add_series(
1186 values => '=Sheet1!$B$1:$B$5',
1187 trendline => {
1188 type => 'linear',
1189 display_equation => 1,
1190 },
1191 );
1192 The "display_r_squared" property displays the R squared value of the
1194 trendline on the chart.
1195 $chart->add_series(
1197 values => '=Sheet1!$B$1:$B$5',
1198 trendline => {
1199 type => 'linear',
1200 display_r_squared => 1
1201 },
1202 );
1203 Several of these properties can be set in one go:
1205 $chart->add_series(
1207 values => '=Sheet1!$B$1:$B$5',
1208 trendline => {
1209 type => 'polynomial',
1210 name => 'My trend name',
1211 order => 2,
1212 forward => 0.5,
1213 backward => 0.5,
1214 intercept => 1.5,
1215 display_equation => 1,
1216 display_r_squared => 1,
1217 line => {
1218 color => 'red',
1219 width => 1,
1220 dash_type => 'long_dash',
1221 }
1222 },
1223 );
1224 Trendlines cannot be added to series in a stacked chart or pie chart,
1226 radar chart, doughnut or (when implemented) to 3D, or surface charts.
1227 Error Bars
1229 Error bars can be added to a chart series to indicate error bounds in
1230 the data. The error bars can be vertical "y_error_bars" (the most
1231 common type) or horizontal "x_error_bars" (for Bar and Scatter charts
1232 only).
1233 The following properties can be set for error bars in a chart series.
1235 type
1237 value (for all types except standard error and custom)
1238 plus_values (for custom only)
1239 minus_values (for custom only)
1240 direction
1241 end_style
1242 line
1243 The "type" property sets the type of error bars in the series.
1245 $chart->add_series(
1247 values => '=Sheet1!$B$1:$B$5',
1248 y_error_bars => { type => 'standard_error' },
1249 );
1250 The available error bars types are available:
1252 fixed
1254 percentage
1255 standard_deviation
1256 standard_error
1257 custom
1258 All error bar types, except for "standard_error" and "custom" must also
1260 have a value associated with it for the error bounds:
1261 $chart->add_series(
1263 values => '=Sheet1!$B$1:$B$5',
1264 y_error_bars => {
1265 type => 'percentage',
1266 value => 5,
1267 },
1268 );
1269 The "custom" error bar type must specify "plus_values" and
1271 "minus_values" which should either by a "Sheet1!$A$1:$A$5" type range
1272 formula or an arrayref of values:
1273 $chart->add_series(
1275 categories => '=Sheet1!$A$1:$A$5',
1276 values => '=Sheet1!$B$1:$B$5',
1277 y_error_bars => {
1278 type => 'custom',
1279 plus_values => '=Sheet1!$C$1:$C$5',
1280 minus_values => '=Sheet1!$D$1:$D$5',
1281 },
1282 );
1283 # or
1285 $chart->add_series(
1288 categories => '=Sheet1!$A$1:$A$5',
1289 values => '=Sheet1!$B$1:$B$5',
1290 y_error_bars => {
1291 type => 'custom',
1292 plus_values => [1, 1, 1, 1, 1],
1293 minus_values => [2, 2, 2, 2, 2],
1294 },
1295 );
1296 Note, as in Excel the items in the "minus_values" do not need to be
1298 negative.
1299 The "direction" property sets the direction of the error bars. It
1301 should be one of the following:
1302 plus # Positive direction only.
1304 minus # Negative direction only.
1305 both # Plus and minus directions, The default.
1306 The "end_style" property sets the style of the error bar end cap. The
1308 options are 1 (the default) or 0 (for no end cap):
1309 $chart->add_series(
1311 values => '=Sheet1!$B$1:$B$5',
1312 y_error_bars => {
1313 type => 'fixed',
1314 value => 2,
1315 end_style => 0,
1316 direction => 'minus'
1317 },
1318 );
1319 Data Labels
1321 Data labels can be added to a chart series to indicate the values of
1322 the plotted data points.
1323 The following properties can be set for "data_labels" formats in a
1325 chart.
1326 value
1328 category
1329 series_name
1330 position
1331 percentage
1332 leader_lines
1333 separator
1334 legend_key
1335 num_format
1336 font
1337 border
1338 fill
1339 pattern
1340 gradient
1341 custom
1342 The "value" property turns on the Value data label for a series.
1344 $chart->add_series(
1346 values => '=Sheet1!$B$1:$B$5',
1347 data_labels => { value => 1 },
1348 );
1349 The "category" property turns on the Category Name data label for a
1351 series.
1352 $chart->add_series(
1354 values => '=Sheet1!$B$1:$B$5',
1355 data_labels => { category => 1 },
1356 );
1357 The "series_name" property turns on the Series Name data label for a
1359 series.
1360 $chart->add_series(
1362 values => '=Sheet1!$B$1:$B$5',
1363 data_labels => { series_name => 1 },
1364 );
1365 The "position" property is used to position the data label for a
1367 series.
1368 $chart->add_series(
1370 values => '=Sheet1!$B$1:$B$5',
1371 data_labels => { value => 1, position => 'center' },
1372 );
1373 In Excel the data label positions vary for different chart types. The
1375 allowable positions are:
1376 | Position | Line | Bar | Pie | Area |
1378 | | Scatter | Column | Doughnut | Radar |
1379 | | Stock | | | |
1380 |---------------|-----------|-----------|-----------|-----------|
1381 | center | Yes | Yes | Yes | Yes* |
1382 | right | Yes* | | | |
1383 | left | Yes | | | |
1384 | above | Yes | | | |
1385 | below | Yes | | | |
1386 | inside_base | | Yes | | |
1387 | inside_end | | Yes | Yes | |
1388 | outside_end | | Yes* | Yes | |
1389 | best_fit | | | Yes* | |
1390 Note: The * indicates the default position for each chart type in
1392 Excel, if a position isn't specified.
1393 The "percentage" property is used to turn on the display of data labels
1395 as a Percentage for a series. It is mainly used for pie and doughnut
1396 charts.
1397 $chart->add_series(
1399 values => '=Sheet1!$B$1:$B$5',
1400 data_labels => { percentage => 1 },
1401 );
1402 The "leader_lines" property is used to turn on Leader Lines for the
1404 data label for a series. It is mainly used for pie charts.
1405 $chart->add_series(
1407 values => '=Sheet1!$B$1:$B$5',
1408 data_labels => { value => 1, leader_lines => 1 },
1409 );
1410 Note: Even when leader lines are turned on they aren't automatically
1412 visible in Excel or Excel::Writer::XLSX. Due to an Excel limitation (or
1413 design) leader lines only appear if the data label is moved manually or
1414 if the data labels are very close and need to be adjusted
1415 automatically.
1416 The "separator" property is used to change the separator between
1418 multiple data label items:
1419 $chart->add_series(
1421 values => '=Sheet1!$B$1:$B$5',
1422 data_labels => { percentage => 1 },
1423 data_labels => { value => 1, category => 1, separator => "\n" },
1424 );
1425 The separator value must be one of the following strings:
1427 ','
1429 ';'
1430 '.'
1431 "\n"
1432 ' '
1433 The "legend_key" property is used to turn on Legend Key for the data
1435 label for a series:
1436 $chart->add_series(
1438 values => '=Sheet1!$B$1:$B$5',
1439 data_labels => { value => 1, legend_key => 1 },
1440 );
1441 The "num_format" property is used to set the number format for the data
1443 labels.
1444 $chart->add_series(
1446 values => '=Sheet1!$A$1:$A$5',
1447 data_labels => { value => 1, num_format => '#,##0.00' },
1448 );
1449 The number format is similar to the Worksheet Cell Format "num_format"
1451 apart from the fact that a format index cannot be used. The explicit
1452 format string must be used as shown above. See "set_num_format()" in
1453 Excel::Writer::XLSX for more information.
1454 The "font" property is used to set the font properties of the data
1456 labels in a series:
1457 $chart->add_series(
1459 values => '=Sheet1!$A$1:$A$5',
1460 data_labels => {
1461 value => 1,
1462 font => { name => 'Consolas' }
1463 },
1464 );
1465 The "font" property is also used to rotate the data labels in a series:
1467 $chart->add_series(
1469 values => '=Sheet1!$A$1:$A$5',
1470 data_labels => {
1471 value => 1,
1472 font => { rotation => 45 }
1473 },
1474 );
1475 See the "CHART FONTS" section below.
1477 The "border" property sets the border properties of the data labels
1479 such as colour and style. See the "CHART FORMATTING" section below.
1480 The "fill" property sets the fill properties of the data labels such as
1482 colour. See the "CHART FORMATTING" section below.
1483 Example of setting data label formatting:
1485 $chart->add_series(
1487 categories => '=Sheet1!$A$2:$A$7',
1488 values => '=Sheet1!$B$2:$B$7',
1489 data_labels => { value => 1,
1490 border => {color => 'red'},
1491 fill => {color => 'yellow'} },
1492 );
1493 The "pattern" property sets the pattern properties of the data labels.
1495 See the "CHART FORMATTING" section below.
1496 The "gradient" property sets the gradient properties of the data
1498 labels. See the "CHART FORMATTING" section below.
1499 The "custom" property is used to set the properties of individual data
1501 labels, see below.
1502 Custom Data Labels
1504 The "custom" property data label property is used to set the properties
1505 of individual data labels in a series. The most common use for this is
1506 to set custom text or number labels:
1507 my $custom_labels = [
1509 { value => 'Jan' },
1510 { value => 'Feb' },
1511 { value => 'Mar' },
1512 { value => 'Apr' },
1513 { value => 'May' },
1514 { value => 'Jun' },
1515 ];
1516 $chart->add_series(
1518 categories => '=Sheet1!$A$2:$A$7',
1519 values => '=Sheet1!$B$2:$B$7',
1520 data_labels => { value => 1, custom => $custom_labels },
1521 );
1522 As shown in the previous examples th "custom" property should be a list
1524 of dicts. Any property dict that is set to "undef" or not included in
1525 the list will be assigned the default data label value:
1526 my $custom_labels = [
1528 undef,
1529 { value => 'Feb' },
1530 { value => 'Mar' },
1531 { value => 'Apr' },
1532 ];
1533 The property elements of the "custom" lists should be dicts with the
1535 following allowable keys/sub-properties:
1536 value
1538 font
1539 border
1540 fill
1541 pattern
1542 gradient
1543 delete
1544 The "value" property should be a string, number or formula string that
1546 refers to a cell from which the value will be taken:
1547 $custom_labels = [
1549 { value => '=Sheet1!$C$2' },
1550 { value => '=Sheet1!$C$3' },
1551 { value => '=Sheet1!$C$4' },
1552 { value => '=Sheet1!$C$5' },
1553 { value => '=Sheet1!$C$6' },
1554 { value => '=Sheet1!$C$7' },
1555 ];
1556 The "font" property is used to set the font of the custom data label of
1558 a series (See the "CHART FONTS" section below):
1559 $custom_labels = [
1561 { value => '=Sheet1!$C$1', font => { color => 'red' } },
1562 { value => '=Sheet1!$C$2', font => { color => 'red' } },
1563 { value => '=Sheet1!$C$2', font => { color => 'red' } },
1564 { value => '=Sheet1!$C$4', font => { color => 'red' } },
1565 { value => '=Sheet1!$C$5', font => { color => 'red' } },
1566 { value => '=Sheet1!$C$6', font => { color => 'red' } },
1567 ];
1568 The "border" property sets the border properties of the data labels
1570 such as colour and style. See the "CHART FORMATTING" section below.
1571 The "fill" property sets the fill properties of the data labels such as
1573 colour. See the "CHART FORMATTING" section below.
1574 Example of setting custom data label formatting:
1576 $custom_labels = [
1578 { value => 'Jan', border => {color => 'blue'} },
1579 { value => 'Feb' },
1580 { value => 'Mar' },
1581 { value => 'Apr' },
1582 { value => 'May' },
1583 { value => 'Jun', fill => {color => 'green'} },
1584 ];
1585 The "pattern" property sets the pattern properties of the data labels.
1587 See the "CHART FORMATTING" section below.
1588 The "gradient" property sets the gradient properties of the data
1590 labels. See the "CHART FORMATTING" section below.
1591 The "delete" property can be used to delete labels in a series. This
1593 can be useful if you want to highlight one or more cells in the series,
1594 for example the maximum and the minimum:
1595 $custom_labels = [
1597 undef,
1598 { delete => 1 },
1599 { delete => 1 },
1600 { delete => 1 },
1601 { delete => 1 },
1602 undef,
1603 ];
1604 Points
1606 In general formatting is applied to an entire series in a chart.
1607 However, it is occasionally required to format individual points in a
1608 series. In particular this is required for Pie and Doughnut charts
1609 where each segment is represented by a point.
1610 In these cases it is possible to use the "points" property of
1612 "add_series()":
1613 $chart->add_series(
1615 values => '=Sheet1!$A$1:$A$3',
1616 points => [
1617 { fill => { color => '#FF0000' } },
1618 { fill => { color => '#CC0000' } },
1619 { fill => { color => '#990000' } },
1620 ],
1621 );
1622 The "points" property takes an array ref of format options (see the
1624 "CHART FORMATTING" section below). To assign default properties to
1625 points in a series pass "undef" values in the array ref:
1626 # Format point 3 of 3 only.
1628 $chart->add_series(
1629 values => '=Sheet1!$A$1:$A$3',
1630 points => [
1631 undef,
1632 undef,
1633 { fill => { color => '#990000' } },
1634 ],
1635 );
1636 # Format the first point only.
1638 $chart->add_series(
1639 values => '=Sheet1!$A$1:$A$3',
1640 points => [ { fill => { color => '#FF0000' } } ],
1641 );
1642 Smooth
1644 The "smooth" option is used to set the smooth property of a line
1645 series. It is only applicable to the "Line" and "Scatter" chart types.
1646 $chart->add_series( values => '=Sheet1!$C$1:$C$5',
1648 smooth => 1 );
1649
1651 The following chart formatting properties can be set for any chart
1652 object that they apply to (and that are supported by
1653 Excel::Writer::XLSX) such as chart lines, column fill areas, plot area
1654 borders, markers, gridlines and other chart elements documented above.
1655 line
1657 border
1658 fill
1659 pattern
1660 gradient
1661 Chart formatting properties are generally set using hash refs.
1663 $chart->add_series(
1665 values => '=Sheet1!$B$1:$B$5',
1666 line => { color => 'blue' },
1667 );
1668 In some cases the format properties can be nested. For example a
1670 "marker" may contain "border" and "fill" sub-properties.
1671 $chart->add_series(
1673 values => '=Sheet1!$B$1:$B$5',
1674 line => { color => 'blue' },
1675 marker => {
1676 type => 'square',
1677 size => 5,
1678 border => { color => 'red' },
1679 fill => { color => 'yellow' },
1680 },
1681 );
1682 Line
1684 The line format is used to specify properties of line objects that
1685 appear in a chart such as a plotted line on a chart or a border.
1686 The following properties can be set for "line" formats in a chart.
1688 none
1690 color
1691 width
1692 dash_type
1693 transparency
1694 The "none" property is uses to turn the "line" off (it is always on by
1696 default except in Scatter charts). This is useful if you wish to plot a
1697 series with markers but without a line.
1698 $chart->add_series(
1700 values => '=Sheet1!$B$1:$B$5',
1701 line => { none => 1 },
1702 );
1703 The "color" property sets the color of the "line".
1705 $chart->add_series(
1707 values => '=Sheet1!$B$1:$B$5',
1708 line => { color => 'red' },
1709 );
1710 The available colours are shown in the main Excel::Writer::XLSX
1712 documentation. It is also possible to set the colour of a line with a
1713 HTML style RGB colour:
1714 $chart->add_series(
1716 line => { color => '#FF0000' },
1717 );
1718 The "width" property sets the width of the "line". It should be
1720 specified in increments of 0.25 of a point as in Excel.
1721 $chart->add_series(
1723 values => '=Sheet1!$B$1:$B$5',
1724 line => { width => 3.25 },
1725 );
1726 The "dash_type" property sets the dash style of the line.
1728 $chart->add_series(
1730 values => '=Sheet1!$B$1:$B$5',
1731 line => { dash_type => 'dash_dot' },
1732 );
1733 The following "dash_type" values are available. They are shown in the
1735 order that they appear in the Excel dialog.
1736 solid
1738 round_dot
1739 square_dot
1740 dash
1741 dash_dot
1742 long_dash
1743 long_dash_dot
1744 long_dash_dot_dot
1745 The default line style is "solid".
1747 The "transparency" property sets the transparency of the "line" color
1749 in the integer range 1 - 100. The color must be set for transparency to
1750 work, it doesn't work with an automatic/default color:
1751 $chart->add_series(
1753 values => '=Sheet1!$B$1:$B$5',
1754 line => { color => 'yellow', transparency => 50 },
1755 );
1756 More than one "line" property can be specified at a time:
1758 $chart->add_series(
1760 values => '=Sheet1!$B$1:$B$5',
1761 line => {
1762 color => 'red',
1763 width => 1.25,
1764 dash_type => 'square_dot',
1765 },
1766 );
1767 Border
1769 The "border" property is a synonym for "line".
1770 It can be used as a descriptive substitute for "line" in chart types
1772 such as Bar and Column that have a border and fill style rather than a
1773 line style. In general chart objects with a "border" property will also
1774 have a fill property.
1775 Solid Fill
1777 The fill format is used to specify filled areas of chart objects such
1778 as the interior of a column or the background of the chart itself.
1779 The following properties can be set for "fill" formats in a chart.
1781 none
1783 color
1784 transparency
1785 The "none" property is used to turn the "fill" property off (it is
1787 generally on by default).
1788 $chart->add_series(
1790 values => '=Sheet1!$B$1:$B$5',
1791 fill => { none => 1 },
1792 );
1793 The "color" property sets the colour of the "fill" area.
1795 $chart->add_series(
1797 values => '=Sheet1!$B$1:$B$5',
1798 fill => { color => 'red' },
1799 );
1800 The available colours are shown in the main Excel::Writer::XLSX
1802 documentation. It is also possible to set the colour of a fill with a
1803 HTML style RGB colour:
1804 $chart->add_series(
1806 fill => { color => '#FF0000' },
1807 );
1808 The "transparency" property sets the transparency of the solid fill
1810 color in the integer range 1 - 100. The color must be set for
1811 transparency to work, it doesn't work with an automatic/default color:
1812 $chart->set_chartarea( fill => { color => 'yellow', transparency => 75 } );
1814 The "fill" format is generally used in conjunction with a "border"
1816 format which has the same properties as a "line" format.
1817 $chart->add_series(
1819 values => '=Sheet1!$B$1:$B$5',
1820 border => { color => 'red' },
1821 fill => { color => 'yellow' },
1822 );
1823 Pattern Fill
1825 The pattern fill format is used to specify pattern filled areas of
1826 chart objects such as the interior of a column or the background of the
1827 chart itself.
1828 The following properties can be set for "pattern" fill formats in a
1830 chart:
1831 pattern: the pattern to be applied (required)
1833 fg_color: the foreground color of the pattern (required)
1834 bg_color: the background color (optional, defaults to white)
1835 For example:
1837 $chart->set_plotarea(
1839 pattern => {
1840 pattern => 'percent_5',
1841 fg_color => 'red',
1842 bg_color => 'yellow',
1843 }
1844 );
1845 The following patterns can be applied:
1847 percent_5
1849 percent_10
1850 percent_20
1851 percent_25
1852 percent_30
1853 percent_40
1854 percent_50
1855 percent_60
1856 percent_70
1857 percent_75
1858 percent_80
1859 percent_90
1860 light_downward_diagonal
1861 light_upward_diagonal
1862 dark_downward_diagonal
1863 dark_upward_diagonal
1864 wide_downward_diagonal
1865 wide_upward_diagonal
1866 light_vertical
1867 light_horizontal
1868 narrow_vertical
1869 narrow_horizontal
1870 dark_vertical
1871 dark_horizontal
1872 dashed_downward_diagonal
1873 dashed_upward_diagonal
1874 dashed_horizontal
1875 dashed_vertical
1876 small_confetti
1877 large_confetti
1878 zigzag
1879 wave
1880 diagonal_brick
1881 horizontal_brick
1882 weave
1883 plaid
1884 divot
1885 dotted_grid
1886 dotted_diamond
1887 shingle
1888 trellis
1889 sphere
1890 small_grid
1891 large_grid
1892 small_check
1893 large_check
1894 outlined_diamond
1895 solid_diamond
1896 The foreground color, "fg_color", is a required parameter and can be a
1898 Html style "#RRGGBB" string or a limited number of named colors. The
1899 available colours are shown in the main Excel::Writer::XLSX
1900 documentation.
1901 The background color, "bg_color", is optional and defaults to black.
1903 If a pattern fill is used on a chart object it overrides the solid fill
1905 properties of the object.
1906 Gradient Fill
1908 The gradient fill format is used to specify gradient filled areas of
1909 chart objects such as the interior of a column or the background of the
1910 chart itself.
1911 The following properties can be set for "gradient" fill formats in a
1913 chart:
1914 colors: a list of colors
1916 positions: an optional list of positions for the colors
1917 type: the optional type of gradient fill
1918 angle: the optional angle of the linear fill
1919 The "colors" property sets a list of colors that define the "gradient":
1921 $chart->set_plotarea(
1923 gradient => { colors => [ '#DDEBCF', '#9CB86E', '#156B13' ] }
1924 );
1925 Excel allows between 2 and 10 colors in a gradient but it is unlikely
1927 that you will require more than 2 or 3.
1928 As with solid or pattern fill it is also possible to set the colors of
1930 a gradient with a Html style "#RRGGBB" string or a limited number of
1931 named colors. The available colours are shown in the main
1932 Excel::Writer::XLSX documentation:
1933 $chart->add_series(
1935 values => '=Sheet1!$A$1:$A$5',
1936 gradient => { colors => [ 'red', 'green' ] }
1937 );
1938 The "positions" defines an optional list of positions, between 0 and
1940 100, of where the colors in the gradient are located. Default values
1941 are provided for "colors" lists of between 2 and 4 but they can be
1942 specified if required:
1943 $chart->add_series(
1945 values => '=Sheet1!$A$1:$A$5',
1946 gradient => {
1947 colors => [ '#DDEBCF', '#156B13' ],
1948 positions => [ 10, 90 ],
1949 }
1950 );
1951 The "type" property can have one of the following values:
1953 linear (the default)
1955 radial
1956 rectangular
1957 path
1958 For example:
1960 $chart->add_series(
1962 values => '=Sheet1!$A$1:$A$5',
1963 gradient => {
1964 colors => [ '#DDEBCF', '#9CB86E', '#156B13' ],
1965 type => 'radial'
1966 }
1967 );
1968 If "type" isn't specified it defaults to "linear".
1970 For a "linear" fill the angle of the gradient can also be specified:
1972 $chart->add_series(
1974 values => '=Sheet1!$A$1:$A$5',
1975 gradient => { colors => [ '#DDEBCF', '#9CB86E', '#156B13' ],
1976 angle => 30 }
1977 );
1978 The default angle is 90 degrees.
1980 If gradient fill is used on a chart object it overrides the solid fill
1982 and pattern fill properties of the object.
1983
1985 The following font properties can be set for any chart object that they
1986 apply to (and that are supported by Excel::Writer::XLSX) such as chart
1987 titles, axis labels, axis numbering and data labels. They correspond to
1988 the equivalent Worksheet cell Format object properties. See
1989 "FORMAT_METHODS" in Excel::Writer::XLSX for more information.
1990 name
1992 size
1993 bold
1994 italic
1995 underline
1996 rotation
1997 color
1998 The following explains the available font properties:
2000 • "name"
2002 Set the font name:
2004 $chart->set_x_axis( num_font => { name => 'Arial' } );
2006 • "size"
2008 Set the font size:
2010 $chart->set_x_axis( num_font => { name => 'Arial', size => 10 } );
2012 • "bold"
2014 Set the font bold property, should be 0 or 1:
2016 $chart->set_x_axis( num_font => { bold => 1 } );
2018 • "italic"
2020 Set the font italic property, should be 0 or 1:
2022 $chart->set_x_axis( num_font => { italic => 1 } );
2024 • "underline"
2026 Set the font underline property, should be 0 or 1:
2028 $chart->set_x_axis( num_font => { underline => 1 } );
2030 • "rotation"
2032 Set the font rotation in the integer range -90 to 90, and 270-271:
2034 $chart->set_x_axis( num_font => { rotation => 45 } );
2036 This is useful for displaying large axis data such as dates in a
2038 more compact format.
2039 There are 2 special case angles outside the range -90 to 90:
2041 • 270: Stacked text, where the text runs from top to bottom.
2043 • 271: A special variant of stacked text for East Asian fonts.
2045 • "color"
2047 Set the font color property. Can be a color index, a color name or
2049 HTML style RGB colour:
2050 $chart->set_x_axis( num_font => { color => 'red' } );
2052 $chart->set_y_axis( num_font => { color => '#92D050' } );
2053 Here is an example of Font formatting in a Chart program:
2055 # Format the chart title.
2057 $chart->set_title(
2058 name => 'Sales Results Chart',
2059 name_font => {
2060 name => 'Calibri',
2061 color => 'yellow',
2062 },
2063 );
2064 # Format the X-axis.
2066 $chart->set_x_axis(
2067 name => 'Month',
2068 name_font => {
2069 name => 'Arial',
2070 color => '#92D050'
2071 },
2072 num_font => {
2073 name => 'Courier New',
2074 color => '#00B0F0',
2075 },
2076 );
2077 # Format the Y-axis.
2079 $chart->set_y_axis(
2080 name => 'Sales (1000 units)',
2081 name_font => {
2082 name => 'Century',
2083 underline => 1,
2084 color => 'red'
2085 },
2086 num_font => {
2087 bold => 1,
2088 italic => 1,
2089 color => '#7030A0',
2090 },
2091 );
2092
2094 The position of the chart in the worksheet is controlled by the
2095 "set_size()" method shown above.
2096 It is also possible to change the layout of the following chart sub-
2098 objects:
2099 plotarea
2101 legend
2102 title
2103 x_axis caption
2104 y_axis caption
2105 Here are some examples:
2107 $chart->set_plotarea(
2109 layout => {
2110 x => 0.35,
2111 y => 0.26,
2112 width => 0.62,
2113 height => 0.50,
2114 }
2115 );
2116 $chart->set_legend(
2118 layout => {
2119 x => 0.80,
2120 y => 0.37,
2121 width => 0.12,
2122 height => 0.25,
2123 }
2124 );
2125 $chart->set_title(
2127 name => 'Title',
2128 layout => {
2129 x => 0.42,
2130 y => 0.14,
2131 }
2132 );
2133 $chart->set_x_axis(
2135 name => 'X axis',
2136 name_layout => {
2137 x => 0.34,
2138 y => 0.85,
2139 }
2140 );
2141 Note that it is only possible to change the width and height for the
2143 "plotarea" and "legend" objects. For the other text based objects the
2144 width and height are changed by the font dimensions.
2145 The layout units must be a float in the range "0 < x <= 1" and are
2147 expressed as a percentage of the chart dimensions as shown below:
2148 From this the layout units are calculated as follows:
2150 layout:
2152 width = w / W
2153 height = h / H
2154 x = a / W
2155 y = b / H
2156 These units are slightly cumbersome but are required by Excel so that
2158 the chart object positions remain relative to each other if the chart
2159 is resized by the user.
2160 Note that for "plotarea" the origin is the top left corner in the
2162 plotarea itself and does not take into account the axes.
2163
2165 In Excel a chartsheet (i.e, a chart that isn't embedded) shares
2166 properties with data worksheets such as tab selection, headers,
2167 footers, margins, and print properties.
2168 In Excel::Writer::XLSX you can set chartsheet properties using the same
2170 methods that are used for Worksheet objects.
2171 The following Worksheet methods are also available through a non-
2173 embedded Chart object:
2174 get_name()
2176 activate()
2177 select()
2178 hide()
2179 set_first_sheet()
2180 protect()
2181 set_zoom()
2182 set_tab_color()
2183 set_landscape()
2185 set_portrait()
2186 set_paper()
2187 set_margins()
2188 set_header()
2189 set_footer()
2190 See Excel::Writer::XLSX for a detailed explanation of these methods.
2192
2194 Here is a complete example that demonstrates some of the available
2195 features when creating a chart.
2196 #!/usr/bin/perl
2198 use strict;
2200 use warnings;
2201 use Excel::Writer::XLSX;
2202 my $workbook = Excel::Writer::XLSX->new( 'chart.xlsx' );
2204 my $worksheet = $workbook->add_worksheet();
2205 my $bold = $workbook->add_format( bold => 1 );
2206 # Add the worksheet data that the charts will refer to.
2208 my $headings = [ 'Number', 'Batch 1', 'Batch 2' ];
2209 my $data = [
2210 [ 2, 3, 4, 5, 6, 7 ],
2211 [ 10, 40, 50, 20, 10, 50 ],
2212 [ 30, 60, 70, 50, 40, 30 ],
2213 ];
2215 $worksheet->write( 'A1', $headings, $bold );
2217 $worksheet->write( 'A2', $data );
2218 # Create a new chart object. In this case an embedded chart.
2220 my $chart = $workbook->add_chart( type => 'column', embedded => 1 );
2221 # Configure the first series.
2223 $chart->add_series(
2224 name => '=Sheet1!$B$1',
2225 categories => '=Sheet1!$A$2:$A$7',
2226 values => '=Sheet1!$B$2:$B$7',
2227 );
2228 # Configure second series. Note alternative use of array ref to define
2230 # ranges: [ $sheetname, $row_start, $row_end, $col_start, $col_end ].
2231 $chart->add_series(
2232 name => '=Sheet1!$C$1',
2233 categories => [ 'Sheet1', 1, 6, 0, 0 ],
2234 values => [ 'Sheet1', 1, 6, 2, 2 ],
2235 );
2236 # Add a chart title and some axis labels.
2238 $chart->set_title ( name => 'Results of sample analysis' );
2239 $chart->set_x_axis( name => 'Test number' );
2240 $chart->set_y_axis( name => 'Sample length (mm)' );
2241 # Set an Excel chart style. Blue colors with white outline and shadow.
2243 $chart->set_style( 11 );
2244 # Insert the chart into the worksheet (with an offset).
2246 $worksheet->insert_chart( 'D2', $chart, { x_offset => 25, y_offset => 10 } );
2247 $workbook->close();
2249 __END__
2251
2253 Excel differentiates between a chart axis that is used for series
2254 categories and an axis that is used for series values.
2255 In the example above the X axis is the category axis and each of the
2257 values is evenly spaced. The Y axis (in this case) is the value axis
2258 and points are displayed according to their value.
2259 Since Excel treats the axes differently it also handles their
2261 formatting differently and exposes different properties for each.
2262 As such some of "Excel::Writer::XLSX" axis properties can be set for a
2264 value axis, some can be set for a category axis and some properties can
2265 be set for both.
2266 For example the "min" and "max" properties can only be set for value
2268 axes and "reverse" can be set for both. The type of axis that a
2269 property applies to is shown in the "set_x_axis()" section of the
2270 documentation above.
2271 Some charts such as "Scatter" and "Stock" have two value axes.
2273 Date Axes are a special type of category axis which are explained
2275 below.
2276
2278 Date Category Axes are category axes that display time or date
2279 information. In Excel::Writer::XLSX Date Category Axes are set using
2280 the "date_axis" option:
2281 $chart->set_x_axis( date_axis => 1 );
2283 In general you should also specify a number format for a date axis
2285 although Excel will usually default to the same format as the data
2286 being plotted:
2287 $chart->set_x_axis(
2289 date_axis => 1,
2290 num_format => 'dd/mm/yyyy',
2291 );
2292 Excel doesn't normally allow minimum and maximum values to be set for
2294 category axes. However, date axes are an exception. The "min" and "max"
2295 values should be set as Excel times or dates:
2296 $chart->set_x_axis(
2298 date_axis => 1,
2299 min => $worksheet->convert_date_time('2013-01-02T'),
2300 max => $worksheet->convert_date_time('2013-01-09T'),
2301 num_format => 'dd/mm/yyyy',
2302 );
2303 For date axes it is also possible to set the type of the major and
2305 minor units:
2306 $chart->set_x_axis(
2308 date_axis => 1,
2309 minor_unit => 4,
2310 minor_unit_type => 'months',
2311 major_unit => 1,
2312 major_unit_type => 'years',
2313 num_format => 'dd/mm/yyyy',
2314 );
2315
2317 It is possible to add a secondary axis of the same type to a chart by
2318 setting the "y2_axis" or "x2_axis" property of the series:
2319 #!/usr/bin/perl
2321 use strict;
2323 use warnings;
2324 use Excel::Writer::XLSX;
2325 my $workbook = Excel::Writer::XLSX->new( 'chart_secondary_axis.xlsx' );
2327 my $worksheet = $workbook->add_worksheet();
2328 # Add the worksheet data that the charts will refer to.
2330 my $data = [
2331 [ 2, 3, 4, 5, 6, 7 ],
2332 [ 10, 40, 50, 20, 10, 50 ],
2333 ];
2335 $worksheet->write( 'A1', $data );
2337 # Create a new chart object. In this case an embedded chart.
2339 my $chart = $workbook->add_chart( type => 'line', embedded => 1 );
2340 # Configure a series with a secondary axis
2342 $chart->add_series(
2343 values => '=Sheet1!$A$1:$A$6',
2344 y2_axis => 1,
2345 );
2346 $chart->add_series(
2348 values => '=Sheet1!$B$1:$B$6',
2349 );
2350 # Insert the chart into the worksheet.
2353 $worksheet->insert_chart( 'D2', $chart );
2354 $workbook->close();
2356 __END__
2358 It is also possible to have a secondary, combined, chart either with a
2360 shared or secondary axis, see below.
2361
2363 It is also possible to combine two different chart types, for example a
2364 column and line chart to create a Pareto chart using the Chart
2365 "combine()" method:
2366 Here is a simpler example:
2368 use strict;
2370 use warnings;
2371 use Excel::Writer::XLSX;
2372 my $workbook = Excel::Writer::XLSX->new( 'chart_combined.xlsx' );
2374 my $worksheet = $workbook->add_worksheet();
2375 my $bold = $workbook->add_format( bold => 1 );
2376 # Add the worksheet data that the charts will refer to.
2378 my $headings = [ 'Number', 'Batch 1', 'Batch 2' ];
2379 my $data = [
2380 [ 2, 3, 4, 5, 6, 7 ],
2381 [ 10, 40, 50, 20, 10, 50 ],
2382 [ 30, 60, 70, 50, 40, 30 ],
2383 ];
2385 $worksheet->write( 'A1', $headings, $bold );
2387 $worksheet->write( 'A2', $data );
2388 #
2390 # In the first example we will create a combined column and line chart.
2391 # They will share the same X and Y axes.
2392 #
2393 # Create a new column chart. This will use this as the primary chart.
2395 my $column_chart = $workbook->add_chart( type => 'column', embedded => 1 );
2396 # Configure the data series for the primary chart.
2398 $column_chart->add_series(
2399 name => '=Sheet1!$B$1',
2400 categories => '=Sheet1!$A$2:$A$7',
2401 values => '=Sheet1!$B$2:$B$7',
2402 );
2403 # Create a new column chart. This will use this as the secondary chart.
2405 my $line_chart = $workbook->add_chart( type => 'line', embedded => 1 );
2406 # Configure the data series for the secondary chart.
2408 $line_chart->add_series(
2409 name => '=Sheet1!$C$1',
2410 categories => '=Sheet1!$A$2:$A$7',
2411 values => '=Sheet1!$C$2:$C$7',
2412 );
2413 # Combine the charts.
2415 $column_chart->combine( $line_chart );
2416 # Add a chart title and some axis labels. Note, this is done via the
2418 # primary chart.
2419 $column_chart->set_title( name => 'Combined chart - same Y axis' );
2420 $column_chart->set_x_axis( name => 'Test number' );
2421 $column_chart->set_y_axis( name => 'Sample length (mm)' );
2422 # Insert the chart into the worksheet
2425 $worksheet->insert_chart( 'E2', $column_chart );
2426 $workbook->close();
2428 The secondary chart can also be placed on a secondary axis using the
2430 methods shown in the previous section.
2431 In this case it is just necessary to add a "y2_axis" parameter to the
2433 series and, if required, add a title using "set_y2_axis()" of the
2434 secondary chart. The following are the additions to the previous
2435 example to place the secondary chart on the secondary axis:
2436 ...
2438 $line_chart->add_series(
2440 name => '=Sheet1!$C$1',
2441 categories => '=Sheet1!$A$2:$A$7',
2442 values => '=Sheet1!$C$2:$C$7',
2443 y2_axis => 1,
2444 );
2445 ...
2447 # Note: the y2 properites are on the secondary chart.
2449 $line_chart2->set_y2_axis( name => 'Target length (mm)' );
2450 The examples above use the concept of a primary and secondary chart.
2452 The primary chart is the chart that defines the primary X and Y axis.
2453 It is also used for setting all chart properties apart from the
2454 secondary data series. For example the chart title and axes properties
2455 should be set via the primary chart (except for the the secondary "y2"
2456 axis properties which should be applied to the secondary chart).
2457 See also "chart_combined.pl" and "chart_pareto.pl" examples in the
2459 distro for more detailed examples.
2460 There are some limitations on combined charts:
2462 • Pie charts cannot currently be combined.
2464 • Scatter charts cannot currently be used as a primary chart but they
2466 can be used as a secondary chart.
2467 • Bar charts can only combined secondary charts on a secondary axis.
2469 This is an Excel limitation.
2470
2472 John McNamara jmcnamara@cpan.org
2473
2475 Copyright MM-MMXXI, John McNamara.
2476 All Rights Reserved. This module is free software. It may be used,
2478 redistributed and/or modified under the same terms as Perl itself.
2479perl v5.36.0 2022-07-22 Excel::Writer::XLSX::Chart(3)