Quantcast

Opening, Modifying, and Saving an Excel File from Python?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Opening, Modifying, and Saving an Excel File from Python?

Little Guy

Hi,

 

I apologize for the lengthy post. 

 

I’m using Python 2.7, Win 7, Excel 2003.  When trying to enter a time object and text data into a, simple, Excel 2003 file.  I’ve tried different combinations for saving the file.  I’ve used workbook.Save(),  workbook.SaveAs(filename), etc.  Sometimes I receive a replace file dialog box, but other times, I receive following message, when it tries to save:

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Traceback (most recent call last):

File "C:\Users\tech\Documents\Python\row_end.py", line 63, in <module>  workbook.Save()

File "C:\Python27\lib\site-packages\win32com\gen_py\00020813-0000-0000-C000-000000000046x0x1x5\_Workbook.py",

line 207, in Save return self._oleobj_.InvokeTypes(283, LCID, 1, (24, 0), (),) com_error: (-2147352567, 'Exception occurred.',

(0, u'Microsoft Office Excel', u"Your changes could not be saved to 'simple.xls' because of a sharing violation. Try saving to a different file.", u'C:\\Program Files (x86)\\Microsoft Office\\OFFICE11\\1033\\xlmain11.chm', 0, -2146827284), None)

 

What I’d like to do is:

 

Open an excel file, stored either locally or on a net share,

Look for the last row in a particular sheet,  say Sheet1,

Append a time object to last row + 1, first column,

Append text data to last row + 1, second column,

Ask to save the file (1 = save, 2 = discard changes)

Save the Excel workbook,

Close and Quit without confirmation dialogs.

 

Basically, there is a sharing violation, sometimes excel.exe is in task manager and sometimes it is not, but the sharing

violation message appears to cause the script to crash.  I’ve tried saving the file to a different file name, which works,

and then closing the current file, then removing it, but still get a sharing violation as it appears to still be in use by some

unknown process, probabaly the OS, though can’t track which process it is.

 

Is there a simple way to save an opened Excel file without getting errors?  As win32com documentation (on python side)

is scarce, I’m using xlrd to get the last row on the excel sheet.

 

I’m using this simple test code, which, due to, trial and error experimentation, has grown a bit unwieldy:

 

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

#!/usr/bin/env python

 

import shutil                                          # shell utility module to work with os

from xlrd import open_workbook           # Third party library allows us to open Excel files, find last row.

import pythoncom                                 # Allows us to use MakeTime(date_object)

import win32com.client                          # Allows to connect to Windows object using COM objects.

import os, sys, time                              # Allows us to work with time and os functions.

 

# Set the Excel file name, working directory and path

old_file_name = 'simple.xls'

working_dir = r"C:\Users\some_user\Documents\Python" + os.sep

old_file_path = os.path.join(working_dir, old_file_name)

 

#-----------------------------xlrd section-----------------------------------------------

# Open the Excel file for reading

book = open_workbook(old_file_path, on_demand=True) 

sheet = book.sheet_by_name('Current')

lst_row = sheet.nrows

print lst_row

#-------------------------Today's date section-------------------------------------

# Returns date object (yyyy-mm-dd)

today = date.today()    print today

date_today = pythoncom.MakeTime(today)  # Return date/time object (mm/dd/yy hh:mm:ss)

print date_today

#------------------------------------------------------------------------------------

 

# The win32com function to open Excel.

xlApp = win32com.client.Dispatch("Excel.Application")

xlApp.Visible = True

 

# Open the file we want in Excel

workbook = xlApp.Workbooks.Open(old_file_path)

 

# Extract some of the file's components we may need.

workbook = xlApp.ActiveWorkbook

 

xlApp.Sheets('Current').Select()

activesheet = xlApp.ActiveSheet

xlSheet.Cells(lst_row + 1, 1).Value = “Time Object”  # Text data for now

xlSheet.Cells(lst_row + 1, 2).Value = date_today

 

save_file = int(raw_input("Save the data Yes = 1, No = 2? "))

try:

    if save_file == 1:

        workbook.Saved = 0

        workbook.Save()

        workbook.Close(SaveChanges=True)

    else:

        print 'File not saved!'

        workbook.Close()

    xlApp.Quit()

    del activesheet

except KeyboardInterrupt:

    print 'Error: '

 

xlApp.Visible = 0

xlApp = None

del xlApp

del workbook

pythoncom.CoUninitialize()

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

Regards,

Little Guy


_______________________________________________
python-win32 mailing list
[hidden email]
http://mail.python.org/mailman/listinfo/python-win32
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Opening, Modifying, and Saving an Excel File from Python?

Tim Roberts
The Little Guy wrote:

>
> Hi,
>
>  
>
> I apologize for the lengthy post.
>
>  
>
> I’m using Python 2.7, Win 7, Excel 2003.  When trying to enter a time
> object and text data into a, simple, Excel 2003 file.  I’ve tried
> different combinations for saving the file.  I’ve used
> workbook.Save(),  workbook.SaveAs(filename), etc.  Sometimes I receive
> a replace file dialog box, but other times, I receive following
> message, when it tries to save:
>

It is important to remember that none of these cause Excel to exit:
    xlApp.Visible = 0
    xlApp = None
    del xlApp

If you have Excel open with your file and set Visible to 0, it can be
very difficult to remember that your file is still open.  You should use
xlApp.Quit() in every exit path.

--
Tim Roberts, [hidden email]
Providenza & Boekelheide, Inc.

_______________________________________________
python-win32 mailing list
[hidden email]
http://mail.python.org/mailman/listinfo/python-win32
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Opening, Modifying, and Saving an Excel File from Python?

Little Guy
In reply to this post by Little Guy

Mr. Roberts,

I tried your suggestion about the varialble.quit() usage but still get the same results.

What normally happens is that I open up the excel file, and make it visible.  I then try to populate two cells,
at the last row + 1 with text, then try to save, close and quit.  What occurs is that, either, sharing violations
occur, and the script crashes, or another instance of excel pops up, with a save dialog box, asking to save
with a random filename generated by the OS, I presume.  Yesterday, I noted that there were over 15 of these
randome filenames saved in the directory of the exce file, but all lacking an .xls extension.  This occured
eventhough, I chose not to save the excel file when the save dialog window came up. 

No matter what I do, the workbook does not close and quit, silently saving all changes, it either crashes or
aks to save the file, providing a different filename. 

Thanks for the reply,
LG

----- Original Message -----

From: [hidden email]

Sent: 08/23/11 03:00 AM

To: [hidden email]

Subject: python-win32 Digest, Vol 101, Issue 22


 
Send python-win32 mailing list submissions to 
	[hidden email] 

To subscribe or unsubscribe via the World Wide Web, visit 
	http://mail.python.org/mailman/listinfo/python-win32 
or, via email, send a message with subject or body 'help' to 
	[hidden email] 

You can reach the person managing the list at 
	[hidden email] 

When replying, please edit your Subject line so it is more specific 
than "Re: Contents of python-win32 digest..." 


Today's Topics: 

   1. Re: SHOpenFolderAndSelectItems (Scott Nelson) 
   2. Re: Opening, Modifying, and Saving an Excel File from Python? 
      (Tim Roberts) 


---------------------------------------------------------------------- 

Message: 1 
Date: Mon, 22 Aug 2011 09:10:58 -0500 
From: Scott Nelson <[hidden email]> 
To: [hidden email] 
Cc: [hidden email] 
Subject: Re: [python-win32] SHOpenFolderAndSelectItems 
Message-ID: 
	<CAH30xazCoEGiJfAo=[hidden email]> 
Content-Type: text/plain; charset="iso-8859-1" 

Done (ID #3396444) 

https://sourceforge.net/tracker/?func=detail&aid=3396444&group_id=78018&atid=551957 
Many thanks for considering this! 
On Sun, Aug 21, 2011 at 10:15 PM, Mark Hammond <[hidden email]>wrote: 

> On 20/08/2011 5:40 AM, Scott Nelson wrote: 
> ... 
> 
> Does pywin have support for SHOpenFolderAndSelectItems()? 
>> 
> 
> Unfortunately not - you can open a feature request at sourceforge and I'll 
> add it for the next release. 
> 
> Cheers, 
> 
> Mark 
> 
-------------- next part -------------- 
An HTML attachment was scrubbed... 
URL: <http://mail.python.org/pipermail/python-win32/attachments/20110822/a315b006/attachment-0001.html> 

------------------------------ 

Message: 2 
Date: Mon, 22 Aug 2011 14:01:51 -0700 
From: Tim Roberts <[hidden email]> 
To: Python-Win32 List <[hidden email]> 
Subject: Re: [python-win32] Opening, Modifying, and Saving an Excel 
	File from Python? 
Message-ID: <[hidden email]> 
Content-Type: text/plain; charset="windows-1252" 

The Little Guy wrote: 
> 
> Hi, 
> 
>  
> 
> I apologize for the lengthy post. 
> 
>  
> 
> I?m using Python 2.7, Win 7, Excel 2003.  When trying to enter a time 
> object and text data into a, simple, Excel 2003 file.  I?ve tried 
> different combinations for saving the file.  I?ve used 
> workbook.Save(),  workbook.SaveAs(filename), etc.  Sometimes I receive 
> a replace file dialog box, but other times, I receive following 
> message, when it tries to save: 
> 

It is important to remember that none of these cause Excel to exit: 
    xlApp.Visible = 0 
    xlApp = None 
    del xlApp 

If you have Excel open with your file and set Visible to 0, it can be 
very difficult to remember that your file is still open.  You should use 
xlApp.Quit() in every exit path. 

-- 
Tim Roberts, [hidden email] 
Providenza & Boekelheide, Inc. 



------------------------------ 

_______________________________________________ 
python-win32 mailing list 
[hidden email] 
http://mail.python.org/mailman/listinfo/python-win32 


End of python-win32 Digest, Vol 101, Issue 22 
*********************************************

 




Regards, 
Little Guy



_______________________________________________
python-win32 mailing list
[hidden email]
http://mail.python.org/mailman/listinfo/python-win32
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Opening, Modifying, and Saving an Excel File from Python?

David Robinow
In reply to this post by Little Guy
On Sat, Aug 20, 2011 at 10:55 PM, The Little Guy <[hidden email]> wrote:
> Hi,
> I apologize for the lengthy post.
 The code you posted does not run at all. I had to rename a few things
to get it to do anything at all.

It appears that xlrd is holding a reference to the excel file.  I was
able to get it to work by adding:

book.release_resources()


after the xlrd code.
_______________________________________________
python-win32 mailing list
[hidden email]
http://mail.python.org/mailman/listinfo/python-win32
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Opening, Modifying, and Saving an Excel File from Python?

Little Guy

Mr. Robinow,

 

The code is only a small test code, not really meant to do anything.

It’s just meant to be used as a simple test to see if I can save data

onto an excel sheet. This is the final part of larger code project.

All it does is open an excel file, append data to it, and try to save

the modified file. Most likely, I may have accidentally, left a line

or two out of the code which is probabaly why it did not work for you.

I apologize for that error on my part.

 

Since win32com documentation is not localized, not easily accessible,

at least by me, coding is a trial and error thing for me.

 

FYI: I tried your suggestion and it worked. Now if I can only find the

win32com way of finding the last row and column in a sheet, I’d be a

happy camper J

 

Thanks for the assist

Little Guy

 

-----Original Message-----
From: David Robinow [mailto:[hidden email]]
Sent: Tuesday, August 23, 2011 12:59 PM
To: The Little Guy
Cc: [hidden email]
Subject: Re: [python-win32] Opening, Modifying, and Saving an Excel File from Python?

 

On Sat, Aug 20, 2011 at 10:55 PM, The Little Guy <[hidden email]> wrote:

> Hi,

> I apologize for the lengthy post.

 The code you posted does not run at all. I had to rename a few things

to get it to do anything at all.

 

It appears that xlrd is holding a reference to the excel file.  I was

able to get it to work by adding:

 

book.release_resources()

 

 

after the xlrd code.


_______________________________________________
python-win32 mailing list
[hidden email]
http://mail.python.org/mailman/listinfo/python-win32
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

ImportError: No module named xxxxxx

pythonStudent
This post has NOT been accepted by the mailing list yet.
In reply to this post by Little Guy
Hello,

When I issue the following statement:

>>> import win32com.client

I got the following result:

Traceback (most recent call last):
  File "<pyshell#3>", line 1, in <module>
    import win32com.client
ImportError: No module named win32com.client

These happened too with the following statements:

>>> from xlrd import open_workbook

Traceback (most recent call last):
  File "<pyshell#1>", line 1, in <module>
    from xlrd import open_workbook
ImportError: No module named xlrd
>>> import pythoncom

Traceback (most recent call last):
  File "<pyshell#2>", line 1, in <module>
    import pythoncom
ImportError: No module named pythoncom


I there anything I need to do first or install first before I execute these statements?
Please help.

Thank you.
Loading...