1Spreadsheet::WriteExcelU:s:eCrhaCrotn(t3r)ibuted Perl DoScpurmeeandtsahteieotn::WriteExcel::Chart(3)
2
3
4
6 Chart - A writer class for Excel Charts.
7
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
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
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
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
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
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
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
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
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
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
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
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
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.36.0 2022-07-22 Spreadsheet::WriteExcel::Chart(3)