Jak przekonwertować numer kolumny (np. 127) do kolumny excel (np. AA)

Jeśli ktoś musi to zrobić w Excelu bez VBA, oto sposób:

=SUBSTITUTE(ADDRESS(1;colNum;4);"1";"")

Gdzie colNum jest numerem Kolumny

I w VBA:

Function GetColumnName(colNum As Integer) As String
    Dim d As Integer
    Dim m As Integer
    Dim name As String
    d = colNum
    name = ""
    Do While (d > 0)
        m = (d - 1) Mod 26
        name = Chr(65 + m) + name
        d = Int((d - m) / 26)
    Loop
    GetColumnName = name
End Function

Sorry, to jest Python zamiast C#, ale przynajmniej wyniki są poprawne:

def ColIdxToXlName(idx):
    if idx < 1:
        raise ValueError("Index is too small")
    result = ""
    while True:
        if idx > 26:
            idx, r = divmod(idx - 1, 26)
            result = chr(r + ord('A')) + result
        else:
            return chr(idx + ord('A') - 1) + result


for i in xrange(1, 1024):
    print "%4d : %s" % (i, ColIdxToXlName(i))

Możesz potrzebować konwersji w obie strony, np. z adresu kolumny Excela, takiego jak AAZ, na liczbę całkowitą i z dowolnej liczby całkowitej do Excela. Dwie poniższe metody właśnie to zrobią. Zakłada indeksowanie oparte na 1, pierwszym elementem w Twoich "tablicach" jest element numer 1. Nie ma ograniczeń co do wielkości tutaj, więc możesz użyć adresów takich jak błąd i to będzie numer kolumny 2613824 ...

public static string ColumnAdress(int col)
{
  if (col <= 26) { 
    return Convert.ToChar(col + 64).ToString();
  }
  int div = col / 26;
  int mod = col % 26;
  if (mod == 0) {mod = 26;div--;}
  return ColumnAdress(div) + ColumnAdress(mod);
}

public static int ColumnNumber(string colAdress)
{
  int[] digits = new int[colAdress.Length];
  for (int i = 0; i < colAdress.Length; ++i)
  {
    digits[i] = Convert.ToInt32(colAdress[i]) - 64;
  }
  int mul=1;int res=0;
  for (int pos = digits.Length - 1; pos >= 0; --pos)
  {
    res += digits[pos] * mul;
    mul *= 26;
  }
  return res;
}

Odkryłem błąd w moim pierwszym poście, więc postanowiłem usiąść i zrobić matematykę. Odkryłem, że system liczbowy używany do identyfikacji kolumn Excela nie jest systemem base 26, jak napisała inna osoba. Rozważmy następujące w bazie 10. Można to również zrobić za pomocą liter alfabetu.

Spacja:.........................S1, S2, S3 : S1, S2, S3
....................................0, 00, 000 :.. A, AA, AAA
....................................1, 01, 001 :.. B, AB, AAB
.................................... …, …, … :.. …, …, …
....................................9, 99, 999 :.. Z, ZZ, ZZZ
Stany całkowite w kosmosie: 10, 100, 1000 : 26, 676, 17576
Stany Ogółem:...............1110................18278

Excel numbers columns in the individual Alphabetic spaces using base 26. Możesz zobaczyć, że ogólnie, progresja przestrzeni stanów to a, a^2, a^3, ... dla pewnej bazy a, a całkowita liczba stanów to a + a^2 + a^3+....

Załóżmy, że chcesz znaleźć całkowitą liczbę stanów A w pierwszych n przestrzeniach. Wzór na to: a = (A) (A^N - 1) /(A-1). Jest to ważne, ponieważ musimy znaleźć przestrzeń N, która odpowiada naszemu indeksowi K. jeśli chcę dowiedzieć się, gdzie k leży w systemie liczbowym, muszę zastąpić A na k i rozwiązać dla N. rozwiązaniem jest n = log{baza A} (A (a-1)/A +1). Jeśli użyję przykładu a = 10 i K = 192, wiem, że N = 2,23804... . To mi mówi, że K leży na początku trzeciej przestrzeni, ponieważ jest nieco większa niż dwie.

Następnym krokiem jest ustalenie, jak daleko jesteśmy w obecnej przestrzeni. Aby to znaleźć, odjmij od K A wygenerowane za pomocą podłogi N. w tym przykładzie podłoga N jest równa dwóm. Więc, A = (10)(10^2 – 1)/(10-1) = 110, jak można się spodziewać po połączeniu Stanów dwóch pierwszych spacji. Należy to odjąć od K, ponieważ te pierwsze 110 Stanów byłoby już rozliczone w dwóch pierwszych przestrzeniach. To pozostawia USA z 82 Stanami. W tym systemie liczbowym reprezentacja 192 w bazie 10 wynosi 082.

Kod C# wykorzystujący indeks bazowy równy zero to

    private string ExcelColumnIndexToName(int Index)
    {
        string range = string.Empty;
        if (Index < 0 ) return range;
        int a = 26;
        int x = (int)Math.Floor(Math.Log((Index) * (a - 1) / a + 1, a));
        Index -= (int)(Math.Pow(a, x) - 1) * a / (a - 1);
        for (int i = x+1; Index + i > 0; i--)
        {
            range = ((char)(65 + Index % a)).ToString() + range;
            Index /= a;
        }
        return range;
    }

//Stary Post

Rozwiązanie oparte o zero w C#.

    private string ExcelColumnIndexToName(int Index)
    {
        string range = "";
        if (Index < 0 ) return range;
        for(int i=1;Index + i > 0;i=0)
        {
            range = ((char)(65 + Index % 26)).ToString() + range;
            Index /= 26;
        }
        if (range.Length > 1) range = ((char)((int)range[0] - 1)).ToString() + range.Substring(1);
        return range;
    }

int nCol = 127;
string sChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
string sCol = "";
while (nCol >= 26)
{
    int nChar = nCol % 26;
    nCol = (nCol - nChar) / 26;
    // You could do some trick with using nChar as offset from 'A', but I am lazy to do it right now.
    sCol = sChars[nChar] + sCol;
}
sCol = sChars[nCol] + sCol;

Aktualizacja: komentarz Piotra jest słuszny. To jest to, co dostaję za pisanie kodu w przeglądarce. :- ) Moim rozwiązaniem nie było kompilowanie, brakowało lewej-większości liter i budowano ciąg w odwrotnej kolejności - wszystko już naprawione.

Pomijając błędy, algorytm w zasadzie Konwertuje liczbę z bazy 10 do bazy 26.

Aktualizacja 2: Joel Coehoorn ma rację-powyższy kod zwróci AB za 27. Gdyby to była prawdziwa liczba bazy 26, AA byłaby równa A i następna liczba PO Z będzie BA.

int nCol = 127;
string sChars = "0ABCDEFGHIJKLMNOPQRSTUVWXYZ";
string sCol = "";
while (nCol > 26)
{
    int nChar = nCol % 26;
    if (nChar == 0)
        nChar = 26;
    nCol = (nCol - nChar) / 26;
    sCol = sChars[nChar] + sCol;
}
if (nCol != 0)
    sCol = sChars[nCol] + sCol;

Łatwo z rekurencją.

public static string GetStandardExcelColumnName(int columnNumberOneBased)
{
  int baseValue = Convert.ToInt32('A');
  int columnNumberZeroBased = columnNumberOneBased - 1;

  string ret = "";

  if (columnNumberOneBased > 26)
  {
    ret = GetStandardExcelColumnName(columnNumberZeroBased / 26) ;
  }

  return ret + Convert.ToChar(baseValue + (columnNumberZeroBased % 26) );
}

Ta odpowiedź jest w javaScript:

function getCharFromNumber(columnNumber){
    var dividend = columnNumber;
    var columnName = "";
    var modulo;

    while (dividend > 0)
    {
        modulo = (dividend - 1) % 26;
        columnName = String.fromCharCode(65 + modulo).toString() + columnName;
        dividend = parseInt((dividend - modulo) / 26);
    } 
    return  columnName;
}

Jestem zaskoczony, że wszystkie dotychczasowe rozwiązania zawierają albo iterację, albo rekurencję.

Oto moje rozwiązanie, które działa w stałym czasie (bez pętli). To rozwiązanie działa dla wszystkich możliwych kolumn programu Excel i sprawdza, czy dane wejściowe można przekształcić w kolumnę programu Excel. Możliwe kolumny mieszczą się w zakresie [A, XFD] lub [1, 16384]. (To zależy od wersji programu Excel)

private static string Turn(uint col)
{
    if (col < 1 || col > 16384) //Excel columns are one-based (one = 'A')
        throw new ArgumentException("col must be >= 1 and <= 16384");

    if (col <= 26) //one character
        return ((char)(col + 'A' - 1)).ToString();

    else if (col <= 702) //two characters
    {
        char firstChar = (char)((int)((col - 1) / 26) + 'A' - 1);
        char secondChar = (char)(col % 26 + 'A' - 1);

        if (secondChar == '@') //Excel is one-based, but modulo operations are zero-based
            secondChar = 'Z'; //convert one-based to zero-based

        return string.Format("{0}{1}", firstChar, secondChar);
    }

    else //three characters
    {
        char firstChar = (char)((int)((col - 1) / 702) + 'A' - 1);
        char secondChar = (char)((col - 1) / 26 % 26 + 'A' - 1);
        char thirdChar = (char)(col % 26 + 'A' - 1);

        if (thirdChar == '@') //Excel is one-based, but modulo operations are zero-based
            thirdChar = 'Z'; //convert one-based to zero-based

        return string.Format("{0}{1}{2}", firstChar, secondChar, thirdChar);
    }
}

..I przekonwertowane do php:

function GetExcelColumnName($columnNumber) {
    $columnName = '';
    while ($columnNumber > 0) {
        $modulo = ($columnNumber - 1) % 26;
        $columnName = chr(65 + $modulo) . $columnName;
        $columnNumber = (int)(($columnNumber - $modulo) / 26);
    }
    return $columnName;
}

Ta sama implementacja w Javie

public String getExcelColumnName (int columnNumber) 
    {     
        int dividend = columnNumber;   
        int i;
        String columnName = "";     
        int modulo;     
        while (dividend > 0)     
        {        
            modulo = (dividend - 1) % 26;         
            i = 65 + modulo;
            columnName = new Character((char)i).toString() + columnName;        
            dividend = (int)((dividend - modulo) / 26);    
        }       
        return columnName; 
    }  

Po przejrzeniu wszystkich dostarczonych tu wersji, postanowiłem zrobić jedną samodzielnie, używając rekurencji.

Oto mój vb.net Wersja:

Function CL(ByVal x As Integer) As String
    If x >= 1 And x <= 26 Then
        CL = Chr(x + 64)
    Else
        CL = CL((x - x Mod 26) / 26) & Chr((x Mod 26) + 1 + 64)
    End If
End Function

Trochę za późno na grę, ale oto kod, którego używam (w C#):

private static readonly string _Alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
public static int ColumnNameParse(string value)
{
    // assumes value.Length is [1,3]
    // assumes value is uppercase
    var digits = value.PadLeft(3).Select(x => _Alphabet.IndexOf(x));
    return digits.Aggregate(0, (current, index) => (current * 26) + (index + 1));
}

Wystarczy wrzucić prostą dwuliniową implementację C# używając rekurencji, ponieważ wszystkie odpowiedzi tutaj wydają się znacznie bardziej skomplikowane niż to konieczne.

/// <summary>
/// Gets the column letter(s) corresponding to the given column number.
/// </summary>
/// <param name="column">The one-based column index. Must be greater than zero.</param>
/// <returns>The desired column letter, or an empty string if the column number was invalid.</returns>
public static string GetColumnLetter(int column) {
    if (column < 1) return String.Empty;
    return GetColumnLetter((column - 1) / 26) + (char)('A' + (column - 1) % 26);
}

Jeśli chcesz go tylko dla Formuły komórki bez kodu, oto wzór na nią:

IF(COLUMN()>=26,CHAR(ROUND(COLUMN()/26,1)+64)&CHAR(MOD(COLUMN(),26)+64),CHAR(COLUMN()+64))

W Delphi (Pascal):

function GetExcelColumnName(columnNumber: integer): string;
var
  dividend, modulo: integer;
begin
  Result := '';
  dividend := columnNumber;
  while dividend > 0 do begin
    modulo := (dividend - 1) mod 26;
    Result := Chr(65 + modulo) + Result;
    dividend := (dividend - modulo) div 26;
  end;
end;

Chciałem dorzucić moją klasę statyczną, której używam, do wymiany między indeksem col i etykietą col. Używam zmodyfikowanej zaakceptowanej odpowiedzi dla mojej metody ColumnLabel

public static class Extensions
{
    public static string ColumnLabel(this int col)
    {
        var dividend = col;
        var columnLabel = string.Empty;
        int modulo;

        while (dividend > 0)
        {
            modulo = (dividend - 1) % 26;
            columnLabel = Convert.ToChar(65 + modulo).ToString() + columnLabel;
            dividend = (int)((dividend - modulo) / 26);
        } 

        return columnLabel;
    }
    public static int ColumnIndex(this string colLabel)
    {
        // "AD" (1 * 26^1) + (4 * 26^0) ...
        var colIndex = 0;
        for(int ind = 0, pow = colLabel.Count()-1; ind < colLabel.Count(); ++ind, --pow)
        {
            var cVal = Convert.ToInt32(colLabel[ind]) - 64; //col A is index 1
            colIndex += cVal * ((int)Math.Pow(26, pow));
        }
        return colIndex;
    }
}

Użyj tego jak...

30.ColumnLabel(); // "AD"
"AD".ColumnIndex(); // 30

private String getColumn(int c) {
    String s = "";
    do {
        s = (char)('A' + (c % 26)) + s;
        c /= 26;
    } while (c-- > 0);
    return s;
}

To nie jest dokładnie baza 26, Nie Ma 0 w systemie. Gdyby tak było, po 'z' następowałoby 'BA', a nie 'AA'.

Udoskonalenie oryginalnego rozwiązania (w C#):

public static class ExcelHelper
{
    private static Dictionary<UInt16, String> l_DictionaryOfColumns;

    public static ExcelHelper() {
        l_DictionaryOfColumns = new Dictionary<ushort, string>(256);
    }

    public static String GetExcelColumnName(UInt16 l_Column)
    {
        UInt16 l_ColumnCopy = l_Column;
        String l_Chars = "0ABCDEFGHIJKLMNOPQRSTUVWXYZ";
        String l_rVal = "";
        UInt16 l_Char;


        if (l_DictionaryOfColumns.ContainsKey(l_Column) == true)
        {
            l_rVal = l_DictionaryOfColumns[l_Column];
        }
        else
        {
            while (l_ColumnCopy > 26)
            {
                l_Char = l_ColumnCopy % 26;
                if (l_Char == 0)
                    l_Char = 26;

                l_ColumnCopy = (l_ColumnCopy - l_Char) / 26;
                l_rVal = l_Chars[l_Char] + l_rVal;
            }
            if (l_ColumnCopy != 0)
                l_rVal = l_Chars[l_ColumnCopy] + l_rVal;

            l_DictionaryOfColumns.ContainsKey(l_Column) = l_rVal;
        }

        return l_rVal;
    }
}

Oto Wersja Actionscript:

private var columnNumbers:Array = ['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'];

    private function getExcelColumnName(columnNumber:int) : String{
        var dividend:int = columnNumber;
        var columnName:String = "";
        var modulo:int;

        while (dividend > 0)
        {
            modulo = (dividend - 1) % 26;
            columnName = columnNumbers[modulo] + columnName;
            dividend = int((dividend - modulo) / 26);
        } 

        return columnName;
    }

JavaScript Solution

/**
 * Calculate the column letter abbreviation from a 1 based index
 * @param {Number} value
 * @returns {string}
 */
getColumnFromIndex = function (value) {
    var base = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'.split('');
    var remainder, result = "";
    do {
        remainder = value % 26;
        result = base[(remainder || 26) - 1] + result;
        value = Math.floor(value / 26);
    } while (value > 0);
    return result;
};

W Perlu, dla wejścia 1 (A), 27 (AA), itd.

sub excel_colname {
  my ($idx) = @_;       # one-based column number
  --$idx;               # zero-based column index
  my $name = "";
  while ($idx >= 0) {
    $name .= chr(ord("A") + ($idx % 26));
    $idx   = int($idx / 26) - 1;
  }
  return scalar reverse $name;
}

Oto moja super późna implementacja w PHP. Ten jest rekurencyjny. Napisałem go tuż przed tym, jak znalazłem ten post. Chciałem zobaczyć, czy inni już rozwiązali ten problem...

public function GetColumn($intNumber, $strCol = null) {

    if ($intNumber > 0) {
        $intRem = ($intNumber - 1) % 26;
        $strCol = $this->GetColumn(intval(($intNumber - $intRem) / 26), sprintf('%s%s', chr(65 + $intRem), $strCol));
    }

    return $strCol;
}

Próbuję zrobić to samo w Javie... Napisałem następujący kod:

private String getExcelColumnName(int columnNumber) {

    int dividend = columnNumber;
    String columnName = "";
    int modulo;

    while (dividend > 0)
    {
        modulo = (dividend - 1) % 26;

        char val = Character.valueOf((char)(65 + modulo));

        columnName += val;

        dividend = (int)((dividend - modulo) / 26);
    } 

    return columnName;
}

Teraz, gdy uruchomiłem go z columnNumber = 29, daje mi wynik = " CA "(zamiast " AC") jakieś uwagi co mi umyka? Wiem, że mogę to odwrócić przez StringBuilder.... Ale patrząc na odpowiedź Grahama, jestem trochę zdezorientowany....

Inny sposób VBA

Public Function GetColumnName(TargetCell As Range) As String
    GetColumnName = Split(CStr(TargetCell.Address), "$")(1)
End Function

Te moje kody do konwersji określonej liczby (indeks zaczyna się od 1) do kolumny Excel.

    public static string NumberToExcelColumn(uint number)
    {
        uint originalNumber = number;

        uint numChars = 1;
        while (Math.Pow(26, numChars) < number)
        {
            numChars++;

            if (Math.Pow(26, numChars) + 26 >= number)
            {
                break;
            }               
        }

        string toRet = "";
        uint lastValue = 0;

        do
        {
            number -= lastValue;

            double powerVal = Math.Pow(26, numChars - 1);
            byte thisCharIdx = (byte)Math.Truncate((columnNumber - 1) / powerVal);
            lastValue = (int)powerVal * thisCharIdx;

            if (numChars - 2 >= 0)
            {
                double powerVal_next = Math.Pow(26, numChars - 2);
                byte thisCharIdx_next = (byte)Math.Truncate((columnNumber - lastValue - 1) / powerVal_next);
                int lastValue_next = (int)Math.Pow(26, numChars - 2) * thisCharIdx_next;

                if (thisCharIdx_next == 0 && lastValue_next == 0 && powerVal_next == 26)
                {
                    thisCharIdx--;
                    lastValue = (int)powerVal * thisCharIdx;
                }
            }

            toRet += (char)((byte)'A' + thisCharIdx + ((numChars > 1) ? -1 : 0));

            numChars--;
        } while (numChars > 0);

        return toRet;
    }

Mój Test Jednostkowy:

    [TestMethod]
    public void Test()
    {
        Assert.AreEqual("A", NumberToExcelColumn(1));
        Assert.AreEqual("Z", NumberToExcelColumn(26));
        Assert.AreEqual("AA", NumberToExcelColumn(27));
        Assert.AreEqual("AO", NumberToExcelColumn(41));
        Assert.AreEqual("AZ", NumberToExcelColumn(52));
        Assert.AreEqual("BA", NumberToExcelColumn(53));
        Assert.AreEqual("ZZ", NumberToExcelColumn(702));
        Assert.AreEqual("AAA", NumberToExcelColumn(703));
        Assert.AreEqual("ABC", NumberToExcelColumn(731));
        Assert.AreEqual("ACQ", NumberToExcelColumn(771));
        Assert.AreEqual("AYZ", NumberToExcelColumn(1352));
        Assert.AreEqual("AZA", NumberToExcelColumn(1353));
        Assert.AreEqual("AZB", NumberToExcelColumn(1354));
        Assert.AreEqual("BAA", NumberToExcelColumn(1379));
        Assert.AreEqual("CNU", NumberToExcelColumn(2413));
        Assert.AreEqual("GCM", NumberToExcelColumn(4823));
        Assert.AreEqual("MSR", NumberToExcelColumn(9300));
        Assert.AreEqual("OMB", NumberToExcelColumn(10480));
        Assert.AreEqual("ULV", NumberToExcelColumn(14530));
        Assert.AreEqual("XFD", NumberToExcelColumn(16384));
    }

Sorry, to jest Python zamiast C#, ale przynajmniej wyniki są poprawne:

def excel_column_number_to_name(column_number):
    output = ""
    index = column_number-1
    while index >= 0:
        character = chr((index%26)+ord('A'))
        output = output + character
        index = index/26 - 1

    return output[::-1]


for i in xrange(1, 1024):
    print "%4d : %s" % (i, excel_column_number_to_name(i))

Zdałem te testy:

• numer kolumny: 494286 = > ABCDZ
• numer kolumny: 27 => AA
• numer kolumny: 52 = > AZ

Używam tego w VB.NET 2003 i działa dobrze...

Private Function GetExcelColumnName(ByVal aiColNumber As Integer) As String
    Dim BaseValue As Integer = Convert.ToInt32(("A").Chars(0)) - 1
    Dim lsReturn As String = String.Empty

    If (aiColNumber > 26) Then
        lsReturn = GetExcelColumnName(Convert.ToInt32((Format(aiColNumber / 26, "0.0").Split("."))(0)))
    End If

    GetExcelColumnName = lsReturn + Convert.ToChar(BaseValue + (aiColNumber Mod 26))
End Function

Inne rozwiązanie:

private void Foo()
{
   l_ExcelApp = new Excel.ApplicationClass();
   l_ExcelApp.ReferenceStyle = Excel.XlReferenceStyle.xlR1C1;
   // ... now reference by R[row]C[column], Ex. A1 <==> R1C1, C6 <==> R3C6, ...
}

Zobacz więcej tutaj- komórka w Excelu dla każdego! autor: Dr Nitin Paranjape

public static string ConvertToAlphaColumnReferenceFromInteger(int columnReference)
    {
        int baseValue = ((int)('A')) - 1 ;
        string lsReturn = String.Empty; 

        if (columnReference > 26) 
        {
            lsReturn = ConvertToAlphaColumnReferenceFromInteger(Convert.ToInt32(Convert.ToDouble(columnReference / 26).ToString().Split('.')[0]));
        } 

        return lsReturn + Convert.ToChar(baseValue + (columnReference % 26));            
    }