Integrate a GUI in a excel sheed

Jason stalcair at speakeasy.net
Wed Feb 19 00:46:22 CET 2003


Brian (and anyone else),
do you know of a way to input and output data in Word and Excel format 
without going through the actual COM from the respective applications? 
I am attempting to write some small scripts to convert some poorly 
written Excel sheets into a database and then generate better designed 
Excel reports as needed from the database.

However, this is on my own spare time and I use OpenOffice at home. 
Even so, if I did have MS Office I would not want to rely on the 
presence of the applications simply to read and write the formats.  Note 
that I do not plan on doing anything fancy, just plain text with perhaps 
a handfull of simple formulas. (data sums)

So far my searching online has only turned up solutions requiring the 
app to be installed.  I do find this a bit amusing since when someone 
mentions "outputting to Excel" the solution presented by many here is 
the only one presented (i.e. ms = client.Dispatch("Excel.Application")) 
yet I have yet to see a similar requirement for Adobe Acrobat to be 
installed to output to PDF format.  Surely there is a way to do this 
without too much pain.

Thanks in advance,
Jason

p.s. I am checking next with the folks who work with OpenOffice.  If 
things go my way there will be a reusable library written in C used by 
OpenOffice for its read/write of various formats to include Excel.

Brian Kelley wrote:
> I have actually done this and it wasn't very easy.  The basic steps that 
>  I took were as follows.
> 
> Excel -->  Visual Basic Wrapper --> Python COM Server --> os.spawn 
> Remote wxPython GUI application
> 
> Spawning the GUI doesn't block the COM server which is essential. 
> Otherwise EXCEL hangs very hard.
> 
> So getting the data into the remote GUI was fairly easy.  The hard part 
> was letting the COM server "know" where the GUI app was located so it 
> could be launched from a system call.  I used the windows registry for 
> this purpose and my had installer write the location of the gui app.
> 
> Now the hard part, what about getting data *back* into excel?
> 
> When the GUI is called it is sent the Active workbook and worksheet 
> numbers along with any relavant cell information and data.  If the 
> remote GUI has the workbook and worksheet it can write any desired 
> information back to excel.
> 
>  >>> ms = client.Dispatch("Excel.Application")
>  >>> book = ms.Workbooks(1) # appropriate workbook #
>  >>> book.Worksheets(1)     # appropriate sheet #
>  >>> sheet = book.Worksheets(1)
>  >>> sheet.Cells(1,1).Value = "foo"
> 
> What are the problems with this approach?  wxPython startup times are 
> abyssmal and the overhead is huge about 10MB.  The COM server doesn't 
> talk to the GUI so EXCEL really can't inspect any GUI states.  You could 
> fix this by letting the COM Object query the GUI through a socket or 
> file based interface.
> 
> I think that this approach would be pretty good for your application. 
> You aren't really "embedding" wxPython into excel, merely launching it 
> with a mechanism for excel automation.
> 
> I actually just submitted a proposal for a Python tutorial for this at 
> PyCON so I'm delighted to see that someone may actually use it :)  Even 
> if I don't do the tutorial I will be writing up the whole processes. 
> Feel free to ask any more questions if you are interested.  I am 
> actually using this approach in a commercial product (which means that I 
> feel relatively confident that it is stable)
> 
> Brian
> 






More information about the Python-list mailing list