js 导出大数据到excel

2023-02-19 05:16:02JavaScript029

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]-->

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

把看到的区域当画布，创建足够能展现界面的Dom就够了。

比如一个屏幕的高度一般是800像素左右，假设一条记录占用的高度是20像素，我们只用创建40个对象即可。

接下来就是对这40个对象进行数据填充，通过滚动条调整填充数据的起始下标。

10W条记录，IE下有些卡

.panel{

overflow:scroll

width:200px

height:80%

}

.panel .scroll{

}

.item{

width:500px

height:20px

}

.odd{

background-color:#ccc

}

.items{

overflow:hidden

position:absolute

}

.red{

color:red

}

.green{

color:green

}

</style>

window.console = window.console || { log: function() {} }

function absolutePoint(element) {

var result = [element.offsetLeft, element.offsetTop]

element = element.offsetParent

while (element) {

result[0] += element.offsetLeft

result[1] += element.offsetTop

element = element.offsetParent

}

return result

}

function ListView(options){

options = options || {}

var self = this,

$C = function(tagName) { return document.createElement(tagName)}, // 创建节点

height,

item_height, // 项高

view_count, // 可见项条数

parent = options.parent || document.body, // 容器

height, // 面板历史高度

div_panel = $C("div"),

div_scroll = $C("div"),

div_items = $C("div"),

div_items_list = [$C("div")],

freed = [div_panel, div_scroll, div_items]// 可释放的对象

div_panel.className = "panel"

parent.appendChild(div_panel)

div_items.className = "items"

document.body.appendChild(div_items)

div_scroll.className = "scroll"

div_panel.appendChild(div_scroll)

div_panel.onscroll = function() {

doChange()

}

div_panel.onresize = function() {

doChange()

}

div_items_list[0].className = "item"

div_items.appendChild(div_items_list[0])

div_scroll.style.width = div_items_list[0].clientWidth + "px"

item_height = div_items_list[0].clientHeight

p = absolutePoint(div_panel)

with(div_items.style) {

left = p[0] + "px"

top = p[1] + "px"

width = div_panel.clientWidth

height = div_panel.clientHeight

}

/**

* 界面改变

function doChange() {

if (!item_height) return

var i, div

if (height != div_panel.clientHeight) {

height = div_panel.clientHeight

view_count = parseInt(height / item_height)

for (i = div_items_list.lengthi <view_counti++) {

div = $C("div")

div.className = "item" + (i % 2 == 0 ? "" : " odd")

div_items.appendChild(div)

div_items_list.push(div)

}

for (i = 0i <div_items_list.lengthi++) {

div_items_list[i].style.display = i <view_count ? "" : "none"

}

div_scroll.style.height = div_panel.clientHeight + options.count - view_count + "px"

console.log(["view_count", view_count])

}

div_items.scrollLeft = div_panel.scrollLeft

if (!options.ondrawitem) return

i = Math.min(view_count, div_items_list.length)

while(i--) {

// 重新绘制

options.ondrawitem(i + div_panel.scrollTop, div_items_list[i])

}

doChange()

this.doChange = doChange

/**

* 释放Dom对象

this.dispose = function() {

var i = freed.length

while(i--) {

freed[i].parentNode.removeChild(freed[i])

}

i = freed.length

while(i--) {

div_items_list[i].parentNode.removeChild(div_items_list[i])

}

function format(template, json) {

if (!json) return template

return template &&template.replace(/\$\{(.+?)\}/g, function() {

return json[arguments[1]]

})

}

window.onload = function() {

var i = 100000, data = new Array(i)

while(i--) {

data[i] = { index: i, random: Math.random(), key: (+new Date()).toString(36) }

}

var listview = new ListView({

count: data.length,

ondrawitem: function(i, div) {

div.innerHTML = format(" <em>${index} </em> <span class=\"red\">${random} </span> <span class=\"green\">${key} </span>", data[i])

}

})

}

</script>

1、打开HTML5软件。

2、点击文件按钮。

3、选择文件使用驱动为JavaScript。

4、点击创建。

5、将大数据地图板块的代码输入。

6、点击运行即可显示。

词库加载错误未能找到文件“E高铁采集器内存溢出ConfigurationDictStopwordstxt”

# 上一篇：电脑如何添加字体

# 下一篇：电脑上垃圾软件删不掉

推荐阅读

热门文章

最新发布

标签列表

js 导出大数据到excel

给您推荐相同类型的内容：