1#!/usr/bin/perl
2
3#######################################################################
4#
5# A demo of an various Excel chart data label features that are available
6# via an Excel::Writer::XLSX chart.
7#
8# Copyright 2000-2021, John McNamara, jmcnamara@cpan.org
9#
10
11use strict;
12use warnings;
13use Excel::Writer::XLSX;
14
15my $workbook  = Excel::Writer::XLSX->new( 'chart_data_labels.xlsx' );
16my $worksheet = $workbook->add_worksheet();
17my $bold      = $workbook->add_format( bold => 1 );
18
19# Add the worksheet data that the charts will refer to.
20my $headings = [ 'Number', 'Data', 'Text' ];
21my $data = [
22    [ 2,  3,  4,  5,  6,  7 ],
23    [ 20, 10, 20, 30, 40, 30 ],
24    [ 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun' ],
25];
26
27$worksheet->write( 'A1', $headings, $bold );
28$worksheet->write( 'A2', $data );
29
30
31#######################################################################
32#
33# Example with standard data labels.
34#
35
36# Create a Column chart.
37my $chart1 = $workbook->add_chart( type => 'column', embedded => 1 );
38
39# Configure the data series and add the data labels.
40$chart1->add_series(
41    categories => '=Sheet1!$A$2:$A$7',
42    values     => '=Sheet1!$B$2:$B$7',
43    data_labels => { value => 1 },
44);
45
46# Add a chart title.
47$chart1->set_title( name => 'Chart with standard data labels' );
48
49# Turn off the chart legend.
50$chart1->set_legend( none => 1 );
51
52# Insert the chart into the worksheet (with an offset).
53$worksheet->insert_chart( 'D2', $chart1, { x_offset => 25, y_offset => 10 } );
54
55
56#######################################################################
57#
58# Example with value and category data labels.
59#
60
61# Create a Column chart.
62my $chart2 = $workbook->add_chart( type => 'column', embedded => 1 );
63
64# Configure the data series and add the data labels.
65$chart2->add_series(
66    categories => '=Sheet1!$A$2:$A$7',
67    values     => '=Sheet1!$B$2:$B$7',
68    data_labels => { value => 1, category => 1 },
69);
70
71# Add a chart title.
72$chart2->set_title( name => 'Category and Value data labels' );
73
74# Turn off the chart legend.
75$chart2->set_legend( none => 1 );
76
77# Insert the chart into the worksheet (with an offset).
78$worksheet->insert_chart( 'D18', $chart2, { x_offset => 25, y_offset => 10 } );
79
80
81#######################################################################
82#
83# Example with standard data labels with different font.
84#
85
86# Create a Column chart.
87my $chart3 = $workbook->add_chart( type => 'column', embedded => 1 );
88
89# Configure the data series and add the data labels.
90$chart3->add_series(
91    categories => '=Sheet1!$A$2:$A$7',
92    values     => '=Sheet1!$B$2:$B$7',
93    data_labels => { value => 1,
94                     font => { bold => 1,
95                               color => 'red',
96                               rotation => -30} },
97);
98
99# Add a chart title.
100$chart3->set_title( name => 'Data labels with user defined font' );
101
102# Turn off the chart legend.
103$chart3->set_legend( none => 1 );
104
105# Insert the chart into the worksheet (with an offset).
106$worksheet->insert_chart( 'D34', $chart3, { x_offset => 25, y_offset => 10 } );
107
108
109#######################################################################
110#
111# Example with standard data labels and formatting.
112#
113
114# Create a Column chart.
115my $chart4 = $workbook->add_chart( type => 'column', embedded => 1 );
116
117# Configure the data series and add the data labels.
118$chart4->add_series(
119    categories => '=Sheet1!$A$2:$A$7',
120    values     => '=Sheet1!$B$2:$B$7',
121    data_labels => { value  => 1,
122                     border => {color => 'red'},
123                     fill   => {color => 'yellow'} },
124);
125
126# Add a chart title.
127$chart4->set_title( name => 'Data labels with formatting' );
128
129# Turn off the chart legend.
130$chart4->set_legend( none => 1 );
131
132# Insert the chart into the worksheet (with an offset).
133$worksheet->insert_chart( 'D50', $chart4, { x_offset => 25, y_offset => 10 } );
134
135
136#######################################################################
137#
138# Example with custom string data labels.
139#
140
141# Create a Column chart.
142my $chart5 = $workbook->add_chart( type => 'column', embedded => 1 );
143
144# Some custom labels.
145my $custom_labels = [
146    { value => 'Amy' },
147    { value => 'Bea' },
148    { value => 'Eva' },
149    { value => 'Fay' },
150    { value => 'Liv' },
151    { value => 'Una' },
152];
153
154
155# Configure the data series and add the data labels.
156$chart5->add_series(
157    categories => '=Sheet1!$A$2:$A$7',
158    values     => '=Sheet1!$B$2:$B$7',
159    data_labels => { value => 1, custom => $custom_labels },
160);
161
162# Add a chart title.
163$chart5->set_title( name => 'Chart with custom string data labels' );
164
165# Turn off the chart legend.
166$chart5->set_legend( none => 1 );
167
168# Insert the chart into the worksheet (with an offset).
169$worksheet->insert_chart( 'D66', $chart5, { x_offset => 25, y_offset => 10 } );
170
171
172#######################################################################
173#
174# Example with custom data labels from cells.
175#
176
177# Create a Column chart.
178my $chart6 = $workbook->add_chart( type => 'column', embedded => 1 );
179
180# Some custom labels.
181$custom_labels = [
182    { value => '=Sheet1!$C$2' },
183    { value => '=Sheet1!$C$3' },
184    { value => '=Sheet1!$C$4' },
185    { value => '=Sheet1!$C$5' },
186    { value => '=Sheet1!$C$6' },
187    { value => '=Sheet1!$C$7' },
188];
189
190
191# Configure the data series and add the data labels.
192$chart6->add_series(
193    categories => '=Sheet1!$A$2:$A$7',
194    values     => '=Sheet1!$B$2:$B$7',
195    data_labels => { value => 1, custom => $custom_labels },
196);
197
198# Add a chart title.
199$chart6->set_title( name => 'Chart with custom data labels from cells' );
200
201# Turn off the chart legend.
202$chart6->set_legend( none => 1 );
203
204# Insert the chart into the worksheet (with an offset).
205$worksheet->insert_chart( 'D82', $chart6, { x_offset => 25, y_offset => 10 } );
206
207
208#######################################################################
209#
210# Example with custom and default data labels.
211#
212
213# Create a Column chart.
214my $chart7 = $workbook->add_chart( type => 'column', embedded => 1 );
215
216# Some custom labels. The undef items will get the default value.
217# We also set a font for the custom items as an extra example.
218$custom_labels = [
219    { value => '=Sheet1!$C$2', font => { color => 'red' } },
220    undef,
221    { value => '=Sheet1!$C$4', font => { color => 'red' } },
222    { value => '=Sheet1!$C$5', font => { color => 'red' } },
223];
224
225
226# Configure the data series and add the data labels.
227$chart7->add_series(
228    categories => '=Sheet1!$A$2:$A$7',
229    values     => '=Sheet1!$B$2:$B$7',
230    data_labels => { value => 1, custom => $custom_labels },
231);
232
233# Add a chart title.
234$chart7->set_title( name => 'Mixed custom and default data labels' );
235
236# Turn off the chart legend.
237$chart7->set_legend( none => 1 );
238
239# Insert the chart into the worksheet (with an offset).
240$worksheet->insert_chart( 'D98', $chart7, { x_offset => 25, y_offset => 10 } );
241
242
243#######################################################################
244#
245# Example with deleted custom data labels.
246#
247
248# Create a Column chart.
249my $chart8 = $workbook->add_chart( type => 'column', embedded => 1 );
250
251# Some deleted custom labels and defaults (undef). This allows us to
252# highlight certain values such as the minimum and maximum.
253$custom_labels = [
254    { delete => 1 },
255    undef,
256    { delete => 1 },
257    { delete => 1 },
258    undef,
259    { delete => 1 },
260];
261
262# Configure the data series and add the data labels.
263$chart8->add_series(
264    categories => '=Sheet1!$A$2:$A$7',
265    values     => '=Sheet1!$B$2:$B$7',
266    data_labels => { value => 1, custom => $custom_labels },
267);
268
269# Add a chart title.
270$chart8->set_title( name => 'Chart with deleted data labels' );
271
272# Turn off the chart legend.
273$chart8->set_legend( none => 1 );
274
275# Insert the chart into the worksheet (with an offset).
276$worksheet->insert_chart( 'D114', $chart8, { x_offset => 25, y_offset => 10 } );
277
278
279#######################################################################
280#
281# Example with custom string data labels and formatting.
282#
283
284# Create a Column chart.
285my $chart9 = $workbook->add_chart( type => 'column', embedded => 1 );
286
287# Some custom labels.
288$custom_labels = [
289    { value => 'Amy', border => {color => 'blue'} },
290    { value => 'Bea' },
291    { value => 'Eva' },
292    { value => 'Fay' },
293    { value => 'Liv' },
294    { value => 'Una', fill   => {color => 'green'} },
295];
296
297
298# Configure the data series and add the data labels.
299$chart9->add_series(
300    categories => '=Sheet1!$A$2:$A$7',
301    values     => '=Sheet1!$B$2:$B$7',
302    data_labels => { value => 1,
303                     custom => $custom_labels,
304                     border => {color => 'red'},
305                     fill   => {color => 'yellow'} },
306);
307
308# Add a chart title.
309$chart9->set_title( name => 'Chart with custom labels and formatting' );
310
311# Turn off the chart legend.
312$chart9->set_legend( none => 1 );
313
314# Insert the chart into the worksheet (with an offset).
315$worksheet->insert_chart( 'D130', $chart9, { x_offset => 25, y_offset => 10 } );
316
317
318$workbook->close();
319
320__END__
321