Other day I needed to convert excel spreadsheet column name to column index when having 200+ columns is easier to express as ‘FB’ instead of 157
A = 0 B = 1 AA = 26 AA = 27 FB = 157
This code will work for any number of column names.
/** * Convert given excel column name to column Index, ex 'A=0', 'AA=26' * @param columnName * @return 0 based index of the column */ private static short convert2ColumnIndex(String columnName) { columnName = columnName.toUpperCase(); short value = 0; for (int i = 0, k = columnName.length() - 1; i < columnName.length(); i++, k--) { int alpabetIndex = ((short) columnName.charAt(i)) - 64; int delta = 0; // last column simply add it if (k == 0) { delta = alpabetIndex - 1; } else { // aggregate if (alpabetIndex == 0) delta = (26 * k); else delta = (alpabetIndex * 26 * k); } value += delta; } return value; } |
Converting from index to column name
This process is trivial we simply keep on taking mod 26 from index till we have nothing left, and converting that value to char.
Actually your code is incorrect, I believe it at most works for length 2.
It is as simple as converting from index to column name.
See the modified version below.
public static int convert2ColumnIndex(String columnName) {
columnName = columnName.toUpperCase();
int value = 0;
for (int i = 0; i < columnName.length(); i++) {
int delta = ( columnName.charAt(i)) – 64;
value = value*26+ delta;
}
return value-1;
}
Thanks, I checked the code works as expected for more than 2 columns, not really sure why I did it that way.
Or you can count with base 26.
int columnIndex = 0;
Regex regex = new Regex(“[A-Za-z]”);
foreach (char eachCharaterIndex in regex.Match(columnNameIndex).Value.ToLower().ToCharArray())
{
columnIndex = (eachCharaterIndex % 32) + columnIndex;
}
return columnIndex;
private int ExcelColumnIndex(string columnNameIndex)
{
int columnIndex = 0;
Regex regex = new Regex(“[A-Za-z]”);
foreach (char eachCharaterIndex in regex.Match(columnNameIndex).Value.ToLower().ToCharArray())
{
columnIndex = (eachCharaterIndex % 32) + columnIndex;
}
return columnIndex;
}