Export to Excel in asp

Well my problem is that yes i do want to export the data to excel but only the selected data.
To be clear i have provided check boxes against each record the user checks the record or records  he wants to export to excel and hits the export button.
Now i m not geting the selected values on the next page and an empty excel file is being created..
Any one knows the reason ..
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Best post some code.  Are you getting the data from a database?
um...is ur form like this following?
<form method=post action=expExcel.asp>
<input type=file name=f1><input type=checkbox name=c1>
<input type=file name=f2><input type=checkbox name=c2>
<input type=file name=f3><input type=checkbox name=c3>

if request.form("c1") = "on" then
    'export file1 to excel...

I have done a similar thing...

Is your checkbox code something similar to this..
<input type="checkbox" id="<%=recordID%>" value="<%=recordID%>" name="select<%=jsel%>">

jsel=jsel+1 ' To provide different names for each checkbox

If your code is somewhat similar to this , then I can give you a javascript function to obtains the IDS of all selected checkbox and submit that to the export page.
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.


For exporting excel ...

Page 1

      <td width="25%"><input type="checkbox" name="ckbox" value="Type of order"><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Type of order</font></td>
            <td><input type="checkbox" name="ckbox" value="MSSPL Agent Invoice No."><font face="Verdana, Arial, Helvetica, sans-serif" size="2">MSSPL Agent Invoice No.</font></td></font></td>
            <td><input type="checkbox" name="ckbox" value="Sales Tax 6"><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Sales Tax 6</font></td></font></font></td>
            <td><input type="checkbox" name="ckbox" value="Sales Tax 3"><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Sales Tax 3</font></td></font></font></td></td></tr>
      <td width="25%"><input type="checkbox" name="ckbox" value="Sales Tax Exempt"><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Sales Tax Exempt</font></td></font></font></td></td>
            <td><input type="checkbox" name="ckbox" value="Shipment Date"><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Shipment Date</font></td>
            <td><input type="checkbox" name="ckbox" value="POD No"><font face="Verdana, Arial, Helvetica, sans-serif" size="2">POD No./AWB No.</font></td>

Page 2

(1) Compose the SQL based on the user selected check box.

(2)  Apply the code for exporting to excel

Let me know IF you need  any code for the same.

Is that all the code for Page 1? And whats the code for the second page?

I have created 2 asp files 1 for listing all the records which the user can select (selExcel.asp) and another for exporting (expExcel.asp). I hope you find this helpfull.


      Dim oConnection, oRecordSet

      Set oConnection = Server.CreateObject("ADODB.Connection")
      oConnection.ConnectionString = "Provider=SQLOLEDB;uid=sa;pwd=;Data Source=V01;Initial Catalog=Sai;"

      Dim sSQL
      sSQL = "Select * from Sai"

      Set oRecordSet = Server.CreateObject("ADODB.Recordset")
      oRecordSet.Open sSQL, oConnection
            <title>Export Selected Data to Excel</title>

            <script language="javascript">


            <form name="frmSelExport" method="POST" action="expExcel.asp">
                  <table border="1" bordercolor="#effcec" cellpadding="3" cellspacing="0" width="100%">
                           <th>Employee Id </th>
                           <th>Employee Name</th>

                        While Not oRecordSet.EOF
                              Response.Write "<tr>"
                              Response.Write "<td><input type='checkbox' name='selRecords' value='" & oRecordSet.Fields(0) & "' />"
                              Response.Write "<td>" & oRecordSet.Fields(0) & "</td>"
                              Response.Write "<td>" & oRecordSet.Fields(1) & "</td>"
                        Set oRecordSet = Nothing
                  <input type="submit" name="btnExport" value="Export Selected to Excel" />
  Set oConnection = Nothing


  Response.ContentType = "application/vnd.ms-excel"
  if (Request.Form("selRecords") = "") Then
      Dim sSQL
      sSQL = "Select * from Sai Where emp_id IN ( " & Request.Form("selRecords") & ")" ', Sairam Where Sai.emp_id = Sairam.emp_id And Sai.

      Dim oConnection , oRs
      Set oConnection = Server.CreateObject("ADODB.Connection")
      oConnection.ConnectionString = "Provider=SQLOLEDB;uid=sa;pwd=;Data Source=V01;Initial Catalog=Sai;"

      Set oRs = Server.CreateObject("ADODB.Recordset")
      oRs.Open sSQL, oConnection

      Dim sOuput
      sOutput = "<html><body>"
      sOutput = "<table border='1' bordercolor='#000000' cellpadding='3' cellspacing='0' width='100%'><tr><th>S. No</th><th>Employee Id</th><th>Employee Name</th></tr>"
      Dim iCnt
      iCnt = 1
      While Not oRs.EOF
            sOutput = sOutput & "<tr><td>" & iCnt & "</td><td>" & oRs.Fields(0) & "</td><td>" & oRs.Fields(1) & "</td></tr>"
            iCnt = iCnt + 1
      sOutput = sOutput & "</table></body></html>"
      Response.Write sOutput
  End if

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
this is code for export to excel sheet ...

'                                          Export to Excel sheet view

'creditdebitSQL="SELECT p.paymentamount,p.paymentmode,p.paymentstatus from paymentdetails p,buyer b where p.buyerid=b.buyerid and b.country <> 'India' and  p.paymendate>='" & fromdate & "' and p.paymendate<='" & todate & "'"
'Response.Write  creditdebitSQL

If trim(Request.Form("ReportExport")) ="yes" then

set rs = server.CreateObject("adodb.recordset")
'Response.write sql
rs.CursorLocation = 3
rs.LockType =2
rs.Open sql, conn

' Move to the first record
while not rs.EOF

'Response.write reccount

set fso = createobject("scripting.filesystemobject")

' Target the text file to be read.
' The text file is continually updated with the current date from the server
set act = fso.opentextfile(server.mappath("excelcounter.txt"))

' Read the value contained in the current day hit counter
' If there is no file for the current day the on error resume next command above
' will force the program to the next line
counter = clng(act.readline)

' Add one to the counter. If there was no value the counter will be set to a value of one
counter = counter + 1

' Close the text object.

' Name for the ouput document
file_being_created= "salesorder" & counter & ".xls"

' Create a new text file on the server with the current date as part of the name
Set act = fso.createtextfile(server.mappath("excelcounter.txt"), true)

' Write the counter value to the text object

' Close the text object

'Response.Write file_being_created

'Delete existing file
set fsodel = createobject("scripting.filesystemobject")

'if trim(fsodel.FileExists(server.mappath(Excel & "/" & file_being_created))) then
if trim(fsodel.FileExists(server.mappath(file_being_created))) then
      'fsodel.DeleteFile(Server.MapPath(Excel & "/" & _
    '        file_being_created))
      'Response.Write  "yes"
      'Response.Write  "no"
end if

' create a file system object
set fso = createobject("scripting.filesystemobject")

' create the text file - true will overwrite any previous files
' Writes the db output to a .xls file in the same directory

'Set act = fso.CreateTextFile(server.mappath(Excel & "/" & _
'                        file_being_created), true)

Set act = fso.CreateTextFile(server.mappath(file_being_created), true)

' All non repetitive html on top goes here
act.WriteLine("<table border=""1"">")
      '------------ Heading -------------------
      act.WriteLine("<td align=""right"" color=yellow>" & Trvalues & "&nbsp;</td>" )
      '-----------  End Heading ---------------      
                        If trim(RECCOUNT) <> "" then
                        end if
                        do while not rs.EOF
                              for i = 0 to Request.Form("ckbox").Count  -1             
                                    act.WriteLine("<td align=""middle"">" & rs(i) & "&nbsp;</td>" )
      Act.WriteLine("</table><table border=1><center>")                  
      '------------- Sales Amount ------------                        
            act.WriteLine("<tr><td align=""right"" color=yellow>Sales Amount</td><td>" & CurrencyType  & "</td><td>" & formatnumber(amt,2)  & "&nbsp;</td></tr>" )
''            act.WriteLine("<tr><td align=""right"" color=yellow>Received Amount</td><td>" & CurrencyType  & "</td><td>" & formatnumber(PaymentReceived)  & "&nbsp;</td></tr>" )
''            act.WriteLine("<tr><td align=""right"" color=yellow>Outstanding Amount</td><td>" & CurrencyType  & "</td><td>" & formatnumber(PaymentOutstanding)  & "&nbsp;</td></tr>" )
' move to the next record

' return to the top of the for - next loop
' change this to "loop" to output all the records
' and the corresponding for statement above should be changed also

' All non repetitive html on top goes here

' close the object (excel)

'******* Display Date *********
If day(date)<10 then
end if

If month(date)<10 then
end if

TodayDate= Days & "/" & months & "/" & year(date)
ReportTime = time()
'Response.Write session("CUserLevel")

' Writes a link to the newly created excel in the browser
if session("CUserLevel") ="1" or session("CUserLevel") ="2" or session("CUserLevel") ="3" or session("CUserLevel") ="4" or session("CUserLevel") ="5" then
      if trim(reccount) <> "" then
            'Response.Write  "Total No. of Records " & RECCOUNT & " "  %>
                  <table border=0 cellpadding=0 cellspacing=0 width=780>
                        <tr bgcolor=#f0f5fa color=black><td align=middle><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Excel Sheet View</font></b></td></tr>
                        <tr bgcolor=#f0f5fa>
                              <td align=middle><a href="<%=file_being_created%>"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><b>Sales Report</b></a>(.xls) has been created on <b><%=TodayDate%>&nbsp;&nbsp;<%=ReportTime%></font></b><br>
      <%end if
end if

end if
waheedzzAuthor Commented:

all my check boxes r dynamic means there is a check box against
every searched record.
now i only want to send those records to excel which the user will
check on the serach result page.
comments from  Sairam_S are closest so far..butmy prob is
not yet solved.as im passign the check box value
in sql and getting  "Data type mismatch in criteria expression."error
r u sure
 "Select * from Users Where Userid IN ( " & Request.Form("chkExport") & ")"
is right????????
secondly mr. vijay7248 kindly send ur code i thnk that can be of use
thnx all
Hi  waheedzz,

Please note that in my code the emp_id was a numeric field. If the userId is a numeric field (int) then there would be no problem, else you must add a single quote for the values, for which, you need to do something as follows:

Dim arrUsers, iLoop, sUsers

arrUsers = Split(Request.Form("chkExport"), ",")
For iLoop = 0 to ubound(arrUsers)
     if (arrUsers(iLoop) <>"") Then
         arrUsers(iLoop) = "'" &  arrUsers(iLoop) & "'"
     End if

sUsers = Join(arrUsers, ",")

sSQL = "Select * from Users Where Userid IN ('" & sUsers & "')"

This must do it....

IF this doesnt work, can you please get me the SQL Query with the error message.

Thank you.

Note : Numeric fields even if bound by single quotes would not give an error. But an varchar or any other data type field would certainly give an error if they are not enclosed within single quotes.
Ok here it is.

First while moving with the recordset rs
You'll be having something like this..
while not rs.eof
  <tr><td><%=rs("F_name")%></td><td><%=rs("lastname")%></td>..... 'To display each record

'Here add this code for check box
  <td><input type="checkbox" id="<%=rs("rowID")%>" value="<%=rs("rowID")%>" name="select<%=jsel%>"></td>
.....'I'm assuming rowID as ur PRIMARY KEY or the RECORD ID of your TABLE
jsel=jesl+1 'Store this count in a hidden field to store the total no. of chkboxes in ur results page so that u can  use this for looping in Javascript.
response.write("<input type='hidden' name='totboxinpage' value='"&jsel&"'/>")

'Now call this Javascript function to find the total no. of selected records for export like
<input type=button value='export' onclick='javascript:getselids();'>

function getselids() {      
      for(i=0;i<document.form1.totboxinpage.value;i++) {      
            if(eval("document.form1.select"+i).checked ==true) {      

//after this function call your onsubmit function say...
//or submit your form to the export page form from here itself
document.form1.action = "export.asp";
//declare selids as a hidden field below your form tag : <input type=hidden name='selids'  value=''>

Now the hidden field will have the your recordIDs as comma seperated values.
Then submit the form ..
You can access the ids of records as request("selids")
Then remove the preceeding comma as
exportids=right(request("selids"),len(request("selids"))-2) 'Pls check this .Print exportids and check whether the format is ok

Now in ur query should be ...
sqlexport="Select * from urtable where rec_id in ("&exportids&");"
'If your rceord id is not a number replace "& with '"& and &" with &"' like...
sqlexport="Select * from urtable where rec_id in ('"&exportids&"');"
'Similarly you must be adding the single quote in the foll line in the function getselids()


waheedzzAuthor Commented:

Hy all
well mr  Sairam_S first soln was closest to mine but thnk u all
for gr8 reply
herez how i did this finaly.hope in future ppl can benefit from it

on the search result page this is my checkbox...

<TD class=etext align=middle width="25%"><input type="checkbox" name="chkExport" value="<%=rsGlobalWeb.Fields("UserId")%>"></Input> </td>

and on the page where excel is imported...

              strRS = Request.form("chkExport")      

               strRSTemp = Split(strRS, ",")

For nCount = 0 to UBound(strRSTemp)
            sSQL = "Select * from Users Where UserId = " & strRSTemp(nCount)
            oRs.Open sSQL, objConnection            
                  Response.Write("<td>" & (nCount + 1) & "</td>")
                  Response.Write("<td>" & oRs.Fields("UserName")       & "</td>")
                  Response.Write("<td>" & oRs.Fields("Address")       & "</td>")
                  Response.Write("<td>" & oRs.Fields("Email")       & "</td>")
                  Response.Write("<td>" & oRs.Fields("Country")       & "</td>")

i hope u gusy get the idea..ok
waheedzzAuthor Commented:

Hy all
well mr  Sairam_S first soln was closest to mine but thnk u all
for gr8 reply
herez how i did this finaly.hope in future ppl can benefit from it

on the search result page this is my checkbox...

<TD class=etext align=middle width="25%"><input type="checkbox" name="chkExport" value="<%=rsGlobalWeb.Fields("UserId")%>"></Input> </td>

and on the page where excel is imported...

              strRS = Request.form("chkExport")      

               strRSTemp = Split(strRS, ",")

For nCount = 0 to UBound(strRSTemp)
            sSQL = "Select * from Users Where UserId = " & strRSTemp(nCount)
            oRs.Open sSQL, objConnection            
                  Response.Write("<td>" & (nCount + 1) & "</td>")
                  Response.Write("<td>" & oRs.Fields("UserName")       & "</td>")
                  Response.Write("<td>" & oRs.Fields("Address")       & "</td>")
                  Response.Write("<td>" & oRs.Fields("Email")       & "</td>")
                  Response.Write("<td>" & oRs.Fields("Country")       & "</td>")

i hope u guys get the idea..ok
Does anyone have the final code for this?  I would greatly appreciate it.  I'm actually doing the exact same thing now.

Many thanks,

Hi Ives - did you get the final code for this?  I am doing exactly the same thing now.


waheedzzAuthor Commented:
herz how i managed it.hope it helps

''page where u will create the check box

If NOT rsUser.EOF Then
<Input type="checkbox" Name="list" value="<%=rs.Fields("ID")%>">
'assign the value id from dbfield"ID" to the check box
end if

now on the page where form is submitted do like this

Dim strUserID
      response.Write(request.Form("list") & "<BR>")
      strUserID = Split(request.Form("list"), ",")  'split with commas , all the id's
      Dim nCount
      Dim strSQL        

      For nCount = 0 to UBound(strUserID)
            strSQL = "UPDATE tablename set statusid = 2 " 'write ur query here
            strSQL = strSQL & " Where ID = " & strUserID(nCount)
            conn.Execute strSQL

Office Web Components,  

I found the easiest way was to code an ASP page to create an HTM page then redirect to that

In the code create a htm file for the table  
Then in the new HTM page
this object below is for windows xp,  (office 2000 is <object classid="clsid:0002E510-0000-0000-C000-000000000046")

<object classid="clsid:0002E551-0000-0000-C000-000000000046" id="Spreadsheet1">
  <param name="DataType" value="HTMLDATA">
  <param name="HTMLData" value="tablefile.htm"></object>

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.