CSV Export Macro

Discuss the spreadsheet application

CSV Export Macro

Postby sabatian » Mon Feb 11, 2008 1:26 pm

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   Expand viewCollapse view
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
sabatian
 
Posts: 2
Joined: Mon Feb 11, 2008 1:19 pm

Re: CSV Export Macro

Postby TerryE » Tue Feb 12, 2008 3:03 pm

Andreas, I've attached my version of this macro:
Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
    oSheet = oDoc.Sheets.getByName(SheetName)
    oDoc.getCurrentController().setActiveSheet(oSheet)
    I would say
    Code: Select all   Expand viewCollapse view
    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.
TerryE
Volunteer
 
Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

Re: CSV Export Macro

Postby sabatian » Fri Feb 15, 2008 2:15 pm

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   Expand viewCollapse view
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
sabatian
 
Posts: 2
Joined: Mon Feb 11, 2008 1:19 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 17 guests