LoginSignup
5
2

More than 5 years have passed since last update.

エクセル列の数値変換(26進数変換)

Last updated at Posted at 2018-11-02

はじめに

普段26進数なんて意識しませんが、プログラムからExcelを操作する時に、何列目?って26進数変換します。

ファイル形式によって扱える最大列数の表

ファイル形式 最大列数
Excel(xlsx) XFD(16384列)
Excel97-2003(xls) IV(256列)

実装例

C#による実装

///<summary>10進数から26進数を求める</summary>
public static string ToBase26(this long self)
{
    if (self <= 0) return "";

    long n = (self % 26 == 0) ? 26 : self % 26;
    if (self == n) return ((char)(n + 64)).ToString();
    return ((self - n) / 26).ToBase26() + ((char)(n + 64)).ToString();
}
///<summary>26進数から10進数を求める</summary>
public static long FromBase26(this string self)
{
    if (string.IsNullOrEmpty(self)) return 0;

    long result = 0;
    char[] chars = self.ToCharArray();
    int len = self.Length - 1;
    foreach (var c in chars)
    {
        int asc = (int)c - 64;
        if (asc < 1 || asc > 26) return 0;
        result += asc * (long)Math.Pow((double)26, (double)len--);
    }
    return result;
}

VB.NETによる実装

    '''<summary>10進数から26進数を求める</summary>
    <Extension()>
    Public Shared Function ToBase26(ByVal self As Long) As String
        If self <= 0 Then Return ""
        Dim n As Long = If((self Mod 26 = 0), 26, self Mod 26)
        If self = n Then Return (ChrW((n + 64))).ToString()
        Return ((self - n) / 26).ToBase26() & (ChrW((n + 64))).ToString()
    End Function

    '''<summary>26進数から10進数を求める</summary>
    <Extension()>
    Public Shared Function FromBase26(ByVal self As String) As Long
        If String.IsNullOrEmpty(self) Then Return 0
        Dim result As Long = 0
        Dim chars As Char() = self.ToCharArray()
        Dim len As Integer = self.Length - 1

        For Each c In chars
            Dim asc As Integer = CInt(c) - 64
            If asc < 1 OrElse asc > 26 Then Return 0
            result += asc * CLng(Math.Pow(CDbl(26), CDbl(Math.Max(System.Threading.Interlocked.Decrement(len), len + 1))))
        Next

        Return result
    End Function
5
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
5
2