Creating Excel Spreadsheets In Notes/IE Using OLE/ActiveX

原创 2004年10月12日 21:30:00
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
      msExcel.Visible = False
      Call msExcel.Workbooks.Add
      Set OpenMSExcel = msExcel
   End If
   Exit Function ' =====================
   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)

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>}
      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.

Creating Excel Spreadsheets In Notes

Have you wanted to create Excel spreadsheets from within Notes but didnt know how to do it? Standard...
  • snowdot23
  • snowdot23
  • 2004年10月12日 21:31
  • 743

Creating Excel Spreadsheets In IE

Lets use the same example  You have a view, column 1 is categorized, columns 2 and 3 are totals. In ...
  • snowdot23
  • snowdot23
  • 2004年10月12日 21:29
  • 657


IX.1 ActiveX的历史和简单的介绍 说到ActiveX,则不得不提到COM(组件对象模型)。COM出现时要解决的问题是,“能否把软件做到和硬件一样”,硬件的理想情况是比如有人规定了U...
  • moonhillcity
  • moonhillcity
  • 2016年11月16日 15:05
  • 612


  • just0kk
  • just0kk
  • 2016年03月02日 21:31
  • 956


  • lp310018931
  • lp310018931
  • 2015年09月19日 18:09
  • 1112

OLE、ActiveX 、COM和普通DLL的简单理解

COM: COM组件是以WIN32动态链接库(DLL)或可执行文件(EXE)形式发布的可执行代码组成。 COM组件是遵循COM规范编写的 COM组件是一些小的二进制可执行文件 COM组件可以给应用程序...
  • Liekkas
  • Liekkas
  • 2015年04月27日 11:22
  • 1231


  • lemonbox
  • lemonbox
  • 2010年07月21日 17:07
  • 5741


ActiveX控件 使用#import方式在程序中调用,如下:try {       if(SUCCEEDED (m_pOlePrint.CreateInstance(__uuidof(JLPrint...
  • skywoodsky
  • skywoodsky
  • 2009年09月02日 10:35
  • 658


转自: 网上看到些文章讲述关于C++创建ActiveX插件的讲述,觉得比较好,特保存起来    一般的Web应用对于浏览器插件能不使用的建议尽量不使用,因为其涉及到安全问题以及影响用户安装(...
  • u012125696
  • u012125696
  • 2014年11月07日 10:27
  • 2011


ActiveX控件和它的容器 1.COM基础 2.ActiveX控件及实现 3.ActiveX控件容器及实现 4.总结 1.COM基础 COM是一种组件开发技术, 它实际上是一种...
  • zhh_steven
  • zhh_steven
  • 2010年07月16日 14:21
  • 4084
您举报文章:Creating Excel Spreadsheets In Notes/IE Using OLE/ActiveX