Excel列序号助手

在处理Excel文件时，通常要获取xx列的数据，这就要求先找到列序号（从0开始）。

    /// <summary>
    /// Excel列序号助手
    /// </summary>
    public class ExcelColumnNumberHelper
    {
        private static string[] COL_ABCs = { 
            "A", "B", "C", "D", "E", "F", "G", "H", "I", "J"
          , "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T"
          , "U", "V", "W", "X", "Y", "Z" 
        };

        /// <summary>
        /// 获取Excel列序号名称
        /// </summary>
        /// <param name="columnNumber"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public static string GetColumnNumberName(int columnNumber) {
            // 序号值是0-16383
            if (!(-1 < columnNumber && columnNumber < 16384))
            {
                throw new Exception("Excel列序号值必须在0-16383的区间（包含两端）");
            }
            int quyu26 = columnNumber % 26;
            string right0 = COL_ABCs[quyu26];

            if (columnNumber < 26) {
                return right0;
            }

            string right1 = "";
            string right2 = "";

            // 大于等于26，且小于 702
            if (columnNumber < 702)
            {
                double beishu01 = columnNumber * 1.0 / 26;
                int abcIndex1 = (int)Math.Floor(beishu01) - 1;

                right1 = COL_ABCs[abcIndex1];
            }
            // 大于等于 702，且小于 16384
            if (columnNumber > 701) {
                double beishu02 = columnNumber * 1.0 / 676;// 676 == 26 * 26
                int abcIndex02 = (int)Math.Floor(beishu02) - 1;
                right2 = COL_ABCs[abcIndex02];

                double _beishu01 = (columnNumber - Math.Floor(beishu02) * 676 ) * 1.0 / 26;
                int _abcIndex1 = (int)Math.Floor(_beishu01) - 1;

                right1 = COL_ABCs[_abcIndex1];

            }
            
            return right2 + right1 + right0;

        }

        /// <summary>
        /// 获取Excel列序号
        /// </summary>
        /// <param name="columnNumberName"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public static int GetColumnNumber(string columnNumberName)
        {
            columnNumberName = string.IsNullOrEmpty(columnNumberName) ? "" : columnNumberName.Trim().ToUpper();
            int nameLength  = columnNumberName.Length;

            // 获取列序号字典
            Dictionary<string, int> columnNumberDic = new Dictionary<string, int>();
            int abcLen = COL_ABCs.Length;
            for (int ind = 0; ind < abcLen; ind++) {
                columnNumberDic[COL_ABCs[ind]] = ind;
            }

            switch (nameLength) {
                case 1: {
                        return columnNumberDic[columnNumberName];
                    };
                case 2:
                    {
                        string str1 = Convert.ToString(columnNumberName[0]);
                        string str0 = Convert.ToString(columnNumberName[1]);
                        int _right1 = columnNumberDic[str1];
                        int right1 = (_right1 + 1) * 26;
                        int right0 = columnNumberDic[str0];

                        return right1 + right0;
                    };
                case 3:
                    {
                        string str2 = Convert.ToString(columnNumberName[0]);
                        string str1 = Convert.ToString(columnNumberName[1]);
                        string str0 = Convert.ToString(columnNumberName[2]);

                        int _right2 = columnNumberDic[str2];
                        int _right1 = columnNumberDic[str1];

                        int right2 = (_right2 + 1) * 676;
                        int right1 = (_right1 + 1) * 26;
                        int right0 = columnNumberDic[str0];

                        int _columnNumber = right2 + right1 + right0;

                        if (_columnNumber > 16383) {
                            throw new Exception("Excel列序号名称最大是XFD");
                        }

                        return _columnNumber;

                    }
                default: {
                        throw new Exception("Excel列序号名称值必须是1到3位字符串");
                    }
            };

        }
    }