1. h5页面excel转json
1.1. h5页面excel转json
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8"/>
<meta name="viewport" content="width=device-width, initial-scale=1.0"/>
<title>Excel转Json</title>
<!-- <script type="text/javascript" src="https://unpkg.com/axios@0.24.0/dist/axios.min.js"></script>-->
<!-- <script type="text/javascript" src="https://cdn.bootcss.com/xlsx/0.11.5/xlsx.core.min.js"></script>-->
<!-- <script type="text/javascript" src="https://cdn.bootcss.com/jquery/3.2.1/jquery.js"></script>-->
<!-- <script type="text/javascript" src="https://cdn.sheetjs.com/xlsx-latest/package/dist/xlsx.full.min.js"></script>-->
<script type="text/javascript" src="../static/util/excel/axios.min.js"></script>
<script type="text/javascript" src="../static/util/excel/xlsx.core.min.js"></script>
<script type="text/javascript" src="../static/js/jquery-3.1.1.min.js"></script>
<script type="text/javascript" src="../static/util/excel/xlsx.full.min.js"></script>
<link rel="stylesheet" type="text/css" href="../css/page-excel.css"/>
</head>
<body>
<div>
<section>
<input class="up_input_excel" type="file" multiple="multiple"
id="up_excel_id" accept=".json,.yml,.xlsx"
capture="camera" enctype="multipart/form-data"/>
<label class="add_accessory_btn up_img" for="up_excel_id"></label>
</section>
<div id="excel_file_id"></div>
<div id="excel_file_result"></div>
</div>
<script>
$(function () {
//监听
listener()
});
/**
* 监听
*/
function listener() {
//上传Excel文件
$('#up_excel_id').change(function () {
var excelFile = '';
var files = this.files
if (files) {
if (files.length > 1) {
mui.alert('每次只能上传一个文件!', ' ', '确定',
function () {
$('.mui-popup-backdrop').css({'display': 'none'})
}, 'div');
} else {
for (var i = 0; i < files.length; i++) {
excelFile = files[i];
$('#excel_file_id').html(excelFile.name)
//Excel转化为Json
transExcelToJson(excelFile)
}
}
}
})
}
/**
* Excel转化为Json
*/
function transExcelToJson() {
if (!excelFile) {
alert("请选择文件");
return;
}
var fileReader = new FileReader();
fileReader.onload = function (fileRes) {
try {
var fileData = fileRes.target.result
// 以二进制流方式读取得到整份excel表格对象
var workbook = XLSX.read(fileData, {
type: 'binary'
})
var fileFlowArr = []; // 存储获取到的数据
} catch (e) {
console.log('文件类型不正确');
return;
}
// 表格的表格范围,可用于判断表头是否数量是否正确
// 遍历每张表读取
for (var sheet in workbook.Sheets) {
if (workbook.Sheets.hasOwnProperty(sheet)) {
var excelObj = {};
excelObj.name = sheet;
excelObj.data = XLSX.utils.sheet_to_json(workbook.Sheets[sheet]);
fileFlowArr.push(excelObj);
// break; // 如果只取第一张表,就取消注释这行
}
}
//在控制台打印出来表格中的数据
let excelJson = {};
for (let i = 0; i < fileFlowArr.length; i++) {
let sheet = fileFlowArr[i];
if (null == sheet) {
continue;
}
if (sheet.name.indexOf('ignore_') === 0) {
continue;
}
let childJSON = null;
let sheetArr = sheet.data;
let keys = sheetArr[0];
if (keys.length === 1) {
//纯数组 [] 表格名字:sheet.name
childJSON = [];
for (let j = 0; j < sheetArr.length; j++) {
let fieldType = sheetArr[j][0];
if (fieldType && typeof fieldType == 'string'
&& fieldType.indexOf('[') == 0) {
fieldType = JSON.parse(fieldType);
}
childJSON[j] = fieldType;
}
} else if (sheetArr.length === 2) {
//对象 表格名字:sheet.name
childJSON = {};
let value = sheetArr[1];
for (let fieldTypeArr in value) {
let fieldType = value[fieldTypeArr];
if (fieldType && typeof fieldType == 'string'
&& fieldType.indexOf('[') == 0) {
fieldType = JSON.parse(fieldType);
}
childJSON[fieldTypeArr] = fieldType;
}
} else if (sheetArr.length > 3) {
//数组 表格名字:sheet.name
childJSON = [];
for (let j = 1; j < sheetArr.length; j++) {
if (null == sheetArr[j] || '' == sheetArr[j]) {
continue;
}
let dataJson = {};
for (let k in keys) {
let key = k;
if (key.indexOf('.') !== -1) {
let tempKeys = key.split('.');
let tempJSON = dataJson;
for (let t = 0; t < tempKeys.length - 1; t++) {
if (null == tempJSON[tempKeys[t]]) {
tempJSON[tempKeys[t]] = {};
}
tempJSON = tempJSON[tempKeys[t]];
}
tempJSON[tempKeys[tempKeys.length - 1]] = sheetArr[j][k];
} else {
let fieldType = sheetArr[j][k];
if (fieldType && typeof fieldType == 'string'
&& fieldType.indexOf('[') == 0) {
fieldType = JSON.parse(fieldType);
}
dataJson[key] = fieldType;
}
}
childJSON.push(dataJson);
}
}
if (null == childJSON) {
continue;
}
if (sheet.name.toLowerCase().indexOf('sheet') !== -1) {
if (fileFlowArr.length === 1) {
excelJson = childJSON;
} else {
Object.assign(excelJson, childJSON);
}
} else {
excelJson[sheet.name] = childJSON;
}
}
console.log(excelJson);
excelJson = [{level: excelJson}];
excelJsonStr = JSON.stringify(excelJson);
$('#excel_file_result').html('转换结果:' + excelJsonStr)
};
// 以二进制方式打开文件
fileReader.readAsBinaryString(excelFile);
}
</script>
</body>
</html>
1.2. h5页面excel(本地)转json
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8"/>
<meta name="viewport" content="width=device-width, initial-scale=1.0"/>
<title>配置工具</title>
<link rel="stylesheet" type="text/css" href="../static/css/mui.min.css"/>
<script type="text/javascript" src="../static/js/mui.min.js"></script>
<script type="text/javascript" src="../static/util/excel/xlsx.core.min.js"></script>
<script type="text/javascript" src="../static/js/jquery-3.1.1.min.js"></script>
<link rel="stylesheet" type="text/css" href="../css/page-excel.css"/>
</head>
<body>
<div>
<section>
<input class="up_input_excel" type="file" multiple="multiple"
id="up_excel_id" accept=".json,.yml,.xlsx"
capture="camera" enctype="multipart/form-data"/>
<label class="add_accessory_btn up_img" for="up_excel_id"></label>
</section>
<div id="excel_file_id"></div>
<div id="excel_file_result"></div>
</div>
<script>
$(function () {
//监听
listener()
//本地Excel转化为Json
getLocalExcelJson()
});
/**
* 本地Excel转化为Json
*/
function getLocalExcelJson() {
//本地xls文件路径
let url = '../assets/excel/china_geo.xlsx';
let xhr = new XMLHttpRequest();
xhr.open('GET', url, true);
xhr.responseType = "blob"
xhr.onload = function() {
if (this.status === 200) {
const blob = this.response
const fileReader = new FileReader()
fileReader.readAsBinaryString(blob)
fileReader.onload = function (file) {
console.log("==file==",file)
let dataBinary = file.target.result
// xlsx.js读取
let workBook = XLSX.read(dataBinary, {type: 'binary', cellDates: true})
let workSheet = workBook.Sheets[workBook.SheetNames[0]]
const data = XLSX.utils.sheet_to_json(workSheet)
console.log(data)
var excelJson=JSON.stringify(data)
//$('#excel_file_id').html(excelFile.name)
$('#excel_file_result').html('转换结果:' + excelJson)
}
}
}
xhr.send();
}
/**
* 监听
*/
function listener() {
//上传Excel文件
$('#up_excel_id').change(function () {
})
}
</script>
</body>
</html>