我在想将Excel表格中的列名转换成数字的最佳方法是什么。
我正在使用Excel Package,这是一个很好的库,用于处理 .xlsx 文档。但是这个库不幸没有包括此功能。
OBS:在这个库中,第一列 A 对应数字1。
我在想将Excel表格中的列名转换成数字的最佳方法是什么。
我正在使用Excel Package,这是一个很好的库,用于处理 .xlsx 文档。但是这个库不幸没有包括此功能。
OBS:在这个库中,第一列 A 对应数字1。
该函数应适用于任意长度的列名。
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;
}
我几个月前就遇到了这个问题。反向操作-从列索引到列名称-也很有趣,如果你尝试使用零基索引解决它而没有意识到这会使事情变得复杂,那么结果将变得非常混乱。如果它是一个正常的多项数字系统,那么它可能会变得非常简单...
以下是我解决该问题的简化版本,作为扩展方法,没有错误处理和所有那些东西。
public static Int32 ToOneBasedIndex(this String name)
{
return name.ToUpper().
Aggregate(0, (column, letter) => 26 * column + letter - 'A' + 1);
}
我已经使用这个技术一段时间了,发现它在处理超出A-Z甚至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));
}
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();
}
源代码:
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
}
}
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] + "";
}
O24有一个列号,你想要一个名称:
=LEFT(RIGHT(ADDRESS(1,O24),LEN(ADDRESS(1,O24))-1),FIND("$",RIGHT((ADDRESS(1,O24)),LEN(ADDRESS(1,O24))-1))-1)
O37有一个列名,你想要一个数字:
=COLUMN(INDIRECT(O37&1))
name
)是大写字母时才能按预期工作。(请将name[i]
替换为name.ToUpper()[i]
以修复此问题。) - Marcus Mangelsdorfpow
变量的需求。这意味着你可以使用正常的i = 0;i < L;i ++
循环,这很好。 - Ethan Maness