+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Create new Excel instead of update certain sheet (/thread-29617.html)
Create new Excel instead of update certain sheet - Pietertl - Sep-12-2020
Dear All,
I'm really new with this Python and need guidance from the expert.
My colleague has 2 python programs that previously was working fine. But when we copy these 2 program into another machine, seems like doesn't work as expected. We are unable to reproduce on the previous machine due to hardware faulty.
Previously, when we run the program, the program will update on certain sheet only, but now the program just created fresh newly Excel file.
Below is the part of the program related to saving data into Excel :
____________________________________________________________________
from openpyxl import load_workbook
f = 'Macro Data Variables.xlsx'
order = ['Date','DE','ES','FR','IT','Total','6MMA','EU','TOTLL','Adjustment','Adj Loans','Net Total','Net 6MMA','EXUSEU','Adj Euro','Global'] # columns in given Excel sheet
try:
wb = load_workbook(filename=f)
except:
wb = openpyxl.Workbook()
wb.save(f)
# create sheet if does not exist
try:
ws = wb.get_sheet_by_name('globalcredit')
except:
wb.create_sheet('globalcredit')
ws = wb.get_sheet_by_name('globalcredit')
row = 4
# replicate the respective formulas for the other rows
try:
for value in list(values.keys()):
if row >= 91:
values[value]['Adjustment'] = 329.123 # change if have further adjustments
else:
values[value]['Adjustment'] = ''
if row < 5:
values[value]['Net Total'] = ''
values[value]['Total'] = ''
else:
values[value]['Net Total'] = '=K' + str(row) + '-K' + str(row-1)
values[value]['Total'] = '=ROUND(SUM(B' + str(row) + ':E' + str(row) + '),2)'
if row < 10:
values[value]['6MMA'] = ''
values[value]['Net 6MMA'] = ''
else:
values[value]['Net 6MMA'] = '=AVERAGE(L' + str(row-5) + ':L' + str(row) + ')'
values[value]['6MMA'] = '=SUM(F' + str(row-5) + ':F' + str(row) + ')/6'
values[value]['Adj Loans'] = '=I' + str(row) + '-J' + str(row)
values[value]['Adj Euro'] = '=(H' + str(row) + '*N' + str(row) + ')/1000'
values[value]['Global'] = '=K' + str(row) + '+O' + str(row)
for i in range(len(order)):
column = i + 1
# convert date to mmm-yy format
if order[i] == 'Date':
ws.cell(row=row,column=column,value=datetime.date(*map(int, value.split('-'))).strftime('%b-%y'))
else:
try:
ws.cell(row=row,column=column,value=values[value][order[i]])
except:
ws.cell(row=row,column=column,value='')
row += 1
wb.save(f)
except Exception as e:
print('Could not write to file. Check code.')
print(e)____________________________________________________________________Apologize if my questions seems like too novice and really appreciate for any feedback.
Warm Regards,
Pieter
RE: Create new Excel instead of update certain sheet - Pietertl - Sep-17-2020
Hi Buran,
Thank you for your help to correct my previous posting.
Apologize for not following the rule.
As per now, i am still looking for your guys help and support regarding the issue my colleague has.