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