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

Transforming Excel/anySpreadsheet files to plain text

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





PostPosted: Thu Apr 08, 2004 2:45 pm    Post subject: Transforming Excel/anySpreadsheet files to plain text Reply with quote

Hi there,

I wonder if someone could answer a question or two for me? I've installed OO and the API, and am looking at some of the examples. One of the applications I'm working on could use an Excel (or Star/Open Office Spreadsheet) to plain text (Tab-delimited, but now CSV) fileconvertor.

After playing around with some of the examples, I found the DocumentConvertor class. The example given transforms a text file into an MS Word 97 document:

Code:
java DocumentConverter text_files/ "swriter: MS Word 97" "doc"


I've been trying to convert MS Excel files giving sWriter's like "Text", "Text CSV", but noe of them work. I've tried Googling for supported types, but I've been unable to find anything. Does anyone know what the correct file type should be?

Thanks very much for your help in advance. Very Happy

- Best regards,
Lee

*dives into code/javadoc for clues*
Back to top
DannyB
Moderator
Moderator


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

PostPosted: Fri Apr 09, 2004 7:32 am    Post subject: Reply with quote

If you don't have a list of the valid filter names, then see this.

http://www.oooforum.org/forum/viewtopic.php?t=3549

This program will give a much more detailed list of the filters and their properties....

http://www.oooforum.org/forum/viewtopic.php?p=15416#15416
_________________
Want to make OOo Drawings like the colored flower design to the left?
Back to top
View user's profile Send private message
Lee
Guest





PostPosted: Mon Apr 12, 2004 11:46 am    Post subject: Reply with quote

Thanks for your quick reply. I've had success changing plain text files to Word documents and vice versa, but I'm having problems working with Excel files:

Code:
[lee@bam]/home1/users/lee/code> ls excel_files/
test.xls
[lee@bam]/home1/users/lee/code> java DocumentConverter "excel_files" "swriter: Text" "txt"
[excel_files]
com.sun.star.io.IOException:
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:274)
        at com.sun.star.lib.uno.protocols.urp.Unmarshal.readThrowable(Unmarshal.java:315)
        at com.sun.star.lib.uno.protocols.urp.Unmarshal.readAny(Unmarshal.java:157)
        at com.sun.star.lib.uno.protocols.urp.Unmarshal.readObject(Unmarshal.java:404)
        at com.sun.star.lib.uno.protocols.urp.urp.readReply(urp.java:192)
        at com.sun.star.lib.uno.protocols.urp.urp.readMessage(urp.java:308)
        at com.sun.star.lib.uno.protocols.urp.urp.readMessage(urp.java:607)
        at com.sun.star.lib.uno.bridges.java_remote.java_remote_bridge$MessageDispatcher.invoke(java_remote_bridge.java:184)
        at com.sun.star.lib.uno.bridges.java_remote.java_remote_bridge$MessageDispatcher.run(java_remote_bridge.java:175)
  test.xls


I've tried a number of the text filters (especially the csv) one, but they all result in the same error. Can anyone advise me on what my problem is? Is the DocumentConverter class capable of this transformation? Are multiple filters required?

Thanks in advance Smile

- Best regards,
Lee
Back to top
DannyB
Moderator
Moderator


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

PostPosted: Mon Apr 12, 2004 12:31 pm    Post subject: Reply with quote

Quote:
java DocumentConverter "excel_files" "swriter: Text" "txt"


I went and looked at the DocumentConverter class. It takes three parameters.
1. Folder
2. Type to convert to
3. Extension to convert to

Since the folder name is "excel_files", I assume this folder has XLS documents in it.

OOo is going to open those into the Calc spreadsheet.

From the Calc spreadsheet, you must use a filter name that is allowed for exporting spreadsheets. (2nd parameter)

I ran the Basic program given above. On the listing I get, there is no filter named "swriter: Text". Even if there were, such a filter is probably not allowed from the Calc spreadsheet.

Here is a valid filter name: 'Text - txt - csv (StarCalc)'.
See this....

Convert SXC to CSV from commandline
http://www.oooforum.org/forum/viewtopic.php?t=6987
This discusses how to build the FilterOptions string.
In order to write the FilterOptions string, I referred to this part of the Developer's Guide...
http://api.openoffice.org/docs/DevelopersGuide/Spreadsheet/Spreadsheet.htm#1+2+2+3+Filter+Options


In order to use the CSV filter, you need to specify a string of filter options. The java DocumentConverter does not pass such a property to storeToUrl.

A couple of choices would be to alter the DocumentConverter class, or to use a Basic program to convert a folder of files from one type to another. The latter approach has been described here a number of times.

List of many past conversion examples
http://www.oooforum.org/forum/viewtopic.php?t=4998
many of which are "command line" examples.

The former approach would involve modifying DocumentConverter to accept an optional fourth parameter -- the filter options string. Then if that string is present, construct an additional com.sun.star.beans.PropertyValue in the array, and pass a name of "FilterOptions", and a value of whatever the fourth parameter is.
_________________
Want to make OOo Drawings like the colored flower design to the left?
Back to top
View user's profile Send private message
Lee
Guest





PostPosted: Tue Apr 13, 2004 6:35 am    Post subject: Reply with quote

Many thanks for all of your replies, Danny! My system now transforms Excel files into CSV Text. Am I correct in saying that in order to make the text files tab-delimted (instead of comma-delimted), I would have to create a Macro? From the list of filters at http://www.oooforum.org/forum/viewtopic.php?t=3549 I don't see any tab-delimited format. Right now, I'm thinking it would be simpler (at least, for me) to transform into CSV and then just do a search/replace on ", " to \t, etc. I'm a new OO user, and the language/syntax of the macros would take some time for me to learn!

I would think that this topic would have been convered on the forum at some point or another, but the search option doesn't return any results.
Back to top
DannyB
Moderator
Moderator


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

PostPosted: Tue Apr 13, 2004 8:10 am    Post subject: Reply with quote

Lee wrote:
Many thanks for all of your replies, Danny! My system now transforms Excel files into CSV Text.


Glad you got that working. It is very gratifying to hear when an actual problem gets solved.



Lee wrote:
Am I correct in saying that in order to make the text files tab-delimted (instead of comma-delimted), I would have to create a Macro?


I'm not sure?

I know that all you really need is a suitable filter options string.

In this thread
http://www.oooforum.org/forum/viewtopic.php?t=6987
someone wanted to use an "@" sign as the delimeter instead of a comma. See the answer. See the reference to the developer's guide. All of this information demonstrates that the export filter for CSV is highly confgurable. In fact, calling it comma separated values is misleading, because the filter is capable of so much more.

Okay, now I am sure. Just tried it.
Create spreadsheet.
File --> Save
Check the checkbox at the bottom of the dialog that says...

[x] Edit Filter Settings

Then save. See the next dialog box that pops up? This one allows you to set the Field delimiter, using a dropdown menu, which includes TAB as one of its choices.

So you definitely can export to tab separated values using just the User Interface without writing a macro. Similarly, I know from an experiment I did yesterday that it is also definitely possible to import tab separated values without using a macro.

In fact, I am beginning to discover that it is possible to do quite a few things in OOo without using a macro. Smile I spend too much time playing with macros.
_________________
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