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
Now add a simple action to the Export button
Select the Excel Component and go to all properties and create a new resource template
In the resource template specify the name of your excel template
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
Specify your view name in my case Images
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
Add four columns in your component
On each column add the Column number in the Excel sheet where the data should be placed and the name of the column.
In my case I ended up with this
And 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.
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.
Very nice 🙂
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?
Hi Steven,
My upcoming posts will show you how to do this 😛
Stay tuned
I thought I was wrong! After its a basic export requirement by companies. Waiting in anticipation!
By the way, your mobile site is probably the best I have ever seen and used. Brilliant!
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)
Will check my java permision policies.. maybe that is the problem.
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
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)
It looks OK, it seams like your data might be null.
🙁 😳 Actually what was missing was the poi\spreadsheets\name property. Where you have a value “Sheet1”
That one is really needed, great that you found the problem.
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)
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?
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.
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?
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.
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.
I think you should send a ping to @guedeWebGate on twitter who created the plugin perhaps he can give you some pointers
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.