Exporting a lotus view to excel

In this post I explain how to export all the documents in a view of a lotus database to an excel spreadsheet using the library Apache POI.
It is a very simple example that exports the string for each column in the view for every document, but you can add other features such as the formatting of the cell contents, or other features of excel.

  1. download the last release of Apache POI
  2. create e new java script library and import as Archive the main jar file downloaded at the previous step, for example poi-3.8-20120326.jar
  3. create a new java agent called “Esport to Excel” with Trigger “Action menu selection” and Target “None”, import the script library of the previous step and add the following code
  4. replace [a full path to excel file] with the path to the excel file and [the name of the view you export] with the name of the view from which you will launch this agent

  5. open the view that you entered in the previous step and run the agent; you should see an excel spreadsheet with the values ​​of the documents in the view (tested on Windows 7 and Lotus 8.5.x)

Some considerations:

  • the path to the excel file and the name of the lotus view are hardcoded as “final String” but you can pass them as parameters to the java agent (see the post Passing information about UI objects to a java agent)
  • I have used the method getColumnValues of the class ViewEntry, that returns an object Vector containing the values of the columns but keep in mind that entry.getColumnValues().elementAt(…) returns null for the columns determined by UI functions (@IsExpandable, @DocNumber, etc.) or by a constant
  • in the case of icons entry.getColumnValues().elementAt(…) doesn’t copy the image but the corresponding numerical value and if it is a constant is not even shown as just written in the previous point
  • for columns of type response getColumnValues shows the value for the main documents too
  • in the case of hidden columns entry.getColumnValues().elementAt(…) returns a value that the user does not see in the lotus view and therefore does not expect to be exported to excel

Because of these considerations, the column values ​​are exported to excel cells only if they match the 2 “if”:

As already mentioned this is just a very simple example, in real environment you probably want to format excel (fonts, colors, borders, etc..) and you can use the classes and methods provided by Apache POI.
With the code statement entry.getColumnValues().elementAt(…).getClass().getName() (not present in the example) you can know if the object returned from entry.getColumnValues().elementAt(…) is a String, Double o DateTime and so you can perform formatting targeted for a certain type of data, for example, you can format all the dates for a given location.

One Reply to “Exporting a lotus view to excel”

  1. Hi ,
    I am new to java.. can you please help me in how to do steps 2 and 3 (import the script library of the previous step ) and my domino client version is 8.5.3

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.