Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Creating an Excel worksheet from  the ADO Record Set

Posted on 2007-03-21
10
Medium Priority
?
235 Views
Last Modified: 2010-03-19
I want to create an Excel work sheet from the content of the ADO record set.

On submit of the ASP page, I refresh the page with the content of the ADO record set into an html table in the ASP page. On a condition, I create a button to for exporting the record set data to Excel. After the page is refreshed, when the user click this button, I want to write the content of the record set  to an Excel file i.e. giving the option for the user to save this data into their local drive. This will be client side activity.

Is it possible to use the data from ADO record set in client side?

I looked into the web but could not figure out the right answer. Can anyone suggest me to overcome this problem? If anyone could give the sample code, it will great.

The sequence of operations is:
1. On submit of the page, the page is refreshed with the data from the ADO Record Set.
2. While refreshing, a button will be created for exporting the data from the ADO Record Set.
3. When the user click on this button, the user should be prompted for the file name.
4. After the file name is given and clicked on the OK button, the file should be saved in the local drive of the user. (This should be standard windows save functionality).

Let me know if more information required.

Thanking you in advance.
Sri.
0
Comment
Question by:sriniram
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 63

Expert Comment

by:Zvonko
ID: 18763975
One question: the data are pulled from ADO and displayed on page, right? Are after that display any data changed, either on browser side or on ADO side?
The question is because, the only thing that the button should do is to repeat the ADO fetch action if the original ADO data did not change and not show the data as html page, but show the ADO data as html <table> data with a MIME type of Excel-Application. The user gets the ADO data as embedded Excel single sheet and can store the sheet as local Excel book.

0
 

Author Comment

by:sriniram
ID: 18764007
Thanks Zvonko.

The data pulled from the ADO will be displayed. This data is not changed on the ADO side or the browser side. The same data displayed in the ASP page should be written to the Excel application - i.e. fetch from the record set and write in the excel application.

Regards
Sri.
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 18764077
Ok, then it is easy.
Your button needs to call a second ASP page doing the same thing as the html display page and do the following on that ASP that does Excel sheet display:
<%

Response.Buffer = TRUE
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "content-disposition", "attachment;filename=Conservation.xls"

''' do your ADO job here to open the connection and get the recordset
'Set adoRS = Server.CreateObject("ADODB.RecordSet")


%>

<HTML>
<BODY>
<TABLE >

<TR>
<% for intFieldsCnt=1 to adoRS.Fields.count %>
   <TD><%=adoRS.Fields(intFieldsCnt).Name%></TD>
<% next %>
</TR>

<!-- server-side loop adding Table entries -->
<% do while not adoRS.EOF %>
<TR>
      <% for intFieldsCnt=1 to adoRS.Fields.count %>
            <TD><%=adoRS.Fields(intFieldsCnt).Value%></TD>
      <%next%>
</TR>
<%
  adoRS.movenext
  loop
 
  adoRS.close
  Set adoRS = Nothing
 %>

</TABLE>
</BODY>
</HTML>


The upper will NOT result in a HTML page but in a Excel Sheet that the user may save localy or discard, as he like it.



0
Build and deliver software with DevOps

A digital transformation requires faster time to market, shorter software development lifecycles, and the ability to adapt rapidly to changing customer demands. DevOps provides the solution.

 

Author Comment

by:sriniram
ID: 18764226
Thanks Zvonko.

If I open one more ASP page when the button is clicked, the same data displayed in the parent ASP page on the new ASP page.

Is there anyway I can avoid the display of the next page? When the user click the button, I should display the dialog box for save the file locally.

Thanks
Sri
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 18764308
What is the html page code for your button?
0
 

Author Comment

by:sriniram
ID: 18764557
Hi Zvonko,

You are right. I think we need to open a new page on the OnClick event of the button.

I was thinking of going through the record set and writing to an excel application without opening a new page in the client side code (using java script). It seems like it may not be possible.

I will try with your suggestion.

Thanks
Sri.
0
 

Author Comment

by:sriniram
ID: 18782510
Hi Zvonko, I have created the page as you suggested. But the output is not opened as Excel. It just appears as regular dipslay in browser.

Can you look into the code and see how I can open in Excel format?

The code is given below:
<!--#INCLUDE FILE="includes/adovbs.inc" -->
<!--#INCLUDE FILE="includes/CommonFunctions.inc" -->
<html>
<head>
</head>
<%

Response.Buffer = TRUE
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "content-disposition", "attachment;filename=Conservation.xls"

Set adoRS = Server.CreateObject ("ADODB.Recordset")
OpenConnection()
Set adoRS = adoConn.Execute ("Stored Procedure Name ")
if err.number = 0 then
'    Response.Write("Error =0")
else
    Response.Write("Error occurred.")
end if
%>
<body>
    <table>
            <tr>
    <%IF adoRS.EOF<>-1 then
        WHILE NOT (adoRS.EOF)   %>
            <tr>
                  <td width="20%" height="23" align="left" class="contenttext">
                  <font size="30">
                  <%Response.Write(adoRS("LastName")) & "," & adoRS("FirstName"))%></font></a></td>
                  <td width="20%" height="23" align="center" class="contenttext">
                  <font size="2"><%=adoRS("applicantSSN")%></font></td>
                  <td width="20%" height="23" align="center" class="contenttext">
                  <font size="2"><%=adoRS("AlternateID")%></font></td>
                  <td width="20%" height="23" align="center" class="contenttext">
                  <font size="2"><%=adoRS("CSA")%></font></td>
                  <td width="20%" height="23" align="center" class="contenttext">
                  <font size="2"><%=adoRS("Status")%></font></td>
            </tr>
                <% adoRS.movenext
            Wend%>
    <% adoRS.Close
       Set adoRS = Nothing
    End if%>
      </table>
</body>
</html>
0
 
LVL 63

Accepted Solution

by:
Zvonko earned 2000 total points
ID: 18782554
OK, change it like this:
1.) Before Response.Buffer and Header settings should NO html output occure. After an output you cannot change headers.
2.) Send a <table only, no <htm> and <body> tag.

Like this:
<%

Response.Buffer = TRUE
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "content-disposition", "attachment;filename=Conservation.xls"

Set adoRS = Server.CreateObject ("ADODB.Recordset")
OpenConnection()
Set adoRS = adoConn.Execute ("Stored Procedure Name ")
if err.number = 0 then
'    Response.Write("Error =0")
else
    Response.Write("Error occurred.")
end if
%>
    <table>
            <tr>
    <%IF adoRS.EOF<>-1 then
        WHILE NOT (adoRS.EOF)   %>
            <tr>
                  <td width="20%" height="23" align="left" class="contenttext">
                  <font size="30">
                  <%Response.Write(adoRS("LastName")) & "," & adoRS("FirstName"))%></font></a></td>
                  <td width="20%" height="23" align="center" class="contenttext">
                  <font size="2"><%=adoRS("applicantSSN")%></font></td>
                  <td width="20%" height="23" align="center" class="contenttext">
                  <font size="2"><%=adoRS("AlternateID")%></font></td>
                  <td width="20%" height="23" align="center" class="contenttext">
                  <font size="2"><%=adoRS("CSA")%></font></td>
                  <td width="20%" height="23" align="center" class="contenttext">
                  <font size="2"><%=adoRS("Status")%></font></td>
            </tr>
                <% adoRS.movenext
            Wend%>
    <% adoRS.Close
       Set adoRS = Nothing
    End if%>
      </table>

0
 

Author Comment

by:sriniram
ID: 18782761
Thanks Zvonko.

I think this will be enough for the current requirement.

There is a possibility that I may need the output in Text or CSV format. Let me see if the client asks for it.

Thanks Again.
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 18782775
You are welcome.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

721 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