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	Test ID	Column C	Column D	Status
TEXT	123456	TEXT	TEXT	Pass
TEXT	123457	TEXT	TEXT	Pass
TEXT	123458	TEXT	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. Example:

Status	Column B	Test ID	Column D	Column E
Pass	TEXT	123456	TEXT	TEXT
Fail	TEXT	123457	TEXT	TEXT
Pass	TEXT	123458	TEXT	TEXT

Answer 1

You'll want to get the indexes for the columns you are interested in so you know which column to look in for each workbook.

The code example below shows a very simple example of getting those column index values.

    private static void ProcessExcelFile(string fileName)
    {
        FileInfo fi = new FileInfo(fileName);
        Console.WriteLine($"Processing Excel File: {fi.Name}");

        Excel.Application xlApp = null;
        Excel.Workbook xlWb = null;
        Excel.Worksheet xlWs = null;
        Excel.Range xlRange = null;

        try
        {
            // Instantiate the Excel objects
            xlApp = new Excel.Application();
            xlWb = xlApp.Workbooks.Open(fileName);
            xlWs = xlWb.Worksheets[1];
            xlRange = xlWs.UsedRange;

            // Note: Excel indexes are 1 based so an index of 0 is invalid
            int idColumnIndex = 0;
            int statusColumnIndex = 0;

            // Get the number of columns in the Excel sheet
            int colCount = xlRange.Columns.Count;
            for (int i = 1; i <= colCount; i++)
            {
                if (xlRange.Cells[1, i]?.Value?.ToString().ToLower() == "test id")
                {
                    // get the Id column index
                    idColumnIndex = i;
                }
                else if (xlRange.Cells[1, i]?.Value?.ToString().ToLower() == "status")
                {
                    // Get the status column index
                    statusColumnIndex = i;
                }

                // we've got all our values so exit the loop
                if (idColumnIndex > 0 && statusColumnIndex > 0)
                {
                     break;
                }
            }

            // Show what indexes we've found.
            if (idColumnIndex > 0)
            {
                Console.WriteLine($"Id column index is {idColumnIndex}.");
            }
            else
            {
                Console.WriteLine($"Id column not found.");
            }

            if (statusColumnIndex > 0)
            {
                Console.WriteLine($"Status column index is {statusColumnIndex}.");
            }
            else
            {
                Console.WriteLine($"Status column not found.");
            }

            // Now that you have your ID and STATUS column indexes
            // You just need to loop through the rows to pull the values
            // for each row.
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        finally
        {
            Console.WriteLine();

            //release com objects to fully kill excel process from running in the background
            if (xlRange != null)
                Marshal.ReleaseComObject(xlRange);

            if (xlWs != null)
                Marshal.ReleaseComObject(xlWs);

            //close and release
            if (xlWb != null)
            {
                xlWb.Close();
                Marshal.ReleaseComObject(xlWb);
            }

            //quit and release
            if (xlApp != null)
            {
                xlApp.Quit();
                Marshal.ReleaseComObject(xlApp);
            }
        }
    }