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

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.

<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: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]-->
 
</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:

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

  1. I would like to ask you how do you open these types of files, as I am getting work for excel spreadsheets in the format you have shown here, but I do not know how to open them. No luck on google search. The files are sent to me as xls file.
    Any help much appreciated

    • I may not be understanding the question, but I’ll take a stab at it: As explained in the article, if you write HTML, like this, into a file and save it as “.xls”, then it is possible to read the file in Microsoft Excel (and possibly LibreOffice Calc, Gnumeric, and other apps). If your file is not in Excel’s binary format, nor HTML, then that’s a whole other discussion unrelated to this article.

  2. We use this method to generate xls files directly from the database with success for a long time. But since we have Office 2010 we always get a warning/error message, that the content of the file doesn’t match the extension of the file. Any ideas how to work around that? The error occurs when I open your example file, too.

    • I’ve noticed that too. I just tell my users to click “Yes [open the file anyway]” and that’s good enough. I’m pretty sure the only alternatives are running Excel itself on the machine that produces the report/worksheet, or using a complicated library that knows how to write proper XLSX files.

  3. Hello,

    Adding to what Alex mentionned, when I open such files in Excel 2010 and make Save, it popups to warn about risk of function loss due to saving in Web Page format, and when i make Save As, the default format is Web Page too, but with html extension.
    My customer generates a lot of these files from Oracle XML Publisher, but not to work with as html table.
    When the output file is saved from the application via IE, if we have Excel 2003 it is saved as a classic xls with real xls in it, but of we have Excel 2010 it is saved as a html table in a .xls file, with all the issues when opening and saving it, unless it it resaved in real xls format with Save As and selecting an Excel format.
    Do you know if there is there a way too setup IE or Excel 2010 not too save these files as Excel format files with Excel format in it ?

  4. 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

  5. Yes its true that excel >= 2010 will warn about html “disguised” by naming the download with .xls extension.
    I don’t know how to solve that and that will probably bother users that are less than “power users” quite a bit.
    This library http://epplus.codeplex.com on codeplex is free and easy to use.
    It will generate excel 2007 .xlsx files (which won’t be possible to open in excel <= 2005) so if you have to support older versions of excel you'll probably have to find another way. You may get some component, or install excel on the serer to generate "real" older files.

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>