how to rename the excel filename after using xlrd

[nike]

i use xlrd and xlutils to add a new sheet to an existing excel
file,and then i want to rename the old file to a new name,but work
wrong.

WindowsError: [Error 32]

the file is open by xlrd,is there any way to close the file?

my code:

def update(self,pathfile,data,sheetname):
from xlrd import open_workbook
from xlutils.copy import copy
rb = open_workbook(pathfile,on_demand=True)
wb = copy(rb)
#新建工作表
sheet = wb.add_sheet(sheetname)
for row in data:
rowx += 1
for colx, value in enumerate(row):
sheet.write(rowx, colx, value)

import os,os.path

os.rename(pathfile,os.path.join(os.path.dirname(pathfile),'bak.xls'))
wb.save(pathfile)

[John Machin]

On 16/03/2010 4:55 PM, nike wrote:
> i use xlrd and xlutils to add a new sheet to an existing excel
> file,and then i want to rename the old file to a new name,but work
> wrong.

Hi nike,

>
> WindowsError: [Error 32]
>
> the file is open by xlrd,is there any way to close the file?
>
> my code:
>
> def update(self,pathfile,data,sheetname):
> from xlrd import open_workbook
> from xlutils.copy import copy
> rb = open_workbook(pathfile,on_demand=True)
> wb = copy(rb)

Problem cause: At this point you have finished with the xlrd Book
object, but because on_demand is True and use_mmap is True, the Book
object is still holding a reference to a mmap object in case you load
more sheets. Although the open() file handle has been closed long ago,
the mmap being in use prevents the rename from happening.

Solution: "del" the Book object as soon as you don't need it any more.
In this case, right here, immediately after the xlutils.copy.copy()
operation.

wb = copy(rb)
# Finished using xlrd Book object
del rb
sheet = wb.add_sheet(sheetname)

I'll add a sentence or two to the xlrd docs mentioning that it's a good
idea to del the Book object as soon as you no longer need it.

Regards,

John

[Steve Romanow]

Thats interesting. I dont think I would have seen that, but I'm a
noob. Does that raise an exception that identifies the problem besides
"Cannot Write".

[John Machin]

On 16/03/2010 11:50 PM, Steve Romanow wrote:
> On 3/16/2010 4:54 AM, John Machin wrote:
>> On 16/03/2010 4:55 PM, nike wrote:
>>> i use xlrd and xlutils to add a new sheet to an existing excel
>>> file,and then i want to rename the old file to a new name,but work
>>> wrong.

>>> WindowsError: [Error 32]

>>>
>>> the file is open by xlrd,is there any way to close the file?
>>>
>>> my code:
>>>
>>> def update(self,pathfile,data,sheetname):
>>> from xlrd import open_workbook
>>> from xlutils.copy import copy
>>> rb = open_workbook(pathfile,on_demand=True)
>>> wb = copy(rb)
>>
>> Problem cause: At this point you have finished with the xlrd Book
>> object, but because on_demand is True and use_mmap is True, the Book
>> object is still holding a reference to a mmap object in case you load
>> more sheets. Although the open() file handle has been closed long ago,
>> the mmap being in use prevents the rename from happening.

> Thats interesting. I dont think I would have seen that, but I'm a
> noob.

It took a bit of head-scratching before I caught on, because xlrd very
definitely closes the open() handle as soon as it has extracted the
Workbook stream from the file. The gotcha is that if the Workbook stream
is contiguous, it's extracted as an mmap object (if mmap is available)
... these live independently.

Hence my intention to point out in the docs that "del Book_object as
soon as possible" is a good idea under any circumstances.

>
> Does that raise an exception that identifies the problem besides
> "Cannot Write".

On the OP's box (see above) and on mine it raises WindowsError:

>>> import xlrd, os
>>> rb = xlrd.open_workbook('foo.xls', on_demand=True)
>>> os.rename('foo.xls', 'bar.xls')
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
WindowsError: [Error 32] The process cannot access the file because it
is being used by another process
>>>

I don't understand your question ... is "Cannot Write" part of the
response on a *x box?

It would help if you were to show the output that you got when you tried
it (and your input as well).

Cheers,

John

[Steve Romanow]

I'm sorry John. I was paraphrasing, just thinking about the exception
that would be thrown in that cicumstance. Sorry to distrtact.

> --
> You received this message because you are subscribed to the Google Groups
> "python-excel" group.
> To post to this group, send an email to python...@googlegroups.com.
> To unsubscribe from this group, send email to
> python-excel...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/python-excel?hl=en-GB.
>
>

--
Sent from my mobile device

[lw]

thank you very much!
it works very well!

On 3/16/10, John Machin <sjma...@lexicon.net> wrote:

[lw]

a new problem:after use xlutils.margins.number_of_good_rows, the xls
file can't be renamed.

my code:

class Excel:

@classmethod
def update(self,data,pathfile,newsheet=True,sheetname="",output=""):

rb = xlrd.open_workbook(pathfile,on_demand=True)
wb = copy(rb)

#add a new sheet
if newsheet:
sheet = wb.add_sheet(sheetname)
#add head
rowx=0
else:
#add to exsting sheet
rbsheet=rb.sheet_by_name(sheetname)
rowx=number_of_good_rows(rbsheet)
#rows number
sheetindex=rb.sheets().index(rbsheet)

sheet=wb.get_sheet(sheetindex)

for row in data:

for colx, value in enumerate(row):
sheet.write(rowx, colx, value)

rowx += 1

#rename the old file
del rb,sheet,rbsheet
if output=="":
output=pathfile
import os,os.path
os.rename(pathfile,os.path.join(os.path.dirname(pathfile)+time.strftime('%Y-%m-%d
%H-%M-%S')+".xls"))

#produce a new file
wb.save(output)

i delete rb,sheet,rbsheet,and can't delete the pathfile.

error messege after running it:

Traceback (most recent call last):

File "D:\Documents and Settings\ww\tmp.py", line 6, in <module>
foo.data2excel(data)
File "G:\Syncplicity\ss.py", line 273, in data2excel
Excel.update(data,pathfile,newsheet,sheetname,output)
File "G:\Syncplicity\ss.py", line 522, in update
os.rename(pathfile,"G:\\"+time.strftime('%Y-%m-%d %H-%M-%S')+".xls")
WindowsError: [Error 32]
Process terminated with an exit code of 1

and i try to delete the file throught explorer.exe, and fails.

anybody konw how to unpack this file and then i can rename it?

[Chris Withers]

lw wrote:
> a new problem:after use xlutils.margins.number_of_good_rows, the xls
> file can't be renamed.

repeat posting the same question because you didn't get a response is
just annoying.

Please desist.

Chris

--
Simplistix - Content Management, Batch Processing & Python Consulting
- http://www.simplistix.co.uk

[Chris Withers]

Chris Withers wrote:
> lw wrote:
>> a new problem:after use xlutils.margins.number_of_good_rows, the xls
>> file can't be renamed.
>
> repeat posting the same question because you didn't get a response is
> just annoying.

Sorry, wrong complaint; if you have a new problem, start a new thread...

> Please desist.

...same advice mind ;-)

[John Machin]

On 9/04/2010 4:24 PM, lw wrote:
> a new problem:after use xlutils.margins.number_of_good_rows, the xls
> file can't be renamed.

Does that mean that you have verified that
xlutils.margins.number_of_good_rows is causing the problem? If so, how?

> my code:
>
> class Excel:
>
>
>
> @classmethod
> def update(self,data,pathfile,newsheet=True,sheetname="",output=""):
>
> rb = xlrd.open_workbook(pathfile,on_demand=True)

Have you (recalling the earlier discussion) tried mmap=False and/or
on_demand=False?

> wb = copy(rb)
>
> #add a new sheet
> if newsheet:
> sheet = wb.add_sheet(sheetname)
> #add head
> rowx=0
> else:
> #add to exsting sheet
> rbsheet=rb.sheet_by_name(sheetname)
> rowx=number_of_good_rows(rbsheet)

What happens if you use
rowx = rbsheet.nrows
instead?

> #rows number
> sheetindex=rb.sheets().index(rbsheet)
>
> sheet=wb.get_sheet(sheetindex)
>
> for row in data:
> for colx, value in enumerate(row):
> sheet.write(rowx, colx, value)
> rowx += 1
>
> #rename the old file
> del rb,sheet,rbsheet

"sheet" is an xlwt sheet. It shouldn't be part of the problem.

> if output=="":
> output=pathfile
> import os,os.path
> os.rename(pathfile,os.path.join(os.path.dirname(pathfile)+time.strftime('%Y-%m-%d
> %H-%M-%S')+".xls"))

os.rename(pathfile, os.path.join(
os.path.dirname(pathfile) + time.strftime('etc etc') + ".xls"
))

os.path.join(only_one_arg) is a bit mysterious. Perhaps that first "+"
should be a comma. Perhaps you should set out your code in a more
understandable fashion. Have you read PEP 8?

> #produce a new file
> wb.save(output)
>
> i delete rb,sheet,rbsheet,and can't delete the pathfile.
>
> error messege after running it:
>
> Traceback (most recent call last):
> File "D:\Documents and Settings\ww\tmp.py", line 6, in <module>
> foo.data2excel(data)
> File "G:\Syncplicity\ss.py", line 273, in data2excel
> Excel.update(data,pathfile,newsheet,sheetname,output)
> File "G:\Syncplicity\ss.py", line 522, in update
> os.rename(pathfile,"G:\\"+time.strftime('%Y-%m-%d %H-%M-%S')+".xls")
> WindowsError: [Error 32]

Can you possibly run it from the commandline or in some other fashion
that doesn't suppress the full text of the error message?

> Process terminated with an exit code of 1
>
> and i try to delete the file throught explorer.exe, and fails.

"and"? You try to delete the file using Windows Explorer *after* getting
the above error messages, and it fails?? What are the symptoms of failure?