Project

General

Profile

Excel export How-To

Excel export can be easily achieved with a little bit of html trickery and the help of the file plugin.
Basically, the whole idea is to produce some html, add a few tags in the header, and make Excel believe this is one of his own native file!

Servoy method

On the Servoy side, you create a report, using a template (or even a String in memory), then you save the resulting report on the client's hard disk, and ask the OS to open it with Excel (this is for Smart client, you will see below that it's even easier in Web client), so here's how it goes:

// prepare the report the way you usually do (putting any Servoy object in a context):
var context = new Object();
context.foundset = foundset;
context.now = new Date();
// etc..

plugins.VelocityReport.installFonts();
plugins.VelocityReport.addDefaultReportParameter('dateFormat', 'dd-MM-yyyy');

// we use a template that should be located in the default velocity reports folder:
var template = 'excelTemplate.html';

// now let's render the report into a String:
var excel = plugins.VelocityReport.renderTemplate(template,context);

if (excel) { // everything went well, we have a report!

    // there is 2 different ways of doing this depending on whether we have a Smart or a Web client

    if (application.getApplicationType() == APPLICATION_TYPES.SMART_CLIENT) {

        // First, the Smart client:

        // ask the user for a location to put the report (NOTE that we are saving the file with a .xls extension):
        var file = plugins.file.getDesktopFolder().getAbsolutePath().replace('\\','/') + "/report.xls";
        var f = plugins.file.showFileSaveDialog(file,'Choose a location to save the Excel report');

        if (f) { // the user validated a location, we have a file!

            var doit = true;

            if (f.exists()) { // ask the user to confirm overwriting if file already exists:
                doit = (plugins.dialogs.showQuestionDialog('Overwrite existing?', 'The file already exist, do you want to overwrite?', 'No', 'Yes') == 'Yes');
            }

            // now save the content of our report to the file:
            if (doit && plugins.file.writeTXTFile(f, excel, 'UTF-8')) {

                // and ask the system to open it (will open Excel on the client if it is installed):

                if ((/Windows/i).test(application.getOSName())) { 
                    application.executeProgram('rundll32', 'url.dll,FileProtocolHandler', f);
                }

                else if ((/FreeBSD/i).test(application.getOSName()) || (/Linux/i).test(application.getOSName())) { 
                       application.executeProgram('mozilla', f);
                }

                else if ((/Mac/i).test(application.getOSName())) { 
                       application.executeProgram('open', f);
                }

            }
        }
    } else {

        // this time in Web client:

        writeTXTFile( 'report.xls', excel, 'UTF-8', 'application/vnd.ms-excel');

    }

}

XHTML Template

The template itself is simple html, with a few tags to help Excel recognize its styles:


<!-- first declare an Excel namespace within our xhtml template -->
<html xmlns:x="urn:schemas-microsoft-com:office:excel">

<head>
<!-- set the charset (remember that Java Strings are UTF-8 by default -->
<meta http-equiv="Content-Type" content="text/html;charset=UTF-8"/>

<!-- give our report a title -->
<title>Excel reports made easy!</title>

<!-- and some styles -->
<style type="text/css" media="all">

//* you can use regular CSS styles in there */
* {
    font-family: Tahoma, Arial, Sans-serif;
    font-size: 12px;
}
h1 {
    font-size: 18px;
    text-align: center;
}
table {
    border: solid gray 1px;
}
tfoot {
    font-weight: bold;
}

/** you can also use Excel built-in styles and formats - in Excel create a simple file and export to html, 
    then open the html file in a text editor to copy the style(s) you want to use (much simpler than reading MS documentation! :-) */
.curr
{
    /** this one defines a currency format */
    mso-number-format:"_ * \#\,\#\#0\.00_\)\\ \0022$\0022_ \;_ * \\\(\#\,\#\#0\.00\\\)\\ \0022$\0022_ \;_ * \0022-\0022??_\)\\ \0022$\0022_ \;_ \@_ ";
    mso-style-name:Currency;
    mso-style-id:4;
}

</style>

<!-- now that part will help Excel create a real Excel sheet: -->
<!--[if gte mso 9]>
<xml>
    <x:ExcelWorkbook>
        <x:ExcelWorksheets>
            <x:ExcelWorksheet>
                <x:Name>Sheet 1</x:Name>
                <x:WorksheetOptions>
                    <x:Panes>
                    </x:Panes>
                </x:WorksheetOptions>
            </x:ExcelWorksheet>
        </x:ExcelWorksheets>
    </x:ExcelWorkbook>
</xml>
<![endif]-->

</head>

<!-- And now on to our fantastic report! :) -->
<body>

    <h1>Example Report</h1>
    <p>Done at: $!now</p>

    <table>
        <!-- HEADER -->
        <thead>
            <tr>
                <th>First header</th>
                <th>Second header</th>
                <th>Third header</th>
                <th>etc...</th>
            </tr>
        </thead>

        <!-- BODY -->
        <tbody>
        #foreach($row in $foundset)
            <tr>
                <td>$!row.yourValue</td>
                <td class="curr">$!row.someMoneyValue</td>
                <td>$!number.format('0.00%', $!row.percentValue)</td>
                <td>etc...</td>
            </tr>
        #end
        </tbody>

        <!-- FOOTER -->
        <tfoot>
            <tr>
                <td>TOTAL</td>
                <td class="curr">$!calculatedMoneyTotal</td>
                <td>$!number.format('0.00%', $!calculatedTotalPercent)</td>
                <td>etc...</td>
            </tr>
        </tfoot>
    </table>
</body>
</html>

That's it!