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.