#!/usr/bin/perl -w ############################################################################### # # This is an example of how to extend the Spreadsheet::WriteExcel module. # # Code is appended to the Spreadsheet::WriteExcel::Worksheet module by reusing # the package name. The new code provides a write() method that allows you to # use Excels A1 style cell references. This is not particularly useful but it # serves as an example of how the module can be extended without modifying the # code directly. # # reverse('©'), March 2001, John McNamara, jmcnamara@cpan.org # use strict; use Spreadsheet::WriteExcel; # Create a new workbook called simple.xls and add a worksheet my $workbook = Spreadsheet::WriteExcel->new("writeA1.xls"); my $worksheet = $workbook->add_worksheet(); # Write numbers or text $worksheet->write (0, 0, "Hello"); $worksheet->writeA1("A3", "A3" ); $worksheet->writeA1("A5", 1.2345 ); ############################################################################### # # The following will be appended to the Spreadsheet::WriteExcel::Worksheet # package. # package Spreadsheet::WriteExcel::Worksheet; ############################################################################### # # writeA1($cell, $token, $format) # # Convert $cell from Excel A1 notation to $row, $col notation and # call write() on $token. # # Returns: return value of called subroutine or -4 for invalid cell # reference. # sub writeA1 { my $self = shift; my $cell = shift; my $col; my $row; if ($cell =~ /([A-z]+)(\d+)/) { ($row, $col) = _convertA1($2, $1); $self->write($row, $col, @_); } else { return -4; } } ############################################################################### # # _convertA1($row, $col) # # Convert Excel A1 notation to $row, $col notation. Convert base26 column # string to a number. # sub _convertA1 { my $row = $_[0]; my $col = $_[1]; # String in AA notation my @chars = split //, $col; my $expn = 0; $col = 0; while (@chars) { my $char = uc(pop(@chars)); # LS char first $col += (ord($char) -ord('A') +1) * (26**$expn); $expn++; } # Convert 1 index to 0 index $row--; $col--; return($row, $col); }