<html xmlns="http://www.w3.org/1999/xhtml" >
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<TITLE>将EXCEL导入页面表格</TITLE>
</head>
<script language="javascript" type="text/javascript">function importXLS(fileName){
alert(fileName);
//连库
conn = new ActiveXObject("ADODB.Connection");
conn.Provider = "Microsoft.Jet.OLEDB.4.0";
conn.ConnectionString = "Data Source=" + fileName + ";Extended Properties=Excel 8.0;";
conn.CursorLocation = 1;
conn.Open;
if (conn.state!=1){
alert('数据库连接错误!');
conn.close
conn=null;
}
//定义表格变量
var oTBL=document.getElementById("addTbl");
var numCol=oTBL.rows(0).cells.length-1;
//定义记录集
var rs = new ActiveXObject("ADODB.Recordset");
rs.Open("SELECT * FROM [Sheet1$]",conn,1,1);
var i=1;
alert(rs.RecordCount);
while(!rs.EOF){
for (var j=0;j<rs.Fields.Count;j++){
if (j>numCol){return;}//EXCEL字段多于表格列,跳出
oTBL.rows(i).cells(j).innerHTML="<select name='organCode' onchange='transactorDepartSearch()'><option>"+rs.Fields(j).value+"</option></select><input type='hidden' name='inde' id='inde' value = '1' >";
alert(rs.Fields(j).value);
oTBL.rows(i).cells(++j).innerHTML="<select name='departmentCode' ><option>"+rs.Fields(j).value+"</option></select>";
alert(rs.Fields(j).value);
oTBL.rows(i).cells(++j).innerHTML="<input type='text' name='feeTypeCode' id='feeTypeCode' value = '"+rs.Fields(j).value+"' >";
alert(rs.Fields(j).value);
oTBL.rows(i).cells(++j).innerHTML="<input type='text' name='feeTypeName' id='feeTypeName' value = '' >";
alert('--');
oTBL.rows(i).cells(++j).innerHTML="<input type='text' name='year' id='year' onblur='chkAmount(this)' value = '"+rs.Fields(j-1).value+"' >";
alert(rs.Fields(j-1).value);
oTBL.rows(i).cells(++j).innerHTML="<input type='text' name='month' id='month' onblur='chkAmount(this)' value = '"+rs.Fields(j-1).value+"' >";
alert(rs.Fields(j-1).name);
oTBL.rows(i).cells(++j).innerHTML="<input type='text' name='quantity' id='quantity' value = '"+rs.Fields(j-1).value+"' >";
alert(rs.Fields(j-1).value);
oTBL.rows(i).cells(++j).innerHTML="<input type='text' name='price' id='price' value = '"+rs.Fields(j-1).value+"' >";
alert(rs.Fields(j-1).value);
oTBL.rows(i).cells(++j).innerHTML="<input type='text' name='amount' id='amount' value = '"+rs.Fields(j-1).value+"' >";
alert(rs.Fields(j-1).value);
}
rs.MoveNext;
i+=1
addTableRow();
}
conn.Close; conn =null; rs = null;
var length=oTBL.rows.length-1;
oTBL.deleteRow(length);
}
function delTableRow(obj){
var row = obj.parentNode.parentNode;
var tbl = obj.parentNode.parentNode.parentNode;
var index = row.rowIndex;
tbl.deleteRow(index);
}function addTableRow(){
var mytable = document.getElementById('addTbl');
var length=mytable.rows.length-1;
var row = mytable.insertRow(length+1);
var txt = "";
var iCol = 0;
var cell = row.insertCell(iCol++);
txt= "<TD class=drafttd2>";
txt=txt+"<select name='organCode' onchange='transactorDepartSearch()' ></select>";
txt=txt+"<input type='hidden' name='inde' id='inde' value = '1' ></TD>";
cell.innerHTML = txt;
var cell = row.insertCell(iCol++);
txt = "<TD class=drafttd2><select name='departmentCode' ></select></TD>";
cell.innerHTML = txt;
var cell = row.insertCell(iCol++);
txt = "<TD class=drafttd2><input type='text' name='feeTypeCode' id='feeTypeCode' size='10' value = '' ></TD>";
cell.innerHTML = txt;
var cell = row.insertCell(iCol++);
txt = "<TD class=drafttd2> <select name='feeTypeName' ></select></TD>";
cell.innerHTML = txt;
var cell = row.insertCell(iCol++);
txt = "<TD class=drafttd2><input type='text' name='year' id='year' onblur='chkYear(this)' size='5' value = '' ></TD>";
cell.innerHTML = txt;
var cell = row.insertCell(iCol++);
txt = "<TD class=drafttd2><input type='text' name='month' id='month' onblur='chkMonth(this)' size='5' value = '' ></TD>";
cell.innerHTML = txt;
var cell = row.insertCell(iCol++);
txt = "<TD class=drafttd2><input type='text' name='quantity' id='quantity' size='5' value = '' ></TD>";
cell.innerHTML = txt;
var cell = row.insertCell(iCol++);
txt = "<TD class=drafttd2><input type='text' name='price' id='price' onblur='chkAmount(this)' size='5' value = '' ></TD>";
cell.innerHTML = txt;
var cell = row.insertCell(iCol++);
txt = "<TD class=drafttd2><input type='text' name='amount' id='amount' onblur='count(this)' size='5' value = '' ></TD>";
cell.innerHTML = txt; var cell = row.insertCell(iCol++);
txt = "<TD class=drafttd2><INPUT onclick='delTableRow(this);' class='tpbutton' type=button value=删除></TD>";
cell.innerHTML = txt;
}</script>
</head><body>
<input type="file" id="f" />
<input type="button" id="b" value="import" onclick="if(f.value=='')alert('请选择xls文件');else importXLS(f.value)" />
<table id="addTbl" name="addTbl" width="100%" border="0" cellspacing="1" cellpadding="2" align="center" class="etable">
<TR>
<TD class=drafttd2>财务机构代码</TD>
<TD class=drafttd2>成本中心代码</TD>
<TD class=drafttd2>费用类型代码</TD>
<TD class=drafttd2>费用类型名称</TD>
<TD class=drafttd2>年</TD>
<TD class=drafttd2>月</TD>
<TD class=drafttd2>数量</TD>
<TD class=drafttd2>单价</TD>
<TD class=drafttd2>总额</TD>
<TD class=drafttd2></TD>
</TR>
<TR>
<TD class=drafttd2>
<select name='organCode' onchange="transactorDepartSearch(this)" >
<option value='1'>1</option>
<option value='2'>2</option>
<option value='3'>3</option>
</select>
<input type="hidden" name="inde" id="inde" value = '1' >
</TD>
<TD class=drafttd2><select name='departmentCode' ></select></TD>
<TD class=drafttd2><input type="text" name="feeTypeCode" id="feeTypeCode" size='10' value = '' ></TD>
<TD class=drafttd2> <select name='feeTypeName' ></select></TD>
<TD class=drafttd2><input type="text" name="year" id="year" onblur="chkYear(this)" size='5' value = '' ></TD>
<TD class=drafttd2><input type="text" name="month" id="month" onblur="chkMonth(this)" size='5' value = '' ></TD>
<TD class=drafttd2><input type="text" name="quantity" id="quantity" size='5' onblur="chkAmount(this)" value = '' ></TD>
<TD class=drafttd2><input type="text" name="price" id="price" onblur="chkAmount(this)" size='5' value = '' ></TD>
<TD class=drafttd2><input type="text" name="amount" id="amount" onblur="count(this)" size='5' value = '' ></TD>
<TD class=drafttd2><INPUT onclick='addTableRow();' class="tpbutton" type=button value=增加></TD>
</TD>
</TR>
</table>
</BODY>
</HTML>用此方法导入时如果单元格内容是数字形式则可以正常导入 如果是非数字(英文)则导入失败 请高手指点下
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货