Solved

Export to Excel in asp

Posted on 2003-11-12
16
22,992 Views
Last Modified: 2009-10-21
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 ..
0
Comment
Question by:waheedzz
  • 4
  • 2
  • 2
  • +6
16 Comments
 
LVL 58

Expert Comment

by:Gary
ID: 9737706
Best post some code.  Are you getting the data from a database?
0
 
LVL 5

Expert Comment

by:ho_alan
ID: 9737724
um...is ur form like this following?
<form method=post action=expExcel.asp>
<input type=file name=f1><input type=checkbox name=c1>
<br>
<input type=file name=f2><input type=checkbox name=c2>
<br>
<input type=file name=f3><input type=checkbox name=c3>
</form>

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

%>
0
 
LVL 3

Expert Comment

by:vijay7248
ID: 9737803
I have done a similar thing...

Is your checkbox code something similar to this..
jsel=1
<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.
0
 
LVL 3

Expert Comment

by:rajuare
ID: 9737970
Hi

For exporting excel ...


Page 1
====

<Table>
      <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>
            <td>&nbsp;</td></tr>




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.





0
 
LVL 58

Expert Comment

by:Gary
ID: 9737980
Is that all the code for Page 1? And whats the code for the second page?
0
 
LVL 2

Accepted Solution

by:
Sairam_S earned 125 total points
ID: 9738176
Hi,

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.

#selExcel.asp#

<%
      Dim oConnection, oRecordSet

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

      Dim sSQL
      sSQL = "Select * from Sai"

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

            <script language="javascript">

            </script>

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

                  <%
                        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>"
                              oRecordSet.MoveNext
                        Wend
                        oRecordSet.close
                        Set oRecordSet = Nothing
                  %>
                  </table>
                  <input type="submit" name="btnExport" value="Export Selected to Excel" />
            </form>
      </body>
</html>
<%
  oConnection.Close
  Set oConnection = Nothing
%>


---------------------------------------------------------------------------------------------#expExcel.asp#


<%
  Response.ContentType = "application/vnd.ms-excel"
 
  if (Request.Form("selRecords") = "") Then
      Response.End
  Else
      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;"
      oConnection.Open

      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
            oRs.MoveNext
      Wend
      sOutput = sOutput & "</table></body></html>"
      Response.Write sOutput
  End if
%>

0
 
LVL 3

Expert Comment

by:rajuare
ID: 9738207
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
'rs.MoveFirst
while not rs.EOF
      RECCOUNT=rs.RecordCount       
      rs.MoveNext
wend

'Response.write reccount
'Response.End


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.
act.close

' 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
act.writeline(counter)

' Close the text object
act.Close

'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))
      fsodel.DeleteFile(Server.MapPath(file_being_created))  
      'Response.Write  "yes"
else
      '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("<html><body>")
act.WriteLine("<table border=""1"">")
'rs.MoveFirst                         
      '------------ Heading -------------------
      Act.WriteLine("<tr>")
      act.WriteLine("<td align=""right"" color=yellow>" & Trvalues & "&nbsp;</td>" )
      act.WriteLine("</tr>")
      '-----------  End Heading ---------------      
                  
                        If trim(RECCOUNT) <> "" then
                              rs.MoveFirst
                        end if
                        
                        do while not rs.EOF
                              Act.WriteLine("<tr>")
                              for i = 0 to Request.Form("ckbox").Count  -1             
                                    act.WriteLine("<td align=""middle"">" & rs(i) & "&nbsp;</td>" )
                              next  
                              act.WriteLine("</tr>")
                              rs.MoveNext
                        loop
      
      Act.WriteLine("</table><table border=1><center>")                  
      '------------- Sales Amount ------------                        
      'Act.WriteLine("<tr>")
            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>" )
      'act.WriteLine("</tr>")            
      '--------------------------------------
' move to the next record
'rs.movenext

' 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
'next

' All non repetitive html on top goes here
act.WriteLine("</table></center></body></html>")

' close the object (excel)
act.close

'******* Display Date *********
If day(date)<10 then
      Days="0"&day(date)      
else
      days=day(date)
end if

If month(date)<10 then
      months="0"&month(date)      
else
      months=month(date)
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 & " "  %>
            <br><br><br><center>
                  <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>
                              </td></tr>
                  </table>
            </center>
      <%end if
end if

end if
0
 

Author Comment

by:waheedzz
ID: 9738478

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
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 2

Expert Comment

by:Sairam_S
ID: 9738931
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
Next

sUsers = Join(arrUsers, ",")

Now,
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.
0
 
LVL 3

Expert Comment

by:vijay7248
ID: 9738999
Ok here it is.

First while moving with the recordset rs
You'll be having something like this..
jsel=0
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.
rs.movenext
wend
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) {      
                  document.form1.selids.value=document.form1.selids.value+","+eval("document.form1.select"+i).value;
            }
      }

//after this function call your onsubmit function say...
chkdata();
//or submit your form to the export page form from here itself
document.form1.action = "export.asp";
document.form1.submit();
//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()
///////document.form1.selids.value=document.form1.selids.value+"','"+eval("document.form1.select"+i).value;

 


0
 

Author Comment

by:waheedzz
ID: 9745771

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("<tr>")
                  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>")
            Response.Write("</tr>")

            oRs.Close
      Next
      Response.Write("</table></body></html>")
i hope u gusy get the idea..ok
BYE
0
 

Author Comment

by:waheedzz
ID: 9745772

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("<tr>")
                  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>")
            Response.Write("</tr>")

            oRs.Close
      Next
      Response.Write("</table></body></html>")
i hope u guys get the idea..ok
BYE
0
 
LVL 1

Expert Comment

by:ives
ID: 10574212
Does anyone have the final code for this?  I would greatly appreciate it.  I'm actually doing the exact same thing now.

Many thanks,

Ives
0
 

Expert Comment

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

Thanks

Kx
0
 

Author Comment

by:waheedzz
ID: 11454521
hi,
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
      Next
Bye











0
 
LVL 2

Expert Comment

by:SeanLWilliams
ID: 11475937
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>

0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now