Symphony API - Export Notes View to Spreadsheet

Abstract

ShowTable of Contents

HideTable of Contents

1 Download link

The sample application demonstrated in this article is a sidebar add-on that reads the selected entries from an open Notes view and exports the view data to a new Symphony spreadsheet using the Symphony Add-on API of XPages2Eclipse.

The sidebar add-on displays a small XPage in the sidebar of the Lotus Notes Client. The sidebar component is visible while the sample database is open, even when another database is focused. This is implemented by using a Composite Application that consists of two components: the main display area of the sample database and the always visible sidebar component.

Sidebar Panel for View Export to Spreadsheet

The sidebar component consists of a single button. In the onClick event, we open a connection to XPages2Eclipse and use the method getLastActiveParts() of the PartService in the PlatformUI API to get information about the active workbench part.

Afterwards, we try to convert this part into a NotesUIView object by leveraging the NotesUI API. If the user is actually looking at a Notes view, we then start the export process. The export progress is visualized by a Monitoring Job of the Job API, so that the user can see the current export progress in a progress dialog and is able to cancel the export operation.

var conn=X2E.createConnection();
var pUI=com.x2e.PlatformUIAPI.getUI(conn);
var wb=pUI.getWorkbench();
var activeWindow=wb.getActiveWorkbenchWindow();
var partService=activeWindow.getPartService();
//get a handle on the workbench part that was active right before
//the user clicked into this sidebar panel
var activeParts=partService.getLastActiveParts();
//element 0 of the list contains the sidebar panel's part
var partRef=activeParts.get(1);
var part=partRef.getPart(false);
if (!part)
    return;
//use NotesUIWorkspace of UI API to convert the part into a NotesUIView
var notesUITools=com.x2e.NotesUIAPI.getTools(conn);
var ws=notesUITools.getNotesUIWorkspace();
var uiview=ws.getUIView(part);
if (!uiview) {
    //no ui view found
    var mbTools=pUI.getMessageBoxTools();
    mbTools.showMessageBox(1+32 /* ERROR+OK */, "No Notes view",
       "Unable to locate the Notes view to export the data!");
    return;
}
//prepare the execution of the export code:
//progress will be visualized by using a Monitoring Job
//(code is running in XPages context, the Job is only used
//for progress visualization)
var exportCallback=function(progMon) {
    exportToSymphony(progMon, uiview);
}
Jobs.syncExec("Exporting view data", exportCallback);

The export code, that is transferring the view data to Symphony, is stored in the Script Library "SymphonyExport":

//the method creates a blank Symphony spreadsheet and copies
//the content of the specified uiView into the spreadsheet cells
function exportToSymphony(progMon, uiView) {
    var conn=X2E.createConnection();
    var pUI=com.x2e.PlatformUIAPI.getUI(conn);
    var mbTools=pUI.getMessageBoxTools();
    
    var symphony=com.x2e.SymphonyAPI.getApplication(conn);
    if (!symphony) {
        //no Symphony API found; may be the case when the
        //user does not have Symphony 3 installed, because
        //the XPages2Eclipse Symphony add-on requires version 3
        mbTools.showMessageBox(1+32 /* ERROR+OK */, "No Symphony API",
           "The XPages2Eclipse Symphony API could not be found. Is Symphony 3 installed?");
        return;
    }
    //create new empty spreadsheet
    var spreadsheets=symphony.getSpreadsheets();
    var ssheet=spreadsheets.addSpreadsheet("", false, true);
    var sheet=ssheet.getActiveSheet();
    
    //read column titles from view
    var columns=uiView.getColumns();
    var outCol=1;
    for (var i=0; i<columns.size(); i++) {
        var currCol=columns.get(i);
        if (!currCol.isHidden()) {
            var range=sheet.cells(1, outCol);
            range.setText(currCol.getName());
            outCol++;
        }
    }
    
    //opening the entry collection may take some time; we
    //display a flashing progress bar
    progMon.beginTask("Accessing view entries", -1);
    var entryCol=uiView.getActionableEntries();
    entryCol.openSync();
    
    //now we know the exact number of view entries
    var colSize=entryCol.size();
    progMon.beginTask("Exporting view data", colSize);
    var entryIt=entryCol.iterator();
    var outRow=2;
    
    //process all entries
    while (entryIt.hasNext()) {
        //check if the user has canceled the job
        if (progMon.isCanceled())
            break;
            
        var currEntry=entryIt.next();
        
        //copy column values of current entry
        outCol=1;
        for (var i=0; i<columns.size(); i++) {
            var col=columns.get(i);
            if (!col.isHidden()) {
                var range=sheet.cells(outRow, outCol);
                range.setText(currEntry.getColumnValueString(i));
                outCol++;
            }
        }
        
        outRow++;
        progMon.worked(1);
    }
    //we're done
    progMon.done();
}

As you can see in the code snippet, we first export the titles of the visible view columns, then retrieve an Iterator of the selected view entries (called "actionable entries" in the Notes UI API) and output all data row by row to Symphony.

Please note that the column and row indices of Symphony start by 1, otherwise an exception is thrown.
After each row is copied over, we call the ProgressMonitor method worked(1) to inform the Monitoring Job that 1 work unit of entryCol.size() total units has been processed.

Here is a sample export for selected emails in the inbox folder: