Viewing file:  writeA1.pl (2.23 KB) -rw-r--r--
Select action/file-type:
 (+) |  (+) |  (+) | Code (+) | Session (+) |  (+) | SDB (+) |  (+) |  (+) |  (+) |  (+) |  (+) |
#!/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);
}
|