1Spreadsheet::WriteExcelU:s:eCrhaCrotn(t3r)ibuted Perl DoScpurmeeandtsahteieotn::WriteExcel::Chart(3)
2
6 Chart - A writer class for Excel Charts.
7
9 To create a simple Excel file with a chart using
10 Spreadsheet::WriteExcel:
11 #!/usr/bin/perl -w
13 use strict;
15 use Spreadsheet::WriteExcel;
16 my $workbook = Spreadsheet::WriteExcel->new( 'chart.xls' );
18 my $worksheet = $workbook->add_worksheet();
19 my $chart = $workbook->add_chart( type => 'column' );
21 # Configure the chart.
23 $chart->add_series(
24 categories => '=Sheet1!$A$2:$A$7',
25 values => '=Sheet1!$B$2:$B$7',
26 );
27 # 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 $worksheet->write( 'A1', $data );
35 __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 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 my $chart = $workbook->add_chart( type => 'column' );
47 Currently the supported chart types are:
49 • "area": Creates an Area (filled line) style chart. See
51 Spreadsheet::WriteExcel::Chart::Area.
52 • "bar": Creates a Bar style (transposed histogram) chart. See
54 Spreadsheet::WriteExcel::Chart::Bar.
55 • "column": Creates a column style (histogram) chart. See
57 Spreadsheet::WriteExcel::Chart::Column.
58 • "line": Creates a Line style chart. See
60 Spreadsheet::WriteExcel::Chart::Line.
61 • "pie": Creates an Pie style chart. See
63 Spreadsheet::WriteExcel::Chart::Pie.
64 • "scatter": Creates an Scatter style chart. See
66 Spreadsheet::WriteExcel::Chart::Scatter.
67 • "stock": Creates an Stock style chart. See
69 Spreadsheet::WriteExcel::Chart::Stock.
70 More charts and sub-types will be supported in time. See the "TODO"
72 section.
73 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 With a Spreadsheet::WriteExcel chart object the add_series() method is
84 used to set the properties for a series:
85 $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 The properties that can be set are:
94 • "values"
96 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 • "categories"
103 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 • "name"
110 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 • "name_formula"
116 Optional, can be used to link the name to a worksheet cell. See
118 "Chart names and links".
119 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 # 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 # 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 set_x_axis()
139 The set_x_axis() method is used to set properties of the X axis.
140 $chart->set_x_axis( name => 'Sample length (m)' );
142 The properties that can be set are:
144 • "name"
146 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 • "name_formula"
152 Optional, can be used to link the name to a worksheet cell. See
154 "Chart names and links".
155 Additional axis properties such as range, divisions and ticks will be
157 made available in later releases. See the "TODO" section.
158 set_y_axis()
160 The set_y_axis() method is used to set properties of the Y axis.
161 $chart->set_y_axis( name => 'Sample weight (kg)' );
163 The properties that can be set are:
165 • "name"
167 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 • "name_formula"
173 Optional, can be used to link the name to a worksheet cell. See
175 "Chart names and links".
176 Additional axis properties such as range, divisions and ticks will be
178 made available in later releases. See the "TODO" section.
179 set_title()
181 The set_title() method is used to set properties of the chart title.
182 $chart->set_title( name => 'Year End Results' );
184 The properties that can be set are:
186 • "name"
188 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 • "name_formula"
194 Optional, can be used to link the name to a worksheet cell. See
196 "Chart names and links".
197 set_legend()
199 The set_legend() method is used to set properties of the chart legend.
200 $chart->set_legend( position => 'none' );
202 The properties that can be set are:
204 • "position"
206 Set the position of the chart legend.
208 $chart->set_legend( position => 'none' );
210 The default legend position is "bottom". The currently supported
212 chart positions are:
213 none
215 bottom
216 The other legend positions will be added soon.
218 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 The properties that can be set are:
224 • "color"
226 Set the colour of the chart area. The Excel default chart area
228 color is 'white', index 9. See "Chart object colours".
229 • "line_color"
231 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 • "line_pattern"
237 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 • "line_weight"
243 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 Here is an example of setting several properties:
248 $chart->set_chartarea(
250 color => 'red',
251 line_color => 'black',
252 line_pattern => 2,
253 line_weight => 3,
254 );
255 Note, for chart sheets the chart area border is off by default. For
257 embedded charts this is on by default.
258 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 The properties that can be set are:
265 • "visible"
267 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 • "color"
273 Set the colour of the plot area. The Excel default plot area color
275 is 'silver', index 23. See "Chart object colours".
276 • "line_color"
278 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 • "line_pattern"
283 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 • "line_weight"
288 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 Here is an example of setting several properties:
293 $chart->set_plotarea(
295 color => 'red',
296 line_color => 'black',
297 line_pattern => 2,
298 line_weight => 3,
299 );
300
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 In Spreadsheet::WriteExcel you can set chart sheet properties using the
307 same methods that are used for Worksheet objects.
308 The following Worksheet methods are also available through a non-
310 embedded Chart object:
311 get_name()
313 activate()
314 select()
315 hide()
316 set_first_sheet()
317 protect()
318 set_zoom()
319 set_tab_color()
320 set_landscape()
322 set_portrait()
323 set_paper()
324 set_margins()
325 set_header()
326 set_footer()
327 See Spreadsheet::WriteExcel for a detailed explanation of these
329 methods.
330
332 Here is a complete example that demonstrates some of the available
333 features when creating a chart.
334 #!/usr/bin/perl -w
336 use strict;
338 use Spreadsheet::WriteExcel;
339 my $workbook = Spreadsheet::WriteExcel->new( 'chart_area.xls' );
341 my $worksheet = $workbook->add_worksheet();
342 my $bold = $workbook->add_format( bold => 1 );
343 # 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 $worksheet->write( 'A1', $headings, $bold );
353 $worksheet->write( 'A2', $data );
354 # Create a new chart object. In this case an embedded chart.
356 my $chart = $workbook->add_chart( type => 'area', embedded => 1 );
357 # 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 # 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 # 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 # Insert the chart into the worksheet (with an offset).
378 $worksheet->insert_chart( 'D2', $chart, 25, 10 );
379 __END__
381
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 The most commonly used colours can be accessed by name or index.
389 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 For example the following are equivalent.
398 $chart->set_plotarea( color => 10 );
400 $chart->set_plotarea( color => 'red' );
401 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 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
413 Chart lines patterns can be set using either an index or a name:
414 $chart->set_plotarea( weight => 2 );
416 $chart->set_plotarea( weight => 'dash' );
417 Chart lines have 9 possible patterns are follows:
419 '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 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
434 Chart lines weights can be set using either an index or a name:
435 $chart->set_plotarea( weight => 1 );
437 $chart->set_plotarea( weight => 'hairline' );
438 Chart lines have 4 possible weights are follows:
440 'hairline' => 1,
442 'narrow' => 2,
443 'medium' => 3,
444 'wide' => 4,
445 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 $chart->set_title(
458 name => 'Year End Results',
459 name_formula => '=Sheet1!$C$1',
460 );
461 These links should be used sparingly since they aren't commonly used in
463 Excel charts.
464
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 # perl 5.8+ example:
471 my $smiley = "\x{263A}";
472 $chart->set_title( name => "Best. Results. Ever! $smiley" );
474 For older perls you write Unicode strings as UTF-16BE by adding a
476 "name_encoding" property:
477 # perl 5.005 example:
479 my $utf16be_name = pack 'n', 0x263A;
480 $chart->set_title(
482 name => $utf16be_name,
483 name_encoding => 1,
484 );
485 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
491 In the section on add_series() it was noted that the series must be
492 defined using a range formula:
493 $chart->add_series( values => '=Sheet1!$B$2:$B$10' );
495 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 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 The syntax is:
508 xl_range_formula($sheetname, $row_1, $row_2, $col_1, $col_2)
510 If you include it in your program, using the standard import syntax,
512 you can use the function as follows:
513 # Include the Utility module or just the function you need.
515 use Spreadsheet::WriteExcel::Utility qw( xl_range_formula );
516 ...
517 # 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 # 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 See Spreadsheet::WriteExcel::Utility for more details.
531
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 Features that are on the TODO list and will be added are:
538 • Chart sub-types.
540 • Colours and formatting options. For now you will have to make do
542 with the default Excel colours and formats.
543 • Axis controls, gridlines.
545 • 3D charts.
547 • Embedded data in charts for third party application support. See
549 Known Issues.
550 • 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 If you are interested in sponsoring a feature let me know.
556
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 • 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 • 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
573 John McNamara jmcnamara@cpan.org
574
576 Copyright MM-MMX, John McNamara.
577 All Rights Reserved. This module is free software. It may be used,
579 redistributed and/or modified under the same terms as Perl itself.
580perl v5.36.0 2023-01-20 Spreadsheet::WriteExcel::Chart(3)