14

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?

Improve this question

4 Answers 4

Reset to default
25

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

18

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)
Improve this answer

5 Comments

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 ?
Here it shows " PermissionError Errno 13 permission denied"
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' `
Try keeping only one save method call that is after all the writings were completed.
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
8

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

Improve this answer

3 Comments

So, in my example you are saying, instead of work.save, I should use work.append?
It says "Workbook object has no attribute called append"
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??
3

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.