将多个html表导出到Excel

I've scavenged the inter web for answers and though I found some, they were mostly incomplete or not working.

我在国际互联网上寻找答案，虽然我发现了一些，但大部分都是不完整的，或者不工作。

What I'm trying to do is: I have a info page which displays information about a customer or server (or something else), this information is displayed in a table, sometimes multiple tables (I sometimes create my own table for some of the data and use Html.Grid(Model.list) to create tables for the rest of the data stored in lists, all on 1 page).

我想做的是:我有一个信息页面显示客户信息或服务器(或其他),这些信息显示在一个表中,有时多个表(有时我创建我自己的表的数据和使用Html.Grid(Model.list)来创建表的数据存储在列表中,所有在1页)。

I found this website which is an awesome: http://www.excelmashup.com/ and does exactly what I want for 1 table, though I need this for multiple tables (they must all be in the same Excel file). I know I can create multiple files (1 for each table) but this is not the desired output.

我发现这个网站非常棒:http://www.excelmashup.com/，它完全符合我对一个表的要求，尽管我需要它来处理多个表(它们必须都在同一个Excel文件中)。我知道我可以创建多个文件(每个表1)，但这不是期望的输出。

So I kept on searching and I found a post on *: Export multiple HTML tables to Excel with JavaScript function

所以我继续搜索，发现了一个关于*的帖子:导出多个HTML表，使用JavaScript函数Excel

This seemed promising so I tried using it but the code had some minor errors which I tried to fix:

这似乎很有希望，所以我尝试使用它，但代码有一些小错误，我试图修复:

var tableToExcel = (function () {
    var uri = 'data:application/vnd.ms-excel;base64,'
        , template = '<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><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>'
        , base64 = function (s) { return window.btoa(unescape(encodeURIComponent(s))) }
        , format = function (s, c) { return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; }) }
    return function (table, name) {
        if (!table.nodeType) table = document.getElementById(table)
        var ctx = { worksheet: name || 'Worksheet', table: table.innerHTML }
        window.location.href = uri + base64(format(template, ctx))
    }
})()

The button I use to trigger it:

我用来触发它的按钮:

<input type="button" onclick="tableToExcel('InformatieTable', 'W3C Example Table')" value="Export to Excel">

but alas to no avail (I did not know what to do with the if (!table.nodeType) table = table line so I just commented it since it seemed to do nothing special).

但是，遗憾的是没有任何用处(我不知道如何处理if (!table. nodetype)表=表行，所以我只是对它进行了注释，因为它似乎没有什么特别之处)。

Now I get an error, or well not really an error but this is what it says when I try to run this code:

现在我得到了一个错误，或者不是真正的错误但是当我试图运行这段代码时，它是这样说的:

Resource interpreted as Document but transferred with MIME type application/vnd.ms-excel: "data:application/vnd.ms-excel;base64,PGh0bWwgeG1sbnM6bz0idXJuOnNjaGVtYXMtbW…JzZXQ9VVRGLTgiLz48L2hlYWQ+PGJvZHk+PHRhYmxlPjwvdGFibGU+PC9ib2R5PjwvaHRtbD4=".

资源解释为文档，但通过MIME类型应用程序/vnd传输。ms excel:“数据:应用程序/ vnd.ms-excel;base64,PGh0bWwgeG1sbnM6bz0idXJuOnNjaGVtYXMtbW…JzZXQ9VVRGLTgiLz48L2hlYWQ + PGJvZHk + PHRhYmxlPjwvdGFibGU + PC9ib2R5PjwvaHRtbD4 = "。

And I get an Excel file as download in my browser but when I try to open it I get an error about the content and file extension not matching and if I would still like to open it. So if I click ok it opens a empty Excel sheet and that's it.

我在浏览器中下载了一个Excel文件但当我试图打开它时，我得到了一个关于内容和文件扩展不匹配的错误如果我还想打开它。如果我点击ok，它会打开一个空的Excel表格，就这样。

I am currently trying to fix that error, though i don't think it will make any difference to the content of the Excel file.

我目前正在尝试修复这个错误，尽管我认为它不会对Excel文件的内容产生任何影响。

Is there anyone that can help me fix this? Or provide an other way of doing this?

有谁能帮我解决这个问题吗?或者提供另一种方法?

I do prefer it to be run client side (so jQuery/java) instead of server side to minimize server load.

我确实希望它是运行客户端(即jQuery/java)，而不是服务器端，以最小化服务器负载。

EDIT

编辑

I've found a better example of the jQuery (one that does work) on http://www.codeproject.com/Tips/755203/Export-HTML-table-to-Excel-With-CSS This converts 1 table into an excel file which is obviously not good enough. But now I have the code to do this so I should be able to adapt it to loop trough all tables on the web page.

我在http://www.codeproject.com/Tips/755203/Export-HTML-table-to-Excel-With-CSS上发现了一个更好的jQuery示例(一个确实有效的例子)，它将一个表转换成一个显然不够好的excel文件。但是现在我有了这样做的代码，所以我应该能够调整它，使它能够遍历web页面上的所有表。

Also updated the code in this example to the correct version I'm using now. I also still get the same error yet when I click on ok when trying to open the Excel file it does show me the content of the table, so I'm just ignoring that for now. anyone who has a solution for this please share.

还将本例中的代码更新为我现在使用的正确版本。我仍然会得到同样的错误当我点击ok时当我试图打开Excel文件时它确实显示了表格的内容，所以我暂时忽略它。任何对此有解决办法的人请分享。

1 个解决方案

#1

Thanks to @Axel Richter I got my answer, he reffered me to the following question

感谢@Axel Richter，我得到了答案，他回答了我下面的问题

I have adapted the code a bit so it would Take all the tables on the web page so it now looks like this:

我对代码做了一些调整，这样它就可以把网页上的所有表格都整合到一起，所以现在看起来是这样的:

<script type="text/javascript">
    var tablesToExcel = (function () {
        var uri = 'data:application/vnd.ms-excel;base64,'
        , tmplWorkbookXML = '<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?><Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">'
          + '<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"><Author>Axel Richter</Author><Created>{created}</Created></DocumentProperties>'
          + '<Styles>'
          + '<Style ss:ID="Currency"><NumberFormat ss:Format="Currency"></NumberFormat></Style>'
          + '<Style ss:ID="Date"><NumberFormat ss:Format="Medium Date"></NumberFormat></Style>'
          + '</Styles>'
          + '{worksheets}</Workbook>'
        , tmplWorksheetXML = '<Worksheet ss:Name="{nameWS}"><Table>{rows}</Table></Worksheet>'
        , tmplCellXML = '<Cell{attributeStyleID}{attributeFormula}><Data ss:Type="{nameType}">{data}</Data></Cell>'
        , base64 = function (s) { return window.btoa(unescape(encodeURIComponent(s))) }
        , format = function (s, c) { return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; }) }
        return function (wsnames, wbname, appname) {
            var ctx = "";
            var workbookXML = "";
            var worksheetsXML = "";
            var rowsXML = "";
            var tables = $('table');
            for (var i = 0; i < tables.length; i++) {
                for (var j = 0; j < tables[i].rows.length; j++) {
                    rowsXML += '<Row>'
                    for (var k = 0; k < tables[i].rows[j].cells.length; k++) {
                        var dataType = tables[i].rows[j].cells[k].getAttribute("data-type");
                        var dataStyle = tables[i].rows[j].cells[k].getAttribute("data-style");
                        var dataValue = tables[i].rows[j].cells[k].getAttribute("data-value");
                        dataValue = (dataValue) ? dataValue : tables[i].rows[j].cells[k].innerHTML;
                        var dataFormula = tables[i].rows[j].cells[k].getAttribute("data-formula");
                        dataFormula = (dataFormula) ? dataFormula : (appname == 'Calc' && dataType == 'DateTime') ? dataValue : null;
                        ctx = {
                            attributeStyleID: (dataStyle == 'Currency' || dataStyle == 'Date') ? ' ss:StyleID="' + dataStyle + '"' : ''
                               , nameType: (dataType == 'Number' || dataType == 'DateTime' || dataType == 'Boolean' || dataType == 'Error') ? dataType : 'String'
                               , data: (dataFormula) ? '' : dataValue.replace('<br>', '')
                               , attributeFormula: (dataFormula) ? ' ss:Formula="' + dataFormula + '"' : ''
                        };
                        rowsXML += format(tmplCellXML, ctx);
                    }
                    rowsXML += '</Row>'
                }
                ctx = { rows: rowsXML, nameWS: wsnames[i] || 'Sheet' + i };
                worksheetsXML += format(tmplWorksheetXML, ctx);
                rowsXML = "";
            }

            ctx = { created: (new Date()).getTime(), worksheets: worksheetsXML };
            workbookXML = format(tmplWorkbookXML, ctx);

            console.log(workbookXML);

            var link = document.createElement("A");
            link.href = uri + base64(workbookXML);
            link.download = wbname || 'Workbook.xls';
            link.target = '_blank';
            document.body.appendChild(link);
            link.click();
            document.body.removeChild(link);
        }
    })();
</script>

so now when ever I want a page to have an option to be exported to excel i add a refference to that script and i add the following button to my page:

所以现在，每当我想要一个页面有一个导出到excel的选项时，我就会在这个脚本中添加一个refference，然后在我的页面中添加以下按钮:

<button onclick="tablesToExcel(['ServerInformatie', 'Relaties'], 'VirtueleMachineInfo.xls', 'Excel')">Export to Excel</button>

so the method:

因此,方法:

tablesToExcel(WorksheetNames, fileName, 'Excel')

Where worksheetNames is an array which needs to contain as much names (or more) as there are tables on the page. You could ofcourse chose to create the worksheet names in a different way. And where fileName is ofcourse the name of the file you'll be downloading.

worksheetNames是一个数组，它需要包含与页面上的表一样多(或更多)的名称。当然，您可以选择以不同的方式创建工作表名称。文件名当然是你要下载的文件的名称。

Not having it all in 1 worksheet is a shame but at least this will do for now.

没有在一个工作表中全部是一个遗憾，但至少这将是现在。

#1