CSV Export Macro

Discuss the spreadsheet application
Post Reply
sabatian
Posts: 2
Joined: Mon Feb 11, 2008 1:19 pm

CSV Export Macro

Post by sabatian »

Hi all,

I need to export a CALC sheet in cvs format preserving the cells' format. The issue here is that some cells have a the string 0x pre-pended to indicate to the tool that will process the csv file that that value is hexadecimal.

If the sheet is exported with File/save as everything wors fine, but if I use a macro (please find below my export function code) the 0x characters are removed.

Does anyone have an idea on how to solve this?

Thanks in advance.

Andrea Sabatini

Code: Select all

function Export (FileName as string, SheetName as string)

Dim oProp(2) As New com.sun.star.beans.PropertyValue
Dim sPath, sFile, sBase, sUrl, sSep As String
Dim oDoc, oSheet As Variant
Dim iLen, iFieldSeparator, iTextDelimiter As Integer

sSep = "/"

oDoc   = ThisComponent

If ( Not GlobalScope.BasicLibraries.isLibraryLoaded("Tools") ) Then
	GlobalScope.BasicLibraries.Loadlibrary("Tools")
End If
If ( oDoc.hasLocation() ) Then
	sURL  = oDoc.getURL()
	sPath = DirectoryNameoutofPath(sURL, sSep) + sSep
	sBase = FileNameoutofPath(sURL, sSep)
End If
iLen  = len(sBase)
sBase = Mid(sBase, 1, iLen - 4)
 
oProp(0).Name  = "InteractionHandler"
oProp(0).Value = ""
oProp(1).Name  = "FilterName"
oProp(1).Value = "scalc: Text - txt - csv (StarCalc)"
iFieldSeparator = asc(";")  ' ASCII value for ,
iTextDelimiter  = asc("""") ' ASCII value for "
oProp(2).Name  = "FilterOptions"
oProp(2).Value = CStr(iFieldSeparator) + "," + CStr(iTextDelimiter) + ",,,"

oSheet = oDoc.Sheets.getByName(SheetName)
oDoc.getCurrentController().setActiveSheet(oSheet)
sFile  = FileName ' sBase + "_" + CStr(SheetName) + ".csv"
oDoc.storeToURL (sPath + sFile, oProp())

end function
TerryE
Volunteer
Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

Re: CSV Export Macro

Post by TerryE »

Andreas, I've attached my version of this macro:

Code: Select all

Sub ExportCurrentSheetToCSV()

Dim oDoc, sURL, sBase, sSheetName, sFile, oPV(1) As New com.sun.star.beans.PropertyValue

oDoc = StarDesktop.CurrentComponent
On Error Goto ErrorExit
If Not oDoc.supportsService("com.sun.star.sheet.SpreadsheetDocument" ) Then Goto ErrorExit
If Not oDoc.hasLocation() Then Goto ErrorExit
sURL = oDoc.URL
If mid(sURL, Len(sURL) - 3, 1) <> "."  Then Goto ErrorExit
sBase = Left(sURL, Len(sURL) - 4)
sSheetName = oDoc.CurrentController.ActiveSheet.Name
sFile = sBase + "_" + sSheetName + ".csv"

oPV(0).Name = "FilterName" : oPV(0).Value = "Text - txt - csv (StarCalc)"
oPV(1).Name = "FilterOptions" : oPV(1).Value = Asc(",") & "," & Asc("""") & ",0,1"

oDoc.storeToURL sFile, oPV()
Exit Sub

ErrorExit:
  Print "This macro can only save a select Sheet from a saved File"
  On Error Goto 0
End Sub
which is in my Library. It exports cells with contain a string representation of a hex code such as 0x64 as "0x64" fine. Some notes on a side-by-side comparison of the two:
  • I used section 9.2.2 of the SDK "Saving Spreadsheet Documents" as my "bible". This says that the filter is Text - txt - csv (StarCalc) (note no scalc:) and that the only other property discusses is FilterOptions
  • Since it is in a library, it uses StarDesktop.CurrentComponent rather than ThisComponent and also checks to see if it has been fired from a spreadsheet.
  • It uses an exception handler to handle errors and return an informative failure message.
  • In general I do use Options Explicit but I don't use typing in OOoBasic. Type conversion is so lax that if you get the typing wrong, it doesn't aid to debugging. Also when you benchmark the RTL, you will find that Variants actually execute faster!
  • I also use the pseudo property overloading to enhance readability so where you would say

    Code: Select all

    oSheet = oDoc.Sheets.getByName(SheetName)
    oDoc.getCurrentController().setActiveSheet(oSheet)
    I would say

    Code: Select all

    oDoc.CurrentController.ActiveSheet = oDoc.Sheets.getByName(SheetName)
  • Use Sub and not Function if aren't returning a value. Void Functions cause a memory leak.
Hope that this helps :-)
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
sabatian
Posts: 2
Joined: Mon Feb 11, 2008 1:19 pm

Re: CSV Export Macro

Post by sabatian »

Hi Terry,

Thanks a lot for your code. I had to make some small changes to the sub you posted and here is the one I am currently using;

Code: Select all

Sub Export(FileName as string, SheetName as string)

  Dim sURL, sBase, oSheet, sFile, oPV(1) As New com.sun.star.beans.PropertyValue
  Dim sPath, sSep As String
  Dim oDoc as variant 
  Dim iLen as integer

  oDoc  = ThisComponent

  On Error Goto ErrorExit

  sSep = "/"

  If ( Not GlobalScope.BasicLibraries.isLibraryLoaded("Tools") ) Then
    GlobalScope.BasicLibraries.Loadlibrary("Tools")
  End If
  If ( oDoc.hasLocation() ) Then
    sURL  = oDoc.getURL()
    sPath = DirectoryNameoutofPath(sURL, sSep) + sSep
  End If
  sFile = sPath + FileName

  oSheet = oDoc.Sheets.getByName(SheetName)
  oDoc.getCurrentController().setActiveSheet(oSheet)

  oPV(0).Name = "FilterName" : oPV(0).Value = "Text - txt - csv (StarCalc)"
  oPV(1).Name = "FilterOptions" : oPV(1).Value = Asc(";") & "," & Asc("""") & ",0,1"

  oDoc.storeToURL (sFile, oPV())
  Exit Sub

  ErrorExit:
    Print "Error occurred in Export macro"
    On Error Goto 0

End Sub
This was necessary because the line:

sURL = oDoc.URL

in your code always returned an empty string and I got errors when the file was written.

Anyway, now this sub has the same interface as before and it works the way I need.

Thanks again,

Andrea
Post Reply