Tuesday, January 4, 2011

Export view contents to excel using LotusScript on web

The following stuff was obtained from "http://www.botstation.com/code/view2excelweb.php". This proved to be very useful to me... Saved a lot of time ofcourse. A Nice Tool

This LotusScript agent exports all documents from a Domino view to Excel. It can be triggered from web browser so the user can immediately see the results in his Excel application.
User can specify what view he wants to export by appending a view name as an URL parameter to the agent.
Set agent to be run as "Web User" in order to prevent accessing reader-protected documents unavailable to regular users.

/***************************************************/
Sub Initialize
'This agent export any view to excel using web browser interface.


'To select a default view to export, change the view name in the code below.
'You can also provide view name as parameter to the agent. As the agent runs
'with authority of it's signer make sure the agent can not access the views which
'are not supposed to be exported. Use this function at own risk or remove it, so 
'the agent always uses the default view. 
'Example of appending a view name parameter to the agent:
'http://intranet.company.com/applications/mydb.nsf/ExportViewAgent?open&expview=ExportAllPeople


'Warning: to avoid errors on columns containing multivalue fields, use following
'column formula to combine multivalues: @Implode(MVField;",")
'Columns containing icons are not exported.
'Columns used for counting totals are not exported. 


'Copyright Botstation (www.botstation.com)
Dim session As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView 
Dim doc As NotesDocument
Dim colcounter As Integer, currentcol As Integer
Dim expview As String, filename As String 
Dim colnames
Dim colnumbers 


Redim colnumbers (0 To 100) As Integer
Set db=session.currentdatabase
Set doc=session.DocumentContext


filename="Excel "+db.Title+" "+Format(Now, "yyyy-mmm-d hh-mm")+".xls" 'file name which will be suggested to user to save the resulting Excel file 
tmp=""


If Instr(doc.query_string_decoded(0),"expview=")=0 Then ' find out if view is supplied as URL parameter to this agent
Set view = db.GetView("ExportToExcel") ' no view supplied as parameter, use your default export view
Else
expview=Strright(doc.query_string_decoded(0),"expview=")
If Instr(expview,"&")>0 Then expview=Strleft(expview,"&")
Set view = db.GetView(expview) ' get the view passed as parameter to the agent
End If
If view Is Nothing Then
Print "Export Error - Export view does not exist" 
Exit Sub
End If
colcounter=0
currentcol=0 'starting column number. 0=first column
Forall c In view.Columns 
If c.isIcon<>True Then ' do not include icon columns
If c.Formula<>"""1""" And c.Formula<>"1" Then 'do not include columns which are counting docs (Total)
colnumbers(colcounter)=currentcol 'put into array what columns are valid for exporting
tmp=tmp+"<th>"+c.Title+"</th>" 
colcounter=colcounter+1 'total nr of valid columns
End If
End If
currentcol=currentcol+1 'increment counter of columns with 1
End Forall


If colcounter=0 Then 
Print "Export Error - No valid columns to export"
Exit Sub
End If
Redim Preserve colnumbers (0 To colcounter-1) As Integer 'remove unused array values
' ---- output browser header ------
Print |Content-Type:application/vnd.ms-excel| 'You can also try with "application/octet-stream"
Print |Content-Disposition: Attachment; filename="|+filename+|"| 'Triggers the save/open browser prompt instead of embedding the spreadsheet in the browser
Print ""
' --------------------------------
Print "<table border=1>" 'set border to 1 pixel, otherwise Excel shows no cell separators.
Print tmp 'output view column headers
Set doc = view.GetFirstDocument
While Not ( doc Is Nothing ) 'process all documents in view
tmp=""
tmp=tmp+"<tr>" 'begin a new table row
Forall colnr In colnumbers 'process all previously validated columns
colvalue= doc.ColumnValues(colnr)
tmp=tmp+"<td>"+CStr(colvalue)+" </td>" 'make a new table cell
End Forall
tmp=tmp+"</tr>" 'close row
Print tmp 'output values to browser after each row/document
Set doc = view.GetNextDocument (doc)
Wend 
Print "</table>"


End Sub 
/***************************************************/

Hope this helps :)

No comments:

Post a Comment