Tuesday, December 16, 2008

SpreadsheetML - Convert column integer index to column alpha value(i.e. A, AA, BH)

Here's a quick snippet I found in a forum post at http://stackoverflow.com/questions/181596/how-to-convert-a-column-number-eg-127-into-an-excel-column-eg-aa. Many times in Excel I want to use a column index value instead of the letter representation(i.e A, AA, BH). I don't have control of the Excel so I can't change the representation. This method returns the letter representation from an integer column value.

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


Schaapie said...

Yeah, finally one that works.
Have been searching all day.


ElGrillo said...

Thank you for this code snippet.