Writing to specific Excel worksheet using python
I want to overwrite specific cells in an already existing excel file. I searched and found this answer, writing to an existing book using xlwt . I have applied it as the following,
def wrtite_to_excel (self):
#first I must open the specified excel file, sice open_file is in the same class, hence we can get it using self.sheet.
bookwt = copy(self.workbook)
sheetwt= bookwt.get_sheet(0)
#now, I must know the column that was estimated so I overwrite it,
colindex= self.columnBox.current() #returns the index of the estimated column
for i in range (1, self.grid.shape[0]):
if (str (self.sheet.cell_value(i,colindex)).lower () == self.missingBox.get().lower()):
#write the estimated value:
sheetwt.write (i, colindex, self.grid[i])
bookwt.save(self.filename + '.out' + os.path.splitext(self.filename)[-1])
Note that self.workbook already exists in another method in the same class,
def open_file (self, file_name):
try:
self.workbook = xlrd.open_workbook(file_name)
I don't really know what it means, '.out' + os.path.splitext (self.filename) [- 1], but it seems that it makes the modified file save in the same path the original with a different name.
After starting the program, the new Excel file is saved in the same path as the original one, however, it is saved with a strange name data.xlsx.out.xlsx
and will not open. I think it is caused by this line '.out' + os.path.splitext(self.filename)[-1]
. I removed this line to overwrite the original file and not save the copy, but when I run the program, I am unable to open the original file and I get an error saying the file cannot be opened because the file format or extension is invalid.
I really want to modify the original file so that I don't create a modified copy.
EDIT: SiHa's answer can modify an existing file without making a copy if only the filename is given,
bookwt.save(self.filename)
And he can save a new copy this way
filepath, fileext = os.path.splitext(self.filename)
bookwt.save(filepath + '_out' + fileext)
Or as the string contained in my code in the question. However, all of these methods have the same problem where after modifying a file, it cannot be opened. After searching, I found that the problem can be resolved by changing the extension of the original file from .xlsx to .xls. After this change, the issue was resolved. This is the link where I found the solution http://www.computing.net/answers/office/the-file-formatfile-extension-is-not-valid/19454.html
Thank.
source to share
To explain this line:
(self.filename + '.out'
The tool concatenates `.out 'to the end of the original filename.
+ os.path.splitext(self.filename)[-1])
The tool splits the filename into a list ['path', 'extension']
and then concatenates the last element (extension) to the end again.
So you are done with data.xlsx.out.xlsx
You can just use it bookwt.save(self.filename)
, although you might run into errors if you still have the file to read. It might be safer to create a copy similar to above:
filepath, fileext = os.path.splitext(self.filename)
bookwt.save(filepath + '_out' + fileext)
What should give you data_out.xlsx
source to share
You can save the excel file as CSV files, this means when opened with python, they show the values in plaintext, separated by commas, like a table with address in ab columns, and rows 1 through 2 look like this
A1,B1 A2,B2
this means you can edit them like regular files and excel can still open them
source to share