Read only visible rows in excel with python

Question

I'd like to read only the visible rows form an excel worksheet in python.

the input (excel sheet):

so when I filter for example:

as an output in python , I will get just the visible data (1 row) in this case.

here my code:

from openpyxl import load_workbook

wb = load_workbook(r'C:\Bureau\test\Data.xlsx') 
ws = wb['workload']

# iterate over all the rows in the sheet
for row in ws: 
    if ws.row_dimensions[row[0].row].hidden == False:
        for cell in row:
            print(cell.value)

the code works but it gives results in this format:

but I want to have with a normal format like a table or dataframe.

Any suggestions?

Thank you for your help

@ Mayank Porwal I mean by visible rows , that if I do a filter in excel than I will read the data in python , I will have just the visible rows that are not hidden because of the filter. — Fazia Chenna
– Fazia Chenna, Commented Feb 11, 2021 at 14:52
@ Mayank Porwal I 've tried this one but it's not giving me the data! stackoverflow.com/questions/31257353/… — Fazia Chenna
– Fazia Chenna, Commented Feb 11, 2021 at 14:56

norie · Accepted Answer · 2021-02-11 17:19:46Z

7

To create a dataframe from the visible rows you could try something like this.

from openpyxl import load_workbook
import pandas as pd

wb = load_workbook(r'Data.xlsx') 
ws = wb['workload']
data = []

for row in ws: 
    if ws.row_dimensions[row[0].row].hidden == False:
      row_values = [cell.value for cell in row]
      data.append(row_values)

df = pd.DataFrame(data[1:], columns=data[0])

print(df)

answered Feb 11, 2021 at 17:19

norie

9,9372 gold badges14 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Fazia Chenna Over a year ago

yes it's working thank you so much , this is what I want to have as a results!

Priya Over a year ago

@norie Thanks! That was helpful. But could you please explain what is row_dimensions[row[0].row]. When I googled, I only got info about row_dimensions that it is used to set the height and width of a row. But I didn't understand row[0].row. Also when I tried in my code, irrespective of index in row i.e., row[0], it gave me same/correct output. Eg: Even when I tried with row[3].row. So, it would be little helpful if you can explain row[0].row

Priya Over a year ago

@norie Could you please help me the same issue but when in readonly mode? When I used the solution, it gave me error saying AttributeError: 'ReadOnlyWorksheet' object has no attribute 'row_dimensions'

Collectives™ on Stack Overflow

Read only visible rows in excel with python

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related