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. 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

import java.io.FileOutputStream;
import java.util.Vector;

import lotus.domino.AgentBase;
import lotus.domino.AgentContext;
import lotus.domino.Database;
import lotus.domino.Session;
import lotus.domino.View;
import lotus.domino.ViewColumn;
import lotus.domino.ViewEntry;
import lotus.domino.ViewEntryCollection;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class JavaAgent extends AgentBase {

	public void NotesMain() {

		try {
			Session session = getSession();
			AgentContext agentContext = session.getAgentContext();

			// (Your code goes here)

			final String fileName = "[a full path to excel file]"; // for example: final String fileName = "c:\\export.xls"
			final String viewName = "[the name of the view you export]";

			Database db = session.getCurrentDatabase();
			View view = db.getView("Export");
			Vector columns = view.getColumns();
			ViewEntryCollection vec = view.getAllEntries();

			FileOutputStream fileOut = new FileOutputStream(fileName);
			HSSFWorkbook workbook = new HSSFWorkbook();
			HSSFSheet spreadsheet = workbook.createSheet(viewName);
			int rowCounter = 0;

			ViewEntry entry = vec.getFirstEntry();
			while (entry != null) {
				if (entry.isDocument()) {
					HSSFRow row = spreadsheet.createRow(rowCounter++);
					int colCounter = 0;
					Vector columnValues = entry.getColumnValues();
					for (int i = 0; i < columnValues.size(); i++) {
						if (columnValues.elementAt(i) != null) {
							ViewColumn vc = (ViewColumn) columns.elementAt(i);
							if (!vc.isIcon() && !vc.isHidden()
									&& !vc.isResponse()) {
								HSSFCell cell = row.createCell(colCounter++);
								cell.setCellValue(columnValues.elementAt(i)
										.toString());
							}
						}
					}
				}
				entry = vec.getNextEntry(entry);
			}

			workbook.write(fileOut);
			fileOut.flush();
			fileOut.close();

			Runtime.getRuntime().exec(
					"rundll32 SHELL32.DLL,ShellExec_RunDLL \"" + fileName
							+ "\"");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

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

1. 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”:

...
if (columnValues.elementAt(i) != null) {
	...
	if (!vc.isIcon() && !vc.isHidden()
			&& !vc.isResponse()) {
...

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.