Show ‘n Tell Thursday: Export directly from a web agent to an Excel spreadsheet in landscape format (23 Feb 2006)

A lot of web applications has a requirement to export some data to Excel spreadsheets. Whether you write the code (normally an agent) to export the data in LotusScript or Java you normally have two options:

  • Use a HTML page
  • Use a XML document

I’m going to concentrate here on using the HTML option since it is by far the easiest and the one that produces the shortest agent. However using the XML option affords you a number of other advanced options such as creating named cells and creating multiple worksheets in a workbook. I really suggest looking into this option if you need more specialized formatting.

To do this the easiest approach is creating the spreadsheet in Excel, saving is as XML and then using the generated XML document as a “template”.

Anyways – on with the export. To export from the web to Excel using a HTML document is mind numbingly easy. Simply create a HTML document with a HTML table and set the Content-type to application/vnd.ms-excel. Exporting a static spreadsheet with two columns and two rows is as simple as the following:

Sub Initialize
   Dim session As New NotesSession
   Dim docContext As NotesDocument
   Set docContext = session.DocumentContext

   'set content-type
   Print "Content-type: application/vnd.ms-excel"

   'start table
   Print |<HTML>|
   Print |<body><table border="1">|

   'output table
   Print |<tr><td>|
   Print |1-1|
   Print |</td><td>|
   Print |1-2|
   Print |</td></tr>|
   Print |<tr><td>|
   Print |2-1|
   Print |</td><td>|
   Print |2-2|
   Print |</td></tr>|

   'stop table and document
   Print |</table></body></html>|

End Sub

The trick is however to set the page title and page orientation for print. To do this you use can use some Microsoft proprietary CSS extensions. The changes from the above code is highlighted in bold. 

Sub Initialize
   Dim session As New NotesSession
   Dim docContext As NotesDocument
   Set docContext = session.DocumentContext

   'set content-type
   Print "Content-type: application/vnd.ms-excel"

   'start table
   Print |<html xmlns:x="urn:schemas-microsoft-com:office:excel">|
   Print |<head>|
   Print |
<style>
  <!--table
  @page
   {mso-header-data:"&CMultiplication Table00ADate: &D00APage &P";
   mso-page-orientation:landscape;}
   br
     {mso-data-placement:same-cell;}

  -->
</style>
  <!--[if gte mso 9]><xml>
   <x:ExcelWorkbook>
    <x:ExcelWorksheets>
     <x:ExcelWorksheet>
      <x:Name>Sample Workbook</x:Name>
      <x:WorksheetOptions>
       <x:Print>
        <x:ValidPrinterInfo/>
       </x:Print>
      </x:WorksheetOptions>
     </x:ExcelWorksheet>
    </x:ExcelWorksheets>
   </x:ExcelWorkbook>
  </xml><![endif]-->|

   'start body and table
   Print |</head><body><table border="1">|

   'output table
   Print |<tr><td>|
   Print |1-1|
   Print |</td><td>|
   Print |1-2|
   Print |</td></tr>|
   Print |<tr><td>|
   Print |2-1|
   Print |</td><td>|
   Print |2-2|
   Print |</td></tr>|

   'stop table and document
   Print |</table></body></html>|

End Sub

It is as simple as that. The trick is of cause in the CSS extensions to control the page setup. The ones used here should be pretty self-explainatory. There are numerous other CSS extensions you can use – see the link at the bottom for more information.

Hope it helps someone. Happy Thursday.

Further reading:

Technorati tags: ,

2 thoughts on “Show ‘n Tell Thursday: Export directly from a web agent to an Excel spreadsheet in landscape format (23 Feb 2006)”

  1. Just curious….I have been doing a bunch of work with reports and AJAX. The content-type value seems to be ignored when pulling information via AJAX. Have you noticed the same thing?

    Like

  2. Well my guess is it’s because the Content-type isn’t handled the same way in an AJAX type call than in a “normal full-browser” call. When using AJAX what other Content-type than text/xml would you set?

    Like

Comments are closed.