Hope this formats correctly. If not, its going to be on my web page at
http://www.bluestream.org.
I had an agent that I used to create a .csv file directly off of the web
browser. I've converted it here. This is completely generic, and will dump any
view listed without requiring conversion. I've added code here to properly
format numbers and dates, which you can modify to your tastes. Just make sure
you change the view name as necessary.
What I normally do is create a hidden data dump view with all the raw data.
That way when people ask for reports (like most people know what it means
anyways,) I give them the benefits of the data dump, so they can create their
own custom reports on the fly without waiting for a developer! This usually
either pacifies them or confuses them enough to let me get back to my game...
'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
Print |Content-Disposition:Attachment; filename="Report.xls"|
On Error Goto errorHandler
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
Elseif col=1 Then
lineitem=|<th align="center"><FONT SIZE=3 COLOR="000000">|+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> </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
Hope this formats correctly. If not, its going to be on my web page at http://www.bluestream.org.
I had an agent that I used to create a .csv file directly off of the web browser. I've converted it here. This is completely generic, and will dump any view listed without requiring conversion. I've added code here to properly format numbers and dates, which you can modify to your tastes. Just make sure you change the view name as necessary.
What I normally do is create a hidden data dump view with all the raw data. That way when people ask for reports (like most people know what it means anyways,) I give them the benefits of the data dump, so they can create their own custom reports on the fly without waiting for a developer! This usually either pacifies them or confuses them enough to let me get back to my game...
'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 Print |Content-Disposition:Attachment; filename="Report.xls"| On Error Goto errorHandler
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 Elseif col=1 Then lineitem=|<th align="center"><FONT SIZE=3 COLOR="000000">|+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> </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