Using POI to Export Lotus Notes Data to Excel

Standard
As those who have been reading my previous posts have seen, I have been working on a project that involves exporting meta data from Lotus Notes documents to an excel sheet.  I’m using the CreateObject(“Excel.Application”) function to do this.  The problem is that the agents that do this export are scheduled and run on a server.  This means that excel has to be installed on that server for the process to work and most of the admins that I had to work with did not like the idea of having excel on a server they have to support. 

For this reason I started messing around with the apache project POI.  This project has pure java ports of file formats based on Microsoft’s OLE 2 Compound
Document Format.  So it has classes for creating excel spreadsheets without having excel installed on the server, which would make the admins happy.  So I created this simple class to create and write rows to an excel file.

package com.clr.excel;

import java.io.FileOutputStream;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ExcelWriter {
    private int curRow;
    private HSSFWorkbook curWB;
    private HSSFSheet curSheet;

    public boolean writeExcelHeader(String[] rowValues) {
        try {
            this.curRow = 0;
            this.curWB = new HSSFWorkbook();
            this.curSheet = this.curWB.createSheet(“Sheet1”);

            //This will write the header row
            return(this.writeToExcel(rowValues));
        } catch(Exception ex) {
            ex.printStackTrace();
            return(false);   
        }
       
    }

    public boolean writeToExcel(String[] rowValues) {

        try {

            HSSFRow row = this.curSheet.createRow(this.curRow);

            for(int i = 0; i < rowValues.length; i++) {
                row.createCell(i).setCellValue(new HSSFRichTextString(rowValues[i]));
            }

            this.curRow++;

        } catch(Exception ex) {
            ex.printStackTrace();
            return(false);
        }

        return(true);
    }

    public boolean writeExcelFile(String fileName) {
        try {
            //Auto fit content
            for(int i = 0; i < 20; i++) {
                this.curSheet.autoSizeColumn((short)i);
            }

            // Write the output to a file
            FileOutputStream fileOut = new FileOutputStream(fileName);
            this.curWB.write(fileOut);
            fileOut.close();
        } catch(Exception ex) {
            ex.printStackTrace();
            return(false);
        }
       
        return(true);

    }

}

Again I used LS2J to use this class from my lotusScript agent.

These declarations are needed to use LS2J

Uselsx “*javacon”
Use “ExcelWriter”

Agent to create excel sheet.

Sub Initialize
    On Error Goto ErrorHandler
    Dim session As New NotesSession
    Dim db As NotesDatabase
    Dim view As NotesView
    Dim doc As NotesDocument
    Dim js As JAVASESSION
    Dim xlWriterClass As JAVACLASS
    Dim xlWriterObject As JavaObject
    Dim rowValues(1 To 3) As String
    Dim folderName As String
   
    Print “Started Running CreateExcel”
   
    folderName = “C:Temp”
   
    ‘Initialize column headers
    rowValues(1) = “Title”
    rowValues(2) = “Date”
    rowValues(3) = “Name”
   
    ‘Initialize Excel Writer class
    Set js = New JAVASESSION
    Set xlWriterClass = js.GetClass(“com.clr.excel.ExcelWriter”)
    Set xlWriterObject = xlWriterClass.CreateObject
    If(Not(xlWriterObject.writeExcelHeader(rowValues)))Then
        Print “Error creating excel sheet!”
        Exit Sub       
    End If
   
    Set db = session.CurrentDatabase
    Set view = db.GetView(“MainView”)
   
    Set doc = view.GetFirstDocument
    Do While(Not(doc Is Nothing))
       
        rowValues(1) = doc.Title(0)
        rowValues(2) = doc.selectedDate(0)
        rowValues(3) = doc.Name(0)
       
        ‘Write metadata
        If(Not(xlWriterObject.writeToExcel(rowValues)))Then
            Print “Could not write to excel!”
        End If
       
        Set doc = view.GetNextDocument(doc)
    Loop
   
Finished:
    xlWriterObject.writeExcelFile(folderName & “workbook.xls”)
   
    Exit Sub
   
ErrorHandler:
    Print “Error ” & Error & ” on line: ” & Erl
   
    Resume Finished
   
End Sub

Enjoy the code here.

6 thoughts on “Using POI to Export Lotus Notes Data to Excel

  1. Hi,

    I enjoy your code its really works thank you.
    All do i´m triyng to do the same with word documents but with no success!!! are doing the same? Will you try it out?

    Regards

    Filipe

  2. Hi Caldeira,

    Are you trying to export data from Lotus Notes to a Word document? Are you getting any specific error?

    I will create a simple example of doing this with the Apache POI Project and post soon.

  3. Hi Carlos, Thank you for your understanding and answer, i´m sorry i lost your page so just today i could see it.

    Yes i have errors and i can post some of my code that i worked to get the word file, its dificul since i dont program a lot in java just lotuscript, javascript a litle to.
    well i onnly can read and create a file .doc/.docx but mt error was to write inside it some string/phrase.
    I will post soon, i leave a rest a while because now i´m learning were i work smallworld magik programming its diferrent programming like c++ you know gis?

    well for now i will return to the POI Vs word.

    Thank you beste regards

  4. import org.apache.poi.hwpf.usermodel.*;
    import org.apache.poi.hssf.model.*;
    import org.apache.poi.hssf.*;

    import org.apache.poi.hwpf.HWPFDocument;

    import org.apache.poi.hwpf.usermodel.Range;
    import org.apache.poi.hwpf.usermodel.Paragraph;
    import org.apache.poi.hwpf.usermodel.Table;
    import org.apache.poi.hwpf.usermodel.Section;

    import java.util.Map;
    import java.util.HashMap;
    import java.util.Calendar;
    import java.io.FileOutputStream;
    import java.text.SimpleDateFormat;
    import java.io.FileOutputStream;

    import lotus.domino.*;
    import java.util.*;
    import java.text.*;
    import java.io.*;
    import java.lang.*;
    import java.util.Date;
    import java.io.PrintWriter;

    public class Word
    {
    /*
    openDocument
    openParagraph
    openBody
    openSection
    openTitle
    openSource
    writePlainText
    closedocument…

    */
    private int curRow;
    // private POIDocument(dir, filesystem);
    // private HSSFWorkbook curWB;
    // private HSSFSheet curSheet;
    // PrintWriter pw = getAgentOutput(); +"
    " // erro
    /* public boolean Range(int start, int end, HWPFDocument doc)
    {
    try {

    } catch(Exception ex)
    {
    ex.printStackTrace();
    return(false);
    }
    } */

    public boolean writeWordHeader(String[] rowValues)
    {
    try {
    this.curRow = 0;
    // this.curWB = new HSSFWorkbook();
    // this.curSheet = this.curWB.createSheet("Sheet1");

    //This will write the header row
    System.out.println("estou no header n");
    return(this.writeToWord(rowValues));
    } catch(Exception ex) {
    ex.printStackTrace();
    return(false);
    }

    }
    public boolean writeToWord(String[] rowValues) {
    System.out.println("valor da linha: ");
    System.out.println("valor: " + rowValues);
    try {

    // HSSFRow row = this.curSheet.createRow(this.curRow);

    for(int i = 0; i < rowValues.length; i++) {
    // row.createCell(i).setCellValue(new HSSFRichTextString(rowValues[i]));
    System.out.println( "linha: " + rowValues[i]);
    }

    // this.curRow++;

    } catch(Exception ex) {
    ex.printStackTrace();
    return(false);
    }

    return(true);
    }
    public boolean writeWordFile(String fileName)
    {
    System.out.println("filename: " + fileName);
    try {
    //Auto fit content

    for(int i = 0; i < 20; i++) {
    // this.curSheet.autoSizeColumn((short)i);
    }

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream(fileName);
    System.out.println("nome: " + fileOut);
    // this.curWB.write(fileOut);
    fileOut.close();
    } catch(Exception ex) {
    ex.printStackTrace();
    return(false);
    }

    return(true);

    }
    }

  5. Anonymous

    HI Carlose,

    I need to move all our reports to this Apache POI format.

    Can I have some smaple code for editing an exisitng excel file, also adding the new worksheet in excel etc.

    Thans in advance,
    Annie

Comments are closed.