34

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.

Joel Vroom
  • 1,611
  • 1
  • 16
  • 30
user2787436
  • 359
  • 1
  • 3
  • 5
  • 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: http://stackoverflow.com/questions/18002133/xlsxwriter-is-there-a-way-to-open-an-existing-worksheet-in-my-workbook ) – Wooble Sep 17 '13 at 12:17
  • Also Xlwings and win32com. – moken Jul 31 '23 at 02:59

4 Answers4

70

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:

Jean-Francois T.
  • 11,549
  • 7
  • 68
  • 107
alecxe
  • 462,703
  • 120
  • 1,088
  • 1,195
  • 7
    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. – alpha_989 Sep 24 '17 at 03:11
24

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')
Ayser
  • 1,035
  • 12
  • 15
  • 1
    But the method you suggested involved a different library, *openpyxl*, instead from what was asked, *xlsxwriter*. – ankostis Feb 03 '16 at 13:14
  • 12
    ya because he asked for a solution " using xlswriter or any alternative " is that right? – Ayser Feb 04 '16 at 22:38
  • 1
    @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 – Ayser May 01 '16 at 12:44
  • @Ayser 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 – bernando_vialli Mar 22 '18 at 13:38
2

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
other
  • 93
  • 1
  • 7
user8545038
  • 21
  • 1
0

You can do by xlwings as well

import xlwings as xw
for book in xlwings.books:
    print(book)
Yonghwan Shin
  • 89
  • 2
  • 6