How to Serve HTML as an Excel File from a Web Application

How to Serve HTML as an Excel File from a Web Application

10 January 2008

It is possible to store HTML in a file named ”.XLS” and then open that in Excel. This is useful for web applications that want to export data easily into a spreadsheet, as it's a lot easier than finding an Excel file handling library and programmatically creating an Excel binary file. This method allows you to:

Output the spreadsheet as almost plain HTML, allowing for easy programming
Use the Freeze Panes feature to freeze header rows and/or columns
Use merged cells
Include line breaks in cells

Skeleton

Create a new file with the ”.XLS” extension and use this skeleton code to start:

<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">
 
<head>
<meta http-equiv=Content-Type content="text/html; charset=windows-1252">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 11">
 
<!--[if gte mso 9]><xml>
 <x:ExcelWorkbook>
  <x:ExcelWorksheets>
   <x:ExcelWorksheet>
    <x:Name>** WORKSHEET NAME **</x:Name>
    <x:WorksheetOptions>
     <x:Selected/>
     <x:FreezePanes/>
     <x:FrozenNoSplit/>
     <x:SplitHorizontal>** FROZEN ROWS + 1 **</x:SplitHorizontal>
     <x:TopRowBottomPane>** FROZEN ROWS + 1 **</x:TopRowBottomPane>
     <x:SplitVertical>** FROZEN COLUMNS + 1 **</x:SplitVertical>
     <x:LeftColumnRightPane>** FROZEN COLUMNS + 1**</x:LeftColumnRightPane>
     <x:ActivePane>0</x:ActivePane>
     <x:Panes>
      <x:Pane>
       <x:Number>3</x:Number>
      </x:Pane>
      <x:Pane>
       <x:Number>1</x:Number>
      </x:Pane>
      <x:Pane>
       <x:Number>2</x:Number>
      </x:Pane>
      <x:Pane>
       <x:Number>0</x:Number>
      </x:Pane>
     </x:Panes>
     <x:ProtectContents>False</x:ProtectContents>
     <x:ProtectObjects>False</x:ProtectObjects>
     <x:ProtectScenarios>False</x:ProtectScenarios>
    </x:WorksheetOptions>
   </x:ExcelWorksheet>
  </x:ExcelWorksheets>
  <x:ProtectStructure>False</x:ProtectStructure>
  <x:ProtectWindows>False</x:ProtectWindows>
 </x:ExcelWorkbook>
</xml><![endif]-->
 
</head>
<body>
 
<table>
 
<thead>
   ** TABLE HEADER ROWS GO HERE **
   <tr>
      <th>Header Cell</th>
      ...
   </tr>
</thead>
 
<tbody>
   ** TABLE DATA ROWS GO HERE **
   <tr>
      <td>Data Cell</td>
      ...
   </tr>
</tbody>
 
</table>
 
</body>
</html>

If you are not using the Freeze Panes feature, omit the six lines from ”<x:FreezePanes/>” to ”</x:LeftColumnRightPane>”.

Making the File Available on the Web Server

If the file is stored statically on the web server, make sure the server is configured to send the correct MIME type for the ”.XLS” extention, which is “application/msexcel”.

If you are generating the file dynamically as the output of a script, make sure to send this MIME type in the HTTP headers before your script outputs the body of the file. Also you should include the Content-Disposition header to instruct the browser to download the file and how to name it:

Content-Type: application/msexcel
Content-Disposition: attachment; filename=NAMEOFWORKSHEET.xls

Styling Cells

Table header cells and data cells get certain default formatting (header: bold centered; data: normal left aligned). To add additional formatting, use the usual HTML tags like “B”, “I”, and “SPAN”. Hyperlinks may be embedded in cells.

To add a line break within a cell, use this code:

<br style="mso-data-placement:same-cell;" />

To merge cells together, do it as you normally would in an HTML table. Don't forget to include and omit the appropriate cells later in the row and in succeeding rows. For example, this code puts three cells in the a 2×2 block of cells:

<tr>
   <td rowspan="2">This cell is 2 rows high.</td>
   <td>cell 2</td>
</tr>
<tr>
   <td>cell 3</td>
</tr>

Example File

Here is an example file to play with: example-html.xls (You can read and edit this file in a text editor!)

Screenshot: