方案一：支持IE
//导出excel function exportExcel(DivID,strTitle){ if(DivID==null) { return false; } var jXls, myWorkbook, myWorksheet; try { jXls = new ActiveXObject('Excel.Application'); } catch (e) { alert("无法启动Excel! 如果您确信您的电脑中已经安装了Excel，"+"那么请调整IE的安全级别。 具体操作： "+"工具 → Internet选项 → 安全 → 自定义级别 → 对 没有标记为安全的ActiveX进行初始化和脚本运行 → 启用"); return false; } jXls.DisplayAlerts = false; myWorkbook = jXls.Workbooks.Add(); var curTb = document.getElementById(DivID); myWorksheet = myWorkbook.ActiveSheet; myWorksheet.name=strTitle; var sel = document.body.createTextRange(); sel.moveToElementText(curTb); sel.select(); window.clipboardData.setData('text',''); sel.execCommand("Copy"); myWorksheet.Paste(); jXls.Visible = true; try{ var fname = jXls.Application.GetSaveAsFilename("OA数据"+strTitle+".xls", "Excel Spreadsheets (*.xls), *.xls"); }catch(e){ print("Nested catch caught " + e); } finally{ if(fname!=false) { myWorkbook .SaveAs(fname); alert("数据成功保存在:"+fname); } } // myWorkbook .Close(savechanges=false); // jXls.Quit(); window.clipboardData.setData('text',''); jXls = null; myWorkbook = null; myWorksheet = null; }
<table class="oa-el-grid-list" id="tb" style="99%" cellspacing="0" cellpadding="0" border="0"> <thead> <%=tab_html%> </thead> <%=TableHtml %> </table> <input id="btnSavExcel" onclick="exportExcel('tb','……表')" type="button" value="导出" />
方案2
<input type="button" onclick="tableToExcel('tablename', 'name')" value="Export to Excel">
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)) } })()
方案3
<a id="dlink" style="display:none;"></a> <input type="button" onclick="tableToExcel('tablename', 'name', 'myfile.xls')" value="Export to Excel">
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, filename) { if (!table.nodeType) table = document.getElementById(table) var ctx = { worksheet: name || 'Worksheet', table: table.innerHTML } document.getElementById("dlink").href = uri + base64(format(template, ctx)); document.getElementById("dlink").download = filename; document.getElementById("dlink").click(); } })()
支持：Chrome、Firefox、Opera、Safari
根据Json导出Excel
; !function () { layui.use(['layer', 'element'], function () { var tableTitle = [{ "value": "行政区", "type": "ROW_HEADER_HEADER", "datatype": "string" }, { "value": "留守儿童(人)", "type": "ROW_HEADER_HEADER", "datatype": "string" }, { "value": "困境儿童(人)", "type": "ROW_HEADER_HEADER", "datatype": "string" },{"value": "合计(人)", "type": "ROW_HEADER_HEADER", "datatype": "string" }] $(".querynav a:eq(2)").click(function (param) { var tableData=[]; layer.confirm("确认导出当前页数据？",function(params) { $.each($(".tableBody tr"),function (i,lay) { var data=[]; layer.closeAll(); for(i=0;i<$(this).children().length;i++){ var a="{value:'"+$(this).children().eq(i).text()+"',type:'ROW_HEADER'}"; data.push( eval("(" + a + ")")); } tableData.push(data); console.log(data.toString()); }); console.log(tableData.toString()); var cityName=""; if ($("#Town").val() != "") { civilregionalismcode += 'civilregionalismcode=' + $("#Town").val(); } else if ($("#Country").val() != "") { civilregionalismcode += 'civilregionalismcode=' + $("#Country").val(); } else if ($("#city").val() != "") { civilregionalismcode += 'civilregionalismcode=' + $("#city").val(); } else if ($("#province").val() != "") { civilregionalismcode += 'civilregionalismcode=' + $("#province").val(); } // return; JSONToExcelConvertor(tableData, "测试数据", tableTitle) }) }); function JSONToExcelConvertor(JSONData, FileName, ShowLabel) { //先转化json var arrData = typeof JSONData != 'object' ? JSON.parse(JSONData) : JSONData; var excel = '<table>'; //设置表头 var row = "<tr>"; for (var i = 0, l = ShowLabel.length; i < l; i++) { row += "<td>" + ShowLabel[i].value + '</td>'; } //换行 excel += row + "</tr>"; console.log(arrData); // return; //设置数据 for (var i = 0; i < arrData.length; i++) { var row = "<tr>"; for (var index in arrData[i]) { var value = arrData[i][index].value === "." ? "" : arrData[i][index].value; row += '<td>' + value + '</td>'; } excel += row + "</tr>"; } excel += "</table>"; var excelFile = "<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'>"; excelFile += '<meta http-equiv="content-type" content="application/vnd.ms-excel; charset=UTF-8">'; excelFile += '<meta http-equiv="content-type" content="application/vnd.ms-excel'; excelFile += '; charset=UTF-8">'; excelFile += "<head>"; excelFile += "<!--[if gte mso 9]>"; excelFile += "<xml>"; excelFile += "<x:ExcelWorkbook>"; excelFile += "<x:ExcelWorksheets>"; excelFile += "<x:ExcelWorksheet>"; excelFile += "<x:Name>"; excelFile += "{worksheet}"; excelFile += "</x:Name>"; excelFile += "<x:WorksheetOptions>"; excelFile += "<x:DisplayGridlines/>"; excelFile += "</x:WorksheetOptions>"; excelFile += "</x:ExcelWorksheet>"; excelFile += "</x:ExcelWorksheets>"; excelFile += "</x:ExcelWorkbook>"; excelFile += "</xml>"; excelFile += "<![endif]-->"; excelFile += "</head>"; excelFile += "<body>"; excelFile += excel; excelFile += "</body>"; excelFile += "</html>"; var uri = 'data:application/vnd.ms-excel;charset=utf-8,' + encodeURIComponent(excelFile); var link = document.createElement("a"); link.href = uri; link.style = "visibility:hidden"; link.download = FileName + ".xls"; document.body.appendChild(link); link.click(); document.body.removeChild(link); } }) } ()
方案4：
/*将JqGrid导出Excel（中文）*/ <script src="/UILib/tableExport.jquery.plugin-master/libs/FileSaver/FileSaver.min.js"></script> <script src="/UILib/tableExport.jquery.plugin-master/tableExport.min.js"></script> <script type="text/javascript"> //导出数据 function ExportData() { var tbId = "datagrid"; //var $tb = $("#" + tbId).clone(); var $tbst = $("table[aria-labelledby='gbox_" + tbId + "']"); $tbst.find("td:hidden").remove(); var $tbs = $tbst.clone(); $tbs.find("span").remove(); var $tb; if ($tbs.length > 1) { var $tbody = $($tbs[1]).children("tbody"); $tbody.children("tr[class='jqgfirstrow']").remove(); $($tbs[0]).append($tbody); $tb = $($tbs[0]); //$tb.children("tbody tr[class='jqgfirstrow']").remove(); $tb.children("thead").children("tr[class='jqg-first-row-header']"); $tb.attr("id", "tb_Temp_Export"); //$tb.hide();//隐藏之后出现导出为空 var $div = $("<div style='0px;height:0px;overflow:hidden;z-index:-1;'></div>").append($tb); $(document.body).append($div); var tbRepName = "数据报表"; var exName = tbRepName;//+ "(" + $('#txtSTime').val() + ")"; $tb.tableExport({ fileName: exName, type: 'excel', worksheetName: [tbRepName] , mso: { styles: ['background-color', 'background', 'color', 'font-family', 'font-size', 'font-weight', 'text-align', 'height', 'width'] } }); setTimeout(function () { $div.remove(); }, 2000); } else { alert("操作失败，请刷新后重试！"); } } </script>
注：加入styles样式时，可能会导出报错
多个Table导出多个Sheet
<html lang="en"> <head> <meta charset="UTF-8"> <title>ExportTablesToExcel</title> </head> <style> #tabDiv1,#tabDiv2,#tabDiv3{border:1px solid pink;margin:10px auto;width:100%; } button{width:100%;} </style> <body> <div id="tablesDiv"> <table id="tabDiv1"> <tr> <td>ID</td> <td>姓名</td> <td>年龄</td> </tr> <tr> <td>0001</td> <td>张三</td> <td>24</td> </tr> </table> <table id="tabDiv2"> <tr> <td>ID</td> <td>姓名</td> <td>年龄</td> </tr> <tr> <td>0002</td> <td>李四</td> <td>24</td> </tr> </table> <table id="tabDiv3"> <tr> <td>ID</td> <td>姓名</td> <td>年龄</td> </tr> <tr> <td>0003</td> <td>王五</td> <td>24</td> </tr> </table> <button οnclick="exp();">export to excel...</button> </div> </body> <script> function exp(){ tablesToExcel(['tabDiv1','tabDiv2','tabDiv3'], ['sheet1','sheet2','sheet3'], "testExport.xls", "Excel"); } //导出excel包含多个sheet //tables:tableId的数组;wsbames:sheet的名字数组;wbname:工作簿名字;appname:Excel function tablesToExcel(tables, wsnames, wbname, appname){ 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]; }) } var ctx = ""; var workbookXML = ""; var worksheetsXML = ""; var rowsXML = ""; for (var i = 0; i < tables.length; i++) { if (!tables[i].nodeType) tables[i] = document.getElementById(tables[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 , 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> </html>
输出内容：
<?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> hy </Author> <Created> hy31337 </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> <Worksheet ss:Name="sheet1"> <Table> <Row> <Cell> <Data ss:Type="String"> ID </Data> </Cell> <Cell> <Data ss:Type="String"> 姓名 </Data> </Cell> <Cell> <Data ss:Type="String"> 年龄 </Data> </Cell> </Row> <Row> <Cell> <Data ss:Type="String"> 0001 </Data> </Cell> <Cell> <Data ss:Type="String"> 张三 </Data> </Cell> <Cell> <Data ss:Type="String"> 24 </Data> </Cell> </Row> </Table> </Worksheet> <Worksheet ss:Name="sheet2"> <Table> <Row> <Cell> <Data ss:Type="String"> ID </Data> </Cell> <Cell> <Data ss:Type="String"> 姓名 </Data> </Cell> <Cell> <Data ss:Type="String"> 年龄 </Data> </Cell> </Row> <Row> <Cell> <Data ss:Type="String"> 0002 </Data> </Cell> <Cell> <Data ss:Type="String"> 李四 </Data> </Cell> <Cell> <Data ss:Type="String"> 24 </Data> </Cell> </Row> </Table> </Worksheet> <Worksheet ss:Name="sheet3"> <Table> <Row> <Cell> <Data ss:Type="String"> ID </Data> </Cell> <Cell> <Data ss:Type="String"> 姓名 </Data> </Cell> <Cell> <Data ss:Type="String"> 年龄 </Data> </Cell> </Row> <Row> <Cell> <Data ss:Type="String"> 0003 </Data> </Cell> <Cell> <Data ss:Type="String"> 王五 </Data> </Cell> <Cell> <Data ss:Type="String"> 24 </Data> </Cell> </Row> </Table> </Worksheet> </Workbook>
其它案例：
git_demo
https://github.com/kayalshri/tableExport.jquery.plugin/blob/master/tableExport.js