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