Deux fonctions permettant de convertir une référence d'une cellule ou d'une plage de cellules d'une feuille Excel contenue dans une chaine de caractères.

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
function range=ricj2ak(range)
 
% RiCj2Ak Conversion between RiCj and Ak notation in Excel.
%   RiCj2Ak converts the string RANGE in form RiCj to form Ak.
%   RiCj2Ak supports single and multiple cell range.
%
%   Note: due to Excel limitation, RiCj2Ak returns an error if 
%         number of columns exceeds 256 (IV).
%
%   Ex :
%       range = 'R10C149:R12C190';
%       range = ricj2ak(range);
%
%           range =
%
%           ES10:GH12
%    
%   See also: ak2ricj
%
 
%   Author: Jérôme Briot
%   Contact: dutmatlab#yahoo#fr
%                http://www.developpez.net/forums/member.php?u=125006
%   Version: 1.0 (07 Aug 2006)
%   Comments:
%
 
% Check input arguments.
error(nargchk(1, 1, nargin))
 
if strfind(range,':') % Multiple cells range.
 
    % Read range. Column numbers are stored into data(2) and data(4).
    data=sscanf(range,'%*c%d%*c%d%*c%*c%d%*c%d');
 
    % Check if any column numbers exceed 256.
    if data(2)>256 | data(4)>256
 
        error('Excel doesn''t support more than 256 columns')
 
    end
 
    % Modify the first sub_range
    C1=[floor((data(2)-1)/26)+64 rem(data(2)-1,26)+65];
    C1(C1<65)=[];
    % Modify the second sub_range
    C2=[floor((data(4)-1)/26)+64 rem(data(4)-1,26)+65];
    C2(C2<65)=[];
    % Create the new range
    range=sprintf('%s%d:%s%d',char(C1),data(1),char(C2),data(3));
 
else % Single cell range.
 
    % Read range
	data=sscanf(range,'%*c%d%*c%d');
	% Check if the column number exceeds 256.
    if data(2)>256
 
        error('Excel doesn''t support more than 256 columns')
 
    end
 
    % Modify the range
    C=[floor((data(2)-1)/26)+64 rem(data(2)-1,26)+65];
    C(C<65)=[];
    % Create the new range
    range=sprintf('%s%d',char(C),data(1));
 
end
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
function range=ak2ricj(range,Rdlm,Cdlm)
 
% Ak2RiCj Conversion bettween Ak and RiCj notation in Excel.
%   Ak2RiCj(RANGE) converts the string RANGE in form A1 to 
%   form RiCj.
%   Ak2RiCj(RANGE,RDLM,CDLM) lets the user specify the row 
%   and column symbols (for non-english Excel version).
%   Ak2RiCj supports both "ak" and "Ak" notation but only
%   returns capitalized form "Ak".
%
%   Note: due to Excel limitation, Ak2RiCj returns an error 
%         if the number of columns exceeds 256 (IV).
%
%   Ex :
%
%       range = 'ES10:GH12';
%       range = ak2ricj(range);
%
%           range =
%
%           R10C149:R12C190
%
%   Ex (for french users):
%
%       range = 'ES10:GH12';
%       range = ak2ricj(range,'L','C');
%
%           range =
%
%           L10C149:L12C190
%
%   See also: ricj2ak.
%
 
%   Author: Jérôme Briot
%   Contact: dutmatlab#yahoo#fr
%                http://www.developpez.net/forums/member.php?u=125006
%   Version: 1.0 (07 Aug 2006)
%   Comments:
%
 
error(nargchk(1, 3, nargin))
 
if nargin==1
 
    Rdlm='R';
    Cdlm='C';
 
end
 
if strfind(range,':') % Multiple cells range
 
    % Split range string into sub_ranges
    [sub_range1,sub_range2]=strtok(range,':');
    sub_range2(1)=[];
 
    % Separate numbers (row index) and characters (column index) for the first sub_range 
    C=sub_range1(isletter(sub_range1));
	R=sub_range1(~isletter(sub_range1));
 
    C=upper(C)-64; % Offset from the ASCII table A -> 65
 
    if length(C)==2 % AA -> IV 
 
        C=C(1)*26+C(2);
 
        if C>256 % >IV
 
            error('Excel doesn''t support more than 256 columns (A->IV)')
 
        end
 
    elseif length(C)>2 
 
        error('Excel doesn''t support more than 256 columns (A->IV)')
 
	end
 
    % Create the first part of the new range
    range=sprintf('%c%s%c%d:',Rdlm,R,Cdlm,C);
 
    % Separate digits (row index) and characters (column index) for the second sub_range
    C=sub_range2(isletter(sub_range2));
	R=sub_range2(~isletter(sub_range2));
 
    C=upper(C)-64; % Offset from the ASCII table A -> 65
 
    if length(C)==2 % AA -> IV 
 
        C=C(1)*26+C(2);
 
        if C>256 % >IV
 
            error('Excel doesn''t support more than 256 columns (A->IV)')
 
        end
 
    elseif length(C)>2 
 
        error('Excel doesn''t support more than 256 columns (A->IV)')
 
	end
 
    % Add the last part of the new range
    range=sprintf('%s%c%s%c%d',range,Rdlm,R,Cdlm,C);
 
else % Single cell range
 
    % Separate digits (row index) and characters (column index)
	C=range(isletter(range));
	R=range(~isletter(range));
 
    C=upper(C)-64; % Offset from the ASCII table A -> 65
 
    if length(C)==2 % AA -> IV 
 
        C=C(1)*26+C(2);
 
        if C>256 % >IV
 
            error('Excel doesn''t support more than 256 columns (A->IV)')
 
        end
 
    elseif length(C)>2 
 
        error('Excel doesn''t support more than 256 columns (A->IV)')
 
	end
 
    % Create the new range
    range=sprintf('%c%s%c%d',Rdlm,R,Cdlm,C);
 
end
Exemples :
>> range = 'R10C149',range = ricj2ak(range)

range =

R10C149


range =

ES10

>> range = 'R10C149:R12C190',range = ricj2ak(range)

range =

R10C149:R12C190


range =

ES10:GH12
>> range = 'ES10',range = ak2ricj(range)

range =

ES10


range =

R10C149

>> range = 'ES10:GH12',range = ak2ricj(range)

range =

ES10:GH12


range =

R10C149:R12C190

>> range = 'ES10:GH12',range = ak2ricj(range,'L','C')

range =

ES10:GH12


range =

L10C149:L12C190
Note : l'algorithme n'est pas de moi, il a été récupéré sur le CSSM. Il existe une version supportant uniquement une référence à la fois dans le FEX.