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

Need to convert XLS files to text, programmatically

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
Ramon F Herrera
Guest





PostPosted: Sat Sep 20, 2003 7:03 pm    Post subject: Need to convert XLS files to text, programmatically Reply with quote

I am writing an application which receives e-mail messages from many sources
and extracts any attachments that are included in them. The application
currently handles zipped, uuencoded and several other formats, but I
need to improve the Excel-handling capabilities.

I am not familiar at all with the OpenOffice source code and would like
to see if it is possible -or how hard it is- to extract the XLS-to-text
functionality from the Calc (SC?) source.

I prefer C/C++ source.

Any advice and insight are very much appreciated.

-Ramon F. Herrera
Back to top
DannyB
Moderator
Moderator


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

PostPosted: Sun Sep 21, 2003 12:07 pm    Post subject: Reply with quote

See this entire thread....

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

It started as a question about converting Excel files to Calc. It evolved into a general discussion between me and another user about converting batches of files form one format to another.

Everything that you would need to know to do it as a Macro in Basic is in that thread if you can put the pieces together. (1) a general conversion program. (2) some substitute pieces to convert from one form to another form using an Export filter. (3) a complete list of filters. (The export filter you probably want is named "Text - txt - csv (StarCalc)")

It would be possible to rewrite the code from Basic to either Python of Java. (Should also be possible to convert to Delphi and Visual FoxPro -- two MS Automatation languages that can definitely access OOo functionality.) Might even be possible in VC to access OOo functionality.

Now if you want to copy actual code (according to the open source license it is under) or simply study the OOo C/C++ source code and learn the technique, I can't be of much help. In this case, I would be concentrating my search efforts in the source code that implements the filters.

Returning to the subject of using Uno code to access OOo functionality. You could accomplish it in Basic using a complete program like this. (Adapted from this post...
http://www.oooforum.org/forum/viewtopic.php?p=10010#10010
)

Code:

' Convert a bunch of Excel spreadsheets.

Sub Main
   ' This is the hardcoded pathname to a folder containing Excel files.
'   cFolder = "C:\Documents and Settings\dbrewer\Desktop\test"
   cFolder = "/home/danny/Desktop/00Docs-Source"

   ' Get the pathname of each file within the folder.
   cFile = Dir$( cFolder + "/*.*" )
   Do While cFile <> ""
      ' If it is not a directory...
      If cFile <> "."  And  cFile <> ".." Then
         ' If it has the right suffix...
         If LCase( Right( cFile, 4 ) ) = ".xls" Then
            ' Open the document.
            oDoc = StarDesktop.loadComponentFromURL( ConvertToUrl( cFolder + "/" + cFile ), "_blank", 0, Array() )
            
            ' Prepare new filename
            cNewName = Left( cFile, Len( cFile ) - 4 )
            
            ' Save it in OOo format.
'            oDoc.storeToURL( ConvertToUrl( cFolder + "/" + cNewName + ".sxc" ), Array() )

            ' Export it using a filter.
            oDoc.storeToURL( ConvertToUrl( cFolder + "/" + cNewName + ".csv" ),_
               Array( MakePropertyValue( "FilterName", "Text - txt - csv (StarCalc)" ) ) )

            ' Close the document.
            oDoc.dispose()
         EndIf
      EndIf
      cFile = Dir$
   Loop
End Sub


'----------
'   Create and return a new com.sun.star.beans.PropertyValue.
'
Function MakePropertyValue( Optional cName As String, Optional uValue ) As com.sun.star.beans.PropertyValue
   Dim oPropertyValue As New com.sun.star.beans.PropertyValue
   If Not IsMissing( cName ) Then
      oPropertyValue.Name = cName
   EndIf
   If Not IsMissing( uValue ) Then
      oPropertyValue.Value = uValue
   EndIf
   MakePropertyValue() = oPropertyValue
End Function


I took a working example I posted earlier, and trivially modified it to convert a batch of files from Excel into Text CSV. Again, it is definitely possible to rewrite this into at least the following languages: Java, Python, MS Visual FoxPro, MS Visual Basic, Delphi. It is probably possible to access MS Automation from VC. Of course, that is not an option if you are not writing for an MS platform. (For instance, the code above was tested only on OOo on Linux.)
_________________
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 Calc 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