Re: Open office calc, exporter toutes les feuilles en csv
Le lundi 23 mars 2009 à 02:39 +0100, Alain Baeckeroot a écrit :
> Ca me saoule, j'aurai du le faire à la main, j'aurai fini depuis
> longtemps :-)
>
> Alain.
Bonjour,
Non il ne faut pas s'emporter, voici la solution ;-)
L'avantage c'est que tu vas maintenant gagner du temps et cela m'a
permis d'avoir une Macro OOo pour exporter en CSV (et qui sait d'autres
seront contents de l'avoir).
@+
Christophe
P.S : j'ai modifié les délimiteurs de champs et de texte, mais
l'original est commenté au-dessus à chaque fois.
----------------------------------------------------------------------
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
Sub Export_CSV
' This is the hardcoded pathname to a folder containing Excel files.
cFolder = "/home/cboissin/Bureau"
' 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)
cFieldDelimiters = ";"
'cTextDelimiter = ""
cTextDelimiter = Chr(34)
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
Reply to: