1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
|
#!/usr/bin/perl -w
###############################################################################
#
# Example of formatting using the Excel::Writer::XLSX module
#
# This is a simple example of how to use functions that reference cells in
# other worksheets within the same workbook.
#
# reverse ('(c)'), March 2001, John McNamara, jmcnamara@cpan.org
#
use strict;
use Excel::Writer::XLSX;
# Create a new workbook and add a worksheet
my $workbook = Excel::Writer::XLSX->new( 'stats_ext.xlsx' );
my $worksheet1 = $workbook->add_worksheet( 'Test results' );
my $worksheet2 = $workbook->add_worksheet( 'Data' );
# Set the column width for columns 1
$worksheet1->set_column( 'A:A', 20 );
# Create a format for the headings
my $heading = $workbook->add_format();
$heading->set_bold();
# Create a numerical format
my $numformat = $workbook->add_format();
$numformat->set_num_format( '0.00' );
# Write some statistical functions
$worksheet1->write( 'A1', 'Count', $heading );
$worksheet1->write( 'B1', '=COUNT(Data!B2:B9)' );
$worksheet1->write( 'A2', 'Sum', $heading );
$worksheet1->write( 'B2', '=SUM(Data!B2:B9)' );
$worksheet1->write( 'A3', 'Average', $heading );
$worksheet1->write( 'B3', '=AVERAGE(Data!B2:B9)' );
$worksheet1->write( 'A4', 'Min', $heading );
$worksheet1->write( 'B4', '=MIN(Data!B2:B9)' );
$worksheet1->write( 'A5', 'Max', $heading );
$worksheet1->write( 'B5', '=MAX(Data!B2:B9)' );
$worksheet1->write( 'A6', 'Standard Deviation', $heading );
$worksheet1->write( 'B6', '=STDEV(Data!B2:B9)' );
$worksheet1->write( 'A7', 'Kurtosis', $heading );
$worksheet1->write( 'B7', '=KURT(Data!B2:B9)' );
# Write the sample data
$worksheet2->write( 'A1', 'Sample', $heading );
$worksheet2->write( 'A2', 1 );
$worksheet2->write( 'A3', 2 );
$worksheet2->write( 'A4', 3 );
$worksheet2->write( 'A5', 4 );
$worksheet2->write( 'A6', 5 );
$worksheet2->write( 'A7', 6 );
$worksheet2->write( 'A8', 7 );
$worksheet2->write( 'A9', 8 );
$worksheet2->write( 'B1', 'Length', $heading );
$worksheet2->write( 'B2', 25.4, $numformat );
$worksheet2->write( 'B3', 25.4, $numformat );
$worksheet2->write( 'B4', 24.8, $numformat );
$worksheet2->write( 'B5', 25.0, $numformat );
$worksheet2->write( 'B6', 25.3, $numformat );
$worksheet2->write( 'B7', 24.9, $numformat );
$worksheet2->write( 'B8', 25.2, $numformat );
$worksheet2->write( 'B9', 24.8, $numformat );
|
