纯HTML加javascript实现：模拟Excel批量VLOOKUP处理文件合并，以及合并后下载

已于 2024-05-31 09:24:03 修改
阅读量152 收藏
点赞数 2
文章标签： excel javascript
于 2024-05-30 17:07:06 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/jun_bai/article/details/139329236
版权

一个表格A是工资表，缺少补贴金额，另一个表格B是补贴金额，要将B表中的补贴金额，以工号为关联填充到A表中的补贴金额中，使用Excel的VLOOKUP函数太麻烦，东拼西凑做了个网页实现了这个效果，给人事科用，嘎嘎香。（注意两个表格都需要有工号字段作为关联，或者自定义一个字段作为关联用）

用到的干货：
1。纯网页上传xls。
2。Excel转Table显示。
3。Table表格行遍历算法。
4。Table表格转xls文件下载。

完整代码如下（粘贴到.HTML后缀的文本文件中即可）：

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Excel批量VLOOKUP处理</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 XXXsrc="js/jquery.js"></script>
    <script XXXsrc="js/xlsx.core.min.js"></script>
</head>
<body>
<center><h1>Excel文件合并</h1></center>
表格A：<input type="file" id="excel-file" accept=".xls,.xlsx"> 
<script>	
	function jsonToTable1(jsonData) {
	  let table = '<table border=1 id="tableA" >'; 
	  var sel = "<select id='putfed'><option value=''></option>";
	  // 添加表头
	  table += '<tr bgcolor="cccccc">';
	  Object.keys(jsonData[0]).forEach(key => {
		table += `<th>${key}</th>`;
		sel += `<option value="${key}">${key}</option>`;
	  });
	  sel = sel + "</select>";
	  table += '</tr>';
	  document.getElementById("xlsA").innerHTML = sel ;
	  document.getElementById("xlsfile2").style.display = "" ;
	  // 添加表数据
	  jsonData.forEach(row => {
		table += '<tr>';
		Object.values(row).forEach(value => {
		  table += `<td>${value}</td>`;
		});
		table += '</tr>';
	  });	 
	  table += '</table>';
	  document.getElementById("xlsRunBt").style.display = "none" ;
	  return table;
	}
</script>
<script>	
	function jsonToTable2(jsonData) { 
	  let table = '<table border=1 id="tableB">'; 
      var sell  = "<select id='linkfed'><option value=''></option>";	
      var self  = "<select id='fromfed'><option value=''></option>";	  
	  // 添加表头
	  table += '<tr bgcolor="cccccc">';
	  Object.keys(jsonData[0]).forEach(key => {
		table += `<th>${key}</th>`; 
		sell += `<option value="${key}">${key}</option>`;
		self += `<option value="${key}">${key}</option>`;
	  }); 
	  table += '</tr>';
	  document.getElementById("xlsB").innerHTML = self ;
	  document.getElementById("xlsLink").innerHTML = sell ;
 	  // 添加表数据
	  jsonData.forEach(row => {
		table += '<tr>';
		Object.values(row).forEach(value => {
		  table += `<td>${value}</td>`;
		});
		table += '</tr>';
	  });	 
	  table += '</table>';
	  document.getElementById("xlsRunBt").style.display = "" ;
	  return table;
	}
</script>
<script> 
   function showTable(str){
        document.getElementById("xlstable").innerHTML = jsonToTable1(str);
   }
   function showTableSrc(str){ 
        document.getElementById("xlstableSrc").innerHTML = jsonToTable2(str);
   }
</script>
<div id='xlsfile2' style='display:none;'>表格B：<input type="file" id="excel-fileSrc" accept=".xls,.xlsx"></div>
<div>
	<table border=1>
	   <tr>
	     <td>以关联字段</td>
		 <td id="xlsLink"></td>
	     <td>将表格B的字段</td>
		 <td id="xlsB"></td>
		 <td>填充到表格A的字段</td>
		 <td id="xlsA"></td>
		 <td ><input type='button' id="xlsRunBt" style="display:none;" value='   开始   ' onclick="xls2xls();"></td>
		 <td ><input type='button' id="xlsDwnBt" style="display:none;" value='   下载   ' onclick="xls2dwn();"></td>
	   </tr>
	</table>
</div>
<div id='xlstable'></div>
<div id='xlstableSrc'></div>
<br><br><br>
</body>
</html> 
<script>	
	function jsonToTable(jsonData) {
	  let table = '<table border=1>'; 
	  // 添加表头
	  table += '<tr bgcolor="cccccc">';
	  Object.keys(jsonData[0]).forEach(key => {
		table += `<th>${key}</th>`;
	  });
	  table += '</tr>';
	 
	  // 添加表数据
	  jsonData.forEach(row => {
		table += '<tr>';
		Object.values(row).forEach(value => {
		  table += `<td>${value}</td>`;
		});
		table += '</tr>';
	  });	 
	  table += '</table>';
	  return table;
	}
</script>
<script>
    //给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表格对象
                var persons = []; // 存储获取到的数据
            } catch (e) {
                console.log('文件类型不正确');
                return;
            }
            // 表格的表格范围，可用于判断表头是否数量是否正确
            var fromTo = '';
            // 遍历每张表读取
            for (var sheet in workbook.Sheets) {
                if (workbook.Sheets.hasOwnProperty(sheet)) {
                    fromTo = workbook.Sheets[sheet]['!ref'];
                    //console.log(fromTo);
                    persons = persons.concat(XLSX.utils.sheet_to_json(workbook.Sheets[sheet]));
					showTable(persons);
                    break; // 如果只取第一张表，就取消注释这行
                }
            }
            //在控制台打印出来表格中的数据
            //console.log(persons);
        };
        // 以二进制方式打开文件
        fileReader.readAsBinaryString(files[0]);
    });
</script>
<script>
    //给input标签绑定change事件，一上传选中的.xls文件就会触发该函数
    $('#excel-fileSrc').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表格对象
                var persons = []; // 存储获取到的数据
            } catch (e) {
                console.log('文件类型不正确');
                return;
            }
            // 表格的表格范围，可用于判断表头是否数量是否正确
            var fromTo = '';
            // 遍历每张表读取
            for (var sheet in workbook.Sheets) {
                if (workbook.Sheets.hasOwnProperty(sheet)) {
                    fromTo = workbook.Sheets[sheet]['!ref'];
                    //console.log(fromTo);
                    persons = persons.concat(XLSX.utils.sheet_to_json(workbook.Sheets[sheet]));
					showTableSrc(persons);
                    break; // 如果只取第一张表，就取消注释这行
                }
            }
            //在控制台打印出来表格中的数据
            //console.log(persons);
        };
        // 以二进制方式打开文件
        fileReader.readAsBinaryString(files[0]);
    });
</script>	
<script>
    function xls2xls(){
		// 获取table元素
		var mylink = $("#linkfed").val();
		var myfrom = $("#fromfed").val();
		var myput  = $("#putfed").val();
		var fedd   = 0; var svdd = 0;
		//alert(myput);
		var table = document.getElementById("tableA");
		    //遍历所有的行
		for (var i = 0, row; row = table.rows[i]; i++) {
		    //遍历当前行的所有列
		  for (var j = 0, col; col = row.cells[j]; j++) {
			// 对每个单元格进行操作
			//console.log("行"+i +"列"+j+"="+col.innerText);
			var vvv = col.innerText.trim();
			if(i==0 && mylink==vvv){ fedd = j ;}  //第一行确定关联字段
			if(i==0 && myput ==vvv){ svdd = j ; } //要写入的字段
		  }
		  if(i > 0 ){
		    var codk = ""; try{ codk=row.cells[fedd].innerText.trim(); }catch(e){ } //获得关联字段值
            var ppp = ""; try{ ppp = getxlsfed(codk); }catch(e){ }
			console.log(i+"开始获取："+mylink+" = "+codk+" = "+ppp);
			try{ row.cells[svdd].innerText = ppp ; }catch(e){ }
		  }
		}
		document.getElementById("xlsDwnBt").style.display = "" ;
	}
</script>
<script>
    function getxlsfed(vc){
		// 获取table元素
		var mylink = $("#linkfed").val();
		var myfrom = $("#fromfed").val();
		var myput  = $("#putfed").val();
		var fedd   = 0; var redd = 0;
		//alert(myput);
		var table = document.getElementById("tableB");
		    //遍历所有的行
		for (var i = 0, row; row = table.rows[i]; i++) {
		    //遍历当前行的所有列
		  for (var j = 0, col; col = row.cells[j]; j++) {
			// 对每个单元格进行操作
			//console.log("行"+i +"列"+j+"="+col.innerText);
			var vvv = col.innerText.trim();
			if(i==0 && mylink==vvv){ fedd = j ; } //获得关联字段ID
			if(i==0 && myfrom==vvv){ redd = j ; } //获得来源字段ID
		  }
		  if(i > 0){
		     var codk = ""; try{ codk=row.cells[fedd].innerText.trim(); }catch(e){ } //获得关联字段值
			 var codv = ""; try{ codv=row.cells[redd].innerText.trim(); }catch(e){ } //获得来源字段值
			 if(vc==codk){ 
			   return codv;
			 }
		  }
		}
	}
</script>
<script> 
        function xls2dwn() {
            var table1 = document.querySelector("#tableA");
            var sheet = XLSX.utils.table_to_sheet(table1);//将一个table对象转换成一个sheet对象
            openDownloadDialog(sheet2blob(sheet),'下载.xlsx');
        }

        // 将一个sheet转成最终的excel文件的blob对象，然后利用URL.createObjectURL下载
        function sheet2blob(sheet, sheetName) {
            sheetName = sheetName || 'sheet1';
            var workbook = {
                SheetNames: [sheetName],
                Sheets: {}
            };
            workbook.Sheets[sheetName] = sheet; // 生成excel的配置项

            var wopts = {
                bookType: 'xlsx', // 要生成的文件类型
                bookSST: false, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
                type: 'binary'
            };
            var wbout = XLSX.write(workbook, wopts);
            var blob = new Blob([s2ab(wbout)], {
                type: "application/octet-stream"
            }); // 字符串转ArrayBuffer
            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;
            }
            return blob;
        }

        function openDownloadDialog(url, saveName) {
            if (typeof url == 'object' && url instanceof Blob) {
                url = URL.createObjectURL(url); // 创建blob地址
            }
            var aLink = document.createElement('a');
            aLink.href = url;
            aLink.download = saveName || ''; // HTML5新增的属性，指定保存文件名，可以不要后缀，注意，file:///模式下不会生效
            var event;
            if (window.MouseEvent) event = new MouseEvent('click');
            else {
                event = document.createEvent('MouseEvents');
                event.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null);
            }
            aLink.dispatchEvent(event);
        } 
</script>

上面代码中xls转JSON，再转回table过程中，发现有单元格为空的，在table中缺少td，导致数据不完整，后来发现XLSX.utils.sheet_to_json改为XLSX.utils.sheet_to_html，可以直接将xls转为table，又将上面的代码改进了一下，更加简化了，数据也更加完整了。代码如下：

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Excel批量VLOOKUP处理</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 XXXsrc="js/jquery.js"></script>
    <script XXXsrc="js/xlsx.core.min.js"></script>
</head>
<body>
<center><h1>Excel文件合并</h1></center>
表格A：<input type="file" id="excel-fileA" accept=".xls,.xlsx"> 
<div id='xlsfile2' style='display:none;'>
表格B：<input type="file" id="excel-fileB" accept=".xls,.xlsx"></div>
<div>
	<table border=1>
	   <tr>
	     <td>以关联字段</td>
		 <td id="xlsLink"></td>
	     <td>将表格B的字段</td>
		 <td id="xlsB"></td>
		 <td>填充到表格A的字段</td>
		 <td id="xlsA"></td>
		 <td ><input type='button' id="xlsRunBt" style="display:none;" value='   开始   ' onclick="xls2xls();"></td>
		 <td ><input type='button' id="xlsDwnBt" style="display:none;" value='   下载   ' onclick="xls2dwn();"></td>
	   </tr>
	</table>
</div>
<div id='xlstableA'></div> 
<div id='xlstableB'></div>
<br><br><br>
</body>
</html>  
<script>
    //给input标签绑定change事件，一上传选中的.xls文件就会触发该函数
    $('#excel-fileA').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表格对象
                var persons = []; // 存储获取到的数据
            } catch (e) {
                console.log('文件类型不正确');
                return;
            }
            // 表格的表格范围，可用于判断表头是否数量是否正确
            var fromTo = '';
            // 遍历每张表读取
            for (var sheet in workbook.Sheets) {
                if (workbook.Sheets.hasOwnProperty(sheet)) {
                    fromTo = workbook.Sheets[sheet]['!ref'];
                    //console.log(fromTo);
					var xlstxt =XLSX.utils.sheet_to_html(workbook.Sheets[sheet]);
					//console.log(xlstxt);
					showTableA(xlstxt);
                    break; // 如果只取第一张表，就取消注释这行
                }
            }
            //在控制台打印出来表格中的数据
            //console.log(persons);
        };
        // 以二进制方式打开文件
        fileReader.readAsBinaryString(files[0]);
    });
</script>
<script>
    //给input标签绑定change事件，一上传选中的.xls文件就会触发该函数
    $('#excel-fileB').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表格对象
                var persons = []; // 存储获取到的数据
            } catch (e) {
                console.log('文件类型不正确');
                return;
            }
            // 表格的表格范围，可用于判断表头是否数量是否正确
            var fromTo = '';
            // 遍历每张表读取
            for (var sheet in workbook.Sheets) {
                if (workbook.Sheets.hasOwnProperty(sheet)) {
                    fromTo = workbook.Sheets[sheet]['!ref'];
                    var xlstxt =XLSX.utils.sheet_to_html(workbook.Sheets[sheet]);
					//console.log(xlstxt);
					showTableB(xlstxt);
                    break; // 如果只取第一张表，就取消注释这行
                }
            }
            //在控制台打印出来表格中的数据
            //console.log(persons);
        };
        // 以二进制方式打开文件
        fileReader.readAsBinaryString(files[0]);
    });
</script>	
<script>
    function xls2xls(){
		// 获取table元素
		var mylink = $("#linkfed").val();
		var myfrom = $("#fromfed").val();
		var myput  = $("#putfed").val();
		
		if(mylink==""){ alert("请选择表格关联字段"); return ;}
		if(myfrom==""){ alert("请选择表格B中要提取的字段"); return ;}
		if(myput=="") { alert("请选择表格A中要填充的字段"); return ;}
		
		var fedd   = 0; var svdd = 0;
		//alert(myput);
		var table = document.getElementById("tableA");
		    //遍历所有的行
		for (var i = 0, row; row = table.rows[i]; i++) {
		    //遍历当前行的所有列
		  for (var j = 0, col; col = row.cells[j]; j++) {
			// 对每个单元格进行操作
			//console.log("行"+i +"列"+j+"="+col.innerText);
			var vvv = col.innerText.trim();
			if(i==0 && mylink==vvv){ fedd = j ; } //第一行确定关联字段
			if(i==0 && myput ==vvv){ svdd = j ; } //要写入的字段
		  }
		  if(i > 0 ){
		    var codk = ""; try{ codk=row.cells[fedd].innerText.trim(); }catch(e){ } //获得关联字段值
            var ppp = ""; try{ ppp = getxlsfed(codk); }catch(e){ }
			console.log(i+"开始获取："+mylink+" = "+codk+" = "+ppp);
			try{ row.cells[svdd].innerText = ppp ; }catch(e){ }
		  }
		}
		document.getElementById("xlsDwnBt").style.display = "" ;
	}
</script>
<script>
    function getxlsfed(vc){
		// 获取table元素
		var mylink = $("#linkfed").val();
		var myfrom = $("#fromfed").val();
		var myput  = $("#putfed").val();
		var fedd   = 0; var redd = 0;
		//alert(myput);
		var table = document.getElementById("tableB");
		    //遍历所有的行
		for (var i = 0, row; row = table.rows[i]; i++) {
		    //遍历当前行的所有列
		  for (var j = 0, col; col = row.cells[j]; j++) {
			// 对每个单元格进行操作
			//console.log("行"+i +"列"+j+"="+col.innerText);
			var vvv = col.innerText.trim();
			if(i==0 && mylink==vvv){ fedd = j ; } //获得关联字段ID
			if(i==0 && myfrom==vvv){ redd = j ; } //获得来源字段ID
		  }
		  if(i > 0){
		     var codk = ""; try{ codk=row.cells[fedd].innerText.trim(); }catch(e){ } //获得关联字段值
			 var codv = ""; try{ codv=row.cells[redd].innerText.trim(); }catch(e){ } //获得来源字段值
			 if(vc==codk){ 
			   return codv;
			 }
		  }
		}
	}
</script>
<script> 
        function xls2dwn() {
            var table1 = document.querySelector("#tableA");
            var sheet = XLSX.utils.table_to_sheet(table1);//将一个table对象转换成一个sheet对象
            openDownloadDialog(sheet2blob(sheet),'下载.xlsx');
        }

        // 将一个sheet转成最终的excel文件的blob对象，然后利用URL.createObjectURL下载
        function sheet2blob(sheet, sheetName) {
            sheetName = sheetName || 'sheet1';
            var workbook = {
                SheetNames: [sheetName],
                Sheets: {}
            };
            workbook.Sheets[sheetName] = sheet; // 生成excel的配置项
            var wopts = {
                bookType: 'xlsx', // 要生成的文件类型
                bookSST: false, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
                type: 'binary'
            };
            var wbout = XLSX.write(workbook, wopts);
            var blob = new Blob([s2ab(wbout)], {
                type: "application/octet-stream"
            }); // 字符串转ArrayBuffer
            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;
            }
            return blob;
        }

        function openDownloadDialog(url, saveName) {
            if (typeof url == 'object' && url instanceof Blob) {
                url = URL.createObjectURL(url); // 创建blob地址
            }
            var aLink = document.createElement('a');
            aLink.href = url;
            aLink.download = saveName || ''; // HTML5新增的属性，指定保存文件名，可以不要后缀，注意，file:///模式下不会生效
            var event;
            if (window.MouseEvent) event = new MouseEvent('click');
            else {
                event = document.createEvent('MouseEvents');
                event.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null);
            }
            aLink.dispatchEvent(event);
        } 
</script>
<script>
  function showTableA(htmll){
          htmll = htmll.replace("<table" , "<table id='tableA' border=1 ");
          document.getElementById("xlstableA").innerHTML = htmll;
		  try{ dealTableA(); }catch(e){ }
  }
  function showTableB(htmll){
          htmll = htmll.replace("<table" , "<table id='tableB' border=1 ");
          document.getElementById("xlstableB").innerHTML = htmll;
		  try{ dealTableB(); }catch(e){ }
  }
</script>
<script>
    function dealTableA(){ 
	    var table = document.getElementById("tableA");
		var sell  = "<select id='putfed'><option value=''></option>";
		    //遍历所有的行
		for (var i = 0, row; row = table.rows[i]; i++) {
		    //遍历当前行的所有列
		  for (var j = 0, col; col = row.cells[j]; j++) {
			// 对每个单元格进行操作
			//console.log("行"+i +"列"+j+"="+col.innerText);
			var vvv = col.innerText.trim();
			if(i==0){
			  console.log(vvv);
			  sell = sell + "<option value='"+vvv+"'>"+vvv+"</option>";
			}
		  }
		  if(i > 0){
		    sell = sell + "</select>";
			document.getElementById("xlsA").innerHTML = sell ;
	        document.getElementById("xlsfile2").style.display = "" ;
			document.getElementById("xlsRunBt").style.display = "none" ;
		    return ;
		  }
		}
	}
</script>
<script>
    function dealTableB(){ 
	    var table = document.getElementById("tableB");
		var sell  = "<select id='linkfed'><option value=''></option>";
		var self  = "<select id='fromfed'><option value=''></option>";
		    //遍历所有的行
		for (var i = 0, row; row = table.rows[i]; i++) {
		    //遍历当前行的所有列
		  for (var j = 0, col; col = row.cells[j]; j++) {
			// 对每个单元格进行操作
			//console.log("行"+i +"列"+j+"="+col.innerText);
			var vvv = col.innerText.trim();
			if(i==0){
			  console.log(vvv);
			  sell = sell + "<option value='"+vvv+"'>"+vvv+"</option>";
			  self = self + "<option value='"+vvv+"'>"+vvv+"</option>";
			}
		  }
		  if(i > 0){
		    sell = sell + "</select>"; 
			self = self + "</select>"; 
			document.getElementById("xlsB").innerHTML = self ;
	        document.getElementById("xlsLink").innerHTML = sell ;
			document.getElementById("xlsRunBt").style.display = "" ;
		    return ;
		  }
		}
	}
</script>

jun_bai
关注
  • 2
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值