I have an excel spreadsheet that looks like:
expense.xlsx
|Amount|CategoryId|Date|
|:----|:------:|-----:|
| 123 | 1 | 2020-07-07|
| 321| 2 | 2020-07-07|
I have a dictionary for the categories:
catDict = {1:'a', 2:'b'}
How would I go about changing the excel column "CategoryId" to match the dictionary values:
| Amount | CategoryId | Date |
|---|---|---|
| 123 | a | 2020-07-07 |
| 321 | b | 2020-07-07 |
Using openpyxl you can try with the following:
import openpyxl as opxl
wb = opxl.load_workbook('expense.xlsx')
ws = wb.active
for i in range(1,ws.max_row+1):
ws['B'+str(i)] = str(ws['B'+str(i)].value).replace('1','a').replace('2','b')
wb.save('expense.xlsx')
If you have a lot more values to replace, then I suggest also checking this question to improve on that part of the code.
Old answer:
Can you use pandas? If so, you can use .replace():
df = pd.read_csv('expense.xlsx') #Read excel
catDict = {1:'a',2:'b'}
df['CategoryId'] = df['CategoryId'].replace(catDict)
#Alternatively one can use `.map()`
df.to_excel('expense.xlsx') #Save back to excel
Using openpyxl, hope this helps!
import openpyxl
workbook = openpyxl.Workbook()
sheet = workbook.active
dict = {1:'a', 2:'b'}
sheet.cell(row=1, column=1, value='Amount')
sheet.cell(row=1, column=2, value='CategoryId')
sheet.cell(row=1, column=3, value='Date')
row, column = 2,1
for key, values in dict.items():
sheet.cell(row=row, column=column, value=key)
sheet.cell(row=row, column=column+1, value=values)
sheet.cell(row=row, column=column+2, value='2020-07-07')
row += 1
file_name = 'sample.xlsx'
workbook.save(filename=file_name)
