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] 

Save all the sheets of a Spreadsheet as CSV - solved

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


Joined: 10 Dec 2006
Posts: 1
PostPosted: Sun Dec 10, 2006 11:33 am    Post subject: Save all the sheets of a Spreadsheet as CSV - solved Reply with quote
Code to save all files of a directory as CSV.
It create a CSV file for each sheet, with a appropriate prefix or suffix.
Based on posts of DannyB, dfrench, and others, who did the hard job.
HTH

Code:
Sub Main
   ' This is the hardcoded pathname to a folder containing Excel files.
   cFolder = "/Users/Shared/Jobs"

   ' 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() )
            '=========
            ' Options for delimiters in CVS
            cFieldDelimiters = Chr(9)
            cTextDelimiter = ""
            cFieldTypes = ""
            ' options....
            '   cFieldDelimiters = ",;" ' for either commas or semicolons
            '   cFieldDelimiters = Chr(9) ' for tab
            '   cTextDelimiter = Chr(34) ' for double quote
            '   cTextDelimiter = Chr(39) ' for single quote
            ' Suppose you want your first field to be numeric, then two text fields, and then a date field....
            '   cFieldTypes = "1/2/2/3"
            ' Use 1=Num, 2=Text, 3=MM/DD/YY, 4=DD/MM/YY, 5=YY/MM/DD, 9=ignore field (do not import)
            '----------
            ' Build up the Filter Options string
            ' From the Developer's Guide
            '   http://api.openoffice.org/docs/DevelopersGuide/DevelopersGuide.htm
            ' See section 8.2.2 under Filter Options
            '   http://api.openoffice.org/docs/DevelopersGuide/Spreadsheet/Spreadsheet.htm#1+2+2+3+Filter+Options
            cFieldDelims = ""
            For i = 1 To Len( cFieldDelimiters )
               c = Mid( cFieldDelimiters, i, 1 )
               If Len( cFieldDelims ) > 0 Then
                  cFieldDelims = cFieldDelims + "/"
               EndIf
               cFieldDelims = cFieldDelims + CStr(Asc( c ))
            Next
   
            If Len( cTextDelimiter ) > 0 Then
               cTextDelim = CStr(Asc( cTextDelimiter ))
            Else
               cTextDelim = "0"
            EndIf

            cFilterOptions = cFieldDelims + "," + cTextDelim + ",0,1," + cFieldTypes

            '=========
            ' Prepare new filename
            cNewName = Left( cFile, Len( cFile ) - 4 )
            
            ' Save it in OOo format.
            'oDoc.storeToURL( ConvertToUrl( cFolder + "/" + cNewName + ".sxc" ), Array() )
            
            ' Loop and selects sheets to save as csv
            oSheets = oDoc.Sheets()
            aSheetNames = oSheets.getElementNames()
               For index=0 to oSheets.getCount() -1
                  oSheet = oSheets.getByIndex(index)
                  
                  ' Define prefix or suffix to append to filename
                  appendName = aSheetNames(index) 'define prefix/suffix as the name of the sheet
                  appendNum = index + 1 ' define prefix/suffix as the number of the sheet                  
                  ' Choose new filename, with prefix or suffix
                  'cNewFileName = appendName + "_" + cNewName 'prefix name
                  'cNewFileName = appendNum + "_" + cNewName ' prefix number
                  'cNewFileName = cNewName + "_" + appendName ' suffix name
                  cNewFileName = cNewName +  "_" + appendNum ' suffix number
                  
               oController = oDoc.GetCurrentController()  'view controller
               oController.SetActiveSheet(oSheet) 'switches view to sheet object
               
               ' Export it using a filter.
               oDoc.StoreToURL( ConvertToUrl( cFolder + "/" + cNewFileName + ".csv" ),_
                  Array( MakePropertyValue( "FilterName", "Text - txt - csv (StarCalc)" ),_
                  MakePropertyValue( "FilterOptions", cFilterOptions ),_
                  MakePropertyValue( "SelectionOnly", true ) ) )
               Next index
            ' Close the document.
            oDoc.dispose()
         EndIf
      EndIf
      cFile = Dir$
   Loop
End Sub


Last edited by nfuria on Tue May 26, 2009 1:10 pm; edited 1 time in total
Back to top
View user's profile Send private message
PBiela
Power User
Power User


Joined: 10 Feb 2004
Posts: 56
Location: Frankfurt/Main Germany
PostPosted: Mon Dec 11, 2006 2:11 am    Post subject: Reply with quote
Nice one, thx for a bit of clean usefull code. Although it would fit better in the Code & Snipplets part of this forum. Wink

btw

Code:
     oController = oDoc.GetCurrentController()  'view controller
               oController.SetActiveSheet(oSheet) 'switches view to sheet object


is a clean way to set an Sheet active. Thank you for reminding me about it.
I used

Code:
    oDoc.oSheet.getCellByPosition(0,0)


which is a bit dirty, but does the same thing.

Is there a third way to activate an Sheet without the ViewController?
_________________
Question Who is John Galt? - Ayn Rand Question
Back to top
View user's profile Send private message
alain_bkt
Newbie
Newbie


Joined: 23 Mar 2009
Posts: 1
PostPosted: Mon Mar 23, 2009 2:40 pm    Post subject: Reply with quote
Thanks a lot for this very useful piece of code Smile
As suggested by users@openoffice.org moderator, i posted your (very slightly modified) code , as an extension.
http://extensions.services.openoffice.org/project/OOcalc_multi_sheets_export

Cheers.
Alain.
Back to top
View user's profile Send private message
tanne24
Newbie
Newbie


Joined: 24 Mar 2009
Posts: 2
PostPosted: Tue Mar 24, 2009 6:38 am    Post subject: Reply with quote
hi,

many thanks to you all working on this, it is sure a great help for me.

But one thing I need to know is: how can I set the charset of created csv file to UTF-8?
Currently the export creates ANSI csv file, so kyrillic characters are not shown.

Greetings
Bjoern
Back to top
View user's profile Send private message
tanne24
Newbie
Newbie


Joined: 24 Mar 2009
Posts: 2
PostPosted: Tue Mar 24, 2009 7:04 am    Post subject: Reply with quote
Hi again,

was too fast with my question, found myself now.

For all the solution:

Just change
Code:

            cFilterOptions = cFieldDelims + "," + cTextDelim + ",0,1," + cFieldTypes

to
Code:

            cFilterOptions = cFieldDelims + "," + cTextDelim + ",76,1," + cFieldTypes


Bjoern
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