-
先写一个文件提交
<input type="file" id="excel-file">
-
导入xlsx.core.min.js
<script type="text/javascript" src="xlsx.core.min.js"></script>
-
解析excel
<script>
$('#excel-file').change(function (e) {
var files = e.target.files;
var fileReader = new FileReader();
fileReader.onload = function (ev) {
try {
var data = ev.target.result,
workbook = XLSX.read(data, {
type: 'binary'
}), // 以二进制流方式读取得到整份excel表格对象
persons = []; // 存储获取到的数据
} catch (e) {
console.log('文件类型不正确');
return;
}
// 遍历每张表读取
for (var sheet in workbook.Sheets) {
if (workbook.Sheets.hasOwnProperty(sheet)) {
var fromTo = workbook.Sheets[sheet]['!ref'];
console.log(fromTo);
var datas = workbook.Sheets[sheet];
// 如果有不规范数据可以在这里进行处理datas
persons = persons.concat(XLSX.utils.sheet_to_json(datas));
break; // 只读了第一张表
}
}
console.log(persons);
};
// 以二进制方式打开文件
fileReader.readAsBinaryString(files[0]);
});
</script>
-
全部代码
<!DOCTYPE html>
<html>
<head>
<title></title>
</head>
<body>
<input type="file" id="excel-file">
</body>
<script type="text/javascript" src="xlsx.core.min.js"></script>
<script src="https://code.jquery.com/jquery-3.1.1.min.js"></script>
<script>
$('#excel-file').change(function (e) {
var files = e.target.files;
var fileReader = new FileReader();
fileReader.onload = function (ev) {
try {
var data = ev.target.result,
workbook = XLSX.read(data, {
type: 'binary'
}), // 以二进制流方式读取得到整份excel表格对象
persons = []; // 存储获取到的数据
} catch (e) {
console.log('文件类型不正确');
return;
}
// 遍历每张表读取
for (var sheet in workbook.Sheets) {
if (workbook.Sheets.hasOwnProperty(sheet)) {
var fromTo = workbook.Sheets[sheet]['!ref'];
console.log(fromTo);
var datas = workbook.Sheets[sheet];
console.log(datas);
// 处理不规范的数据
for (var nums in datas) {
console.log(nums);
var num = nums.replace(/[^0-9]/ig, "");
console.log(num);
var row_data = new Array();
row_data[0] = "A" + String(num);
row_data[1] = "B" + String(num);
row_data[2] = "C" + String(num);
row_data[3] = "D" + String(num);
row_data[4] = "E" + String(num);
row_data[5] = "F" + String(num);
row_data[6] = "G" + String(num);
row_data[7] = "H" + String(num);
row_data[8] = "I" + String(num);
row_data[9] = "J" + String(num);
row_data[10] = "K" + String(num);
row_data[11] = "L" + String(num);
row_data[12] = "M" + String(num);
var arry_row = new Array();
var count = 0;
for (var i of row_data) {
if (datas[i]) {
arry_row[count] = datas[i].v.replace(/\s*/g,"");
count += 1;
}
}
// 找到正确数据的列名
if (arry_row.indexOf("单位") < 0 || arry_row.indexOf("数量") < 0) {
for (var i = 0; i < row_data.length; i++) {
delete datas[row_data[i]];
}
}
else {
// 修改读取范围
datas["!ref"] = row_data[0] + ":" + datas['!ref'].split(':')[1];
break;
}
}
persons = persons.concat(XLSX.utils.sheet_to_json(datas));
break; // 只读了第一张表
}
}
console.log(persons);
};
// 以二进制方式打开文件
fileReader.readAsBinaryString(files[0]);
});
</script>
</html>
对前端不怎么熟悉，代码比较乱，仅供参考
本文链接：时光不写博客-前端解析Excel：xlsx.core.min.js
