Microsoft Interop: Excel Column Names

Question

I am using Microsoft Interop to read the data.

In excel-sheet the column-names are like A,B,C,D,....,AA,AB,.... and so on. Is there any way to read this column-names?

If you need any other info please let me know.

Regards, Priyank

What do you mean, "read the column names"? Do you want to know which column your in? — Jetti, Mar 15 '12 at 14:06
@Jetti: yes i want to read the column names and display to the user in a dropdown list. — Priyank Thakkar, Mar 15 '12 at 14:08
By column names...you mean what is in row 1 for each column, correct? — Jetti, Mar 15 '12 at 14:13
@Jetti: Nope not in the row 1, but what acutally the excel displays for each column. e.g. A,B,C — Priyank Thakkar, Mar 15 '12 at 14:14
Take a look at my answer, it will give you the letter names for each column — Jetti, Mar 15 '12 at 14:22

score 15 · Accepted Answer · answered Mar 15 '12 at 14:22

15

     Excel.Application xlApp = new Excel.Application();
     Excel.Workbook xlWorkbook = xlApp.Workbooks.Open("workbookname");
     Excel.Worksheet xlWorksheet = xlWorkbook.Sheets[1]; // assume it is the first sheet
     int columnCount = xlWorksheet.UsedRange.Columns.Count;
     List<string> columnNames = new List<string>();
     for (int c = 1; c < columnCount; c++)
     {
         if (xlWorksheet.Cells[1, c].Value2 != null)
         {
             string columnName = xlWorksheet.Columns[c].Address;
             Regex reg = new Regex(@"(\$)(\w*):");
             if (reg.IsMatch(columnName))
             {
                 Match match = reg.Match(columnName);             
                 columnNames.Add(match.Groups[2].Value);
             }                      
        }
     }

This will put each column name in a List<string> which you can then bind to a drop down box.

answered Mar 15 '12 at 14:22

Jetti

2,418
1
17
25

1

Where do you find the doc concerning the whole interop.excel namespace ? By looking at the MSDN, I cant seem to find anything interesting. – squelos Mar 15 '12 at 14:45
@squelos you can find some information [from MSDN here](http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel(v=office.11).aspx). Other info I got from a book. I also have a blog post which you can read [here](http://dontbreakthebuild.com/2011/01/30/excel-and-c-interop-with-net-4-how-to-read-data-from-excel/) that deals with reading Excel data with C# and .NET 4.0 – Jetti Mar 15 '12 at 14:53
1

good starting point for me! I had wanted to copy the column names from Excel over to a CSV file and adjusted your code to columnNames.Add(xlWorksheet.Cells[1, c].Value); Thanks! – Rob Koch May 10 '13 at 21:37
The last column was missing: for (int c = 1; c <= columnCount; c++) – jcmeyrignac Nov 18 '22 at 14:41

score 0 · Answer 2 · answered Aug 14 '13 at 11:49

You can also Read Column By index

let your Excel work Sheet object is "excelWorksheet" then you can access it as

for setting a value you can use

excelWorksheet.Cells[rowindex, columnindex].Value = "test";

for getting a value you can use

string result = excelWorksheet.Cells[rowindex, columnindex].Value ;

Remember that fields are dynamically generated so it may show error in writing your code but ignore that

for example you want to set text in excel sheet row 1 & column 2 then

excelWorksheet.Cells[1, 2].Value = "test";

I have used it & it works perfectly

Microsoft Interop: Excel Column Names

2 Answers2

Linked