js导出excel

javascript

浏览数:753

2019-1-8

<!DOCTYPE html>  
<html>  
<head lang="en">  
<meta charset="UTF-8">  
<title>html 表格导出Excel</title>   
</head>  
<body>  
  <div >   
    <button onclick="method('tableExcel')">表格导出Excel</button>  
    <button onclick="jsonMethod()">json导出Excel</button>  
  </div>  
  <div id="myDiv">  
    <table id="tableExcel" width="100%" border="1" cellspacing="0" cellpadding="0">  
      <tr><td colspan="5" align="center">html 表格导出道Excel</td></tr>  
      <tr><td>列标题1</td><td>列标题2</td><td>类标题3</td><td>列标题4</td><td>列标题5</td></tr>  
      <tr><td>aaa</td><td>bbb</td><td>ccc</td><td>ddd</td><td>eee</td></tr>  
      <tr><td>AAA</td><td>BBB</td><td>CCC</td><td>DDD</td><td>EEE</td></tr>  
      <tr><td>FFF</td><td>GGG</td><td>HHH</td><td>III</td><td>JJJ</td></tr>  
    </table>  
  </div>  
</body>  
</html> 
<script language="JavaScript" type="text/javascript"> 
//---------------------------将JSON导出Excel---------------------------//

//需要进行数据转换,

// 我们使用ajax请求到的数据

var data = [{name:"hjm100",duty:'管理员',opensource:'http://git.oschina.net/hjm100',aa:'无用数据'},
            {name:"zmjie100",duty:'前端开发',opensource:'http://git.oschina.net/zmjie100',aa:'无用数据'},
            {name:"ghf100",duty:'前端开发',opensource:'http://git.oschina.net/ghf100',aa:'无用数据'},
            {name:"zhengshanshan",duty:'前端开发',opensource:'http://git.oschina.net/zhengshanshan',aa:'无用数据'}];
//数据处理
var title = [{"value":"名称",key:'name'},
             {"value":"职务",key:'duty'},
             {"value":"开源地址",key:'opensource'}];

/**dataSwitch函数参数
 * @param datas  原始的json数据
 * @param titles 自己设置的表头数据
 * 注意:
 * 1.必须设置titles
 * 2.titles是过滤datas的必要条件
 * 3.titles中的key属性对应的是需要提取的原始数据的key值
 * 4.datas与titles需要相互对应
 */
//数据转换函数
function dataSwitch(datas,titles){
  if(datas == ''|| titles=='') return;  
  var res = [],resd = [];
  for (var j = 0; j < datas.length; j++) {
    //清空一下res
    res = []
    for (var i = 0; i < titles.length; i++) {
      res.push({"value":datas[j][titles[i].key]})
    }
    resd.push(res)
  }
  return (resd)
}

//JSONToExcelConvertor函数需要的数据格式
// var datas = {"title":[{"value":"店铺"},{"value":"点击量"},{"value":"竞争力"}],
//               "data":[[{"value":"运营淘宝代",}, {"value":3057},{"value":955}],
//                       [{"value":"淘宝网",}, {"value":412},{"value":1860}]]
//             }; 

//空壳函数用来做调用,与方便理解!!
function jsonMethod(){
  //执行导出函数
  JSONToExcelConvertor(dataSwitch(data,title), "鸿基梦", title,'鸿基梦组织重要成员信息汇总');  
}

/**JSONToExcelConvertor函数参数
 * @param JSONData  需要导出的数据
 * @param FileName  Excel表名
 * @param ShowLabel 表头
 * @param Explain   表格说明(选填)
 * 注意: 
 * 上述参数都为转化后数据
 */
function JSONToExcelConvertor(JSONData, FileName, ShowLabel,Explain) {  
  //先转化json  
  var arrData = typeof JSONData != 'object' ? JSON.parse(JSONData) : JSONData;  
  var excel = '<table>',row = ''; 
  //添加说明(如果想要添加说明请传入Explain参数) 
  if(Explain)row +="<tr><td colspan='"+ShowLabel.length+"' align='center'>"+Explain+"</td></tr>";       
  //设置表头  
  row += "<tr>";  
  for (var i = 0, l = ShowLabel.length; i < l; i++){  
    row += "<td>" + ShowLabel[i].value + '</td>';  
  }  
  //换行  
  excel += row + "</tr>";  
  //设置数据  
  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 += FileName;  
  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);  
}  

//---------------------------将表格导出Excel---------------------------//
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 method(tableid){ //整个表格拷贝到EXCEL中
  if(getExplorer()=='ie'){  
    var curTbl  = document.getElementById(tableid), 
        oXL     = new ActiveXObject("Excel.Application"),
        //创建AX对象excel 
        oWB     = oXL.Workbooks.Add(),
        //获取workbook对象  
        xlsheet = oWB.Worksheets(1),
        //激活当前sheet 
        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);  
      oXL.Quit();  
      oXL   = null;  
      idTmr = window.setInterval("Cleanup();", 1);  
    }  
  }else{  
    tableToExcel(tableid)  
  } 
  oXL.worksheets(1).Paste; 
} 

//兼容ie之外浏览器 
function Cleanup() {  
  window.clearInterval(idTmr);  
  CollectGarbage();  
}  
var tableToExcel = (function() {  
  var uri      = 'data:application/vnd.ms-excel;base64,',  
      template = '<html><head><meta charset="UTF-8"></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>