1 ## Copyright (C) 2009,2010,2011 Philip Nienhuis <pr.nienhuis@users.sf.net>
3 ## This program is free software; you can redistribute it and/or modify it under
4 ## the terms of the GNU General Public License as published by the Free Software
5 ## Foundation; either version 3 of the License, or (at your option) any later
8 ## This program is distributed in the hope that it will be useful, but WITHOUT
9 ## ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
10 ## FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
13 ## You should have received a copy of the GNU General Public License along with
14 ## this program; if not, see <http://www.gnu.org/licenses/>.
16 ## calccelladdress (R, C) - compute spreadsheet style cell address from
17 ## row & column index (both 1-based).
19 ## Max column index currently set to 18278 (max ODS: 1024, OOXML: 16384).
20 ## Row limits for ODF and OOXML are 65536 and 1048576, resp.
22 ## Author: Philip Nienhuis <prnienhuis at users.sf.net>
23 ## Created: 2009-12-12
25 ## 2009-12-27 Fixed OOXML limits
26 ## 2010-03-17 Simplified argument list, only row + column needed
27 ## 2010-09-27 Made error message more comprehensible
28 ## 2010-10-11 Added check for row range
29 ## 2011-04-21 Added tests
30 ## 2011-04-30 Simplified column name computation
31 ## 2011-12-17 Bugfix for wrong column address if column equals multiple of 26
32 ## 2011-12-18 Added tests for multiple-of-26 cases
34 function [ celladdress ] = calccelladdress (row, column)
36 if (nargin < 2) error ("calccelladdress: Two arguments needed") endif
38 if (column > 18278 || column < 1) error ("Specified column out of range (1..18278)"); endif
39 if (row > 1048576 || row < 1), error ('Specified row out of range (1..1048576)'); endif
43 rmd = floor ((column - 1) / 26);
44 str = [char(column - rmd * 26 + 'A' - 1) str];
48 celladdress = sprintf ("%s%d", str, row);
53 %! a = calccelladdress (1, 1);
57 %! a = calccelladdress (378, 28);
58 %! assert (a, 'AB378');
61 %! a = calccelladdress (65536, 1024);
62 %! assert (a, 'AMJ65536');
65 %! a = calccelladdress (1048576, 16384);
66 %! assert (a, 'XFD1048576');
69 %! a = calccelladdress (378, 26);
70 %! assert (a, 'Z378');
73 %! a = calccelladdress (378, 702);
74 %! assert (a, 'ZZ378');
77 %! a = calccelladdress (378, 701);
78 %! assert (a, 'ZY378');
81 %! a = calccelladdress (378, 703);
82 %! assert (a, 'AAA378');
85 %! a = calccelladdress (378, 676);
86 %! assert (a, 'YZ378');