通常，我们都是将Excel文件上传打服务器后再进行解析数据保存入库，然而当没有必要保存Excel文件时，但又要保证用户体验时，我们就可以用JS脚本来接地本地Excel将数据直接传到后台进行操作。
因为也是第一次用JS去解析Excel，百度出来的解决方案都是需要引用js-xlsx.js。然而项目必须使用IE8，结果可想而知，该死的IE8！
下面就都介绍一下吧。
1.IE8以及一下，用JS解析本地文件。
先说一下大体流程，用户通过File文本框选择文件，点击右边保存，数据显示在前台，数据提交后台。
html
<td align="center"> <input class="easyui-filebox" id="file1" data-options="buttonText:'浏览'" style="width:350px"> <a href="javascript:addExcel()" class="easyui-linkbutton" data-options="iconCls:'icon-ok'">保存</a> </td>
js
function addExcel(){//显示excel var str = $("#file1").filebox("getValue");//获取本地路径 var s=str.split("\."); if(str==''){ alert("请选择excel的路径信息！"); return; } if(s[1]=="xls" || s[1]=="xlsx"){//判断文件格式 try { var oXL = new ActiveXObject("Excel.Application");//创建Excel.Application对象 var oWB = oXL.Workbooks.open(str);//打开并读取文件 var oSheet = oWB.ActiveSheet;//将sheet页作为对象 processExcel(oSheet);//读取数据 } catch(e) { if (e.number == -2146827859) { alert("请检查你的电脑是否装有Excel并且已经启用Activex控件!"); } else { alert("你上传的Excel文件格式不正确！"); alert(e.description); } } finally { oXL.quit(); } } else { alert("只能加载Excel文件！"); } } function processExcel(oSheet){ if( //规定模板格式 new String(oSheet.cells(1,1))!='项目代码'|| new String(oSheet.cells(1,2))!='项目'|| new String(oSheet.cells(1,3))!='本旬收入'|| new String(oSheet.cells(1,4))!='本旬进度'|| new String(oSheet.cells(1,5))!='本月预计'|| new String(oSheet.cells(2,2))!=' 各项收入合计'|| new String(oSheet.cells(3,2))!='一、税收收入合计'|| new String(oSheet.cells(4,2))!='其中：中央级'|| new String(oSheet.cells(5,2))!=' 省级'|| new String(oSheet.cells(6,2))!=' 市县级'|| new String(oSheet.cells(7,2))!=' 1、国内增值税'|| new String(oSheet.cells(8,2))!=' 2、国内消费税'|| new String(oSheet.cells(9,2))!=' 3、营业税'|| new String(oSheet.cells(10,2))!=' 4、企业所得税'|| new String(oSheet.cells(11,2))!=' 5、个人所得税'|| new String(oSheet.cells(12,2))!=' 6、资源税'|| new String(oSheet.cells(13,2))!=' 7、固定资产投资方向调节税'|| new String(oSheet.cells(14,2))!=' 8、城市维护建设税'|| new String(oSheet.cells(15,2))!=' 9、房产税' || new String(oSheet.cells(16,2))!=' 10、印花税' || new String(oSheet.cells(17,2))!=' 11、城市土地使用税' || new String(oSheet.cells(18,2))!=' 12、土地增值税' || new String(oSheet.cells(19,2))!=' 13、车船税' || new String(oSheet.cells(20,2))!=' 14、车辆购置税' || new String(oSheet.cells(21,2))!=' 15、烟叶税' || new String(oSheet.cells(22,2))!=' 16、耕地占用税' || new String(oSheet.cells(23,2))!=' 17、契税' || new String(oSheet.cells(24,2))!='二、其他收入合计' || new String(oSheet.cells(25,2))!=' 1、教育费附加收入' || new String(oSheet.cells(26,2))!=' 2、文化事业建设费' || new String(oSheet.cells(27,2))!=' 3、社会保险基金收入' || new String(oSheet.cells(28,2))!=' 4、地方教育附加' || new String(oSheet.cells(29,2))!=' 5、残疾人就业保障金' || new String(oSheet.cells(30,2))!=' 6、其他收入'|| new String(oSheet.cells(2,1))!='1'|| new String(oSheet.cells(3,1))!='100'|| new String(oSheet.cells(4,1))!='1000'|| new String(oSheet.cells(5,1))!='2000'|| new String(oSheet.cells(6,1))!='3000'|| new String(oSheet.cells(7,1))!='10101'|| new String(oSheet.cells(8,1))!='10102'|| new String(oSheet.cells(9,1))!='10103'|| new String(oSheet.cells(10,1))!='10104'|| new String(oSheet.cells(11,1))!='10106'|| new String(oSheet.cells(12,1))!='10107'|| new String(oSheet.cells(13,1))!='10108'|| new String(oSheet.cells(14,1))!='10109'|| new String(oSheet.cells(15,1))!='10110' || new String(oSheet.cells(16,1))!='10111' || new String(oSheet.cells(17,1))!='10112' || new String(oSheet.cells(18,1))!='10113' || new String(oSheet.cells(19,1))!='10114' || new String(oSheet.cells(20,1))!='10116' || new String(oSheet.cells(21,1))!='10120' || new String(oSheet.cells(22,1))!='10118' || new String(oSheet.cells(23,1))!='10119' || new String(oSheet.cells(24,1))!='200' || new String(oSheet.cells(25,1))!='30203' || new String(oSheet.cells(26,1))!='30217' || new String(oSheet.cells(27,1))!='10200' || new String(oSheet.cells(28,1))!='30216' || new String(oSheet.cells(29,1))!='30218' || new String(oSheet.cells(30,1))!='30221' ){ alert("模板不正确，请使用正确模板导入数据！"); return; }else{ var i=0; var i2=1; var tb=""; while(i<30){//模板为三十行 i++; tb+="<tr >"+ "<td>"+(new String(oSheet.cells(i,2))=="undefined"?"0":new String(oSheet.cells(i,2)))+"</td>"+ "<td>"+(new String(oSheet.cells(i,3))=="undefined"?"0":new String(oSheet.cells(i,3)))+"</td>"+ "<td>"+(new String(oSheet.cells(i,4))=="undefined"?"0":new String(oSheet.cells(i,4)))+"</td>"+ "<td>"+(new String(oSheet.cells(i,5))=="undefined"?"0":new String(oSheet.cells(i,5)))+"</td>"+ "<td>"+(new String(oSheet.cells(i,6))=="undefined"?"0":new String(oSheet.cells(i,6)))+"</td></tr>"; } $("#tb_excel").empty(); $("#tb_excel").append(tb); var a=""; var trs=$("#tb_excel").find("tr"); while(i2<30){ i2++; var c3=(new String(oSheet.cells(i2,3))=="undefined"?"0":new String(oSheet.cells(i2,3))); var c4=(new String(oSheet.cells(i2,4))=="undefined"?"0":new String(oSheet.cells(i2,4))); var c5=(new String(oSheet.cells(i2,5))=="undefined"?"0":new String(oSheet.cells(i2,5))); var c6=(new String(oSheet.cells(i2,6))=="undefined"?"0":new String(oSheet.cells(i2,6))); if(isNaN(c3.replace(/\\/g,"/"))){ $(trs[i2-1]).find("td").eq(1).css("color","red"); a="阻止"; } if(isNaN(c4.replace(/\\/g,"/"))){ $(trs[i2-1]).find("td").eq(2).css("color","red"); a="阻止"; } if(isNaN(c5.replace(/\\/g,"/"))){ $(trs[i2-1]).find("td").eq(3).css("color","red"); a="阻止"; } if(isNaN(c6.replace(/\\/g,"/"))){ $(trs[i2-1]).find("td").eq(4).css("color","red"); a="阻止"; } } if(a=="阻止"){ alert("标红出必须为数字！"); return; } insertExcel(oSheet); } } function insertExcel(oSheet){//保存excel数据 var xun=$("#xun").val(); var arr=[]; var i=1; while(i<30){ i++; var o={ "xx_id":xun, "unt_lev2_id":"${QX_SWJG_DM}", "zsxm_lev3_id":new String(oSheet.cells(i,1))=="undefined"?"":new String(oSheet.cells(i,1)), "zsxm_name":new String(oSheet.cells(i,2))=="undefined"?"":new String(oSheet.cells(i,2)), "xx_rev":new String(oSheet.cells(i,3))=="undefined"?"0":new String(oSheet.cells(i,3)), "xx_jd":new String(oSheet.cells(i,4))=="undefined"?"0":new String(oSheet.cells(i,4)), "fcst_mm_rev":new String(oSheet.cells(i,5))=="undefined"?"0":new String(oSheet.cells(i,5)), "fcst_yy_rev":new String(oSheet.cells(i,6))=="undefined"?"0":new String(oSheet.cells(i,6)) }; arr.push(o); } var j=JSON.stringify(arr); $.post("bb_zzsrgl_zlsb!insertExcel.action",{"jsonArr":j},function(result){ if(result.cont>0){ alert("保存成功！"); }else{ alert("保存失败！"); } },"json"); }
2.非IE，我们就可以用xlsx.js来解析，超简单
<script type="text/javascript" src="JS/xlsx.js"></script>
html
<input type="file" onchange="importf(this)"/> <div id="demo"></div>
js
/* 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; //是否将文件读取为二进制字符串 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) { alert("手动"); wb = XLSX.read(btoa(fixdata(data)), {//手动转化 type: 'base64' }); alert(wb+"**00000000000000"); } else { wb = XLSX.read(data, { type: 'binary' }); alert(wb+"**1111111111111111"); } //wb.SheetNames[0]是获取Sheets中第一个Sheet的名字 //wb.Sheets[Sheet名]获取第一个Sheet的数据 var j=JSON.stringify( XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]) ); document.getElementById("demo").innerHTML= JSON.stringify( XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]) ); var ej=eval("("+j+")"); alert(ej.length); }; 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; }
941
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
