使用javascript将HTML表导出到excel中 [英] Exporting HTML table into excel using javascript
问题描述
我想将表导出为excel格式,但是当我做文本字段和列标题的按钮,编辑和删除按钮也被导出到我不想要的excel文件。
任何人都可以告诉我我在javascript中犯了什么错误。
这是我从网上得到的jquery代码( http://jsfiddle.net/insin/cmewv/ )
< script type =text / javascript>
var tableToExcel =(function(){
var uri ='data:application / vnd.ms-excel; base64,'
,template ='< html xmlns:o =urn :schemas-microsoft-com:office:officexmlns:x =urn:schemas-microsoft-com:office:excelxmlns =http://www.w3.org/TR/REC-html40>< ; head><! - [if gte mso 9]>< xml>< x:ExcelWorkbook>< x:ExcelWorksheets>< x:ExcelWorksheet>< x:Name> {worksheet} X:名称>< X:WorksheetOptions>< X:DisplayGridlines />< / X:WorksheetOptions>< / X:ExcelWorksheet>< / X:ExcelWorksheets>< / X:ExcelWorkbook>< / XML> ;<![endif] - >< / head>< body>< table> {table}< / table>< / body>< / html>'
,base64 =函数{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.getElementBy Id(table)
var ctx = {worksheet:name || 'worksheet',table:table.innerHTML}
window.location.href = uri + base64(format(template,ctx))
}
})()
< /脚本>
我的HTML代码如下
< TABLE id =table_idclass =displayalign =Centerborder =1pxwidth =80%>
< thead>
< tr>
< th> < b> User_ID< / th>< / b>
< form action =SearchIdmethod =post>
< input type =hiddenname =hiddennamevalue =hidden_uid>
< input type =textname =uidid =uid>
< input type =submitvalue =Search>
< / form>
< b> User_Name< / th>< / b>
< form action =SearchIdmethod =post>
< input type =textname =unameid =uname>
< input type =hiddenname =hiddennamevalue =hidden_uname>
< input type =submitvalue =Search>
< / form>
< b> Password< / th>< / b>
< form action =SearchIdmethod =post>
< input type =textname =passid =pass>
< input type =hiddenname =hiddennamevalue =hidden_pass>
< input type =submitvalue =Search>
< / form>
< b>指定< / th>< / b>
< form action =SearchIdmethod =post>
< input type =textname =desigid =desig>
< input type =hiddenname =hiddennamevalue =hidden_desig>
< input type =submitvalue =Search>
< / form>
< / thead>
< tbody>
<%Iterator itr;%>
<%List data =(List)request.getAttribute(UserData);
for(itr = data.iterator(); itr.hasNext();)
{%>
< tr>
<%String s =(String)itr.next(); %GT;
< td><%= s%>< / td>
< td><%= itr.next()%>< / td>
< td><%= itr.next()%>< / td>
< td><%= itr.next()%>< / td>
< form id =editaction =EditRecordmethod =post>
< td>< input type =hiddenname =hidden_editid =edit_idvalue =<%= s%>/>
< input type =submitid =myButtonvalue =Editname =editonclick =toggleVisibility('');> < / TD>
< / form>
< td>< form id =deleteaction =DeleteRecordmethod =post>
< td>< input type =hiddenname =hidden_deleteid =delete_idvalue =<%= s%>/>
< input type =submitvalue =deletename =delete> < / TD>
< / form>< / td>
<%}%>
< / tr>
< / tbody>
< / TABLE>
尝试设置标签,具有按钮和编辑文本有一个类来识别这个domss例如:Class ='ignore'。
克隆之后,这个yout表和克隆表删除了所有设置了类的dom。
删除这个dom后,克隆表将导出到excel。
编辑-------
尝试这个。
js脚本
var tableToExcel =(function(){
var uri ='data:application / vnd.ms-excel; base64,',template ='< html xmlns:o =urn:schemas-microsoft -com:office:officexmlns:x =urn:schemas-microsoft-com:office:excelxmlns =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(enc odeURIComponent(s))
},format = function(s,c){
return s.replace(/ {(\w +)} / g,function(m,p){
return c [p];
})
}
返回函数(表,名称){
if(!table.nodeType)
table = document.getElementById(table);
var cln = table.cloneNode(true);
var paras = cln.getElementsByClassName('ignore');
while(paras [0]){
paras [0] .parentNode.removeChild(paras [0]);
}
var ctx = {
工作表:名称|| '工作表',
表:cln.innerHTML
}
window.location.href = uri + base64(format(template,ctx))
}
}) ();
HTML示例
code>< input type =button
onclick =tableToExcel('testTable','W3C Example Table')
value =导出到Excel>
< table id =testTable
summary =不同版本的MS Windows中的代码页支持。
rules =groupsframe =hsidesborder =2>
< caption> MICROSOFT WINDOWS中的CODE-PAGE支持< / caption>
< colgroup align =center>< / colgroup>
< colgroup align =left>< / colgroup>
< colgroup span =2align =center>< / colgroup>
< colgroup span =3align =center>< / colgroup>
< thead valign =top>
< tr>
< th>代码页< br> ID
< / th>
< th> Name< / th>
< th> ACP< / th>
> OEMCP< / th>
< th> Windows< br> NT 3.1
< / th>
< th> Windows< br> NT 3.51
< / th>
< th> Windows< br> 95
< / th>
< / tr>
< / thead>
< tbody>
< tr>
< td> 1200< / td>
< td style =background-color:#00f; color:#fff> Unicode(BMP
ISO / IEC-10646)< / td>
< td>< / td>
< td>< / td>
< td> X< / td>
< td> X< / td>
< td> *< / td>
< / tr>
< tr>
< td> 1250< / td>
< td style =font-weight:bold> Windows 3.1东欧< / td>
< td> X< / td>
< td>< / td>
< td> X< / td>
< td> X< / td>
< td> X< / td>
< / tr>
< tr>
< td> 1251< / td>
< td> Windows 3.1西里尔文< / td>
< td> X< / td>
< td>< / td>
< td> X< / td>
< td> X< / td>
< td> X< / td>
< / tr>
< tr>
< td> 1252< / td>
< td> Windows 3.1 US(ANSI)< / td>
< td> X< / td>
< td>< / td>
< td> X< / td>
< td> X< / td>
< td> X< / td>
< / tr>
< tr>
< td> 1253< / td>
< td> Windows 3.1希腊语< / td>
< td> X< / td>
< td>< / td>
< td> X< / td>
< td> X< / td>
< td> X< / td>
< / tr>
< tr>
< td> 1254< / td>
< td> Windows 3.1土耳其语< / td>
< td> X< / td>
< td>< / td>
< td> X< / td>
< td> X< / td>
< td> X< / td>
< / tr>
< tr>
< td> 1255< / td>
< td>希伯来语< / td>
< td> X< / td>
< td>< / td>
< td>< / td>
< td>< / td>
< td> X< / td>
< / tr>
< tr>
< td> 1256< / td>
< td>阿拉伯语< / td>
< td> X< / td>
< td>< / td>
< td>< / td>
< td>< / td>
< td> X< / td>
< / tr>
< tr>
< td> 1257< / td>
< td>波罗的海< / td>
< td> X< / td>
< td>< / td>
< td>< / td>
< td>< / td>
< td> X< / td>
< / tr>
< tr>
< td> 1361< / td>
< td>韩语(Johab)< / td>
< td> X< / td>
< td>< / td>
< td>< / td>
< td> **< / td>
< td> X< / td>
< / tr>
< / tbody>
< tbody>
< tr>
< td> 437< / td>
< td> MS-DOS美国< / td>
< td>< / td>
< td> X< / td>
< td> X< / td>
< td> X< / td>
< td> X< / td>
< td class =ignore>< button> teste< / button>< / td>
< / tr>
< tr>
< td> 708< / td>
< td>阿拉伯语(ASMO 708)< / td>
< td>< / td>
< td> X< / td>
< td>< / td>
< td>< / td>
< td> X< / td>
< td class =ignore>< button> teste< / button>< / td>
< / tr>
< tr>
< td> 709< / td>
< td>阿拉伯语(ASMO 449+,BCON V4)< / td>
< td>< / td>
< td> X< / td>
< td>< / td>
< td>< / td>
< td> X< / td>
< td class =ignore>< button> teste< / button>< / td>
< / tr>
< tr>
< td> 710< / td>
< td>阿拉伯语(透明阿拉伯语)< / td>
< td>< / td>
< td> X< / td>
< td>< / td>
< td>< / td>
< td> X< / td>
< td class =ignore>< button> teste< / button>< / td>
< / tr>
< tr>
< td> 720< / td>
< td>阿拉伯语(透明ASMO)< / td>
< td>< / td>
< td> X< / td>
< td>< / td>
< td>< / td>
< td> X< / td>
< td class =ignore>< button> teste< / button>< / td>
< / tr>
< / tbody>
< / table>
I have table with 4 columns where every column includes text field and button and at the end of every row consists of edit and delete button. I want to export the table into excel format but when I do the text field and button at the column header and edit and delete button are also getting exported into excel file which I dont want. Can any one tell me where I am making the mistake in javascript, please.
Here is my jquery code which I got it from net (http://jsfiddle.net/insin/cmewv/)
<script type="text/javascript">
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))
}
})()
</script>
my HTML code as follows
<TABLE id="table_id" class="display" align="Center" border="1px" width="80%">
<thead>
<tr>
<th> <b>User_ID </th></b>
<form action="SearchId" method="post">
<input type="hidden" name="hiddenname" value="hidden_uid" >
<input type="text" name="uid" id="uid">
<input type="submit" value="Search">
</form>
<th><b>User_Name </th></b>
<form action="SearchId" method="post">
<input type="text" name="uname" id="uname">
<input type="hidden" name="hiddenname" value="hidden_uname" >
<input type="submit" value="Search">
</form>
<th><b>Password</th></b>
<form action="SearchId" method="post">
<input type="text" name="pass" id="pass">
<input type="hidden" name="hiddenname" value="hidden_pass" >
<input type="submit" value="Search">
</form>
<th><b>Designation</th></b>
<form action="SearchId" method="post">
<input type="text" name="desig" id="desig">
<input type="hidden" name="hiddenname" value="hidden_desig" >
<input type="submit" value="Search">
</form>
</thead>
<tbody >
<%Iterator itr;%>
<%List data=(List) request.getAttribute("UserData");
for(itr=data.iterator();itr.hasNext();)
{%>
<tr>
<% String s= (String) itr.next(); %>
<td><%=s %></td>
<td><%=itr.next() %></td>
<td><%=itr.next() %></td>
<td><%=itr.next() %></td>
<form id="edit" action="EditRecord" method="post" >
<td><input type="hidden" name="hidden_edit" id="edit_id" value="<%=s %>"/>
<input type="submit" id="myButton" value="Edit" name="edit" onclick="toggleVisibility('');"> </td>
</form>
<td><form id="delete" action="DeleteRecord" method="post" >
<td><input type="hidden" name="hidden_delete" id="delete_id" value="<%=s %>"/>
<input type="submit" value="delete" name="delete"> </td>
</form></td>
<%} %>
</tr>
</tbody>
</TABLE>
Try set tag that have a buttons and a edit text with a class to indentify this dom´s ex.: Class='ignore'. After this clone this yout table and in table cloned remove all dom's that have class that you seted. After removed this dom´s pass the cloned table to function that will export to excel.
Edit -------
Try this.
js script
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 cln=table.cloneNode(true);
var paras = cln.getElementsByClassName('ignore');
while(paras[0]) {
paras[0].parentNode.removeChild(paras[0]);
}
var ctx = {
worksheet : name || 'Worksheet',
table : cln.innerHTML
}
window.location.href = uri + base64(format(template, ctx))
}
})();
HTML example
<input type="button"
onclick="tableToExcel('testTable', 'W3C Example Table')"
value="Export to Excel">
<table id="testTable"
summary="Code page support in different versions of MS Windows."
rules="groups" frame="hsides" border="2">
<caption>CODE-PAGE SUPPORT IN MICROSOFT WINDOWS</caption>
<colgroup align="center"></colgroup>
<colgroup align="left"></colgroup>
<colgroup span="2" align="center"></colgroup>
<colgroup span="3" align="center"></colgroup>
<thead valign="top">
<tr>
<th>Code-Page<br>ID
</th>
<th>Name</th>
<th>ACP</th>
<th>OEMCP</th>
<th>Windows<br>NT 3.1
</th>
<th>Windows<br>NT 3.51
</th>
<th>Windows<br>95
</th>
</tr>
</thead>
<tbody>
<tr>
<td>1200</td>
<td style="background-color: #00f; color: #fff">Unicode (BMP of
ISO/IEC-10646)</td>
<td></td>
<td></td>
<td>X</td>
<td>X</td>
<td>*</td>
</tr>
<tr>
<td>1250</td>
<td style="font-weight: bold">Windows 3.1 Eastern European</td>
<td>X</td>
<td></td>
<td>X</td>
<td>X</td>
<td>X</td>
</tr>
<tr>
<td>1251</td>
<td>Windows 3.1 Cyrillic</td>
<td>X</td>
<td></td>
<td>X</td>
<td>X</td>
<td>X</td>
</tr>
<tr>
<td>1252</td>
<td>Windows 3.1 US (ANSI)</td>
<td>X</td>
<td></td>
<td>X</td>
<td>X</td>
<td>X</td>
</tr>
<tr>
<td>1253</td>
<td>Windows 3.1 Greek</td>
<td>X</td>
<td></td>
<td>X</td>
<td>X</td>
<td>X</td>
</tr>
<tr>
<td>1254</td>
<td>Windows 3.1 Turkish</td>
<td>X</td>
<td></td>
<td>X</td>
<td>X</td>
<td>X</td>
</tr>
<tr>
<td>1255</td>
<td>Hebrew</td>
<td>X</td>
<td></td>
<td></td>
<td></td>
<td>X</td>
</tr>
<tr>
<td>1256</td>
<td>Arabic</td>
<td>X</td>
<td></td>
<td></td>
<td></td>
<td>X</td>
</tr>
<tr>
<td>1257</td>
<td>Baltic</td>
<td>X</td>
<td></td>
<td></td>
<td></td>
<td>X</td>
</tr>
<tr>
<td>1361</td>
<td>Korean (Johab)</td>
<td>X</td>
<td></td>
<td></td>
<td>**</td>
<td>X</td>
</tr>
</tbody>
<tbody>
<tr>
<td>437</td>
<td>MS-DOS United States</td>
<td></td>
<td>X</td>
<td>X</td>
<td>X</td>
<td>X</td>
<td class="ignore"><button>teste</button></td>
</tr>
<tr>
<td>708</td>
<td>Arabic (ASMO 708)</td>
<td></td>
<td>X</td>
<td></td>
<td></td>
<td>X</td>
<td class="ignore"><button>teste</button></td>
</tr>
<tr>
<td>709</td>
<td>Arabic (ASMO 449+, BCON V4)</td>
<td></td>
<td>X</td>
<td></td>
<td></td>
<td>X</td>
<td class="ignore"><button>teste</button></td>
</tr>
<tr>
<td>710</td>
<td>Arabic (Transparent Arabic)</td>
<td></td>
<td>X</td>
<td></td>
<td></td>
<td>X</td>
<td class="ignore"><button>teste</button></td>
</tr>
<tr>
<td>720</td>
<td>Arabic (Transparent ASMO)</td>
<td></td>
<td>X</td>
<td></td>
<td></td>
<td>X</td>
<td class="ignore"><button>teste</button></td>
</tr>
</tbody>
</table>
这篇关于使用javascript将HTML表导出到excel中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!