Selecting A Dynamic range in Excel using Python - Openpyxl

Question

I have an Excel file with data in column A. I want to loop through each cell and select the row that that i first come across that has a formula.

This is my code:

import openpyxl 
wb=openpyxl.load_workbook(path1)
sheet = wb['Sheet1']

names=sheet['A']

for cellObj in names:
    val = str(cellObj.value)
    if val[0] == "=":
        #select the row from column A to Z and make it's values equal to itself (like doing a copy/pasteSpecial values in excel to remove the formula)

I guess i can append the row in an empty list then make it equal to the row again, but how do i even do that?

Thanks!

You check for the cell.data_type == "f"

Charlie Clark
– Charlie Clark

2022-02-14 15:16:35 +00:00
Commented Feb 14, 2022 at 15:16 — Charlie Clark
– Charlie Clark, Commented Feb 14, 2022 at 15:16

Alvin Cruz · Accepted Answer · 2022-02-13 05:37:39Z

1

Basically, you just wanted to remove the formula and leave the actual data itself? If you want this for the whole file then add data_only=True. I also suggest saving the workbook in a different filename or create a copy of original excel file.

import openpyxl 
wb = openpyxl.load_workbook(path1, data_only=True) 
wb.save(save_path)
wb.close()

answered Feb 13, 2022 at 5:37

Alvin Cruz

1805 bronze badges

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

1 Comment

OnTheHunt Over a year ago

I am only trying to remove the formula from the FIRST row that contains a formula. I have many other formulas elsewhere i don't want to remove.

Collectives™ on Stack Overflow

Selecting A Dynamic range in Excel using Python - Openpyxl

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related