Export from Notes to Excel – 3 different ways

While reading the developerWorks forums, I have noted that some tasks seem to be common, and users often have issues with it. One very common is to export data from Notes/Domino to Excel. My guess is that some manager/executive want to get the data to manipulate and analyze it in Excel, which they know better, and where reporting can be done easier.

First of all, for anyone that want to do real integration with Excel (or Word, or any other office program), I would suggest to look at the presentations given by John Head at Lotusphere and other conferences in the past:
Lotusphere 2011 – JMP208
IamLUG 2009 – Integration and Coexistance
Lotusphere 2006 – BP309
Lotusphere 2005 – JMP108

But if you just need to export raw data to Excel, there are a couple easy ways to do that. You don’t need to automate Excel, something the programmers posting in the forums seem to frequently do. That just makes things more complicated, and if you run the code in a server agent, Microsoft Office have to be installed on the server itself. Different versions of Excel also cause different issues.

The different methods I use to export Notes data to Excel are as follows:

• Create a CSV file, which can be opened in Excel
• Create a HTML table in a file with .xls extension, which can be opened in Excel
• Create a HTML table and return it through a browser, with a content type set to open in Excel.

Depending on what the requirement is, I usually choose one of those method.

Method 1 – CSV file (local)

The easiest way to get data from Notes to Excel (or any other spreadsheet program) is to simply save the data as  comma-separated values (CSV). Use regular Lotusscript file operations and write the values to the file, encased in double quotes and separated by a comma. You need to build a string containing a complete line, as the Print statement adds a line break to the end automatically.

A CSV file could look like this:

FirstName,LastName,Department,StartYear
"Karl-Henry","Martinsson","IT","2002"
"John","Smith","Accounting","2009"

The first row is the header, with the field names listed. This helps on the receiving side, but is optional. Dates and numbers don’t need quotes around them, as long as they don’t contain commas. So make a habit to format any numbers without commas.

Method 2 – HTML file (local)

This method is very similar to the CSV method. The difference is that you create a table in HTML, matching the cells in Excel you want to create/populate. But instead of creating a file with the extension .htm or .html, you give it the extension .xls. Excel is intelligent enough to identify it as HTML and map the content correctly.

The biggest advantages of using a HTML file instead of CSV is that you get more control over the formatting. By using colspan and rowspan, you can merge cells together, for example in a header. You can also use markup to make cells bold or italic, or even set the text and background colors.

Method 3 – HTML (through browser)

This method is a variant of method 2. But instead of calling the agent inside the Notes client and creating a file somewhere in the file system, the agent is called through a URL. This means the Domino HTTP task must be enabled on the server. The URL can be called from inside the Notes client using the @URLOpen function, or the OpenURL method of the NotesUIWorkspace class.

Instead of writing to a file, use the Print statement to print to the browser. The first line printed must be the content type, so the browser realize what application to open the file in. For Excel, it looks like this:

Print "content-type:application/vnd.ms-excel"

It also works with (among other) “application/msexcel” and “application/xls”, but the one above is the official one, so I suggest using that.

After you print the content type, simply print the HTML table code to the browser. This will open the HTML in Excel after the agent is done. It’s that easy.