Le 22/03/2009 à 18:59, Alain Baeckeroot a écrit : > > Hi > > I have a big oocalc, with 100 sheets, and i would like to export > each one to csv, ideally with the > name_of_the_sheet1.csv > name_of_the_sheet2.csv > > the export filter does it only for the current sheet :-( . > > I'm on linux, with ooffice 2.4 (i can upgrade to 3.0 if needed) >
After some hard googling and debian-help i managed to get this macro working, mostly copied from http://www.oooforum.org/forum/viewtopic.phtml?p=194938#194938 It would be nice if a macro of this kind was included in OOcalc, for exporting. Regards Alain REM ***** BASIC ***** ' ' READ THIS IS MANDATORY :-) ' ' This Open Office macro will ' - read all the .xls files located in the cFolder defined some line below ' - export each sheet of each file as a separate .csv file, named : filename_sheetname.csv ' ' you need to adapt this file to your needs (directory cFolder, cFieldTypes and maybe other...) ' 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 = "/Big/WORKSPACE/tmp" ' 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 = "2/2/2/2/2/2/2/9/9/9/9/9/9/9/9/9/9" ' 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 (remove .xls extension) 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 ' Replace spaces with underscores in filenames. cNewFileName = Replace(cNewFileName, " ", "_") 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 --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
