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

Template literals

I have to follow up with changes to ES6. Today I noticed template literals (there is also tagged template literals but that I will check later).
I am really happy with that, it is simple and helps a lot.

Template literals are a new feature in ES6 that provide us string template things!

Let's have a look on real example

var a = "Apples";
var b = 10;
console.log(`I would like to buy ${b+b} ${a}.`);
// I would like to buy 20 Apples.

Deferred Loading

On my job we are trying to decrease time for loading pages as much as it is possible, we simply want 'fast pages'. We don't want our users waited for 2 seconds each time they load page. We are focusing to keep not more then 0.5-1 second per page and we are doing really a lot in that area. Nowadays It's quite common for most of websites to include javascripts just in head area (or at bottom area) as static html so each time we load pages they will be not responsive till all resources will be loaded. I want to share how to load of javascript files (or any another resouces) just after page loads. It could increase speed dramatically. This solution loads all javascrip files deferred after page load on onload event. That snippet should be minimized and be present on every page where you want to use deferred loading.

Snippet of deferred loader

(function(){
 function deferred(success){
    if (typeof window.onload != 'function') {
   window.onload = success;
  } else {
   var old = window.onload ;
   window.onload = function() {
    old();
    success();
   }
  }
 }

 function getScript(url,success){
  var script=document.createElement('script');
  script.src=url;
  var head=document.getElementsByTagName('head')[0],done=false;
  script.onload=script.onreadystatechange = function(){
    if ( !done && (!this.readyState || this.readyState == 'loaded' || this.readyState == 'complete') ) {
   done=true;
   success();
   script.onload = script.onreadystatechange = null;
   head.removeChild(script);
    }
  };
  head.appendChild(script);
 }

 function getScripts(urls, success){
  var total = 0;
  function trySuccess(){
   total++;
   if(total == urls.length)success();
  }
  
  for (var i = 0; i < urls.length; i++) {
   getScript(urls[i],trySuccess);
  }
 }
 
 var DeferredLoader = window.DeferredLoader = {};
 DeferredLoader.getScript = getScript;
 DeferredLoader.getScripts = getScripts;
 DeferredLoader.deferred= deferred;
})();

How to use loading, in that example we load jQuery, then when it's loaded we load kissmetrics libraries and only then utils.

var urls = {
 jquery: "/javascript/jquery-1.9.1.min.js",
 dependent: ["/javascript/util-2013-04-10m.js"],
 kissmetrics:['//i.kissmetrics.com/i.js','//doug1izaerwt3.cloudfront.net/' + window._kmk + '.1.js' ]
};

DeferredLoader.deferred(function () {
 DeferredLoader.getScript(urls.jquery, function () {
  DeferredLoader.getScripts(urls.kissmetrics, function () {
   DeferredLoader.getScripts(urls.dependent, function(){})
  })
 })
})

JavaScript: use power of console

console.log() - is pretty nice way to display debug information without interrupting user's actions. But do you know that 'console object' has lot of other useful methods? In this article I will try to show another important method in console object.


1. power of output
most common case for using console object is:
console.log("hello word")
but here few more ways how we can use it
console.log("string1", "string2", 1, 2, {proper1:"val1", proper2: 5});
console.log("Do you know that word %s same for all language except english %s. %d < %d", "ananas", "pineapple", 1, 2);

There are few another kind of log (works same as log but has different status)
console.debug(), console.info(), console.warn(), console.error()


console.group() and console.groupEnd() allow you to format very nice output with categories, it is very usefull when you have tons of data in log so you can easy control what is related to etc.
console.group("Food")
console.group("Fruit")
console.log("Apple")
console.groupEnd("Fruit")
console.group("Vegetables")
console.log("Potato")
console.groupEnd("Vegetables")

console.groupEnd("Food")

2. power of time
just put console.time()/console.timeEnd() before/after code where you want to measure time .
Aslo there are profile methods console.profile() and console.profileEnd() which allow to see "stack".


3. power of inspection
I guess most of us often want to see specific properties in HTML fragment, for such tasks we can use console.dir(object) or console.dirxml(element)


Some usefull articles about logging: Firebug about logging and Joe Hewitts about console

short dojo + ajax example how to get data from db

There are 3 most useful ahax-approaches I use when working with Lotus Domino:
- getting view as JSON. database.nsf/viewname?readviewentries& OutputFormat=JSON (fast and modern)

- agent approach (example here, most flexible and probably most slow approach)

- page/form/view as elelemnts, f.x. database.nsf/myform?openform&unid=123123 (easy to use but not  flexible, I use it very rarely)

Very small and known example of dojo's ajax, I'm getting some information from agent and then will use it in my JS later.

var jsondata;

dojo.xhrGet({

url: "database/agentGetData?Openagent&param1=id1",

sync: true,

load: function(data) {

jsondata = data;

}

});

I'm interesting in any another 'smart' approaches, so if u have some interesting idea we can discuss in comments them.

Native JSON

var jsonObjStr = '{"name":"Erast Fandorin", "speciality":"detective"}';
var object_person = JSON.parse(jsonString);
// object_person is object now with 2 properties

var personString = JSON.stringify(person);
// personString now keeps the string '{"name":"Erast Fandorin", "speciality":"detective"}'

this native JSON is now supported mostof browsers, so just use it :)

JS staff: Use Object instead of Switch.

Well, many of us use switch to solve own tasks, but there is another way I would like to show. We can use Object approach to solve our problem and it looks more intelligent from my point of view.

Let me show couple examples

1. Example with switch
function FoodType(food) {
var getfoodtype;

switch(food) {
case 'apple': getfoodtype = 'fruit'; break;
case 'cucumbers': getfoodtype = 'vegetable'; break;
case 'watermelon': getfoodtype = 'strawberry'; break;
default: getfoodtype = 'not recognized';
}

return getfoodtype;
}

var getfoodtype, case, and break, we can avoid them.

2. Example with Object
FoodType {
apple: fruit,
cucumbers: vegetable,
watermelon: strawberry,

GetFoodType: function(type) {
return(this[type] || 'not recognized');
}

}

Ofc there could be cases where Object approach does not work and we have to use switch, but it is alternative, so keep in mind this.

Dynamical counter in Lotus Notes client

Have you ever tried to show length of field (f.x. just below the field) ?

Let me show my example, I've a form, there I have couple fields I need to show length of it (because there is some validation of length for this field).

So when user input new char in Title field, Character count should increase to 1. Know how to do that? I believe many of you know, but anywhere I would like to share this approach.

There are actually 2 fields: 1 for input and another one is just below the Title of field (computed type).

What we need to do - use some simple JavaScript lines..

step 1.
go to JS Header even in form/subform and put there couple lines of JS (select Run: Client and JavaScript)

 var f = document.forms[0];  
 var stopDynamicalCounter = 0;  
 function updateCharCounter(delay) {  
   if(stopDynamicalCounter == 0) return;  
   f.AlternateTitle_length.value = f.AlternateTitle.value.length;  
   setTimeout( 'updateCharCounter( ' + delay + ' )', delay);  
 }  

step 2.
select input-field, and go to onFocus event, select Run: Client and JavaScript and put there 2 lines

 stopDynamicalCounter = 1;  
 updateCharCounter(100)  

step 3.
select input-field, and go to onBlur event, select Run: Client and JavaScript and put there 1 line

 stopDynamicalCounter = 0;  

That's all.

Change encoding attribute after XLST

I've done some transformation my DXL using XSLT. The funny thing was that after transformation using

// Transform
var str = docxml.transformNode(xsl);

I always got 'encoding' as UTF-16, that's not correct, so after some time I found nice solution

here is code I used: http://dotnet.itags.org/dotnet-tech/66097/

var doc = new ActiveXObject("Microsoft.XMLDOM");
doc.load("test.xml");

// Print initial encoding
var pi = doc.firstChild;
var eattr = pi.attributes.getNamedItem("encoding");
var encoding = eattr.value;

// Convert to string and reload (this loses the encoding)
var xml = doc.xml;
doc.loadXML(xml);

// Reset the encoding to "ISO-8859-1"
pi = doc.firstChild;
var eattr = pi.attributes.getNamedItem("encoding");
if (eattr == null) {
eattr = doc.createAttribute("encoding");
pi.attributes.setNamedItem(eattr);
}
eattr.value = "ISO-8859-1";

// And save document with new encoding.
doc.save("test2.xml");

It works fine for me (and not, output option in XSL did not help)

Date.prototype.addDays

I did not find that method how can I add days to my date, so here is solution:

// add days
Date.prototype.addDays = function(days) {
var secsDay = 86400000; // there are 86400000 secs in 1 day
var time = this.getTime(); // number of milliseconds since midnight of January 1, 1970.
var newdate = new Date(time + (days * secsDay));
this.setDate(newdate.getDate());
}

If anybody has better solution, you are welcome!

'wget' as way to download files

Need to download files from our Domino server by this path http://host/myfile.log, using MS DOS command?

You can try to use wget as one of possible way to do that.

here is an exmplae of DOS command:

wget.exe http://host/myfile.log

I've run this DOS command in my JS file (yes ActiveX is required for that), but anywhere it is quite interesting approach.

JavaScript libraries and reference sites

Got it from BestPracticesWebAppDevDomino8.pdf

Ajaxian.com
a site dedicated to improving Web development

devguru.com - A another site for Web development information

Dojo
http://www.dojotoolkit.org/

jQuery
http://jquery.com
A fast, concise, JavaScript Library that simplifies working with HTML documents

JSON.org
http://json.org
The home page for JSON information

MooTools
http://mootools.net
A compact, modular, OO JavaScript framework that is designed for the intermediate to advanced JavaScript developer

Prototype
http://www.prototypejs.org/

script.aculo.us
script.aculo.us
Provides an easy-to-use, cross-browser user interface

Yahoo UI
http://developer.yahoo.com/yui
Provides a set of utilities and controls, written in JavaScript

EXT
http://extjs.com
Provides for a cross-browser UI libraries


Some useful links:

Web development tools
http://www.ibm.com/developerworks/wikis/display/dominoappdev/Web+development+tools

Web development resources
http://www.ibm.com/developerworks/wikis/display/dominoappdev/Web+development+resources

Domino resources
http://www.ibm.com/developerworks/wikis/display/dominoappdev/Domino+resources

isNumeric for web

as I think the best approach to check value in web for IsNumeric is:

function IsNumeric(inputVal,sErrorMsg) {
if (isNaN(parseFloat(inputVal))) {
     alert(sErrorMsg)
     return false;
}
return true
}

I also used such approach, but I don't like it anymore
function IsNumeric(expression) {
var nums = "0123456789";
if (expression.length==0)return(false);
for (var n=0; n <>
if(nums.indexOf(expression.charAt(n))==-1)return(false);
}
return(true);
}