CODEWITHSUNDEEP
pythonopenpyxl
OnTheHunt
OnTheHunt

Reputation: 11

Selecting A Dynamic range in Excel using Python - Openpyxl

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!

Upvotes: 0

Views: 1047

Answers (1)

Alvin Cruz
Alvin Cruz

Reputation: 180

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()

Upvotes: 1

Related Questions