如何读取excel中指定单元格内的数据
如题
我的excel表格是不规则的，顶部有标题栏目
问题点数：20、回复次数：2Top
1 楼fengchu2（凤雏）回复于 2003-08-03 14:30:28 得分 20
给您一点代码：
<%
dim deptid,deptdate
deptid=CInt(trim(request("deptid")))
deptdate=CInt(trim(request("deptdate")))
'==============================建立access数据连接conn1
dim curDir,conn1 ,sql,rs1
curDir = Server.MapPath("database/qydbone.mdb")
Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &curDir
'===============================判断数据是否已经存在
Set rs1 = Server.CreateObject("ADODB.Recordset")
rs1.open "select * from fzb where deptid like '" &deptid& "' and deptdate like '" &deptdate& "'",conn1
if not rs1.eof then %>
<script language="javascript">
alert("对不起，您导入的数据已经存在！");
window.history.go(-1);
</script><%
response.end
end if
rs1.close
'==============================建立excel数据连接~ conn
dim dbpath,conn
dbpath=trim(request("inputfile"))
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Driver={Microsoft Excel Driver (*.xls)};ReadOnly=0;DBQ=" & dbpath
'===============================excel数据读入记录集rs
dim SQL1,rs
SQL1="select * from [sheet1$]"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open SQL1, conn, 3, 3
'================================创建临时表table1
sql="CREATE TABLE table1 ("
for i=0 to rs.Fields.Count-2
sql=sql & rs(i).Name&" CHAR(100),"
Next
sql=sql & rs(rs.Fields.Count-1).Name&" CHAR(100))"
conn1.Execute sql
'==================================创建access记录集rs1读取excel纪录并写入table1
dim i,j
Set rs1.ActiveConnection = conn1
rs1.Source = "select * from table1"
rs1.CursorType = 3 ' adOpenKeyset
rs1.LockType = 3 'adLockOptimistic
rs1.Open
Do While Not rs.Eof
rs1.AddNew
for i=0 to rs.Fields.Count-1
rs1(i)=Trim(rs(i))
Next
rs1.Update
rs.MoveNext
j=j+1
Loop
rs1.close
dim hbzj,f_yszk,e_yszk,qtysk,yufzk,f_ch,e_ch,ccp,f_ldzchj,e_ldzchj
dim gdzcyj,ljzj,f_gdzcjz,e_gdzcjz,f_gdzcje,e_gdzcje,zjgc,gdzchj,wxzc,dyzc
dim f_zczj,e_zczj,dqjk,yfzk,yszk,yjsj,qtyfk,ldfzhj,cqjk,fzhj,f_syzqyhj,e_syzqyhj
rs1.open "select * from table1 where f2='1'",conn1
hbzj =trim(rs1("f4"))
if isnull(hbzj) then
hbzj=0
end if
rs1.close
rs1.open "select * from table1 where f2='6'",conn1
f_yszk =trim(rs1("f3"))
e_yszk =trim(rs1("f4"))
if isnull(f_yszk) then
f_yszk=0
end if
if isnull(e_yszk) then
e_yszk=0
end if
rs1.close
rs1.open "select * from table1 where f2='7'",conn1
qtysk =trim(rs1("f4"))
if isnull(qtysk) then
qtysk=0
end if
rs1.close
rs1.open "select * from table1 where f2='8'",conn1
yufzk =trim(rs1("f4"))
if isnull(yufzk) then
yufzk=0
end if
rs1.close
rs1.open "select * from table1 where f2='12'",conn1
f_ch =trim(rs1("f3"))
e_ch =trim(rs1("f4"))
if isnull(f_ch) then
f_ch=0
end if
if isnull(e_ch) then
e_ch=0
end if
rs1.close
rs1.open "select * from table1 where f2='14'",conn1
ccp =trim(rs1("f4"))
if isnull(ccp) then
ccp=0
end if
rs1.close
rs1.open "select * from table1 where f2='19'",conn1
f_ldzchj =trim(rs1("f3"))
e_ldzchj =trim(rs1("f4"))
if isnull(f_ldzchj) then
f_ldzchj=0
end if
if isnull(e_ldzchj) then
e_ldzchj=0
end if
rs1.close
rs1.open "select * from table1 where f2='25'",conn1
gdzcyj =trim(rs1("f4"))
if isnull(gdzcyj) then
gdzcyj=0
end if
rs1.close
rs1.open "select * from table1 where f2='26'",conn1
ljzj =trim(rs1("f4"))
if isnull(ljzj) then
ljzj=0
end if
rs1.close
rs1.open "select * from table1 where f2='27'",conn1
f_gdzcjz=trim(rs1("f3"))
e_gdzcjz=trim(rs1("f4"))
if isnull(f_gdzcjz) then
f_gdzcjz=0
end if
if isnull(e_gdzcjz) then
e_gdzcjz=0
end if
rs1.close
Top
2 楼fengchu2（凤雏）回复于 2003-08-03 14:30:39 得分 0
rs1.open "select * from table1 where f2='29'",conn1
f_gdzcje=trim(rs1("f3"))
e_gdzcje=trim(rs1("f4"))
if isnull(f_gdzcje) then
f_gdzcje=0
end if
if isnull(e_gdzcje) then
e_gdzcje=0
end if
rs1.close
rs1.open "select * from table1 where f2='32'",conn1
zjgc =trim(rs1("f4"))
if isnull(zjgc) then
zjgc=0
end if
rs1.close
rs1.open "select * from table1 where f2='34'",conn1
gdzchj =trim(rs1("f4"))
if isnull(gdzchj) then
gdzchj=0
end if
rs1.close
rs1.open "select * from table1 where f2='35'",conn1
wxzc =trim(rs1("f4"))
if isnull(wxzc) then
wxzc=0
end if
rs1.close
rs1.open "select * from table1 where f2='37'",conn1
dyzc =trim(rs1("f4"))
if isnull(dyzc) then
dyzc=0
end if
rs1.close
rs1.open "select * from table1 where f2='44'",conn1
f_zczj =trim(rs1("f3"))
e_zczj =trim(rs1("f4"))
if isnull(f_zczj) then
f_zczj=0
end if
if isnull(e_zczj) then
e_zczj=0
end if
rs1.close
rs1.open "select * from table1 where f6='45'",conn1
dqjk =trim(rs1("f8"))
if isnull(dqjk) then
dqjk=0
end if
rs1.close
rs1.open "select * from table1 where f6='47'",conn1
yfzk =trim(rs1("f8"))
if isnull(yfzk) then
yfzk=0
end if
rs1.close
rs1.open "select * from table1 where f6='48'",conn1
yszk =trim(rs1("f8"))
if isnull(yszk) then
yszk=0
end if
rs1.close
rs1.open "select * from table1 where f6='52'",conn1
yjsj =trim(rs1("f8"))
if isnull(yjsj) then
yjsj=0
end if
rs1.close
rs1.open "select * from table1 where f6='54'",conn1
qtyfk =trim(rs1("f8"))
if isnull(qtyfk) then
qtyfk=0
end if
rs1.close
rs1.open "select * from table1 where f6='59'",conn1
ldfzhj =trim(rs1("f8"))
if isnull(ldfzhj) then
ldfzhj=0
end if
rs1.close
rs1.open "select * from table1 where f6='60'",conn1
cqjk =trim(rs1("f8"))
if isnull(cqjk) then
cqjk=0
end if
rs1.close
rs1.open "select * from table1 where f6='68'",conn1
fzhj =trim(rs1("f8"))
if isnull(fzhj) then
fzhj=0
end if
rs1.close
rs1.open "select * from table1 where f6='86'",conn1
f_syzqyhj=trim(rs1("f7"))
e_syzqyhj=trim(rs1("f8"))
if isnull(f_syzqyhj) then
f_syzqyhj=0
end if
if isnull(e_syzqyhj) then
e_syzqyhj=0
end if
rs1.close
conn1.execute "drop table table1"
'response.write hbzj& "@@" &f_yszk& "@@" &e_yszk& "@@" &qtysk& "@@" &yufzk& "@@" &f_ch& "@@" &e_ch& "@@" &ccp& "@@<br>" &_
' f_ldzchj& "@@" &e_ldzchj& "@@" &gdzcyj& "@@" &ljzj& "@@" &f_gdzcjz& "@@" &e_gdzcjz& "@@" &f_gdzcje& "@@<br>"&_
' e_gdzcje& "@@" &zjgc& "@@" &gdzchj& "@@" &wxzc& "@@" &dyzc& "@@" &f_syzqyhj
conn1.execute "insert into fzb values('" &deptid& "','" &deptdate& "','" &CDbl(hbzj)& "','" &CDbl(f_yszk)& "','"&_
CDbl(e_yszk)& "','" &CDbl(qtysk)& "','" &CDbl(yufzk)& "','" &CDbl(f_ch)& "','" &CDbl(e_ch)& "','"&_
CDbl(ccp)& "','" &CDbl(f_ldzchj)& "','" &CDbl(e_ldzchj)& "','" &CDbl(gdzcyj)& "','" &CDbl(ljzj)& "','"&_
CDbl(f_gdzcjz)& "','" &CDbl(e_gdzcjz)& "','" &CDbl(f_gdzcje)& "','" &CDbl(e_gdzcje)& "','" &CDbl(zjgc)& "','"&_
CDbl(gdzchj)& "','" &CDbl(wxzc)& "','" &CDbl(dyzc)& "','" &CDbl(f_zczj)& "','" &CDbl(e_zczj)& "','"&_
CDbl(dqjk)& "','" &CDbl(yfzk)& "','" &CDbl(yszk)& "','" &CDbl(yjsj)& "','" &CDbl(qtyfk)& "','" &CDbl(ldfzhj)& "','" &CDbl(cqjk)& "','" &CDbl(fzhj)& "','"&_
CDbl(f_syzqyhj)& "','" &CDbl(e_syzqyhj)& "')"
'===================================导入数据完成 转回输入页
%>
<script language="javascript">
alert("恭喜！你输入的数据已经成功导入！");
window.location="importexcel.asp" ;
</script><%
rs.close
conn.close
conn1.close
set rs=nothing
set rs1=nothing
set conn=nothing
'set conn1=nothing %>
Top
