3
###############################################################################
5
# This program contains helper functions to deal with the Excel A1 cell
8
# These functions have been superceded by Spreadsheet::WriteExcel::Utility.
10
# reverse('�'), March 2001, John McNamara, jmcnamara@cpan.org
16
print "Cell B7 is equivalent to (";
17
print join " ", cell_to_rowcol('B7');
18
print ") in row column notation.\n";
20
print "Cell \$B7 is equivalent to (";
21
print join " ", cell_to_rowcol('$B7');
22
print ") in row column notation.\n";
24
print "Cell B\$7 is equivalent to (";
25
print join " ", cell_to_rowcol('B$7');
26
print ") in row column notation.\n";
28
print "Cell \$B\$7 is equivalent to (";
29
print join " ", cell_to_rowcol('$B$7');
30
print ") in row column notation.\n\n";
32
print "Row and column (1999, 29) are equivalent to ";
33
print rowcol_to_cell(1999, 29), ".\n";
35
print "Row and column (1999, 29, 0, 1) are equivalent to ";
36
print rowcol_to_cell(1999, 29, 0, 1), ".\n\n";
38
print "The base cell is: Z7\n";
39
print "Increment the row: ", inc_cell_row('Z7'), "\n";
40
print "Decrement the row: ", dec_cell_row('Z7'), "\n";
41
print "Increment the column: ", inc_cell_col('Z7'), "\n";
42
print "Decrement the column: ", dec_cell_col('Z7'), "\n\n";
45
###############################################################################
47
# rowcol_to_cell($row, $col, $row_absolute, $col_absolute)
49
# Convert a zero based row and column reference to a A1 reference. For example
50
# (0, 2) to C1. $row_absolute, $col_absolute are optional. They are boolean
51
# values used to indicate if the row or column value is absolute, i.e. if it is
52
# prefixed by a $ sign: eg. (0, 2, 0, 1) converts to $C1.
54
# Returns: a cell reference string.
60
my $row_abs = $_[2] || 0;
61
my $col_abs = $_[3] || 0;
79
my $int = int ($col / 26);
80
my $frac = $col % 26 +1;
87
$chr2 = chr (ord('A') + $frac -1);;
91
$chr1 = chr (ord('A') + $int -1);
94
$row++; # Zero index to 1-index
96
return $col_abs . $chr1 . $chr2 . $row_abs. $row;
100
###############################################################################
102
# cell_to_rowcol($cell_ref)
104
# Convert an Excel cell reference in A1 notation to a zero based row and column
105
# reference; converts C1 to (0, 2, 0, 0).
107
# Returns: row, column, row_is_absolute, column_is_absolute
114
$cell =~ /(\$?)([A-I]?[A-Z])(\$?)(\d+)/;
116
my $col_abs = $1 eq "" ? 0 : 1;
118
my $row_abs = $3 eq "" ? 0 : 1;
121
# Convert base26 column string to number
122
# All your Base are belong to us.
123
my @chars = split //, $col;
128
my $char = pop(@chars); # LS char first
129
$col += (ord($char) -ord('A') +1) * (26**$expn);
133
# Convert 1-index to zero-index
137
return $row, $col, $row_abs, $col_abs;
141
###############################################################################
143
# inc_cell_row($cell_ref)
145
# Increments the row number of an Excel cell reference in A1 notation.
146
# For example C3 to C4
148
# Returns: a cell reference string.
153
my ($row, $col, $row_abs, $col_abs) = cell_to_rowcol($cell);
157
return rowcol_to_cell($row, $col, $row_abs, $col_abs);
161
###############################################################################
163
# dec_cell_row($cell_ref)
165
# Decrements the row number of an Excel cell reference in A1 notation.
166
# For example C4 to C3
168
# Returns: a cell reference string.
173
my ($row, $col, $row_abs, $col_abs) = cell_to_rowcol($cell);
177
return rowcol_to_cell($row, $col, $row_abs, $col_abs);
181
###############################################################################
183
# inc_cell_col($cell_ref)
185
# Increments the column number of an Excel cell reference in A1 notation.
186
# For example C3 to D3
188
# Returns: a cell reference string.
193
my ($row, $col, $row_abs, $col_abs) = cell_to_rowcol($cell);
197
return rowcol_to_cell($row, $col, $row_abs, $col_abs);
201
###############################################################################
203
# dec_cell_col($cell_ref)
205
# Decrements the column number of an Excel cell reference in A1 notation.
206
# For example D3 to C3
208
# Returns: a cell reference string.
213
my ($row, $col, $row_abs, $col_abs) = cell_to_rowcol($cell);
217
return rowcol_to_cell($row, $col, $row_abs, $col_abs);