| View previous topic :: View next topic |
| Author |
Message |
Lee
Guest
|
 Posted: Thu Apr 08, 2004 2:45 pm Post subject: Transforming Excel/anySpreadsheet files to plain text |
 |
|
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. 
- Best regards,
Lee
*dives into code/javadoc for clues* |
|
| Back to top |
|
 |
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA
|
|
| Back to top |
|
|
Lee
Guest
|
| Posted: Mon Apr 12, 2004 11:46 am Post subject: |
|
|
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 
- Best regards,
Lee |
|
| Back to top |
|
|
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA
|
| Posted: Mon Apr 12, 2004 12:31 pm Post subject: |
|
|
| 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 |
|
|
Lee
Guest
|
| Posted: Tue Apr 13, 2004 6:35 am Post subject: |
|
|
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
Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA
|
| Posted: Tue Apr 13, 2004 8:10 am Post subject: |
|
|
| 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. I spend too much time playing with macros.
_________________
Want to make OOo Drawings like the colored flower design to the left? |
|
| Back to top |
|
|
|
|
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
|
