how to append data using openpyxl python to excel file from a specified row?

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?

score 23 · Answer 1 · answered May 16 '18 at 22:54

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)

score 17 · Accepted Answer · edited Jun 21 '21 at 10:23

17

Try using:

sheet.max_row

It will return the last row value, you can start writing the new values from there:

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

edited Jun 21 '21 at 10:23

Tomerikoo

18,379
16
47
61

answered May 12 '16 at 09:23

Strik3r

1,052
8
15

Thats correct. sheet.max_row gives the last row. So in my loop where should i give this value? Is it in the `start=row_number` ? – May 12 '16 at 09:52
Here it shows " PermissionError Errno 13 permission denied" – May 12 '16 at 09:58
Just tried it, Still it says permission denied! Traceback (most recent call last): ` writer.save(filename, as_template=as_template) File "C:\Users\rnair\AppData\Local\Programs\Python\Python35\lib\site-packages\ openpyxl-2.4.0a1-py3.5.egg\openpyxl\writer\excel.py", line 230, in save archive = ZipFile(filename, 'w', ZIP_DEFLATED, allowZip64=True) File "C:\Users\rnair\AppData\Local\Programs\Python\Python35\lib\zipfile.py", l ine 1009, in __init__ self.fp = io.open(file, filemode) PermissionError: [Errno 13] Permission denied: 'sample.xlsx' ` – May 12 '16 at 10:26
Try keeping only one save method call that is after all the writings were completed. – Strik3r May 12 '16 at 11:05
1

Thank you so much! I had actually kept the excel file opened in the side. When I close it and run the program, it works perfectly – May 12 '16 at 11:22

score 8 · Answer 3 · answered May 12 '16 at 09:26

8

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

answered May 12 '16 at 09:26

Charlie Clark

18,477
4
49
55

So, in my example you are saying, instead of work.save, I should use work.append? – May 12 '16 at 09:33
It says "Workbook object has no attribute called append" – May 12 '16 at 09:47
I see that you have answered this same question for someone else where you advised him to specify the path of the excel file while creating it. As i am not creating any file and just using the available one, do I need to specify too?? – May 12 '16 at 10:34

score 3 · Answer 4 · answered Aug 06 '19 at 11:27

You can use the append() method to append values to the existing excel files. Example:

workbook_obj = openpyxl.load_workbook(excelFilePath)
sheet_obj = workbook_obj.active
col1 = 'NewValueCol1'
col2 = 'NewValueCol2'
sheet_obj.append([col1, col2])
workbook_obj.save(excelFilePath)

here the col1 and col2 values will be added with the existing data in the excel sheet.

how to append data using openpyxl python to excel file from a specified row?

4 Answers4

Linked