1Spreadsheet::WriteExcelU:s:eCrhaCrotn(t3r)ibuted Perl DoScpurmeeandtsahteieotn::WriteExcel::Chart(3)
2
3
4

NAME

6       Chart - A writer class for Excel Charts.
7

SYNOPSIS

9       To create a simple Excel file with a chart using
10       Spreadsheet::WriteExcel:
11
12           #!/usr/bin/perl -w
13
14           use strict;
15           use Spreadsheet::WriteExcel;
16
17           my $workbook  = Spreadsheet::WriteExcel->new( 'chart.xls' );
18           my $worksheet = $workbook->add_worksheet();
19
20           my $chart     = $workbook->add_chart( type => 'column' );
21
22           # Configure the chart.
23           $chart->add_series(
24               categories => '=Sheet1!$A$2:$A$7',
25               values     => '=Sheet1!$B$2:$B$7',
26           );
27
28           # Add the worksheet data the chart refers to.
29           my $data = [
30               [ 'Category', 2, 3, 4, 5, 6, 7 ],
31               [ 'Value',    1, 4, 5, 2, 1, 5 ],
32           ];
33
34           $worksheet->write( 'A1', $data );
35
36           __END__
37

DESCRIPTION

39       The "Chart" module is an abstract base class for modules that implement
40       charts in Spreadsheet::WriteExcel. The information below is applicable
41       to all of the available subclasses.
42
43       The "Chart" module isn't used directly, a chart object is created via
44       the Workbook add_chart() method where the chart type is specified:
45
46           my $chart = $workbook->add_chart( type => 'column' );
47
48       Currently the supported chart types are:
49
50       •   "area": Creates an Area (filled line) style chart. See
51           Spreadsheet::WriteExcel::Chart::Area.
52
53       •   "bar": Creates a Bar style (transposed histogram) chart. See
54           Spreadsheet::WriteExcel::Chart::Bar.
55
56       •   "column": Creates a column style (histogram) chart. See
57           Spreadsheet::WriteExcel::Chart::Column.
58
59       •   "line": Creates a Line style chart. See
60           Spreadsheet::WriteExcel::Chart::Line.
61
62       •   "pie": Creates an Pie style chart. See
63           Spreadsheet::WriteExcel::Chart::Pie.
64
65       •   "scatter": Creates an Scatter style chart. See
66           Spreadsheet::WriteExcel::Chart::Scatter.
67
68       •   "stock": Creates an Stock style chart. See
69           Spreadsheet::WriteExcel::Chart::Stock.
70
71       More charts and sub-types will be supported in time. See the "TODO"
72       section.
73
74       Methods that are common to all chart types are documented below.
75

CHART METHODS

77   add_series()
78       In an Excel chart a "series" is a collection of information such as
79       values, x-axis labels and the name that define which data is plotted.
80       These settings are displayed when you select the "Chart -> Source
81       Data..." menu option.
82
83       With a Spreadsheet::WriteExcel chart object the add_series() method is
84       used to set the properties for a series:
85
86           $chart->add_series(
87               categories    => '=Sheet1!$A$2:$A$10',
88               values        => '=Sheet1!$B$2:$B$10',
89               name          => 'Series name',
90               name_formula  => '=Sheet1!$B$1',
91           );
92
93       The properties that can be set are:
94
95       •   "values"
96
97           This is the most important property of a series and must be set for
98           every chart object. It links the chart with the worksheet data that
99           it displays. Note the format that should be used for the formula.
100           See "Working with Cell Ranges".
101
102       •   "categories"
103
104           This sets the chart category labels. The category is more or less
105           the same as the X-axis. In most chart types the "categories"
106           property is optional and the chart will just assume a sequential
107           series from "1 .. n".
108
109       •   "name"
110
111           Set the name for the series. The name is displayed in the chart
112           legend and in the formula bar. The name property is optional and if
113           it isn't supplied will default to "Series 1 .. n".
114
115       •   "name_formula"
116
117           Optional, can be used to link the name to a worksheet cell. See
118           "Chart names and links".
119
120       You can add more than one series to a chart, in fact some chart types
121       such as "stock" require it. The series numbering and order in the final
122       chart is the same as the order in which that are added.
123
124           # Add the first series.
125           $chart->add_series(
126               categories => '=Sheet1!$A$2:$A$7',
127               values     => '=Sheet1!$B$2:$B$7',
128               name       => 'Test data series 1',
129           );
130
131           # Add another series. Category is the same but values are different.
132           $chart->add_series(
133               categories => '=Sheet1!$A$2:$A$7',
134               values     => '=Sheet1!$C$2:$C$7',
135               name       => 'Test data series 2',
136           );
137
138   set_x_axis()
139       The set_x_axis() method is used to set properties of the X axis.
140
141           $chart->set_x_axis( name => 'Sample length (m)' );
142
143       The properties that can be set are:
144
145       •   "name"
146
147           Set the name (title or caption) for the axis. The name is displayed
148           below the X axis. This property is optional. The default is to have
149           no axis name.
150
151       •   "name_formula"
152
153           Optional, can be used to link the name to a worksheet cell. See
154           "Chart names and links".
155
156       Additional axis properties such as range, divisions and ticks will be
157       made available in later releases. See the "TODO" section.
158
159   set_y_axis()
160       The set_y_axis() method is used to set properties of the Y axis.
161
162           $chart->set_y_axis( name => 'Sample weight (kg)' );
163
164       The properties that can be set are:
165
166       •   "name"
167
168           Set the name (title or caption) for the axis. The name is displayed
169           to the left of the Y axis. This property is optional. The default
170           is to have no axis name.
171
172       •   "name_formula"
173
174           Optional, can be used to link the name to a worksheet cell. See
175           "Chart names and links".
176
177       Additional axis properties such as range, divisions and ticks will be
178       made available in later releases. See the "TODO" section.
179
180   set_title()
181       The set_title() method is used to set properties of the chart title.
182
183           $chart->set_title( name => 'Year End Results' );
184
185       The properties that can be set are:
186
187       •   "name"
188
189           Set the name (title) for the chart. The name is displayed above the
190           chart. This property is optional. The default is to have no chart
191           title.
192
193       •   "name_formula"
194
195           Optional, can be used to link the name to a worksheet cell. See
196           "Chart names and links".
197
198   set_legend()
199       The set_legend() method is used to set properties of the chart legend.
200
201           $chart->set_legend( position => 'none' );
202
203       The properties that can be set are:
204
205       •   "position"
206
207           Set the position of the chart legend.
208
209               $chart->set_legend( position => 'none' );
210
211           The default legend position is "bottom". The currently supported
212           chart positions are:
213
214               none
215               bottom
216
217           The other legend positions will be added soon.
218
219   set_chartarea()
220       The set_chartarea() method is used to set the properties of the chart
221       area. In Excel the chart area is the background area behind the chart.
222
223       The properties that can be set are:
224
225       •   "color"
226
227           Set the colour of the chart area. The Excel default chart area
228           color is 'white', index 9. See "Chart object colours".
229
230       •   "line_color"
231
232           Set the colour of the chart area border line. The Excel default
233           border line colour is 'black', index 9.  See "Chart object
234           colours".
235
236       •   "line_pattern"
237
238           Set the pattern of the of the chart area border line. The Excel
239           default pattern is 'none', index 0 for a chart sheet and 'solid',
240           index 1, for an embedded chart. See "Chart line patterns".
241
242       •   "line_weight"
243
244           Set the weight of the of the chart area border line. The Excel
245           default weight is 'narrow', index 2. See "Chart line weights".
246
247       Here is an example of setting several properties:
248
249           $chart->set_chartarea(
250               color        => 'red',
251               line_color   => 'black',
252               line_pattern => 2,
253               line_weight  => 3,
254           );
255
256       Note, for chart sheets the chart area border is off by default. For
257       embedded charts this is on by default.
258
259   set_plotarea()
260       The set_plotarea() method is used to set properties of the plot area of
261       a chart. In Excel the plot area is the area between the axes on which
262       the chart series are plotted.
263
264       The properties that can be set are:
265
266       •   "visible"
267
268           Set the visibility of the plot area. The default is 1 for visible.
269           Set to 0 to hide the plot area and have the same colour as the
270           background chart area.
271
272       •   "color"
273
274           Set the colour of the plot area. The Excel default plot area color
275           is 'silver', index 23. See "Chart object colours".
276
277       •   "line_color"
278
279           Set the colour of the plot area border line. The Excel default
280           border line colour is 'gray', index 22. See "Chart object colours".
281
282       •   "line_pattern"
283
284           Set the pattern of the of the plot area border line. The Excel
285           default pattern is 'solid', index 1. See "Chart line patterns".
286
287       •   "line_weight"
288
289           Set the weight of the of the plot area border line. The Excel
290           default weight is 'narrow', index 2. See "Chart line weights".
291
292       Here is an example of setting several properties:
293
294           $chart->set_plotarea(
295               color        => 'red',
296               line_color   => 'black',
297               line_pattern => 2,
298               line_weight  => 3,
299           );
300

WORKSHEET METHODS

302       In Excel a chart sheet (i.e, a chart that isn't embedded) shares
303       properties with data worksheets such as tab selection, headers,
304       footers, margins and print properties.
305
306       In Spreadsheet::WriteExcel you can set chart sheet properties using the
307       same methods that are used for Worksheet objects.
308
309       The following Worksheet methods are also available through a non-
310       embedded Chart object:
311
312           get_name()
313           activate()
314           select()
315           hide()
316           set_first_sheet()
317           protect()
318           set_zoom()
319           set_tab_color()
320
321           set_landscape()
322           set_portrait()
323           set_paper()
324           set_margins()
325           set_header()
326           set_footer()
327
328       See Spreadsheet::WriteExcel for a detailed explanation of these
329       methods.
330

EXAMPLE

332       Here is a complete example that demonstrates some of the available
333       features when creating a chart.
334
335           #!/usr/bin/perl -w
336
337           use strict;
338           use Spreadsheet::WriteExcel;
339
340           my $workbook  = Spreadsheet::WriteExcel->new( 'chart_area.xls' );
341           my $worksheet = $workbook->add_worksheet();
342           my $bold      = $workbook->add_format( bold => 1 );
343
344           # Add the worksheet data that the charts will refer to.
345           my $headings = [ 'Number', 'Sample 1', 'Sample 2' ];
346           my $data = [
347               [ 2, 3, 4, 5, 6, 7 ],
348               [ 1, 4, 5, 2, 1, 5 ],
349               [ 3, 6, 7, 5, 4, 3 ],
350           ];
351
352           $worksheet->write( 'A1', $headings, $bold );
353           $worksheet->write( 'A2', $data );
354
355           # Create a new chart object. In this case an embedded chart.
356           my $chart = $workbook->add_chart( type => 'area', embedded => 1 );
357
358           # Configure the first series. (Sample 1)
359           $chart->add_series(
360               name       => 'Sample 1',
361               categories => '=Sheet1!$A$2:$A$7',
362               values     => '=Sheet1!$B$2:$B$7',
363           );
364
365           # Configure the second series. (Sample 2)
366           $chart->add_series(
367               name       => 'Sample 2',
368               categories => '=Sheet1!$A$2:$A$7',
369               values     => '=Sheet1!$C$2:$C$7',
370           );
371
372           # Add a chart title and some axis labels.
373           $chart->set_title ( name => 'Results of sample analysis' );
374           $chart->set_x_axis( name => 'Test number' );
375           $chart->set_y_axis( name => 'Sample length (cm)' );
376
377           # Insert the chart into the worksheet (with an offset).
378           $worksheet->insert_chart( 'D2', $chart, 25, 10 );
379
380           __END__
381

Chart object colours

383       Many of the chart objects supported by Spreadsheet::WriteExcl allow the
384       default colours to be changed. Excel provides a palette of 56 colours
385       and in Spreadsheet::WriteExcel these colours are accessed via their
386       palette index in the range 8..63.
387
388       The most commonly used colours can be accessed by name or index.
389
390           black   =>   8,    green    =>  17,    navy     =>  18,
391           white   =>   9,    orange   =>  53,    pink     =>  33,
392           red     =>  10,    gray     =>  23,    purple   =>  20,
393           blue    =>  12,    lime     =>  11,    silver   =>  22,
394           yellow  =>  13,    cyan     =>  15,
395           brown   =>  16,    magenta  =>  14,
396
397       For example the following are equivalent.
398
399           $chart->set_plotarea( color => 10    );
400           $chart->set_plotarea( color => 'red' );
401
402       The colour palette is shown in "palette.html" in the "docs" directory
403       of the distro. An Excel version of the palette can be generated using
404       "colors.pl" in the "examples" directory.
405
406       User defined colours can be set using the set_custom_color() workbook
407       method. This and other aspects of using colours are discussed in the
408       "Colours in Excel" section of the main Spreadsheet::WriteExcel
409       documentation:
410       <http://search.cpan.org/dist/Spreadsheet-WriteExcel/lib/Spreadsheet/WriteExcel.pm#COLOURS_IN_EXCEL>.
411

Chart line patterns

413       Chart lines patterns can be set using either an index or a name:
414
415           $chart->set_plotarea( weight => 2      );
416           $chart->set_plotarea( weight => 'dash' );
417
418       Chart lines have 9 possible patterns are follows:
419
420           'none'         => 0,
421           'solid'        => 1,
422           'dash'         => 2,
423           'dot'          => 3,
424           'dash-dot'     => 4,
425           'dash-dot-dot' => 5,
426           'medium-gray'  => 6,
427           'dark-gray'    => 7,
428           'light-gray'   => 8,
429
430       The patterns 1-8 are shown in order in the drop down dialog boxes in
431       Excel. The default pattern is 'solid', index 1.
432

Chart line weights

434       Chart lines weights can be set using either an index or a name:
435
436           $chart->set_plotarea( weight => 1          );
437           $chart->set_plotarea( weight => 'hairline' );
438
439       Chart lines have 4 possible weights are follows:
440
441           'hairline' => 1,
442           'narrow'   => 2,
443           'medium'   => 3,
444           'wide'     => 4,
445
446       The weights 1-4 are shown in order in the drop down dialog boxes in
447       Excel. The default weight is 'narrow', index 2.
448
450       The "add_series())", set_x_axis(), set_y_axis() and set_title() methods
451       all support a "name" property. In general these names can be either a
452       static string or a link to a worksheet cell. If you choose to use the
453       "name_formula" property to specify a link then you should also the
454       "name" property. This isn't strictly required by Excel but some third
455       party applications expect it to be present.
456
457           $chart->set_title(
458               name          => 'Year End Results',
459               name_formula  => '=Sheet1!$C$1',
460           );
461
462       These links should be used sparingly since they aren't commonly used in
463       Excel charts.
464

Chart names and Unicode

466       The "add_series())", set_x_axis(), set_y_axis() and set_title() methods
467       all support a "name" property. These names can be UTF8 strings if you
468       are using perl 5.8+.
469
470           # perl 5.8+ example:
471           my $smiley = "\x{263A}";
472
473           $chart->set_title( name => "Best. Results. Ever! $smiley" );
474
475       For older perls you write Unicode strings as UTF-16BE by adding a
476       "name_encoding" property:
477
478           # perl 5.005 example:
479           my $utf16be_name = pack 'n', 0x263A;
480
481           $chart->set_title(
482               name          => $utf16be_name,
483               name_encoding => 1,
484           );
485
486       This methodology is explained in the "UNICODE IN EXCEL" section of
487       Spreadsheet::WriteExcel but is semi-deprecated. If you are using
488       Unicode the easiest option is to just use UTF8 in perl 5.8+.
489

Working with Cell Ranges

491       In the section on add_series() it was noted that the series must be
492       defined using a range formula:
493
494           $chart->add_series( values => '=Sheet1!$B$2:$B$10' );
495
496       The worksheet name must be specified (even for embedded charts) and the
497       cell references must be "absolute" references, i.e., they must contain
498       "$" signs. This is the format that is required by Excel for chart
499       references.
500
501       Since it isn't very convenient to work with this type of string
502       programmatically the Spreadsheet::WriteExcel::Utility module, which is
503       included with Spreadsheet::WriteExcel, provides a function called
504       xl_range_formula() to convert from zero based row and column cell
505       references to an A1 style formula string.
506
507       The syntax is:
508
509           xl_range_formula($sheetname, $row_1, $row_2, $col_1, $col_2)
510
511       If you include it in your program, using the standard import syntax,
512       you can use the function as follows:
513
514           # Include the Utility module or just the function you need.
515           use Spreadsheet::WriteExcel::Utility qw( xl_range_formula );
516           ...
517
518           # Then use it as required.
519           $chart->add_series(
520               categories    => xl_range_formula( 'Sheet1', 1, 9, 0, 0 ),
521               values        => xl_range_formula( 'Sheet1', 1, 9, 1, 1 ),
522           );
523
524           # Which is the same as:
525           $chart->add_series(
526               categories    => '=Sheet1!$A$2:$A$10',
527               values        => '=Sheet1!$B$2:$B$10',
528           );
529
530       See Spreadsheet::WriteExcel::Utility for more details.
531

TODO

533       Charts in Spreadsheet::WriteExcel are a work in progress. More chart
534       types and features will be added in time. Please be patient. Even a
535       small feature can take a week or more to implement, test and document.
536
537       Features that are on the TODO list and will be added are:
538
539       •   Chart sub-types.
540
541       •   Colours and formatting options. For now you will have to make do
542           with the default Excel colours and formats.
543
544       •   Axis controls, gridlines.
545
546       •   3D charts.
547
548       •   Embedded data in charts for third party application support. See
549           Known Issues.
550
551       •   Additional chart types such as Bubble and Radar. Send an email if
552           you are interested in other types and they will be added to the
553           queue.
554
555       If you are interested in sponsoring a feature let me know.
556

KNOWN ISSUES

558       •   Currently charts don't contain embedded data from which the charts
559           can be rendered. Excel and most other third party applications
560           ignore this and read the data via the links that have been
561           specified. However, some applications may complain or not render
562           charts correctly. The preview option in Mac OS X is an known
563           example. This will be fixed in a later release.
564
565       •   When there are several charts with titles set in a workbook some of
566           the titles may display at a font size of 10 instead of the default
567           12 until another chart with the title set is viewed.
568
569       •   Stock (and other) charts should have the X-axis dates aligned at an
570           angle for clarity. This will be fixed at a later stage.
571

AUTHOR

573       John McNamara jmcnamara@cpan.org
574
576       Copyright MM-MMX, John McNamara.
577
578       All Rights Reserved. This module is free software. It may be used,
579       redistributed and/or modified under the same terms as Perl itself.
580
581
582
583perl v5.36.0                      2023-01-20 Spreadsheet::WriteExcel::Chart(3)
Impressum