28,390
社区成员
发帖
与我相关
我的任务
分享
rs.Open "SELECT T1.*,IIF(T2.sfzhm <> NULL, 1, 0) AS IsExist FROM [Excel 8.0;Database="&server.mappath("xls/2014109142819586.xls")&";HDR=YES].[Sheet1$] AS T1 LEFT JOIN cn_peixun AS T2 ON T1.SFZ = T2.sfzhm", conn, 1, 1
第二,可以通过 rs.Fields获取列信息
<thead>
<tr>
<%For Each fld in rs.Fields%>
<th><span><%=Server.HTMLEncode(fld.Name)%></span></th>
<%Next %>
</tr>
</thead>
第三,通过判断IsExist是否为1,给你的td添加样式style="color: red; font-weight: bold"
<%
response.ContentType ="application/vnd.ms-excel; charset=GB2312"
response.Addheader "Content-Disposition", "attachment;Filename=" & date() & ".xls"
response.Charset = "GB2312"
dim conn,rs
set conn=server.createobject("adodb.connection")
Const DB_BASE = "DataBase/#Yi_Wang_Xun_C_Base.mdb" '数据库名
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath(""&DB_BASE&"")
set rs=server.createobject("adodb.recordset")
rs.open "select a.*,1 as 结果 from [excel 8.0;database="&server.mappath("xls/2014109142819586.xls")&"].[sheet1$] as a where 身份证 in (select sfzhm from cn_peixun) ",conn,1,1
%>
<table width=90% border=0 align=center ID="Table2" cellpadding=1 cellspacing=1>
<tr>
<td>身份证</td>
</tr>
<% DO WHILE Not rs.eof %>
<tr>
<td style="vnd.ms-excel.numberformat:@"><%=rs(1)%></a></td>
</tr>
<%
rs.movenext
loop
rs.close
conn.close
%>
代码贴上来,请高人解决,可以页面上显示,也可以导出EXCEL
有2个问题
1、如果在页面返回,如何显示 导入进来拿个EXCEL 所有列呢? 如果我不知道的话,RS(0)..RS(1) 不知道要写几个。
2、能否按照导入进来的那个EXCEL格式,在上面做好重复标记,下载下来,比如说我导入10条的EXCEL表,导出还是10条,在重复身份证记录上做好记号,比如加粗,变红。
求教!
<%
For i = 0 to l
%>
<td<%If rs(i+1) = 1 AND Server.HTMLEncode(rs.Fields(i).Name) = "身份证" Then%> style="color: red; font-weight: bold;"<%End If%>><%=rs(i)%></td>
<%
Next
%>
因为你刚开始用了vnd.ms-excel.numberformat:@;,可能是为了防止输出的身份证号被当作数字,所以要格式化成字符格式。改的时候忘记判断列是不是身份证号这一列了。这回再试试。
<%
Response.ContentType ="application/vnd.ms-excel; charset=GB2312"
Response.Addheader "Content-Disposition", "attachment;Filename=" & date() & ".xls"
Response.Charset = "GB2312"
DIM conn, rs
SET conn = Server.CreateObject("adodb.connection")
Const DB_BASE = "DataBase/#Yi_Wang_Xun_C_Base.mdb" '数据库名
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(DB_BASE)
SET rs = Server.CreateObject("adodb.recordset")
rs.Open "SELECT T1.*,IIF(T2.sfzhm <> NULL, 1, 0) AS 结果 FROM [Excel 8.0;Database= "& Server.MapPath("xls/2014109142819586.xls") & ";HDR=YES].[Sheet1$] AS T1 LEFT JOIN cn_peixun AS T2 ON T1.身份证 = T2.sfzhm", conn, 1, 1
%>
<table width=90% border=0 align=center ID="Table2" cellpadding=1 cellspacing=1>
<thead>
<tr>
<%
DIM i, l
l = rs.Fields.Count - 1 '不导出最后一列(是否重复)
For i = 0 to l
%>
<th><%=Server.HTMLEncode(rs.Fields[i].Name)%></th>
<%
Next
%>
</tr>
</thead>
<tbody>
<%
Do While Not rs.Eof
%>
<tr>
<%
For i = 0 to l
%>
<td style="<%If rs(i+1) = 1 Then%> color: red; font-weight: bold; vnd.ms-excel.numberformat:@;"<%End If%>><%=rs(i)%></td>
<%
Next
%>
</tr>
<%
rs.MoveNext
Loop
%>
</tbody>
rs.Close
conn.Close
%>
</table>