Solved

Creating an Excel worksheet from  the ADO Record Set

Posted on 2007-03-21
10
214 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
  • 5
  • 5
10 Comments
 
LVL 63

Expert Comment

by:Zvonko
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:sriniram
Comment Utility
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
Comment Utility
What is the html page code for your button?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:sriniram
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
You are welcome.
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

In Part 1 (http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/A_7849-Hex-Maze.html) we covered the hexagonal maze basics -- how the cells are represented in a JavaScript array and how the maze is displayed.  In this part, we'…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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…

762 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

7 Experts available now in Live!

Get 1:1 Help Now