How to Export Data to Microsoft Excel or Microsoft Word

Forio Broadcast lets you export a page to either Microsoft Excel or Microsoft Word.  Accomplishing this is straightforward.  Design the page using a mix of HTML and FML, much as you would for a normal page.  Then name the file ending in ".txls" for Excel (e.g. "report.txls") or ".tdoc" for Word (e.g. "report.tdoc").  When the user clicks on a hyperlink for this page, instead of the page being displayed as HTML it will be opened and displayed in Excel or Word.
 

How this Works

When the user browser retrieves any web page, image, or other document from a server it comes with information that is known as a MIME type. The browser uses this MIME type (in addition to the file extension) to determine how to display the file.  Most HTML files (*.html, *.htm) have a MIME type of "text/html" and are displayed directly in the browser as HTML.  Similarly, most Images have a mime type of "image/gif" or "image/jpeg".  

When the browser requests a file which ends in ".txls", Forio Broadcast appends a MIME type of "application/vnd.ms-excel" which signals the browser to send the document to Microsoft Excel.  Likewise, when the file ends in ".tdoc", Forio Broadcast uses a MIME type of "application/msword" which sends the document to Microsoft Word.  

In some cases the spreadsheet might open directly within the browser, in other cases it might open as a separate document (This is a browser setting).  But in all cases the user can cut/paste from the spreadsheet or can click "Save As" to save it as an Excel file.

As a side note, files with the standard extensions ".xls" and ".doc" will also go to Microsoft Excel and Word, but will not be processed as FML. This allows you to include standard spreadsheets in your web directory that users may download for reference.
 

Formatting Your Exported Excel Document

When a ".txls" file is opened in Microsoft Excel, the application displays the page as a single worksheet.  Due to the differences between a HTML layout and an Excel worksheet layout, there may be some differences in the way the information is displayed.  Here are some tips to help out.

Rows and Columns

As noted above, Excel maps your HTML file as a set of rows and columns in a single worksheet.  For best results, your entire web page should be a giant table, with no nested subtables.  Excel will translate each row and column of the HTML table into an Excel row and column.  You can make a row span more than one column with the tag "colspan".  (just as you can in an HTML table).  

If you create a more complicated page (e.g. with multiple tables either in sequence or nested), Excel will try to guess at how it should display the data and the results may not always be as expected.  

Images

Almost all images can be displayed in an Excel spreadsheet.   (The exception is FML graphs, which can not be included in an exported Excel file).  Images should be referenced with an absolute URL instead of a relative URL.  For example, use:

instead of the usual

Formulas

You can include formulas in your Excel spreadsheet (for example to total up numbers) by starting the formula with an equal sign ('=').  Of course, you can always calculate the result in your model.  But including the formula in Excel allows non-simulation users to experiment with the spreadsheet.  You'll have to count carefully to know which cells are which.

Example formula:

Other Formatting Issues

Most HTML formatting statements (boldfaced text, background colors) will carry over to the Excel spreadsheet.  They may not look exactly the same in Excel as they do in HTML, and a few (seemingly arbitrary) formatting attributes don't work at all.   You'll probably need to do some trial and error to make this work.
 

Formatting Your Exported Word Document

Formatting a Word document is very similar to exporting to an Excel document.  In fact, it's considerably easier since the HTML does not have to map to consistently sized rows and columns.  You can use a table to layout the page, just as you do in Excel.  Or, you can just include the text directly in paragraph format with <P> tags.  Like Microsoft Excel, most HTML formatting rules will also apply to the exported Word document.
 

Example: Creating an Excel Table of Simulation Results

The following FML code can be used to create a table of simulation results.  Note the use of local references to ensure that columns widths are used consistently but can be easily changed.  ( more about creating tables )

If you name the file "table.htm", you will see output similar to the following:

If you name the file "table.txls", you will see an Excel spreadsheet that look something like this:

Finding out More

While the basics described above are enough to export a readable, attractive exported report from your simulation, there's much more that you can do.   Starting in Microsoft Office 2000, Microsoft has included an extensive set of special CSS and XML statements that can be included in an HTML text file.  These statements allow you to specify additional formatting characteristics (such as number formatting for cell formulas).  In Excel 2002, the entire document (including multiple worksheets) can even be imported/exported as a text XML file.  

Unfortunately, documentation for these features can be difficult to dig up.  You might find it useful to build your own spreadsheet in Microsoft Excel and then save the file in either HTML or XML format.   If you study the resulting file in a text editor you will be able to see some of this additional functionality.