API
@-Formulas
JavaScript
LotusScript
Reg Exp
Web Design
Notes Client
XPages
 
Creating Excel Spreadsheets In Notes/IE Using OLE/ActiveX
In this series we've talked about creating an Excel spreadsheet using OLE Automation in Notes and using ActiveX in Internet Explorer. (We've also talked about using IE, but not ActiveX). Here, we'll combine the OLE and ActiveX methods into one agent that does both, based on the environment.

The key to doing everything is knowing that a web agent runs on the server. So the agent's session will be on the server. When a Notes client is running an agent, the session is not on the server. So we can use this knowledge to determine what interface to use.

The first thing we want to do is change our OpenMSExcel function written for the Notes client. What we'll do now is not pass in a file name (we'll always open an empty sheet) and return either a handle to the spreadsheet object in Notes or Nothing if we're in the browser. So we need to pass in the session to this function. Our function becomes:

Function OpenMSExcel(session As NotesSession) As Variant
   ' Open up a doc in Excel. If MS Excel is running, then create a new file in there. If it's
   ' not running, then it needs to be started.
   Dim msExcel As Variant
   
   If session.IsOnServer Then ' Web user
      Print {<HTML>}
      Print {<HEAD>}
      Print {<script language='VBScript'>}
      Print {Set excel = CreateObject("Excel.Application")}
      Print {excel.Visible = True}
      Print {Set newBook = excel.Workbooks.Add}
      Set OpenMSExcel = Nothing
   Else ' Notes client user
      On Error Goto CreateNewInstance
      Set msExcel = GetObject("Excel.Application") ' Attempt to grab MS Excel if already open
Done:
      msExcel.Visible = False
      Call msExcel.Workbooks.Add
      Set OpenMSExcel = msExcel
   End If
   Exit Function ' =====================
CreateNewInstance:
   Print "Loading Microsoft Excel.... Please Wait...."
   Err = 0 ' Clear the error handler
   Set msExcel = CreateObject("Excel.Application") ' Launch MS Excel if not already open
   Print " "
   Resume Done ' Jump back up to the point where a document will be opened and returned
End Function

If we're working in a browser (the session is on the server), then print out the HEAD section and set up the VBScript variables, just like we did in the ActiveX version. If the session is not on the server, then create the Excel object using OLE Automation.

The agent is going to change slightly. First, we are going to need some global variables. I'll explain why later. So, in the (Declarations) section, add these lines:

Dim excel As Variant
Dim worksheet As Variant
Dim cell As Variant

The agent itself starts out in a similar fashion:

Sub Initialize
   Dim session As New NotesSession
   Dim db As NotesDatabase
   Dim view As NotesView
   Dim nav As NotesViewNavigator
   Dim entry As NotesViewEntry
   Dim rowNum As Integer
   
   Set excel = OpenMSExcel(session)

The differences are that the global variables have been removed, and we are calling the function to create the Excel object and returning Nothing if we're using a browser or the OLE Automation object if we're using the Notes client.

Next, we want to print out the header information. But this is different for OLE and ActiveX. In OLE, the code runs on the client. In ActiveX, the code is sent to the browser through Print statements for the browser to execute. But the code is identical. So, how do we handle this? You can have a big "if" block and duplicate the code, but I have a better way. Let's put the code into a string and send the string to the browser for ActiveX or run the code if the client. We can run the code using the Execute statement in LotusScript. Let me demonstrate:

   Dim stmt As String
   ' Print out the header row
   stmt = {Set worksheet = excel.Application.Workbooks(1).Sheets(1)}
   If excel Is Nothing Then Print stmt Else Execute(stmt)

Kinda slick, huh? But are we going to have to put that same line in for every single statement? No. What we can do is take advantage of the vertical bar string delimiter in LotusScript. So we can put several statements on consecutive lines into one string and send all the statements to the browser at once or execute them all at once. So, our code becomes this:

   ' Print out the first row
   stmt = |Set worksheet = excel.Application.Workbooks(1).Sheets(1)
Set cell = worksheet.Range("A1")
cell.FormulaR1C1 = "Conference"
Set cell = worksheet.Range("B1")
cell.FormulaR1C1 = "Home Wins"
Set cell = worksheet.Range("C1")
cell.FormulaR1C1 = "Home Losses"
Set cell = worksheet.Range("D1")
cell.FormulaR1C1 = "Home Win %"
Set cell = worksheet.Range("E1")
cell.FormulaR1C1 = "Cumulative Win %"|
   If excel Is Nothing Then Print stmt Else Execute(stmt)

So, all those statements are either sent to the browser in one shot or executed all at once. Following the ActiveX method, we can use this same technique throughout our code:

   rowNum = 2 ' Current row of data
   
   ' Get a NotesViewNavigator from our view
   Set db = session.CurrentDatabase
   Set view = db.GetView("vwByConference")
   Set nav = view.CreateViewNav
   Set entry = nav.GetFirst
   
   ' Go through all the entries in the view
   While Not entry Is Nothing
      stmt = |Set cell = worksheet.Range("A| & Cstr(rowNum) & |")
cell.FormulaR1C1 = "| & entry.ColumnValues(0) & |"
Set cell = worksheet.Range("B| & Cstr(rowNum) & |")
cell.FormulaR1C1 = | & Cstr(entry.ColumnValues(1)) & |
Set cell = worksheet.Range("C| & Cstr(rowNum) & |")
cell.FormulaR1C1 = | & Cstr(entry.ColumnValues(2)) & |
Set cell = worksheet.Range("D| & Cstr(rowNum) & |")
cell.FormulaR1C1 = "=+RC[-2]/(RC[-2]+RC[-1])"
Set cell = worksheet.Range("E| & Cstr(rowNum) & |")
cell.FormulaR1C1 = "=SUM(RC[-3]:R2C[-3])/(SUM(RC[-3]:R2C[-3])+SUM(RC[-2]:R2C[-2]))"|
      If excel Is Nothing Then Print stmt Else Execute(stmt)
      rowNum = rowNum + 1
      Set entry = nav.GetNextCategory(entry)
   Wend

Notice that we use the vertical bar delimiter in our strings and the string is never finally closed until way at the end. But we are still including the row numbers in our string, and the column values just like we did for the ActiveX method. Once all the statements are built, they are all sent to the browser at once or executed at once. Then the pointer in the view is moved and we continue through the view.

The formatting at the end of the data writing follows a similar technique:

   ' Do some formatting
   stmt = |worksheet.Rows("1:1").RowHeight = 25.5
Set cell = worksheet.Range("A1:E1")
cell.WrapText = True
cell.Font.FontStyle = "Bold"
worksheet.Columns("A:A").ColumnWidth = 7.43
worksheet.Columns("B:B").ColumnWidth = 8
worksheet.Columns("C:C").ColumnWidth = 9.43
worksheet.Columns("D:D").ColumnWidth = 8.43
worksheet.Columns("E:E").ColumnWidth = 10.71
Set cell = worksheet.Columns("D:E")
cell.NumberFormat = "0.0%"|
   If excel Is Nothing Then Print stmt Else Execute(stmt)

At the very end of our code, we prompt in different ways and we have close out the HTML for the browser:

      If excel Is Nothing Then
      Print {alert("Your Excel spreadsheet has been generated.")}
      Print {</script>}
      Print {</HEAD>}
      Print {<BODY><H1>Spreadsheet has been loaded. Check Excel for data.</H1></BODY>}
      Print {</HTML>}
   Else
      excel.Visible = True
      Msgbox "Your Excel spreadsheet has been generated.", 64, "Success"
   End If
End Sub

Well, that concludes our series on creating Excel spreadsheets. Hopefully you see how easy it is to create spreadsheets either through the Notes client, or through a web browser, or through both using the same agent.