Moving on with Excel Export part 4 : XPages Export

The recent parts of this series has shown you how to create excel exports from a view and also changing the layout of the reports with headers and logos. This post will show you how to filter the data in your excel reports both using keys and fulltext searches. Let’s get started.

To get some filtered export we add an combobox into our XPage and in this case to show both a Export with a key and search we have two buttons for the different export options.

screen237

 

 

 

 

 

 

The code behind the combobox is a standard lookup fro the value data

screen235

 

 

 

 

 

And storing the information within a viewScope variable ExportData

screen233

 

 

 

 

 

 

 

 

The setup behind the buttons is the same as we done in the previous posts but we add a scriptblock before doing the actual export

screen231

 

 

 

 

 

 

 

If we look at the simple formula for a key based export.

screen232

 

 

 

 

 

 

 

 

 

 

 

 

We clear the ExportSearch viewScope var I’ll explain this later and assign the ExportData var into the ExportKey var. The view we are working against is an ordinary view with a categorized first column.

screen236

 

 

 

 

 

 

What you need is assigning the key param with the viewScope.ExportKey. In this case I’ve also assigned the search param. But in an ordinary case you would probably assign only one of them. Depending on if you want to get all documents by category or by Fulltext search.

screen238

 

 

 

 

 

 

The code behind the search export button is this

screen230We place a standard FT search formula inside the ExportSearch viewScope variable. Selecting all documents that the field Region has the value stored in the ExportData viewScope variable.

So exporting to excel with user customized data has never been easier. 

Apache POI for XPages has more options up it sleave so this series will continue so stay tuned in.

Moving on with Excel Export part 3 : XPages Export

Making you report look a bit nicer with some formatting.

Excel reporting from views can be so dull and boring the XPages for POI can give you more options in this matter and help you create some design to your report.

1. Customizing your template

Because POI is using a copy of you template you can customize your temple with logos and predefined information.

screen210

 

 

 

 

 

We need to import this new template and change the startRow and template name

screen213

In this case we start at row 12 below the logo and we assign the new template with the logo that we created. Note that the data will be placed on the row below the one you specified, don’t know why but good to know.

screen214

 

 

 

 

 

 

 

 

 

And the Export will be a combined with the logo from the template and the data from the Domino view.

But we want more

2. Setting up a header for you data

This can be done in two ways 1 directly in your template but that isn’t good in if you want to add a column that you need to change the template or if you want to use the template for multiple exports.

So what we do is that we add columns to our Export above the data, we need 4 columns for this example.

screen215

We click on add on cellValues and add 4 items of cellValue and as you can see we could also add data to Excel bookmarks. Could be handy if you have a multi sheet excel file.

screen216

We specify the columnNumber, rowNumber and because we know the header value we write it but we could actually compute the value also. But a header in plain text is quite dull so we click on cellStyle, scroll down and set the property to get bold on the font and a bigger size.

screen218

Your Excel export with a custom logo and a styled header is now ready. There is lots of stuff to cover with POI and application integration so this series will continue, the more I dig into POI for XPages the more I love it.

screen219

 

 

 

 

 

 

 

 

 

Part 1: http://www.xpagedeveloper.com/2015/if-your-user-want-reports-in-excel-you-should-read-this

Part 2: http://www.xpagedeveloper.com/2015/moving-on-with-excel-export-part-2

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.

If your user want reports in Excel you should read this

XPages did get a great addon more than a year ago to be more exact 13:th of September 2013. It was when Christian Güdemann uploaded the first version of POI for XPages. Apache POI is a rather old apache project, 13 years to be exact. And there have even been a Notes in 9 show by Paul Calhoun on how to use the standalone version of Apache POI.

But Christians version makes everything that much simpler to get the Excel file out to the end user, start by going to poi4xpages.openntf.org/ or essentials.openntf.org/ if you want lots of more great XPage addons. Download the latest version of one of the project.

Now you need to install the OSGI plugins into your designer and to the server, and David Leedy as recently created a notes in 9 video on how to install plugins into the designer and the server.

And when you have installed POI for XPages in both places you are good to try it out.

It’s very simple, Add the poi library to the database you want to use it in you do this in xsp.properties in the  Page Generation tab

 

 

 

 

 

 

Now you are ready to create a new XPage, when you have created the XPage.

 

 

 

 

 

Drag the simple View Export into you new Export XPage also create a button on your XPage

screen183

 

 

 

 

 

Click on the button and create a simple action

screen184

 

 

 

 

 

 

 

 

 

 

 

 

Select the sveId, you should find the ID of your simple Export control in the list.

Select you export component and select “All properties”

screen185

Fill out the downloadFileName with a name of you choice i.e export.xlxs

 

also select if you want if the view headers should be exported includeHeader and write the name of the view you want to export view.

Open up the XPage and click on the button and you export will be sent to you client.

News from IBM ConnectED15

Some of the news from IBM Connect

IBM releases early access to IBM Verse.

IBM Verse will come an an Domino solution for on premise deployment later this year

The Domino HTTP engine will get more updates that the previous released updates with SHA2 and TLS 1.0

It will get new HTTP security options as TLS 1.2, PFS and AEAD

screen103

screen104

the IBM HTTP server addon released with Domino 9 will be deprecated

screen105The NSF Datastore will get updates that we have all wanted for a long time.

View Indexes outside of the database and with this and DAOS we are breaking the 64GB nsf database maximum finally.

Live view updates is a server task that will pull and update view indexes with a interval of your choice this update is already in 9.0.1 FP3

screen106

XPages will receive support for encryption and signatures

New backend classes for XPages, Lotuscript and Java agents

There are plans for future upgrades to update java in Domino to 1.7 or 1.8

screen108

screen112

XPages support in Bluemix, see Niclas article 

Availability for this great new functionality is Q2 2015, price has not been announced yet.

This is the news I have captured, if I missed something please comment

Part 2 of Partial Refresh in Notes Forms

I couldn’t actually accept myself defeated in the battle to get partial refresh into old school Notes applications. And after a second look at my code I found a way, so I rewrote the javascript library to be a javascript object class so all calls are made inside the class. So now partial refresh of the entire form tag is the standard way of refresh but you can also narrow the refresh to everything inside a div or a table sell if you want to. And Serverside refreshes from a QuerySave agent also works.

So what you need to do is to add the AjaxCall scriptlibrary to your database.

Call the function PartialDomino.onloadAjax() in your onload event

Now all refresh events on the form will be handles as partial refresh.

If you want to also add clicks from a button, give the button a id and add that id using PartialDomino.addPartialRefreshId(“refresh”,””)

The next time the button is clicked a partial refresh is done.

If you would have added a second id to the function, only the element with that id will be replaced after the refresh.

Download the database and see the working example

disk

PartialDominov2.zip

2014 a year of change

Looking back at 2014, I see the year that XPages really got accepted as the primary language for Domino.
IBM Bluemix got launched and the response was no XPage support in Bluemix, but in the last 2 months we saw that IBM had changed their minds and now there are talking of some kind of support for xPages apps in Bluemix. Great IBM that will get the community to start using the service even more if you can deploy your apps in a real scalable App Engine. The Domino OpenNTF API, evolved and have lot’s of great features like the graph database implementation.

My 2014 has been almost only XPages Development both internal with our application suite, custom applications for different companies and adding more customers to QNova:s application cloud  that has been a real success for the company I work for. My board work with OpenNTF has just started, lots of things to understand and I hope to be able to contribute even more in 2015.

Hope you all out there have a great holiday and come back in 2015 with new contribution power because we all can do more if we do it together.

 

Holiday Hack: Get some XPage power in old Notes apps

UPDATE: Post with fully working example with serverside redirects working

One of the great things with XPages is the partial refresh that help you to update information inside the page without a full reload of the page. Well that is not possible with older notes applications because it does a full roundtrip to the server each time we change something. But we can update the screen without the users notice it with ajax. What I did was create a copy of the main js function that Domino uses _doClick and replace that with my own copy of that function in onload.

function onloadAjax(){

  window._doClickNew=window._doClick

  window._doClick=window.newDoClick

}

I store the original function within _doClickNew that is the great thing in this case with javascript. Everything is an object including functions and I can place myself before the original function. My function will now be called when something happens on the Domino form. There is two types of events on a old school Domino form. There is a refresh post and a full post, the script detects if you are trying to do a refresh post and convert that into a ajax request. You can also add entries to an Javascript array PartialRefreshArray by pushing new entries PartialRefreshArray.push(“mybutton”) if you then add a button to the form with the id mybutton it will also be ajax refreshed.

But there are some down sides to this functionality, we can’t use server side redirects. When the form is posted the referer is used when returning data, and because we have ajax refreshed the form the url isn’t updated correctly if we have created the document using the following url

server/database/form?openform

What we need to do is to create the document before we open it the first time and load the document using

server/database/view/UNID?editDocument

If that is used the code can imitate partial refresh, but remember no server side redirects can be used, webquerysave agent runs but not the redirect, so if you want to move away from the form you need to use client side JS.

I’ve created a simple database to show the functionality that you can download here 

disk

Conclusion, the partial refresh behaviour can be added but then you need to hack any redirects done using agents and you need to pre create the document and have a scheduled agent to delete the forms not saved.

This is only an hack example not any production code, and may stop working if IBM changes anything.

 

Setting up a sFTP server to be used with an existing Domino SSL certificate

With the new way of creating SSL certs for Domino a new opportunity occurred to me and actually it worked great. Because we create the certificates using OpenSSL the generated Key and certificate can actually be used on the same server to setup a FTPS server. Saidly because the Domino FTP server on OpenNTF.org doesn’t support FTPS I had to use the FileZilla server the setup was super simple.

Run the setup

go into settings and enable FTP over SSL select your .Key file and your certificate file that you got back from your certifier. Also check the other option to force all connections to the server to be FTPS.

FileZilla Server Settings

And now you have a FTPS server that can deliver external content to you Domino server. The last thing you need to setup is the users and groups that should be able to connect to the server.

Update: Paul Farris commented that this is an SSL based FTP server not an SSH based so the real name should be FTPS not sFTP.

Some performance tips for your Domino / XPages servers

When your applications has grown very big you might face a problem with performance in this case I had a customer with quite a large crm system that in some cases got very high disk io but the cpu didn’t spike.

neither did the memory almost 50% not used by the system.

So what I did was I increased the memory available for the XPage runtime JVM using HTTPJVMMaxHeapSize

and to get get better database performance I increased the database IO buffers using

NSF_BUFFER_POOL_SIZE_MB

also using cacheset tool I increased the amount of memory that Windows will cache files using, this can give you quite a lot of performance because Windows will keep more files in memory instead of reading everything from disk and if you have a newer server with more than 8GB of memory why not use the memory. You can change this when the server is running and find your optimal settings.

You need to trim this depending on how much free memory your server has and you don’t want to run out of memory.

If you have some more tips for the community feel free to comment.