Python: How do I check for the values of each row and column?

Faith Berroya 0
Viewed 60

I have this table, and I need to verify the column names first then the second row's values. I'm new to Python and I tried to check on other site such as this: https://www.tutorialspoint.com/how-to-get-all-the-values-of-a-particular-row-based-on-a-condition-in-a-worksheet-in-selenium-with-python but do we have something like an automatic indexing for checking of values? One approach is I can save the column names in an array and add assertion to check if they are equal. But how do I get the column names?

enter image description here

For the column names, I have to verify if it has the same data in the array:

columnNames:["Column 1", "Column 2", "Column 3", "Column 4", "Column 5", "Column 6", "Column 7", "Column 8", "Column 9", "Column 10", "Column 11"]

For the value row I need to know how do I specify index but automatic? Or should I make it like row[2].value? But I'm not sure. :/ I'm so confused...

I only currently have this one :/

columnNames: ["Column 1", "Column 2", "Column 3", "Column 4", "Column 5", "Column 6", "Column 7", "Column 8", "Column 9", "Column 10", "Column 11"]
    activeWorksheet = excelTitle.active
    for item in range(1,activeWorksheet.max_row+1):
        global excelColumnNames
        excelColumnNames.append(activeWorksheet.cell(row=item))
openpyxl selenium python
1 Answers

Its not clear what your ultimate requirement is. Perhaps the following will help move in the right direction.
For the comparison you probably just want to use a python list as shown in the example code below as 'column_names'. You can then do a direct check if a Column Header is in the list and find the Index if that is necessary.
The example code shows how you can iterate the header row and compare the Header text with that in the 'column_names' list and also compare the index position.

You've noted that you want to "verify the column names first then the second row's values". For this you don't want to make two passes through the Excel cells, that'd be inefficient. If possible, the verification and result of that check if it involves writing to the sheet should be achieved in the one pass. As you have not included what verification is needed for the values I cannot comment on how to handle it here.
If you want to just extract the values for the headers to use outside Excel I have included creating a dictionary 'row_values', of the columns that verify on name and index.

from openpyxl import load_workbook

# List of column names to compare against
column_names =['Column 1', 'Column 2', 'Column 3', 'Column 4', 'Column 5', 'Column 6', 'Column 7', 'Column 8',
              'Column 9', 'Column 10', 'Column 11']
# Open Excel workbook and sheet
wb = load_workbook(filename="foo.xlsx")
ws = wb['Sheet1']

row_values = {} # Dictionary for Column names and values that verify
for row in ws.iter_rows(max_col=ws.max_column, max_row=1):
    for list_index, cell in enumerate(row):
        c_value = cell.value
        if c_value in column_names:  # Check if the column name is in the list 'column_names'
            # The following code is for columns where the name is in the 'column_names' list
            row_index = column_names.index(c_value)  # Get the index of the column name in the list 'column_names'
            if row_index == list_index:  # Check if the index match
                print(f"Column name '{c_value}' is in the List at the correct Index {row_index}")
                row_values[c_value] = cell.offset(row=1).value  # If the column name and Index match add the column to the dictionary
            else:
                print(f"Column name '{c_value}' is in the List at an incorrect Index {row_index}")  # If the index doesn't match this columns is not included in the dictionary
        else:
            # The following code is for columns where the name is not in the 'column_names' list
            print(f"The Column name '{c_value}' is not in the List")

print("\nDictionary values:\n" + str(row_values))
moken0
Related