How to write/update data into cells of existing XLSX workbook using xlsxwriter in python

Question

I am able to write into new xlsx workbook using

import xlsxwriter  
def write_column(csvlist):
    workbook = xlsxwriter.Workbook("filename.xlsx",{'strings_to_numbers': True})
    worksheet = workbook.add_worksheet()
    row = 0
    col = 0
    for i in csvlist:
        worksheet.write(col,row, i)
        col += 1

    workbook.close() 

but couldn't find the way to write in an existing workbook. Please help me to write/update cells in existing workbook using xlswriter or any alternative.

Insolent

I think openpxyl is the only python library that claims to both read and write files. I haven't used it, but the xlswriter docs mention it. (related: #18002633 ) – Nedra

Also Xlwings and win32com. – Pyrargyrite

Answer 1

Quote from xlsxwriter module documentation:

This module cannot be used to modify or write to an existing Excel XLSX file.

If you want to modify existing xlsx workbook, consider using openpyxl module.

See also:

• Modify an existing Excel file using Openpyxl in Python
• Use openpyxl to edit a Excel2007 file (.xlsx) without changing its own styles?

Cortezcortical

openpyxl can read existing excel files and write data back to it. However, if your excel sheet/workbook has charts, those charts will be lost. – Humidify

Answer 2

you can use this code to open (test.xlsx) file and modify A1 cell and then save it with a new name

import openpyxl
xfile = openpyxl.load_workbook('test.xlsx')

sheet = xfile.get_sheet_by_name('Sheet1')
sheet['A1'] = 'hello world'
xfile.save('text2.xlsx')

Beason

But the method you suggested involved a different library, openpyxl, instead from what was asked, xlsxwriter. – Elnora

ya because he asked for a solution " using xlswriter or any alternative " is that right? – Beason

@SagarMehta openpyxl does not support the old .xls file format, you can use xlrd to read the file, or convert it to the more recent .xlsx file format – Beason

@Beason how about if I want to update an existing sheet (or multiple sheet) where each sheet is an entire pandas dataframe. How will make code look differently? Say I have 3 sheets, Sheet1, Sheet2, Sheet3, which exist and I want to update them with 3 pandas dataframes – Desai

Answer 3

Note that openpyxl does not have a large toolbox for manipulating and editing images. Xlsxwriter has methods for images, but on the other hand cannot import existing worksheets...

I have found that this works for rows... I'm sure there's a way to do it for columns...

import openpyxl

oxl = openpyxl.load_workbook('File Loction Here')
xl = oxl.['SheetName']

x=0
col = "A"
row = x

while (row <= 100):
    y = str(row)
    cell = col + row
    xl[cell] = x
    row = row + 1
    x = x + 1

Allergy

Answer 4

You can do by xlwings as well

import xlwings as xw
for book in xlwings.books:
    print(book)

Stereochrome