How to get values of filtered excel data using Python?

Question

I’m trying to get the value of each cell in every shown row after applying an AutoFilter to some columns.

Example code:

import xlwings as xl
filename = “C:\somepath\Data.xlsx”

# Example of Excel data containing student info and grades
with xl.App(visible=True) as app:
    wb = xl.Book(filename)
    ws = wb.sheets.active

    # Excel sheet with 200 rows and 10 columns
    ws.api.Range(“A1”).CurrentRegion.AutoFilter(Field=1)
    # Apply custom filter to column E
    ws.api.Range(“E:E”).AutoFilter(Field=5, Criteria1=“A”)
    # Read back excel sheet with shown cells
    # Only shown cells from filter not hidden ones

I’m stumped on how to do this. I would appreciate any help.

Answer 1

You would probably need to use the check on the row to see if it is hidden

.api.EntireRow.Hidden

Then do whatever with that row or cells on that row as needed if it is not.


The required steps would likely be:

1. Apply your filter as you have.
2. Then loop through any column (I'm looping through Column A, so A2, A3, A4 etc)
3. Check if the cell is in a hidden row, if it is skip to the next. If not do whatever is needed on that row

The example code below just prints out all the cell values in the row if it is visible. (Note this example uses the xlsxwriter util to convert column number to letter for convenience but otherwise makes no use of that module for extracting the visible rows).

import xlwings as xl
import xlsxwriter

filename = "Data.xlsx"

# Example of Excel data containing student info and grades
with xl.App(visible=True) as app:
    wb = xl.Book(filename)
    ws = wb.sheets.active

    # Excel sheet with 200 rows and 10 columns
    ws.api.Range("A1").CurrentRegion.AutoFilter(Field=1)
    # Apply custom filter to column E
    ws.api.Range("E:E").AutoFilter(Field=5, Criteria1="Data")
    # Read back excel sheet with shown cells
    # Only shown cells from filter not hidden ones

    max_col_letter = xlsxwriter.utility.xl_col_to_name(ws["A1"].expand("right").last_cell.column-1)

    for cell in ws['A2'].expand("down"):
        if not cell.api.EntireRow.Hidden:  # For each cell in Column A to max row is this row hidden?
            print(cell.address)
            print(ws.range(f"{cell.address}:{cell.address.replace('A', max_col_letter)}").value, end='')
            print('\n-------------\n')