Serve HTML as an Excel File from a Web Application

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

Please note: The solution on this page shows you how to put HTML-encoded data in an .xls file. This is speciically abusing the file extension, just to get Excel to try to open it; properly, an .xls file must only have binary Excel 2000 format data in it. Recent versions of Microsoft Access will complain when you open the result. If you dismiss the warning, it should work fine.

This is a quick-and-dirty way to get a table from your web app into Excel without an external library and without a lot of coding. Past comments about these deficiencies in the solution provided here have been deleted and future comments along those lines will be deleted.

If you don’t like this solution, search for libraries and articles about writing valid .xlsx files and .ods files. (The OpenDocument ODS file format is much simpler to get right than .xlsx, and recent versions of Excel will happily open it without warning about the content not matching the filename.)

Skeleton

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

Note: WordPress borked the following sample code. Please download the example file at the end of this article, and view it offline to get the code skeleton you need for your program.

 
<!-- [if gte mso 9]><xml>
 <x:excelworkbook>
  <x:excelworksheets>
   <x:excelworksheet>
    <x:name>** WORKSHEET NAME **</x:name>
    <x:worksheetoptions>
     <x:selected></x:selected>
     <x:freezepanes></x:freezepanes>
     <x:frozennosplit></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]-->
 
 
   ** TABLE HEADER ROWS GO HERE **
      ...
 
   ** TABLE DATA ROWS GO HERE **
      ...
Header Cell
Data Cell

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:

This cell is 2 rows high.cell 2
cell 3

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:

2 thoughts on “Serve HTML as an Excel File from a Web Application

  1. Hi,

    I’ve no problem generating files like these, except when opened, numbers appear as text (i.e. aligned to the left, no digit grouping, etc.). When I click in the cell and then press Enter, it picks up the format I’ve assigned to it in the html file.
    As such, my question is whether you’d know how to display cells holding numbers as regular numbers and not as text… ?

    Thanks,
    D

  2. Thanks for the post.

    It’s not working in in Safari. Pls suggest if there is any way to get this done.

    Thanks,
    Sathiya

Leave a Reply

Please view the Comment Policy before submitting a comment.
 

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>