Importing XLS/XLSX into Notes on Unix/Linux server

HCL Domino Domino Forum
1

Hi,

I need to import an XLS/XLSX spreadsheet via an agent, but the agent is running on an UNIX box, so the automation route to Excel is not an option. Am attempting to use the Apache POI libraries in Java but having no luck. Have attempted to connect the agent to the Java Debugger in Designer, but it can’t be reached so have resorted to writing small files to locate where the agent crashes. Below is a simple agent I’m trying out

import java.io.FileInputStream;

import java.io.PrintWriter;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import lotus.domino.*;

public class JavaAgent extends AgentBase {

public void NotesMain() {

PrintWriter errorWrite;

try {

Session session = getSession();

AgentContext agentContext = session.getAgentContext();

Database db=agentContext.getCurrentDatabase();

// (Your code goes here)

errorWrite= new PrintWriter( “d:\tmp\testOutput.txt”, “UTF-8”);

errorWrite.write(“Start”);

errorWrite.write("Database title is "+db.getTitle());

errorWrite.write(“About to open spreadsheet”);

errorWrite.close();

//InputStream inp = new FileInputStream(“C:\tmp\TestImportSheet.xlsx”);

// Workbook wb = WorkbookFactory.create(inp);

XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(“d:\tmp\TestImportSheet.xlsx”));

errorWrite=new PrintWriter( “d:\tmp\testOutput.txt”, “UTF-8”);

errorWrite.write(“Spreadsheet opened”);

Sheet sheet = wb.getSheetAt(0);

Row row = sheet.getRow(2);

String value=getCellValue(row,1);

// Write the output to a file

errorWrite.write(value);

errorWrite.close();

}

catch(Exception e) {

e.printStackTrace();

}

}

I’ve narrowed down the crash to this line

XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(“d:\tmp\TestImportSheet.xlsx”));

But have no idea what the error is as I can’t see any output. The file definitely exists.

If anyone has any alternatives or ideas, would appreciate any suggestions.

Thanks,

Cameron

2

Subject: Which version of the library?

Cameron,

I’ll give it a shot. What version of the library are you using?

3

Subject: The suggestions worked

Hi Chad,

Thanks for the links. I downgraded to version 3.9 recompiled the code, and it worked! The main cause is the version of Java, one the links says the latest version requires JVM 1.6.0_18 but Notes has 1.6.0. 3.9 requires 1.5.

Found the debugger so hit and miss - sometimes it loaded, sometimes it refused (am running on local). Also, found the debug configurations were not present after I closed Designer/Notes and reloaded.

Again, thanks for the links. Was getting very frustrated not to be able to debug and had no info. My Google searches didn’t find much (probably looking for the wrong thing).

Regards,

Cameron

4

Subject: Great! Glad it worked! <>

5

Subject: Couple of issues

The first issue I ran into with your code is this one: java.lang.ClassNotFoundException: org.apache.xmlbeans.XmlException - Stack Overflow http://stackoverflow.com/questions/23080945/java-lang-classnotfoundexception-org-apache-xmlbeans-xmlexception. After that, though, I ran into this: java - NoSuchMethodError in main thread while reading xlsx using apache poi - Stack Overflow http://stackoverflow.com/questions/26866398/nosuchmethoderror-in-main-thread-while-reading-xlsx-using-apache-poi. Have a look at those and see if they get you past these problems.

6

Subject: Going to try an earlier version

Hi Chad,

Thanks so much for the reply. Am going to try an earlier version as I downloaded 3.11, so will try 3.9. Any new features aren’t important, just need to read the values from the spreadsheet. Will update with any progress.

Regards,

Cameron