asp怎样用ADO方式修改excel数据？急(页 1) - ASP技术论坛

seesee90 发表于 2008-6-25 15:57

asp怎样用ADO方式修改excel数据？急

entermaster 发表于 2008-6-25 16:54

读取所有的Excel 表

<form name="form1" method="post" action="xls_two.asp">
  <table width="80%" border="0" align="center" cellpadding="5" cellspacing="1" bgcolor="#666666">
<%
'建立Connection对象
Dim db,rs,strSql,i,rst,table_count
Set db = Server.CreateObject("ADODB.Connection")
db.Open "Driver={Microsoft Excel Driver (*.xls)};Dbq=" & Server.MapPath("shuju.xls")
set rst=db.OpenSchema(20)
%>
<tr align="center" bgcolor="#E6E6E6">
   <td height="30" bgcolor="#E6E6E6">请选择 Excel 工作簿  </td>
</tr>
<tr align="center" bgcolor="#E6E6E6">
   <td bgcolor="#FFFFFF"><select name="table_name" id="table_name">
      <option value="">请选择</option>
<%
    while not rst.eof
Response.write "<option value="&rst("Table_Name")&">"&rst("Table_Name")&"</option>"
rst.movenext
wend
%>
   </select>    </td>
</tr>

<tr align="center" bgcolor="#E6E6E6">
   <td><input type="submit" name="button" id="button" value="下一步"></td>
</tr>
  </table>
</form>

entermaster 发表于 2008-6-25 16:55

读取所有数据
注意其中的表名变量 table_name 因为我是从上一个文件接收的 Form 变量,一般情况下第一个表都是 Sheet$,也就是上面的回复那里来的.

<form name="form1" method="post" action="xls_three.asp">
  <table width="80%" border="0" align="center" cellpadding="5" cellspacing="1" bgcolor="#666666">
<%
'建立Connection对象
Dim db,rs,strSql,i,rst,table_count,table_name,j,t_all,t_all_two
Set db = Server.CreateObject("ADODB.Connection")
db.Open "Driver={Microsoft Excel Driver (*.xls)};Dbq=" & Server.MapPath("shuju.xls")
'打开记录集，表名一定要以"[表名$]"的格式
table_name = request("table_name")
if table_name = "" then
response.write "<script>alert('对不起，表名不能为空')</script>"
response.write "<script>history.back()</script>"
end if
strSql="Select * From ["&table_name&"]"
Set rs=db.Execute(strSql)
if rs.eof and rs.bof then
response.write "<script>alert('对不起 " &table_name&" 表中没有任何数据')</script>"
response.write "<script>history.back()</script>"
end if
'循环读取所有行
Response.Write "<tr bgcolor = white align='center'>"
Response.Write "<td>操作</td>"
For i = 0 to rs.fields.count - 1
Response.Write "<td>" & rs(i).name & "</td>"
Next
Response.Write "</tr>"
j = 0
Do While Not rs.EOF
Response.Write "<tr bgcolor = white align='center'>"
Response.Write "<td><input type = 'checkbox' name = 'checkbox'/></td>"
For i = 0 to rs.fields.count - 1
Response.Write "<td>" & rs(i) & "</td>"
Next
Response.Write "</tr>"
rs.MoveNext
j = j + 1
Loop

%>
<tr align="center" bgcolor="#E6E6E6">
   <td colspan="<%=i + 1%>" align="left" valign="middle" bgcolor="#FFFFFF">您可以选择插入：</td>
</tr>
<tr align="center" bgcolor="#E6E6E6">
   <td colspan="<%=i + 1%>" align="center" valign="middle" bgcolor="#FFFFFF"><%
For i = 0 to rs.fields.count - 1
if t_all = "" then
t_all = "<$=" & rs(i).name & "$>"
t_all_two = rs(i).name
else
t_all = t_all & "," & "<$=" & rs(i).name & "$>"
t_all_two = t_all_two & "," & rs(i).name
end if
'Response.Write "<span style='cursor:pointer;color=red' >" &rs(i).name & "</span>  "

%>
<span id="wbf" onClick="insertAtCaret(document.form1.tarea,this.innerText);" style="cursor:pointer; color:red
;"><%=rs(i).name%></span>
<%
Next
response.write t_all
%>
<input type="hidden" name="t_all" id="t_all" value="<%=t_all%>">
<input type="hidden" name="t_all_two" id="t_all_two" value="<%=t_all_two%>">
<input type="hidden" name="table_name" id="table_name" value="<%=table_name%>">
</td>
</tr>
<tr align="center" bgcolor="#E6E6E6">
   <td colspan="<%=i + 1%>" valign="middle" bgcolor="#FFFFFF">定制发送内容：
   <textarea name="tarea" rows="10" cols="80"onselect="setCaret(this);" onClick="setCaret(this);" onKeyUp="setCaret(this);">你好</textarea></td>
</tr>
<tr align="center" bgcolor="#E6E6E6">
   <td colspan="<%=i + 1%>"><input type="submit" name="button" id="button" value="提交">   <input type="reset" name="button2" id="button2" value="重置"></td>
</tr>
  </table>
</form>
<%
Response.write j
'关闭对象
rs.Close
Set rs=nothing

%>

[[it] 本帖最后由 entermaster 于 2008-6-25 16:56 编辑 [/it]]

seesee90 发表于 2008-6-25 20:51

多谢enter大大的回帖，在学习中。。。。

seesee90 发表于 2008-6-25 21:00

后来，用了这种笨方法来实现，因自己是新手，很菜

<%
dim xlsfile
xlsfile="18611jbjc.xls"
Set connx = CreateObject("ADODB.Connection")
on error resume next
connx.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&Server.MapPath(xlsfile)&";Extended Properties=""Excel 8.0;HDR=yes;IMEX=3"""

dim xlsfile1
xlsfile1="18611.xls"
Set connx1= CreateObject("ADODB.Connection")

connx1.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&Server.MapPath(xlsfile1)&";Extended Properties=""Excel 8.0;HDR=yes;IMEX=1"""

if err then
err.clear
set connx=nothing
set connx1=nothing
response.write("<font color=#888888 size=3px>连接到 XLS 文件出现错误，请检查数据处理文件</font> <font color=blue size=3px><b></b></font> <font color=#888888 size=3px>的 EXCEL 连接部分是否设置正确</font>")
response.end()
end if

response.write("EXECL文件连接成功"&"<br>")

set rsx=server.createobject("adodb.recordset")
rsx.open "select * from [sheet1$]",connx,2,3

   while not rsx.eof

Set rs=server.CreateObject("ADODB.recordset")
rs.open  "select * from [sheet1$]",connx1,1,1

         rs.movefirst
'rs.open  "select * from [sheet1$] where rsx("考号")=rs("no")",connx1,1,1

while not rs.eof

if rsx("考号")=rs("no") then
rsx("政治")=rs("tsco")

rsx.update

end if

rs.movenext

wend

   rsx.movenext


   wend

'response.write("</table>")

rsx.close
set rsx=nothing
connx.close
set connx=Nothing
response.write(" <font color=blue size=3px><b></b></font>EXECL文件数据交换连接成功"&"<br>")

%>

yms123 发表于 2008-6-26 10:46

http://bbs.bccn.net/viewthread.php?tid=162737&extra=page%3D2%26amp%3Bfilter%3Ddigest
以前写过ASP生成Excel的一个程序，供楼主参考

seesee90 发表于 2008-6-27 10:33

谢谢各位的答复。论坛确是学习的好地方

页: [1]