CODEWITHSUNDEEP
pythonopenpyxl
János
János

Reputation: 143

openpyxl select dynamic range until last non-empty row within range

I need to select dynamic ranges in my workbooks. I know the starting position of the range (ex:C2), however, the number of rows might change every day.

Here is my sample xlsx. I want to select from c2:g4.

  A         B       C       D       E       F       G
1 head1     head2   head3   head4   head5   head6   head7
2 Data1.1   Data2.1 Data3.1 Data4.1 Data5.1 Data6.1 Data7.1
3 Data1.2   Data2.2 Data3.2 Data4.2 Data5.2 Data6.2 Data7.2
4 Data1.3   Data2.3 Data3.3 Data4.3 Data5.3 Data6.3 Data7.3
5 Data1.4   Data2.4                 
6 Data1.5   Data2.5                 
7 Data1.6   Data2.6                 
8 Data1.7   Data2.7

This is my code:

from openpyxl import load_workbook
workbook = load_workbook(filename="example.xlsx")
sheet = workbook.active
for value in sheet.iter_rows(min_row=2,
                             min_col=3,   
                             values_only=bool):
        print(value)

And here is the output:

('Data3.1', 'Data4.1', 'Data5.1', 'Data6.1', 'Data7.1')
('Data3.2', 'Data4.2', 'Data5.2', 'Data6.2', 'Data7.2')
('Data3.3', 'Data4.3', 'Data5.3', 'Data6.3', 'Data7.3')
(None, None, None, None, None)
(None, None, None, None, None)
(None, None, None, None, None)
(None, None, None, None, None)

How can I limit my search to the desired range and eliminate the 'None' values?

Thanks!

Upvotes: 2

Views: 742

Answers (1)

d1nch8g
d1nch8g

Reputation: 619

Here is how:

from openpyxl import load_workbook
workbook = load_workbook(filename="example.xlsx")
sheet = workbook.active
for value in sheet.iter_rows(min_row=2,
                             min_col=3,   
                             values_only=bool):
        if None in value:
            continue
        print(value)

Upvotes: 1

Related Questions