Home | Invoicing | Resume | Feedback | Guestbook | Personal Pages | Other Web Sites | Anything from Amazon!

Domino Anti-SPAM Excel Export via Notes Excel Export via the Web Export Contacts to Outlook Import Contacts from Outlook Mail Blaster Improved Mailing List XML Name Picker File Sending Agent Launch File Attachments Fix Document Field Agent Installing R4.x on NT Make Lotus your Mailto: Client Mail Purge Agent Server Message Broadcast RFC822 Internet Email Addressing SMTP Inbound Font for R5 Tricks with the HTTPD.CNF

This is a modification to the Export to CSV program. What this code does is open an Excel session via the web, and write an HTML table to it using special Excel commands.

Just plug it in as an agent in your web site, assign a view, and give it a try. Tested with Excel 97, 2000, and XP.

This has a special section that if it detects a currency amount, it assigns it currency in the Excel cell. Same thing with dates. You can easily find out what you would need to modify the code with for more formats by saving a sample spreadsheet as HTML within Excel, then reading the source.

-------- Cut Here --------

'Generate Report|report.xls: 

Option Public
Option Explicit
%INCLUDE "lsconst.lss"
%INCLUDE "lsxbeerr.lss"

Sub Initialize
	
'This agent creates a excel file and dumps it directly to the web browser.  
'The browser interprets it as a file.
'The script runs line by line, first taking the view column headers, then
'all the data itself.  Please make sure you update the view name.
'It is currently ExportView
	
	Dim session As New NotesSession
	Dim db As NotesDatabase
	Dim v As NotesView
	Dim docX As NotesDocument
	Dim col As Integer
	Dim lineitem As String	
	Dim View As String
	Set db = session.CurrentDatabase
	
	'Sets the download to use Excel
	Print |Content-Type:application/vnd.ms-excel|	
	'Triggers the save/open prompt instead of embedding the spreadsheet in the browser.
'Set your default file name here also.
	Print |Content-Disposition:Attachment; filename="Report.xls"|
	On Error Goto errorHandler
'SET YOUR VIEWNAME HERE OR ELSE THE SCRIPT WILL BOMB!
	View="ExportView"
	Set v = db.GetView(View$)
	Call v.refresh
	col=1
	Print |<Table border>|
	lineitem=""
	Forall vColumn In v.Columns               
		If col=1 Then
			lineitem=|<th align="center"><FONT SIZE=3 COLOR="0000FF">|+vColumn.Title
		Else
			lineitem=lineitem+|<th align="center"><FONT SIZE=3 COLOR="0000FF">|+vColumn.Title
		End If
		col=col+1
	End Forall
	lineitem=lineitem
	
	Print lineitem
	Set docX=v.GetFirstDocument
	lineitem=""
	While Not docX Is Nothing
		col=1
		Forall cValue In docX.ColumnValues
			If col=1 Then
				lineitem=|<tr>|
			End If
			If cValue="" Then    'blank value still formats the cell
				lineitem=lineitem+|<td>&nbsp;</td>|						
			Elseif Isdate(cValue) Then  'date format
				lineitem=lineitem+|<TD ALIGN="right" STYLE="vnd.ms-excel.numberformat:dd-mmm-yyyy">|+cValue+|</td>|
			Elseif Isnumeric(cValue) Then
				If (v.columns(col-1).numberformat=3) Then  'currency format
					lineitem=lineitem+|<td ALIGN="right" STYLE="vnd.ms-excel.numberformat:$#,##0.00">|+cValue+|</td>|
				Else  'other number format
					lineitem=lineitem+|<td ALIGN="right">|+cValue+|</td>|
				End If
			Else    'Plain text format
				lineitem=lineitem+|<td>|+cValue+|</td>|
			End If
		col=col+1
		End Forall
		Print lineitem+|</tr>|
		Set docX=v.GetNextDocument(docX)
	Wend
	Print |</table>|
	Exit Sub
	
errorHandler:
	Print "There has been an error " & Err() & " : " & Error() & " - On Line "+Cstr (Erl) & Chr$(13)
	Exit Sub
	
End Sub