Thursday, 22 January 2015

Export to Excel

A frequently asked question from end-users is whether the data within a view can be exported to Excel. This applies to both the classic Notes applications and for XPages applications. At the moment I am working on a classic Notes application, a complaints system. In this application a view is included with all documents and associated fields which the end-users want to export to Excel. To export the data to Excel a button is included in the View with some LotusScript functionality. In the LotusScript functionality the possibility is included  that the end users can export all documents in the view or only selected documents in the view.


I use the following LotusScript:

Sub Click(Source As Button)
On Error Resume Next
Dim s As New notessession
Dim db As notesdatabase
Set db= s.currentdatabase
Dim uiw As New NotesUIWorkspace
Dim otherdoc As NotesDocument
Dim otherview As NotesView
Dim othercol As NotesDocumentCollection
Dim tempdoc As notesdocument
Dim uiv As notesuiview

 Set uiv = uiw.currentview

If Instr(s.Notesversion, "Release 4") Then
currentviewname = s.getenvironmentstring("CurrentView")
If currentviewname="" Then
Msgbox "Notes R4, code is not set up properly. Contact developer."
End
End If
Call s.setenvironmentvar("CurrentView","")
Elseif uiv.viewalias <> "" Then 'use alias if it isn't blank
currentviewname = uiv.viewalias
Else ' use name
currentviewname = uiv.viewname
End If

 Set otherview = db.GetView(currentviewname)
If otherview Is Nothing Then
Messagebox "Could not open the view. """ & currentviewname & """"
Exit Sub
End If

Set othercol = db.unprocesseddocuments
If othercol.count >1 Then 'if more than one doc selected then confirm
resp = Messagebox("Do you want to export only the " & _
"selected " & othercol.count & " documents?", 36, "Selected only?" )
Else
Messagebox "Exporting all rows. (To export only selected " & _
"rows tick those required in the left margin first.)"
End If  '6= yes

Dim object As NotesEmbeddedObject
Dim xlApp As Variant
Dim oWorkbook As Variant

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True 'set to visible, this can be moved to the end if you wish

Set oworkbook = xlApp.Workbooks 'handle to Workbook
oworkbook.Add

hcolmn=1
Forall c In otherview.Columns
xlApp.cells(1,hcolmn) = c.title
hcolmn=hcolmn+1
End Forall
row=2
If resp=6 Then 'selected documents
Dim seldoc As notesdocument
Set seldoc = othercol.GetFirstDocument
While Not seldoc Is Nothing
If resp=6 Then
Set otherdoc = otherview.getnextdocument(seldoc)
If otherdoc Is Nothing Then
Set otherdoc = otherview.getprevdocument(seldoc)
If otherdoc Is Nothing Then
Print " >1 doc should be selected"
End
Else
Set otherdoc = otherview.getnextdocument(otherdoc)
End If
Else 'got next doc
Set otherdoc = otherview.getprevdocument(otherdoc)
 End If      
End If
 For colmn = 0 To Ubound(otherview.Columns)
xlApp.cells(row,colmn+1) = otherdoc.columnvalues(colmn)
Next
row=row+1  
Set seldoc = othercol.GetNextDocument(seldoc)    
Wend
Else  ' all documents
Set otherdoc = otherview.GetFirstDocument
 While Not otherdoc Is Nothing
For colmn = 0 To Ubound(otherview.Columns)
xlApp.cells(row,colmn+1) = otherdoc.columnvalues(colmn)
Next
row=row+1
Set otherdoc = otherview.GetNextDocument(otherdoc)    
Wend
End If

xlApp.application.Rows("1:1").Select
With xlApp.application.Selection.Font
.bold = True
.ColorIndex = 48
.Name = "Arial"
.Size = 12
End With

xlApp.application.Rows("2:2").Select
xlApp.application.ActiveWindow.FreezePanes = True

xlApp.cells.select
xlApp.selection.Columns.AutoFit
xlApp.application.rows("1:1").Select
End Sub

The result is an export to Excel including column headers.
Perhaps this LotusScript may still be of some help to someone.

No comments:

Post a Comment