Keeping the boss happy
Reporting. That one word is enough to put the fear in me. If I had a pound for every time I'd told somebody that "Notes isn't too hot when it comes to reporting" then I'd probably be out with my mates tonight instead of sitting at home writing this....
Did anybody ever use Lotus Notes Reporter 2. That one piece of software caused me more nightmares than the time I saw V, late at night, alone and young when a lady gave birth to a lizard-tongued baby.
Luckily it's not very often I get asked to create reports. If ever I do then I usually opt for the foolproof method of printing all the data back to the browser in the form of a table. The problem being that your code needs to account for every whim of the manager and all the formats they "need" to see the data in. Can get very messy, very quickly.
The last time I got asked to do some reporting it made me think of something Chris King said. That then led me to do some investigating. What I found was very interesting indeed. Writing the reporting side of an application is almost like having fun now.
What I did:
First thing was to test the theory. To do this I created a simple agent with code similar to the following and ran it from the browser.
Print {Content-Type:application/vnd.ms-excel}
Print {Hello Excel}
Sure enough, this opened the Excel ActiveX object within the browser and displayed the data. At this point I realised this was going to change the way I develop Web Applications forever.
Next thing I tried was to send the data in the form of an HTML formatted table.
Print |Content-Type:application/vnd.ms-excel|
Print |<table border="1">
<tr><td width="80">Quarter</td><td>Sales</td></tr>
<tr><td>1</td><td>£10,000</td></tr>
<tr><td>2</td><td>£12,000</td></tr>
<tr><td>3</td><td>£18,000</td></tr>
<tr><td>4</td><td>£30,000</td></tr>
</table>|
Which produced:
Excited yet? I was. Just imagine what you can do now. All you need do is send the data to the browser and then the user can do all the work, formatting the data as they like. They can save it as an .XLS file to their hard-drive. They can also use things like AutoFilter to better view the data. Anything they want really.
You can also do this with Word and Powerpoint. The oppurtunities being almost endless.
Producing the spreadsheet:
Assuming the data you are sending to the browser is held in Notes documents that are held in views you can use code similar to the following to produce dynamic tables.
Dim db As NotesDatabase
Dim qs As NotesView
Dim nv As NotesViewNavigator
Dim ne As NotesViewEntry
Set qs = db.GetView("QuarterlySales")
Set nv = qs.CreateViewNav
Set ne = nv.GetFirst
Print |Content-Type:application/vnd.ms-excel|
Print |<table border="1">
<tr><td width="80">Quarter</td><td>Sales</td></tr>|
While Not(ne Is Nothing)
Print|<tr>
<td>| + ne.ColumnValues(1) + |</td>
<td >| + ne.ColumnValues(2) + |</td>
</tr>|
Set ne = nv.GetNext( ne )
Wend
Now the data in the spreadsheet is truly dynamic. How impressed the boss will be.
Taking it further:
The second of the above two examples is still quite limiting. What we are going to need to do to make the boss really happy is make it look pretty and add extra features.
To find out how one would go about this I first took a basic example and added some formatting and a sum formula.
With this done, I saved the spreadsheet using the "Save as Web Page" option in Excel. Here is the result. Look at the source to this page and you start to get some ideas about how to format cells as number/currency and add formulas to cells. To add the "total" row in the above code we need the following line:
Print |<tr><td>Total</td>
<td x:fmla="=SUM(B2:B5)"><b></b></td>
</tr>|
In applications where you don't know that there are 4 rows you will obviously need to add the logic to work out the number of rows and alter the above formula. You also need the lines at the top of the file that define the x namespace.
Something to note:
One problem I came across was IE getting confused about what it was presenting. To get round this probem I found it was best to end all Agent names with ".xls" so a typical URL would end in /db.nsf/Table.xls?OpenAgent. This fools IE in to believing it really is an Excel file.
Have fun....
Not working
I have a problem : the browser doesn't start the activeX object. He returns (correct) simple HTML code in de browser.
Any idea
Reply
Re: Not working
I have to admint that I only tested this on my PC with IE6 and Office 2000. Which version of Excel have you got installed?
Did you name the agent "somthing.XLS"?
Have you tried opening a real Excel file in the browser to see if that opens in ActiveX or prompts for a download?
This is probably going to be a problem with the config of your machine. So far I've had no complaint from people whose projects I've used this on...
Reply
Web Components
Isn't it Web Components that you use? In that case, 2000 is required.
The View wrote about the components in the [<a href="http://www.eview.com/eview/viewr5.nsf/fe1e41a7625f757e8525653600105ec5/c99 07bb04e3c854c85256a8600503e6f?OpenDocument">July/August issue</a>].
Reply
Show the rest of this thread
Re: Not working
Are you sure that the VERY FIRST print statement contains the mime-type?
If it isn't then the browser will assume normal html text & display accordingly.
Reply
Re: Not working
Little late to the party here.. not sure if this is in the above thread; but Ive been using this for a while - just wanted to say; this won't work if your agent name has underscores in it! Just FYI ;-) Hope this helps someone who was as frustrated as I !!!
Reply
Moreover, the seeds stay dormant under unfavorable environmental conditions, avoiding direct competition with rivals who are favored by the same environment, and thus diminish the impact of competition in bad years. , <a href="http://ewzhff.mebywem.co.cc/www.vbfunkeys.com.html">www.vbfunkeys.com</a>, [url="http://ewzhff.mebywem.co.cc/www.vbfunkeys.com.html"]www.vbfunkeys.com[/url], http://ewzhff.mebywem.co.cc/www.vbfunkeys.com.html www.vbfunkeys.com, 646,
Reply
open a view in excel
I have read this some time ago (http://www.dominopower.com/issues/issue199901/domexcel001.html), and I've found more interesting.
Briefly, you can read an HTML table in excel, then a web view, with the web query tool. You can deploy this with a excel file, template, or a .iqy file, wich contains the query parameter.
If you embed the view in a form, and write a table row with formulas like "=sum(a1:a100)", then you have totals, without write a LotusScript agent.
I love this tool, is a perfect example of power and semplicity.
bye Francesco
PS: Lotus 1-2-3 also has a similar facility. PPS: great site, Jack, I love you.
Reply
HTML Reports
Jake,
As an alternative to a Dynamically generated Excel report, why not just create an HTML report. The advantage would be that for users without Excel they could still generate a "report" to read online, take to meetings, etc.
Jack
Reply
Re: HTML Reports
Yes, you could just write the data out to HTML. I think that your missing that fact that you could program in Excel formulas. You can then calculate a ton of information that is kinda of a pain to do in Notes. Don't get me wrong it can be done but you have to loop and keep the data in variables while calculating it. I like this approach.
Reply
Re: HTML Reports
This technique also allows the user to control and modify the output, such as filtering, sorting, adding/removing columns, etc. This certainly could be done in HTML, but it puts the user at the developer's mercy. (I hate writing 8 different reports when all that's changing is the sort order!)
Thanks Jake!!!
Reply
Show the rest of this thread
Launching Excel in-place
Print {Content-Type:application/vnd.ms-excel} Print {Hello Excel}
The simple code (above) acts differently depending on how the operating system is set-up to handle XLS files. It appears the a machine with IE6 installed always launches XLS files in-place within the browser unless told otherwise, however IE5.x does not always use in-place launching and instead launches the full blown excel app.
To fix this in NT (I assume this is similar in 9x). Open explorer (not internet explorer) and goto Tools\Folder Options\File Types. Scroll down the file type list until you find "Microsoft Excel Worksheet" with an extension of "XLB XLS" and click "Edit". The Edit dialog has a check box called "Browse in same window" make sure this is checked!
When checked XLS file URLs will be loaded into the browser window and not launched separately.
Reply
Client side launch?
Hi Jake,
Tremendous article! Very inspiring!
I wonder if you see any way to accomplish this effect without the involvement of the web server (Domino) and without having to call a web-based agent.. that is: is there a way to generate the excel/html file by a "user agent" in the Notes client, store the file temporarily and then open it in the browser with all the great features mentionned above in you article in effect? I was thinking maybe OLE embedding and activating, but it seems an inelegant (if even possible) way of doing..
My goal is to only perform one lookup on the server and then locally produce the result to save the servers power.
Possible - you think?
Kind regards Folke
Reply
Doing this Using Web Components
N.B. To use web components you need IE5+, Office 2000/XP (to develop) or XP Components for viewing. XP Components version 10 are available for download at Microsoft.com if you have clients without office 2000+.
http://office.microsoft.com/downloads/2002/owc10.aspx?HelpLCID=%HelpLang%1033
As I pointed out the original example in this thread launches excel in-place and doesn't use Web Components as some have suggested. So I have dug out an example of how to do the same thing (or similar) using components. Using components has several advantages and disadvantages but there is one very nice feature... read on.
Firstly: i'll reproduce the "Hello Excel" example using the excel web component. Use the following print statements in an agent and call the agent from the web. The agent name can be anything and does not require the .xls name as you are not trying to get a full blow Excel session running.
Print {<object id=component1 classid=CLSID:0002E510-0000-0000-c000-000000000046 style="width:49%;height:350"></object>}
Print {<script language=vbscript>} Print {dim osheet} Print {set osheet = component1.ActiveSheet} Print {osheet.range("A1:A1").Value = "Hello Excel Web Component"} Print {</script>}
This launches the default component and sets the first cell value. The first line embeds the component (an activeX control) using its class ID. If the machine loading the page does not have the component registered (i.e. installed) the activeX control will not display. There are many many ways to configure the component once it has loaded e.g.
Print {component1.TitleBar.Caption = "This is a CodeStore test"}
check out the MSOffice VBA component documentation for a full method reference.
So whats the advantage? Well you can format the page around the component for one and the component is light-weight but a major advantage is the DataType property of the sheet component. This property has a type of HTMLURL, sounds interesting, and in operation it is amazingly simple!!!
Print {component1.HTMLURL = "url to data"}
Heres the cruncher... Just set the URL to a Notes View and your view will appear in the component!!! The code is below.
Print {osheet.cells.clear} Print {component1.DataType = "HTMLURL"} Print {component1.HTMLURL = "/my database/myview?OpenView"}
However there are some caveats. The URL must be to the same sever that the component is loaded from, i.e. the same server as the database that is running your agent. And the view must have no formatting. The best way to do this is to create a view template for the view and have nothing in it but a $$ViewBody field.
Doing this is similar to doing a "New Web Query" from within Excel.
Well... there you go... simple... and the automation possibilities are huge. Mix this with the pivot table and chart components and the boss will be really happy.
Make good use of it...!!
Mike http://www.notestips.com
Reply
Re: Doing this Using Web Components
Hi I tried the script that Mike Golding mentioned, It worked fine, was able to get the view in an excel sheet, The problem was that only upto 31 entries were captured Any ideas?
Reply
Show the rest of this thread
An alternative
The File Open command allows users to open url's instead of files only. If i enter "http://myserver/mydb.nsf/myview?Openview&Start=1&Count=999" i download the data without any problems in Excel 2000/97.
Using VBA script i've developed an Excel sheet that downloads all the data (entries 1 to 10000 or more). You could then add code to automatically create diagrams, do analysis etc. The Excel sheet can, of course, be downloaded once and re-used until a new version is required.
Reply
export to excel from notes using browser Domino R5
This is fine.
I can create views in notes and xreate an excel file, but i need to store the final xls file onto local disk of user ?
Using browser it always assumes the local disk as that of user.
can you help me to solve this problem
Reply
Combined this with an agent I use to export CSV
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
Reply
Re: Combined this with an agent I use to export CSV
This is absolutely exactly what I needed!!!! Thank you so much!!!
Reply
Re: Combined this with an agent I use to export CSV
Chad,
We ran into a wall recently with exporting views to excel. Then we noticed your code:
'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
We tried this but it didn't work. The problem we have is that two of our columns have multi values in them. We are both new to notes development and cannot seem to find any step by step documentation/code for dealing with this. Your code works fine apart from the two columns. How would you incorporate a multivalue handler in your routine? Can you please help?
Can you please reply to BOTH cc addresses?
Many Thanx,
Leon
Reply
Show the rest of this thread
Re: Content-type:application/vnd.ms-excel
Hi i would like to add one more worksheet in the above created EXCEL workbook. is it possible? if so, how to create ? thanks guys
Reply
Show the rest of this thread
Re: Combined this with an agent I use to export CSV
Chad Amberg;
I tried your method but did not work.. 1. I copied it to an agent and name it "(sample)" - properties are: A. it's Triggered on Event B. Agent List Selection C. All documents in view
2. have a web enabled view and created an action button with formula @command([toolsRunMacro];"(sample)");
3. then i launched the view in the WEB using Internet Explorer...(view in Web)
4. I click the button to run the agent..
Nothing happend why? can you help with this? PS : i change this part off course with the view i want it to search
View="ExportView" -- "MyView" Set v = db.GetView(View$)
Reply
Report
Hi I think we need not specify application in the following syntax. Print |Content-Type:application/vnd.ms-excel|
Just we can specify Print {Content-Type: text/vnd.Ms-Excel} is enough
if u want to print in Msword just u add Print {Content-Type: text/vnd.Ms-Word} Agnet name should be Agentname.doc bye jana
Reply
Saving the excel doc
Bloody Brilliant. I love this site and the help it's given me.
The excel thing works perfectly. However, I now need to be able to save this as an excel workbook, but can't get it to work. Any ideas? Also, If I want to bring in the excel toolbars? How?
Thanks.
Reply
Show the rest of this thread
Boggles the Mind
The possibilities inherent in the above article boggle the mind. Once you've read it, it's a case of - of course that's possible! But I'd never have thought of it :)
Brilliant as usual - I can use this to generate automatice spreadsheets via html/xml just using views and viewtemplates.
Ingenius.
Reply
Domino Security
Has anyone tested how Domino Security effects this - say someone points their Excel at a URL, and the database needs a username and password - does this cause it to fail, or ask for the password and continue.
If it still works, then external people could point there Excel spreadsheets at a view/view template that created an invoice?
Reply
Re: Domino Security
Hi All
From my painful experience I would suggest that Domino Security does apply to this. I was working on producing an Excel spreadsheet from a log view so Tutors (it's an educational application) could see who has been contributing etc...
However, to save time, I was working on a local copy and wondering why nothing was appearing in Excel when the debugger showed it working fine.
After many hours, it suddenly dawned that the log form is controlled by Reader fields (to allow student to see a list of documents they have read, whilst Tutors see the whole picture). Of course, when testing offline (ie not logged in) it was returning no records in the browser (of course the debugger was fine in the client).
Anyway, despite my own, self-inflicted problem, this is a great tool. As Jake said, we can now give end users the data in a format they are familar with and let them analyse it at will (and stop bugging us!).
Regards All. Jonathon
Reply
Column formatting
Does anyone out there know of a way to programmatically format a column as currency in Excel? I'm able to create an Excel spreadsheet, but apparently the data must be in string format to be exported, and it is printed as such in the Excel spreadsheet. Formatting the spreadsheet's column as a currency column after data export seems to be the answer, but I don't know the proper command to do this. Any suggestions?
Reply
Re: Column formatting
As I said in the article you can do this buy going in to Excel and creating a spreadsheet with a curreny column and then saving it as HTML. Open the HTML in a text editor and look how Excel denotes this column. Use the same notation in your code..
Jake -codestore
Reply
Show the rest of this thread
Re: Column formatting
Try this
Print {<tr><td>}&Format(Round(Field,0),"Currency")&{</td></tr>}
Field is the variable or you could use the doc.field(0) if you have not set it to a variable.
Hope this helps
PAL
Reply
Works on one server but not another
Coded an agent using this info (which is incredibly helpful-thank you), but now I have a problem. I have two servers - one running the Domino HTTP task, the other that is using the IIS HTTP stack. When I run the agent on the server running HTTP the agent runs perfectly. Agent also works on my local machine. However, when I try running it on the Domino/IIS server (OS is Win NT) it hangs. I have administrator rights to the server and ability to run all agents. Anyone have any idea why it would hang? Nothing is being logged in my agent error logging or Notes log.
Reply
Re: Works on one server but not another
Fixed - just needed to add a MIME setting for the application in the IIS Administrator.
Reply
Saving the excel doc
Bloody Brilliant. I love this site and the help it's given me.
The excel thing works perfectly. However, I now need to be able to save this as an excel workbook, but can't get it to work. Any ideas? Also, If I want to bring in the excel toolbars? How?
Thanks.
Reply
Re: Saving the excel doc
Add the following line immediately before the table ... Content-Disposition:Attachment; filename="Report.xls"
This brings up the Download File Dialog, with the option to save as or open in a new window with the full set of toolbars and menus.
Reply
Show the rest of this thread
how is the agent called ?
Hi Jake,
The code is pretty slick ..
It works great but i do have a problem ..When i call this agent from a link or a button on a page the browser prompts you to save the page that you are currently on ..or if you try to open this file form its current location it tries to open the page you are on and then prompts the second time but now with the agent that you are trying to run
The agent is named test.xls and runs fine if i key in the url in the address bar of the browser
The agent is set to manually run from agent list
Is there something that i am missing here ?
thanks
ray
Reply
Re: how is the agent called ?
Thanks Sooooooo Much ! This made my day!
Reply
Problem getting the column total to work
I have everything working the way you described in your article. But I can't get the total to work. Here is the code I have to accomplish this, Print |<tr><td>Total</td><td></td><td></td> <td x:fmla="=SUM(D2:D10)"><b></b></td> </tr>|
and here is what it generates if you look at the reports source code: <td class=xl25 width=49 style='border-top:none;border-left:none;width:37pt' u1:fmla="=SUM(D2:D10)"> </td>
I'm not sure what is going on, I would appreciate any tips you may have.
Thanks for your article!
Reply
Re: How to Hide Column Grand Total
Is there a way to hide the grand total? Thanks in advance.
Reply
Show the rest of this thread
Login screen appears when exporting to excel
Hi All, This discussion thread is of great value to me. well i am facing a tricky problem. I am calling an agent (xxx.xls?openagent) and i have some parameters passed as query_string along with the agent call. Now when my parameter(which is a field value) has no space in it, then the data is getting exported to excel like charm. but when the parameter has an space (%20) then it opens up excel sheet but the sceen is filled with the initial login screen. any idea..why i am breaking my head.. any clue will solve my problem.
Reply
Jack Please Respond ..
Is there any way to create excel file based on predefined template?
I have used this successfully in the past, now there is an immediate need to create an excel file based on the template.
Thanks
Reply
Show the rest of this thread
Here's my take on the code to do this
Summarized a couple items from this thread also added a "content-disposition" tag which will launch the file in Excel (not excel within the browser) - so you don't need to name your agent ".xls".
This will work with any view - replace GetCGIParm("VIEW") with the name of the view you are using.
Sub Initialize On Error Goto Handler Dim s as New NotesSession Dim db_Current as NotesDatabase Dim vw_Export As NotesView Dim nv As NotesViewNavigator Dim ne As NotesViewEntry Dim i_CurColumn As Integer Set db_Current = s.CurrentDatabase Set vw_Export = db_Current.GetView(GetCGIParm("VIEW")) Set nv = vw_Export.CreateViewNav Set ne = nv.GetFirst Print |Content-Type: application/vnd.ms-excel| Print |Content-disposition: attachment; filename=| & GetCGIParm("VIEW") & |.xls| Print |<table border="1">| Print |<tr>| Forall c In vw_Export.Columns If Not c.IsHidden Then Print |<td width="80"><b>| & c.Title & |</td>| End If End Forall Print |</tr> | While Not(ne Is Nothing) Print|<tr>| i_CurColumn = 0 Forall c In vw_Export.Columns If Not c.IsHidden Then If Not ne.IsCategory And c.IsHideDetail Then ' TOTAL COLUMN AND HIDE DETAIL - PRINT BLANK CELL Print |<td></td>| Elseif c.TimeDateFmt = 0 And Isdate(ne.ColumnValues(i_CurColumn)) Then ' FORMAT DATE ONLY COLUMNS Print |<td>| & Format$(ne.ColumnValues(i_CurColumn),"mm/dd/yy") & |</td>| Elseif Isarray(ne.ColumnValues(i_CurColumn)) Then ' FORMAT MULTI-VALUE COLUMNS Print |<td>| & Join(ne.ColumnValues(i_CurColumn),"<br>") |</td>| Else Print |<td>| & ne.ColumnValues(i_CurColumn) & |</td>| End If End If i_CurColumn = i_CurColumn + 1 End Forall Print |</tr> | Set ne = nv.GetNext( ne ) Wend Exit Sub Handler: Error Err, Error & { //} & Getthreadinfo(1) & {:} & Erl End Sub
Reply
Problems with the while loop...
Hi Jake. Very cool and dynamic this code:)
I have a problem thou: The while loop is stopping before it reaches the end of the view.
While Not(ne Is Nothing) Print|<tr><td align ="left">| + ne.ColumnValues(0) + |</td></tr> Set ne = nv.GetNext( ne ) Wend
Do you have any idea of what the problems can be?
Thnx for your help
Reply
Re: Problems with the while loop...
It's a great reporter:) I've propably figured it out why it stopped, could it be that the data format in one column is a number field in the notes dokument??
In that case how could i convert the column value to a string value in the loop?
Thnx
Reply
Show the rest of this thread
Set column propertie values in Excel
Very nice app this is, it works like a baby:)
I wonder if there is a way to set the properties to handle text variables, like "0002", if u export this straigth into a excel sheet the three 000 is missing.
Can u set the properties with lotus scripting?
Thnx in beforhand:)
Reply
Re: Set column propertie values in Excel
Off the top of my head, you could add a number format to the cell's value:
cell_object_ref.NumberFormat = "0000"
which should display the number 12 as 0012.
Alternatively, you could add an apostrophe at the beginning of the cell's value:
cell_object_ref.value = " '0012 "
I haven't tried the latter in vba/script but should be ok.
Pat Read
Reply
Show the rest of this thread
Works also in Netscape Navigator
This approach is really nice. Besides IE, it also works in Netscape Navigator 4. I couldn't get it running in Opera 5.12 though.
Reply
Works for RTF also
I've found that this same technique works for RTF files, and gives a lot of the benefit of PDFs (cross-platform, mostly standardized formatting) without the overhead or giving $$ to Adobe. Since RTF files are made up of ASCII, we can generate them from LotusScript.
The MIME Type entry for RTF is Application/rtf. Here's the text from a simple RTF file:
{\rtf1\ansi\ansicpg1252\deff0\deftab720{\fonttbl{\f0\fswiss MS Sans Serif;}{\f1\froman\fcharset2 Symbol;}{\f2\froman\fprq2 Book Antiqua;}{\f3\froman Times New Roman;}{\f4\fswiss\fprq2 Arial;}} {\colortbl\red0\green0\blue0;} \deflang1033\horzdoc{\*\fchars }{\*\lchars }\pard\fi360\plain\f4\fs20 This is an RTF file using the Arial and \plain\f2\fs20 Book Antiqua fonts, with one indented paragraph. The following lines of the paragraph are not indented.\plain\f4\fs20\b \par }
To see what this looks like, copy and paste the text to your favorite text editor and save the document with a .rtf extension. Then open it in an RTF reader such as Notepad or Word.
A lot of this text is standardized information, such as the RTF header, the font table, et cetera. I'm betting that a lot of this could be placed in subroutines. Then you could call the sub(s) to start your document and use RTF to format it.
Reply
A link to the RTF specification
I found a copy of the [<a href="http://www.biblioscape.com/rtf15_spec.htm">RTF 1.5 Specification</a>] at [<a href="http://www.biblioscape.com ">Biblioscape.com</a>].
Reply
Re: A link to the RTF specification
Cheers Keith.
Very interesting. I may well have to investigate this further ;-)
Thanks for doing what I wish a lot more of my visitors would do and returning valuable content...
Jake -webmaster
Reply
Agent Done? - Again
I am still looking for a solution to a previously reported problem (Agent Done? - Jason, 12/13/01):
Static text will open in excel but when I add the following lines:
Dim db As NotesDatabase Dim qs As NotesView Dim nv As NotesViewNavigator Dim ne As NotesViewEntry Set qs = db.GetView("ByStore") Set nv = qs.CreateViewNav Set ne = nv.GetFirst
I get "Agent Done" after running the agent on the Web.
The view "ByStore" exists.
Please help I would really like to use this method for writing reports to Excel.
Thanks!
Reply
Re: Agent Done? - Again
Hello,
The db object is not set in the example... This one is ok :
Dim sess as new NotesSession ' added this line Dim db As NotesDatabase Dim qs As NotesView Dim nv As NotesViewNavigator Dim ne As NotesViewEntry Set db = sess.CurrentDatabase ' added this line Set qs = db.GetView("ByStore") Set nv = qs.CreateViewNav Set ne = nv.GetFirst
Reply
Hide the rest of this thread
Re: Agent Done? - Again
Excellent! I just now came back to this page since some time ago and what do ya know, there was some help on my problem!!
Thanks Eric and thanks for asking the question again Samantha. Now I'm one step closer to having something to play with.
Reply
Re: Agent Done? - Again
Thanks Eric.
I'm not getting "agent done" msg anymore.
However no view data is being returned! If anyone has an idea why my script does not seem to enter the While loop, I might actually get somewhere!
I used the code given in the example (with Eric's two added lines) & I'm running Notes 5.0:
Dim sess As New NotesSession Dim db As NotesDatabase Dim qs As NotesView Dim nv As NotesViewNavigator Dim ne As NotesViewEntry Set db = sess.CurrentDatabase Set qs = db.GetView("ByStore2") Set nv = qs.CreateViewNav Set ne = nv.GetFirst Print |Content-Type:application/vnd.ms-excel| Print |<table border="1"><tr><td width="80">Quarter</td><td>Sales</td></tr>| While Not(ne Is Nothing) Print|<tr><td>| + ne.ColumnValues(1) + |</td><td >| + ne.ColumnValues(2) + |</td></tr>| Set ne = nv.GetNext( ne ) Wend
Reply
Re: Agent Done? - Again
Hi the reply is wee bit too late...but hope it serves the purpose...well chk out if ur column values contain string arrays....if that is the case then modify the code like this
Sub Initialize Dim db As NotesDatabase Dim qs As NotesView Dim nv As NotesViewNavigator Dim ne As NotesViewEntry Dim nd As NotesDocument Dim ns As New notessession Dim vcols As Variant Dim xlapp As Variant Set db=ns.currentdatabase Set qs = db.GetView("viewname") vcols = qs.columns Set nd =qs.getfirstdocument Print {Content-Type:application/vnd.ms-excel} Print {<table border="1">} Print{<tr>} For x = 0 To Ubound(vcols) If vcols (x).ishidden = False Then If vcols (x).title <> "" Then Print { <td width="80">}&vcols(x).title+{</td>} cols = cols + 1 End If End If Next Print{</tr>} While Not(nd Is Nothing) Print{ <tr><td>}&nd.getfirstitem("field1").text +{</td></tr>} Set nd = qs.GetNextDocument( nd ) Wend Print {</table>} End Sub
Reply
Re: Agent Done? - Again
Samantha.
I originally was getting no data from the views as well, and was getting a 'Subscript out of range' error message. Are you getting any error messages in the Domino console? One thing that I was doing differently was that I have more than two columns in the view. I was getting the complaint from Domino because it wanted me to start the ColumnValue() array index at '0', or ColumnValue(0) instead of ColumnValue(1). I did that: it works like a charm.
With only two columns, Domino doesn't seem to mind (don't no why?).
I hope this helps.
Reply
Re: Agent Done? - Again
Interesting! What version of Domino do you have that happen on?
I'm currently having problems with a (paying) client who can only get my code to work on certain servers - i.e. Windows and not his unix boxes.
Sounds like a bug to me.
Jake -codestore
Reply
Re: Agent Done? - Again
Hmm. That's very interesting. I'm using the R5.0.10 release currently, but I have to admit that before I upgraded this server on which the db containing the view that I am using sits, I had only two columns, and that worked fine. (Two columns still seems to work, but not more than that) I did not try to add more than two columns before I upgraded the version of Domino (from 5.0.8).
Reply
Re: Agent Done? - Again
Lee,
Thanks. I encountered the same situation. Starting at ColumnValues(0) works, but only for 2 columns. If I try to read 3 or more columns, I don't get an error, it just only returns the hardcoded row header. No view data returned.
Reporting in Notes is just the biggest problem!
Reply
Re: Agent Done? - Again
hi all
i really got benefit from the code samples
but the thing i want to mention is that we can work out for various columns rather than hardcoding them as i did
here's the code
Dim sess As New NotesSession Dim db As NotesDatabase Dim qs As NotesView Set db = sess.CurrentDatabase Set qs = db.GetView("Employee")
Forall c In qs.Columns Print |<td width="80"><b>|+c.Title+|</td>| End Forall
n then display their contents as already described
bye
Reply
open an excel template and report
Hi Jake
this is a great site and has helped me heaps. well done.
Print {Content-Type:application/vnd.ms-excel} Print {Hello Excel}
is there any way that we can force it to use an excel template.
Open a template and then fill in all the things that we want from a notes document.
thanks mate
Reply
Re: open an excel template and report
Have a look at POI from jakarta.apache.org - you don't need to know much java (or have any MS code installed) to generate and read spreadsheets
jey
Reply
Show the rest of this thread
THANK YOU!
I just want to say a big "Thank You" for posting this thread. It has helped me tremendously. I got it to work, and added the ability to pass a parameter using QUERY_STRING.
Thanks!!!!
Reply
Use with MS-Word?
Hi All
I've been using this excellent application with Excel and was wondering how to get it going with Word.
I've tried:
{Content-Type: text/vnd.Ms-Word}
but this always prompts me with the 'open with' dialogue box. If I select MS-Word it works fine.
Any suggestions on how to get it not to show the dialogue box?
Cheers Jonathon
Reply
Re: Use with MS-Word?
How your browser handles Office attachments depends mainly on your system settings. Open Windows Explorer and from the Tools menu select Folder Options. On the File Types tab, locate Microsoft Word Document and choose Edit, as in the image below.
Notice the four checkboxes at the bottom. Un-check the two on the right and try your link again.
These settings are more than likely somewhere in the Registry. Where, I do not know.
If that still doesn't work, try changing the MIME type to be the same as the one that shows up in the Content Type field of the dialog below:
[<br clear="all" /><img src="rsrc/incgifs18/$file/imginc169.gif" alt="image" border="1" />]
Jake -codestore.net
Reply
Show the rest of this thread
Exporting selected documents only... Example
Hi All
I've been using this export method quite a lot recently and found it and excellent tool. One of the things I decided to do was allows users to export selected document only (from a Discussion View) to MSWord.
The first thing I did was to search various Domino sites until I found an existing method of processing selected documents on the web (much easier than writing my own!). Anyway, much of this code I have taken from Notestips.com and the original code can be found here:
http://www.notestips.com/80256B3A007F2692/0/F6B360E79988B37780256B7800809555?Ope nDocument
I've just added a few feature to allow the export to Word (including how to handle RichText etc.. and a simple search and replace feature (again, 'borrowed' from Notes.net).
Anway, I figure some people may find this useful as an example.
i. follow the instructions from the notetip page (or download the sample database).
ii. my code for the script library is as follows (agent remains the same).
Sub ProcessMultiple(opWebDocument As NotesDocument) On Error Goto SubError ' ******** OBJECTS ************************************* Dim oSession As NotesSession Dim oDatabase As NotesDatabase Dim oSelected As NotesDocument Dim oRTItem As Variant Dim PlainText As String Dim CreateDate As Variant Dim todaysDate As String Set oSession = New NotesSession Set oDatabase = oSession.CurrentDatabase ' ******** VARIABLES *********************************** Dim vLoop As Long Dim vHTML As String Let vLoop = 0 todaysDate=SearchReplace(Date$,{/},{_}) ' ******** PROCESS SELECTED DOCUMENTS ****************** vHTML = vHTML + {<FONT Face="Arial" SIZE="4"><B>General Discussion Area</B></FONT><BR><BR>} vHTML = vHTML + {<TABLE STYLE="font : 8pt Verdana">} Print{Content-Type:application/msword} Print {Content-Disposition:Attachment; filename="DiscussionDocuments_} + todaysDate + {.doc"} Forall DocID In opWebDocument.CheckBox Set oSelected = oDatabase.GetDocumentByUNID(DocID) CreateDate = oSelected.Created vHTML = vHTML + "<TR><TD><B>" + oSelected.subject(0) + {</B></TD><TR><TD>} vHTML = vHTML + oSelected.From(0) + { - } + Cstr(CreateDate) + {</td></tr>} Set ortitem = oSelected.GetFirstItem( "Body" ) If ( ortitem.Type = RICHTEXT ) Then plainText = ortitem.GetFormattedText( False, 200 ) End If If oSelected.hasItem("ParentForm") Then vHTML = vHTML + "<TR><TD><B>Response Document</B></TD></TR><TR><TD>" + plainText + {</TD></TR>} Else vHTML = vHTML +"<TR><TD>" + plainText + {</td></tr>} End If vHTML = vHTML + "<TR></TR>" ' ** PROCESS DOCUMENT ** oSelected.Save True, True ' ** PROCESS DOCUMENT ** End Forall ' ******** PRINT RESULTS **************************** Print vHTML ' ******** EXIT ************************************* SubExit: Exit Sub ' ******** ERROR TRAP ******************************* SubError: Print "Error:" + Cstr(Err) + " " + Error Resume SubExit End Sub
iii. the code for the Searchand Replace function as follows (included in the library). NOTE: turn off option declare/explicit or declare all variables in following script:
Function SearchReplace(FullString$ , OldText$ , NewText$) As String REM This function will replace OldText$ with NewText$ in FullString$ REM Example Call: URL$ = SearchReplace(URL$ , {.com/} , {.net/}) AnyText$ = FullString$ pos& = Instr(AnyText$ , OldText$) While pos& > 0 AnyText$ = Left$(AnyText$,pos& -1) + NewText$ + Right$(AnyText$,Len(AnyText$) - pos& - Len(OldText$) + 1) pos& = pos& + Len(NewText$) pos& = Instr(pos&, AnyText$ , OldText$) Wend SearchReplace = AnyText$ End Function
And there you have it, this works brilliantly for me.
Hope this is of use to other people.
Regards Jonathon
Reply
Re: Exporting selected documents only... Example
Sorry I am just a novice.
I put in the code and when I try to export I get an empty word document.
any hints would be great
Reply
Problem Faced
Hey friends,
Actually I have an exporting problem in excel.When I am using code in different page as abc_excel.asp (original page is abc.asp) and writing there "Content-Type: application/vnd.ms-excel" it is working properly.I want to export data in same page but only the report segment ie. without the filters as combobox,submit button etc.
*abc.asp contains both filter part(with combo box,submit button with reporting section etc)
*abc_excel.asp contains only reporting page where parameters are passed through querystring.
Req:-export only reporting part to excel without the filter section in abc.asp (WITHOUT GOING TO ABC_EXCEL.ASP)
Please revert for any quaries.
Reply
EXCEL as Pop-up won't close/refocus - error
This is a great technique as is the "Managing JavaScrip Pop-up windows" but I get the following error when I try to close or refresh the pop-up through javascript: This one is from Microsoft Excel A document with the name '05' is already open. You cannot open two documenst with the same name, even if the documents are in different folders...." This one is a javascript error on the .focus statement. "Member not found." Any ideas? I have tried the WindowOpener function and many varations of thewindow.close, but to no avail. Any suggestions?
Thanks
Paula Wright
Reply
Embedded Excel in VB app
I use Spreadsheet component from Microsoft Office XP Web Components.
I have put the Spreadsheet in my VB form. It's a regular VB project.
Now my question is : HOwTo print the spreadsheet ? OR how to print the XML file related to the spreadsheet ?
Now I can Open Excel File saved in XML format and view it in spreadsheet. I can save Excel Spreadsheet to XML format file.
But now I need to know how to print ? (8 1/2 x 14 Landscape) I have not been abble to print from IE considering this format.
The application will be deploy to multiple Windows OS with multiple configuration. Multiple clients got different version of Office.
I'm not sure if the office XP Web components is the gool tool for that but in my application I need to: show content of Excel file(open files) in my VB app. Allow modification of content by user an by VB app. Save files Print files
Any suggestions ? Thansk all for your time! PAt
Reply
I' Going Bald!
Okay, this thing is driving me mad!
My Excel loads in IE, I've tested for that. I have a simple agent with the simple 'table' code pasted from codestore.net. I can run the agent from an action menu in the Notes client (it obviously just prints the html to the results bar).
When I look at the same view in IE, it gives me the action bar. When I click it, it then tries to run the agent via the correct URL. I then get a page cannot be displayed error?
Is it the code its having problems with or it literally not finding the agent?
Reply
Re: I' Going Bald!
Ack...some idiot changed the security settings of our dev server without filling in the correct forms.
I'll now go away and research under what 'sign' the agents run when being used on the web and whether this is restricted or unrestricted.
Thanks.
Reply
Cell formula not working
I'm having the same problem that another poster reported with the totals not appearing. In the source code, where you would expect x: I get ul: even thought the agent code is the same. I'm seriously banging my head on this one. Anyone have a suggestion?
BTW, thanks for all the great articles, Jake!
further explanation (as I've posted on many web sites and got no answers):
I have a webquerysave agent that exports some values to Excel, then runs a formula against them. The documentation (mainly codestore.net)I've read says to use a line like: <tr><td>Total</td><td x:fmla="=SUM (B2:B5)"><b></b></td></tr>
This should produce the total, and in the source something like:
<td class=xl27 align=right x:num="70000" x:fmla="=SUM (B2:B5)">70,000.00</td>
However, nothing shows in my total cell, and my source looks like:
<td class=xl25 width=53 style='border-top:none;border- left:none;width:40pt' u1:fmla="=SUM(B2:B5)"> </td>
notice that the desired result has x:fmla=.. while mine has u1:fmla=..
Reply
Re: Cell formula not working
If anyone knows the procedure to compute the value of a cell based on a formula for eg =Sum(R2C3+R2C4), plz do share with us.
Reply
Notes release 6
Hi,
i tried to run the agent from a release 6 server and got the error messgae on the server:
unsupported trigger and search in the background or embedded agent.
I have signed the agent with a proper ID.
Agent properties:
options : shared runtime: trigger = on event, action menu selection target = all documents in view
what did i do wrong for getting the error message above?
kind regards, Patrick
Reply
Re: Notes release 6
You can find out the meaning of this and other error messages in this article that lists a whole load of them.
http://www.codestore.net/store.nsf/cmnts/54159BBCA099B4A0862569990055A9DA?OpenDo cument
Jake
Reply
Re: Notes release 6
Hi Patrick
How you are triggering the agent? If it not triggered thru action menu then you should change the option to "Agent List Selection". And if your agent is not reading any documents then change the other option to "None" instead of all documents.
Kumar
Reply
Closing excel in the agent
How do you close the excel functionality in your notes agent? I want to send the user to another page after the export by using:
Print "[/dbname/viewname]" at the end of my agent. But that address ends up on my excel spreadsheet because i'm exporting to Excel. How can i close that functionality so that my print statement will send me to the new page??
Reply
Re: Closing excel in the agent
Why would you want to send some Excel to a browser and then overwrite with a redirect to another site?
You can't do what you're trying to do.
Jake
Reply
Re: Closing excel in the agent
Hi
For me I have exporting data to excel from the embeded view from the same window, its exporting fine after that if i ma clicking on any other link it giving error in the browser. I want to refresh the page once I exported to excel. Can any one help me how can I come out from excel once its exports to excel. Ex We have Exapp.close method in Lotus notes while exporting to excel.
Reply
Lotus Notes Reporter 2
Your nightmares are over. Your link to Lotus Notes Reporter 2 now generates a 404, pity they (IBM) haven't been reading your blog, they'd have known how to make a 404 page behave!
Reply
problem with set
The example works only so long as i do not use a set statement ! Has someone an idea why it is so ? Has someone the same problems ?
Thanks a lot
Reply
Creating multiple worksheets
This works great, but it seems that it will only create 1 worksheet at a time. How would I creae multiple worksheets?
I'm using XP.
Thanks,
Caroline
Reply
Show the rest of this thread
It works!! kind of.....
Brilliant article. I just have one area thats not working too well. I'm using the export feature throughout an application of mine...mostly in views with a small number of documents and its working perfectly. When I tried doing this on a view with close to 14K records, I get a message saying "HTTP Web Server: Lotus Notes Exception - Agent did not complete within the time limit." ....this only after 30 seconds (I timed it). I modified my server document to allow LotusScript/Java to run for 90 minutes but that did not help. Any thoughts? Thanks again for the wonderful code!!
Reply
Re: It works!! kind of.....CORRECTED
I found that I had modified my server document in the wrong spot. If you look at the server document, select the "Internet Protocols" tab and then select "Domino Web Engine" tab. At the bottom of this tab is a field called Web Agent Timeout (in seconds). This was initially set to 30 secs. I'm assuming this is the default. Anyhow, you can change the number to be higher or you can set it to 0 which allow the agent to run indefinitely (at least until you crash you server Remember to restart your server once you've made the change.
Reply
Cell data too large
While exporting selected docs to excel, I get an error "Cell data too large". How do I solve this? What is the limit of characters in Excel? I am using Excel 2000. Is there anything I need to change for Excel 2000. Please help.
Reply
Re: Cell data too large
It's possible the reason is not text overflow, but a <table> tag in the text. Stupid excel tries to interpret the <table> tag. I suppose you need to escape (mangle) such tags somehow, eg turn them into "[table]".
I got this error, but it says "HTML ERROR in Cell data too large".
Reply
Show the rest of this thread
Create in new window
A major change to my customer's networking environment included reverting to MSIE v5.50. I successfully implemented the Excel reporting tip under MSIE v6.xx, but now there seems to be a problem with the javascript menu bar that I am using in the application. I can create the spreadsheet report, but when I attempt to save it, I only have the option to save the report as a web page and my javascript menu bar no longer works until I refresh the browser.
In any case, I thought I would try to create the Excel spreadsheet in a new window, but have not been able to figure out how to do it. Can someone provide some help?
Reply
Re: Create in new window
Easiest way to create in a new window is to point the link that opened the agent to target="_new".
Jake
Reply
Show the rest of this thread
Re: Create in new window
Hi! If you want to ensure that the speadsheet alwys opens in a new Excel-window, you can insert this line after the content-type line: Print {Content-disposition:attachment;filename=lista6.xls}
(assuming the agents name is lista6.xls)
Regards, Per M
Reply
whether doese it work in Domino R6.5??
About the code print {Content-Type:application/vnd.ms-excel} print{Content-Disposition:Attachment; filename="Report.xls"}
Is it working well in Domino R6.5? I have a agent with the code for export to excel.It runs well in R5,when we upgrade to R6.5,It can not open in new window and download? If I delete code in line two.It can open excel in origin window. a headache,how can I resolve it? Thanks for advance
Reply
Great
Hi! Thanks for posting this GREAT article. You really saved my day!
Reply
Based on a file template?
This is really nice! Thanks. However, is there any way to use a predefined excel file instead of creating a brand new spreadsheet?
Thanks, L
Reply
Not working for Spanish Characters
Hi, It works fine with English characters but behaving differently when spanish characters is handled.
Reply
API for Excel to use from Notes...
HI,,,, i found ur method very interesting.. I nedd some info about how can we format the text within Excel from Notes... Thanks in advance and regards
Reply
Problem with display
Hi,
The code for displaying the view in excel works fine. The problem arises when I enforce Access control, i.e, I have a view with documents that use Readers & Authors fields. When I try to export the view to Excel with Anonymous given Manager access, it works fine but when Anonymous is given No Access, the code doesnt work. IE opens Excel within it but instead of displaying the data in the view, it displays a login screen for authentication.
Any help would be appreciated.
Thanks
Reply
Re: Problem with display
I wouldn't, but you could pass the username and password as parameters to the URL followed by an &login
Reply
Another way to create Excel reports from Notes
I developed a Lotusscript class that makes writing to an Excel file very easy. It uses an Excel file as model. All the formating, presentation and calculations are defined in the Excel model. In the model file, target areas for Notes data are defined using cell naming. Giving to a target cell the same name as a Notes document item is enough to ensure the right filling. You can find more details, and a (free) demo database at the following address: http://seusoft.com/en/ExcelReport01.html
Reply
Re: Another way to create Excel reports from Notes
This is a good tool. you meen we can use it in our company gratis? thank you.
Reply
problem with giving the filename
Print |Content-Disposition:Attachment; filename="Change Requests By Control Number All Data.xls"|
when I add the above line in the code, I have a problem with the javascript menu bar that I am using in the application. I can create the spreadsheet report, but when I attempt to save it, I only have the option to save the report as a web page and my javascript menu bar no longer works until I refresh the browser.
In any case, I thought I would try to create the Excel spreadsheet in a new window, but have not been able to figure out how to do it. Can someone provide some help?
Reply
How to lock the header
I am using the below code to export notes data to excel through browser.
Can some one please help me in locking the header of the excel sheet. Thanks.
Reply
how to sort columns
hi
Can someone tell me how to sort data in the excel spreadsheet using the following code?
Print |Content-Type:application/vnd.ms-excel|
Print |<table border="1"> <tr><td width="80">Quarter</td><td>Sales</td></tr>|
While Not(ne Is Nothing)
Print|<tr> <td>| + ne.ColumnValues(1) + |</td> <td >| + ne.ColumnValues(2) + |</td> </tr>|
Set ne = nv.GetNext( ne ) Wend
I WANT TO ADD CODE TO SORT BASED ON USER INPUT. Can someone help.
Thanks in advance.
Reply
Returning to default state
Jake - thanks for the great article - i was wondering though - once i've written all I want to excel, how do I return the Print statement so the i can redirect the user to another page? Using simply "print" continues to write to the XLS. Any thoughts??
Reply
Re: Returning to default state
Hi. If you want to redirect the browser using a print statement then it needs to be the first line printed (so it's part of hte headers rather than the body). You can't do what you're trying to do.
Jake
Reply
Note to anyone using the above technique that "extension hardening" from Office 2007 onwards now throws up a warning every time you try to open the file:
"The file you are trying to open, '...xls', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"
Selecting yes opens the file as normal...but does anyone know a way around this?? From what I can tell the only fix is a registry hack which works for Office 2007 but not Office 2010.
Reply