C# Interop Excel find column by name then store entire column by row

Question

I'm trying to create a program that will read through a new spreadsheet every time. This program has to find a column named "ID" and store all the IDs below and store the corresponding status of that ID.

Column A ID Column C STATUS
SOME TEXT 123456 SOME TEXT Pass
SOME TEXT 123457 SOME TEXT Pass
SOME TEXT 123458 SOME TEXT Fail
SOME TEXT 123459 SOME TEXT Fail

For example I want to store only all values of Column B(ID) along with Column D(Status). As this data will be exported to another spreadsheet with the corresponding IDs while updating the status of this ID. However, these columns are not static as these spreadsheets are generated randomly per person using the program. Column B(ID) may be Column F next time, and Column D(Status) may be column A.

Answer 1

3/14/2012 · excel worksheet.Cells [rowindex, columnindex].Value = "test"; for getting a value you can use string result = excel worksheet.Cells [rowindex, columnindex].Value; Remember that fields are dynamically generated so it may show an error in writing your code but ignore that, for example, if you want to set text in excel sheet row 1 & column 2 then

Answer 2

Welcome to Microsoft Q&A, you could try the following code to get all values of a column by name.

   static void Main(string[] args)  
        {  
            var dt = READExcel(@"Test.xlsx");  
            var listId= dt.AsEnumerable().Select(r => r.Field<string>("ID")).ToList();  
            var liststatus= dt.AsEnumerable().Select(r => r.Field<string>("Status")).ToList();  
  
        }  
        public static DataTable READExcel(string path)  
        {  
            Microsoft.Office.Interop.Excel.Application objXL = null;  
            Microsoft.Office.Interop.Excel.Workbook objWB = null;  
            objXL = new Microsoft.Office.Interop.Excel.Application();  
            objWB = objXL.Workbooks.Open(path);  
            Microsoft.Office.Interop.Excel.Worksheet objSHT = objWB.Worksheets[1];  
  
            int rows = objSHT.UsedRange.Rows.Count;  
            int cols = objSHT.UsedRange.Columns.Count;  
            DataTable dt = new DataTable();  
            int noofrow = 1;  
  
            for (int c = 1; c <= cols; c++)  
            {  
                string colname = objSHT.Cells[1, c].Text;  
                dt.Columns.Add(colname);  
                noofrow = 2;  
            }  
  
            for (int r = noofrow; r <= rows; r++)  
            {  
                DataRow dr = dt.NewRow();  
                for (int c = 1; c <= cols; c++)  
                {  
                    dr[c - 1] = objSHT.Cells[r, c].Text;  
                }  
  
                dt.Rows.Add(dr);  
            }  
  
            objWB.Close();  
            objXL.Quit();  
            return dt;  
        }  

Best Regards,
Jack

---

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.