Creating Excel Spreadsheets In Notes/IE Using OLE/ActiveX

王朝other·作者佚名  2006-01-09
窄屏简体版  字體: |||超大  

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.

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航