Tuesday, September 24, 2019

Create Excel files with LotusScript without Excel installed

One of my customer asked me to find a solution to create Excel files using LotusScript on server without Excel on it (well who wants to do install Excel and other tools on Server). Took some time but I have made a proof of concept using Apache POI and it worked very very nice. I have also made a LS2J cover so it's more easily for people who are not familiar with Java to create Excel files.

I put demo on my github account with some explanation so feel free to have a look on it: excel-apache-ls but if you wonder how it works, see snippet below:

Option Public
Option Declare

UseLSX "*javacon"
Use "Apache.Excel"

Sub Initialize
 Dim jSession As JavaSession
 Dim jClass As Javaclass
 Dim jObject As JavaObject
 Dim filepath As String
 Dim row As Integer

 Set jSession = New Javasession
 Set jClass = jSession.GetClass("explicants.office.Excel")
 Set jObject = jClass.Createobject()
 
 Call jObject.createSheet("sheet A-100")
 Call jObject.createSheet("sheet B-100")
 Call jObject.createSheet("sheet C-100")
 
 Call jObject.getSheet("sheet A-100")

 row = row + 1
 Call jObject.setCellValueString("lorem", row, 0)
 Call jObject.setCellValueString("ipsum", row, 1)
 Call jObject.setCellValueDouble(55, row, 2)
 
 row = row + 1
 Call jObject.setCellValueString("hello", row, 0)
 Call jObject.setCellValueString("world", row, 1)
 Call jObject.setCellValueDouble(200.50, row, 2)
 
 row = row + 1
 Call jObject.setCellValueString("gurli gris", row, 0)
 Call jObject.setCellValueString("george", row, 1)
 Call jObject.setCellValueDouble(0.505, row, 2)
 
 filepath = Environ("Temp") & Join(Evaluate({@Unique})) & ".xls"
 Call jObject.saveAsFile(filepath)
 
 MsgBox filepath
End Sub

3 comments :

Fredrik Norling said...

Nice work, perhaps add it as a project on openntf ?

Dmytro said...

Good point, will do.

Caroline G said...

Grreat post