Thanks!!!!

1aspfan — Wed, 18 Apr 2001 14:00:48 +0000

Peter,

Thanks for the code!!!!!!!!!!!!!!

Peter J. Boettcher — Wed, 18 Apr 2001 13:51:48 +0000

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")%>&nbsp;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")%>&nbsp;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.

Thank you

1aspfan — Wed, 18 Apr 2001 13:38:09 +0000

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 — Wed, 18 Apr 2001 13:02:12 +0000

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.