New post
Article
· Oct 3, 2016 3m read

Tips & Tricks - SQL to Excel

#Caché #Code Snippet #SQL #Tips & Tricks

End users sure love Excel tables. So, how can you deliver the second to the first? Read this post to find out.

Let's say you wrote  some SQL query, and now you need to make the results looks presentable. So, Excel. Excel can interpret html tables and display them as usual if they have an xls extension. Here's the code, and it does the following:

  • Executes SQL
  • Iterates over the resultset writing it to stream in a HTML format
  • Writes stream to file

Snippets

You can pass any number of arguments into the entry point and they in turn would be passed into statement. The following method works as expected and is a valid Caché ObjectScript code:

ClassMethod generateStreamFromSQL(sql As %String, args...) As %Status
{
    set st = ##class(%SQL.Statement).%New()
    set sc = st.%Prepare(sql)
    set result = st.%Execute(args...)
}

Example

Call like this:

w ##class(Utils.XLS).generateFileFromSQL("D:\1.xls", "SELECT 123456789100+1 AS Num, TO_CHAR(123456789101) AS Text, NOW() As DateTime, TO_CHAR(NOW()) As DateTimeText")

Excel displays the file like this:

 

And the contents of 1.xls looks like this:

<html>
    <head>
        <style>
            .num {
              mso-number-format:General;
            }
            .text{
              mso-number-format:"\@";/*force text*/
            }
            .date {
              mso-number-format:"Short Date";
            }
        </style>
    </head>
    <body>
        <table border="1">
            <tr style="font-weight:bold">
                <td>Num</td>
                <td>Text</td>
                <td>DateTime</td>
                <td>DateTimeText</td>
            </tr>
            <tr>
                <td class="num">123456789101</td>
                <td class="text">123456789101</td>
                <td class="date">2016-10-03 17:25:54</td>
                <td class="text">2016-10-03 17:25:54</td>
            </tr>
        </table>
    </body>
</html>

Note the css styles - they allow proper number/string/date formatting, here are some other values for mso-number-format.

And here's an example with several arguments:

w ##class(Utils.XLS).generateFileFromSQL("D:\1.xls", "SELECT ID FROM Sample.Person WHERE ID = ? OR ID = ?", 1,3)
Discussion (6)1
Log in or sign up to continue
Eduard Lebedyuk Oct 4, 2016 to Marco Blom

If you are on windows you may have access problems while writing into the root of system drive.

 I suggest you:

  • Provide filename pointing to your desktop directory (C:\Users\eduard\Desktop\1.xls for me, for example)
  • Instead of w ##class... save the status and decode it into a more readable form:
set sc = ##class(Utils.XLS).generateFileFromSQL(...)
write $System.Status.GetErrorText(sc)
0 0
Neerav Verma Jun 1, 2019 to Eduard Lebedyuk

I am getting this error

0 ´‰AError rendering: java probably not in path, Use $LOG=1 to checkdRESTARTV:e^zcomputeDirectStatus+2^%SYS.ZENReportExcelExporter.1^14e^zCommonExport+45^%SYS.ZENReportExcelExporter.1^12e^zgenerateXLSXfromXML+11^Utils.XLSX.1^11e^zgenerateFileFromSQL+6^Utils.XLSX.1^1^^Utils.XLSX.1^01E^zgenerateXMLFromSQL+12^Utils.XLSX.1^11e^zgenerateFileFromSQL+4^Utils.XLSX.1^1e^^^0

0 0