Lotus Notes FAQ Visit Our Sponsor!

How do you export to an OpenOffice Calc spreadsheet?

From Frank Strunzkus:

This example goes through a notes view and exports some fields of each notes document into a table row in openoffice calc.

You can choose if the user should be able or not to see the OpenOffice application while the export agent is running. Once the OO file is ready it will be saved to harddisk.

My example requires a oo calc file that will be used as a template. This template will be filled with data comming from notes. (Google for examples, if you prefer to create a empty file rather than using a preformatted one like I prefer.)

To test my demo you first need to create the template file on your C: drive. Create a simple empty .ods file. My example requires that there is a at least one table in the file that has the name "Sheet1".

Option Public
Option Declare


Class OOProperties 

'I found this class in the OpenOffice forum and found it very usefull. Thanks for sharing it!

'This clase can be used to pass properties to OO Objects
'E.g. pass the Hidden=True property to the app object to run the application in the background
'If you do not have to set properties, you do not mandatory need this class in your code

Private vProp() As Variant 
Private app As Variant 
Private vStruct As Variant 
Private bInz As Integer 

Sub new() 
  Set App = createobject("com.sun.star.ServiceManager")
  Set vStruct = App.Bridge_GetStruct("com.sun.star.beans.PropertyValue") 
End Sub 

Sub addProperty(sName As String, vValue As Variant) 
  If bInz Then 
    Redim Preserve vProp(Ubound(vProp) + 1) 
    Redim vProp(0) 
  End If 
  bInz = True 
  vStruct.Name = sName 
  vStruct.Value = vValue 
  Set vProp(Ubound(vProp)) = vStruct 
End Sub 

Property Get Values() 
  If Not bInz Then 
    Me.addProperty "Dummy!", 0 
    bInz = True 
  End If 
  Values = vProp 
End Property 

End Class


Sub Initialize
  Dim session As New NotesSession
  Dim ws As New NotesUIWorkspace
  Dim fileOo As String

  Dim args() As Variant

  Dim prop As New OOProperties 

  Dim App As Variant
  Dim objCoreReflection As Variant
  Dim objDesktop As Variant
  Dim objDocument As Variant
  Dim objSheet As Variant
  Dim cell As Variant

  Dim db As notesdatabase 
  Set db = ws.CurrentDatabase.Database

  Dim view As NotesView 'The notes view that contains the data to export
  Set view = db.GetView("allpersons")

  Dim doc As NotesDocument 'A notes document that contains data to be exported
  Set doc = view.GetFirstDocument

'The filename of a file that should be used as a template. 
'Using a template allows you to define columnheaders, set colors and add logos etc. in advance.
'You can even prepare charts that display your data from Notes graphically
'I normany use a function to export the template file from my notes database to the filesystem first.
'Remember that Openoffice uses / not \ you might have to convert your filepath. 
  fileOo = "C:/MyEmptyTemplateFile.ods" 

'Create objects that are required to work with openoffice
  Set App = createobject("com.sun.star.ServiceManager") 'The servicemanager

  Set objCoreReflection= App.createInstance("com.sun.star.reflection.CoreReflection")
  Set objDesktop = App.createInstance("com.sun.star.frame.Desktop") 'The main desktop object

  prop.addProperty "Hidden", True 'Decide to run Openoffice hidden in background or visible to the user

  Set objDocument = objDesktop.loadComponentFromURL("
file:///"+fileOo,"_blank", 0, prop.Values) 'Load the file

  Set objSheet = objDocument.Sheets.getByName("Sheet1") 'Define which Sheet to process. This sheet must exist in the template


  Dim rowposition As Integer 'current spreadsheet row
  Dim colposition As Integer 'current spreadsheet column

  rowposition = 3 'Note that the array starts at 0, to adress the first spreedsheet row 1 set rowposition = 0

  While Not doc Is Nothing 
    colposition = 0

    Print "Preparing Spreadsheet, please wait! Processing "+ doc.Lastname(0)

    Call InsertSpreedsheetCellValue (objSheet, colposition ,rowposition, doc.Firstname(0))
    Call InsertSpreedsheetCellValue (objSheet, colposition ,rowposition, doc.Lastname(0))

    rowposition = rowposition+1
    Set doc = view.GetNextDocument(doc)
  Call objDocument.Store
  Call objDocument.close( True )

  Msgbox "Your file is ready! "+fileOo

End Sub


Sub InsertSpreedsheetCellValue (objSheet As Variant, colposition As Integer, rowposition As Integer, fieldvalue As String)

  Dim cell As Variant
  Set Cell = objSheet.getCellByPosition(colposition, rowposition)
  Cell.String = FieldValue
  colposition = colposition+1

End Sub

Applies to Notes Versions: 6 6.5 7
Last Modified: November 30, 2007