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        (required)
96           categories    (optional for most chart types)
97           name          (optional)
98           name_formula  (optional)
99
100       ·   "values"
101
102           This is the most important property of a series and must be set for
103           every chart object. It links the chart with the worksheet data that
104           it displays.
105
106               $chart->add_series( values => '=Sheet1!$B$2:$B$10' );
107
108           Note the format that should be used for the formula. The worksheet
109           name must be specified (even for embedded charts) and the cell
110           references must be "absolute" references, i.e., they must contain
111           "$" signs. This is the format that is required by Excel for chart
112           references. You must also add the worksheet that you are referring
113           to before you link to it, via the workbook "add_worksheet()"
114           method. See also "Working with Cell Ranges".
115
116       ·   "categories"
117
118           This sets the chart category labels. The category is more or less
119           the same as the X-axis. In most chart types the "categories"
120           property is optional and the chart will just assume a sequential
121           series from "1 .. n".
122
123               $chart->add_series(
124                   categories    => '=Sheet1!$A$2:$A$10',
125                   values        => '=Sheet1!$B$2:$B$10',
126               );
127
128       ·   "name"
129
130           Set the name for the series. The name is displayed in the chart
131           legend and in the formula bar. The name property is optional and if
132           it isn't supplied will default to "Series 1 .. n".
133
134               $chart->add_series(
135                   ...
136                   name          => 'Series name',
137               );
138
139       ·   "name_formula"
140
141           Optional, can be used to link the name to a worksheet cell. See
142           "Chart names and links".
143
144               $chart->add_series(
145                   ...
146                   name          => 'Series name',
147                   name_formula  => '=Sheet1!$B$1',
148               );
149
150       You can add more than one series to a chart, in fact some chart types
151       such as "stock" require it. The series numbering and order in the final
152       chart is the same as the order in which that are added.
153
154           # Add the first series.
155           $chart->add_series(
156               categories => '=Sheet1!$A$2:$A$7',
157               values     => '=Sheet1!$B$2:$B$7',
158               name       => 'Test data series 1',
159           );
160
161           # Add another series. Category is the same but values are different.
162           $chart->add_series(
163               categories => '=Sheet1!$A$2:$A$7',
164               values     => '=Sheet1!$C$2:$C$7',
165               name       => 'Test data series 2',
166           );
167
168   set_x_axis()
169       The "set_x_axis()" method is used to set properties of the X axis.
170
171           $chart->set_x_axis( name => 'Sample length (m)' );
172
173       The properties that can be set are:
174
175           name          (optional)
176           name_formula  (optional)
177
178       ·   "name"
179
180           Set the name (title or caption) for the axis. The name is displayed
181           below the X axis. This property is optional. The default is to have
182           no axis name.
183
184               $chart->set_x_axis( name => 'Sample length (m)' );
185
186       ·   "name_formula"
187
188           Optional, can be used to link the name to a worksheet cell. See
189           "Chart names and links".
190
191               $chart->set_x_axis(
192                   name          => 'Sample length (m)',
193                   name_formula  => '=Sheet1!$A$1',
194               );
195
196       Additional axis properties such as range, divisions and ticks will be
197       made available in later releases. See the "TODO" section.
198
199   set_y_axis()
200       The "set_y_axis()" method is used to set properties of the Y axis.
201
202           $chart->set_y_axis( name => 'Sample weight (kg)' );
203
204       The properties that can be set are:
205
206           name          (optional)
207           name_formula  (optional)
208
209       ·   "name"
210
211           Set the name (title or caption) for the axis. The name is displayed
212           to the left of the Y axis. This property is optional. The default
213           is to have no axis name.
214
215               $chart->set_y_axis( name => 'Sample weight (kg)' );
216
217       ·   "name_formula"
218
219           Optional, can be used to link the name to a worksheet cell. See
220           "Chart names and links".
221
222               $chart->set_y_axis(
223                   name          => 'Sample weight (kg)',
224                   name_formula  => '=Sheet1!$B$1',
225               );
226
227       Additional axis properties such as range, divisions and ticks will be
228       made available in later releases. See the "TODO" section.
229
230   set_title()
231       The "set_title()" method is used to set properties of the chart title.
232
233           $chart->set_title( name => 'Year End Results' );
234
235       The properties that can be set are:
236
237           name          (optional)
238           name_formula  (optional)
239
240       ·   "name"
241
242           Set the name (title) for the chart. The name is displayed above the
243           chart. This property is optional. The default is to have no chart
244           title.
245
246               $chart->set_title( name => 'Year End Results' );
247
248       ·   "name_formula"
249
250           Optional, can be used to link the name to a worksheet cell. See
251           "Chart names and links".
252
253               $chart->set_title(
254                   name          => 'Year End Results',
255                   name_formula  => '=Sheet1!$C$1',
256               );
257
258   set_legend()
259       The "set_legend()" method is used to set properties of the chart
260       legend.
261
262           $chart->set_legend( position => 'none' );
263
264       The properties that can be set are:
265
266           position      (optional)
267
268       ·   "position"
269
270           Set the position of the chart legend.
271
272               $chart->set_legend( position => 'none' );
273
274           The default legend position is "bottom". The currently supported
275           chart positions are:
276
277               none
278               bottom
279
280           The other legend positions will be added soon.
281

WORKSHEET METHODS

283       In Excel a chart sheet (i.e, a chart that isn't embedded) shares
284       properties with data worksheets such as tab selection, headers,
285       footers, margins and print properties.
286
287       In Spreadsheet::WriteExcel you can set chart sheet properties using the
288       same methods that are used for Worksheet objects.
289
290       The following Worksheet methods are also available through a non-
291       embedded Chart object:
292
293           get_name()
294           activate()
295           select()
296           hide()
297           set_first_sheet()
298           protect()
299           set_zoom()
300           set_tab_color()
301
302           set_landscape()
303           set_portrait()
304           set_paper()
305           set_margins()
306           set_header()
307           set_footer()
308
309       See Spreadsheet::WriteExcel for a detailed explanation of these
310       methods.
311

EXAMPLE

313       Here is a complete example that demonstrates most of the available
314       features when creating a chart.
315
316           #!/usr/bin/perl -w
317
318           use strict;
319           use Spreadsheet::WriteExcel;
320
321           my $workbook  = Spreadsheet::WriteExcel->new( 'chart_area.xls' );
322           my $worksheet = $workbook->add_worksheet();
323           my $bold      = $workbook->add_format( bold => 1 );
324
325           # Add the worksheet data that the charts will refer to.
326           my $headings = [ 'Number', 'Sample 1', 'Sample 2' ];
327           my $data = [
328               [ 2, 3, 4, 5, 6, 7 ],
329               [ 1, 4, 5, 2, 1, 5 ],
330               [ 3, 6, 7, 5, 4, 3 ],
331           ];
332
333           $worksheet->write( 'A1', $headings, $bold );
334           $worksheet->write( 'A2', $data );
335
336           # Create a new chart object. In this case an embedded chart.
337           my $chart = $workbook->add_chart( type => 'area', embedded => 1 );
338
339           # Configure the first series. (Sample 1)
340           $chart->add_series(
341               name       => 'Sample 1',
342               categories => '=Sheet1!$A$2:$A$7',
343               values     => '=Sheet1!$B$2:$B$7',
344           );
345
346           # Configure the second series. (Sample 2)
347           $chart->add_series(
348               name       => 'Sample 2',
349               categories => '=Sheet1!$A$2:$A$7',
350               values     => '=Sheet1!$C$2:$C$7',
351           );
352
353           # Add a chart title and some axis labels.
354           $chart->set_title ( name => 'Results of sample analysis' );
355           $chart->set_x_axis( name => 'Test number' );
356           $chart->set_y_axis( name => 'Sample length (cm)' );
357
358           # Insert the chart into the worksheet (with an offset).
359           $worksheet->insert_chart( 'D2', $chart, 25, 10 );
360
361           __END__
362

364       The add_series()), "set_x_axis()", "set_y_axis()" and "set_title()"
365       methods all support a "name" property. In general these names can be
366       either a static string or a link to a worksheet cell. If you choose to
367       use the "name_formula" property to specify a link then you should also
368       the "name" property. This isn't strictly required by Excel but some
369       third party applications expect it to be present.
370
371           $chart->set_title(
372               name          => 'Year End Results',
373               name_formula  => '=Sheet1!$C$1',
374           );
375
376       These links should be used sparingly since they aren't commonly used in
377       Excel charts.
378

Chart names and Unicode

380       The add_series()), "set_x_axis()", "set_y_axis()" and "set_title()"
381       methods all support a "name" property. These names can be UTF8 strings
382       if you are using perl 5.8+.
383
384           # perl 5.8+ example:
385           my $smiley = "\x{263A}";
386
387           $chart->set_title( name => "Best. Results. Ever! $smiley" );
388
389       For older perls you write Unicode strings as UTF-16BE by adding a
390       "name_encoding" property:
391
392           # perl 5.005 example:
393           my $utf16be_name = pack 'n', 0x263A;
394
395           $chart->set_title(
396               name          => $utf16be_name,
397               name_encoding => 1,
398           );
399
400       This methodology is explained in the "UNICODE IN EXCEL" section of
401       Spreadsheet::WriteExcel but is semi-deprecated. If you are using
402       Unicode the easiest option is to just use UTF8 in perl 5.8+.
403

Working with Cell Ranges

405       In the section on "add_series()" it was noted that the series must be
406       defined using a range formula:
407
408           $chart->add_series( values => '=Sheet1!$B$2:$B$10' );
409
410       The worksheet name must be specified (even for embedded charts) and the
411       cell references must be "absolute" references, i.e., they must contain
412       "$" signs. This is the format that is required by Excel for chart
413       references.
414
415       Since it isn't very convenient to work with this type of string
416       programmatically the Spreadsheet::WriteExcel::Utility module, which is
417       included with Spreadsheet::WriteExcel, provides a function called
418       "xl_range_formula()" to convert from zero based row and column cell
419       references to an A1 style formula string.
420
421       The syntax is:
422
423           xl_range_formula($sheetname, $row_1, $row_2, $col_1, $col_2)
424
425       If you include it in your program, using the standard import syntax,
426       you can use the function as follows:
427
428           # Include the Utility module or just the function you need.
429           use Spreadsheet::WriteExcel::Utility qw( xl_range_formula );
430           ...
431
432           # Then use it as required.
433           $chart->add_series(
434               categories    => xl_range_formula( 'Sheet1', 1, 9, 0, 0 ),
435               values        => xl_range_formula( 'Sheet1', 1, 9, 1, 1 );,
436           );
437
438           # Which is the same as:
439           $chart->add_series(
440               categories    => '=Sheet1!$A$2:$A$10',
441               values        => '=Sheet1!$B$2:$B$10',
442           );
443
444       See Spreadsheet::WriteExcel::Utility for more details.
445

TODO

447       Charts in Spreadsheet::WriteExcel are a work in progress. More chart
448       types and features will be added in time. Please be patient. Even a
449       small feature can take a week or more to implement, test and document.
450
451       Features that are on the TODO list and will be added are:
452
453       ·   Chart sub-types.
454
455       ·   Colours and formatting options. For now you will have to make do
456           with the default Excel colours and formats.
457
458       ·   Axis controls, gridlines.
459
460       ·   3D charts.
461
462       ·   Embedded data in charts for third party application support. See
463           Known Issues.
464
465       ·   Additional chart types such as Bubble and Radar. Send an email if
466           you are interested in other types and they will be added to the
467           queue.
468
469       If you are interested in sponsoring a feature let me know.
470

KNOWN ISSUES

472       ·   Currently charts don't contain embedded data from which the charts
473           can be rendered. Excel and most other third party applications
474           ignore this and read the data via the links that have been
475           specified. However, some applications may complain or not render
476           charts correctly. The preview option in Mac OS X is an known
477           example. This will be fixed in a later release.
478
479       ·   When there are several charts with titles set in a workbook some of
480           the titles may display at a font size of 10 instead of the default
481           12 until another chart with the title set is viewed.
482
483       ·   Stock (and other) charts should have the X-axis dates aligned at an
484           angle for clarity. This will be fixed at a later stage.
485

AUTHOR

487       John McNamara jmcnamara@cpan.org
488

490       Copyright MM-MMX, John McNamara.
491
492       All Rights Reserved. This module is free software. It may be used,
493       redistributed and/or modified under the same terms as Perl itself.
494
495
496
497perl v5.12.0                      2010-01-21 Spreadsheet::WriteExcel::Chart(3)
Impressum