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