How to write data into existing '.xlsx' file which has multiple sheets

Question

i have to update/append data into existing xlsx file.

xlsx file contains multiple sheets. for example i want to append some data into existing sheet 'Sheet1', how to do this

Answer 1

To append a new row of data to an existing spreadsheet, you could use the openpyxl module. This will:

1. Load the existing workbook from the file.
2. Determines the last row that is in use using ws.get_highest_row()
3. Add the new row on the next empty row.
4. Write the updated spreadsheet back to the file

For example:

import openpyxl

file = 'input.xlsx'
new_row = ['data1', 'data2', 'data3', 'data4']

wb = openpyxl.load_workbook(filename=file)
ws = wb['Sheet1']     # Older method was  .get_sheet_by_name('Sheet1')
row = ws.get_highest_row() + 1

for col, entry in enumerate(new_row, start=1):
    ws.cell(row=row, column=col, value=entry)

wb.save(file)

Note, as can be seen in the docs for XlsxWriter:

XlsxWriter is designed only as a file writer. It cannot read or modify an existing Excel file.

This approach does not require the use of Windows / Excel to be installed but does have some limitations as to the level of support.