Python Forum
Create new Excel instead of update certain sheet
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Create new Excel instead of update certain sheet
#1
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
Reply
#2
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.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Python script for excel sheet Nabil 5 458 Jun-01-2021, 02:29 PM
Last Post: anuradha
  Add a new column when I extract each sheet in an Excel workbook as a new csv file shantanu97 0 464 Mar-24-2021, 04:56 AM
Last Post: shantanu97
  Python Matplotlib: Create chart for every 4 columns in Excel file JaneTan 2 701 Feb-28-2021, 05:02 AM
Last Post: JaneTan
  Append excel sheet using openpyxl TammyP 1 489 Feb-02-2021, 06:32 PM
Last Post: nilamo
  How to Copy Single Value From One Excel Sheet to Another SunWers 4 683 Dec-29-2020, 05:39 PM
Last Post: SunWers
Question Python + Google Sheet | Best way to update specific cells in a single Update()? Vokofe 1 506 Dec-16-2020, 05:26 AM
Last Post: Vokofe
  Writing to existing excel sheet jksvend 0 502 Oct-12-2020, 11:19 AM
Last Post: jksvend
  Edit Open and Active Excel sheet in Python JoeDainton123 1 656 Jul-29-2020, 12:52 AM
Last Post: Larz60+
  copy content of text file with three delimiter into excel sheet vinaykumar 0 553 Jul-12-2020, 01:27 PM
Last Post: vinaykumar
  How to create an Excel app that runs Python? felipe0216 3 726 May-31-2020, 01:19 AM
Last Post: ibutun

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020