LibreOffice / OpenOffice Calc：VBscript，将XLS表格导出为CSV

1 回答

• 6

  好的，经过几天的研究后我找到了解决方案，各处都散布着微小的信息 . 我希望这段代码可以很好地为某人服务：

  ' Variables
  Dim wshshell      ' Scripting object
  Dim oFSO         ' Filesystem object
  Dim runcommand   ' Runcommand, if not run in Cscript
  
  Dim oSM      ' OpenOffice / LibreOffice Service Manager
  Dim oDesk      ' OpenOffice / LibreOffice Desktop
  Dim oCRef      ' OpenOffice / LibreOffice Core Reflections
  
  Dim sFileName   ' Filename without extension
  Dim sLoadUrl   ' Url for file loading
  Dim sSaveUrl   ' Url for file writing
  Dim args0(0)   ' Load arguments
  
  ' Create file system object
  Set wshshell = CreateObject("Wscript.Shell")
  Set oFSO = CreateObject("Scripting.FileSystemObject")
  
  ' If not run in cscript, run in cscript
  if instr(1, wscript.fullname, "cscript.exe")=0 then
     runcommand = "cscript //Nologo xyz.vbs"
     wshshell.run runcommand, 1, true
     wscript.quit
  end if
  
  ' If there are files, start Calc
  If oFSO.GetFolder(".").Files.Count>0 then
     ' If no LibreOffice open -> run
        Set oSM = WScript.CreateObject("com.sun.star.ServiceManager")
     ' Create desktop
        Set oDesk = oSM.createInstance("com.sun.star.frame.Desktop")
        Set oCRef = oSM.createInstance( "com.sun.star.reflection.CoreReflection" )
  else
     ' If no files in directory
        wscript.quit
  End If
  
  ' Error handling
  on error resume next
  
  ' CSV settings for saving of file(s)
  sFilterName = "Text - txt - csv (StarCalc)"
  sFilterOptions = "9,0,76,1,,0,false,true,true"
  sOverwrite = True
  
  ' load component for file access
  oDesk = GlobalScope.BasicLibraries.loadLIbrary( "Tools" )
  
  ' load argument "hidden"
  Set args0(0) = oSM.Bridge_GetStruct("com.sun.star.beans.PropertyValue") 
  Set args0(0) = MakePropertyValue("Hidden", True)
  
  For each oFile in oFSO.GetFolder(".").Files
     if lcase(right(oFile.Name,3))="xls" then
        ' open file
           sLoadUrl = ConvertToURL(oFile.Path)
           Set oDoc = oDesk.loadComponentFromURL(sLoadUrl, "_blank", 0, args0 )
        ' read filename without extension or path
           sFileName = oFSO.GetBaseName( oFile.Path )
        ' save sheets in CSVs
           For i = 0 to oDoc.Sheets.getcount -1
              oActSheet = oDoc.CurrentController.setActiveSheet( oDoc.Sheets.getByIndex(i) )
              sSaveUrl = ConvertToURL( oFile.ParentFolder & "\" & sFileName & "_" & oDoc.sheets.getByIndex(i).Name & ".csv" )
              saveCSV oSM, oDoc, sSaveUrl, sFilterName, sFilterOptions, sOverwrite
           Next
        ' Close document
        oDoc.close(True)
        Set oDoc = Nothing
        Set oActSheet = Nothing
        sFileName = ""
        sLoadUrl = ""
        sSaveUrl = ""
     Else
     End If
  Next
  
  ' Close LibreOffice
  oDesk.terminate
  Set oDesk = nothing
  Set oSM = nothing
  
  
  Function ConvertToURL(sFileName)
  ' Convert Windows pathnames to url
  
  Dim sTmpFile
  
  If Left(sFileName, 7) = "file://" Then
     ConvertToURL = sFileName
     Exit Function
  End If
  
  ConvertToURL = "file:///"
  sTmpFile = oFSO.GetAbsolutePathName(sFileName)
  
  ' replace any "\" by "/"
     sTmpFile = Replace(sTmpFile,"\","/") 
  
  ' replace any "%" by "%25"
     sTmpFile = Replace(sTmpFile,"%","%25") 
  
  ' replace any " " by "%20"
     sTmpFile = Replace(sTmpFile," ","%20")
  
  ConvertToURL = ConvertToURL & sTmpFile
  End Function
  
  
  Function saveCSV( oSM, oDoc, sSaveUrl, sFilterName, sFilterOptions, sOverwrite )
  ' Saves the open document resp. active sheet in a single file
  
  Dim aProps( 2 ), oProp0, oProp1, oProp2, vRet
  
  ' Set filter name and write into property array
     Set oProp0      = oSM.Bridge_GetStruct( "com.sun.star.beans.PropertyValue" )
     oProp0.Name     = "FilterName"
     oProp0.Value    = sFilterName
     Set aProps( 0 ) = oProp0
  
  ' Set filter options and write into property array
     Set oProp1      = oSM.Bridge_GetStruct( "com.sun.star.beans.PropertyValue" )
     oProp1.Name     = "FilterOptions"
     oProp1.Value    = sFilterOptions
     Set aProps( 1 ) = oProp1
  
  ' Set file overwrite and write into property array
     Set oProp2      = oSM.Bridge_GetStruct( "com.sun.star.beans.PropertyValue" )
     oProp2.Name     = "Overwrite"
     oProp2.Value    = sOverwrite
     Set aProps( 2 ) = oProp2
  
  ' Save
     vRet            = oDoc.storeToURL( sSaveUrl, aProps )
  
  End Function
  

  我希望至少我的这一小小贡献可以帮助他人 .

  回复于 2024-04-19T17:35:16+08:00