Iterate through sheets in Pyexcel in python

Question

I want to iterate through all the non empty sheets of Excel to get the headers. I must use PyExcel for that.Here is my code:

import pyexcel as pe
book = pe.get_book(file_name="Mydata.xlsx")




j=0
print(j)
for j in range(100):
    for item in book.sheet_by_index(j):

     sheet = pe.get_sheet(file_name="Mydata.xlsx")
     sheetheaders= sheet.row_at(0)
     header_list = [i for i in sheetheaders if i != '' ]

     print(header_list)
     j=j+1

Can anyone help me by telling how do I iterate it without getting following error?

Traceback (most recent call last):
   line 11, in <module>
    for sheet in book[i]:
TypeError: 'NoneType' object is not iterable

Thank you!

The sheets' indexes are most probably zero-based. Try setting i to 0. — DeepSpace
– DeepSpace, Commented Jul 11, 2017 at 21:08
It's complaining sheeheaders is None. Are you sure you didn't misspell shee*t*headers? Where is this variable defined? Did you mean maybe header_list = [header for header in sheet]? — darksky
– darksky, Commented Jul 11, 2017 at 21:15
Hi,I updated the code. it is not going to second sheet. It is iterating in first sheet and giving those headers again and again. — Analyst
– Analyst, Commented Jul 11, 2017 at 21:31

chfw · Accepted Answer · 2017-07-12 19:38:17Z

1

Please try this:

import pyexcel as p

header_list = [[ a_header for a_header in sheet.row[0] if a_header] for sheet in p.get_book(file_name="my file.xlsx") if sheet.number_of_rows() > 0]

edited Jul 12, 2017 at 19:38

answered Jul 12, 2017 at 12:10

chfw

4,6122 gold badges32 silver badges32 bronze badges

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

3 Comments

Analyst Over a year ago

Hi, Thank you so much! This package made my life much easier!

chfw Over a year ago

I am glad to hear that. It's my pleasure!

Analyst Over a year ago

Hi, it worked but I am getting ' ' in output list as an item. I tried using>>>>>>>> '~[a for a in header_list if len( a ) > 0]~' but it didn't work. example:::::::: char=[a,v,b,' ', ' ']

darksky · Accepted Answer · 2017-07-11 21:37:59Z

1

You do not need to increment the index manually in a for loop. Try the following code:

for sheet_index in range(book.number_of_sheets()):
    sheet = book.sheet_by_index(sheet_index)
    header_list = [header for header in sheet.row_at(0)]

    print(header_list)

answered Jul 11, 2017 at 21:37

darksky

2,12018 silver badges31 bronze badges

Collectives™ on Stack Overflow

Iterate through sheets in Pyexcel in python

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related