asked on

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.

Zvonko

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.

sriniram

ASKER

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.

Zvonko

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>


<% 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.

sriniram

ASKER

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

Zvonko

What is the html page code for your button?

sriniram

ASKER

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.

sriniram

ASKER

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:


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

ASKER CERTIFIED SOLUTION

Zvonko

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sriniram

ASKER

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.

Zvonko

You are welcome.