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.

Related resources from SearchDomino.com:
Tip: How to import data from Microsoft Excel to a Lotus Notes form with LotusScript

Tip: Secure Microsoft Excel spreadsheets with LotusScript 

Tip: How to convert Microsoft Word or Microsoft Excel documents to HTML

LotusScript Reference Center

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.
 

This was first published in April 2008

Dig deeper on LotusScript

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchWinIT

Search400

  • iSeries tutorials

    Search400.com's tutorials provide in-depth information on the iSeries. Our iSeries tutorials address areas you need to know about...

  • V6R1 upgrade planning checklist

    When upgrading to V6R1, make sure your software will be supported, your programs will function and the correct PTFs have been ...

  • Connecting multiple iSeries systems through DDM

    Working with databases over multiple iSeries systems can be simple when remotely connecting logical partitions with distributed ...

SearchEnterpriseLinux

SearchVirtualDataCentre.co.UK

Close