Coding News

A while back, I wrote a LotusScript agent to export data out of a Notes' view into Excel from within the Notes client (see the code here).

Just recently, I had the requirement to do the same thing, but from a web browser, no Notes client allowed. Some digging around the Net resulted in an interesting way of doing it. It uses what Microsoft calls a Web Query File. It is essentially a text file containing, at a minimum, the URL of the target data. In my case, the target was a hidden view with very little formatting (some columns were sorted, that's about it). I attached the web query file to a document, then created a link on a page that referenced the document with the file. When the link was clicked, Internet Explorer opened Excel and the data was transferred into Excel. The best thing was that it happened pretty quickly... the worst thing is that it seems to be limited to 1200 rows!!! D'OH!!! I tried every trick I could in the URL for displaying views in Domino (&Count=9999, etc.), but that didn't work. I tried running a web query from within Excel to my Domino view, but again, only 1200 rows were imported. I tried this using Excel 97 and Excel XP... same result. I am not sure if this is a Domino or Excel limitation. Oh well... time for another solution.

I am now back to an Agent kicked off from a link on the page using the ?OpenAgent command. This agent is different from the one I created above in that it formats the data into an HTML table for export to Excel. It runs pretty quickly on smaller amounts of data. In the example here, the Agent runs up against Month To Date style views. The views will only contain one months worth of data at any given time (and no, I am not using @Today in my view selection formulas). Here is the code...

                                             Sub Initialize
                                             %REM
                                             What this agent does...
                                             	This agent is triggerred from the ?OpenAgent URL command of each of the "MTD report" pages.
                                             	It creates a new Excel spreadsheet, filled with data from a specified Notes view, within IE by setting the
                                             	Content-Type header.  The user can then save the Excel spreadsheet and manipulate the data at a later time.
                                             Inputs...
                                             	View Name - passed in through the Query_String CGI variable, accessed by the DocumentContext
                                             %END REM
                                             	On Error Goto errhandler
                                             	Dim session As New NotesSession
                                             	Dim db As NotesDatabase
                                             	Dim view As NotesView
                                             	Dim nvn As NotesViewNavigator
                                             	Dim nve As NotesViewEntry
                                             	Dim webDoc As NotesDocument
                                             	Dim colCount As Integer
                                             	Dim i As Integer
                                             	Dim viewName As String
                                             	
                                             	Set db = session.CurrentDatabase
                                             	Set webDoc = session.DocumentContext
                                             	viewName = Strrightback(webDoc.Query_String(0), "view=")
                                             	Set view = db.GetView(viewName & "ToExcel")
                                             	Set nvn = view.CreateViewNav()
                                             	Set nve = nvn.GetFirst
                                             	
                                             	Print |Content-Type:application/vnd.ms-excel|
                                             	Print |<table border="1">|
                                             	Print |<tr>|
                                             	Forall c In view.Columns
                                             		Print|<td width="180"><b>| & c.Title & |</b></td>|
                                             	End Forall
                                             	Print |</tr>|
                                             	colCount = view.ColumnCount - 1	
                                             	Print |<tr><td colspan="| & colCount & |"> </td>|	
                                             	While Not(nve Is Nothing)
                                             		Print |<tr>|
                                             		For i = 0 To colCount
                                             			Print |<td>| & nve.ColumnValues(i) & |</td>|
                                             		Next
                                             		Print |</tr>|
                                             		Set nve = nvn.GetNext(nve)
                                             	Wend
                                             	Print |</table>|
                                             	Exit Sub
                                             errhandler:
                                             	Print "<br><font face=""Verdana"" color=""#000080"" size=""1"">An error occured in the Initialize object while
                                             searching for your record.  Please contact your local QA Administrator and give them the following information... <br>Error"
                                             & Str(Err) & ": " & Error$ & ". Error occurred at line " & Erl() & "</font>"
                                             	Exit Sub
                                             End Sub