C# 如何将Excel工作表列名转换为数字？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/848147/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to convert Excel sheet column names into numbers?
提问by Victor Rodrigues
I was wondering what is the best way to convert excel sheet column names into numbers.
我想知道将 Excel 工作表列名称转换为数字的最佳方法是什么。
I'm working with Excel Package, a good library to handle .xlsx documents. This library unfortunately doesn't have this functionality included.
我正在使用Excel Package，这是一个处理 .xlsx 文档的好库。不幸的是，这个库没有包含这个功能。
OBS: The first column, A, corresponds to number 1 in this library.
OBS：第一列 A 对应于该库中的编号 1。
采纳答案by Noldorin
This function should work for an arbitrary length column name.
此函数应该适用于任意长度的列名。
public static int GetColumnNumber(string name)
{
int number = 0;
int pow = 1;
for (int i = name.Length - 1; i >= 0; i--)
{
number += (name[i] - 'A' + 1) * pow;
pow *= 26;
}
return number;
}
回答by Daniel Brückner
I had to deal with this a few months ago. The inverse - column index to column name - is fun, too, and becomes really messy if you try to solve it with a zero based index not recognizing that this complicates things. It could be so simple if it would be a normal polyadic numeral system ...
几个月前我不得不处理这个问题。倒数 - 列索引到列名 - 也很有趣，如果您尝试使用基于零的索引来解决它而没有意识到这会使事情变得复杂，那么它会变得非常混乱。如果它是一个普通的多元数字系统，它可能会如此简单......
Here is a simplified version of my solution as a extension method without error handling and all that stuff.
这是我的解决方案的简化版本，作为没有错误处理和所有这些东西的扩展方法。
public static Int32 ToOneBasedIndex(this String name)
{
return name.ToUpper().
Aggregate(0, (column, letter) => 26 * column + letter - 'A' + 1);
}
回答by Sam Martinez
I've been working with this for a while now and found this to work really good for columns that go beyond A-Z, or even beyond AA-ZZ... It's accomplished by breaking apart each character in the string and recursively calling itself to derive the DEC value of the ASCII character (less 64), then multiplying it by 26^n. A return value of long was used to overcome a potential limitation when n > 4.
我已经使用它一段时间了，发现这对于超出 AZ 甚至超出 AA-ZZ 的列非常有用......它是通过分解字符串中的每个字符并递归调用自身来实现的ASCII 字符的 DEC 值（小于 64），然后乘以 26^n。当 n > 4 时，返回值 long 用于克服潜在的限制。
public long columnNumber(String columnName)
{
char[] chars = columnName.ToUpper().ToCharArray();
return (long)(Math.Pow(26, chars.Count() - 1)) *
(System.Convert.ToInt32(chars[0]) - 64) +
((chars.Count() > 2) ? columnNumber(columnName.Substring(1, columnName.Length - 1)) :
((chars.Count() == 2) ? (System.Convert.ToInt32(chars[chars.Count() - 1]) - 64) : 0));
}
Also, if you'd like to get the inverse (i.e. pass in the columnNumber and get the columnName, here's some code that works for that.
此外，如果您想获得相反的结果（即传入 columnNumber 并获得 columnName，这里有一些适用于此的代码。
public String columnName(long columnNumber)
{
StringBuilder retVal = new StringBuilder();
int x = 0;
for (int n = (int)(Math.Log(25*(columnNumber + 1))/Math.Log(26)) - 1; n >= 0; n--)
{
x = (int)((Math.Pow(26,(n + 1)) - 1) / 25 - 1);
if (columnNumber > x)
retVal.Append(System.Convert.ToChar((int)(((columnNumber - x - 1) / Math.Pow(26, n)) % 26 + 65)));
}
return retVal.ToString();
}
回答by Arijit Pal Chaudhury
Source code:
源代码：
namespace XLS
{
/// <summary>
/// Represents a single cell in a excell sheet
/// </summary>
public struct Cell
{
private long row;
private long column;
private string columnAddress;
private string address;
private bool dataChange;
/// <summary>
/// Initializes a new instance of the XLS.Cell
/// class with the specified row and column of excel worksheet
/// </summary>
/// <param name="row">The row index of a cell</param>
/// <param name="column">The column index of a cell</param>
public Cell(long row, long column)
{
this.row = row;
this.column = column;
dataChange = true;
address = string.Empty;
columnAddress = string.Empty;
}
/// <summary>
/// Initializes a new instance of the XLS.Cell
/// class with the specified address of excel worksheet
/// </summary>
/// <param name="address">The adress of a cell</param>
public Cell(string address)
{
this.address = address;
dataChange = false;
row = GetRow(address);
columnAddress = GetColumnAddress(address);
column = GetColumn(columnAddress);
}
/// <summary>
/// Gets or sets the row of this XLS.Cell
/// </summary>
public long Row
{
get { return row <= 0 ? 1 : row; }
set { row = value; dataChange = true; }
}
/// <summary>
/// Gets or sets the column of this XLS.Cell
/// </summary>
public long Column
{
get { return column <= 0 ? 1 : column; }
set { column = value; dataChange = true; }
}
/// <summary>
/// Gets or sets the address of this XLS.Cell
/// </summary>
public string Address
{
get { return dataChange ? ToAddress() : address; }
set
{
address = value;
row = GetRow(address);
column = GetColumn(address);
}
}
/// <summary>
/// Gets the column address of this XLS.Cell
/// </summary>
public string ColumnAddress
{
get { return GetColumnAddress(Address); }
private set { columnAddress = value; }
}
#region Private Methods
private static long GetRow(string address)
{
return long.Parse(address.Substring(GetStartIndex(address)));
}
private static string GetColumnAddress(string address)
{
return address.Substring(0, GetStartIndex(address)).ToUpperInvariant();
}
private static long GetColumn(string columnAddress)
{
char[] characters = columnAddress.ToCharArray();
int sum = 0;
for (int i = 0; i < characters.Length; i++)
{
sum *= 26;
sum += (characters[i] - 'A' + 1);
}
return (long)sum;
}
private static int GetStartIndex(string address)
{
return address.IndexOfAny("123456789".ToCharArray());
}
private string ToAddress()
{
string indexToString = string.Empty;
if (Column > 26)
{
indexToString = ((char)(65 + (int)((Column - 1) / 26) - 1)).ToString();
}
indexToString += (char)(65 + ((Column - 1) % 26));
dataChange = false;
return indexToString + Row;
}
#endregion
}
}
}
回答by George K.
O24 has a column number and you want a name:
O24 有一个列号，您需要一个名称：
=LEFT(RIGHT(ADDRESS(1,O24),LEN(ADDRESS(1,O24))-1),FIND("$",RIGHT((ADDRESS(1,O24)),LEN(ADDRESS(1,O24))-1))-1)
=LEFT(RIGHT(ADDRESS(1,O24),LEN(ADDRESS(1,O24))-1),FIND("$",RIGHT((ADDRESS(1,O24)),LEN(ADDRESS(1,O24)) )-1))-1)
O37 has a column name and you want a number:
O37 有一个列名，你需要一个数字：
=COLUMN(INDIRECT(O37&1))
= 列（间接（O37&1））
回答by singapore saravanan
public static string GetColumnName(int index)
{
const string letters = "ZABCDEFGHIJKLMNOPQRSTUVWXY";
int NextPos = (index / 26);
int LastPos = (index % 26);
if (LastPos == 0) NextPos--;
if (index > 26)
return GetColumnName(NextPos) + letters[LastPos];
else
return letters[LastPos] + "";
}
