RE: Adding filters to excel report

[chandhseke]

Hi folks,

I am a newbie to this functionality of creating excel report using VB/VB Script.
I have been given a code and asked to add filters to the excel sheet that is created using the below code.

Can somebody help me out in resloving the issue??

<%
Option Explicit
%>
<!--#include file="connectio n.asp"-->//DB connection string
<%
Dim sqlstr
Dim oConn,sql,rs,He aderTitle,dDate Now,sDateString ,sFileName

Const PAGE_TITLE = "Contract Database Reporting"

'download to excel
'setup filename
dDateNow = Now
sDateString = CStr(Month(dDat eNow)) & CStr(Day(dDateN ow)) & CStr(Year(dDate Now)) & CStr(Hour(dDate Now)) & CStr(Minute(dDa teNow))

sqlstr = Trim(Request.Fo rm("SQL"))

sFileName = "ContractDB_dat adump_" & sDateString & ".xls"

sql = sqlstr
'db oConnection and query

if Request.QuerySt ring("Pref") = "Yes" then
sql = "SELECT Datecreated,Ref _Num,Record_Sta tus,Agr_Model,S upplierRating,S upType,Company_ Name,Com_Group, Com_Code,Prefer redGeography,Pr eferredCountrie s,Proc_Email,St art_Date,End_Da te FROM Sample WHERE Record_Status = 'Active' AND SupplierRating LIKE 'Preferred%' Order by Company_Name Asc"
end if

Set rs = Server.CreateOb ject("ADODB.Rec ordset")
Set rs = oConn.Execute(s ql)

'call rs to excel function
Call DisplayRSInExce l(rs,"#CC0033", "#FFFFFF",sFile Name)
'cleanup
rs.Close
Set rs = Nothing
oConn.Close
Set oConn = Nothing

'-------------routines used in this page --------------------------------------------------------
'function display RS in excel format in browser
Sub DisplayRSInExce l(oRs,sHeaderBG Color,sHeaderFo ntColor,sFilena me)
Dim m, i 'number of fields, loop variable
Dim s1, s2 'work strings
Dim sCellValue
Dim sCellBGColor
Dim objExcel
Dim objMailName
'Dim sFieldVal
Dim s_RetVal

'excel header info
Response.Buffer = True
Response.Conten tType = "applicatio n/vnd.ms-excel"
Response.AddHea der "Content-Disposition", "attachment ; filename=" & sFilename

If oRs.EOF Then
Response.Write "No records match your request" & vbcrlf
Else
if Request.QuerySt ring("Pref") = "Yes" then

'create table & header cells (and border if desired)
Response.Write "<table cellpadding=""0 "" cellspacing=""0 "" border=""1"">" & vbcrlf & "<tr>" & vbcrlf

'create header
m = oRs.Fields.Coun t - 1
For i = 0 To 13

s1 = oRs.Fields(i).N ame & ""
s2 = replace(s1, "_", " ")
Response.Write "<td align=""left"" valign=""top"" bgcolor=""" & sHeaderBGColor & """><b><fon t color=""" & sHeaderFontColo r & """>" & s2 & "</font></b></td>"
Next
Response.Write "</tr>" & vbcrlf & vbcrlf

'create detail records
Do While Not oRs.EOF
Response.Write "<tr>"
For i = 0 To 13
sCellValue = oRs.Fields(i).V alue

If IsNull(sCellVal ue) Then sCellValue = ""
sCellBGColor = ""
Next
Response.Write "</tr>" & vbcrlf
oRs.MoveNext
Loop
Response.Write "</table>" & vbcrlf

Else

'create table & header cells (and border if desired)
Response.Write "<table cellpadding=""0 "" cellspacing=""0 "" border=""1"">" & vbcrlf & "<tr>" & vbcrlf

'create header
m = oRs.Fields.Coun t - 1

For i = 0 To 26

s1 = oRs.Fields(i).N ame & ""

If s1 = "ReqState" Then
Else
s2 = replace(s1, "_", " ")
Response.Write "<td align=""left"" valign=""top"" bgcolor=""" & sHeaderBGColor & """><b><fon t color=""" & sHeaderFontColo r & """>" & s2 & "</font></b></td>"
End If
Next
Response.Write "</tr>" & vbcrlf & vbcrlf

'create detail records
Do While Not oRs.EOF
Response.Write "<tr>"
For i = 0 To 26
sCellValue = oRs.Fields(i).V alue

If oRs.Fields(i).N ame = "ReqState" Then
Else

If IsNull(sCellVal ue) Then sCellValue = ""
sCellBGColor = ""

Response.Write "<td " & sCellBGColor & ">" & sCellValue & "</td>"

End If
Next
Response.Write "</tr>" & vbcrlf
oRs.MoveNext
Loop
Response.Write "</table>" & vbcrlf

End If

End If

End Sub
%>

Thanks in advance.

Regards,
Chandhseke

[chandhseke]

P.S I want to add filters to the excel sheet that gets generated using the above code. Please help