Nice way to create Excel/Word/... documents on Domino server

Hi

I believe that almost every Lotus Notes developer had a task about creation some report in MS Excel.
It might be a task about creation a MS Word document.

Sometimes you needed to do that on the server side: in Web UI, by some schedule, whatever...

In the most of cases you can not expect to resolve such tasks using OLE related technologies.
The reason is simple - administrators do not install Office application on the server.
So how to do that?

There are enough possibilities to do that even without OLE. Google for them.

I want to share with you the way I usually used (not always, but usually).
It is based on native for MS Office xml-files.
Approach is very simple.

Create office document you want to get using corresponding application manually.
For example, I need to create some XLS-report.
I create it manually in MS Excel.

To make it easier to operate with this manually created xls-template later I created placeholders I am going to replace with real values using lotus script. You may use your own, you do not need to do it like me with two % symbols.

After that I save this template as XML using native MS Excel capabilities.
I use "XML Spreadsheet 2003" option because I want to support any possible MS Excel versions.

Now you have everything you need.

You save this xml-template as an attachment somewhere in configuration NotesDocument.
In your Web application (or in a scheduled agent) you extract this xml-file to disk, associate NotesStream with it and then do any required manipulations with xml including replacement of placeholders with real values using lotus script.

When your file is ready, you may (print "[" & url & "]") it or (mail) it to the end user.

To make this xml-document looking as a usual xls-document you may simply rename its extension from "xml" to "xls". The only one issue here is that from version 2010 of MS Office such extension substitution seems suspicious for MS Excel/Word and user will get warning about it (see screen below).
But except this warning with only MS Office 2010 user will not notice any difference.

Previous versions of MS Office (2003, 2007) did not care about such extension substitutions so it works perfect for those versions.

Of course, you will meet some additional things you need to do to make such report good enough but I am sure you will manage to do this. For example, how to output many rows to xml-template like this? It is quite easy. You just need to play with XML you have. You need to find a piece of XML that contains first table row and copy it using lotus script.

The same about MS Word documents.