Moving on with Excel Export part 2 : XPages Export

In my last post I wrote about how to in some simple steps generate an automated export of an view to excel. In this post I will show you how to fine grain this using the excel component instead.

Start by creating a blank excel file note what the sheet you want to place data on is called usually Sheet1 if you have an english version of Excel. Save and import it as a file resource in the database, name it export.xlsx

Now create a new XPage add a button and the Excel component POI Workbook

 

 

 

 

 

This is how the result should look

screen188

 

 

 

 

 

Now add a simple action to the Export button

screen189

 

 

 

 

 

 

 

 

 

 

 

Select the Excel Component and go to all properties and create a new resource template

screen190

In the resource template specify the name of your excel template

screen191

 

 

 

The template is now ready to be used and we can now move over to setup the actual data that should be added to the spreadsheet. create a new dominoViewSource

screen194

Specify your view name in my case Images

screen195

Look at your view and note down the column headers, I have 4 columns with the names Topics, Size, Created and last a calculated column called Ext

screen198

Add four columns in your component

screen196

 

 

 

On each column add the Column number in the Excel sheet where the data should be placed and the name of the column.

screen201

 

 

 

 

In my case I ended up with this

screen200And now to we need to add the name of the exportfile and the easiest thing to forget the sheet name where you want to place the export data, Sheet1 in my case.

screen202

Now you view export is a bit more advance and you can specify what to export and in what column.

I will continue the series and show you more about the power of POI for XPages, stay tuned.

2016 Update that I got from a developer Lisa Gerlich that you might run into
I was having problems exporting more than 1000 documents using the
poi4Xpages excel workbook widget. I ended up posting on OpenNTF and
got an answer.
There is a property under the datasource property called maxRow. It
defaults to 1000. You can set the value as high you dare.

Leave a comment ?

20 Comments.

  1. Steven Podrouzek

    I do like the export facility, but it seems it cannot export specific document information. For example, if I have a start date and a end date, specific employee, or region, I cannot get that working. Am I just being stupid Fredrik?

  2. Fredrik Norling

    Hi Steven,
    My upcoming posts will show you how to do this 😛
    Stay tuned

  3. Steven Podrouzek

    I thought I was wrong! After its a basic export requirement by companies. Waiting in anticipation!

  4. Steven Podrouzek

    By the way, your mobile site is probably the best I have ever seen and used. Brilliant!

  5. D.R. (@drTheFirst)

    Hi! E keep getting a NullPointerException error..

    Here is the first line of stack trace.. Any tips on where to look?

    Error : Error during Workbookgeneration
    POI LIB : 1.2.6.201312211419
    StackTrace:
    java.lang.NullPointerException
    at org.apache.poi.xssf.usermodel.XSSFWorkbook.getSheet(XSSFWorkbook.java:818)

  6. D.R. (@drTheFirst)

    Will check my java permision policies.. maybe that is the problem.

    • Fredrik Norling

      Hi try adding some debug notes.ini params to check what is generating your problem.
      I don’t think you need to change anything in the java.policy file.

      Try adding these debug params to notes.ini on your server and try to export again
      DEBUG_POI=6
      DEBUG_POI_FOP=6

      • D.R. (@drTheFirst)

        This is what i get in log and it dosent really give any clue:

        30.03.2015 14:47:54 HTTP JVM: POI LOG-LEVEL is set to: 6 / 6
        30.03.2015 14:47:54 HTTP JVM: POI PDF LOG-LEVEL is set to: / 1
        30.03.2015 14:47:54 HTTP JVM: POI FOP LOG-LEVEL is set to: 6 / 6
        30.03.2015 14:47:54 HTTP JVM: BOLJ FINO: First getting the File (biz.webgate.dominoext.poi.component.kernel.WorkbookProcessor)
        30.03.2015 14:47:54 HTTP JVM: INFO: Getting NoteCollection (biz.webgate.dominoext.poi.component.data.ResourceTemplateSource)
        30.03.2015 14:47:54 HTTP JVM: INFO: Select only MiscFormaElements (biz.webgate.dominoext.poi.component.data.ResourceTemplateSource)
        30.03.2015 14:47:54 HTTP JVM: INFO: buildColleciton (biz.webgate.dominoext.poi.component.data.ResourceTemplateSource)
        30.03.2015 14:47:55 HTTP JVM: INFO: browseColllection (biz.webgate.dominoext.poi.component.data.ResourceTemplateSource)
        30.03.2015 14:47:55 HTTP JVM: INFO: DONE (biz.webgate.dominoext.poi.component.data.ResourceTemplateSource)
        30.03.2015 14:47:55 HTTP JVM: INFO: No recycle -> sesSigner.currentDB (biz.webgate.dominoext.poi.util.DatabaseProvider)
        30.03.2015 14:47:55 HTTP JVM: BOLJ FINO: Push the Result to the HttpServlet (biz.webgate.dominoext.poi.component.kernel.WorkbookProcessor)
        30.03.2015 14:47:55 HTTP JVM: BOLJ FINO: Proccess Spread Sheet (biz.webgate.dominoext.poi.component.kernel.WorkbookProcessor)

  7. D.R. (@drTheFirst)

    I have another problem :/ sorry for the bugging. I am trying to fetch data from another database now.. In one of them it says “DB is not currentDB”. Is that the problem or just a log? And then there is an exception “Object has been removed or recycled”.
    A view is flat. Database path has a value of “SERVERNAME/SRVDOM!!database_filename.nsf”. Database source should be fine, since I get a xlsx template from it, as well.

    Here are the first few lines of domino log, which I think are relevant.
    31.03.2015 08:31:03 HTTP JVM: BOLJ FINO: First getting the File (biz.webgate.dominoext.poi.component.kernel.WorkbookProcessor)
    31.03.2015 08:31:04 HTTP JVM: INFO: Recycle -> DB is not currentDB (biz.webgate.dominoext.poi.util.DatabaseProvider)
    31.03.2015 08:31:04 HTTP JVM: BOLJ FINO: Push the Result to the HttpServlet (biz.webgate.dominoext.poi.component.kernel.WorkbookProcessor)
    31.03.2015 08:31:04 HTTP JVM: BOLJ FINO: Proccess Spread Sheet (biz.webgate.dominoext.poi.component.kernel.WorkbookProcessor)
    31.03.2015 08:31:04 HTTP JVM: BOLJ FINO: Proccess Cell Values (biz.webgate.dominoext.poi.component.kernel.WorkbookProcessor)
    31.03.2015 08:31:04 HTTP JVM: BOLJ FINO: Proccess ExportDefinition (biz.webgate.dominoext.poi.component.kernel.WorkbookProcessor)
    31.03.2015 08:31:04 HTTP JVM: BOLJ FINO: Proccess Export Row (biz.webgate.dominoext.poi.component.kernel.workbook.EmbeddedDataSourceExportProcessor)
    31.03.2015 08:31:04 HTTP JVM: BOLJ FINO: Load Access Source (biz.webgate.dominoext.poi.component.kernel.workbook.EmbeddedDataSourceExportProcessor)
    31.03.2015 08:31:05 HTTP JVM: Build collection with Key: (0)
    31.03.2015 08:31:05 HTTP JVM: INFO: Recycle -> DB is not currentDB (biz.webgate.dominoext.poi.util.DatabaseProvider)
    31.03.2015 08:31:05 HTTP JVM: BOLJ FINO: Start Processing Cells (biz.webgate.dominoext.poi.component.kernel.workbook.EmbeddedDataSourceExportProcessor)
    31.03.2015 08:31:05 HTTP JVM: NotesException: Object has been removed or recycled
    31.03.2015 08:31:05 HTTP JVM: at lotus.domino.local.NotesBase.CheckObject(Unknown Source)
    31.03.2015 08:31:05 HTTP JVM: at lotus.domino.local.ViewEntryCollection.getFirstEntry(Unknown Source)

    • Fredrik Norling

      The entry is what I believe some info.
      I also believe that your database is null or not opened yet, and if you do the report from a local dB it works?

      • D.R. (@drTheFirst)

        Localy it works.. I tried to add smaller demo database as source and a view with just a few documents to see if that is the trick.. but no luck.. Any idea what should I check to see why is database not oppening.

        • Fredrik Norling

          Normally if you want to access another server thru code you need to add that server to the trusted server section in the server document. Is that in place?

          • D.R. (@drTheFirst)

            Yes.. I’ve accidentaly posted a new tread down below. Databases are already used as datasources for my Web app.. so the ACL’s are in order.. I tried to check if a database is accesed by setting a wrong path and the plugin returns a good log as database has not been opened yet. So it’s not a db path problem.. Databases are on the same server so trust is not an issue.
            p.s. feel free to delete my obsolete comments or shorten them.

          • D.R. (@drTheFirst)

            Ive tried everything I can think of.. I’ve completely replicated the POI example, just added the source from another database… I think maybe the key is in the domino log line where it says: HTTP JVM: INFO: Recycle -> DB is not currentDB (biz.webgate.dominoext.poi.util.DatabaseProvider)
            Maybe here it wants to work with current database instead of the database provided and there for view is not found and also an entry is null.

          • Fredrik Norling

            I think you should send a ping to @guedeWebGate on twitter who created the plugin perhaps he can give you some pointers

  8. D.R. (@drTheFirst)

    p.s. Databases are already used as datasources for my Web app.. so the ACL’s are in order.. I tried to check if a database is accesed by setting a wrong path and the plugin returns a good log as database has not been opened yet. So it’s not a db path problem.. Probably it does not initialize it fast enough or something, as you’ve mentioned.. Will work some more on it, to try to figure out what the problem is.

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

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