Write to a specific Excel worksheet using python

Question

I want to overwrite specific cells in an already existing excel file. I've searched and found this answer, writing to existing workbook using xlwt. I've 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])

Notice that, self.workbook already exists in another method in the same class this way,

def open_file (self, file_name):

        try:
            self.workbook = xlrd.open_workbook(file_name)

I really don't know what this means, '.out' + os.path.splitext(self.filename)[-1], but it seems that it causes the modified file to be saved in the same path of the original one with a different name.

After running the program, a new Excel file gets saved in the same path of the original one, however it is saved with a weird name as data.xlsx.out.xlsx and it doesn't open. I think it's caused by this line '.out' + os.path.splitext(self.filename)[-1]. I removed that line in order to overwrite the original file and not saving a copy, but when running the program I become unable to open the original file and I get an error message saying that the file can't be opened because the file format or extension is not valid.

What I really want is to modify the original file not to create a modified copy.

EDIT: SiHa's answer could modify the existing file without creating a copy if only the file name is specified like this,

bookwt.save(self.filename)

And, it could save a new copy this way,

filepath, fileext = os.path.splitext(self.filename)
bookwt.save(filepath + '_out' + fileext)

Or as the line provided in my code in the question. However, in all of these methods the same problem exists, where after modifying the file it can't be opened. After searching I found that the problem could be solved by changing the extension of the original file from .xlsx to .xls. After making this change, the problem was solve. 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 You.

score 1 · Accepted Answer · answered Jul 13 '15 at 13:35

1

To explain the line in question:

(self.filename + '.out' Means concatenate `.out' to the end of the original filename.

+ os.path.splitext(self.filename)[-1]) Means split the filename into a list of ['path', 'extension'] then concatenate the last element (the extension) back onto the end again.

So you end up with data.xlsx.out.xlsx

You should just be able to use bookwt.save(self.filename), although you may run in to errors if you still have the file open for reading. It may be safer to create a copy in a similar manner to the above:

filepath, fileext = os.path.splitext(self.filename)
bookwt.save(filepath + '_out' + fileext)

Which should give you data_out.xlsx

answered Jul 13 '15 at 13:35

SiHa

7,830
13
34
43

Thanks a lot SiHa, I added the 2 lines but I had the same problem. The copied file get saved in the path successfully, but it can't be opened I get the same error message I mentioned in the question. After searching, I found that this problem could be solved if the original file is saved with extension xls not xlsx. I've just changed the file extension and the file was modified successfully without creating a new copy. I also could create a copy using the same lines you provided by concatenating xls to the file name . Now it's working, thanks a lot :) – Dania Jul 13 '15 at 18:35
1

Ah, yes. Xlwt would appear to only support .xls see [this](http://stackoverflow.com/a/21635803/3714940) question. You might want to look at [openpyxl](https://openpyxl.readthedocs.org/en/latest/) for .xlsx support – SiHa Jul 13 '15 at 20:38

score 0 · Answer 2 · answered Jul 13 '15 at 12:57

0

You can save excel file as CSV files this means that when they are open by python they show the values in plain text seperated by commas for example the spreadsheet with the address in the columns a to b and the rows 1 to 2 would look like this

A1,B1
A2,B2

this means that you can edit them like normal files and excel can still open them

answered Jul 13 '15 at 12:57

AlexanderRD

2,069
2
11
19

This doesn't answer the OP's question. – SiHa Jul 13 '15 at 13:13
this is how an excel file would be changed when it is stored in CSV form it is stored in plain text with comma relating to the next cell and a carriage return allowing it to go onto the next line – AlexanderRD Jul 13 '15 at 13:20
is there a requirement that the file must be stored in the .xls format because excel can open CSV files as normal worksheets – AlexanderRD Jul 13 '15 at 13:21
Thank you AlexanderRD for your answer, currently my code processes Excel files. But your suggestion is good if the file type is to be changed in the future. Thanks. – Dania Jul 13 '15 at 18:38

Write to a specific Excel worksheet using python

2 Answers2