CODEWITHSUNDEEP
pythonopenpyxlxlsx
Yogesh Sudheer Modak
Yogesh Sudheer Modak

Reputation: 325

Change contents of a specific cell in multiple xlsx files in python

I'm trying to write a python script that modifies/chnages contents of a cell of a particular sheet in all .xlsx sheets that exist in the current working directory.

The shape of the sheet in all the .xlsx is something like this:

column1 column 2

value1.1 value2.1

value1.2 value2.2

.

.

value1.n value2.n

.

.

Apologies for improper formating, I don't know how to format so that the values look like cells, so imagine the above as 2 columns of a sheet.

Now, what I want is to open each .xlsx file, open the first sheet: "sheet", find value1.n, whcih is present in each sheet and modify value2.n to value2.nm, where value2.nm is the modified value.

Ultimately, i want the sheet to look like this:

column1 column 2

value1.1 value2.1

value1.2 value2.2

.

.

value1.n value2.nm

.

.

whilst all other content remains untouched.

I already have code that can access the first sheet and find the value1.n i'm looking for, but I'm unable to proceed further.

Any help on this is appreciated.

import openpyxl
count = 0
for f in files:
    if f[-4:] == "xlsx":
        book = openpyxl.load_workbook(f)
        sheet = book.active
        for row in sheet.iter_rows(values_only=True):
            for data in row:
                if data == "value1.n":
                    count = count + 1
                    print(data)
                    print(count)

the print statements are just for me to keep track.

I'm using Windows 10, and coding on locally installed Jupyter Notebook that uses a Python3 notebook.

Upvotes: 0

Views: 423

Answers (1)

Rahul Raut
Rahul Raut

Reputation: 1518

import openpyxl
count = 0
for f in files:
    if f[-4:] == "xlsx":
        book = openpyxl.load_workbook(f)
        sheet = book.active
        for idx, row in enumerate(sheet.iter_rows()):
            if row[0].value == "value1.n":
                row[1].value = count + 1
                count = count + 1
        book.save(f)

Upvotes: 1

Related Questions