Sometimes you need to loop cells in Excel. In fact it is looping rows and columns, because cells have two dimensions. The rows are counted up, but the columns are named alphabetic. Looping them is not that easy and gets harder until you hit the
Z, because the next 'letter' would be
AA. So a number would be more easy.
So here is a function to parse the letter into a number:
1
2
3
4
5
6
7
8
9
10
11
12
| public static int NumberFromExcelColumn(string column)
{
int retVal = 0;
string col = column.ToUpper();
for (int iChar = col.Length - 1; iChar >= 0; iChar--)
{
char colPiece = col[iChar];
int colNum = colPiece - 64;
retVal = retVal + colNum * (int)Math.Pow(26, col.Length - (iChar + 1));
}
return retVal;
}
|
After working with the number you would like to display the result. But nobody would understand the number.. soooo parse it back into a letter:
1
2
3
4
5
6
7
8
9
10
11
12
13
| public static string ExcelColumnFromNumber(int column)
{
string columnString = "";
decimal columnNumber = column;
while (columnNumber > 0)
{
decimal currentLetterNumber = (columnNumber - 1) % 26;
char currentLetter = (char)(currentLetterNumber + 65);
columnString = currentLetter + columnString;
columnNumber = (columnNumber - (currentLetterNumber + 1)) / 26;
}
return columnString;
}
|
Comments
Post a Comment