Creating Excel Spreadsheets In IE Using ActiveX
Starting two weeks ago, we gave a tip that talked about creating an Excel spreadsheet inside Notes using OLE automation. Last week we explored the same topic, but using a browser interface. One drawback to the browser interface was the lack of formatting options available to us. This week we'll use ActiveX to create an actual Excel spreadsheet (instead of an embedded Excel spreadsheet) that can be modified and formatted to our exact specifications.
To accomplish this, we need to use VBScript to create the Excel object. This is actually pretty straightforward:
Sub Initialize
Dim session As New NotesSession
Dim db As NotesDatabase
Dim excel As Variant
Dim worksheet As Variant
Dim cell As Variant
Dim view As NotesView
Dim nav As NotesViewNavigator
Dim entry As NotesViewEntry
Dim rowNum As Integer
Print {<HTML>}
Print {<HEAD>}
Print {<script language='VBScript'>}
Print {Set excel = CreateObject("Excel.Application")}
Print {excel.Visible = True}
Print {Set newBook = excel.Workbooks.Add}
Instead of printing out the different content type, this time we're printing out HTML. But inside the HEAD section, we're writing some VB Script. This script will create an instance of Excel, set it to visible and add an empty workbook. So, inside the browser, we're creating the instance of Excel. Your users might get a security warning based on their browser settings. The security warning is telling you that the browser is trying to launch another application and it is asking if that's all right.
Now that we have an object inside the VBScript, the rest of the code actually looks quite similar to the Notes version, except that we want to write out the statements to the browser so the browser will execute them.
' Print out the header row
Print {Set worksheet = excel.Application.Workbooks(1).Sheets(1)}
Print {Set cell = worksheet.Range("A1")}
Print {cell.FormulaR1C1 = "Conference"}
Print {Set cell = worksheet.Range("B1")}
Print {cell.FormulaR1C1 = "Home Wins"}
Print {Set cell = worksheet.Range("C1")}
Print {cell.FormulaR1C1 = "Home Losses"}
Print {Set cell = worksheet.Range("D1")}
Print {cell.FormulaR1C1 = "Home Win %"}
Print {Set cell = worksheet.Range("E1")}
Print {cell.FormulaR1C1 = "Cumulative Win %"}
Notice how we use "Print" statements? The code is the same as the Notes version, but the browser needs to execute the code. So we send the code to the browser through Print statements.
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
Print {Set cell = worksheet.Range("A} & Cstr(rowNum) & {")}
Print {cell.FormulaR1C1 = "} & entry.ColumnValues(0) & {"}
Print {Set cell = worksheet.Range("B} & Cstr(rowNum) & {")}
Print {cell.FormulaR1C1 = } & Cstr(entry.ColumnValues(1))
Print {Set cell = worksheet.Range("C} & Cstr(rowNum) & {")}
Print {cell.FormulaR1C1 = } & Cstr(entry.ColumnValues(2))
Print {Set cell = worksheet.Range("D} & Cstr(rowNum) & {")}
Print {cell.FormulaR1C1 = "=+RC[-2]/(RC[-2]+RC[-1])"}
Print {Set cell = worksheet.Range("E} & Cstr(rowNum) & {")}
Print {cell.FormulaR1C1 = "=SUM(RC[-3]:R2C[-3])/(SUM(RC[-3]:R2C[-3])+SUM(RC[-2]:R2C[-2]))"}
rowNum = rowNum + 1
Set entry = nav.GetNextCategory(entry)
Wend
This is a little more confusing. We set up the NotesViewNavigator object in the way we've done in both previous examples. And we still use Print statments to send code to the browser. But the browser doesn't know anything about rowNum because that is internal to the LotusScript. (We could put a variable into the output sent to the browser, but it isn't necessary). So the code sent to the browser needs to know exactly what cell to move to. So, for the first line, we want the code sent to the browser to be Set cell = worksheet.Range("A2"). This means that most of the code is fixed, but part is variable. So we have to be careful on what is sent to the browser.
On a similar note, we are sending values to the browser. The browser won't know anything about the variable entry because that is a NotesViewEntry object. So we have to send the values to the browser. The first column is a string, so we send that as a string by putting quotes around the value in the script sent to the browser. The second and third columns are numeric, so we need to convert them to strings (the value of a Print statement must be a string) but there are no quotes inside the code sent to the browser, so the spreadsheet object will see these values as numbers.
The formulas are the same as we used in the Notes version, and are not based on any values internal to our agent, so we can hard-code those Print statements.
' Do some formatting
Print {worksheet.Rows("1:1").RowHeight = 25.5}
Print {Set cell = worksheet.Range("A1:E1")}
Print {cell.WrapText = True}
Print {cell.Font.FontStyle = "Bold"}
Print {worksheet.Columns("A:A").ColumnWidth = 7.43}
Print {worksheet.Columns("B:B").ColumnWidth = 8}
Print {worksheet.Columns("C:C").ColumnWidth = 9.43}
Print {worksheet.Columns("D:D").ColumnWidth = 8.43}
Print {worksheet.Columns("E:E").ColumnWidth = 10.71}
Print {Set cell = worksheet.Columns("D:E")}
Print {cell.NumberFormat = "0.0%"}
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>}
End Sub
At the end, we do the formatting just as before. All the code is constant, so we can send the hard-coded Print statments to the browser. At the end, we send up a message to the user. At the very end, we need to close the VBScript tag. That VBScript was written in the web page header, so we need to close the HEAD. Then, we should have a BODY tag. Inside that we only print out a message. That tag will be generated last because all the code is in the HEAD section. But it tells the user what happened. Finally, close out the HTML tag and we're done.
All right, since this was so similar to the Notes version, next week we'll explore a way to combine the two into one agent that uses both OLE and ActiveX.