Robert Cemper · Aug 22, 2017 3m read

Light weight EXCEL download

I worked through the Community for proposals to provide end users
in an easy way with data formatted as EXCEL sheet.

There is an great article Tips & Tricks - SQL to Excel

there's an important message embedded: "EXCEL can interpret HTML tables and display them as usual"

But the result is on your server and you are left alone to get it out to your remote user somewhere.
Could be a subject to be solved using REST or WebServices.

In a different place it was proposed to use ZENreports. It's a great tool with a broad range of options and features.
The week point about:  ZENreports is not covered by a typical standard license but requires an license extension.
And it's not available on some older versions of Caché. So an upgrade might be necessary.
You also may face a discussion with your financial director on ROI and similar commercial subjects.
And: The result is on your server and it's your extra job to get it to your remote user somewhere.
As seen before.

Next approach: Using Activate to write EXCEL directly through standard DLLs.
YES, you get all in your hands that you need and much more that you don't want and don't need because EXCEL is huge.
I've seen implementations that had a C# wrapper in between to filter out unwanted functionality.
OK! You have full control.
But would you like to have a full EXCEL installation on your Windows server?  With the related licensing?
Combined with the unpredictable risk of sudden product changes as we have experience with MS?
And: The result is again on your server and it's your extra job to get it to your remote user somewhere.
As seen before.

Another proposal: Squirrel
Now the transport to users is solved! This is a fine solution with well educated users understanding SQL.
But 2 things need to be handled:
- Getting the correct version installed at the user's equipment. A matter of the user population.
- Managing SQL access rights to have enough rights to succeed and equally to prevent misuse.
 It's a standard with relational DBs. But I haven't seen it often with Caché.

Where's the light weight export to EXCEL ?

After that research I went back in history.
Good old CSP is well equipped to produce HTML tables accepted from EXCEL as input.
With modern Browsers you don't even need <head> and  <body> tags.
So the required code around your SQL result set is really slim.
And you are free to add any formatting you need either by HTML or in SQL.

The final trick to move your table from browser to EXCEL:
In the method OnPreHTTP inherited from %CSP.Page you
set %response.ContentType="application/"

Now when you call the class with your browser you get asked to open or to save it.
Next , because the extenison is .cls you get asked for the program to open it.

And if you select EXCEL the table is ready for the user to work with it.



This could be a slim solution for rater static SQL queries.
Well suited to serve a wide distributed population of users.

- No need for additional EXCEL installation on Caché server
- No need for new Caché version + license upgrades to run ZENreports
- No need for extra transport to move results to users
- No need for local installed installed software (Squirrel)
- No need for additional management of SQL access rights

Rather small size of code with simple structure

Now you will understand why I titled the article "Light Weight"

You may download my tiny example for namespace SAMLPES  here as .zip


This is a coding example working on Caché 2018.1.3 and IRIS 2020.2 
It will not be kept in synch with new versions 
It is also NOT serviced by InterSystems Support !
1 1,260
Discussion (3)7
Log in or sign up to continue

Hi, Robert! 

Great simple example! Things should be simple when possible for sure.

Thanks for the github repo!

Do you mind to export code as UDL and push it to repo?

You can manage that with either Atelier, or with this simple lib cache-udl by [@Oleg Dmitrovich] or via the port app by [@Rubens.Silva].

It would be much readable on github like it is in all this repositories.

Thank you, Robert!

A quick note on new Open Exchange publishing enhancements: if you code is publicly available on Github and you put the github URL to the Github URL field it automatically uses License on Github (if any) and as documentation to expose on Open Exchange.