30

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,501
  • 1
  • 15
  • 29
user2787436
  • 319
  • 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

5 Answers5

64

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.
  • 10,399
  • 5
  • 62
  • 91
alecxe
  • 441,113
  • 110
  • 1,021
  • 1,148
  • 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
21

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
  • 895
  • 11
  • 14
  • 1
    But the method you suggested involved a different library, *openpyxl*, instead from what was asked, *xlsxwriter*. – ankostis Feb 03 '16 at 13:14
  • 9
    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
  • 6
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
  • 1
  • 6
-12

If you have issue with writing into an existing xls file because it is already created you need to put checking part like below:

PATH='filename.xlsx'
if os.path.isfile(PATH):
    print "File exists and will be overwrite NOW"
else:
    print "The file is missing, new one is created"

... and here part with the data you want to add

nadgnilykangur
  • 1