Python Modify data of existing .xlsx file

Question

I have some code here in python which created a .xlsx file using openpyxl.

However, when i tried to modify the file, the new data will register into the file but previous data is gone. I heard of using deepcopy or (.copy.copy) to copy the data of the file but how can i paste the data copied plus my current edits into the .xlsx file?

(*some code are missing here as it is a program with GUI, the code is juz far too long)

##############creating######################

try:
    wb_ID = load_workbook('list.xlsx') 
    ws_ID = wb_ID.active
    pass

except EnvironmentError as e:      # OSError or IOError... As FileNotFoundError only exist in Python 3.x version
    print(os.strerror(e.errno)) #use operating system error to define FileNotFoundErrorError
    from openpyxl import Workbook #import Workbook function to create new xlsx (excel) file
    wb_ID = Workbook()
    ws_ID = wb_ID.active
    ws_ID['A1'] = "IC"
    ws_ID.merge_cells('B1:E1')
    ws_ID['B1'] = "Name"
    ws_ID.merge_cells('F1:K1')
    ws_ID['L1'] = "Email"
    ws_ID['M1'] = "Height"
    ws_ID['N1'] = "Gender"
    ws_ID['K1'] = "Bloodtype"
    ws_ID.merge_cells('O1:Q1')
    ws_ID['O1'] = "Default Consultation Day"
    ws_ID.merge_cells('R1:T1')
    ws_ID['R1'] = "Latest Appoinment"
    wb_ID.save("list.xlsx")
    pass
############editing#########################
wb = load_workbook(filename='list.xlsx')
ws = wb.active
last_row = 1
while True:
    last_row += 1
    cellchecker =ws['A'+str(last_row)].value #get the value of the cell
    print(cellchecker)
    print last_row
    if cellchecker is None: #check if cell is empty-->then this row number is the new row
        wb.save('list.xlsx')
        break
    else:
        continue

print(str(last_row)) #convert to string var before passing the var for worksheet function
from openpyxl import Workbook
wb = Workbook() 
ws = wb.active
ws['A'+str(last_row)] = (str(entry_IC.get().strip(' ')))
ws.merge_cells('B'+str(last_row)+':E'+str(last_row))
ws['B'+str(last_row)] = (str(entry_Name.get()))
ws.merge_cells('F'+str(last_row)+':K'+str(last_row))
ws['F'+str(last_row)] = (str(entry_email.get().strip(' ')))
ws['L'+str(last_row)] = (str(entry_Height.get().strip(' ')))
ws['M'+str(last_row)] = gender_selected
ws['N'+str(last_row)] = bloodtype_selected
ws.merge_cells('O'+str(last_row)+':Q'+str(last_row))
ws['O'+str(last_row)] = str(default_selected_day)
ws.merge_cells('R'+str(last_row)+':T'+str(last_row))
today = datetime.date.today() #as u might wonder why i redeclare this var since it already exist, but this is local var for this function only
ws['T'+str(last_row)] = (str(today))

wb.save('list.xlsx')

noticed that the editing part will overwrite the existing data as warned in openpyxl documentation. I really couldn't find a way to modify existing .xlsx file. Please help me out, I'm stuck here.

I don't understand what the problem is: openpyxl lets you edit existing files and this includes overwriting data. In addition, the code in unnecessarily verbose. — Charlie Clark, Apr 20 '16 at 07:03
above code result in the lost of original data in file while new data is saved into it @CharlieClark — Hashashihn Altheim, Apr 20 '16 at 08:19
What data is lost where? You are deliberately replacing the file "list.xlsx" with a completely new workbook. — Charlie Clark, Apr 20 '16 at 09:30
So in that case I'm replacing the old file with a completely new workbook, how should I do to open and modify the existing workbook? I checked on the documentation but I really couldn't understand — Hashashihn Altheim, Apr 20 '16 at 12:57
Use `load_workbook()` to open and edit an existing file. Just as you do at the top of your code. — Charlie Clark, Apr 20 '16 at 14:04
So I just need to load_workbook(), then edit like usual, and finally wb.save(), is it true? Thank you for your patience in answering my question — Hashashihn Altheim, Apr 20 '16 at 14:36

score 0 · Accepted Answer · answered Apr 19 '16 at 20:03

0

I've run into this problem several times, and haven't been able to solve it using pure python; however, you can use the following code to call a VBA macro from a Python script, which can be used to modify your existing excel file.

This has allowed me to come up with creative ways to streamline work so that I don't ever have to actually open up excel files in order to update them, even if data processing or gathering requires python.

# run excel macro from python
import win32com.client
fn = '//path/filename.xlsm'
xl=win32com.client.Dispatch("Excel.Application")
xl.Application.Visible = True
xl.Workbooks.Open(Filename = fn,ReadOnly = 1)
xl.Application.Run("macroname")
xl=0

answered Apr 19 '16 at 20:03

Nathan Clement

1,103
2
18
30

Do you mind to show me some example of how you update your xlsx file? Perhapes a dummy file? Please and THANK YOU @Nathan Clement – Hashashihn Altheim Apr 20 '16 at 08:21
@HashashihnAltheim It really depends on what you want to do. Usually I start with my master .xlsm that I want to modify. It usually has a bunch of charts, conditional formatting and data validation, which makes it hard to modify in Python. I will then export new data to a known location with Python, then use python to call a macro in my master .xlsm which copies the export into the master spreadsheet. – Nathan Clement Apr 20 '16 at 16:49

Python Modify data of existing .xlsx file

1 Answers1