Generating an excelsheet with POI…

It’s very easy for an lotusscript developer to generate an excel sheet with some OLE… but when the code should be run on the server it’s getting bad.

My last entry was about to pass a vector to the java API from lotusscript. Here some background to create the excel sheet with POI.

Of course you should customize this code to fit your solution. If it run on the server with a lot of instances, remember to do some stress tests to avoid some servers issues.

Greetings…

First get – POI Export with poi 3.2

http://www.criverapr.com/2008/10/using-poi-to-export-lotus-notes-data-to.html

Second – ls2j and dynamic array
http://www-10.lotus.com/ldd/nd6forum.nsf/c21908baf7e06eb085256a39006eae9f/45bc5292d67167508525706500525a52?OpenDocument

Dynamic arrays in lotusscript with ls2j

Last week, I was facing a tricky issue. I wanted to use some java to create an excel sheet. The POI java API requires a java vector. I created a dynamic array in lotusscript (to adjust the numbers of rows of my excel sheet) but when I called the POI API I’ve got an ugly error. The trick is to declare a variant, then affect the dynamic array to the variant and pass it to the JAVA API. So easy !

http://www-10.lotus.com/ldd/nd6forum.nsf/c21908baf7e06eb085256a39006eae9f/45bc5292d67167508525706500525a52?OpenDocument

Lotus notes FT-Search, looking for non present field…

I wanted to do a lotusscript FT-search of the non existence of a field in documents… let’s begin some head aches… hopefully with google I found quickly a solution.

NOT [fieldname] is present

Thanks to Alan… here the full post :

http://www.alanlepofsky.net/alepofsky/alanblog.nsf/dx/searching-for-blank-fields?opendocument&comments

Lotus script – GoogleMap localisation class

Hello, this simple class allows to display a google map from the lotus notes client ! There’s missing the Window’s API reference. Tell me if you want the lss file with all the references…

Greetings !

Class GoogleMapStreetUrl

Private sUrl As String

Private sStreet As String

Private sCity As String

Sub new()

sUrl = “http://maps.google.com/maps?f=q&source=s_q&hl=fr&geocode=&q=%LOC%,%STREET%”

End Sub

Public Function setStreet(psStreet As String) As Integer

sStreet = Replace(psStreet, ” “, “+”)

End Function

Public Function setCity(psCity As String) As Integer

sCity = Replace(psCity, ” “, “+”)

End Function

Public Function getUrl() As String

Dim sNewUrl As String

sNewUrl = Replace(sUrl, “%LOC%”,sCity)

sNewUrl = Replace(sNewUrl, “%STREET%”,sStreet)

getUrl = sNewUrl

End Function

Public Function open() As String

Dim url As String

url = Me.getUrl()

Call ShellExecute ( GetActiveWindow(), “open”, url, “”, “”, WIN_NORMAL)

End Function

End Class

Easy lotusscript picklist class for your apps…

When creating your application it’s always painful to remember how to generate a picklist… here comes in play my class (which is part of my own private framework).

Which 3 lines of code, I’m doing a picklist… let me explain…

Dim pl As New DbPickList(Nothing)
pl.setView("ClasseByName")
Set docRet = pl.getSingleDocument()

The first line allows you to create the picklist object which contain the notesuiworkspace object. The class contains member functions to set every parameter of the original picklist function. Defaults values are set when instantiating the object. There is two basic function to display the picklist 1. getSingleDocument() which return a simple document and getMultipleDocuments() which give you the notesdocumentcollection. Give a look to the class, it’s easy.

Link to the lss file

First download the .lss file and put everything in a script library. Then you should add the use statement.
The class allows you to customize easily the picklist but it is more convenient than the ugly function from the notesUiworkspace… give it a try and enjoy.

One cool thing is to derivate the class and then create all the picklist you need in a new class, then you’ve got centralized your picklists in a central class which allows to simplify the maintenance cycle of your app.

Greetings.

How to execute Visual Basic from JAVA ? – part 2 – autostart your VB…

In our last article we have seen how to start word from JAVA. Now we will start word with a document in parameter and then we will put an autoopen marcro in our document.

JAVA – Code

String sCmd ;

sCmd = “C:\\Program Files\\Microsoft Office\\OFFICE11\\winword.exe ” + “\”c:\\Mes documents\\Doc3.doc\”" ;

try {
Runtime.getRuntime().exec(sCmd);
} catch (IOException e) {
e.printStackTrace();
}

This will start word with a special document.

The next step is how to autostart the VB code.

You can put in your document macro the autoopen, which will automatically starts when the document is launch

VB – Code in the document

Sub autoopen()

Load UserForm1
UserForm1.Show

End Sub

Ok, this is a simple way to starts VB from JAVA. Of course you can’t synchronise your programs and also you can’t pass parameters. This is not a perfect integration but it can helps you to reuse easily something which exists already on your system.
Of course it breaks the main advantage of JAVA to be multi-system, but the world isn’t perfect… for now.

Greetings.

The cCounter class.

* When programming a very long an complex agent it’s very fine to display the agent stats at the end of the agent’s execution. These stats could be a lot of numeric data. It’s a pain to declare and manage all these variables.
* For doing this you need to create a lot of different counters and it’s very boring to manage the init part of these counters, the increment and the display. And what a pain if you want to add a new counter…
* With my solution you will find this very easy to manage and you will add a lot of interesting counters in your next complex agent.
* The idea is to put every counter in a class which will manage for us the boring job.
* This class will init the counter, manage the increment and give us the text.

Here the code :

'  Class counter v1.0
'  By Pierre Koerber

Class cCounter
lCounter List As Long

Public Function incrementCount(sCounterName As String)
If Iselement(lCounter(sCounterName)) = False Then
lCounter(sCounterName) = 1
Else
lCounter(sCounterName) = lCounter(sCounterName) + 1
End If
End Function

Public Function toString() As String
Dim sRes As String
Forall x In lCounter

If sRes = "" Then
sRes = Listtag(x) + "=" + Cstr(x)
Else
sRes = sRes + "," + Listtag(x) + "=" + Cstr(x)
End If
End Forall
toString = sRes
End Function

End Class

'-------------------------------

' calling code, this allows you to manage three counter in a easy and cool way.

'-------------------------------

sub initialize
dim counter as new cCounter()

set doc = dc.getFirstDocument
while not(doc is nothing)
if doc.Subject(0) = "" then
Call counter.incrementCount("Err")
else

call counter.incrementCount("Treated")
end if

Call counter.incrementCount("RcdTreated")
set doc = dc.getNextDocument(doc)
wend

log(counter.toString())

end sub