CODEWITHSUNDEEP
pythonexcelopenpyxl
Polina Rogov
Polina Rogov

Reputation: 33

openpyxl IF Formula and iterating over multiple rows until you reach an empty row

This is a 3 part question: 1. When entering the following using openpyxl I get this Alert ("We found a problem with some content in 'Risks Chartio Import Py Test.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes.")

sheet ['N2'] = '="IF(AND(M2>0,M2<=1),"01-VERY LOW",IF(AND(M2>1,M2<=4),"02-LOW", IF(AND(M2>4,M2<=9),"03-MEDIUM",IF(AND(M2>9,M2<=16),"04-HIGH",IF(AND(M2>16,M2<=25),"05-CRITICAL")))))"'

How can I fix the code above so that the formula works in my xlsx file

  1. How do I Identify the last row in the file with data and then iterate the formula above until I reach that row?

Thanks!

Upvotes: 0

Views: 224

Answers (1)

Dror Av.
Dror Av.

Reputation: 1214

  1. You have a redundant set of quotation marks ("...") in your formula, just remove them:

sheet ['N2'] = '=IF(AND(M2>0,M2<=1),"01-VERY LOW",IF(AND(M2>1,M2<=4),"02-LOW", IF(AND(M2>4,M2<=9),"03-MEDIUM",IF(AND(M2>9,M2<=16),"04-HIGH",IF(AND(M2>16,M2<=25),"05-CRITICAL")))))'

  1. to get the last row of a file with openpyxl just use worksheet.max_row which will give you the last row used in that file (note that row that had data that was deleted are not considered "empty" as they have an empty string in them.

  2. To iterate over a worksheet columns and rows see this answer.

Upvotes: 0

Related Questions