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
84 is 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
200 legend.
201 $chart->set_legend( position => 'none' );
203 The properties that can be set are:
205 · "position"
207 Set the position of the chart legend.
209 $chart->set_legend( position => 'none' );
211 The default legend position is "bottom". The currently supported
213 chart positions are:
214 none
216 bottom
217 The other legend positions will be added soon.
219 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 The properties that can be set are:
225 · "color"
227 Set the colour of the chart area. The Excel default chart area
229 color is 'white', index 9. See "Chart object colours".
230 · "line_color"
232 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 · "line_pattern"
238 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 · "line_weight"
244 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 Here is an example of setting several properties:
249 $chart->set_chartarea(
251 color => 'red',
252 line_color => 'black',
253 line_pattern => 2,
254 line_weight => 3,
255 );
256 Note, for chart sheets the chart area border is off by default. For
258 embedded charts this is on by default.
259 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 The properties that can be set are:
266 · "visible"
268 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 · "color"
274 Set the colour of the plot area. The Excel default plot area color
276 is 'silver', index 23. See "Chart object colours".
277 · "line_color"
279 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 · "line_pattern"
284 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 · "line_weight"
289 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 Here is an example of setting several properties:
294 $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 In Spreadsheet::WriteExcel you can set chart sheet properties using the
308 same methods that are used for Worksheet objects.
309 The following Worksheet methods are also available through a non-
311 embedded Chart object:
312 get_name()
314 activate()
315 select()
316 hide()
317 set_first_sheet()
318 protect()
319 set_zoom()
320 set_tab_color()
321 set_landscape()
323 set_portrait()
324 set_paper()
325 set_margins()
326 set_header()
327 set_footer()
328 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 #!/usr/bin/perl -w
337 use strict;
339 use Spreadsheet::WriteExcel;
340 my $workbook = Spreadsheet::WriteExcel->new( 'chart_area.xls' );
342 my $worksheet = $workbook->add_worksheet();
343 my $bold = $workbook->add_format( bold => 1 );
344 # 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 $worksheet->write( 'A1', $headings, $bold );
354 $worksheet->write( 'A2', $data );
355 # Create a new chart object. In this case an embedded chart.
357 my $chart = $workbook->add_chart( type => 'area', embedded => 1 );
358 # 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 # 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 # 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 # Insert the chart into the worksheet (with an offset).
379 $worksheet->insert_chart( 'D2', $chart, 25, 10 );
380 __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 The most commonly used colours can be accessed by name or index.
390 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 For example the following are equivalent.
399 $chart->set_plotarea( color => 10 );
401 $chart->set_plotarea( color => 'red' );
402 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 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 $chart->set_plotarea( weight => 2 );
417 $chart->set_plotarea( weight => 'dash' );
418 Chart lines have 9 possible patterns are follows:
420 '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 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 $chart->set_plotarea( weight => 1 );
438 $chart->set_plotarea( weight => 'hairline' );
439 Chart lines have 4 possible weights are follows:
441 'hairline' => 1,
443 'narrow' => 2,
444 'medium' => 3,
445 'wide' => 4,
446 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 $chart->set_title(
459 name => 'Year End Results',
460 name_formula => '=Sheet1!$C$1',
461 );
462 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 # perl 5.8+ example:
472 my $smiley = "\x{263A}";
473 $chart->set_title( name => "Best. Results. Ever! $smiley" );
475 For older perls you write Unicode strings as UTF-16BE by adding a
477 "name_encoding" property:
478 # perl 5.005 example:
480 my $utf16be_name = pack 'n', 0x263A;
481 $chart->set_title(
483 name => $utf16be_name,
484 name_encoding => 1,
485 );
486 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 $chart->add_series( values => '=Sheet1!$B$2:$B$10' );
496 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 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 The syntax is:
509 xl_range_formula($sheetname, $row_1, $row_2, $col_1, $col_2)
511 If you include it in your program, using the standard import syntax,
513 you can use the function as follows:
514 # Include the Utility module or just the function you need.
516 use Spreadsheet::WriteExcel::Utility qw( xl_range_formula );
517 ...
518 # 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 # 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 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 Features that are on the TODO list and will be added are:
539 · Chart sub-types.
541 · Colours and formatting options. For now you will have to make do
543 with the default Excel colours and formats.
544 · Axis controls, gridlines.
546 · 3D charts.
548 · Embedded data in charts for third party application support. See
550 Known Issues.
551 · 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 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 · 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 · 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 All Rights Reserved. This module is free software. It may be used,
580 redistributed and/or modified under the same terms as Perl itself.
581perl v5.30.1 2020-01-30 Spreadsheet::WriteExcel::Chart(3)