1#!/usr/local/bin/perl -w
2
3###############################################################################
4#
5# Example of a sales worksheet to demonstrate several different features.
6# Also uses functions from the L<Spreadsheet::WriteExcel::Utility> module.
7#
8# reverse('�'), October 2001, John McNamara, jmcnamara@cpan.org
9#
10
11use strict;
12use Spreadsheet::WriteExcel;
13use Spreadsheet::WriteExcel::Utility;
14
15# Create a new workbook and add a worksheet
16my $workbook        = Spreadsheet::WriteExcel->new("sales.xls");
17my $worksheet       = $workbook->add_worksheet('May Sales');
18
19
20# Set up some formats
21my %heading         =   (
22                            bold        => 1,
23                            pattern     => 1,
24                            fg_color    => 19,
25                            border      => 1,
26                            align       => 'center',
27                        );
28
29my %total           =   (
30                        bold        => 1,
31                        top         => 1,
32                        num_format  => '$#,##0.00'
33                        );
34
35my $heading         = $workbook->add_format(%heading);
36my $total_format    = $workbook->add_format(%total);
37my $price_format    = $workbook->add_format(num_format => '$#,##0.00');
38my $date_format     = $workbook->add_format(num_format => 'mmm d yyy');
39
40
41# Write the main headings
42$worksheet->freeze_panes(1); # Freeze the first row
43$worksheet->write('A1', 'Item',     $heading);
44$worksheet->write('B1', 'Quantity', $heading);
45$worksheet->write('C1', 'Price',    $heading);
46$worksheet->write('D1', 'Total',    $heading);
47$worksheet->write('E1', 'Date',     $heading);
48
49# Set the column widths
50$worksheet->set_column('A:A', 25);
51$worksheet->set_column('B:B', 10);
52$worksheet->set_column('C:E', 16);
53
54
55# Extract the sales data from the __DATA__ section at the end of the file.
56# In reality this information would probably come from a database
57my @sales;
58
59foreach my $line (<DATA>) {
60    chomp $line;
61    next if $line eq '';
62    # Simple-minded processing of CSV data. Refer to the Text::CSV_XS
63    # and Text::xSV modules for a more complete CSV handling.
64    my @items = split /,/, $line;
65    push @sales, \@items;
66}
67
68
69# Write out the items from each row
70my $row = 1;
71foreach my $sale (@sales) {
72
73    $worksheet->write($row, 0, @$sale[0]);
74    $worksheet->write($row, 1, @$sale[1]);
75    $worksheet->write($row, 2, @$sale[2], $price_format);
76
77    # Create a formula like '=B2*C2'
78    my $formula =   '='
79                    . xl_rowcol_to_cell($row, 1)
80                    . "*"
81                    . xl_rowcol_to_cell($row, 2);
82
83    $worksheet->write($row, 3, $formula, $price_format);
84
85    # Parse the date
86    my $date = xl_decode_date_US(@$sale[3]);
87    $worksheet->write($row, 4, $date, $date_format);
88    $row++;
89}
90
91# Create a formula to sum the totals, like '=SUM(D2:D6)'
92my $total = '=SUM(D2:'
93            . xl_rowcol_to_cell($row-1, 3)
94            . ")";
95
96$worksheet->write($row, 3, $total, $total_format);
97
98
99
100__DATA__
101586 card,20,125.50,5/12/01
102Flat Screen Monitor,1,1300.00,5/12/01
10364 MB dimms,45,49.99,5/13/01
10415 GB HD,12,300.00,5/13/01
105Speakers (pair),5,15.50,5/14/01
106
107