2021-01-19

<!DOCTYPE html>

<head>

<title>js解析excel</title>

<style>

html{margin:0;padding:0px;}

body{margin:0;padding:20px;}

.mydiv{padding:10px;font-size:12px;border-bottom:1px dotted #eee;}

.mydiv .name{display:inline-block;width:200px;margin-right:30px;}

.mydiv .url{display:inline-block;width:400px; margin-right:30px;}

.mydiv .price{display:inline-block;width:100px;color:#ff0000;}

.loading{background:rgba(0,0,0,0.1);width:100%;height:100%;position:fixed;left0;top:0;}

.sk-chase {width: 40px;height: 40px;position: absolute;left:50%;top:50%;margin:-20px 0 0 -20px;animation: sk-chase 2.5s infinite linear both;}

.sk-chase-dot {width: 100%;height: 100%;position: absolute;left: 0;top: 0; animation: sk-chase-dot 2.0s infinite ease-in-out both; }

.sk-chase-dot:before { content: ''; display: block; width: 25%; height: 25%; background-color: #000; border-radius: 100%; animation: sk-chase-dot-before 2.0s infinite ease-in-out both; }

.sk-chase-dot:nth-child(1) { animation-delay: -1.1s; }

.sk-chase-dot:nth-child(2) { animation-delay: -1.0s; }

.sk-chase-dot:nth-child(3) { animation-delay: -0.9s; }

.sk-chase-dot:nth-child(4) { animation-delay: -0.8s; }

.sk-chase-dot:nth-child(5) { animation-delay: -0.7s; }

.sk-chase-dot:nth-child(6) { animation-delay: -0.6s; }

.sk-chase-dot:nth-child(1):before { animation-delay: -1.1s; }

.sk-chase-dot:nth-child(2):before { animation-delay: -1.0s; }

.sk-chase-dot:nth-child(3):before { animation-delay: -0.9s; }

.sk-chase-dot:nth-child(4):before { animation-delay: -0.8s; }

.sk-chase-dot:nth-child(5):before { animation-delay: -0.7s; }

.sk-chase-dot:nth-child(6):before { animation-delay: -0.6s; }

@keyframes sk-chase {

100% { transform: rotate(360deg); }

}

@keyframes sk-chase-dot {

80%, 100% { transform: rotate(360deg); }

}

@keyframes sk-chase-dot-before {

50% {

transform: scale(0.4);

} 100%, 0% {

transform: scale(1.0);

}

</style>

</head>

<body>

<div style="margin:10px auto 10px;font-weight:bold;">表1 解析到文本框</div>

<div style="margin:10px auto 10px;font-weight:bold;">表2 解析到文本框</div>

<button onclick="setprice($('#area'))">表1 按价格排序(可切换排序)</button>

<button style="margin-left:30px;" onclick="setprice($('#area2'))">表2 按价格排序(可切换排序)</button>

<button style="margin-left:30px;" onclick="setname()">两个表，按媒体名称，找重复值</button>

</div>

</div>

//给input标签绑定change事件，一上传选中的.xls文件就会触发该函数

$('#excel-file').change(function(e) {

$('#loading').show();

var files = e.target.files;

var fileReader = new FileReader();

fileReader.onload = function(ev) {

try {

var data = ev.target.result

var workbook = XLSX.read(data, {

type: 'binary'

}) // 以二进制流方式读取得到整份excel表格对象

var persons = []; // 存储获取到的数据

} catch (e) {

console.log('文件类型不正确');

return;

}

// 表格的表格范围，可用于判断表头是否数量是否正确

var fromTo = '';

// 遍历每张表读取

for (var sheet in workbook.Sheets) {

if (workbook.Sheets.hasOwnProperty(sheet)) {

fromTo = workbook.Sheets[sheet]['!ref'];

console.log(fromTo);

persons = persons.concat(XLSX.utils.sheet_to_json(workbook.Sheets[sheet]));

// break; // 如果只取第一张表，就取消注释这行

}

//在控制台打印出来表格中的数据

console.log(persons);

localStorage.setItem('globalArr',JSON.stringify(persons));

$("#area").val(JSON.stringify(persons));

var html='<div>'

for(var i=0;i<persons.length;i++){

html+='<div class="mydiv"><span class="name">'+persons[i].name+'</span><span class="url">'+persons[i].url+'</span><span class="price">'+persons[i].price+'</span></div>'

}

html+='</div>'

$('#areaBody').html(html);

$('#loading').hide();

};

// 以二进制方式打开文件

fileReader.readAsBinaryString(files[0]);

});

$('#excel-file2').change(function(e) {

$('#loading').show();

var files = e.target.files;

var fileReader = new FileReader();

fileReader.onload = function(ev) {

try {

var data = ev.target.result

var workbook = XLSX.read(data, {

type: 'binary'

}) // 以二进制流方式读取得到整份excel表格对象

var persons = []; // 存储获取到的数据

} catch (e) {

console.log('文件类型不正确');

return;

}

// 表格的表格范围，可用于判断表头是否数量是否正确

var fromTo = '';

// 遍历每张表读取

for (var sheet in workbook.Sheets) {

if (workbook.Sheets.hasOwnProperty(sheet)) {

fromTo = workbook.Sheets[sheet]['!ref'];

console.log(fromTo);

persons = persons.concat(XLSX.utils.sheet_to_json(workbook.Sheets[sheet]));

// break; // 如果只取第一张表，就取消注释这行

}

//在控制台打印出来表格中的数据

console.log(persons);

localStorage.setItem('globalArr2',JSON.stringify(persons));

$("#area2").val(JSON.stringify(persons));

var html='<div>'

for(var i=0;i<persons.length;i++){

html+='<div class="mydiv"><span class="name">'+persons[i].name+'</span><span class="url">'+persons[i].url+'</span><span class="price">'+persons[i].price+'</span></div>'

}

html+='</div>'

$('#areaBody2').html(html);

$('#loading').hide();

};

// 以二进制方式打开文件

fileReader.readAsBinaryString(files[0]);

});

function sortId(a,b){

return a.price-b.price;;

}

function sortId2(a,b){

return b.price-a.price;

}

let flag=true;

function setprice(obj){

var arr=obj.val();

arr=JSON.parse(arr);

if(flag){

arr.sort(sortId);

}else{

arr.sort(sortId2);

}

flag=!flag;

var result=arr;

var html='<div>'

for(var i=0;i<result.length;i++){

html+='<div class="mydiv"><span class="name">'+result[i].name+'</span><span class="url">'+result[i].url+'</span><span class="price">'+result[i].price+'</span></div>'

}

html+='</div>'

$('#price').html(html);

}

function setname(){

var arr1=localStorage.getItem('globalArr'),arr2=localStorage.getItem('globalArr2');

arr1=JSON.parse(arr1),arr2=JSON.parse(arr2);

var result = [];

arr2.forEach(item =>{

arr1.forEach(_item =>{

if(_item.name === item.name){

result.push(item)

}

})

if(result.length>0){

var html='<div>'

for(var i=0;i<result.length;i++){

html+='<div class="mydiv"><span class="name">'+result[i].name+'</span><span class="url">'+result[i].url+'</span><span class="price">'+result[i].price+'</span></div>'

}

html+='</div>'

$('#price').html(html);

}else{

$('#price').html('<div style="color:#666;">暂无<span style="color:#f29702">【重复名称】</span>的数据！</div>');

}

function seturl(){

var arr1=localStorage.getItem('globalArr'),arr2=localStorage.getItem('globalArr2');

arr1=JSON.parse(arr1),arr2=JSON.parse(arr2);

var result = [];

arr2.forEach(item =>{

arr1.forEach(_item =>{

if(_item.url === item.url){

result.push(item)

}

})

if(result.length>0){

var html='<div>'

for(var i=0;i<result.length;i++){

html+='<div class="mydiv"><span class="name">'+result[i].name+'</span><span class="url">'+result[i].url+'</span><span class="price">'+result[i].price+'</span></div>'

}

html+='</div>'

$('#price').html(html);

}else{

$('#price').html('<div style="color:#666;">暂无<span style="color:#ff0000">【重复链接】</span>的数据！</div>');

}

</script>

</body>

</html>

2021-01-19

推荐阅读更多精彩内容