OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Generating a Spreadsheet via Python

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
fatchan
Newbie
Newbie


Joined: 22 Apr 2005
Posts: 1

PostPosted: Fri Apr 22, 2005 5:31 pm    Post subject: Generating a Spreadsheet via Python Reply with quote

Hi All,

I have a bunch of statistical data that I would like to format into a spreadsheet with python. The easiest route i could think of was to use python to generate a csv file, but the problem is i want to have a more robust spreadsheet capable of representing different colored cells. Has anyone tried to use python to generate SXW files?

I did a quick search on this matter but could not find a post with the answer i seek, any help would be appreciated,.

Thanks a lot.
Back to top
View user's profile Send private message
hol.sten
Super User
Super User


Joined: 14 Nov 2004
Posts: 3533
Location: Hamburg, Germany

PostPosted: Sat Apr 23, 2005 1:47 am    Post subject: Re: Generating a Spreadsheet via Python Reply with quote

fatchan wrote:
I have a bunch of statistical data that I would like to format into a spreadsheet with python. The easiest route i could think of was to use python to generate a csv file, but the problem is i want to have a more robust spreadsheet capable of representing different colored cells. Has anyone tried to use python to generate SXW files?

Don't mix up the OOo file formats: sxw if for writer documents, sxc for spreadsheets.

fatchan wrote:
I did a quick search on this matter but could not find a post with the answer i seek, any help would be appreciated,.

This seems to be the most valuable starting point for OOo and Python: http://www.oooforum.org/forum/viewtopic.phtml?t=4818

With kind regards
hol.sten
Back to top
View user's profile Send private message
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Sat Apr 23, 2005 8:57 am    Post subject: Re: Generating a Spreadsheet via Python Reply with quote

fatchan wrote:
I have a bunch of statistical data that I would like to format into a spreadsheet with python. The easiest route i could think of was to use python to generate a csv file, but the problem is i want to have a more robust spreadsheet capable of representing different colored cells. Has anyone tried to use python to generate SXW files?

I did a quick search on this matter but could not find a post with the answer i seek, any help would be appreciated,.


You've come to the right place!

I hope the following information may be helpful.

You could either write an independant Python program which generates an OOo spreadsheet, or you could write a Python program or a Python component that interacts with OOo via. its API and causes OOo to generate the spreadsheet and then save it.

If you want to write a completely independant program that does NOT require or work with OOo, then you need to directly generate the spreadsheet document yourself. OOo documents are just Zip files that contain XML. (Yes, try renaming an OOo document suffix to be ".zip" and then unzip it.) For more information about the OOo document format, see...

http://xml.openoffice.org

The remainder of this message will be about interacting with OOo's API from Python.

Uno, Components, Services and Interfaces

OOo is made up of many components.

These components are built using a technology called Universal Network Objects (UNO for short).

Each component can contain zero or more Services. A Service is roughly like an "object" in most proramming languages.

A Service can implement one or more Interfaces.

An Interface contains methods which you can call. (Interfaces begin with an X.)

So when you surf the API documentation, you might find a Service, such as a SheetCell which represents a single cell of a spreadsheet, but that service has multiple interfaces, such as XCellAddressable or XColumnRowRange; but also the SheetCell includes other Services, such as Cell or CharacterProperties. This means that everything in those included services is also part of SheetCell. That means, that all properties and interfaces of a Cell or CharacterProperties are also part of a SheetCell. Furthermore, if an included Service includes other Services, then recursively, everything that is part of those included, included services are part of SheetCell. Hope that makes sense. For more examples, see the links "How to navigate the OOo API Docs" (below)

UNO components can be written in any language, and then called from any other language. For instance, you could write a component in C++, and then create an instance of that component in, say, Java and then call methods of the compoennt. You could write a component in Python, and then instantiate that component from Basic and call its methods.

Every programming language that works with UNO requires a Bridge.

The Python-UNO bridge is described here. It is possible to create macro scripts in Python starting in OOo 2.0 beta.

Before writing components, try writing an external Python program that manipulates OOo via. its API. Which leads me to...



Example Python programs that work with Calc spreadsheets

Over in my Calc Examples thread, you can find a lot of examples of how to program the Calc spreadsheet. Mostly, these examples are in Basic. But I do have an example in Python, as well as an example in Java, plus an example in Microsoft Visual Basic and one in Microsoft Visual FoxPro.

I adapted the Python example I mentioned in the previous paragraph into a self contained program that can be run on Computer B but control an OOo spreadsheet that is on Computer A.


I have previously posted a thread called Danny's Python Modules which contains quite a few modules from my (growing) Python library. That thread also has quite a few pointers to other Python articles here on OOoForum.org.

Here are some pointers to setting up Python IDLE (the GUI development environment) on Windows XP to work with OOo's Python. And here is an even better article on setting up Python on Windows to use IDLE.




See Also...


How to navigate the OOo API Docs
================================

In Java, how to use API Docs to find available interfaces, methods.
How to call getSupportedServiceNames()
http://www.oooforum.org/forum/viewtopic.php?p=58512#58512

In this post, begin at the sentence...
"So how did I learn about how to use removeByIndex() ?"
http://www.oooforum.org/forum/viewtopic.php?p=25537#25537

http://www.oooforum.org/forum/viewtopic.php?p=29989#29989
http://www.oooforum.org/forum/viewtopic.php?p=13511#13511
http://www.oooforum.org/forum/viewtopic.php?p=15399#15399
http://www.oooforum.org/forum/viewtopic.php?p=15246#15246
http://www.oooforum.org/forum/viewtopic.php?p=11995#11995
http://www.oooforum.org/forum/viewtopic.php?t=3479
http://www.oooforum.org/forum/viewtopic.php?p=16186#16186
http://www.oooforum.org/forum/viewtopic.php?t=4608
http://www.oooforum.org/forum/viewtopic.php?p=13834#13834
http://www.oooforum.org/forum/viewtopic.php?p=15034#15034
http://www.oooforum.org/forum/viewtopic.php?t=3735
http://www.oooforum.org/forum/viewtopic.php?t=3681

XShapes, DrawPage, GenericDrawPage
http://www.oooforum.org/forum/viewtopic.php?t=5328

Developer's Guide
http://api.openoffice.org/DevelopersGuide/DevelopersGuide.html

(Developer's Guide TOC is here)
http://api.openoffice.org/docs/DevelopersGuide/DevelopersGuide.htm

and the online API reference....
http://api.openoffice.org/docs/common/ref/com/sun/star/module-ix.html

Useful links from Russ...
http://www.oooforum.org/forum/viewtopic.php?p=16374#16374





How to write components (writing components)
============================================
Seeking ideas for new Calc add-in functions
http://www.oooforum.org/forum/viewtopic.php?t=12589
The above thread has a link where to download the functions,
including source code more highly developed than the
crude example here.
http://www.oooforum.org/forum/viewtopic.php?p=49922#49922

Jar files in components
http://www.oooforum.org/forum/viewtopic.php?p=62235#62235

Development of Calc Function add ins in Java
http://www.oooforum.org/forum/viewtopic.php?t=12537
source code to early development version begins here
and continues into message which follows.
http://www.oooforum.org/forum/viewtopic.php?p=49922#49922
Problem with invocation of Java Addin method from Calc
http://www.oooforum.org/forum/viewtopic.php?t=12921

Development of Calc Function add in component in Python
http://www.oooforum.org/forum/viewtopic.php?t=8456

Python component providing XML parser
http://www.oooforum.org/forum/viewtopic.php?p=38234#38234

Python container components
http://www.oooforum.org/forum/viewtopic.php?t=9115

* Addon.xcs, custom configuration schema, accessing from code,
how to put xcs/xcu files into your component.
http://www.oooforum.org/forum/viewtopic.php?p=62655#62655
_________________
Want to make OOo Drawings like the colored flower design to the left?
Back to top
View user's profile Send private message
shunting
Newbie
Newbie


Joined: 19 May 2005
Posts: 1

PostPosted: Thu May 19, 2005 9:45 am    Post subject: Reply with quote

D:\otherwhere>python
Python 2.2.2 (#37, Oct 14 2002, 17:02:34) [MSC 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import uno
Traceback (most recent call last):
File "<stdin>", line 1, in ?
File "C:\Program Files\OpenOffice.org1.1.4\program\uno.py", line 67, in ?
_g_ctx = pyuno.getComponentContext( )
SystemError: Error during bootstrapping uno (uno::Exception):loading component library failed: file:///C:/Program%20Files/OpenOffice.org1.1.4/program/simplereg.uno.dll
>>>

Using python.bat

Python 2.2.2 (#37, Oct 14 2002, 17:02:34) [MSC 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import uno
>>>

Surely I don't have to run my python scripts from the OpenOffice directory to get pyuno to work, so what am I doing wrong?
Back to top
View user's profile Send private message
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Sat May 21, 2005 9:06 am    Post subject: Reply with quote

shunting wrote:
Surely I don't have to run my python scripts from the OpenOffice directory to get pyuno to work, so what am I doing wrong?

I think, basically, Yes you do.

Of course, this is not much of a problem if you set up the Python IDLE environemnt, the way I describe in a link I gave earlier (see message above).

Alternately, you could write a batch file, similar to what I describe to set up IDLE, and then run your own batch file from anywhere. Your batch file would set up the necessary environment for python with uno, and then run your script.

Another approach is to install a component written in Python into the office. There is a much higher barrier to learning how to do this. I plan to have some actual examples, maybe this summer. My examples will be for OOo 2.0, because it is easy to install packages in the new version by just picking Tools --> Package Manager...
_________________
Want to make OOo Drawings like the colored flower design to the left?
Back to top
View user's profile Send private message
sam_hunting
General User
General User


Joined: 22 May 2005
Posts: 5

PostPosted: Mon May 23, 2005 5:41 am    Post subject: Reply with quote

Thanks, Danny. I've written a batch file to set the environement variables.

Hopefully the new OpenOffice will improve some of these limitations....
Back to top
View user's profile Send private message
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Mon May 23, 2005 10:20 am    Post subject: Reply with quote

sam_hunting wrote:
Hopefully the new OpenOffice will improve some of these limitations....


It doesn't. Not in the direct sense you want.

This is not really the way that the Python within OOo is most useful.

The more useful techniques are:

  • Write a python component (hard to figure out, easier if someone who has done it shows you how)
  • Write a python "macro" and embed it within a document, like the way Basic macros can be distributed within documents. Someone just opens your document and clicks a button or control on the document to activate your macro. (easier than writing a component, but still not as easy as Basic, due to lack of an IDE.)


The advantage of the component approach is that, in OOo 2.0, the user just uses the command
Tools --> Package Manager....
and installs your package. Now your macro, along with any graphics, other resources, data files, dialog boxes, menus, toolbar icons, etc. are globally available within the office.
(Drawback: difficult to learn to develop, but worth it. I still don't have a full example ready to hand out.)

The advantage of the "macro" approach is that you can send the document to any any user (on any OS) and they can open it, and immediately interact with form controls on the document to begin working with your macro.

Writing an external python script and running it outside of OOo, like what you are trying to do, is most useful for....

  • Running a python script on computer A to control an OOo running on computer B. (Note that A and B don't need to run the same version of OOo, or even run on the same OS. Computer A, for example, could be OOo 1.1.4 on Linux, and computer B could be OOo 2.0 beta on Win XP, or vice versa.)

_________________
Want to make OOo Drawings like the colored flower design to the left?
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group