本文介绍了一种将网页中的表格数据导出为Excel文件的方法，包括针对不同浏览器的兼容性处理方案，并提供了完整的JavaScript代码实现及HTML示例。
先上代码
<script type="text/javascript" language="javascript">
var idTmr;
function getExplorer() {
var explorer = window.navigator.userAgent;
//ie
if (explorer.indexOf("MSIE") >= 0) {
return 'ie';
}
//firefox
else if (explorer.indexOf("Firefox") >= 0) {
return 'Firefox';
}
//Chrome
else if (explorer.indexOf("Chrome") >= 0) {
return 'Chrome';
}
//Opera
else if (explorer.indexOf("Opera") >= 0) {
return 'Opera';
}
//Safari
else if (explorer.indexOf("Safari") >= 0) {
return 'Safari';
}
}
function method1(tableid,name="1.xlsx") { //整个表格拷贝到EXCEL中
if (getExplorer() == 'ie') {
var curTbl = document.getElementById(tableid);
var oXL = new ActiveXObject("Excel.Application");
//创建AX对象excel
var oWB = oXL.Workbooks.Add();
//获取workbook对象
var xlsheet = oWB.Worksheets(1);
//激活当前sheet
var sel = document.body.createTextRange();
sel.moveToElementText(curTbl);
//把表格中的内容移到TextRange中
sel.select;
//全选TextRange中内容
sel.execCommand("Copy");
//复制TextRange中内容
xlsheet.Paste();
//粘贴到活动的EXCEL中
oXL.Visible = true;
//设置excel可见属性
try {
var fname = oXL.Application.GetSaveAsFilename("Excel.xls", "Excel Spreadsheets (*.xls), *.xls");
} catch (e) {
print("Nested catch caught " + e);
} finally {
oWB.SaveAs(fname);
oWB.Close(savechanges = false);
//xls.visible = false;
oXL.Quit();
oXL = null;
//结束excel进程，退出完成
//window.setInterval("Cleanup();",1);
idTmr = window.setInterval("Cleanup();", 1);
}
} else {
tableToExcel(tableid,name)
}
}
function Cleanup() {
window.clearInterval(idTmr);
CollectGarbage();
}
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) {
console.log(table,name)
if (!table.nodeType) table = document.getElementById(table)
var ctx = {
worksheet: name || 'Worksheet',
table: table.innerHTML
}
return;
window.location.href = uri + base64(format(template, ctx))
}
})();
</script> $('#export').click(function () {
method1('table');
})$('#export').attr('href',uri + base64(format(template, ctx))) //解决文件无扩展名的问题
转载自http://blog.csdn.net/sinat_15114467/article/details/51098522
github也有写好的插件：
jQuery table2excel - v1.1.1
* jQuery plugin to export an .xls file in browser from an HTML table
* https://github.com/rainabba/jquery-table2excel
这个地址也是相关的介绍https://segmentfault.com/a/1190000000336643
我的html实例
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="gb2312">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>数据</title>
<style>
* {
margin: 0;
padding: 0;
text-decoration: none;
list-style: none;
font-size: 20px;
}
body{
text-align: center;
}
.agent{
text-align: left;
}
table {
width: 1200px;
margin: 0 auto;
vertical-align: center;
font-size: 18px;
text-align: center;
border: 1px solid #ccc;
}
td,
th {
border: 1px solid #000;
overflow: hidden;
}
.kefu {
width: 70px;
}
.page {
text-align: center;
font-size: 20px;
}
.page a {
display: inline-block;
}
#export {
display: block;
text-align: center;
font-size: 20px;
}
/* 时间和日期 */
#choose {
width: 1200px;
margin: 20px auto;
text-align: center;
}
/* 对话框 */
.convBox {
position: fixed;
top: 0px;
left: 0;
bottom: 0;
right: 0;
margin: auto;
width: 1200px;
height: 500px;
overflow: auto;
display: none;
background: #ccc;
border: 1px solid #000;
}
.convBox h6 {
font-size: 20px;
margin: 15px 0;
text-align: center;
}
.convBox .close {
position: absolute;
top: 8px;
right: 8px;
font-size: 20px;
}
.convBox li {
float: left;
margin: 8px 20px;
}
.clearfix:after {
content: '';
display: block;
height: 0;
visibility: hidden;
clear: both;
}
.clearfix {
zoom: 1;
}
.mainCon .agent,
.mainCon .client {
padding: 8px 20px;
}
.mainCon .client {
text-align: right;
background: #68c558;
}
</style>
</head>
<body>
<div id="choose">
<input type="text" class="startTm">
<input type="text" class="endTm">
<button>提交</button>
</div>
<table id="table">
<tr>
<th>编号</th>
<th>对话开始时间</th>
<th>会话结束时间</th>
<th>客户id</th>
<th>搜索关键词</th>
<th class="kefu">客服</th>
<th>客服首次响应访客的等待时</th>
<th>访客的消息数</th>
<th>对话内容</th>
<th>客户地点</th>
<th>IP</th>
</tr>
</table>
<p class="page">
<a href="javascript:void(0)" class="prev">上一页</a>
<a href="javascript:void(0)" class="next">下一页</a>
<span></span>
</p>
<a href="javascript:void(0)" id="export" download="对话记录.xlsx">导出对话记录</a>
<div class="convBox">
<h6><span></span>的对话</h6>
<a href="javascript:void(0)" class="close">X</a>
<ul class="contentKey clearfix">
<li class="agent_name">
客服：<span>dfsdfsfs</span>
</li>
<li class="wait_in_secs">
对话等待时间：<span>5</span>S
</li>
<li class="visitor_ip">
IP：<span>yan</span>
</li>
<li class="visitor_location">
地点：<span>yan</span>
</li>
<li class="search_engine_kw">
搜索关键词：<span>sdfsfsfsdfsfsfsgsdsg</span>
</li>
<li class="conv_start_tm">
对话开始时间：<span>sdfsfsfsdfsfsfsgsdsg</span>
</li>
<li class="conv_end_tm">
对话结束时间：<span>sdfsfsfsdfsfsfsgsdsg</span>
</li>
</ul>
<div class="mainCon clearfix">
</div>
</div>
<script src="js/jquery-1.11.3.min.js"></script>
<script>
var tableList= '<tr>\
<th>编号</th>\
<th>对话开始时间</th>\
<th>会话结束时间</th>\
<th>客户id</th>\
<th>搜索关键词</th>\
<th class="kefu">客服</th>\
<th>客服首次响应访客的等待时</th>\
<th>访客的消息数</th>\
<th>对话内容</th>\
<th>客户地点</th>\
<th>IP</th>\
</tr>';
$(document).ready(function () {
var pageAll='';//计算总数时用的参数
var pagesize = 0; //显示页
var limit = 20; //每页显示的数目
var offset = pagesize * limit; //跳过的数目
var startTm = $('.startTm').val();
var endTm = $('.endTm').val();
//初始化时间
var startTm = getFormatDate('ymd');
var endTm = getFormatDate('ymd');
$('.startTm').val(getFormatDate('ymd'));
$('.endTm').val(getFormatDate('ymd'));
var data1 = {
startTm: startTm,
endTm: endTm,
offset: 0
};
//ajax请求
function ajax(data1) {
// console.log(data1)
$.ajax({
url: "php/index.php",
data: data1,
success: function (data) {
console.log(data)
var newJson = JSON.parse(data);
if (newJson.result.length < 20) {
$('.page span').text("已经是最后一页");
} else if (newJson.result.length <= 0) {
$('.page span').text("已经是最后一页");
return;
} else {
var Nowpage = pagesize + 1;
$('.page span').text("当前是第" + Nowpage + "页");
}
// addContent(newJson.result)
tableList+=addContent(newJson.result);
$('#table').append(addContent(newJson.result));
}
})
}
function ajax2(data1) {
$.ajax({
url: "php/index.php",
data: data1,
success: function (data) {
var newJson = JSON.parse(data);
tableList+=addContent(newJson.result);
if (newJson.result.length < 20 || newJson.result.length <= 0) {
// console.log(tableList)
var dateN=(new Date()).toLocaleString();
// console.log(dateN)
// return;
method1(tableList,dateN);
// return tableList;
} else {
allrecord();
}
}
})
}
//调出所有的记录
function allrecord(){
pageAll++;
var offset = pageAll * limit; //跳过的数目
var startTm = $('.startTm').val();
var endTm = $('.endTm').val();
var data1 = {
startTm: startTm,
endTm: endTm,
offset: offset
}
ajax2(data1);
}
ajax(data1);
//插入对话内容
function addContent(rs) {
// console.log(rs)
var arr = [];
var length = rs.length;
for (var i = 0; i < length; i++) {
if(!rs[i]){continue;}
arr.push('<tr>');
arr.push('<td>' + i + '</td>');
arr.push('<td>' + rs[i].conv_start_tm + '</td>');
arr.push('<td>' + rs[i].conv_end_tm + '</td>');
arr.push('<td>' + rs[i].client_info.visitor_name + '</td>');
arr.push('<td>' + rs[i].search_engine_kw + '</td>');
arr.push('<td>' + rs[i].agent_name + '</td>');
arr.push('<td>' + rs[i].wait_in_secs + '</td>');
arr.push('<td>' + rs[i].conv_visitor_msg_count + '</td>');
if(rs[i].conv_visitor_msg_count>0){
let con = '';
let conLen = rs[i].conv_content.length;
for(let j=0;j<conLen;j++){
con+=rs[i].conv_content[j].from+rs[i].conv_content[j].timestamp+rs[i].conv_content[j].content;
}
arr.push('<td class="convId">' + con + '<span>' + rs[i].conv_id + '</span></td>');
}else{
arr.push('<td class="convId">' + '点击显示内容<span>' + rs[i].conv_id + '</span></td>');
}
arr.push('<td>' + rs[i].visitor_location + '</td>');
arr.push('<td>'+rs[i].visitor_ip+'</td>');
arr.push('</tr>');
}
// tableList+=arr.join('')
// $('#table').append(arr.join(''));
return arr.join('');
}
$('.prev').click(function () {
$("#table tr").not($("#table tr:first")).remove();
pagesize = (--pagesize < 0) ? 0 : pagesize;
offset = pagesize * limit; //跳过的数目
startTm = $('.startTm').val();
endTm = $('.endTm').val();
var data1 = {
startTm: startTm,
endTm: endTm,
offset: offset
}
ajax(data1);
})
$('.next').click(function () {
$("#table tr").not($("#table tr:first")).remove();
if ($('.page span').text() == '已经是最后一页') {
return false;
}
pagesize = (++pagesize < 0) ? 0 : pagesize;
// console.log(pagesize)
var offset = pagesize * limit; //跳过的数目
var startTm = $('.startTm').val();
var endTm = $('.endTm').val();
var data1 = {
startTm: startTm,
endTm: endTm,
offset: offset
}
ajax(data1);
})
// 日期选择表单
$('#choose button').click(function () {
pagesize=0;
var startTm = $('.startTm').val();
var endTm = $('.endTm').val();
var data1 = {
startTm: startTm,
endTm: endTm,
offset: 0
}
$("#table tr").not($("#table tr:first")).remove();
ajax(data1);
});
// 当前时间的函数
/*
* @param param string 确定时间的显示格式 'ymd' => 年-月-日
* 其它 => 年-月-日+时：分：秒
* @param num num +1代表后一天，-1代表前一天
*
**/
function getFormatDate(param, num = 0) {
var date = new Date();
var seperator1 = "-";
var seperator2 = ":";
var seperator3 = '+';
var y = date.getFullYear();
var m = date.getMonth() + 1;
var d = date.getDate() + num;
var h = date.getHours();
var i = date.getMinutes();
var s = date.getSeconds();
if (m >= 1 && m <= 9) {
m = "0" + m;
}
d = d <= 0 ? 1 : d;
if (d >= 0 && d <= 9) {
d = "0" + d;
}
if (param = 'ymd') {
var currentdate = y + seperator1 + m + seperator1 + d;
} else {
var currentdate = y + seperator1 + m + seperator1 + d +
seperator3 + h + seperator2 + i +
seperator2 + s;
}
return currentdate;
}
// 显示聊天内容
$(document).on('click', '.convId', function () {
var convId = $(this).find('span').text();
var data1 = {
conv_id: convId
}
$.ajax({
url: "php/conv.php",
data: data1,
success: function (data) {
var newJson = (new Function("", "return " + data))(data).result;
// console.log(newJson)
$('.convBox h6 span').text(convId);
$('.convBox .agent_name span').text(newJson.agent_name);
$('.convBox .wait_in_secs span').text(newJson.wait_in_secs);
$('.convBox .visitor_ip span').text(newJson.visitor_ip);
$('.convBox .visitor_location span').text(newJson.visitor_location);
$('.convBox .search_engine_kw span').text(newJson.search_engine_kw);
$('.convBox .conv_end_tm span').text(newJson.conv_end_tm);
$('.convBox .conv_start_tm span').text(newJson.conv_start_tm);
$('.convBox .visitor_location span').text(newJson.visitor_location);
let convLen = newJson.conv_content.length;
let conv_content = [];
// console.log(convLen)
for (let j = 0; j < convLen; j++) {
// conv_content.push(111)
conv_content.push('<div class="' + newJson.conv_content[j].from +
'"><p>' + newJson.conv_content[j].timestamp + '</p>' +
newJson.conv_content[j].content + '</div>');
}
// console.log(conv_content)
$('.convBox .mainCon').html(conv_content.join(''));
}
});
$('.convBox').show();
})
$(document).on('click', '.convBox .close', function () {
$('.convBox').hide();
});
// 打印
$('#export').click(function () {
if ($('.page span').text() == '已经是最后一页') {
// method1(tableList,'聊天数据');
method1(tableList,'1.xlsx');
return false;
}
pageAll =pagesize;
allrecord();
// method1(allrecord(),'美洽对话');
})
});
</script>
<script type="text/javascript" language="javascript">
var idTmr;
function getExplorer() {
var explorer = window.navigator.userAgent;
//ie
if (explorer.indexOf("MSIE") >= 0) {
return 'ie';
}
//firefox
else if (explorer.indexOf("Firefox") >= 0) {
return 'Firefox';
}
//Chrome
else if (explorer.indexOf("Chrome") >= 0) {
return 'Chrome';
}
//Opera
else if (explorer.indexOf("Opera") >= 0) {
return 'Opera';
}
//Safari
else if (explorer.indexOf("Safari") >= 0) {
return 'Safari';
}
}
function method1(tableid,name="1.xlsx") { //整个表格拷贝到EXCEL中
if (getExplorer() == 'ie') {
var curTbl = document.getElementById(tableid);
var oXL = new ActiveXObject("Excel.Application");
//创建AX对象excel
var oWB = oXL.Workbooks.Add();
//获取workbook对象
var xlsheet = oWB.Worksheets(1);
//激活当前sheet
var sel = document.body.createTextRange();
sel.moveToElementText(curTbl);
//把表格中的内容移到TextRange中
sel.select;
//全选TextRange中内容
sel.execCommand("Copy");
//复制TextRange中内容
xlsheet.Paste();
//粘贴到活动的EXCEL中
oXL.Visible = true;
//设置excel可见属性
try {
var fname = oXL.Application.GetSaveAsFilename("Excel.xls", "Excel Spreadsheets (*.xls), *.xls");
} catch (e) {
print("Nested catch caught " + e);
} finally {
oWB.SaveAs(fname);
oWB.Close(savechanges = false);
//xls.visible = false;
oXL.Quit();
oXL = null;
//结束excel进程，退出完成
//window.setInterval("Cleanup();",1);
idTmr = window.setInterval("Cleanup();", 1);
}
} else {
tableToExcel(tableid,name)
}
}
function Cleanup() {
window.clearInterval(idTmr);
CollectGarbage();
}
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) {
var ctx = {worksheet:name , table:table}
// console.log(uri + base64(format(template, ctx)))
// return;
// $('#export').attr('href',uri + base64(format(template, ctx)))
window.location.href = uri + base64(format(template, ctx));
}
})()
</script>
</body>
</html>
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
