One-click Microsoft Excel (or Symphony Spreadsheets) via XPages and SSJS

Contact Information

Chris Toohey

Blogger, podcaster, writer, and geek Chris Toohey covers topics from application development to the latest must-have-gadgets.

dominoGuru.com on Facebook

Latest Updates

Products & Applications

Showtime
My Blackberry Enterprise Server Push Utility for the Lotus Notes Client, allows you to create Jobs for individual Channel, Message, and Browser Content Pushes, as well as allows you to delete Pushed Channel Icons from defined recipient devices.

Time Tracker
The idea is simple. At the start of your day - upon completion of your first task - create an entry highlighting what you did and whether you feel it was an efficient or inefficient use of your time. Based on several requests, you can also select the priority, apply categories, or even align your time against a project.

For Lotus Notes Client v8.0 and above, you can use the Time Tracker Widget to make this process even easier!

Zephyr
My Configuration-based Rich Text Mail Merge and Emailing Utility, Zephyr allows you to create rich, data-driven emails to support automated workflow - all via Microsoft Word Mail Merge-like architecture. Dear <firstname> allows you to personalize each email message not only to the individual recipient, but also to the individual application workflow event!

xCopy
xCopy is a simple configurable xCopy client for the Lotus Notes client. By creating and defining xCopy Profiles, you can batch process your file backup or remote upload jobs. With the addition of the xCopy sidebar widget, you can easily kick-off these jobs, and modify both the xCopy Profiles and xCopy itself.

Community & Resources

Lotus Technical Information & Education Community

The Lotus Technical Information & Education community is comprised of IBM, business partner, and customer subject matter experts who use product wikis, published articles, white papers, community blogs and the latest in social media to build and share high quality technical content.

OpenNTF.org - Open Source Community for Lotus Notes Domino

OpenNTF is devoted to enabling groups of individuals all over the world to collaborate on IBM Lotus Notes/Domino applications and release them as open source.

developerWorks Lotus : Wikis

Share your deployment experiences and best practices in our wikis and help IBM to create scenarios for successful deployments. Contribute to the community by collaborating on shared content and leverage the shared knowledge from that community.

One-click Microsoft Excel (or Symphony Spreadsheets) via XPages and SSJS

04/19/2010 01:35:29 PM by Chris Toohey

One of the goals of my View developer2010 session, Using MVC Architecture to Take Your XPage Applications to the Next Level, is to show you that XPages offer User Interface (or View) options for your IBM Lotus Notes Domino Applications far beyond a Lotus Notes Client or Web Browser Client UI component.

One of my examples used in the demo application illustrates this point by giving the user a click-to-export Button on the dashboard-style index.xsp XPage. Pretty slick stuff, especially when the entire XPage and SSJS source is 31 lines of code.

export.xsp XPage Source


<?xml version="1.0" encoding="UTF-8"?>
<xp:view xmlns:xp="http://www.ibm.com/xsp/core" rendered="false">
    <xp:this.afterRenderResponse><![CDATA[#{javascript:var exCon = facesContext.getExternalContext(); 
var writer = facesContext.getResponseWriter();
var response = exCon.getResponse();
var projects:NotesView = database.getView('projects')
var viewNav:NotesViewNavigator = projects.createViewNav();
var viewEnt:NotesViewEntry = viewNav.getFirst();
var output:string = "";
while (viewEnt != null) {
  output += "<tr>";
  output += "<td>" + viewEnt.getColumnValues()[0]; + "</td>";
  output += "<td>" + viewEnt.getColumnValues()[2] + "</td>";
  output += "<td>" + viewEnt.getColumnValues()[1] + "</td>";
  output += "<td>" + viewEnt.getColumnValues()[3] + "</td>";
  output += "</tr>";
 viewEnt = viewNav.getNext(viewEnt);
}
response.setContentType("application/vnd.ms-excel");
response.setHeader("Cache-Control", "no-cache");
writer.write("<table>");
writer.write("<thead><tr>");
writer.write("<td><b>Project</b></td>");
writer.write("<td><b>Developer</b></td>");
writer.write("<td><b>Estimate ( Hours )</b></td>");
writer.write("<td><b>Estimate ( Budget )</b></td>");
writer.write("</tr></thead>");
writer.write(output);
writer.write("</table>");
writer.endDocument();}]]></xp:this.afterRenderResponse>
</xp:view>

A few things you'll notice right away...

Non-rendered XPage
This is an example of the XPage XAgent stuff I've been talking about lately. As seen on Line 2, I set the XPage rendered parameter to false. We'll be using the afterRenderResponse to build our Excel export.
Defined NotesView
On Line 6, I've defined projects as my target NotesView. You'll want to change this, as well as your intended export content (Lines 12-15) to match, well, whatever you're looking to export.
Controlling the rendered ContentType
Line 19, where our magic happens... I simply set the ContentType to application/vnd.ms-excel, and allow the ResponseWriter to build my table.

Yep, a table. and it's supported by Microsoft, too.

Using your export.xsp XPage

Export Button in IBM Lotus Notes Domino Designer XPage Design Pane

It's pretty simple really, since our XPage does all of the heavy lifting for you.

Step 1: Add a button that opens your XPage

The above Export button runs the following event:

<xp:openPage name="/export.xsp"></xp:openPage>

Step 2: Open (or download) the resulting Excel spreadsheet

Microsoft Excel Spreadsheet Export from IBM Lotus Notes Domino XPages

Yeah, that's it.

You can run this in the Web Browser Client as well as the Lotus Notes Client (8.5.1 or greater).

(You will want to rename the export.xsp to something.xls when you run your Export...)

In Closing...

Using this technique, you will be able to rather easily export your NotesData to your productivity suite. You can modify the SSJS to include UI-driven parameters passed via session variables to give you even more control over what is being exported.

And, of course, if you liked this sample... I think you'll love my session at developer2010. Let me know if I'll see you there!

Permalink | Comments (19)

Applications Best Practices Development Developer2010 Lotus Lotus Notes SSJS XPages

Name:Paul WithersWebsite:http://hermes.intec.co.uk/intec/blog.nsfComment

Great and very simple. And considering the performance of SSJS, well worth using for traditional Domino on the web if you've got an 8.5.x server.

Name:David LeedyWebsite:http://notesin9.comComment

Great tip Chris! I'll be looking to try this.

Name:Tony WestwellComment

Wow, so few lines, the last time I saw anything like this it must have two pages, can't wait for session, yes I will be in Boston, If the ash cloud ever lifts. Super tip, will you be wearing a cape in Boston?

Name:Fredrik SjöströmComment

Very nice solution!

Name:Stephan H. WisselWebsite:http://www.wissel.net/Comment

Invest another line:

response.setHeader("Content-disposition", "attachment; filename=projects.xls");

That settles the file name.

Name:Stephan H. WisselWebsite:http://www.wissel.net/Comment and cut lines here:

output += "<tr>";
for (var x in viewEnt.getColumnValues()) {
output += "<td>" + x + "</td>"}
}
output += "</tr>";

:-) stw

Name:Tom CorriganComment

Unfortunately this technique only works on Windows (and perhaps only in IE - I'm not certain), therefore I don't think it can be considered production ready. A technique to output a proper excel spreadsheet is, of course, far more complicated.

Name:Chris TooheyWebsite:http://www.dominoguru.comComment @Paul:

Thanks! I like the simple things that make the big impressions on users.

@Leedy:

Go ahead, steal my code! Damn hack...

@Tom W:

There is a photo of my floating around somewhere wearing a table skirt as a cape during one of the Lotuspheres...

@Fredrik:

Thanks!

@Stephan:

1) Genius sir, my presentation (and demo app) are already updated!

2) Aside from trying to be verbose, you'll notice that the position of the columns are switched. I'm doing that both to match the project View to my XPage Repeat Control rendering, but also to show that you're not bound to the project View design/column placement. You're absolutely correct though, this chops down the overall lines.

In fact, if you really pushed it, you could get this whole solution out the door in 20 lines!

@Tom: Well, I'm not too sure about that. If a Mac or Linux build can't render a particular Content Type, then that's on them! Having only PCs at my disposal, I can absolutely tell you that this works across all of my browsers (Chrome, Firefox, and IE) as well as the Lotus Notes 8.5.1 Client. See, this technique simply delivers an xls file, it's what you do with that file that is client-specific. On my current laptop, for example, I'm running Symphony and not Excel... and this works perfectly!

Name:Nathan T. FreemanWebsite:http://nathan.lotus911.comComment If line count is your criteria, go for 16.

var viewNav:NotesViewNavigator = database.getView('projects').createViewNav();

var viewEnt:NotesViewEntry = viewNav.getFirst();

var output:string = "";

while (viewEnt != null) {

output += "<tr>";

for (var x in viewEnt.getColumnValues()) {

output += "<td>" + x + "</td>"

}

output += "</tr>";

viewEnt = viewNav.getNext(viewEnt);

}

facesContext.getExternalContext().getResponse().setContentType("application/vnd.ms-excel");

facesContext.getExternalContext().getResponse().setHeader("Content-disposition", "attachment; filename=projects.xls");

facesContext.getExternalContext().getResponse().setHeader("Cache-Control", "no-cache");

facesContext.getResponseWriter().write("<table><thead><tr><td><b>Project</b></td><td><b>Estimate ( Hours )</b></td><td><b>Developer</b></td><td><b>Estimate ( Budget )</b></td></tr></thead>"+output+"</table>");

facesContext.getResponseWriter().endDocument();

Name:Filipe CaldeiraWebsite:htp://filipe.uni.googlepages.comComment Hey Chris,

Great technique, i want to tell you that i created agents with that html tags and change HTML header to specify Excel's MIME content type, to extract notes data to generate reports in excel/word but my main worry was to use something like you coded without having office produts installed in the servers!

But with someting else that i have tryed the OOXML(microsoft) or even POI java APIs(use the ooxml format) in lotus. So since IBM use the ODF format with symphony its a way to do it or maybe the OOXML format instead, how do you feel about that? i know i´m going out of your theme scope but its cheap/fast ways of doing reports, yes?

Best regards,

p.s. sorry for the written language, i tried not to commit errors.

Filipe

Name:Stephan H. WisselWebsite:http://www.wissel.net/Comment

For platform support it boils down to content-type. You could set it to application/x-vnd.oasis.opendocument.spreadsheet and see if OpenOffice, Symphony (or any other app you have that does ODF) loads that correctly. On Symphony 1.1 it wouldn't do that (there is an SPR for automatically run HTML import, not sure if it made it into 3.0)

Name:Nathan LarkinComment I am assuming this solution does not work for categorized views?

Name:Chris TooheyWebsite:http://www.dominoguru.com/Comment

@Nathan: Correct -- but one could argue that the move from UI-driven Views to backend Views means that you'll only have categorized Views in order to quickly get subsets of information, and not for direct UI-driven function (like exporting data).

Name:roopesh laxmanComment hi,
i'm a student.i tried to export the view to the excel in xpage. and i am getting automation object cannot be created error.can you pls send me one sample nsf for how to export to excel in xpages...

thanks in advance

roopesh :-)

Name:Chris TooheyWebsite:http://www.dominoguru.comComment @Roopesh: What version of Lotus Notes are you using? This code will only work with Lotus Notes Client 8.5.1 or greater.

Name:Fredrik NorlingWebsite:http://everythinglotus.blogspot.com/Comment How do I change the charset of the output data sent to the browser?

It seams like the data is always UTF-8 and I would like to return none double byte text in a file.

Name:LeeComment Relatively new to xPages so was wondering what the benefit of using an "xAgent" with SSJS for this view output technique is rather than using a traditional LS agent?

Name:Werner AndersComment Hi Chris,

This works well, except for one issue I've run into in I.E. After I click the 'Export' button I get a dialog to save / open the file. Whether I save the file, open it or even click Cancel, I then cannot click on any tabs, links, buttons on the underlying page. Even the 'Export' button itself does nothing at this point. If I wait about 20 seconds, then the tabs and buttons are functioning as expected once again. It's almost as if there is something hanging, due to the redirect to a page that is not rendered and the underlying page does not properly register my clicks. Even though my mouse changes to the hover icon over the tabs and I can see the URL for each tab. Any thoughts?

Much appreciated,

Werner

Name:MikeComment We have been using code like this for a while, works great, when all of a sudden it started crashing a certain machine, and now another as well.

var con:javax.faces.context.ExternalContext = facesContext.getExternalContext();
var response:com.ibm.xsp.webapp.XspHttpServletResponse = con.getResponse();
var writer = response.getWriter();

//without these 2 lines it displays no problem, with them it prompts for filename and then BOOM :-(
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment; filename=test.xls");

writer.write("<table><tbody><tr><td>HI!</td></tr></tbody>/table>");
facesContext.responseComplete();

Of course the real code is much more involved than that, but this is what it boils down to. First 1 user, now a 2nd. Tried reinstalling things, digging for clues through log files, different save locations, etc etc, no luck. Hope it doesn't spread to any further users!

Anybody out there seen behaviour like this, have any suggestions what to look for? If I get it figured out I will post some details on what to watch out for, try to save someone else some heartburn.

RSS 2.0:
Guru Tag: