JS解析Excel

<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>

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");}

<input type="file" onchange="importf(this)"/><div id="demo"></div>

/*FileReader共有4种读取方法：1.readAsArrayBuffer(file)：将文件读取为ArrayBuffer。2.readAsBinaryString(file)：将文件读取为二进制字符串3.readAsDataURL(file)：将文件读取为Data URL4.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;}