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");
}
来源: http://www.bubuko.com/infodetail-2277990.html