How to export Lotus Notes views to a Microsoft Excel database
Exporting data from Lotus Notes views into Microsoft Excel can be simple. Learn how by using a LotusScript agent, a shared action and HTML table formatting.
Exporting data from a Lotus Notes view to a Microsoft Excel database is a simple process that doesn't require object linking and embedding (OLE) and is easily customizable. To begin, create a LotusScript agent that will retrieve all data from a Lotus Notes view and export it to Microsoft Excel. Lotus Notes users can then format the Excel spreadsheet to their specifications for reporting purposes.
This process requires a single LotusScript agent and one shared action to call it. Place the shared action in the Lotus Notes view that you want to export. The trick is to write a text file using HTML table formatting and save the file with a Microsoft Excel extension (.XLS). Next, you can send email messages to Lotus Notes users that include their reports.
When a user opens the file, Microsoft Windows will open Excel. The program then detects that the file is an HTML table and imports it into Microsoft Excel using the specified HTML formatting.
Sub Initialize 'I placed all of the code in a single sub (normally I split it all out... but for this demo purpose, I just put all code in one sub) Dim doc As NotesDocument Dim uidoc As NotesUIDocument Dim tmpdir As String Dim uiview As NotesUIView Dim view As NotesView Dim dc As NotesDocumentCollection Dim ret As Integer Dim session As NotesSession Dim db As NotesDatabase Dim workspace As NotesUIWorkspace Dim tmpdatafilename As String Dim tmpdatafilenumber As Integer Dim memodoc As NotesDocument Dim rtitem As NotesRichTextItem Dim object As NotesEmbeddedObject Set session = New NotesSession Set workspace = New NotesUIWorkspace Set db = session.CurrentDatabase tmpdir = session.GetEnvironmentString ("Directory", True) tmpdatafilenumber% = Freefile() tmpdatafilename = tmpdir & "HRReport.xls" 'Open a file for output Open tmpdatafilename For Output As tmpdatafilenumber Set uiview = workspace.CurrentView Set view = uiview.View 'Print to the file some basic CSS for easy formatting later if the users want a change to the default. Print # tmpdatafilenumber%, | <HTML> <HEAD> <TITLE>Table example</TITLE> <STYLE type="text/css"> TABLE { background: #ffffff; border: solid black; empty-cells: hide } TD { border: solid black; border-left: none; border-right: none; } TD.top { border: solid black; border-left: none; border-right: none; background: #C0C0C0; font-weight: bold; font-size: 11px; text-align: center; } TD.viewname { border: solid black; border-left: none; border-right: none; border-bottom: none; background: #C0C0C0; font-weight: bold; font-size: 16px; text-align: left; } TD.salary { border: solid black; border-left: none; border-right: none; background: #00FFFF; } </STYLE> </HEAD> <BODY>| 'Heart of the code.... 'Print an HTML Table and then construct the table from the column contents of the current view. Print # tmpdatafilenumber%, |<table>| 'Include the view name in the top of the spreadsheet Print # tmpdatafilenumber%, |<tr>| Print # tmpdatafilenumber%, |<td class="viewname" colspan="| & Cstr((Ubound(view.Columns) + 1)) & |">| Print # tmpdatafilenumber%, view.Name Print # tmpdatafilenumber%, |</td>| Print # tmpdatafilenumber%, |</tr>| 'Start a row of headers (couldn't get TH CSS to work, so I created my own class called top) Print # tmpdatafilenumber%, |<tr>| Forall vc In view.Columns Print # tmpdatafilenumber%, |<td class="top">| Print # tmpdatafilenumber%, vc.title Print # tmpdatafilenumber%, |</td>| End Forall Print # tmpdatafilenumber%, |</tr>| 'Now loop though all of the documents in the view and create a HTML for each one. 'For each column, put in the cell tag. Set doc = view.GetFirstDocument Do While Not doc Is Nothing Print # tmpdatafilenumber%, |<tr>| Forall c In doc.ColumnValues Print # tmpdatafilenumber%, |<td>| If Isarray(c) Then 'Make sure you work with a multivalued field. For a = 0 To Ubound(c) If a = 0 Then Print # tmpdatafilenumber%, c(a) Else Print # tmpdatafilenumber%, "<br>" & c(a) End If Next Print # tmpdatafilenumber%, |</td>| Else Print # tmpdatafilenumber%, c End If Print # tmpdatafilenumber%, |</td>| End Forall Print # tmpdatafilenumber%, |</tr>| Set doc = view.GetNextDocument(doc) Loop 'Now that you have all of your data, end the table and html tags Print # tmpdatafilenumber%, |</table>| Print # tmpdatafilenumber%, |</body>| Print # tmpdatafilenumber%, |</html>| Close tmpdatafilenumber% 'Now you can either prompt them for where the report is on their hard drive, or you can email it to them 'If like email.... Set memodoc = db.CreateDocument memodoc.Form = "Memo" memodoc.Subject = "Exported View Report" Set rtitem = New NotesRichTextItem (memodoc, "Body") Call rtitem.AppendText(memodoc.Subject(0)) Call rtitem.AppendText(Chr$(10) & Chr$(10)) Call rtitem.AppendText("Open the attached file in Excel to view the report.") Call rtitem.AppendText(Chr$(10) & Chr$(10)) Set object = rtitem.EmbedObject _ ( EMBED_ATTACHMENT, "", tmpdatafilename) memodoc.SendTo = session.UserName Call memodoc.Send(False) Kill tmpdatafilename ' Delete the file from the users hard drive if you email the report. Messagebox "Please check your inbox for the report", 0 + 64, "Report Complete" End Sub
Do you have comments on this tip? Let us know.
This tip was submitted to the SearchDomino.com tip library by member WIlliam Robinson. Please let others know how useful it is via the rating scale below. Do you have a useful Lotus Notes, Domino, Workplace or WebSphere tip or code snippet to share? Submit it to our monthly tip contest and you could win a prize.