Come convertire un numero di colonna (ad esempio 127) in una colonna excel (ad esempio AA)

Come si converte un numero numerico in un nome di colonna di Excel in C # senza utilizzare l’automazione ottenendo il valore direttamente da Excel.

Excel 2007 ha un intervallo ansible compreso tra 1 e 16384, ovvero il numero di colonne che supporta. I valori risultanti dovrebbero essere sotto forma di nomi di colonne excel, ad esempio A, AA, AAA ecc.

Ecco come lo faccio:

private string GetExcelColumnName(int columnNumber) { int dividend = columnNumber; string columnName = String.Empty; int modulo; while (dividend > 0) { modulo = (dividend - 1) % 26; columnName = Convert.ToChar(65 + modulo).ToString() + columnName; dividend = (int)((dividend - modulo) / 26); } return columnName; } 

Se qualcuno ha bisogno di farlo in Excel senza VBA, ecco un modo:

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

dove colNum è il numero della colonna

E in 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 

Siamo spiacenti, questo è Python invece di C #, ma almeno i risultati sono corretti:

 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)) 

Potrebbe essere necessaria la conversione in entrambi i modi, ad esempio dall’indirizzo di colonna di Excel come AAZ a intero e da qualsiasi intero a Excel. I due metodi seguenti faranno proprio questo. Suppone 1 indicizzazione basata, il primo elemento nei tuoi “array” è l’elemento numero 1. Nessun limite di dimensione qui, quindi puoi usare indirizzi come ERRORE e quello sarebbe il numero di colonna 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; } 

Ho scoperto un errore nel mio primo post, quindi ho deciso di sedermi e fare i conti. Quello che ho trovato è che il sistema numerico utilizzato per identificare le colonne di Excel non è un sistema di base 26, come un’altra persona ha pubblicato. Considera quanto segue nella base 10. Puoi anche farlo con le lettere dell’alfabeto.

Spazio: ……………………. S1, S2, S3: S1, S2, S3
……………………………… 0, 00, 000: .. A, AA, AAA
……………………………… 1, 01, 001: .. B, AB, AAB
……………………………… …, …, …: .. …, …, …
……………………………… 9, 99, 999: .. Z, ZZ, ZZZ
Stati totali nello spazio: 10, 100, 1000: 26, 676, 17576
Stati totali: …………… 1110 ……………. 18278

Le colonne dei numeri di Excel nei singoli spazi alfabetici utilizzando la base 26. Puoi vedere che in generale, la progressione dello spazio di stato è a, a ^ 2, a ^ 3, … per qualche base a, e il numero totale di stati è a + a ^ 2 + a ^ 3 + ….

Supponiamo di voler trovare il numero totale di stati A nei primi N spazi. La formula per farlo è A = (a) (a ^ N – 1) / (a-1). Questo è importante perché abbiamo bisogno di trovare lo spazio N che corrisponde al nostro indice K. Se voglio scoprire dove K giace nel sistema numerico ho bisogno di sostituire A con K e risolvere per N. La soluzione è N = log { base a} (A (a-1) / a +1). Se uso l’esempio di a = 10 e K = 192, so che N = 2.23804 …. Questo mi dice che K si trova all’inizio del terzo spazio poiché è poco più grande di due.

Il prossimo passo è trovare esattamente quanto siamo nello spazio attuale. Per trovarlo, sottrai da K la A generata usando il piano di N. In questo esempio, il pavimento di N è due. Quindi, A = (10) (10 ^ 2 – 1) / (10-1) = 110, come previsto quando si combinano gli stati dei primi due spazi. Questo deve essere sottratto da K perché questi primi 110 stati sarebbero già stati presi in considerazione nei primi due spazi. Questo ci lascia con 82 stati. Quindi, in questo sistema numerico, la rappresentazione di 192 nella base 10 è 082.

Il codice C # utilizzando un indice di base di zero è

  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; } 

// Old Post

Una soluzione a base zero in 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; 

Aggiornamento : il commento di Peter è giusto. Questo è quello che ottengo per scrivere codice nel browser. 🙂 La mia soluzione non si stava compilando, mancava la lettera più a sinistra e stava costruendo la stringa in ordine inverso – tutto ora risolto.

Bug a parte, l’algoritmo è in pratica la conversione di un numero dalla base 10 alla base 26.

Aggiornamento 2 : Joel Coehoorn ha ragione – il codice sopra restituirà AB per 27. Se fosse il numero base reale 26, AA sarebbe uguale ad A e il numero successivo dopo Z sarebbe 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; 

Facile con la ricorsione.

 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) ); } 

Questa risposta è in 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; } 

Sono sorpreso che tutte le soluzioni finora contengano iterazione o ricorsione.

Ecco la mia soluzione che funziona in tempo costante (senza loop). Questa soluzione funziona per tutte le possibili colonne di Excel e controlla che l’input possa essere trasformato in una colonna di Excel. Le possibili colonne sono comprese nell’intervallo [A, XFD] o [1, 16384]. (Dipende dalla tua versione di 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); } } 

..E convertito in php:

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

Stessa implementazione in Java

 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; } 

Dopo aver guardato tutte le Versioni fornite qui, ho deciso di farne una io, usando la ricorsione.

Ecco la mia versione di vb.net:

 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 

Un po ‘tardi per il gioco, ma ecco il codice che uso (in 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)); } 

se lo vuoi solo per una formula cellulare senza codice, ecco una formula per questo:

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

In 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; 

Volevo inserire la mia class statica che utilizzo, per interporre tra col index e col Label. Io uso una risposta accettata modificata per il mio metodo 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; } } 

Usa questo come ...

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

Semplicemente lanciando una semplice implementazione in C # a due righe usando la ricorsione, perché tutte le risposte qui sembrano molto più complicate del necessario.

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

Non è esattamente base 26, non c’è 0 nel sistema. Se c’era, ‘Z’ sarebbe seguito da ‘BA’ non da ‘AA’.

Perfezionamento della soluzione originale (in C #):

 public static class ExcelHelper { private static Dictionary l_DictionaryOfColumns; public static ExcelHelper() { l_DictionaryOfColumns = new Dictionary(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; } } 

Ecco una versione di 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; } 

Soluzione JavaScript

 /** * 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; }; 

In perl, per un input di 1 (A), 27 (AA), ecc.

 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; } 

Ecco la mia super implementazione in ritardo in PHP. Questo è ricorsivo. L’ho scritto poco prima di trovare questo post. Volevo vedere se altri avevano già risolto questo problema …

 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; } 

Sto cercando di fare la stessa cosa in Java … Ho scritto il seguente codice:

 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; } 

Ora una volta che l’ho eseguito con columnNumber = 29, mi dà il risultato = “CA” (invece di “AC”) qualsiasi commento cosa mi manca? So di poterlo invertire con StringBuilder …. Ma guardando la risposta di Graham, sono un po ‘confuso ….

Un altro modo VBA

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

Questi miei codici consentono di convertire un numero specifico (indice iniziale da 1) in colonna 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; } 

Il mio test delle unità:

  [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)); } 

Siamo spiacenti, questo è Python invece di C #, ma almeno i risultati sono corretti:

 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)) 

Ha superato questi casi di test:

  • Numero colonna: 494286 => ABCDZ
  • Numero colonna: 27 => AA
  • Numero colonna: 52 => AZ

Sto usando questo in VB.NET 2003 e funziona bene …

 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 

Un’altra soluzione:

 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, ... } 

vedere di più qui – Cell reference in Excel per tutti! dal dott. 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)); }