Home > Back-end >  C# Interop Excel find column by name then store entire column by row
C# Interop Excel find column by name then store entire column by row

Time:08-13

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

CodePudding user response:

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);
            }
        }
    }
  • Related