Far and away, the cleanest, easiest export from tables to Excel is Jquery DataTables Table Tools plugin. You get a grid that sorts, filters, orders, and pages your data, and with just a few extra lines of code and two small files included, you get export to Excel, PDF, CSV, to clipboard and to the printer.
So, quick to deploy, no browser limitations, no server-side language required, and most of all very EASY to understand. It's a win-win. The one thing it does have limits on, though, is strict formatting of columns.
If formatting and colors are absolute dealbreakers, the only 100% reliable, cross browser method I've found is to use a server-side language to process proper Excel files from your code. My solution of choice is PHPExcel It is the only one I've found so far that positively handles export with formatting to a MODERN version of Excel from any browser when you give it nothing but HTML. Let me clarify though, it's definitely not as easy as the first solution, and also is a bit of a resource hog. However, on the plus side it also can output direct to PDF as well. And, once you get it configured, it just works, every time.
UPDATE - September 15, 2016: TableTools has been discontinued in favor of a new plugin called "buttons" These tools perform the same functions as the old TableTools extension, but are FAR easier to install and they make use of HTML5 downloads for modern browsers, with the capability to fallback to the original Flash download for browsers that don't support the HTML5 standard. As you can see from the many comments since I posted this response in 2011, the main weakness of TableTools has been addressed. I still can't recommend DataTables enough for handling large amounts of data simply, both for the developer and the user.
and in fact I got it downloadable as a Excel file. However, I did not get the expected result - the file was open in OpenOffice.org Writer. That is my problem: I do not have Excel in this machine so I cannot try it better. Also, this trick worked more or less six years ago with older browsers and an antique version of MS Office, so I really cannot say if it will work today.
Anyway, in the document above I added a button which would download the entire document as an Excel file, in theory:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Java Friends</title>
</head>
<body>
<table style="font-weight: bold">
<tr style="background-color:red"><td>a</td><td>b</td></tr>
<tr><td>1</td><td>2</td></tr>
<tr>
<td colspan="2">
<button onclick="window.open('data:application/vnd.ms-excel,'+document.documentElement.innerHTML);">
Get as Excel spreadsheet
</button>
</td>
</tr>
</table>
</body>
</html>
Save it in a file and click on the button. I'd love to know if it worked or not, so I ask you to comment even for saying that it did not work.
值得注意的是，较旧的浏览器不支持数据 URI 方案，因此您可能需要为那些不支持它的浏览器生成文件服务器端。
您可能还需要对数据URI内容执行base64编码，这可能需要js库，以及在数据 URI 中的 mime 类型后面添加字符串“;base64”。
It is possible to use the old Excel 2003 XML format (before OpenXML) to create a string that contains your desired XML, then on the client side you could use a data URI to open the file using the XSL mime type, or send the file to the client using the Excel mimetype "Content-Type: application/vnd.ms-excel" from the server side.
Open Excel and create a worksheet with your desired formatting and colors.
Save the Excel workbook as "XML Spreadsheet 2003 (*.xml)"
Open the resulting file in a text editor like notepad and copy the value into a string in your application
Assuming you use the client side approach with a data uri the code would look like this:
<script type="text/javascript">
var worksheet_template = '<?xml version="1.0"?><ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">'+
'<ss:Styles><ss:Style ss:ID="1"><ss:Font ss:Bold="1"/></ss:Style></ss:Styles><ss:Worksheet ss:Name="Sheet1">'+
'<ss:Table>{{ROWS}}</ss:Table></ss:Worksheet></ss:Workbook>';
var row_template = '<ss:Row ss:StyleID="1"><ss:Cell><ss:Data ss:Type="String">{{name}}</ss:Data></ss:Cell></ss:Row>';
</script>
Then you can use string replace to create a collection of rows to be inserted into your worksheet template
<script type="text/javascript">
var rows = document.getElementById("my-table").getElementsByTagName('tr'),
row_data = '';
for (var i = 0, length = rows.length; i < length; ++i) {
row_data += row_template.replace('{{name}}', rows[i].getElementsByTagName('td')[0].innerHTML);
}
</script>
Once you have the information collected, create the final string and open a new window using the data URI
<script type="text/javascript">
var worksheet = worksheet_template.replace('{{ROWS}}', row_data);
It is worth noting that older browsers do not support the data URI scheme, so you may need to produce the file server side for those browser that do not support it.
You may also need to perform base64 encoding on the data URI content, which may require a js library, as well as adding the string ';base64' after the mime type in the data URI.
Excel has a little known feature called "Web queries" which let you retrieve data from almost every web page without additional programming.
A web query basicly runs a HTTP request directly from within Excel and copies some or all of the received data (and optionally formatting) into the worksheet.
After you've defined the web query you can refresh it at any time without even leaving excel. So you don't have to actually "export" data and save it to a file - you'd rather refresh the data just like from a database.
You can even make use of URL parameters by having excel prompt you for certain filter criteria etc...
However the cons I've noticed so far are:
dynamicly loaded data is not accessible, because Javascript is not executed
首先，我不建议尝试导出 Html 并希望用户的 Excel 实例能够选择它。根据我的经验，该解决方案充满了问题，包括与 Macintosh 客户端不兼容，并向用户抛出错误，表明相关文件不是指定的格式。最安全、用户友好的解决方案是服务器端解决方案，您可以在其中使用库构建实际的 Excel 文件并将其发送回用户。下一个最佳解决方案和更通用的解决方案是使用 Open XML 格式。我遇到了一些与旧版 Excel 的罕见兼容性问题，但总的来说，这应该为您提供一个适用于任何版本的 Excel（包括 Mac）的解决方案。
First, I would not recommend trying export Html and hope that the user's instance of Excel picks it up. My experience that this solution is fraught with problems including incompatibilities with Macintosh clients and throwing an error to the user that the file in question is not of the format specified. The most bullet-proof, user-friendly solution is a server-side one where you use a library to build an actual Excel file and send that back to the user. The next best solution and more universal solution would be to use the Open XML format. I've run into a few rare compatibility issues with older versions of Excel but on the whole this should give you a solution that will work on any version of Excel including Macs.
if your excel file is not very fancy (no diagrams, formulas, macroses) you can dig into the format and compose bytes for your file, then encode them with base64 and put in to the href
This is actually more simple than you'd think: "Just" copy the HTML table (that is: The HTML code for the table) into the clipboard. Excel knows how to decode HTML tables; it'll even try to preserve the attributes.
<script Language="javascript">
function ExportHTMLTableToExcel()
{
var thisTable = document.getElementById("tbl").innerHTML;
window.clipboardData.setData("Text", thisTable);
var objExcel = new ActiveXObject ("Excel.Application");
objExcel.visible = true;
var objWorkbook = objExcel.Workbooks.Add;
var objWorksheet = objWorkbook.Worksheets(1);
objWorksheet.Paste;
}
</script>
This code is IE only so it is only useful in situations where you know all of your users will be using IE (like, for example, in some corporate environments.)
<script Language="javascript">
function ExportHTMLTableToExcel()
{
var thisTable = document.getElementById("tbl").innerHTML;
window.clipboardData.setData("Text", thisTable);
var objExcel = new ActiveXObject ("Excel.Application");
objExcel.visible = true;
var objWorkbook = objExcel.Workbooks.Add;
var objWorksheet = objWorkbook.Worksheets(1);
objWorksheet.Paste;
}
</script>
Note: if the page is not accessible directly, but login, you will need to handle this by entering the form data and emulating the user actions with python
here is the example
from win32com.client import Dispatch
ie.Document.all('username').value=usr
ie.Document.all('password').value=psw
the same manner for retrieval of data from web page. Let's say element with id 'el1' contain the data. retrieve the element text to the variable
el1 = ie.Document.all('el1').value
then when data is in python variable, you can open the excel screen in similar manner using python:
only the tip: use AppleScript - it has simple and similar API as win32com.client Dispatch
Solution for Linux:
java.awt.Robot might work for this it has click, key press (hot keys can be used), but none API for Linux that I am aware about that can work as simple as AppleScript
如果数据实际上是 HTML 页面，并且不是由 ASP、PHP 或其他脚本语言创建的，并且您使用的是 Internet Explorer 6，并且计算机上安装了 Excel，则只需右键单击页面并浏览菜单。您应该看到“导出到 Microsoft Excel”。如果所有这些条件都成立，请单击菜单项，在出现一些提示后，它将导入到 Excel 中。
If the data is actually an HTML page and has NOT been created by ASP, PHP, or some other scripting language, and you are using Internet Explorer 6, and you have Excel installed on your computer, simply right-click on the page and look through the menu. You should see "Export to Microsoft Excel." If all these conditions are true, click on the menu item and after a few prompts it will be imported to Excel.
if you can't do that, he gives an alternate "drag-and-drop" method:
有两种实用的方法可以自动执行此操作，但只有一种解决方案可以在所有浏览器中使用。 首先，您应该使用 open xml 规范来构建 Excel 工作表。 Microsoft 提供免费插件，使此格式也可用于旧版 Office 版本。自 Office 2007 以来，open xml 已成为标准。这两种方式很明显是服务器端或客户端。
另一种方法是使用服务器端实现。所有语言都应该有很多开放 XML 的实现。你只需要抓住一个。在大多数情况下，这将是修改视图模型以生成文档的最简单方法，但可以肯定的是，您可以将所有数据从客户端发送回服务器并执行相同的操作。
There are practical two ways to do this automaticly while only one solution can be used in all browsers. First of all you should use the open xml specification to build the excel sheet. There are free plugins from Microsoft available that make this format also available for older office versions. The open xml is standard since office 2007. The the two ways are obvious the serverside or the clientside.
The clientside implementation use a new standard of CSS that allow you to store data instead of just the URL to the data. This is a great approach coz you dont need any servercall, just the data and some javascript. The killing downside is that microsoft don't support all parts of it in the current IE (I don't know about IE9) releases. Microsoft restrict the data to be a image but we will need a document. In firefox it works quite fine. For me the IE was the killing point.
The other way is to user a serverside implementation. There should be a lot implementations of open XML for all languages. You just need to grap one. In most cases it will be the simplest way to modify a Viewmodel to result in a Document but for sure you can send all data from Clientside back to server and do the same.
发布评论
评论（14）
毫无疑问，从表格到 Excel 的最干净、最简单的导出是 Jquery DataTables Table Tools 插件。一个对数据进行排序、过滤、排序和分页的网格，只需几行额外的代码和两个小文件，您就可以导出到 Excel、PDF、CSV、剪贴板和打印机。
这就是所需的全部代码：
因此，可以快速部署，没有浏览器限制，不需要服务器端语言，而且最重要的是非常容易理解。这是双赢的。但它确实有限制的一件事是严格的列格式。
如果格式和颜色绝对是破坏因素，那么我发现的唯一 100% 可靠的跨浏览器方法是使用服务器端语言从代码中处理正确的 Excel 文件。我选择的解决方案是 PHPExcel 这是迄今为止我发现的唯一一个可以积极处理格式化导出的解决方案当您只提供 HTML 时，可以从任何浏览器转换为现代版本的 Excel。但让我澄清一下，它绝对不像第一个解决方案那么简单，而且也有点占用资源。然而，从好的方面来说，它也可以直接输出为 PDF。而且，一旦配置完成，它每次都能正常工作。
更新 - 2016 年 9 月 15 日： TableTools 已停止使用，取而代之的是名为“ 的新插件按钮”这些工具执行与旧的 TableTools 扩展相同的功能，但安装起来要容易得多，并且它们利用现代浏览器的 HTML5 下载，并且能够为不支持的浏览器回退到原始 Flash 下载。不支持 HTML5 标准。正如您从我 2011 年发布此回复以来的许多评论中看到的那样，TableTools 的主要弱点已经得到解决。对于开发人员和用户而言，我仍然无法推荐 DataTables 来简单地处理大量数据。
Far and away, the cleanest, easiest export from tables to Excel is Jquery DataTables Table Tools plugin. You get a grid that sorts, filters, orders, and pages your data, and with just a few extra lines of code and two small files included, you get export to Excel, PDF, CSV, to clipboard and to the printer.
This is all the code that's required:
So, quick to deploy, no browser limitations, no server-side language required, and most of all very EASY to understand. It's a win-win. The one thing it does have limits on, though, is strict formatting of columns.
If formatting and colors are absolute dealbreakers, the only 100% reliable, cross browser method I've found is to use a server-side language to process proper Excel files from your code. My solution of choice is PHPExcel It is the only one I've found so far that positively handles export with formatting to a MODERN version of Excel from any browser when you give it nothing but HTML. Let me clarify though, it's definitely not as easy as the first solution, and also is a bit of a resource hog. However, on the plus side it also can output direct to PDF as well. And, once you get it configured, it just works, every time.
UPDATE - September 15, 2016: TableTools has been discontinued in favor of a new plugin called "buttons" These tools perform the same functions as the old TableTools extension, but are FAR easier to install and they make use of HTML5 downloads for modern browsers, with the capability to fallback to the original Flash download for browsers that don't support the HTML5 standard. As you can see from the many comments since I posted this response in 2011, the main weakness of TableTools has been addressed. I still can't recommend DataTables enough for handling large amounts of data simply, both for the developer and the user.
很久以前，我发现如果我们以 Excel 内容类型发送它，Excel 会打开一个带有表格的 HTML 文件。考虑上面的文档：
我在上面运行了以下书签：
事实上，我将其作为 Excel 文件下载。 但是，我没有得到预期的结果 - 文件是在 OpenOffice.org Writer 中打开的。这是我的问题：我的机器上没有 Excel，所以我无法更好地尝试。另外，这个技巧或多或少在六年前适用于旧版浏览器和 MS Office 的古董版本，所以我真的不能说它今天是否有效。
无论如何，在上面的文档中，我添加了一个按钮，理论上可以将整个文档下载为 Excel 文件：
将其保存在文件中，然后单击该按钮。我很想知道它是否有效，所以我要求你发表评论，即使是说它不起作用。
A long time ago, I discovered that Excel would open an HTML file with a table if we send it with Excel content type. Consider the document above:
I ran the following bookmarklet on it:
and in fact I got it downloadable as a Excel file. However, I did not get the expected result - the file was open in OpenOffice.org Writer. That is my problem: I do not have Excel in this machine so I cannot try it better. Also, this trick worked more or less six years ago with older browsers and an antique version of MS Office, so I really cannot say if it will work today.
Anyway, in the document above I added a button which would download the entire document as an Excel file, in theory:
Save it in a file and click on the button. I'd love to know if it worked or not, so I ask you to comment even for saying that it did not work.
可以使用旧的 Excel 2003 XML 格式（在 OpenXML 之前）创建一个包含所需 XML 的字符串，然后在客户端，您可以使用数据 URI 使用 XSL mime 类型打开文件，或将文件发送到客户端使用服务器端的 Excel mimetype“Content-Type: application/vnd.ms-excel”。
<脚本类型=“text/javascript”>
var worksheet_template = '
'
'
var row_template = '
收集信息后，创建最终字符串并使用数据 URI 打开一个新窗口
值得注意的是，较旧的浏览器不支持数据 URI 方案，因此您可能需要为那些不支持它的浏览器生成文件服务器端。
您可能还需要对数据URI内容执行base64编码，这可能需要js库，以及在数据 URI 中的 mime 类型后面添加字符串“;base64”。
It is possible to use the old Excel 2003 XML format (before OpenXML) to create a string that contains your desired XML, then on the client side you could use a data URI to open the file using the XSL mime type, or send the file to the client using the Excel mimetype "Content-Type: application/vnd.ms-excel" from the server side.
Once you have the information collected, create the final string and open a new window using the data URI
It is worth noting that older browsers do not support the data URI scheme, so you may need to produce the file server side for those browser that do not support it.
You may also need to perform base64 encoding on the data URI content, which may require a js library, as well as adding the string ';base64' after the mime type in the data URI.
Excel具有一个鲜为人知的功能，称为“ Web查询”，可让您在没有其他编程的情况下从每个网页中检索数据。
Web查询基本上直接从Excel内部运行HTTP请求，并将某些接收到的数据（并选择地格式化）复制到工作表中。
定义Web查询后，您可以随时刷新它，甚至不离开Excel。因此，您不必实际“导出”数据并将其保存到文件中 - 您宁愿像数据库那样刷新数据。
您甚至可以通过让Excel提示您以获取某些过滤条件等来利用URL参数...
但是我注意到到目前为止的缺点是：
在这里是一个问题关于如何在Excel中创建Web查询。它链接到a Microsoft帮助网站有关如何从网页获取外部数据
Excel has a little known feature called "Web queries" which let you retrieve data from almost every web page without additional programming.
A web query basicly runs a HTTP request directly from within Excel and copies some or all of the received data (and optionally formatting) into the worksheet.
After you've defined the web query you can refresh it at any time without even leaving excel. So you don't have to actually "export" data and save it to a file - you'd rather refresh the data just like from a database.
You can even make use of URL parameters by having excel prompt you for certain filter criteria etc...
However the cons I've noticed so far are:
Here is a question about how to create web queries in Excel. It links to a Microsoft Help site about How-To Get external data from a Web page
这是一个 php，但你也许可以将其更改为 javascript：
This is a php but you maybe able to change it to javascript:
首先，我不建议尝试导出 Html 并希望用户的 Excel 实例能够选择它。根据我的经验，该解决方案充满了问题，包括与 Macintosh 客户端不兼容，并向用户抛出错误，表明相关文件不是指定的格式。最安全、用户友好的解决方案是服务器端解决方案，您可以在其中使用库构建实际的 Excel 文件并将其发送回用户。下一个最佳解决方案和更通用的解决方案是使用 Open XML 格式。我遇到了一些与旧版 Excel 的罕见兼容性问题，但总的来说，这应该为您提供一个适用于任何版本的 Excel（包括 Mac）的解决方案。
开放 XML
First, I would not recommend trying export Html and hope that the user's instance of Excel picks it up. My experience that this solution is fraught with problems including incompatibilities with Macintosh clients and throwing an error to the user that the file in question is not of the format specified. The most bullet-proof, user-friendly solution is a server-side one where you use a library to build an actual Excel file and send that back to the user. The next best solution and more universal solution would be to use the Open XML format. I've run into a few rare compatibility issues with older versions of Excel but on the whole this should give you a solution that will work on any version of Excel including Macs.
Open XML
Mozilla 仍然支持 Base 64 URI。这允许您使用 javascript 动态组合二进制内容：
如果您的 excel 文件不是很花哨（没有图表、公式、宏），您可以深入研究格式并为文件组合字节，然后使用 base64 对其进行编码并放入href
参考
https://developer.mozilla.org/en/data_URIs
mozilla still support base 64 URIs. This allows you to compose dynamically the binary content using javascript:
if your excel file is not very fancy (no diagrams, formulas, macroses) you can dig into the format and compose bytes for your file, then encode them with base64 and put in to the href
refer to
https://developer.mozilla.org/en/data_URIs
这实际上比您想象的更简单：“只需”将 HTML 表格（即：表格的 HTML 代码）复制到剪贴板中。 Excel 知道如何解码 HTML 表格；它甚至会尝试保留属性。
困难的部分是“将表复制到剪贴板”，因为没有从 JavaScript 访问剪贴板的标准方法。请参阅此博客文章：使用以下命令访问系统剪贴板JavaScript – 圣杯？
现在您所需要的只是 HTML 格式的表格。我建议使用 jQuery 和 html() 方法。
This is actually more simple than you'd think: "Just" copy the HTML table (that is: The HTML code for the table) into the clipboard. Excel knows how to decode HTML tables; it'll even try to preserve the attributes.
The hard part is "copy the table into the clipboard" since there is no standard way to access the clipboard from JavaScript. See this blog post: Accessing the System Clipboard with JavaScript – A Holy Grail?
Now all you need is the table as HTML. I suggest jQuery and the html() method.
此代码仅适用于 IE，因此仅在您知道所有用户都将使用 IE 的情况下才有用（例如，在某些公司环境中）。
This code is IE only so it is only useful in situations where you know all of your users will be using IE (like, for example, in some corporate environments.)
假设：
给定 url
转换必须在客户端完成
系统是Windows、Mac和linux
Windows的解决方案：
打开ie窗口并可以访问的python代码：
theurl 变量包含 url ('http://')
注意：如果页面无法直接访问，但需要登录，则需要通过输入表单数据并使用 python 模拟用户操作来处理此问题，
示例
这里是相同的 从网页检索数据的方式。假设 id 为“el1”的元素包含数据。
将元素文本检索到变量，
然后当数据在 python 变量中时，您可以使用 python 以类似的方式打开 Excel 屏幕：
Mac 解决方案：
仅提示：使用 AppleScript - 它具有简单和相似的功能API 作为 win32com.client 调度
Linux 解决方案：
java.awt.Robot 可能适用于此，它有单击、按键（可以使用热键），但据我所知，没有适用于 Linux 的 API可以像 AppleScript 一样简单地工作
Assumptions:
given url
the conversion has to be done on client side
systems are Windows, Mac and linux
Solution for Windows:
python code that open the ie window and has access to it:
theurl variable contain the url ('http://')
Note: if the page is not accessible directly, but login, you will need to handle this by entering the form data and emulating the user actions with python
here is the example
the same manner for retrieval of data from web page. Let's say element with id 'el1' contain the data.
retrieve the element text to the variable
then when data is in python variable, you can open the excel screen in similar manner using python:
Solution for Mac:
only the tip: use AppleScript - it has simple and similar API as win32com.client Dispatch
Solution for Linux:
java.awt.Robot might work for this it has click, key press (hot keys can be used), but none API for Linux that I am aware about that can work as simple as AppleScript
简单的谷歌搜索发现了这个：
如果你不能这样做，他提供了另一种“拖放”方法：
http:// /www.mrkent.com/tools/converter/
simple google search turned up this:
if you can't do that, he gives an alternate "drag-and-drop" method:
http://www.mrkent.com/tools/converter/
现在有更好的方法。
用于 JavaScript 的 OpenXML SDK。
https://openxmlsdkjs.codeplex.com/
And now there is a better way.
OpenXML SDK for JavaScript.
https://openxmlsdkjs.codeplex.com/
有两种实用的方法可以自动执行此操作，但只有一种解决方案可以在所有浏览器中使用。
首先，您应该使用 open xml 规范来构建 Excel 工作表。 Microsoft 提供免费插件，使此格式也可用于旧版 Office 版本。自 Office 2007 以来，open xml 已成为标准。这两种方式很明显是服务器端或客户端。
客户端实现使用新的 CSS 标准，允许您存储数据而不仅仅是数据的 URL。这是一个很好的方法，因为您不需要任何服务器调用，只需要数据和一些 JavaScript。致命的缺点是微软不支持当前 IE（我不知道 IE9）版本中的所有部分。微软将数据限制为图像，但我们需要一个文档。在 Firefox 中它工作得很好。对我来说，IE 是致命点。
另一种方法是使用服务器端实现。所有语言都应该有很多开放 XML 的实现。你只需要抓住一个。在大多数情况下，这将是修改视图模型以生成文档的最简单方法，但可以肯定的是，您可以将所有数据从客户端发送回服务器并执行相同的操作。
There are practical two ways to do this automaticly while only one solution can be used in all browsers.
First of all you should use the open xml specification to build the excel sheet. There are free plugins from Microsoft available that make this format also available for older office versions. The open xml is standard since office 2007. The the two ways are obvious the serverside or the clientside.
The clientside implementation use a new standard of CSS that allow you to store data instead of just the URL to the data. This is a great approach coz you dont need any servercall, just the data and some javascript. The killing downside is that microsoft don't support all parts of it in the current IE (I don't know about IE9) releases. Microsoft restrict the data to be a image but we will need a document. In firefox it works quite fine. For me the IE was the killing point.
The other way is to user a serverside implementation. There should be a lot implementations of open XML for all languages. You just need to grap one. In most cases it will be the simplest way to modify a Viewmodel to result in a Document but for sure you can send all data from Clientside back to server and do the same.