Exporting to Excel via scheduled agent


In one of my projects I had task to do a excel export. Nothing new about that other than it had to happen ones a month.
 
In the beginning , its was plan generic export script , worked great manually. Then when it was schedule it gave an error “can´t find Object”. And can´t use xlapp.save. method  After some time of testing and trying we found out that this could not be done on Domino server that was running as a services. Needed to run as an application. Anyone has any idea why that is ? 
Advertisements

13 thoughts on “Exporting to Excel via scheduled agent

  1. The domino server runs under the system account which does not have access to the file system. If this is a windows box I have code that should allow this

  2. Do have Excel installed on the server? You cannot call the xlapp functions without having the software installed on the machine. An alternate approach to exporting via a scheduled agent is to build an html or delimited text file and give it an .xls extension.

  3. I am thinking along the same lines as Kevin. “Can’t find object” usually means the OLE call to start Excel has failed.

    You must have Excel installed on the server to generate an Excel file. However, I have managed to do it using MSOXML. But your users need to have Excel 2003 in order to view it.

    It’s pretty easy, just save a test file in XML format (it’s a file type option under “Save As”), and butcher the XML as you need.

    Ofcourse the agent still needs to have full access to the server to write a file, but it’s just text…. I use it alot to avoid installing Excel on the server (which is not recommended because it will eventually crash the server)

    http://office.microsoft.com/en-us/excel/ha011019641033.aspx

  4. i would suggest you have a close look at Apache POI library. It allows you to read/write MS Office formats in Java. You can import the library into a Domino Java library and it would run on any server.
    What I typically do:
    – Have a Java class that does the outwards work but doesn’t use any of the Domino Java stuff
    – Have a LS Wrapper class around it
    — use LS to do the Domino work

  5. If you’re not doing fancy formatting with your excel, you can always dump out the text in HTML format and then save the file with a .XLS extension.

  6. Actually *MUCH LESS* complicated than Excel OLE. Installing a desktop application on a server is never a good idea — someone decides to move to AIX or Linux -> your code breaks. You move from 32Bit to 64Bit -> Your code breaks.

    Syntax is here: http://poi.apache.org/spreadsheet/quick-guide.html

    You write:
    Workbook wb = new HSSFWorkbook();
    FileOutputStream fileOut = new FileOutputStream(“workbook.xls”);
    wb.write(fileOut);
    fileOut.close();
    and that is all to have a new (empty) Excel file.
    No Excel install, no messing in the Registry, no platform headache

    The wrapping into a class is standard OO… and the LS — well mostly you are comfortable with LotusScript, so you don’t want to throw this away.

    The LS object could look like this:

    Public Class workbook
    sub save(FileName as String)

    end sub

    sub addCell(row as integer, col as integer, value as variant)
    ….
    end sub

    sub send(receipients as Variant)
    end sub
    end class

    Inside you would create the instance and either save it or drectly send it (using in-Memory MIME) without hitting the disk.

    Beats OLE any time.

    • Hello Stephan,

      Could you elaborate on the in-Memory MIME please. A little bit of code perhaps that shows how it can be done.

      Thanks,
      Stefaan

  7. I solved this by making a csv file from the view and send it by email to user . thanks for your advice

  8. import java.io.File;
    import java.util.Vector;
    import jxl.Workbook;
    import jxl.format.Colour;
    import jxl.write.Label;
    import jxl.write.WritableCellFormat;
    import jxl.write.WritableSheet;
    import jxl.write.WritableWorkbook;
    import lotus.domino.*;
    import java.util.Date;

    public class JavaAgent extends AgentBase {

    //Variables de la clase
    private static WritableWorkbook workbook;
    private static WritableSheet sheet;
    private static String sFile;
    private static int iFila = 1;
    private static int iCol = 1;
    private static Session session;
    private static Database dbActual;
    private static JMessageBox Mess;
    public void NotesMain() {
    try {
    Mensaje = new JMessageBox();
    Mensaje.simpleMessageBox(“Start job.”, “Export to Excel”);

    SalidaLogSistema(“Start process”);
    sFile = “c:\\FileExcel.xls”;
    workbook = Workbook.createWorkbook(new File(sFile));
    sheet = workbook.createSheet(“Stock”, 0);

    SalidaLogSistema(“Asignacion variables notes”);
    session = getSession();
    dbActual = session.getCurrentDatabase();

    View vStock = dbActual.getView(“View1”);
    ViewNavigator nav = vStock.createViewNav();

    ViewEntry tmpentry;
    ViewEntry entry = nav.getFirst();

    SalidaLogSistema(“start process”);
    while (entry != null) {
    Vector aValues = entry.getColumnValues();
    if (entry.isCategory()){
    iCol=entry.getIndentLevel();
    SalidaLogSistema(“Cat-“+iCol+”:”+aValues.elementAt(iCol).toString());
    Label colsLabel = new Label(iCol,iFila,aValues.elementAt(iCol).toString());
    sheet.addCell(colsLabel);
    iCol++;
    }
    else if (entry.isDocument())
    {
    SalidaLogSistema(“Document”+iFila);
    //Iterar el array y escribir los valores en las columnas
    int iColDoc = iCol;
    for (int i=iColDoc;i<aValues.size();i++){
    if (aValues.elementAt(i) != null) {
    Label colsLabel = new Label(i,iFila,aValues.elementAt(i).toString());
    sheet.addCell(colsLabel);
    iColDoc++;
    }
    }
    }
    tmpentry = nav.getNext();
    entry.recycle();
    entry = tmpentry;
    iFila++;
    }
    SalidaLogSistema("Close excel");
    workbook.write();
    workbook.close();
    SalidaLogSistema("Send email");
    EnviarCorreoExcel();
    SalidaLogSistema("End process");

    Mess.simpleMessageBox("Check your email.", "Export to Excel");
    }
    catch(Exception e) {
    e.printStackTrace();
    }
    }

    public void SalidaLogSistema(String sTexto){
    Date fecha = new Date();
    System.out.println (fecha +" "+ sTexto);
    }

    public void EnviarCorreoExcel(){
    try{
    Document memo = dbActual.createDocument();
    memo.appendItemValue("Form", "Memo");
    memo.appendItemValue("Subject", "File Excel");
    RichTextItem rti = memo.createRichTextItem("Body");
    rti.appendText("Added your excel file:");
    rti.addNewLine(2);
    rti.embedObject(EmbeddedObject.EMBED_ATTACHMENT,null, sFichero, "List");
    memo.send(true, session.getUserName());
    }catch(NotesException e){
    System.out.println(e.id + " " + e.text);
    e.printStackTrace();
    }
    }

    }

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s