如何从网页导出表格?

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

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

14 个解决方案

#1

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

如果格式化和颜色是绝对的dealbreakers，唯一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为现代浏览器下载,与回退到原始的能力下载Flash浏览器不支持HTML5标准。正如您在2011年发布此响应后的许多评论中所看到的，TableTools的主要缺点已经得到解决。我仍然不能推荐足够的数据来处理大量的数据，无论是对于开发人员还是用户。

#2

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，所以我不能做得更好。同样，这一技巧在六年前使用的是老式浏览器和微软办公软件的古董版本，所以我真的不能说它是否能在今天工作。

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

保存在文件中并单击按钮。我很想知道它是否有用，所以我请您评论一下，甚至说它不起作用。

#3

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盾”内容类型:应用程序/。服务器端的ms-excel。

Open Excel and create a worksheet with your desired formatting and colors.
打开Excel，用你想要的格式和颜色创建一个工作表。
Save the Excel workbook as "XML Spreadsheet 2003 (*.xml)"
将Excel工作簿保存为“XML Spreadsheet 2003 (*.xml)”
Open the resulting file in a text editor like notepad and copy the value into a string in your application
在文本编辑器中打开结果文件，如notepad，并将该值复制到应用程序中的字符串中。

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>

假设您使用数据uri的客户端方法，代码如下:

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

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


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

window.open('data:application/vnd.ms-excel,'+worksheet); </script> 
window.open('数据:应用程序/ vnd.ms-excel,' +工作表);> < /脚本

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

#4

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

Excel有一个鲜为人知的特性叫做“Web查询”，它可以让您从几乎每个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 query basicly直接从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...

你甚至可以使用URL参数，通过excel提示你有一定的过滤标准等等。

However the cons I've noticed so far are:

然而，到目前为止我注意到的缺点是:

dynamicly loaded data is not accessible, because Javascript is not executed
动态加载的数据是不可访问的，因为Javascript没有执行。
URL length is limited
URL长度是有限的

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

这里有一个关于如何在Excel中创建web查询的问题。它链接到一个微软帮助网站关于如何从一个网页获取外部数据。

#5

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

#6

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.

首先，我不建议尝试导出Html，并希望用户的Excel实例能够将其提取出来。我的经验是，这个解决方案充满了问题，包括与Macintosh客户的不兼容，并向用户抛出一个错误的文件，而不是指定的格式。最防弹的、用户友好的解决方案是一个服务器端，您可以使用一个库来构建一个实际的Excel文件并将其发送回用户。下一个最佳解决方案和更通用的解决方案是使用开放XML格式。我已经遇到了一些较老版本的Excel的兼容性问题，但总的来说，这应该会给你一个解决方案，可以在任何版本的Excel中工作，包括mac。

Open XML

#7

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

mozilla仍然支持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

#8

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>

#9

Assumptions:

假设:

given url

给定的url
the conversion has to be done on client side

转换必须在客户端完成。
systems are Windows, Mac and linux

系统是Windows、Mac和linux。

Solution for Windows:

解决方案:

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

打开ie窗口并访问它的python代码:url变量包含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

从web页面检索数据的方式相同。假设元素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:

麦克:解决方案

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

只有提示:使用AppleScript——它有简单和相似的API作为win32com。客户端发送

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。机器人可能会为它工作，它有点击，按键(可以使用热键)，但是我所知道的Linux的API都不能像AppleScript那样简单。

#10

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()方法。

#11

simple google search turned up this:

简单的谷歌搜索结果如下:

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.

如果数据实际上是一个HTML页面，而不是由ASP、PHP或其他脚本语言创建的，那么您使用的是Internet Explorer 6，您的计算机上安装了Excel，只需右键单击该页面并查看菜单。您应该看到“导出到Microsoft Excel”。如果所有这些条件都是正确的，单击菜单项，并在一些提示之后将导入Excel。

if you can't do that, he gives an alternate "drag-and-drop" method:

如果你不能这样做，他会给出另一个“拖放”方法:

http://www.mrkent.com/tools/converter/

#12

And now there is a better way.

现在有了更好的方法。

OpenXML SDK for JavaScript.

对JavaScript OpenXML SDK。

https://openxmlsdkjs.codeplex.com/

#13

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.

有两种方法可以自动实现这一功能，而只有一个解决方案可以在所有浏览器中使用。首先，您应该使用open xml规范来构建excel表。微软提供的免费插件使这种格式也适用于旧的office版本。开放xml是自2007年以来的标准。这两种方式显然是服务器端或客户端。

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.

clientside实现使用了一个新的CSS标准，它允许您存储数据而不仅仅是数据的URL。这是一个很棒的方法，因为你不需要任何服务器调用，只需要数据和一些javascript。致命的缺点是微软不支持当前IE中所有的部分(我不知道IE9)。微软将数据限制为图像，但我们需要一个文档。在firefox中，它运行得很好。对我来说，IE就是杀手锏。

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.

另一种方法是使用服务器端实现。对于所有语言，都应该有很多开放XML的实现。你只需要grap一。在大多数情况下，它将是修改Viewmodel以产生文档的最简单的方法，但您肯定可以将所有数据从Clientside发送回服务器并执行相同的操作。

#14

   function normalexport() {

       try {
           var i;
           var j;
           var mycell;
           var tableID = "tblInnerHTML";
           var drop = document.getElementById('<%= ddl_sections.ClientID %>');
           var objXL = new ActiveXObject("Excel.Application");
           var objWB = objXL.Workbooks.Add();
           var objWS = objWB.ActiveSheet;
           var str = filterNum(drop.options[drop.selectedIndex].text);
           objWB.worksheets("Sheet1").activate; //activate dirst worksheet
           var XlSheet = objWB.activeSheet; //activate sheet
           XlSheet.Name = str; //rename


           for (i = 0; i < document.getElementById("ctl00_ContentPlaceHolder1_1").rows.length - 1; i++) {
               for (j = 0; j < document.getElementById("ctl00_ContentPlaceHolder1_1").rows(i).cells.length; j++) {
                   mycell = document.getElementById("ctl00_ContentPlaceHolder1_1").rows(i).cells(j);

                   objWS.Cells(i + 1, j + 1).Value = mycell.innerText;

                   //                                                objWS.Cells(i + 1, j + 1).style.backgroundColor = mycell.style.backgroundColor;
               }
           }

           objWS.Range("A1", "L1").Font.Bold = true;
           //                objWS.Range("A1", "L1").Font.ColorIndex = 2;
           //                 objWS.Range("A1", "Z1").Interior.ColorIndex = 47;

           objWS.Range("A1", "Z1").EntireColumn.AutoFit();

           //objWS.Range("C1", "C1").ColumnWidth = 50;

           objXL.Visible = true;

       } catch (err) {
           alert("Error. Scripting for ActiveX might be disabled")
           return
       }
       idTmr = window.setInterval("Cleanup();", 1);

   }


   function filterNum(str) {

       return str.replace(/[ / ]/g, '');
   }

#1