使用sheetjs读取excle表格

ansinie

已于 2022-09-15 11:54:19 修改

阅读量813

点赞数

分类专栏： JavaScript 文章标签： javascript 前端 js

于 2022-09-15 11:53:25 首次发布

本文链接：https://blog.csdn.net/qq_53496572/article/details/126868380

版权

JavaScript 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

	//cdn引入js
	<script lang="javascript" src="https://cdn.sheetjs.com/xlsx-latest/package/dist/xlsx.full.min.js"></script>
	
	var url = "http://127.0.0.1:8848/dome/file/excle1.xlsx";
	/* set up async GET request */
	var req = new XMLHttpRequest();
	req.open("GET", url, true);  //读取表格文件  如果是input选取文件那直接把文件流传入  XLSX.read(文件流); 就可以了
	req.responseType = "arraybuffer";
	req.onload = function(e) {
	  	var workbook = XLSX.read(req.response);
		console.log(workbook.Sheets)  //多个表的名称 表一 表二
		list = XLSX.utils.sheet_to_json(workbook.Sheets[workbook.SheetNames[0]])  //拿到表一的数据并转换格式
		console.log(list)  //looklook
	};

<!-- 完整案例，替换url就可使用 -->
<!DOCTYPE html>
<html>
	<head>
		<meta charset="utf-8">
		<title></title>
		<!-- use the latest version -->
		<script lang="javascript" src="https://cdn.sheetjs.com/xlsx-latest/package/dist/xlsx.full.min.js"></script>
		
	</head>
	<style type="text/css">
		.prize_list{width: 90%;max-width: 960px;height: 70vh;position: fixed;top: 50%;left: 50%;transform: translate(-50%,-50%);
		box-shadow: 0 0 5px #333;border-radius: 10px;overflow-y: auto;display: block;}
		.prize_list .item{margin-top: 10px;	text-align: center;display: flex; justify-content: space-around;}
		.prize_list .item .box{flex-shrink: 0;flex: 1;}
	</style>
	<body>
		<table class="prize_list">
			<tr class="prize_item">
			</tr>
		</table>
	</body>
	<script>
	
	var url = "http://127.0.0.1:8848/dome/file/excle1.xlsx";
	let list = ''

	/* set up async GET request */
	var req = new XMLHttpRequest();
	req.open("GET", url, true);
	req.responseType = "arraybuffer";
	req.onload = function(e) {
	  var workbook = XLSX.read(req.response);
		console.log(workbook.Sheets)
		list = XLSX.utils.sheet_to_json(workbook.Sheets[workbook.SheetNames[0]])
		let list_dom = document.querySelector('.prize_list')
		list_dom.innerHTML = ''
		list_dom.appendChild(addTH((list[0])))
		list.map(res=>{
			list_dom.appendChild(addTD(res))
		})
	};
	
	req.send();
	
	function addTH(data){
		let item = document.createElement('tr')
		item.className = 'item'
		for(let sheet in data){
			if(sheet !== '收件地址'){ //跳过某些不显示的字段
			item.innerHTML +=  `<th class="box">${sheet}</th>`	
			}
			
		}
		return item
	}
	function addTD(data){
		let item = document.createElement('tr')
		item.className = 'item'
		for(let sheet in data){
			if(sheet !== '收件地址'){  //跳过某些不显示的字段
				if(sheet === '联系电话'){
					let req = /(\d{3})\d{4}(\d{4})/;  //替换中间四位
					data[sheet] = data[sheet].toString().replace(req,"$1****$2")
				}
				item.innerHTML +=  `<td class="box">${data[sheet]}</td>`
			}
		}
		return item
	}
	
	</script>
</html>