Excel列名转换
这段时间工作中需要用到Excel的列名转换,如列A,转为1或索引0,AA转为27或索引26。于是开发了个代码解决这个问题,简单记录下来。以C#代码为例:
/// <summary>/// 电子表格相关转换工具/// </summary>public class ColumnConvert{/// <summary>/// Excel列26进制/// </summary>private static readonly int ColumnRadix = 26;/// <summary>/// 列名转数字,从1开始/// </summary>/// <param name="column">列名</param>/// <returns>列编号</returns>public static int GetColumnNumber(string column){int res = 0;int power = 1;var col = column.ToUpper();for (int i = col.Length - 1; i >= 0; i--){char now = col[i];int diff = now - 'A';res += (diff + 1) * power;power *= ColumnRadix;}return res;}/// <summary>/// 列名转索引,从0开始/// </summary>/// <param name="column">列名</param>/// <returns>列索引</returns>public static int GetColumnIndex(string column){return GetColumnNumber(column) - 1;}/// <summary>/// 列号转列名,从1开始/// </summary>/// <param name="colNum">列号</param>/// <returns></returns>public static string GetColumnName(int colNum){StringBuilder res = new StringBuilder();int remain = (colNum - 1) % ColumnRadix;char addChar = (char)('A' + remain);res = res.Insert(0, addChar);colNum = (colNum - 1) / ColumnRadix;while (colNum >= 1){int left = (colNum - 1) % ColumnRadix;char add = (char)('A' + left);res = res.Insert(0, add);colNum = (colNum - 1) / ColumnRadix;}return res.ToString();}}