Published: | |
Updated: |
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:
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.)
Create a new file with the .xls
extension and use this skeleton code to start:
example.xls
<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>
<tr>
<th>** TABLE HEADER ROWS GO HERE **</th>
...
</tr>
</thead>
<tbody>
<tr>
<td>** TABLE DATA ROWS GO HERRE</td>
...
</tr>
...
</tbody>
</body>
</html>
If you are not using the Freeze Panes feature, omit the six lines from <x:freezepanes />
to </x:LeftColumnRightPane>
.
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
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>cell 2</td>
</tr>
<tr>
<td>cell 3</td>
</tr>
Here is an example file to play with: example-html.xls.zip (You can read and edit this file in a text editor!)
Screenshot:
This policy contains information about your privacy. By posting, you are declaring that you understand this policy:
This policy is subject to change at any time and without notice.
Reader-contributed comments on Glump.net are owned by their original authors, who reserve all rights.
Comments rules:
Comments (11)
MS blocked the opening of these types of files in a recent automatic update, but have gone back on this block and are in the process of rolling out a fix (as part of the usual automatic update) next week.
We've been successfully doing this: generating htm file and writing it to excel as .xls and it has worked fine.
Now some machines don't handle it and a blank file comes up. Do you know if there is some sort of setting or security that is prohibiting this now?
I'm aware that there are some problems, but I haven't been paying attention to it or to this method of getting data into Excel.
Thanks. Any ideas of where to look for compatibility/security issues/updates that may keep this working on one machine and not another?
No.
I had the same problem seems like all machines running office 365 excel have this problem. An easy workaround is to click on save and open at the bottom of the page and it will display the warning message about the file format which you reply yes to as usual and it should open like before. This will of course leave files in the temp directory or wherever you save the file to, so it is not the best fix.
Interesting we too are also having problem now, we have been using this for years.
I did notice one trick is to save the file first then open it, insted of just using open in IE.
Here is the answer
http://stackoverflow.com/questions/38466169/response-contenttype-application-vnd-ms-excel-xls-stopped-working
The issue is related to Microsoft Security Update KB3115262 for Excel that was released July 12, 2016. The security update information can be found in Microsoft Security Bulletin MS16-088 - Critical.
I've found three workarounds (in my order of preference):
Instead of clicking Open, save the file and then open it
In Excel Trust Center > Trust Center Settings > Protected View, uncheck the top two options. This is potentially unsafe.
Uninstall the security update.
MS patch to fix this issue: support.microsoft.com/en-us/kb/3181507 โ S McCrohan 8 hours ago
Well.... If I need to generate a html file to excell with around 200 Mb??? At the moment my Ms Excel stop working and not open the archive, this will resolve my issue?
Thanks for the post.
It's not working in in Safari. Pls suggest if there is any way to get this done.
Thanks,
Sathiya
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