blocks|key|1600282|text|public+static+int+ExcelColumnNameToNumber(string+columnName)
{
++++if+(string.IsNullOrEmpty(columnName))+throw+new+ArgumentNullException("columnName");

++++columnName+=+columnName.ToUpperInvariant();

++++int+sum+=+0;

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

++++return+sum;
}|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1600283|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>public static int ExcelColumnNameToNumber(string columnName)
{
 if (string.IsNullOrEmpty(columnName)) throw new ArgumentNullException("columnName");

 columnName = columnName.ToUpperInvariant();

 int sum = 0;

 for (int i = 0; i &lt; columnName.Length; i++)
 {
 sum *= 26;
 sum += (columnName[i] - 'A' + 1);
 }

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

blocks|key|1600287|text|int+result+=+colName.Select((c,+i)+=>
++++((c+-+'A'+%2B+1)+*+((int)Math.Pow(26,+colName.Length+-+i+-+1)))).Sum();|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1600288|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>int result = colName.Select((c, i) =&gt;
 ((c - 'A' + 1) * ((int)Math.Pow(26, colName.Length - i - 1)))).Sum();
</code></pre>

blocks|key|1385461|text|int+col+=+colName.ToCharArray().Select(c+=>+c+-+'A'+%2B+1).
++++++++++Reverse().Select((v,+i)+=>+v+*+(int)Math.Pow(26,+i)).Sum();|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1385462|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>int col = colName.ToCharArray().Select(c =&gt; c - 'A' + 1).
 Reverse().Select((v, i) =&gt; v * (int)Math.Pow(26, i)).Sum();
</code></pre>

blocks|key|1385444|text|从最后一个到第一个循环遍历字符。将每个字母(A=1，Z=26)的值乘以26**N，加到一个连续的总数上。我在C#中的字符串操作技能是不存在的，所以这里有一些非常复杂的伪代码：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1385445|sum=0;
len=length(letters);
for(i=0;i<len;i%2B%2B)
++sum+%2B=+((letters[len-i-1])-'A'%2B1)+*+pow(26,i);|code-block|syntax|javascript|1385446|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|$]]

Loop through the characters from last to first. Multiply the value of each letter (A=1, Z=26) times 26**N, add to a running total. My string manipulation skill in C# is nonexistent, so here is some very mixed pseudo-code:

<pre><code>sum=0;
len=length(letters);
for(i=0;i&lt;len;i++)
 sum += ((letters[len-i-1])-'A'+1) * pow(26,i);
</code></pre>

blocks|key|1385493|text|如果有人感兴趣，这是我用JavaScript写的一个解决方案。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1385494|var+letters+=+"abc".toUpperCase();
var+sum+=+0;
for(var+i+=+0;+i+<+letters.length;i%2B%2B)
{
++++sum+*=+26;
++++sum+%2B=+(letters.charCodeAt(i)+-+("A".charCodeAt(0)-1));
}
alert(sum);|code-block|syntax|javascript|1385495|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 a solution I wrote up in JavaScript if anyone is interested.

<pre><code>var letters = "abc".toUpperCase();
var sum = 0;
for(var i = 0; i &lt; letters.length;i++)
{
 sum *= 26;
 sum += (letters.charCodeAt(i) - ("A".charCodeAt(0)-1));
}
alert(sum);
</code></pre>

blocks|key|1600376|text|较短的版本：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1600377|int+col+=+"Ab".Aggregate(0,+(a,+c)+=>+a+*+26+%2B+c+&+31);++//+28|code-block|syntax|javascript|1600378|要忽略非A-Za-z字符：|offset|length|style|CODE|1600379|int+col+=+"+!$Af$3+".Aggregate(0,+(a,+c)+=>+(uint)((c+%7C+32)+-+'a')+>+25+?+a+:+a+*+26+%2B+(c+&+31));+//+32|1600380|entityMap^0|0|0|4|6|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|@$I|T|J|U|K|L]]|9|@]|A|$]]|$1|M|3|N|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

Shorter version:
<pre><code>int col = &quot;Ab&quot;.Aggregate(0, (a, c) =&gt; a * 26 + c &amp; 31); // 28
</code></pre>
To ignore non <code>A-Za-z</code> characters:
<pre><code>int col = &quot; !$Af$3 &quot;.Aggregate(0, (a, c) =&gt; (uint)((c | 32) - 'a') &gt; 25 ? a : a * 26 + (c &amp; 31)); // 32
</code></pre>

blocks|key|1385527|text|在Excel+VBA中，您可以使用.Range方法获取数字，如下所示：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1385528|Dim+rng+as+Range
Dim+vSearchCol+as+variant+'your+input+column
Set+rng.Thisworkbook.worksheets("mySheet").Range(vSearchCol+&+"1:"+&+vSearchCol+&+"1")|code-block|syntax|javascript|1385529|然后使用.column属性：|1385530|+debug.print+rng.column|1385531|如果你需要完整的代码，请看下面的代码：|1385532|Function+ColumnbyName(vInput+As+Variant,+Optional+bByName+As+Boolean+=+True)+As+Variant
++++Dim+Rng+As+Range
++++If+bByName+Then
+++++++If+Not+VBA.IsNumeric(vInput)+Then
++++++++++++Set+Rng+=+ThisWorkbook.Worksheets("mytab").Range(vInput+&+"1:"+&+vInput+&+"1")
++++++++++++ColumnbyName+=+Rng.Column
+++++++Else
++++++++++++MsgBox+"Please+enter+valid+non+Numeric+column+or+change+paramter+bByName+to+False!"
+++++++End+If

++++Else
++++++++If+VBA.IsNumeric(vInput)+Then
++++++++++++ColumnbyName+=+VBA.Chr(64+%2B+CInt(vInput))
++++++++Else
++++++++++++MsgBox+"Please+enter+valid+Numeric+column+or+change+paramter+bByName+to+True!"
++++++++End+If

++++End+If
End+Function|1385533|entityMap^0|H|6|0|0|4|7|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|X|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|Y|8|@$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|M|3|N|5|H|7|11|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|12|8|@]|D|@]|E|$]]|$1|Q|3|R|5|H|7|13|8|@]|D|@]|E|$I|J]]|$1|S|3|-4|5|6|7|14|8|@]|D|@]|E|$]]]|T|$]]

in Excel VBA you could use the <code>.Range</code> Method to get the number, like so:

<pre><code>Dim rng as Range
Dim vSearchCol as variant 'your input column
Set rng.Thisworkbook.worksheets("mySheet").Range(vSearchCol &amp; "1:" &amp; vSearchCol &amp; "1")
</code></pre>

Then use <code>.column</code> property:

<pre><code> debug.print rng.column
</code></pre>

if you need full code see below:

<pre><code>Function ColumnbyName(vInput As Variant, Optional bByName As Boolean = True) As Variant
 Dim Rng As Range
 If bByName Then
 If Not VBA.IsNumeric(vInput) Then
 Set Rng = ThisWorkbook.Worksheets("mytab").Range(vInput &amp; "1:" &amp; vInput &amp; "1")
 ColumnbyName = Rng.Column
 Else
 MsgBox "Please enter valid non Numeric column or change paramter bByName to False!"
 End If

 Else
 If VBA.IsNumeric(vInput) Then
 ColumnbyName = VBA.Chr(64 + CInt(vInput))
 Else
 MsgBox "Please enter valid Numeric column or change paramter bByName to True!"
 End If

 End If
End Function
</code></pre>

blocks|key|1385553|text|我猜这基本上和其他一些答案的结果是一样的，但它可能会让人更清楚地知道，字母等同于一个数字是怎么回事。这不是一个基于26的系统，因为没有0占位符。也就是说，第26列将是'A0‘或其他东西，而不是基数26中的Z。它不是以27为底的，因为‘alpha-git’并不代表27的幂。伙计，这真的让你意识到在巴比伦人发明零之前，算术一定是多么混乱！|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1385554|++UInt32+sum+=+0,+gitVal+=+1;
++foreach+(char+alphagit+in+ColumnName.ToUpperInvariant().ToCharArray().Reverse())
++{
++++sum+%2B=+gitVal+*+(UInt32)(alphagit+-+'A'+%2B+1)
++++gitVal+*=+26;
++}|code-block|syntax|javascript|1385555|像其他一些人一样，我颠倒了字符数组，所以我不需要知道任何关于指数的信息。|1385556|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|$]]

I guess this essentially works out pretty much the same as some of the other answers, but it may make a little more clear what's going on with the alpha equivalent of a numeric digit. It's not quite a base 26 system because there is no 0 placeholder. That is, the 26th column would be 'A0' or something instead of Z in base 26. And it's not base 27 because the 'alpha-gits' don't represent powers of 27. Man, it really makes you appreciate what a mess arithmetic must have been before the Babylonians invented the zero!

<pre><code> UInt32 sum = 0, gitVal = 1;
 foreach (char alphagit in ColumnName.ToUpperInvariant().ToCharArray().Reverse())
 {
 sum += gitVal * (UInt32)(alphagit - 'A' + 1)
 gitVal *= 26;
 }
</code></pre>

Like some others, I reversed the character array so I don't need to know anything about exponents.

blocks|key|1600399|text|为此，我只使用了一行：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1600400|int+ColumnNumber+=+Application.Range[MyColumnName+%2B+"1"].Column;++|code-block|syntax|javascript|1600401|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|$]]

For this purpose I use only one line:
<pre><code>int ColumnNumber = Application.Range[MyColumnName + &quot;1&quot;].Column; 
</code></pre>

I need an algorithm to convert an Excel Column letter to its proper number.

The language this will be written in is C#, but any would do or even pseudo code. 

Please note I am going to put this in C# and I don't want to use the office dll.

For 'A' the expected result will be 1

For 'AH' = 34

For 'XFD' = 16384

What is the algorithm to convert an Excel Column Letter into its Number?

我需要一个算法来转换一个Excel列字母到其适当的数字。本文将使用的语言是C#，但任何语言都可以，甚至是伪代码。请注意，我将把它放在C#中，并且我不想使用office dll。对于'A‘，预期的结果将是1For 'AH‘= 34For 'XFD‘= 16384

问将Excel列字母转换为数字的算法是什么？
EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将Excel列字母转换为数字的算法是什么？EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将Excel列字母转换为数字的算法是什么？
EN