1#!/usr/local/bin/perl -w 2 3############################################################################### 4# 5# Example of how to add a user defined data handler to the Spreadsheet:: 6# WriteExcel write() method. 7# 8# The following example shows how to add a handler for a 7 digit ID number. 9# It adds an additional constraint to the write_handler1.pl in that it only 10# filters data that isn't in the third column. 11# 12# 13# reverse('�'), September 2004, John McNamara, jmcnamara@cpan.org 14# 15 16use strict; 17use Spreadsheet::WriteExcel; 18 19 20my $workbook = Spreadsheet::WriteExcel->new("write_handler2.xls"); 21my $worksheet = $workbook->add_worksheet(); 22 23 24############################################################################### 25# 26# Add a handler for 7 digit id numbers. This is useful when you want a string 27# such as 0000001 written as a string instead of a number and thus preserve 28# the leading zeroes. 29# 30# Note: you can get the same effect using the keep_leading_zeros() method but 31# this serves as a simple example. 32# 33$worksheet->add_write_handler(qr[^\d{7}$], \&write_my_id); 34 35 36############################################################################### 37# 38# The following function processes the data when a match is found. The handler 39# is set up so that it only filters data if it is in the third column. 40# 41sub write_my_id { 42 43 my $worksheet = shift; 44 my $col = $_[1]; 45 46 # col is zero based 47 if ($col != 2) { 48 return $worksheet->write_string(@_); 49 } 50 else { 51 # Reject the match and return control to write() 52 return undef; 53 } 54 55} 56 57 58# This format maintains the cell as text even if it is edited. 59my $id_format = $workbook->add_format(num_format => '@'); 60 61 62# Write some numbers in the user defined format 63$worksheet->write('A1', '0000000', $id_format); 64$worksheet->write('B1', '0000001', $id_format); 65$worksheet->write('C1', '0000002', $id_format); 66$worksheet->write('D1', '0000003', $id_format); 67 68 69 70__END__ 71 72