<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Excel批量处理</title>
<script src="https://cdn.bootcss.com/jquery/3.2.1/jquery.js"></script>
<script src="https://cdn.bootcss.com/xlsx/0.11.5/xlsx.core.min.js"></script>
<script type="text/javascript" src="https://cdn.jsdelivr.net/npm/echarts/dist/echarts.min.js"> </script>
<script src="https://unpkg.com/axios/dist/axios.min.js"></script>
<style>
#main0 {
width: 500px;
height: 500px;
border: 1px solid #ddd;
}
#main1 {
width: 500px;
height: 500px;
border: 1px solid #ddd;
}
#main2 {
width: 500px;
height: 500px;
border: 1px solid #ddd;
}
</style>
</head>
<body>
<input type="file" id="excel-file">
<div style="width: 100%; display: flex; flex-wrap: wrap;">
<div id="main0">
</div>
<div id="main1">
</div>
<div id="main2">
</div>
</div>
<script>
var _data0 = []
var _data1 = []
var _data2 = []
//给input标签绑定change事件,一上传选中的.xls文件就会触发该函数
$('#excel-file').change(function (e) {
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表格对象
} catch (e) {
console.log('文件类型不正确');
return;
}
// 表格的表格范围,可用于判断表头是否数量是否正确
var fromTo = '';
var count = 0
// 遍历每张表读取
for (var sheet in workbook.Sheets) {
if (workbook.Sheets.hasOwnProperty(sheet)) {
fromTo = workbook.Sheets[sheet]['!ref'];
// _data = _data.concat(XLSX.utils.sheet_to_json(workbook.Sheets[sheet]));
_data = XLSX.utils.sheet_to_json(workbook.Sheets[sheet])
switch (count) {
case 0:
_data0 = XLSX.utils.sheet_to_json(workbook.Sheets[sheet])
break;
case 1:
_data1 = XLSX.utils.sheet_to_json(workbook.Sheets[sheet])
break;
case 2:
_data2 = XLSX.utils.sheet_to_json(workbook.Sheets[sheet])
break;
case 3:
_data3 = XLSX.utils.sheet_to_json(workbook.Sheets[sheet])
break;
default:
break;
}
count++
console.log("fileReader.onload -> _data", _data)
// break; // 如果只取第一张表,就取消注释这行
}
}
var __data0 = getData(_data0)
var __data1 = getData(_data1)
var __data2 = getData(_data2)
var __data3 = getData(_data3)
getECharts0(__data0, 0)
getECharts1(__data1, 1, __data2)
getECharts2(__data3, 2)
};
// 以二进制方式打开文件
fileReader.readAsBinaryString(files[0]);
});
// 柱状图
function getECharts0(_data, index) {
console.log("_data", _data)
var chartDom = document.getElementById('main' + index);
var myChart = echarts.init(chartDom);
var option;
option = {
legend: {
show: true
},
xAxis: {
name: '月份',
type: 'category',
data: _data["月份"]
},
yAxis: {
name: '指标',
type: 'value'
},
series: [{
name: '销量',
data: _data["销量"],
type: 'bar',
showBackground: true,
backgroundStyle: {
color: 'rgba(180, 180, 180, 0.2)'
}
},
{
name: '利润',
data: _data["利润"],
type: 'bar',
showBackground: true,
color: '#ff0',
backgroundStyle: {
color: 'rgba(180, 180, 180, 0.2)'
}
}
]
};
option && myChart.setOption(option);
}
// 饼状图
function getECharts1(_data, index, _data1) {
console.log("_data1", _data1)
console.log("_data", _data)
var data = []
var data1 = []
_data['销量'].forEach((item, index) => {
data.push({
value: item,
name: _data['门店'][index]
})
})
_data1['销量'].forEach((item, index) => {
data1.push({
value: item,
name: _data1['月份'][index]
})
})
console.log("data", data)
var chartDom = document.getElementById('main' + index);
var myChart = echarts.init(chartDom);
var option;
option = {
legend: {
show: true
},
tooltip: {
trigger: 'item',
formatter: '{a} <br/>{b}: {c} ({d}%)'
},
series: [{
name: '销量',
data: data,
type: 'pie',
showBackground: true,
radius: [0, '30%'],
backgroundStyle: {
color: 'rgba(180, 180, 180, 0.2)'
},
label: {
position: 'inner',
},
},
{
name: '销量',
data: data1,
type: 'pie',
showBackground: true,
radius: ['45%', '60%'],
backgroundStyle: {
color: 'rgba(180, 180, 180, 0.2)'
}
}
]
};
option && myChart.setOption(option);
}
// 折线图
function getECharts2(_data, index) {
// console.log("_data", _data)
var chartDom = document.getElementById('main' + index);
var myChart = echarts.init(chartDom);
var option;
option = {
legend: {
show: true
},
xAxis: {
name: '月份',
type: 'category',
data: _data["月份"]
},
yAxis: {
name: '销售',
type: 'value'
},
series: [{
name: '整体',
data: _data["整体"],
type: 'line',
showBackground: true,
backgroundStyle: {
color: 'rgba(180, 180, 180, 0.2)'
}
},
{
name: '门店1',
data: _data["门店1"],
type: 'line',
showBackground: true,
backgroundStyle: {
color: 'rgba(180, 180, 180, 0.2)'
}
},
{
name: '门店2',
data: _data["门店2"],
type: 'line',
showBackground: true,
backgroundStyle: {
color: 'rgba(180, 180, 180, 0.2)'
}
},
{
name: '门店3',
data: _data["门店3"],
type: 'line',
showBackground: true,
backgroundStyle: {
color: 'rgba(180, 180, 180, 0.2)'
}
}
]
};
option && myChart.setOption(option);
}
function getData(_data) {
var ret = {
}
_data.forEach((item, index) => {
for (var name in item) {
if (item.hasOwnProperty(name)) {
if (!ret[name]) {
ret[name] = []
}
ret[name].push(item[name])
}
}
item.hasOwnProperty()
});
return ret
}
function getData1(_data) {
var ret = {}
_data.forEach((item, index) => {
for (var name in item) {
if (item.hasOwnProperty(name)) {
if (!ret[name]) {
ret[name] = []
}
ret[name].push(item[name])
}
}
item.hasOwnProperty()
});
return ret
}
// axiosGetData()
// function axiosGetData() {
// console.log(axios);
// axios.get('/user', {
// params: {
// ID: 12345
// }
// })
// .then(function (response) {
// console.log(response);
// })
// .catch(function (error) {
// console.log(error);
// });
// }
</script>
</body>
</html>