I am unable to find examples where xlwt is used to write into existing files. I have a existing xls file that I need to write to. When I use xlrd to read the file, I cant seem to figure out how to transform the “Book” type returned into a xlwt.Workbook. I would appreciate if someone can point me to an example.
Here’s some sample code I used recently to do just that.
It opens a workbook, goes down the rows, if a condition is met it writes some data in the row. Finally it saves the modified file.
from xlutils.copy import copy # http://pypi.python.org/pypi/xlutils from xlrd import open_workbook # http://pypi.python.org/pypi/xlrd START_ROW = 297 # 0 based (subtract 1 from excel row number) col_age_november = 1 col_summer1 = 2 col_fall1 = 3 rb = open_workbook(file_path,formatting_info=True) r_sheet = rb.sheet_by_index(0) # read only copy to introspect the file wb = copy(rb) # a writable copy (I can't read values out of this, only write to it) w_sheet = wb.get_sheet(0) # the sheet to write to within the writable copy for row_index in range(START_ROW, r_sheet.nrows): age_nov = r_sheet.cell(row_index, col_age_november).value if age_nov == 3: #If 3, then Combo I 3-4 year old for both summer1 and fall1 w_sheet.write(row_index, col_summer1, 'Combo I 3-4 year old') w_sheet.write(row_index, col_fall1, 'Combo I 3-4 year old') wb.save(file_path + '.out' + os.path.splitext(file_path)[-1])
xlutils.copy. Try something like this:
from xlutils.copy import copy w = copy('book1.xls') w.get_sheet(0).write(0,0,"foo") w.save('book2.xls')
Keep in mind you can’t overwrite cells by default as noted in this question.
The code example is exactly this:
from xlutils.copy import copy from xlrd import * w = copy(open_workbook('book1.xls')) w.get_sheet(0).write(0,0,"foo") w.save('book2.xls')
You’ll need to create book1.xls to test, but you get the idea.
# -*- coding: utf-8 -*- import openpyxl file = 'sample.xlsx' wb = openpyxl.load_workbook(filename=file) # Seleciono la Hoja ws = wb.get_sheet_by_name('Hoja1') # Valores a Insertar ws['A3'] = 42 ws['A4'] = 142 # Escribirmos en el Fichero wb.save(file)
I had the same problem. My customer ordered me Python 3.4 script that updates XLS (not XLSX) Excel files.
The 1st package xlrd was installed by “pip install” without problems in my Python home.
The 2nd one xlwt needed to say “pip install xlwt-future” to be compatible.
The 3rd one xlutils has no support for Python 3, but I adapted it a little bit and now it works at least for dummy script:
#!C:Python343python from xlutils.copy import copy # http://pypi.python.org/pypi/xlutils from xlrd import open_workbook # http://pypi.python.org/pypi/xlrd from xlwt import easyxf # http://pypi.python.org/pypi/xlwt file_path = 'C:DevTest_upd.xls' rb = open_workbook('C:DevTest.xls',formatting_info=True) r_sheet = rb.sheet_by_index(0) # read only copy to introspect the file wb = copy(rb) # a writable copy (I can't read values out of this, only write to it) w_sheet = wb.get_sheet(0) # the sheet to write to within the writable copy w_sheet.write(1, 1, 'Value') wb.save(file_path)
I attached the file here: http://ifolder.su/43507580
Write to firstname.lastname@example.org if it got expired.
P.S.: Some functions are not called in the dummy example, so maybe they will need for an adaptation also. Who wants to do it, fix exceptions one-by-one with a google help. It’s not a very difficult task, because the package code is small…