Posted By

softmechanics on 01/29/10


Tagged

basic linux cli csv excel export xls openoffice xls2csv


Versions (?)

Export a Single Sheet of an Excel Spreadsheet as CSV from the Command Line with Open Office


 / Published in: Other
 

modified from branflake2267's post at oooforum.org: http://www.oooforum.org/forum/viewtopic.phtml?t=52942

usage: Paste into the Standard.Module1 macro file. Then, create xls2csv bash script containing:

#!/bin/bash
file="$(readlink -f "$1")"
/usr/lib/openoffice/program/soffice -invisible -headless -norestore "macro:///Standard.Module1.ConvertXls2Csv(\"$file\", \"$2\")"

The first argument is the xls file name, second is the name of the sheet:

xls2csv ./spreadsheet.xls "Some Sheet"

  1. Sub ConvertXls2Csv( cFile, cSheet )
  2. cURL = ConvertToURL( cFile )
  3.  
  4. 'debug
  5. 'Print "|" + cURL + "|"
  6.  
  7.  
  8. ' Open the document.
  9. ' Just blindly assume that the document is of a type that OOo will correctly recognize and open -- without specifying an import filter.
  10. oDoc = StarDesktop.loadComponentFromURL( cURL, "_blank", 0, Array(MakePropertyValue("Hidden", True, ) )
  11.  
  12. ' Select the desired sheet
  13. sheets = oDoc.Sheets
  14. sheets.moveByName(cSheet, 0)
  15.  
  16. 'change the file ext to 'csv' or whatever you want
  17. cFile = Left( cFile, Len( cFile ) - 4 ) + "-" + cSheet + ".csv"
  18. cURL = ConvertToURL( cFile )
  19.  
  20. ' Save the document using a filter.
  21. oDoc.storeToURL( cURL, Array(MakePropertyValue( "FilterName", "Text - txt - csv (StarCalc)" ),)
  22.  
  23. 'close the document
  24. oDoc.close( True )
  25. End Sub
  26.  
  27. 'used to setup the propertyies array
  28. Function MakePropertyValue( Optional cName As String, Optional uValue ) As com.sun.star.beans.PropertyValue
  29. Dim oPropertyValue As New com.sun.star.beans.PropertyValue
  30. If Not IsMissing( cName ) Then
  31. oPropertyValue.Name = cName
  32. EndIf
  33. If Not IsMissing( uValue ) Then
  34. oPropertyValue.Value = uValue
  35. EndIf
  36. MakePropertyValue() = oPropertyValue
  37. End Function

Report this snippet  

You need to login to post a comment.