blocks|key|1364258|text|将字符串看作是26进制中具有A、B、……数字的列号的反转是否有帮助？Z？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1364259|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Does it help to think of the string as the reverse of the column number in base 26 with digits represented by A, B, ... Z?

blocks|key|1364290|text|哈-已经在我们的代码库中写了-大约3次不同的时间：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1364291|%25%25+@doc+Convert+an+string+to+a+decimal+integer
%25%25+@spec+b26_to_i(string())+->+integer()

b26_to_i(List)+when+is_list(List)+->
++++b26_to_i(string:to_lower(lists:reverse(List)),0,0).

%25%25+private+functions
b26_to_i([],+_Power,+Value)+->+
++++Value;

b26_to_i([H%7CT],Power,Value)->
++++NewValue+=+case+(H+>+96)+andalso+(H+<+123)+of
+++++++++++++++++++true+->
+++++++++++++++++++++++round((H+-+96)+*+math:pow(26,+Power));
+++++++++++++++++++_++++->
+++++++++++++++++++++++exit([H+%7C+T]+%2B%2B+"+is+not+a+valid+base+26+number")
+++++++++++++++end,
++++b26_to_i(T,+Power+%2B+1,+NewValue+%2B+Value).|code-block|syntax|javascript|1364292|难题是它实际上不是一个数字的Base26表示(我们在这里的函数名中欺骗了自己)，因为其中没有0。|1364293|顺序是:+A，B，C…Z、AA、AB、AC|1364294|而不是:+A，B，C+...Z，BA，BB，BC|1364295|(语言是Erlang，mais+oui)。|1364296|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

Hah - written it already in our code base - about 3 different times :(

<pre><code>%% @doc Convert an string to a decimal integer
%% @spec b26_to_i(string()) -&gt; integer()

b26_to_i(List) when is_list(List) -&gt;
 b26_to_i(string:to_lower(lists:reverse(List)),0,0).

%% private functions
b26_to_i([], _Power, Value) -&gt; 
 Value;

b26_to_i([H|T],Power,Value)-&gt;
 NewValue = case (H &gt; 96) andalso (H &lt; 123) of
 true -&gt;
 round((H - 96) * math:pow(26, Power));
 _ -&gt;
 exit([H | T] ++ " is not a valid base 26 number")
 end,
 b26_to_i(T, Power + 1, NewValue + Value).
</code></pre>

The riddle is that it isn't actually a Base26 representation of a number (we are lying to ourselves in our function name here) because there is no 0 in it.

The sequence is:
A, B, C ... Z, AA, AB, AC

and not:
A, B, C ...Z, BA, BB, BC

(the language is Erlang, mais oui).

blocks|key|1364337|text|假设A列=1|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1364338|int+GetColumnNumber(string+columnName)
{
++int+sum+=+0;
++int+exponent+=+0;
++for(int+i+=+columnName.Length+-+1;+i>=0;+i--)
++{
++++sum+%2B=+(columnName[i]+-+'A'+%2B+1)+*++(GetPower(26,+exponent));
++++exponent%2B%2B;
++}
++return+sum;
}

int+GetPower(int+number,+int+exponent)
{
++int+power+=+1;
++for(int+i=0;+i<exponent;+i%2B%2B)
++++power+*=+number;
++return+power;
}|code-block|syntax|javascript|1364339|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Assuming column A = 1

<pre class="lang-c prettyprint-override"><code>int GetColumnNumber(string columnName)
{
 int sum = 0;
 int exponent = 0;
 for(int i = columnName.Length - 1; i&gt;=0; i--)
 {
 sum += (columnName[i] - 'A' + 1) * (GetPower(26, exponent));
 exponent++;
 }
 return sum;
}

int GetPower(int number, int exponent)
{
 int power = 1;
 for(int i=0; i&lt;exponent; i++)
 power *= number;
 return power;
}
</code></pre>

blocks|key|1579634|text|这是一个CFML的例子：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1579635|<cffunction+name="ColToNum"+returntype="Numeric">
++++<cfargument+name="Input"+type="String"+/>
++++<cfset+var+Total+=+0+/>
++++<cfset+var+Pos+=+0+/>

++++<cfloop+index="Pos"+from="1"+to="#Len(Arguments.Input)#">
++++++++<cfset+Total+%2B=+26%5E(Pos-1)+*+(+Asc(+UCase(+Mid(Arguments.Input,Pos,1)+)+)+-+64+)+/>
++++</cfloop>

++++<cfreturn+Total+/>
</cffunction>

<cfoutput>
++++#ColToNum('AABCCE')#
</cfoutput>|code-block|syntax|javascript|1579636|因为我的心情很奇怪，这里有一个CFScript版本：|1579637|function+ColToNum+(+Input+)
{
++++var+Total+=+0;

++++for+(+var+Pos+=+1+;+Pos+<=+Len(Arguments.Input)+;+Pos%2B%2B+)
++++{
++++++++Total+%2B=+26%5E(Pos-1)+*+(+Asc(+UCase(+Mid(Arguments.Input,Pos,1)+)+)+-+64+);
++++}

++++return+Total;
}

WriteOutput(+ColToNum('AABCCE')+);|1579638|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Here's a CFML one:

<pre><code>&lt;cffunction name="ColToNum" returntype="Numeric"&gt;
 &lt;cfargument name="Input" type="String" /&gt;
 &lt;cfset var Total = 0 /&gt;
 &lt;cfset var Pos = 0 /&gt;

 &lt;cfloop index="Pos" from="1" to="#Len(Arguments.Input)#"&gt;
 &lt;cfset Total += 26^(Pos-1) * ( Asc( UCase( Mid(Arguments.Input,Pos,1) ) ) - 64 ) /&gt;
 &lt;/cfloop&gt;

 &lt;cfreturn Total /&gt;
&lt;/cffunction&gt;

&lt;cfoutput&gt;
 #ColToNum('AABCCE')#
&lt;/cfoutput&gt;
</code></pre>

 
And because I'm in an odd mood, here's a CFScript version:

<pre><code>function ColToNum ( Input )
{
 var Total = 0;

 for ( var Pos = 1 ; Pos &lt;= Len(Arguments.Input) ; Pos++ )
 {
 Total += 26^(Pos-1) * ( Asc( UCase( Mid(Arguments.Input,Pos,1) ) ) - 64 );
 }

 return Total;
}

WriteOutput( ColToNum('AABCCE') );
</code></pre>

blocks|key|1579723|text|我很久以前就为一些Python脚本写了这篇文章：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1579724|def+index_to_int(index):
++++s+=+0
++++pow+=+1
++++for+letter+in+index[::-1]:
++++++++d+=+int(letter,36)+-+9
++++++++s+%2B=+pow+*+d
++++++++pow+*=+26
++++#+excel+starts+column+numeration+from+1
++++return+s|code-block|syntax|javascript|1579725|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I wrote this ages ago for some Python script:

<pre class="lang-py prettyprint-override"><code>def index_to_int(index):
 s = 0
 pow = 1
 for letter in index[::-1]:
 d = int(letter,36) - 9
 s += pow * d
 pow *= 26
 # excel starts column numeration from 1
 return s
</code></pre>

blocks|key|1579769|text|稍有关联的是，更好的挑战是反过来:给定列号，找到字符串形式的列标签。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1579770|我为KOffice实现的Qt版本：|1579771|QString+columnLabel(+unsigned+column+)
{
++QString+str;
++unsigned+digits+=+1;
++unsigned+offset+=+0;

++column--;
++for(+unsigned+limit+=+26;+column+>=+limit%2Boffset;+limit+*=+26,+digits%2B%2B+)
++++offset+%2B=+limit;

++for(+unsigned+c+=+column+-+offset;+digits;+--digits,+c/=26+)
++++str.prepend(+QChar(+'A'+%2B+(c%2526)+)+);

++return+str;
}|code-block|syntax|javascript|1579772|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Slightly related, the better challenge is the other way around: given the column number, find the column label as string.

Qt version as what I implemented for KOffice:

<pre class="lang-cpp prettyprint-override"><code>QString columnLabel( unsigned column )
{
 QString str;
 unsigned digits = 1;
 unsigned offset = 0;

 column--;
 for( unsigned limit = 26; column &gt;= limit+offset; limit *= 26, digits++ )
 offset += limit;

 for( unsigned c = column - offset; digits; --digits, c/=26 )
 str.prepend( QChar( 'A' + (c%26) ) );

 return str;
}
</code></pre>

blocks|key|1364695|text|另一个更神秘的erlang示例：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1364696|col2int(String)+->+col2int(0,String).
col2int(X,[A%7CL])+when+A+>=+65,+A+=<+90+->
col2int(26+*+X+%2B+A+-+65+%2B+1,+L);
col2int(X,[])+->+X.|code-block|syntax|javascript|1364697|和逆函数：|1364698|int2col(Y)+when+Y+>+0+->+int2col(Y,[]).
int2col(0,L)+->+L;
int2col(Y,L)+when+Y+rem+26+==+0+->+
+++int2col(Y+div+26+-+1,[(26%2B65-1)%7CL]);
int2col(Y,L)+->
+++P+=+Y+rem+26,
+++int2col((Y+-+P)+div+26,[P+%2B+65-1%7CL]).|1364699|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

another [more cryptic] erlang example:

<pre><code>col2int(String) -&gt; col2int(0,String).
col2int(X,[A|L]) when A &gt;= 65, A =&lt; 90 -&gt;
col2int(26 * X + A - 65 + 1, L);
col2int(X,[]) -&gt; X.
</code></pre>

and inverse function:

<pre><code>int2col(Y) when Y &gt; 0 -&gt; int2col(Y,[]).
int2col(0,L) -&gt; L;
int2col(Y,L) when Y rem 26 == 0 -&gt; 
 int2col(Y div 26 - 1,[(26+65-1)|L]);
int2col(Y,L) -&gt;
 P = Y rem 26,
 int2col((Y - P) div 26,[P + 65-1|L]).
</code></pre>

blocks|key|1364749|text|Delphi：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1364750|//+convert+EXcel+column+name+to+column+number+1..256
//+case-sensitive;+returns+0+for+illegal+column+name
function+cmColmAlfaToNumb(+const+qSRC+:+string+)+:+integer;
var+II+:+integer;
begin
+++result+:=+0;
+++for+II+:=+1+to+length(qSRC)+do+begin
++++++if+(qSRC[II]<'A')or(qSRC[II]>'Z')+then+begin
+++++++++result+:=+0;
+++++++++exit;
++++++end;
++++++result+:=+result*26%2Bord(qSRC[II])-ord('A')%2B1;
+++end;
+++if+result>256+then+result+:=+0;
end;|code-block|syntax|javascript|1364751|-Al。|1364752|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Delphi:

<pre><code>// convert EXcel column name to column number 1..256
// case-sensitive; returns 0 for illegal column name
function cmColmAlfaToNumb( const qSRC : string ) : integer;
var II : integer;
begin
 result := 0;
 for II := 1 to length(qSRC) do begin
 if (qSRC[II]&lt;'A')or(qSRC[II]&gt;'Z') then begin
 result := 0;
 exit;
 end;
 result := result*26+ord(qSRC[II])-ord('A')+1;
 end;
 if result&gt;256 then result := 0;
end;
</code></pre>

-Al.

blocks|key|1364836|text|def+ExcelColumnToNumber(ColumnName):
++++ColNum+=+0
++++for+i+in+range(0,+len(ColumnName)):
++++++++#+Easier+once+formula+determined:+'PositionValue+*+Base%5EPosition'
++++++++#+i.e.+AA=(1*26%5E1)%2B(1*26%5E0)+++or++792=(7*10%5E2)%2B(9*10%5E1)%2B(2*10%5E0)
++++++++ColNum+%2B=+(int(ColumnName[i],36)+-9)+*+(pow(26,+len(ColumnName)-i-1))
++++return+ColNum|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1364837|附注：我的第一个Python脚本！|unstyled|1364838|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre class="lang-py prettyprint-override"><code>def ExcelColumnToNumber(ColumnName):
 ColNum = 0
 for i in range(0, len(ColumnName)):
 # Easier once formula determined: 'PositionValue * Base^Position'
 # i.e. AA=(1*26^1)+(1*26^0) or 792=(7*10^2)+(9*10^1)+(2*10^0)
 ColNum += (int(ColumnName[i],36) -9) * (pow(26, len(ColumnName)-i-1))
 return ColNum
</code></pre>

p.s. My first Python script!

blocks|key|1580045|text|注意:这两个版本都只假设大写字母A到Z。其他任何字母都会导致错误计算。添加一些错误检查和/或大写来改进它们并不难。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1580046|Scala|1580047|def+excel2Number(excel+:+String)+:+Int+=+
++(0+/:+excel)+((accum,+ch)+=>+accum+*+26+%2B+ch+-+'A'+%2B+1)|code-block|syntax|javascript|1580048|Haskell|1580049|excel2Number+::+String+->+Int
excel2Number+=+flip+foldl+0+$+\accum+ch+->+accum+*+26+%2B+fromEnum+ch+-+fromEnum+'A'+%2B+1|1580050|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Caveat: both of these versions assume only uppercase letters A to Z. Anything else causes a miscalculation. It wouldn't be hard to add a bit of error checking and/or uppercasing to improve them.

Scala

<pre class="lang-scala prettyprint-override"><code>def excel2Number(excel : String) : Int = 
 (0 /: excel) ((accum, ch) =&gt; accum * 26 + ch - 'A' + 1)
</code></pre>

Haskell

<pre class="lang-hs prettyprint-override"><code>excel2Number :: String -&gt; Int
excel2Number = flip foldl 0 $ \accum ch -&gt; accum * 26 + fromEnum ch - fromEnum 'A' + 1
</code></pre>

blocks|key|1364997|text|这个版本是纯功能的，并且允许可选的'code‘序列，例如，如果你想只使用字母'A’到'C‘。在Scala中，根据dcsobral的建议。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1364998|def+columnNumber(name:+String)+=+{
++++val+code+=+'A'+to+'Z'

++++name.foldLeft(0)+{+(sum,+letter)+=>
++++++++(sum+*+code.length)+%2B+(code.indexOf(letter)+%2B+1)
++++}
}|code-block|syntax|javascript|1364999|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This version is purely functional and permits alternative 'code' sequences, for example if you wanted to only uses the letters 'A' to 'C'. In Scala, with a suggestion from dcsobral. 

<pre class="lang-scala prettyprint-override"><code>def columnNumber(name: String) = {
 val code = 'A' to 'Z'

 name.foldLeft(0) { (sum, letter) =&gt;
 (sum * code.length) + (code.indexOf(letter) + 1)
 }
}
</code></pre>

blocks|key|1365040|text|对我来说，这听起来像是标准的缩减：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1365041|Python：|1365042|def+excel2num(x):+
++++return+reduce(lambda+s,a:s*26%2Bord(a)-ord('A')%2B1,+x,+0)|code-block|syntax|javascript|1365043|C#：|1365044|int+ExcelToNumber(string+x)+{
++++return+x.Aggregate(0,+(s,+c)+=>+s+*+26+%2B+c+-+'A'+%2B+1+);
}|1365045|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Sounds like a standard reduce to me:

Python:

<pre class="lang-py prettyprint-override"><code>def excel2num(x): 
 return reduce(lambda s,a:s*26+ord(a)-ord('A')+1, x, 0)
</code></pre>

C#:

<pre class="lang-cs prettyprint-override"><code>int ExcelToNumber(string x) {
 return x.Aggregate(0, (s, c) =&gt; s * 26 + c - 'A' + 1 );
}
</code></pre>

blocks|key|1365072|text|你可以在C中这样做：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1365073|unsigned+int+coltonum(char+*+string)
{
+++unsigned+result+=+0;
+++char+ch;

+++while(ch+=+*string%2B%2B)
++++++result+=+result+*+26+%2B+ch+-+'A'+%2B+1;

++return+result;
}|code-block|syntax|javascript|1365074|无错误检查，仅适用于大写字符串，字符串必须以null结尾。|1365075|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

You can do this in C like this:

<pre class="lang-c prettyprint-override"><code>unsigned int coltonum(char * string)
{
 unsigned result = 0;
 char ch;

 while(ch = *string++)
 result = result * 26 + ch - 'A' + 1;

 return result;
}
</code></pre>

No error checking, only works for upper case strings, string must be null terminated.

blocks|key|1365125|text|从列名中获取列号|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1365126|Java：|1365127|public+int+getColNum+(String+colName)+{

++++//remove+any+whitespace
++++colName+=+colName.trim();

++++StringBuffer+buff+=+new+StringBuffer(colName);

++++//string+to+lower+case,+reverse+then+place+in+char+array
++++char+chars[]+=+buff.reverse().toString().toLowerCase().toCharArray();

++++int+retVal=0,+multiplier=0;

++++for(int+i+=+0;+i+<+chars.length;i%2B%2B){
++++++++//retrieve+ascii+value+of+character,+subtract+96+so+number+corresponds+to+place+in+alphabet.+ascii+'a'+=+97+
++++++++multiplier+=+(int)chars[i]-96;
++++++++//mult+the+number+by+26%5E(position+in+array)
++++++++retVal+%2B=+multiplier+*+Math.pow(26,+i);
++++}
++++return+retVal;
}|code-block|syntax|javascript|1365128|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Get the column number from its name

Java:

<pre class="lang-java prettyprint-override"><code>public int getColNum (String colName) {

 //remove any whitespace
 colName = colName.trim();

 StringBuffer buff = new StringBuffer(colName);

 //string to lower case, reverse then place in char array
 char chars[] = buff.reverse().toString().toLowerCase().toCharArray();

 int retVal=0, multiplier=0;

 for(int i = 0; i &lt; chars.length;i++){
 //retrieve ascii value of character, subtract 96 so number corresponds to place in alphabet. ascii 'a' = 97 
 multiplier = (int)chars[i]-96;
 //mult the number by 26^(position in array)
 retVal += multiplier * Math.pow(26, i);
 }
 return retVal;
}
</code></pre>

blocks|key|1580234|text|在Java(read+more+here)中，从整型变量中获取列名：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1580235|public+String+getColName+(int+colNum)+{

+++String+res+=+"";

+++int+quot+=+colNum;
+++int+rem;++++++++
++++/*1.+Subtract+one+from+number.
++++*2.+Save+the+mod+26+value.
+++*3.+Divide+the+number+by+26,+save+result.
+++*4.+Convert+the+remainder+to+a+letter.
+++*5.+Repeat+until+the+number+is+zero.
+++*6.+Return+that+bitch...
+++*/
++++while(quot+>+0)
++++{
++++++++quot+=+quot+-+1;
++++++++rem+=+quot+%25+26;
++++++++quot+=+quot+/+26;

++++++++//cast+to+a+char+and+add+to+the+beginning+of+the+string
++++++++//add+97+to+convert+to+the+correct+ascii+number
++++++++res+=+(char)(rem%2B97)+%2B+res;++++++++++++
++++}+++
++++return+res;
}|code-block|syntax|javascript|1580236|entityMap|0|LINK|mutability|MUTABLE|url|http://mcdrummerman.wordpress.com/2010/01/07/convert-a-column-number-to-a-column-name-java-example/#more-146^0|6|E|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

Get a column name from an int in Java(<a href="http://mcdrummerman.wordpress.com/2010/01/07/convert-a-column-number-to-a-column-name-java-example/#more-146" rel="nofollow noreferrer">read more here</a>):

<pre class="lang-java prettyprint-override"><code>public String getColName (int colNum) {

 String res = "";

 int quot = colNum;
 int rem; 
 /*1. Subtract one from number.
 *2. Save the mod 26 value.
 *3. Divide the number by 26, save result.
 *4. Convert the remainder to a letter.
 *5. Repeat until the number is zero.
 *6. Return that bitch...
 */
 while(quot &gt; 0)
 {
 quot = quot - 1;
 rem = quot % 26;
 quot = quot / 26;

 //cast to a char and add to the beginning of the string
 //add 97 to convert to the correct ascii number
 res = (char)(rem+97) + res; 
 } 
 return res;
}
</code></pre>

blocks|key|1365163|text|在Mathematica中：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1365164|FromDigits[ToCharacterCode@#+-+64,+26]+&|code-block|syntax|javascript|1365165|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

In Mathematica:

<pre><code>FromDigits[ToCharacterCode@# - 64, 26] &amp;
</code></pre>

blocks|key|1580303|text|使用向导先生令人惊叹的Mathematica代码，但摆脱了神秘的纯函数！|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1580304|columnNumber[name_String]+:=+FromDigits[ToCharacterCode[name]+-+64,+26]|code-block|syntax|javascript|1580305|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Using Mr. Wizard's awesome Mathematica code, but getting rid of the cryptic pure function!

<pre><code>columnNumber[name_String] := FromDigits[ToCharacterCode[name] - 64, 26]
</code></pre>

blocks|key|1580356|text|另一个Delphi：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1580357|function+ExcelColumnNumberToLetter(col:+Integer):+string;
begin
++if+(col+<=+26)+then+begin
++++Result+:=+Chr(col+%2B+64);
++end
++else+begin
++++col+:=+col-1;
++++Result+:=+ExcelColumnNumberToLetter(col+div+26)+%2B+ExcelColumnNumberToLetter((col+mod+26)+%2B+1);
++end;
end;|code-block|syntax|javascript|1580358|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Another Delphi one:

<pre><code>function ExcelColumnNumberToLetter(col: Integer): string;
begin
 if (col &lt;= 26) then begin
 Result := Chr(col + 64);
 end
 else begin
 col := col-1;
 Result := ExcelColumnNumberToLetter(col div 26) + ExcelColumnNumberToLetter((col mod 26) + 1);
 end;
end;
</code></pre>

blocks|key|1365275|text|另一个Java：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1365276|public+static+int+convertNameToIndex(String+columnName)+{
++++int+index+=+0;
++++char[]+name+=+columnName.toUpperCase().toCharArray();

++++for(int+i+=+0;+i+<+name.length;+i%2B%2B)+{
++++++++index+*=+26;
++++++++index+%2B=+name[i]+-+'A'+%2B+1;
++++}

++++return+index;
}|code-block|syntax|javascript|1365277|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Another Java:

<pre><code>public static int convertNameToIndex(String columnName) {
 int index = 0;
 char[] name = columnName.toUpperCase().toCharArray();

 for(int i = 0; i &lt; name.length; i++) {
 index *= 26;
 index += name[i] - 'A' + 1;
 }

 return index;
}
</code></pre>

blocks|key|1365368|text|维基百科有很好的解释和算法|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1365369|http://en.wikipedia.org/wiki/Hexavigesimal|offset|length|1365370|public+static+String+toBase26(int+value){
++++//+Note:+This+is+a+slightly+modified+version+of+the+Alphabet-only+conversion+algorithm

++++value+=+Math.abs(value);
++++String+converted+=+"";

++++boolean+iteration+=+false;

++++//+Repeatedly+divide+the+number+by+26+and+convert+the
++++//+remainder+into+the+appropriate+letter.
++++do+{
++++++++int+remainder+=+value+%25+26;

++++++++//+Compensate+for+the+last+letter+of+the+series+being+corrected+on+2+or+more+iterations.
++++++++if+(iteration+&&+value+<+25)+{
++++++++++++remainder--;
++++++++}

++++++++converted+=+(char)(remainder+%2B+'A')+%2B+converted;
++++++++value+=+(value+-+remainder)+/+26;

++++++++iteration+=+true;
++++}+while+(value+>+0);

++++return+converted;++++
}|code-block|syntax|javascript|1365371|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|16|0|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|S|8|@]|9|@$D|T|E|U|1|V]]|A|$]]|$1|F|3|G|5|H|7|W|8|@]|9|@]|A|$I|J]]|$1|K|3|-4|5|6|7|X|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|C]]]]

Wikipedia has good explanations and algos

<a href="http://en.wikipedia.org/wiki/Hexavigesimal" rel="nofollow">http://en.wikipedia.org/wiki/Hexavigesimal</a>

<pre><code>public static String toBase26(int value){
 // Note: This is a slightly modified version of the Alphabet-only conversion algorithm

 value = Math.abs(value);
 String converted = "";

 boolean iteration = false;

 // Repeatedly divide the number by 26 and convert the
 // remainder into the appropriate letter.
 do {
 int remainder = value % 26;

 // Compensate for the last letter of the series being corrected on 2 or more iterations.
 if (iteration &amp;&amp; value &lt; 25) {
 remainder--;
 }

 converted = (char)(remainder + 'A') + converted;
 value = (value - remainder) / 26;

 iteration = true;
 } while (value &gt; 0);

 return converted; 
}
</code></pre>

blocks|key|1365447|text|巧合的是，我用javascript解决了同样的问题|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1365448|​|1365449|$(function()+{+//shorthand+document.ready+function
++++var+getNumber+=+function(x)+{
++++++++var+result+=+0;
++++++++var+multiplier+=+1;
++++++++for+(+var+i+=+x.length-1;+i+>=+0;+i--)
++++++++{+
++++++++++++var+value+=+((x[i].charCodeAt(0)+-+"A".charCodeAt(0))+%2B+1);
++++++++++++result+=+result+%2B+value+*+multiplier;
++++++++++++multiplier+=+multiplier+*+26;
++++++++}
++++++++return+result;
++++};
++++
++++$('#form').on('submit',+function(e)+{+//use+on+if+jQuery+1.7%2B
++++++++e.preventDefault();++//prevent+form+from+submitting
++++++++var+data+=+$("#number").val();
++++++++$('#answer').text(getNumber(data));
++++});
});|code-block|syntax|javascript|1365450|<script+src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
<form+id="form">
<input+type="text"+id="number"></input>
++++<button>submit</button>
</form>
<p+id="answer">|1365451|1365452|++++var+getNumber+=+function(x)+{
++++++++var+result+=+0;
++++++++var+multiplier+=+1;
++++++++for+(+var+i+=+x.length-1;+i+>=+0;+i--)
++++++++{+
++++++++++++var+value+=+((x[i].charCodeAt(0)+-+"A".charCodeAt(0))+%2B+1);
++++++++++++result+=+result+%2B+value+*+multiplier;
++++++++++++multiplier+=+multiplier+*+26;
++++++++}
++++++++return+result;
++++};|1365453|http://jsfiddle.net/M7Xty/1/|offset|length|1365454|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|0|0|0|0|0|S|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Z|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|10|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|F|7|11|8|@]|9|@]|A|$G|H]]|$1|K|3|C|5|6|7|12|8|@]|9|@]|A|$]]|$1|L|3|M|5|F|7|13|8|@]|9|@]|A|$G|H]]|$1|N|3|O|5|6|7|14|8|@]|9|@$P|15|Q|16|1|17]]|A|$]]|$1|R|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|S|$T|$5|U|V|W|A|$X|O]]]]

Coincidentally I've solved the same problem using javascript 

<div class="snippet" data-lang="js" data-hide="true">
<div class="snippet-code snippet-currently-hidden">
<pre class="snippet-code-js lang-js prettyprint-override"><code>$(function() { //shorthand document.ready function
 var getNumber = function(x) {
 var result = 0;
 var multiplier = 1;
 for ( var i = x.length-1; i &gt;= 0; i--)
 { 
 var value = ((x[i].charCodeAt(0) - "A".charCodeAt(0)) + 1);
 result = result + value * multiplier;
 multiplier = multiplier * 26;
 }
 return result;
 };
 
 $('#form').on('submit', function(e) { //use on if jQuery 1.7+
 e.preventDefault(); //prevent form from submitting
 var data = $("#number").val();
 $('#answer').text(getNumber(data));
 });
});</code></pre>
<pre class="snippet-code-html lang-html prettyprint-override"><code>&lt;script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"&gt;&lt;/script&gt;
&lt;form id="form"&gt;
&lt;input type="text" id="number"&gt;&lt;/input&gt;
 &lt;button&gt;submit&lt;/button&gt;
&lt;/form&gt;
&lt;p id="answer"&gt;&lt;/p&gt;</code></pre>
</div>
</div>


<pre><code> var getNumber = function(x) {
 var result = 0;
 var multiplier = 1;
 for ( var i = x.length-1; i &gt;= 0; i--)
 { 
 var value = ((x[i].charCodeAt(0) - "A".charCodeAt(0)) + 1);
 result = result + value * multiplier;
 multiplier = multiplier * 26;
 }
 return result;
 };
</code></pre>

<a href="http://jsfiddle.net/M7Xty/1/" rel="noreferrer">http://jsfiddle.net/M7Xty/1/</a>

blocks|key|1580609|text|在Python中，不使用reduce：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1580610|def+transform(column_string):
++++return+sum((ascii_uppercase.index(letter)%2B1)+*+26**position+for+position,+letter+in+enumerate(column_string[::-1]))|code-block|syntax|javascript|1580611|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

In Python, without reduce:

<pre><code>def transform(column_string):
 return sum((ascii_uppercase.index(letter)+1) * 26**position for position, letter in enumerate(column_string[::-1]))
</code></pre>

blocks|key|1580689|text|以下是Python中此代码的另一个版本：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1580690|keycode=1
for+i+in+range+(1,len(word)):
++++numtest[i]=word[i-1]
++++keycode+=+keycode*26*int(wordtest[numtest[i]])
last=word[-1:]
keycode=keycode%2Bint(wordtest[last])
print(keycode)
print(bin(keycode))
#Numtest+and+wordtest+are+dictionaries.|code-block|syntax|javascript|1580691|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Here is another version of this code in Python:

<pre class="lang-py prettyprint-override"><code>keycode=1
for i in range (1,len(word)):
 numtest[i]=word[i-1]
 keycode = keycode*26*int(wordtest[numtest[i]])
last=word[-1:]
keycode=keycode+int(wordtest[last])
print(keycode)
print(bin(keycode))
#Numtest and wordtest are dictionaries.
</code></pre>

blocks|key|1580746|text|简单的Java解决方案-->|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1580747|public+class+ColumnName+{

public+static+int+colIndex(String+col)
{+++int+index=0;
++++int+mul=0;
++++for(int+i=col.length()-1;i>=0;i--)
++++{+++
++++++++index++%2B=+(col.charAt(i)-64)+*+Math.pow(26,+mul);
++++++++mul%2B%2B;
++++}
++++return+index;
}

public+static+void+main(String[]+args)+{

++++System.out.println(colIndex("AAA"));

}|code-block|syntax|javascript|1580748|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Easy Java solution -->

<pre><code>public class ColumnName {

public static int colIndex(String col)
{ int index=0;
 int mul=0;
 for(int i=col.length()-1;i&gt;=0;i--)
 { 
 index += (col.charAt(i)-64) * Math.pow(26, mul);
 mul++;
 }
 return index;
}

public static void main(String[] args) {

 System.out.println(colIndex("AAA"));

}
</code></pre>

I was recently asked in a job interview to resolve a programming puzzle that I thought it would be interesting to share. It's about translating Excel column letters to actual numbers, if you recall, Excel names its columns with letters from A to Z, and then the sequence goes AA, AB, AC... AZ, BA, BB, etc.

You have to write a function that accepts a string as a parameter (like "AABCCE") and returns the actual column number.

The solution can be in any language.

Programming Riddle: How might you translate an Excel column name to a number?

最近，我在一次求职面试中被要求解决一个编程难题，我认为分享这个难题会很有趣。这是关于将Excel列字母转换为实际数字，如果你还记得，Excel用字母从A到Z命名它的列，然后顺序是AA，AB，AC……AZ、BA、BB等您必须编写一个接受字符串作为参数的函数(如"AABCCE")，并返回实际的列数。解决方案可以是任何语言。

问编程谜语:如何将Excel列名转换为数字？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问编程谜语:如何将Excel列名转换为数字？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问编程谜语:如何将Excel列名转换为数字？
EN