XML to Excel?
Not sure if this is the right forum for this question. If not I apologize.
I am returning XML from SQL server 2000, and popualting an ActiveX grid. I need a button on the page that says "export to Excel". I do not have a clue how to export xml to excel any thoughts?
Thank you very much!!!!!!
Mike
Peter J. Boettcher posted this at 13:02 — 18th April 2001.
They have: 812 posts
Joined: Feb 2000
It's not worth the extra overhead, when the user clicks the "Export to Excel" button I would just have an ASP page that calls the database and creates the Excel object. It doesn't make sense to add the extra step of generating the XML when you don't really need it.
PJ | Are we there yet?
pjboettcher.com
1aspfan posted this at 13:38 — 18th April 2001.
They have: 32 posts
Joined: Jan 2001
Peter,
Thank you for your reply. Do you know of any tutorials for working with the Excel Object?
I really appreciate your help.
One more question. The grid is filled with XML is there not a way to use the XML that already exsists to keep from going back to the database?
Peter J. Boettcher posted this at 13:51 — 18th April 2001.
They have: 812 posts
Joined: Feb 2000
To stay on the client side you would need some sort of client side control that would parse the data into Excel. I don't know if it's possible to do this without going back to the server (or giving the client-side control total access over the hard-drive, not likely). Here's some code I use to query a database and transform the output into Excel:
<%
Response.ContentType = "application/vnd.ms-excel"
%>
<%
Dim oCmd
Dim oInventory
Dim objParam
Dim Ctr
Ctr = 0
Set oCmd = Server.CreateObject("ADODB.Command")
Set oInventory = Server.CreateObject("ADODB.RecordSet")
oInventory.CursorLocation = adUseClient
oInventory.CursorType = adOpenStatic
With oCmd
.ActiveConnection = Application("DATABASE")
.CommandText = "Stored_Procedure_Name"
.CommandType = adCmdStoredProc
End With
Set objParam = oCmd.CreateParameter("SPOrg_ID",adVarChar,adParamInput,50,Request.Cookies("ORGID"))
oCmd.Parameters.Append objParam
Set objParam = oCmd.CreateParameter("SPViewType",adInteger,adParamInput,4,1)
oCmd.Parameters.Append objParam
Set objParam = oCmd.CreateParameter("SPSortBy",adVarChar,adParamInput,50,0)
oCmd.Parameters.Append objParam
Set oInventory = oCmd.Execute
%>
<title><%=oInventory("OrgName")%> Inventory Listing (as of <%=Date%>)</title>
<TABLE border CROSSTAB CROSSTABGRAND=ROWCOLUMN bgcolor=white width=100%>
<TR>
<TD nowrap colspan=7>
<font style="font-size:18pt"><strong><%=oInventory("OrgName")%> Inventory Listing</strong></font> (as of <%=Date%>)</TD>
</TR>
<tr>
<td valign=top bgcolor=C0C0C0><strong>Keystone Legend:</strong></td>
<td colspan=6 bgcolor=C0C0C0><strong>Coverage:</strong></td>
</tr>
<tr>
<td valign=top>
AMS - Active Monitoring Service<br>
ARP - Advanced Replacement Parts<br>
ESS - Extended Software Subscription<br>
OSS - On-Site Service<br>
SLR - Service Level Reporting<br><br></td>
<td valign=top nowrap colspan=6>
Standard - (Monday - Friday, 9:00-5:00, next business day response)<br>
Extended - (Monday - Friday, 9:00-5:00, 4 hour response time)<br>
Premium - (24 hours/day 7 days/week, 4 hour response time)</td>
</tr>
<tr>
<th bgcolor=black rowfield><strong><font color=white>Item Description</font></strong></th>
<th bgcolor=black><strong><font color=white>Model Number</font></strong></th>
<th bgcolor=black><strong><font color=white>Serial Number</font></strong></th>
<th bgcolor=black><strong><font color=white>Location</font></strong></th>
<th bgcolor=black><strong><font color=white>Keystone Coverage Type</font></strong></th>
<th bgcolor=black><strong><font color=white>Coverage Window</strong></font></th>
<th bgcolor=black><strong><font color=white>Coverage Expiry</strong></font></th>
</tr>
<% Do While Not oInventory.EOF %>
<% If Ctr = 0 Then %>
<TR>
<% Ctr = 1 %>
<% Else %>
<tr bgcolor=lightyellow>
<% Ctr = 0 %>
<% End IF %>
<TD align=left><%=Trim(oInventory("Description"))%></TD>
<td align=left><%=Trim(oInventory("PartNum"))%></td>
<td align=left><%=Trim(oInventory("SerialNum"))%></td>
<td align=left><%=Trim(oInventory("USite"))%></td>
<td align=left><%=CoverageHolder%></td>
<td align=left><%=ExtractCoverage%></td>
<td align=left>Expires in <%=ExpireDays%> days, on <%=oInventory("ExpireDate")%></td>
</TR>
<% oInventory.MoveNext
Loop %>
</TABLE>
<%
oInventory.Close
Set oInventory = Nothing
Set oCmd = Nothing
%>
Also you might want to check out: http://www.ezxmlwizard.com/ , they seem to have a component that might work for you.
PJ | Are we there yet?
pjboettcher.com
1aspfan posted this at 14:00 — 18th April 2001.
They have: 32 posts
Joined: Jan 2001
Peter,
Thanks for the code!!!!!!!!!!!!!!
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.