EXCEL列名与数字之间转换

来源：互联网发布：手机赌博软件开发编辑：程序博客网时间：2024/05/14 13:34

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace SDLearn1 {
public partial class LearnForm : Form {

private const int AlphaSpan = 26; // 英文字母数

        public LearnForm() {
            InitializeComponent();
        }

        private void CalcButton_Click(object sender, EventArgs e) {
            var source = this.EntryTextBox.Text;
            try {
                if(string.IsNullOrEmpty(source) || source.Trim() == "") {
                    MessageBox.Show("入力がありません", "エラー", MessageBoxButtons.OK, MessageBoxIcon.Error);
                } else if(IsNumericOnly(source.Trim())) {
                    // 数字のみ

                    var result = GetColumnName(source.Trim());
                    MessageBox.Show(result);
                } else if(IsAlphaOnly(source.Trim())) {
                    // アルファベットのみ

        /// <summary>
        /// 全部数字か
        /// </summary>
        /// <param name="source"></param>
        /// <returns></returns>
        private bool IsNumericOnly(string source) {

            foreach(var ch in source) {
                if(ch < '0' || ch > '9') {
                    return false;
                }
            }

return true;
}

        /// <summary>
        /// 全部アルファベットか
        /// </summary>
        /// <param name="source"></param>
        /// <returns></returns>
        private bool IsAlphaOnly(string source) {

            foreach(var ch in source.ToUpper()) {
                if(ch < 'A' || ch > 'Z') {
                    return false;
                }
            }

return true;
}

        /// <summary>
        /// カラム番号の取得
        /// </summary>
        /// <param name="source">カラム名を表す文字列</param>
        /// <returns>変換したカラム番号</returns>
        private string GetColumnNumber(string source) {

var s = source.ToUpper().Trim();

            if(s.Length >= 5) {
                // 桁数が多い
                throw new ArgumentOutOfRangeException("source");
            }

            int no = 0;
            foreach(var ch in s) {
                var n = ch - 'A';

no = no * AlphaSpan + n + 1;
}

return no.ToString();
}

        /// <summary>
        /// カラム名の取得
        /// </summary>
        /// <param name="source">カラム番号を表す文字列</param>
        /// <returns>変換したカラム名</returns>
        private string GetColumnName(string source) {

            int s;
            if(!int.TryParse(source, out s)) {
                throw new ArgumentException();
            }

            if(s <= 0){
                // 0以下は対象外
                throw new ArgumentOutOfRangeException("source");
            }

            string name = "";
            while(s > 0) {
                s--;
                int rest = s % AlphaSpan;

                name = (char)(rest + 'A') + name;
                s = s / AlphaSpan;
            }

            return name;
        }
    }
}