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

Solution 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.

Solution 2

Try xlwings (currently available from http://xlwings.org) it is suitable for both reading and writing excel files.

Everything you need is in the quickstart tutorial. Something like this should be what you want.

import xlwings as xw

with open("FileName.xlsx", "w") as file:
    wb = xw.Book(file)  # Creates a connection with workbook
    xw.Range('A1:D1').value = [1,2,3,4]

Selecting a Sheet

In order to read and write data to a specific sheet. You can activate a sheet and then call Range('cell_ref').

Sheet('Sheet1').activate();

Using Range to select cells

To select a single cell on the current worksheet

a = xw.Range('A1').value;
xw.Range('A1').value = float(a)+5;

To explicitly select a range of cells

xw.Range('A1:E8').value = [new_cell_values_as_list_of_lists];
xw.Range('Named range').value = [new_cell_values_as_list_of_lists];

To automatically select a contiguous range of populated cells that start from 'A1' and go right and down... until empty cell found.

Range('A1').table.value;

It is also possible to just select a row or column using:

Range('A1').vertical.value;
Range('A1').horizontal.value;

Other methods of creating a range object (from the api doc enter link description here)

Range('A1')          Range('Sheet1', 'A1')          Range(1, 'A1')
Range('A1:C3')       Range('Sheet1', 'A1:C3')       Range(1, 'A1:C3')
Range((1,2))         Range('Sheet1, (1,2))          Range(1, (1,2))
Range((1,1), (3,3))  Range('Sheet1', (1,1), (3,3))  Range(1, (1,1), (3,3))
Range('NamedRange')  Range('Sheet1', 'NamedRange')  Range(1, 'NamedRange')

Comments

[ajay imade]

I agree, XlsxWriter is only designed to file write , not for updating existing file

[James McCorrie]

True, although if this link becomes invalid then it's probably because xlwings is no longer available. At which point it's irreverent if I've copied bits from the quick start into this post or not, isn't it?

[George Stocker]

@djmcorrie the answer is not just for you, it's for everyone who follows you. Answers that say, "Just use this tool" without any context or direct relation to solving the OP's problem (Such as showing the tool being used to solve the OP's problem) are not useful on this site, and depending on the severity of the answer, either are downvoted or deleted. I deleted this in response to a flag that indicated such; and I did. Then this answer was drastically improved, and so I undeleted it. This is how an answer on Stack Overflow that involves a third party tool should look.

[James McCorrie]

Okay thanks George I'll try and answer better next time ;)

[Jason S]

It looks like xlwings got rewritten so this answer is no longer valid. Which is an argument for NOT copying bits of 3rd-party documentation.

[James McCorrie]

Thanks for the heads up Jason, I've updated the answer to reflect current docs... ironically the URL had also changed. So i guess neither approach is perfect. Although personally I think both is probably a good balance as it give more chance to get the information needed.

[James McCorrie]

There are limitations with openpyxl at the moment... it can't handle graphs and some other objects. It would be worth reviewing the docs and checking that your worksheet isn't going to be affected by these limitations. Another option is try it and see ;)

[James McCorrie]

xlwings uses the Excel COM API, so shouldn't affect unsupported elements like graphs. But if your just using simple Excel workbooks, then openpyxl is a good option. I'm sure it will improve though...