Integrate a GUI in a excel sheed
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,
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)
More information about the Python-list