Creating Excel Spreadsheets In IE

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

Let's use the same example You have a view, column 1 is categorized, columns 2 and 3 are totals. In the spreadsheet, columns D and E will be computed.

To make things easy, let's do this in an agent. So the URL will be ....agentName?OpenAgent. The simplest way to create a spreadsheet is to send an HTML table directly to Excel in your agent. This is done by setting the content type in your agent. The content type will send the information directly to Excel. Here's an example:

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 {Content-Type:application/vnd.ms-excel}

Print {<HTML><HEAD>}

Print {</HEAD>}

Print {<BODY>}

Print {<TABLE>}

Print {<TR>}

Print {<TD WIDTH="55"><B>Conference</B></TD>}

Print {<TD WIDTH="60"><B>Home Wins</B></TD>}

Print {<TD WIDTH="70"><B>Home Losses</B></TD>}

Print {<TD WIDTH="60"><B>Home Win %</B></TD>}

Print {<TD WIDTH="80"><B>Cumulative Win %</B></TD>}

Print {</TR>}

The content type of application/vnd.ms-excel tells the browser to use Excel. Then we simply build a table. The first row, we set the column widths and put the headers in bold. (Last week we set the column widths and made the headers bold after everything else was done). The same variables will be needed this time.

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

This is the same as last week - setting up the view navigator.

' Go through all the entries in the view

While Not entry Is Nothing

Print {<TR>}

Print {<TD>} & entry.ColumnValues(0) & {</TD>}

Print {<TD>} & Cstr(entry.ColumnValues(1)) & {</TD>}

Print {<TD>} & Cstr(entry.ColumnValues(2)) & {</TD>}

Print {<TD>=B} & Cstr(rowNum) & {/(B} & Cstr(rowNum) & {+C} & Cstr(rowNum) & {)</TD>}

Print {<TD>=Sum(B2..B} & Cstr(rowNum) & {)/(Sum(B2..B} & Cstr(rowNum) & {)+Sum(C2..C} _

& Cstr(rowNum) & {))</TD>}

Print {</TR>}

rowNum = rowNum + 1

Set entry = nav.GetNextCategory(entry)

Wend

This is a little different than last week. This time, we are printing out HTML that will end up being the cell values. Columns A, B, and C are straightforward. For columns D and E, we want to create a formula. That's why we start out the cell data with the equals sign. Then it's the formula, in the same way you would type it in to Excel. So, for row #4, the value in column D will be =B4/(B4+C4). Again, for the "cumulative" row, the sum is computed from row 2 through the current row.

Print "</TABLE></BODY></HTML>"

End Sub

Since all the formatting was done in the first row of the table, nothing more is needed here.

If you actually run this agent, you'll notice that the number formatting for columns D and E are "general". In the OLE automation version from last week, we formatted the data to be percentage with one decimal point. I do not belive there is any way to accomplish this same formatting using the <TABLE> technique. Next week I'll talk about using ActiveX on the browser to create a spreadsheet that you can format.

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