Skip to content

Instantly share code, notes, and snippets.

@rdapaz
Created May 22, 2020 07:04
Show Gist options
  • Save rdapaz/7344fce226db1c5f0ee664b455b7fcc5 to your computer and use it in GitHub Desktop.
Save rdapaz/7344fce226db1c5f0ee664b455b7fcc5 to your computer and use it in GitHub Desktop.
Change values in a spreadsheet from a Python dictionary of old to new values
import re
import win32com.client as c
subs = """
One|1
Two|2
""".splitlines()
subs = {k: v for k, v in [y.split('|') for y in subs if len(y) > 0]}
xlApp = c.gencache.EnsureDispatch('Excel.Application')
xlApp.Visible = True
path = r'path.xlsx'
wk = xlApp.Workbooks.Open(path)
sh = wk.Worksheets('Sheet1 (2)')
eof = sh.Range('A65535').End(-4162).Row
for row in range(2,eof+1):
for org, rep in subs.items():
rx = re.compile(r'{0:s}'.format(org))
desc = sh.Range(f'C{row}').Value
if rx.search(desc):
desc = re.sub(org, rep, desc)
sh.Range(f'I{row}').Value = desc
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment