CODEWITHSUNDEEP
pythonexcelopenpyxlxlsxxlsxwriter
ajay imade
ajay imade

Reputation: 143

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

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

Upvotes: 4

Views: 22158

Answers (2)

Martin Evans
Martin Evans

Reputation: 46759

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.

Upvotes: 10

James McCorrie
James McCorrie

Reputation: 2652

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')

Upvotes: 4

Related Questions