How can I get the Column Name as integer Value?

Thanks Sam for the response. I have a temporary solution for my issue with finding the Column Number (integer value) from a Column Letter. Here is an Excel /C# example for spreadsheet called "mypath"

A1                              B1               C1      D1         E1
Customer Name       Address      State   City      CustomerID

//create the Excel application object
// also remember to include your Microsoft.Interop.Excel reference to the project!
Microsoft.Office.Interop.Excel._Application excelApp = new Microsoft.Office.Interop.Excel.Application();

string myPath = @"C:\Excel.xls";
excelApp.Workbooks.Open(myPath); // open the excel file mypath
int rowIndex = 2; int colIndex = 0;

Microsoft.Office.Interop.Excel.Range namedRange1 = excelApp.Range["A1:IV1"]; // search these header rows in my worksheet

//search for the row header "customer name"
Microsoft.Office.Interop.Excel.Range Range1 = namedRange1.Find("Customer Name", Type.Missing, Type.Missing,
Microsoft.Office.Interop.Excel.XlLookAt.xlWhole, Microsoft.Office.Interop.Excel.XlSearchOrder.xlByColumns,
Microsoft.Office.Interop.Excel.XlSearchDirection.xlNext, false, false, Type.Missing);

// Return to the first occurrence of the search
Range1 = namedRange1.Find(Range1);

string straddrss = ""; // initialize the string

straddrss = Range1.get_Address(); //get the address of the cell where the match was found , e.g. $A $1

char[] delimiterChars = { '$' }; //parse by $

string[] words = straddrss.Split(delimiterChars); //return each parsed string

straddrss = words[1]; //return the column letter from the array

colIndex = ExcelColumnNameToNumber(straddrss);   //call procedure to convert the column letter to a integer (number), AND then return it into the variable colIndex
                                                //colIndex now represents the column name as a number

public static int ExcelColumnNameToNumber(string columnName)
{
if (string.IsNullOrEmpty(columnName)) throw new ArgumentNullException("columnName");
char[] characters = columnName.ToUpperInvariant().ToCharArray();
int sum = 0;
for (int i = 0; i < characters.Length; i++)
{
sum *= 26;
sum += (characters[i] - 'A' + 1);
}
return sum; // in this example, sum would be "1" representing the column # where Customer Name resides
}

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

j l16y ago
Thanks Sam for the response. I have a temporary solution for my issue with finding the Column Number (integer value) from a Column Letter. Here is an Excel /C# example for spreadsheet called "mypath"

A1                              B1               C1      D1         E1
Customer Name       Address      State   City      CustomerID

//create the Excel application object
// also remember to include your Microsoft.Interop.Excel reference to the project!
Microsoft.Office.Interop.Excel._Application excelApp = new Microsoft.Office.Interop.Excel.Application();

string myPath = @"C:\Excel.xls";
excelApp.Workbooks.Open(myPath); // open the excel file mypath
int rowIndex = 2; int colIndex = 0;

Microsoft.Office.Interop.Excel.Range namedRange1 = excelApp.Range["A1:IV1"]; // search these header rows in my worksheet

//search for the row header "customer name"
Microsoft.Office.Interop.Excel.Range Range1 = namedRange1.Find("Customer Name", Type.Missing, Type.Missing,
Microsoft.Office.Interop.Excel.XlLookAt.xlWhole, Microsoft.Office.Interop.Excel.XlSearchOrder.xlByColumns,
Microsoft.Office.Interop.Excel.XlSearchDirection.xlNext, false, false, Type.Missing);

// Return to the first occurrence of the search
Range1 = namedRange1.Find(Range1);

string straddrss = ""; // initialize the string

straddrss = Range1.get_Address(); //get the address of the cell where the match was found , e.g. $A $1

char[] delimiterChars = { '$' }; //parse by $

string[] words = straddrss.Split(delimiterChars); //return each parsed string

straddrss = words[1]; //return the column letter from the array

colIndex = ExcelColumnNameToNumber(straddrss);   //call procedure to convert the column letter to a integer (number), AND then return it into the variable colIndex
                                                //colIndex now represents the column name as a number

public static int ExcelColumnNameToNumber(string columnName)
{
if (string.IsNullOrEmpty(columnName)) throw new ArgumentNullException("columnName");
char[] characters = columnName.ToUpperInvariant().ToCharArray();
int sum = 0;
for (int i = 0; i < characters.Length; i++)
{
sum *= 26;
sum += (characters[i] - 'A' + 1);
}
return sum; // in this example, sum would be "1" representing the column # where Customer Name resides
}
Sam Hobbs16y ago
Based on what? What do you need the column number for? What is the input? Is the input a cell, and you need to know the column number of the cell or is the input the ccolumn name?
j l16y ago
thanks for the reply!

I am actually using the Microsoft Interop Library with Visual Studio.

Do you know how I would use the Interop library to get the column number?

J
Sebastian16y ago
If you are accessing the it via ADO.Net, IDataReader's GetOrdinal(string name) and the opposite GetName(int i) with FieldCount should help. Though, I don't know if an Excel DataReader implements those methods but it should be worth a try.