1#!/usr/bin/perl -w
2
3###############################################################################
4#
5# Simple example of how to embed an externally created chart into a
6# Spreadsheet:: WriteExcel worksheet.
7#
8#
9# This example adds an "Open-high-low-close" stock chart extracted from the
10# file Chart3.xls as follows:
11#
12#   perl chartex.pl -c=demo4 Chart4.xls
13#
14# reverse('�'), September 2007, John McNamara, jmcnamara@cpan.org
15#
16
17use strict;
18use Spreadsheet::WriteExcel;
19
20my $workbook  = Spreadsheet::WriteExcel->new("demo4.xls");
21my $worksheet = $workbook->add_worksheet();
22
23
24# Add the chart extracted using the chartex utility
25$worksheet->embed_chart('G2', 'demo401.bin', 3, 3, 1.08, 1.21);
26
27# Link the chart to the worksheet data using a dummy formula.
28$worksheet->store_formula('=Sheet1!A1');
29
30
31# Add some extra formats to cover formats used in the charts.
32my $chart_font_1 = $workbook->add_format(font_only => 1);
33my $chart_font_2 = $workbook->add_format(font_only => 1, bold => 1);
34my $chart_font_3 = $workbook->add_format(font_only => 1);
35my $chart_font_4 = $workbook->add_format(font_only => 1);
36
37# Add all other formats.
38my $bold        = $workbook->add_format(bold => 1);
39my $date_format = $workbook->add_format(num_format => 'dd/mm/yyyy');
40
41
42# Adjust column widths and add some headers
43$worksheet->set_column('A:A', 12);
44
45$worksheet->write('A1', 'Date',  $bold);
46$worksheet->write('B1', 'Open',  $bold);
47$worksheet->write('C1', 'High',  $bold);
48$worksheet->write('D1', 'Low',   $bold);
49$worksheet->write('E1', 'Close', $bold);
50
51
52# Add data to range that the chart refers to.
53my @dates = (
54
55   "2004-08-19T",
56   "2004-08-20T",
57   "2004-08-23T",
58   "2004-08-24T",
59   "2004-08-25T",
60   "2004-08-26T",
61   "2004-08-27T",
62   "2004-08-30T",
63   "2004-08-31T",
64   "2004-09-01T",
65   "2004-09-02T",
66   "2004-09-03T",
67   "2004-09-07T",
68   "2004-09-08T",
69   "2004-09-09T",
70   "2004-09-10T",
71   "2004-09-13T",
72   "2004-09-14T",
73   "2004-09-15T",
74   "2004-09-16T",
75   "2004-09-17T",
76   "2004-09-20T",
77   "2004-09-21T",
78);
79
80# Open-High-Low-Close prices
81my @prices = (
82
83    [100.00, 104.06,  95.96, 100.34],
84    [101.01, 109.08, 100.50, 108.31],
85    [110.75, 113.48, 109.05, 109.40],
86    [111.24, 111.60, 103.57, 104.87],
87    [104.96, 108.00, 103.88, 106.00],
88    [104.95, 107.95, 104.66, 107.91],
89    [108.10, 108.62, 105.69, 106.15],
90    [105.28, 105.49, 102.01, 102.01],
91    [102.30, 103.71, 102.16, 102.37],
92    [102.70, 102.97,  99.67, 100.25],
93    [ 99.19, 102.37,  98.94, 101.51],
94    [100.95, 101.74,  99.32, 100.01],
95    [101.01, 102.00,  99.61, 101.58],
96    [100.74, 103.03, 100.50, 102.30],
97    [102.53, 102.71, 101.00, 102.31],
98    [101.60, 106.56, 101.30, 105.33],
99    [106.63, 108.41, 106.46, 107.50],
100    [107.45, 112.00, 106.79, 111.49],
101    [110.56, 114.23, 110.20, 112.00],
102    [112.34, 115.80, 111.65, 113.97],
103    [114.42, 117.49, 113.55, 117.49],
104    [116.95, 121.60, 116.77, 119.36],
105    [119.81, 120.42, 117.51, 117.84],
106);
107
108
109
110my $row = 1;
111$worksheet->write_date_time($row++, 0, $_, $date_format) for @dates;
112$worksheet->write_col('B2', \@prices);
113
114__END__
115