SNTT : Export any Lotus Notes View to CSV or Excel, automagically

Here is a some very generic code which will work on any Lotus Notes View to print out the contents to a Comma Separated Value (CSV) file, or as an Excel File, with minimum formating, as a general export routines.

ViewCSVPrint.txt
ViewExcelPrint.txt

Import them to a manual agent or a shared action button, open a view and go.

The CVS export prompts you for a file Location, the Excel export (assuming you have MS Excel installed) opens a excel file (it makes it visible at the end) with the results. (you could easily modidy the Excel export to prompt for a – or just use a hard coded default – file path and name, and save to the file system.

A couple of things to note :

• The ViewCSVPrint routine in the CVS export does not use any UI objects so it could be called by a scheduled agent and run on the server, as long as you supply a valid View object (using a notesDatabase.GetView( viewName$ ) call) and a valid FileNamePath. By giving the file a “.csv” extension the file can be auto magically opened in excel, as well as processed by other programs. (CSV and XML files are the Lingua franca’s of computer systems.)
• The Excel export must run on a workstation, which must have Excel installed. It is possible to use Java (or other languages) libraries that create excel files on a sever, but I’m not going there. I’m using the LSX OLE libraries available and you can figure out more by looking at Microsoft’s own excel VBA help.
• In the Excel export I’m using an ugly hack in the intializeColumnArrayValues sub routine to map the column number to the excel column letter label. This limits it to 12 columns at presents, but would be trival to extend. Even better if I figured out some clever way to translate column # 17 to chr(”A”) + 14 – 1 = “n” or something. When I can marshal my brain cells I’ll do just that for a future SNTT.
• The CVS also runs very very fast for very large numbers of rows, compared to the Excel export. Seconds compared to minutes.
• Both exports works best with flat (non- categorized) views where all the columns have titles and without hidden columns. This is because I’m using a ViewEntryCollection which never contains categories. But, they will work ony any view.
• For the Excel export, I’m splitting and freezing the header row, and doing some fit to size on the data, but that’s it. You could do some more formatting by querying the columns and depending on the type (number or data) or formating (left alignment, number of decimals) then apply that to the excel data column.

Enjoy (technorati.com tags :Show-n-Tell+Thursday, SnTT)

Update : See also
SNTT : Export any Lotus Notes View to Excel from Domino Web, automagically

Update March 20th 2007 : Lotus Guru, aka Kevin Pettitt, borrowed the code here and worked it to Make Attractive Exports of Categorized Notes Views. That’a Boy Kev!