投稿
  1. 首页
  2. sql

js 导出大数据到excel

fuse • 2023-3-28 • sql • 阅读 10

js 导出大数据到excel,第1张

完整代码：

//导出excelfunction tableToExcel(){

var arrSor = ["sorttable10","sorttable30","sorttable60","sorttable120"]

let str = ""

for (var k = 0k <arrSor.lengthk++) {

var tab=document.getElementById(arrSor[k])

var rows=tab.rows

const jsonData = []

for(var i=2i<rows.lengthi++){ //遍历表格的行

var json = {}

for(var j=0j<rows[i].cells.lengthj++){ //遍历每行的列

json[(i+1)+"-"+(j+1)] = rows[i].cells[j].innerHTML

}

jsonData.push(json)

}

//列标题

let str1 = "<tr><td align='center' colspan='5'><b>最大"+arrSor[k].substring(9,arrSor[k].length)+"分钟雨量</b></td></tr>"

let str2 = "<tr align='center'><th>站点</th><th>站名</th><th>雨量最大值</th><th>降水时段</th><th>落入最大降水时段</th></tr>"

//循环遍历，每行加入tr标签，每个单元格加td标签

for(let i = 0 i <jsonData.length i++ ){

str2+='<tr>'

for(let item in jsonData[i]){

//增加\t为了不让表格显示科学计数法或者其他格式

var itemTem= jsonData[i][item]

if (itemTem == "暂无数据") {

str2+=`<td colspan='5' align='center'>${ itemTem + '\t'}</td>`

}else {

str2+=`<td align='center'>${ itemTem + '\t'}</td>`

}

}

str2+='</tr>'

}

let str3 = "<tr><td colspan='5'></td></tr>"

str += (str1 + str2 + str3)

}

let worksheet = '雨量最大值'

let uri = 'data:application/vnd.ms-excelbase64,'

//下载的表格模板数据

let 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><meta charset='UTF-8'><!--[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]-->

<style type="text/css">

table {border: 1px solid #000000}

table tr td b {background:#FFFFFFcolor:#3D3D3Dfont-size:24pxborder: 1px solid #000000}

table th {background:#AEE1FEcolor:#3D3D3Dfont-size:20pxborder: 1px solid #000000}

table td {background:#FFFFFFcolor:#3D3D3Dfont-size:20pxborder: 1px solid #000000}</style>

</head><body><table>${str}</table></body></html>`

//下载模板

// window.location.href = uri + this.base64(template)

var link = document.createElement("a")

link.href = uri + this.base64(template)

link.download = "雨量最大值-" +new Date().format("yyyy年MM月dd日 h时")+ ".xls"

link.style = "visibility:hidden"

document.body.appendChild(link)

link.click()

document.body.removeChild(link)}//输出base64编码function base64 (template) {

return window.btoa(unescape(encodeURIComponent(template))) }

解析：

遍历取出表，顺序是行从上往下，列从左往右，将数据存进数组，下面再拼接成表。

image.png

参考文章：https://blog.csdn.net/hhzzcc_/article/details/80419396

第一种方法（大量数据导出）

//导出excelfunction tableToExcel() {

var arrSor = ["sorttable10","sorttable30","sorttable60","sorttable120"]

let str = ""

for (var k = 0k <arrSor.lengthk++) {

var tab=document.getElementById(arrSor[k])

var rows=tab.rows

const jsonData = []

for(var i=2i<rows.lengthi++){ //遍历表格的行

var json = {}

for(var j=0j<rows[i].cells.lengthj++){ //遍历每行的列

if (rows[i].cells[j].outerHTML.indexOf("rgb(255, 255, 0)") != -1) {

json["yellow"+(i+1)+"-"+(j+1)] = rows[i].cells[j].innerHTML

}else {

json[(i+1)+"-"+(j+1)] = rows[i].cells[j].innerHTML

}

}

jsonData.push(json)

}

//列标题

let str1 = "<tr><td colspan='5' align='center' style='background-color:#FFFFFFfont-size:24pxborder: 1px solid #000000'><b>最大"

+arrSor[k].substring(9,arrSor[k].length)+"分钟雨量</b></td></tr>"

let str2 = "<tr>" +

"<th style='background-color:#AEE1FEfont-size:22pxborder: 1px solid #000000' align='center'>站点</th>" +

"<th style='background-color:#AEE1FEfont-size:22pxborder: 1px solid #000000' align='center'>站名</th>" +

"<th style='background-color:#AEE1FEfont-size:22pxborder: 1px solid #000000' align='center'>雨量最大值</th>" +

"<th style='background-color:#AEE1FEfont-size:22pxborder: 1px solid #000000' align='center'>降水时段</th>" +

"<th style='background-color:#AEE1FEfont-size:22pxborder: 1px solid #000000' align='center'>落入最大降水时段</th></tr>"

//循环遍历，每行加入tr标签，每个单元格加td标签

for(let i = 0 i <jsonData.length i++ ){

str2+="<tr align='center'>"

for(let item in jsonData[i]){

if (item.indexOf("yellow") != -1) {//取列数等于3

//增加\t为了不让表格显示科学计数法或者其他格式

var itemTem= jsonData[i][item]

if (itemTem == "暂无数据") {

str2+=`<td colspan='5' style='background-color:#FFFF00font-size:22pxborder: 1px solid #000000'> ${ itemTem + '\t'}</td>`

}else {

str2+=`<td style='background-color:#FFFF00font-size:22pxborder: 1px solid #000000'> ${ itemTem + '\t'}</td>`

}

}else {

//增加\t为了不让表格显示科学计数法或者其他格式

var itemTem= jsonData[i][item]

if (itemTem == "暂无数据") {

str2+=`<td colspan='5' style='background-color:#FFFFFFfont-size:22pxborder: 1px solid #000000'> ${ itemTem + '\t'}</td>`

}else {

str2+=`<td style='background-color:#FFFFFFfont-size:22pxborder: 1px solid #000000'> ${ itemTem + '\t'}</td>`

}

}

}

str2+='</tr>'

}

let str3 = "<tr><td colspan='5' style='background-color:#FFFFFFborder: 1px solid #000000'></td></tr>"

str += (str1 + str2 + str3)

}

var tableHtml="<html><head><meta charset='UTF-8'></head><body><table>"+str+"</body></html>"

var excelBlob = new Blob([tableHtml], {type: 'application/vnd.ms-excel'})

var fileName = "雨量最大值-"+new Date().format("yyyy年MM月dd日 h时")+".xls"

if(isIE()){

window.navigator.msSaveOrOpenBlob(excelBlob,fileName)

}else{

var oa = document.createElement('a')

oa.href = URL.createObjectURL(excelBlob)

oa.download = fileName

document.body.appendChild(oa)

oa.click()

}} //判断是否IE浏览器function isIE() {

if (!!window.ActiveXObject || "ActiveXObject" in window) {

return true

} else {

return false

}}

参考文章：https://blog.csdn.net/qq_34169240/article/details/84231226

(function ($) {

Date.prototype.Format = function (fmt) {

var o = {

"M+": this.getMonth() + 1, //月份

"d+": this.getDate(), //日

"h+": this.getHours(), //小时

"m+": this.getMinutes(), //分

"s+": this.getSeconds(), //秒

"q+": Math.floor((this.getMonth() + 3) / 3), //季度

"S": this.getMilliseconds() //毫秒

}

if (/(y+)/.test(fmt)) fmt = fmt.replace(RegExp.$1, (this.getFullYear() + "").substr(4 - RegExp.$1.length))

for (var k in o)

if (new RegExp("(" + k + ")").test(fmt)) fmt = fmt.replace(RegExp.$1, (RegExp.$1.length == 1) ? (o[k]) : (("00" + o[k]).substr(("" + o[k]).length)))

return fmt

}

$.fn.ExportExcel = function (thread_id,tab_id, options) {

var defaults = {

height: '24px',

'line-height': '24px',

margin: '0 5px',

padding: '0 11px',

color: '#000',

background: '#02bafa',

border: '1px #26bbdb solid',

'border-radius': '3px',

/*color: #fff*/

display: 'inline-block',

'text-decoration': 'none',

'font-size': '12px',

outline: 'none',

cursor: 'pointer'

}

var options = $.extend(defaults, options)

return this.each(function () {

var currentObject = $(this)//获取当前对象

currentObject.css(defaults)

currentObject.onmouseover = function () {

$(this).css('cursor', 'hand')

}

currentObject.click(function () {

//From:jsfiddle.net/h42y4ke2/16/

var tab_text = '<html xmlns:x="urn:schemas-microsoft-com:office:excel">'

tab_text = tab_text + '<head><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet>'

tab_text = tab_text + '<x:Name>Test Sheet</x:Name>'

tab_text = tab_text + '<x:WorksheetOptions><x:Panes></x:Panes></x:WorksheetOptions></x:ExcelWorksheet>'

tab_text = tab_text + '</x:ExcelWorksheets></x:ExcelWorkbook></xml></head><body>'

tab_text = tab_text + "<table border='1px'>"

tab_text = tab_text + $('#' + thread_id).html()

tab_text = tab_text + $('#' + tab_id).html()

tab_text = tab_text + '</table></body></html>'

var data_type = 'data:application/vnd.ms-excel'

var timeStr = new Date().Format('yyyyMMddhhmmss')

$(this).attr('href', data_type + ', ' + encodeURIComponent(tab_text))

$(this).attr('download', '日常数据报表' + timeStr + '.xls')

})

})

}

})(jQuery)

<html>

<a href="#" id="export">导出</a>

<table>

<thead id="theadDate">

<tr>

<th>姓名</th>

<th>班级</th>

<th>年龄</th>

</tr>

</thead>

<tbody id="tbodyDate">

<tr>　<td>张三</td>

<td>高二</td>

<td>18</td>

</tr>

<tr>

<td>李四</td>

<td>高三</td>

<td>20</td>

</tr>

</tbody>

</table>

<script src="assets/javascripts/autotest/export-excel.js" ></script><!-- 引入js文件-->

<script type="text/javascript">

//导出 调用

$(function () {

$('#export').ExportExcel('theadDate','tbodyDate')//tbodyDate为table的id,export为a标签。

})

</script>

</html>

1.右击数据库，选择任务-导出数据，2.然后数据源默认，数据库选择好，3.下一步目的，选择excel，以及保存路径，版本等，4.再下一步选择要输出的表5.然后就ok了。（没有提到的全都默认就行）

欢迎分享，转载请注明来源：内存溢出

原文地址: http://outofmemory.cn/sjk/6770701.html

赞 (0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
fuse fuse 一级用户组
0 0
上一篇 2023-03-28
下一篇 2023-03-28

发表评论

登录后才能评论

评论列表（0条）

保存