Python for loop using openpyxl

Question

I'm using the openpyxl package to make a simple tool that will be used to take excel inputs of large spreadsheets and make smaller more usable ones. I have tried using many different types of loops for this logic, but I still can't get it to work. The objective is to find any cell that has a value of 1 and copy the cell directly next to it onto a new workbook. Here's my code:

# for loop data logic
data = default['Default']
n = 1
for i in range(0,1000):
    for j in range(0,0):
        ds = data.cell(row = i, column = j)               
        if  ds == 1:
            n = n + 1
            ds.cell(row = i, column = 0).value = separated.cell(row = n, column = 0).value

NOTE: The data that I am inputting has many cells that have a numerical value of 1 in the first column. That is the one column this loop is testing for. I've been testing the code with a simple print command:

print(separated['A2'].value)

Which prints that there is no present value in the cell by printing "None." Again, I've tried two or three different for loops for this logic, and while they have all run without errors, none ever seem to change any values in the "separated" worksheet. Any and all help is appreciated.

did you print ds. see the value

The6thSense
– The6thSense

2015-06-18 13:44:46 +00:00
Commented Jun 18, 2015 at 13:44 — The6thSense
– The6thSense, Commented Jun 18, 2015 at 13:44

Charlie Clark · Accepted Answer · 2015-06-18 14:03:48Z

1

There are several errors in the code. The inner loop will never run because range(0, 0) is empty. Furthermore, openpyxl uses 1-indexing: A1 == cell(row=1, column=1) so ws.cell(row=0, column=0) would raise an exception if it ever executed.

However, you should probably just use ws.get_squared_range(min_col, min_row, max_col, max_row) to get a generator of the cells you want to look at.

answered Jun 18, 2015 at 14:03

Charlie Clark

19.7k4 gold badges56 silver badges64 bronze badges

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

3 Comments

Chris Miller Over a year ago

I made the changes that you named it did help. I found on another website someone using the (0,0) notation for A1, and that was the root of the problem. With a little fidgeting I got it to work. Thanks for replying, I really appreciate it.

Charlie Clark Over a year ago

(0, 0) is, of course, standard indexing in Python but we found (1, 1) for A1 causes less problems.

Eric Levieil Over a year ago

To be precise, openpyxl switched to 1-indexing (in version 2.0 I think).

Felipe Valera · Accepted Answer · 2016-09-14 04:10:29Z

1

I think you can scan like this, scanning for value or text. Example:

for col in sheet['A1:B100']:

        for cell in col:

            if cell.value == '1':

                print("Lo halle")

Then you proceed to copy the information in the new workbook.

Good luck

answered Sep 14, 2016 at 4:10

Felipe Valera

531 silver badge6 bronze badges

Collectives™ on Stack Overflow

Python for loop using openpyxl

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