Javascript 如何将表格从网页导出到 Excel

Question

提问by code511788465541441

How can I export tables to Excel from a webpage. I want the export to contain all the formatting and colours.

如何将表格从网页导出到 Excel。我希望导出包含所有格式和颜色。

Answer 1

回答by bpeterson76

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.

毫无疑问，从表格到 Excel 的最干净、最简单的导出是Jquery DataTables 表格工具插件。您会得到一个网格，可以对数据进行排序、过滤、排序和分页，只需添加几行额外的代码和两个小文件，您就可以导出到 Excel、PDF、CSV、剪贴板和打印机。

This is all the code that's required:

这是所需的所有代码：

  $(document).ready( function () {
    $('#example').dataTable( {
        "sDom": 'T<"clear">lfrtip',
        "oTableTools": {
            "sSwfPath": "/swf/copy_cvs_xls_pdf.swf"
        }
    } );
} );

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

如果格式和颜色是绝对的破坏者，那么我发现的唯一 100% 可靠的跨浏览器方法是使用服务器端语言从您的代码处理正确的 Excel 文件。我选择的解决方案是PHPExcel它是迄今为止我发现的唯一一个可以在您只提供 HTML 时从任何浏览器积极处理导出格式到现代版本的 Excel 的解决方案。不过让我澄清一下，它绝对不像第一个解决方案那么容易，而且有点资源浪费。但是，从好的方面来说，它也可以直接输出为 PDF。而且，一旦您对其进行了配置，它每次都可以正常工作。

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.

更新 - 2016 年 9 月 15 日：TableTools 已停产，取而代之的是一个名为“按钮”的新插件。这些工具执行与旧 TableTools 扩展相同的功能，但更容易安装，并且它们使用现代浏览器的 HTML5 下载，具有回退到不支持 HTML5 标准的浏览器的原始 Flash 下载的功能。从我在 2011 年发布此回复以来的许多评论中可以看出，TableTools 的主要弱点已经得到解决。对于开发人员和用户来说，我仍然不能推荐足以简单地处理大量数据的 DataTables。

Answer 2

回答by brandizzi

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:

很久以前，我发现如果我们使用 Excel 内容类型发送一个带有表格的 HTML 文件，Excel 会打开它。考虑上面的文件：

<!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>
  </table>    
</body>
</html>

I ran the following bookmarklet on it:

我在上面运行了以下书签：

javascript:window.open('data:application/vnd.ms-excel,'+document.documentElement.innerHTML);

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.

事实上，我把它作为 Excel 文件下载了。但是，我没有得到预期的结果 - 该文件是在 OpenOffice.org Writer 中打开的。那是我的问题：我在这台机器上没有 Excel，所以我无法尝试更好。此外，这个技巧在六年前或多或少适用于较旧的浏览器和 MS Office 的古董版本，所以我真的不能说它今天是否有效。

Anyway, in the document above I added a button which would download the entire document as an Excel file, in theory:

无论如何，在上面的文档中，我添加了一个按钮，理论上可以将整个文档下载为 Excel 文件：

<!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 loveto know if it worked or not, so I ask you to comment even for saying that it did not work.

将其保存在一个文件中，然后单击按钮。我喜欢知道它的工作或没有，所以我问你甚至说，它没有工作发表评论。

Answer 3

回答by samshull

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.

可以使用旧的Excel 2003 XML 格式（在 OpenXML 之前）创建包含所需 XML 的字符串，然后在客户端使用数据 URI 使用 XSL mime 类型打开文件，或发送文件从服务器端使用 Excel mimetype "Content-Type: application/vnd.ms-excel" 到客户端。

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);

window.open('data:application/vnd.ms-excel,'+worksheet);
</script>

打开 Excel 并使用所需的格式和颜色创建工作表。
将 Excel 工作簿另存为“XML 电子表格 2003 (*.xml)”
在记事本等文本编辑器中打开生成的文件，并将值复制到应用程序中的字符串中

假设您使用带有数据 uri 的客户端方法，代码将如下所示：


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

然后您可以使用字符串替换来创建要插入到工作表模板中的行集合


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

收集信息后，创建最终字符串并使用数据 URI 打开一个新窗口


<script type="text/javascript">
var worksheet = worksheet_template.replace('{{ROWS}}', row_data);

window.open('data:application/vnd.ms-excel,'+worksheet);
</script>

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.

值得注意的是，较旧的浏览器不支持数据 URI 方案，因此您可能需要为不支持它的浏览器生成文件服务器端。

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.

您可能还需要对数据 URI 内容执行 base64 编码，这可能需要一个js 库，以及在数据 URI 中的 mime 类型后添加字符串 ';base64'。

Answer 4

回答by HAL 9000

Excel has a little known feature called "Web queries" which let you retrieve data from almost every web page without additional programming.

Excel 有一个鲜为人知的功能，称为“Web 查询”，它使您无需额外编程即可从几乎每个网页中检索数据。

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.

Web 查询基本上直接从 Excel 中运行 HTTP 请求，并将部分或全部接收到的数据（以及可选的格式）复制到工作表中。

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.

定义 Web 查询后，您可以随时刷新它，甚至无需离开 excel。因此，您不必实际“导出”数据并将其保存到文件中——您宁愿像从数据库中一样刷新数据。

You can even make use of URL parameters by having excel prompt you for certain filter criteria etc...

您甚至可以通过让 excel 提示您输入某些过滤条件等来使用 URL 参数...

However the cons I've noticed so far are:

但是，到目前为止我注意到的缺点是：

dynamicly loaded data is not accessible, because Javascript is not executed
URL length is limited

动态加载的数据不可访问，因为没有执行Javascript
URL长度有限制

Hereis 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

这是一个关于如何在 Excel 中创建 Web 查询的问题。它链接到关于如何从网页获取外部数据的Microsoft 帮助站点

Answer 5

回答by Thomas

First, I would notrecommend 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.

首先，我不建议尝试导出 Html 并希望用户的 Excel 实例能够使用它。我的经验是，此解决方案充满了问题，包括与 Macintosh 客户端不兼容，并向用户抛出错误，指出相关文件不是指定的格式。最安全、用户友好的解决方案是服务器端解决方案，您可以在其中使用库来构建实际的 Excel 文件并将其发送回用户。下一个最佳解决方案和更通用的解决方案是使用 Open XML 格式。我在旧版本的 Excel 中遇到了一些罕见的兼容性问题，但总的来说，这应该为您提供一个适用于任何版本的 Excel 的解决方案，包括 Mac。

Open XML

打开 XML

Answer 6

回答by RedSoxFan

This is a php but you maybe able to change it to javascript:

这是一个 php，但您也许可以将其更改为 javascript：

<?php>
$colgroup = str_repeat("<col width=86>",5);
$data = "";
$time = date("M d, y g:ia");
$excel = "<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\">
<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\" \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\">
<html>
<head>
<meta http-equiv=\"Content-type\" content=\"text/html;charset=utf-8\" />
<style id=\"Classeur1_16681_Styles\">
.xl4566 {
color: red;
}
</style>
</head>
<body>
<div id=\"Classeur1_16681\" align=center x:publishsource=\"Excel\">
<table x:str border=0 cellpadding=0 cellspacing=0 style=\"border-collapse: collapse\">
<colgroup>$colgroup</colgroup>
<tr><td class=xl2216681><b>Col1</b></td><td class=xl2216681><b>Col2</b></td><td class=xl2216681 ><b>Col3</b></td><td class=xl2216681 ><b>Col4</b></td><td class=xl2216681 ><b>Col5</b></td></tr>
<tr><td class=xl4566>1</td><td>2</td><td>3</td><td>4</td><td>5</td></tr>
</table>
</div>
</body>
</html>";
  $fname = "Export".time().".xls";
  $file = fopen($fname,"w+");
  fwrite($file,$excel);
  fclose($file);
  header('Content-Type: application/vnd.ms-excel');
  header('Content-Disposition: attachment; filename="'.basename($fname).'"');
  readfile($fname);
  unlink($fname); ?>

Answer 7

回答by Pavlonator

mozilla still support base 64 URIs. This allows you to compose dynamically the binary content using javascript:

mozilla 仍然支持 base 64 URI。这允许您使用 javascript 动态组合二进制内容：

<a href="data:application/vnd.ms-excel<base64 encoded binary excel content here>"> download xls</a>

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

如果您的 excel 文件不是很花哨（没有图表、公式、宏），您可以深入了解格式并为您的文件组合字节，然后用 base64 对其进行编码并放入 href

refer to https://developer.mozilla.org/en/data_URIs

参考 https://developer.mozilla.org/en/data_URIs

Answer 8

回答by Pavlonator

Assumptions:

假设：

given url
the conversion has to be done on client side
systems are Windows, Mac and linux

给定的网址
转换必须在客户端完成
系统是 Windows、Mac 和 linux

Solution for Windows:

Windows 解决方案：

python code that open the ie window and has access to it: theurl variable contain the url ('http://')

打开 ie 窗口并可以访问它的 python 代码：theurl 变量包含 url ('http://')

ie = Dispatch("InternetExplorer.Application")
ie.Visible = 1
ie.Navigate(theurl)

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

注意：如果页面不能直接访问，而是登录，则需要通过输入表单数据并使用 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

从网页中检索数据的方式相同。假设 id 为 'el1' 的元素包含数据。检索元素文本到变量

el1 = ie.Document.all('el1').value

then when data is in python variable, you can open the excel screen in similar manner using python:

然后当数据在python变量中时，您可以使用python以类似方式打开excel屏幕：

from win32com.client import Dispatch
xlApp = Dispatch("Excel.Application")
xlWb = xlApp.Workbooks.Open("Read.xls")
xlSht = xlWb.WorkSheets(1)
xlSht.Cells(row, col).Value = el1

Solution for Mac:

Mac 解决方案：

only the tip: use AppleScript - it has simple and similar API as win32com.client Dispatch

唯一的提示：使用 AppleScript - 它具有与 win32com.client Dispatch 类似的简单 API

Solution for Linux:

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

java.awt.Robot 可能为此工作，它具有点击、按键（可以使用热键），但我知道没有任何适用于 Linux 的 API 可以像 AppleScript 一样简单

Answer 9

回答by Aaron Digulla

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.

这实际上比您想象的要简单：“只需”将 HTML 表格（即：表格的 HTML 代码）复制到剪贴板中。Excel 知道如何解码 HTML 表格；它甚至会尝试保留属性。

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?

困难的部分是“将表复制到剪贴板”，因为没有从 JavaScript 访问剪贴板的标准方法。请参阅此博客文章：使用 JavaScript 访问系统剪贴板 – 圣杯？

Now all you need is the table as HTML. I suggest jQuery and the html()method.

现在您需要的只是 HTML 形式的表格。我建议使用 jQuery 和html()方法。

Answer 10

回答by NakedBrunch

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

此代码仅适用于 IE，因此仅在您知道所有用户都将使用 IE 的情况下才有用（例如，在某些公司环境中。）

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

Javascript 如何将表格从网页导出到 Excel

提问by code511788465541441

回答by bpeterson76

回答by brandizzi

回答by samshull

回答by HAL 9000

回答by Thomas

回答by RedSoxFan

回答by Pavlonator

回答by Pavlonator

回答by Aaron Digulla

回答by NakedBrunch

相关推荐

最近更新

标签

Javascript 如何将表格从网页导出到 Excel

提问by code511788465541441

回答by bpeterson76

回答by brandizzi

回答by samshull

回答by HAL 9000

回答by Thomas

回答by RedSoxFan

回答by Pavlonator

回答by Pavlonator

回答by Aaron Digulla

回答by NakedBrunch

相关推荐

javascript 属性名称中是否允许使用破折号？

Javascript 用javascript模拟左右箭头键事件

Javascript 如何将参数传递给promise函数

Javascript 刷新引导表

相关推荐

最近更新

标签