How can I get the Column Name as integer Value?

Question

I have 4 header columns in an Excel Workbook ,example :
CustomerID Account Name Date Name

How do I read back the only Column #...... Example CustomerID is column "1", Account Name is "2", Date is "3"
I do not want the cell references e.g. A1, B1,C1
Can someone tell me how to get the number value of the Column position?
thanks
J

j l · Answer

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 { sum *= 26 ; sum += (characters[i] - 'A' + 1 ); } return sum; // in this example sum would be 1 representing the column # where Customer Name resides }

Sam Hobbs · Answer

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 l · Answer

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

Sebastian · Answer

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.