H5导入导出Excel

最新推荐文章于 2024-03-16 01:41:39 发布

Yonfly

最新推荐文章于 2024-03-16 01:41:39 发布

阅读量2.4k

点赞数

分类专栏： HTML5+API

本文链接：https://blog.csdn.net/m0_37894611/article/details/105972309

版权

HTML5+API 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

Excel导入

<!DOCTYPE html>
<html>

	<head>
		<meta charset="UTF-8">
		<title></title>
		<script src="jquery.min.js"></script>
		<script src="xlsx.core.min.js"></script>
		<style>
			body {
				line-height: 24px;
			}
			
			table{
				border-collapse: collapse;
				margin: 0 auto;
				text-align: center;
				width: 70%;
			}
			
			table td, table th
			{
				border: 1px solid #cad9ea;
				color: #666;
				height: 30px;
			}
			
			table thead th
			{
				background-color: #CCE8EB;
				width: 100px;
			}
			
			table tr:nth-child(odd)
			{
				background: #fff;
			}
			
			table tr:nth-child(even)
			{
			    background: #F5FAFA;
			}
			
			.btn{
				float: left;
				background-color: #1278F6;
				padding: 10px;
				width: 100px;
				border: 1px solid #FFFFFF;
				text-align: center;
				border-radius: 10px;
			}
			
			.file {
			    position: relative;
			    display: inline-block;
			    background: #D0EEFF;
			    border: 1px solid #99D3F5;
			    border-radius: 4px;
			    padding: 4px 12px;
			    overflow: hidden;
			    color: #1E88C7;
			    text-decoration: none;
			    text-indent: 0;
			    line-height: 20px;
			}
			
			.file input {
			    position: absolute;
			    font-size: 100px;
			    right: 0;
			    top: 0;
			    opacity: 0;
			}
			
			.file:hover {
			    background: #AADFFD;
			    border-color: #78C3F3;
			    color: #004974;
			    text-decoration: none;
			}
		</style>
	</head>

	<body>
		<div style="text-align: center;">
			<a href="javascript:;" class="file">
				点击选择文件
				<input type="file" onchange="importf(this)" />
			</a>
		</div>
		<br />
		<table id="demo">

		</table>
		<br />
		<div style="width: 100%;text-align: center;">
			<div style="width: 30%;margin: 0 auto;">
				<div id='content' style="color: red;"></div>
				<div class="btn" title="" onclick="prep()">上一页</div>
				<div class="btn" title="" onclick="next()">下一页</div>
				<div class="btn" title="打印请使用 信封B5" onclick="printPage()">打印</div>
			</div>
		</div>
		<script>
			/*
				FileReader共有4种读取方法：
				1.readAsArrayBuffer(file)：将文件读取为ArrayBuffer。
				2.readAsBinaryString(file)：将文件读取为二进制字符串
				3.readAsDataURL(file)：将文件读取为Data URL
				4.readAsText(file, [encoding])：将文件读取为文本，encoding缺省值为'UTF-8'
            */
			var wb; //读取完成的数据
			var rABS = false; //是否将文件读取为二进制字符串
			var excel_data = null; //excel数据
			var pageNow = 1; //当前页

			function importf(obj) { //导入
				if (!obj.files) {
					return;
				}
				var f = obj.files[0];
				var reader = new FileReader();
				reader.onload = function(e) {
					var data = e.target.result;
					if (rABS) {
						wb = XLSX.read(btoa(fixdata(data)), { //手动转化
							type: 'base64'
						});
					} else {
						wb = XLSX.read(data, {
							type: 'binary'
						});
					}
					//wb.SheetNames[0]是获取Sheets中第一个Sheet的名字
					//wb.Sheets[Sheet名]获取第一个Sheet的数据
					excel_data = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]);
                    console.log(excel_data);
					pageNow = 1;
					GetPageData(excel_data, pageNow);
				};
				if (rABS) {
					reader.readAsArrayBuffer(f);
				} else {
					reader.readAsBinaryString(f);
				}
			}

			function fixdata(data) { //文件流转BinaryString
				var o = "",
					l = 0,
					w = 10240;
				for (; l < data.byteLength / w; ++l) o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w, l * w +
					w)));
				o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w)));
				return o;
			}

			var tb_title =
				`
				<tr>
					<td>资产编号</td>
					<td>资产名称</td>
					<td>规格型号</td>
					<td>所属部门</td>
					<td>年</td>
					<td>月</td>
					<td>日</td>
				</tr>
			`
			var pageSize = 8; //每页数据条数
			var page_count = 0;
			//获取指定页数据
			function GetPageData(data, i) {
				if (!data) {
					alert("数据异常");
					return;
				}
				var tb_body = '';
				var pageCount = data.length;
				page_count = Math.ceil(pageCount / pageSize);
				if (i < 0 || i > page_count) {
					i = 1;
					alert("页码超出范围!!!");
					return;
				}
				var page_Start = (i - 1) * pageSize;
				var page_End = i * pageSize - 1;
				if (page_End > pageCount - 1) {
					page_End = pageCount - 1;
				}
				for (var inx = page_Start; inx <= page_End; inx++) {
					tb_body +=
						`
						<tr class='needdata'>
							<td>${data[inx].资产编号}</td>
							<td>${data[inx].资产名称}</td>
							<td>${data[inx].规格型号}</td>
							<td>${data[inx].所属部门}</td>
							<td>${data[inx].年}</td>
							<td>${data[inx].月}</td>
							<td>${data[inx].日}</td>
						</tr>
					`
				}
				document.getElementById("demo").innerHTML = tb_title + tb_body
				document.getElementById("content").innerHTML = '共 ' + pageCount + ' 条数据 ' + page_count + ' 页 ,当前页为 ' + i
			}
			// 上一页
			function prep() {
				if (pageNow > 1) {
					pageNow--;
				}
				GetPageData(excel_data, pageNow);
			}
			//下一页 
			function next() {
				if (pageNow < page_count) {
					pageNow++;
				}
				GetPageData(excel_data, pageNow);
			}
			//打印
			function printPage() {
				window.print();
			}
		</script>
	</body>

</html>

导出Excel,json内无内容，可添加自行测试

<!DOCTYPE html>
<html>

	<head>
		<meta charset="UTF-8">
		<title></title>
		<script src="xlsx.core.min.js"></script>
	</head>

	<body>
		<button onclick="downloadExl(jsono)">导出</button>
		<!--以下a标签不需要内容-->
		<a href="" download="文件名.xlsx" id="hf"></a>
		<script>
			var jsono = [{ //测试数据

			}];
			var tmpDown; //导出的二进制对象
			function downloadExl(json, type) {
				var tmpdata = json[0];
				json.unshift({});
				var keyMap = []; //获取keys
				//keyMap =Object.keys(json[0]);
				for (var k in tmpdata) {
					keyMap.push(k);
					json[0][k] = k;
				}
				var tmpdata = []; //用来保存转换好的json 
				json.map((v, i) => keyMap.map((k, j) => Object.assign({}, {
					v: v[k],
					position: (j > 25 ? getCharCol(j) : String.fromCharCode(65 + j)) + (i + 1)
				}))).reduce((prev, next) => prev.concat(next)).forEach((v, i) => tmpdata[v.position] = {
					v: v.v
				});
				var outputPos = Object.keys(tmpdata); //设置区域,比如表格从A1到D10
				var tmpWB = {
					SheetNames: ['mySheet'], //保存的表标题
					Sheets: {
						'mySheet': Object.assign({},
							tmpdata, //内容
							{
								'!ref': outputPos[0] + ':' + outputPos[outputPos.length - 1] //设置填充区域
							})
					}
				};
				tmpDown = new Blob([s2ab(XLSX.write(tmpWB, {
						bookType: (type == undefined ? 'xlsx' : type),
						bookSST: false,
						type: 'binary'
					} //这里的数据是用来定义导出的格式类型
				))], {
					type: ""
				}); //创建二进制对象写入转换好的字节流
				var href = URL.createObjectURL(tmpDown); //创建对象超链接
				document.getElementById("hf").href = href; //绑定a标签
				document.getElementById("hf").click(); //模拟点击实现下载
				setTimeout(function() { //延时释放
					URL.revokeObjectURL(tmpDown); //用URL.revokeObjectURL()来释放这个object URL
				}, 100);
			}

			function s2ab(s) { //字符串转字符流
				var buf = new ArrayBuffer(s.length);
				var view = new Uint8Array(buf);
				for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
				return buf;
			}
			// 将指定的自然数转换为26进制表示。映射关系：[0-25] -> [A-Z]。
			function getCharCol(n) {
				let temCol = '',
					s = '',
					m = 0
				while (n > 0) {
					m = n % 26 + 1
					s = String.fromCharCode(m + 64) + s
					n = (n - m) / 26
				}
				return s
			}
		</script>
	</body>

</html>