Creating an Excel worksheet from the ADO Record Set

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.
sriniramAsked:
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.

ZvonkoSystems architectCommented:
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
sriniramAuthor Commented:
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
ZvonkoSystems architectCommented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

sriniramAuthor Commented:
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
ZvonkoSystems architectCommented:
What is the html page code for your button?
0
sriniramAuthor Commented:
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
sriniramAuthor Commented:
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
ZvonkoSystems architectCommented:
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

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
sriniramAuthor Commented:
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
ZvonkoSystems architectCommented:
You are welcome.
0
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
Microsoft SQL Server 2005

From novice to tech pro — start learning today.