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
84       is 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
200       legend.
201
202           $chart->set_legend( position => 'none' );
203
204       The properties that can be set are:
205
206       ·   "position"
207
208           Set the position of the chart legend.
209
210               $chart->set_legend( position => 'none' );
211
212           The default legend position is "bottom". The currently supported
213           chart positions are:
214
215               none
216               bottom
217
218           The other legend positions will be added soon.
219
220   set_chartarea()
221       The "set_chartarea()" method is used to set the properties of the chart
222       area. In Excel the chart area is the background area behind the chart.
223
224       The properties that can be set are:
225
226       ·   "color"
227
228           Set the colour of the chart area. The Excel default chart area
229           color is 'white', index 9. See "Chart object colours".
230
231       ·   "line_color"
232
233           Set the colour of the chart area border line. The Excel default
234           border line colour is 'black', index 9.  See "Chart object
235           colours".
236
237       ·   "line_pattern"
238
239           Set the pattern of the of the chart area border line. The Excel
240           default pattern is 'none', index 0 for a chart sheet and 'solid',
241           index 1, for an embedded chart. See "Chart line patterns".
242
243       ·   "line_weight"
244
245           Set the weight of the of the chart area border line. The Excel
246           default weight is 'narrow', index 2. See "Chart line weights".
247
248       Here is an example of setting several properties:
249
250           $chart->set_chartarea(
251               color        => 'red',
252               line_color   => 'black',
253               line_pattern => 2,
254               line_weight  => 3,
255           );
256
257       Note, for chart sheets the chart area border is off by default. For
258       embedded charts this is on by default.
259
260   set_plotarea()
261       The "set_plotarea()" method is used to set properties of the plot area
262       of a chart. In Excel the plot area is the area between the axes on
263       which the chart series are plotted.
264
265       The properties that can be set are:
266
267       ·   "visible"
268
269           Set the visibility of the plot area. The default is 1 for visible.
270           Set to 0 to hide the plot area and have the same colour as the
271           background chart area.
272
273       ·   "color"
274
275           Set the colour of the plot area. The Excel default plot area color
276           is 'silver', index 23. See "Chart object colours".
277
278       ·   "line_color"
279
280           Set the colour of the plot area border line. The Excel default
281           border line colour is 'gray', index 22. See "Chart object colours".
282
283       ·   "line_pattern"
284
285           Set the pattern of the of the plot area border line. The Excel
286           default pattern is 'solid', index 1. See "Chart line patterns".
287
288       ·   "line_weight"
289
290           Set the weight of the of the plot area border line. The Excel
291           default weight is 'narrow', index 2. See "Chart line weights".
292
293       Here is an example of setting several properties:
294
295           $chart->set_plotarea(
296               color        => 'red',
297               line_color   => 'black',
298               line_pattern => 2,
299               line_weight  => 3,
300           );
301

WORKSHEET METHODS

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

EXAMPLE

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

Chart object colours

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

Chart line patterns

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

Chart line weights

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

Chart names and Unicode

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

Working with Cell Ranges

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

TODO

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

KNOWN ISSUES

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

AUTHOR

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