how to append data using openpyxl python to excel

Question

I have different Python list variables(data1, data2, data3 ect) containing data which I want to put into an already existing excel sheet. Presently My loop goes like this.

for row, entry in enumerate(data1,start=1):
  st.cell(row=row, column=1, value=entry)
  work.save('sample.xlsx')
for row, entry in enumerate(data2,start=1):
  st.cell(row=row, column=2, value=entry)
  work.save('sample.xlsx')
for row, entry in enumerate(data3,start=1):
  st.cell(row=row, column=3, value=entry)
  work.save('sample.xlsx')
for row, entry in enumerate(data4,start=1):
  st.cell(row=row, column=4, value=entry)
  work.save('sample.xlsx')
for row, entry in enumerate(data5,start=1):
  st.cell(row=row, column=5, value=entry)
  work.save('sample.xlsx')
for row, entry in enumerate(data6,start=1):
  st.cell(row=row, column=6, value=entry)
  work.save('sample.xlsx')
for row, entry in enumerate(data7,start=1):
  st.cell(row=row, column=7, value=entry)
  work.save('sample.xlsx')      

Once my Python script runs, It will store the data from the 1st row. If I am again running the script I want the new data to come below the available data

How to do so?

Answer 1

openpyxl has many different methods to be precise but ws.append in previous answers is strong enough to answer your demands. Consider you have written your data to a new sample.xlsx:

from openpyxl.workbook import Workbook

headers       = ['Company','Address','Tel','Web']
workbook_name = 'sample.xlsx'
wb = Workbook()
page = wb.active
page.title = 'companies'
page.append(headers) # write the headers to the first line

# Data to write:
companies = [['name1','address1','tel1','web1'], ['name2','address2','tel2','web2']]

for info in companies:
    page.append(info)
wb.save(filename = workbook_name)

Now, to append new lines you must first open an existing book with load_workbook:

from openpyxl import load_workbook

workbook_name = 'sample.xlsx'
wb = load_workbook(workbook_name)
page = wb.active

# New data to write:
new_companies = [['name3','address3','tel3','web3'], ['name4','address4','tel4','web4']]

for info in new_companies:
    page.append(info)

wb.save(filename=workbook_name)

Answer 2

ws.append() will always add rows below any existing data.

Answer 3

Try using

sheet.max_row 

it will return the last row value, You can start writing the new values from here.

max = ws.max_row
for row, entry in enumerate(data1,start=1):
   st.cell(row=row+max, column=1, value=entry)

Hope it helps.Happy Coding :)