Wrox Home  
Search P2P Archive for: Go
Shopping CartView Cart  |  My Account  |  Support
Home Browse Titles

P2P Archives
 

Archive Index
Back to Active P2P Forum

 


Wrox Chapters on Demand - Download the chapters you need and take them on the go!
Wrox First - Instant Access online to new Wrox books

New Titles for ASP.NET

Cover image for product 0470379308
Professional ASP.NET 3.5 Security, Membership, and Role Management with C# and VB
Cover image for product 047041569X
LINQ-ifying TheBeerHouse: An N-Tier LINQ Web Application with ASP.NET 2.0 Website Programming Problem Design Solution
Wrox Blox
  Return to Index  

asptoday_discuss thread: Re: Exporting ASP Reports to Excel Format

Message #1 by raymond.gonsalves@v... on Thu, 2 May 2002 23:37:09
Hello Lawrence,

Thank you for helping me solve my problem:

i.e. When the save dialogbox prompts the user to save the file it suggests 
the filename "theASPpostingpage.asp" as the file to save and in the Save 
as File Type box it list "Active Server Page".
 
I would like it to suggest the filename to be "myfile.xls" so the users do 
not accidently save the file as an *.asp file on their hard drive.

I'm not using the File System Object. I'm exporting data from a table on 
my ASP page to Excel so I'm building the Excel file on the fly. Your code 
that helped me was:

Response.Clear
Response.AddHeader "Content-Disposition","attachment; filename=Assets.xls"
Response.CharSet = "UTF-8"
Response.ContentType = "application/msexcel"

MANY THANKS!!!

Sincerly,
Raymond Gonalves
Rye, NY

_________________________________________________________________________
The complete routine for exporting from the table to Excel is as follows:

<%@ Language=VBScript %>
<%

Response.Buffer=True

  Dim adoConn
  Dim adoCmd
  Dim adoRS
  Dim sTRConnectionSTRing

Response.Clear
Response.AddHeader "Content-Disposition","attachment; filename=Assets.xls"
Response.CharSet = "UTF-8"
Response.ContentType = "application/msexcel"
  
  'create ADO Objects
  Set adoConn = Server.CreateObject("ADODB.Connection")
  Set adoCmd = Server.CreateObject("ADODB.Command")
  Set adoRS = Server.CreateObject("ADODB.Recordset")
  
  STRConnSTRing="Provider=MSDASQL.1;Persist Security Info= 
False;UserID=dstprof;Password=dstprof;Data Source=DISTPROF" 
 
  'Get the POs   
   adoCmd.CommandText = "SELECT * FROM CIRASSET WHERE Country='" & Session
("countryvalue") & "' and projectyear between " & Session("ExcelYearfrom") 
& " and " & Session("ExcelYearto")		' & Session("countryvalue")

  'Open the Connection
  adoConn.Open sTRConnSTRing

  'associate the Command with the Open Connection
  adoCmd.ActiveConnection = adoConn

  'reTRieve recordset
  Set adoRS = adoCmd.Execute
	Response.Write "<FONT color='000080'><STRONG>Asset</STRONG></FONT>"
	Response.Write "<TABLE border='1' cellPadding='1' cellSpacing='1' 
width='1025'>"
	
	Response.Write "<TR bgcolor='#E6E6FA'>"
	'Response.Write "<TD width='25'></TD>"
	Response.Write "<TD width='50'><FONT size=2 
color='#000080'>Year</FONT></TD>"
	Response.Write "<TD width='50'><FONT size=2 
color='#000080'>Asset#</FONT></TD>"
	Response.Write "<TD width='250'><FONT size=2 
color='#000080'>Description</FONT></TD>"
	Response.Write "<TD width='75'><FONT size=2 
color='#000080'>Balance Sheet Code</FONT></TD>"
	Response.Write "<TD width='125'><FONT size=2 
color='#000080'>January</FONT></TD>"
	Response.Write "<TD width='125'><FONT size=2 
color='#000080'>February</FONT></TD>"
	Response.Write "<TD width='125'><FONT size=2 
color='#000080'>March</FONT></TD>"
	Response.Write "<TD width='125'><FONT size=2 
color='#000080'>April</FONT></TD>"	
	Response.Write "<TD width='125'><FONT size=2 
color='#000080'>May</FONT></TD>"
	Response.Write "<TD width='125'><FONT size=2 
color='#000080'>June</FONT></TD>"
	Response.Write "<TD width='125'><FONT size=2 
color='#000080'>July</FONT></TD>"
	Response.Write "<TD width='125'><FONT size=2 
color='#000080'>August</FONT></TD>"
	Response.Write "<TD width='125'><FONT size=2 
color='#000080'>September</FONT></TD>"
	Response.Write "<TD width='125'><FONT size=2 
color='#000080'>October</FONT></TD>"
	Response.Write "<TD width='125'><FONT size=2 
color='#000080'>November</FONT></TD>"
	Response.Write "<TD width='125'><FONT size=2 
color='#000080'>December</FONT></TD>"	
	Response.Write "</TR>"


  Do While not adoRS.EOF 
    Response.Write "<TR>" & vbcrlf
    'Response.Write "<TD><FONT size=2> <Ahref='EditPO.asp?Projkey=1&pono=" 
& adoRS("PONO").value & "'>Edit</A></FONT></TD>" & vbcrlf		
    Response.Write "<TD><FONT style=arial size=2>" & adoRS
("PROJECTYEAR").value & "</FONT></TD>" & vbcrlf
    Response.Write "<TD ALIGN=CENTER><FONT style=arial size=2>" & adoRS
("ASSET_LINE_NO").value & "</FONT></TD>" & vbcrlf
    If adoRS("DESCRIPTION").value = "" Then
      Response.Write "<TD><FONT style=arial size=2>"
      Response.Write "&nbsp;"
      Response.Write "</FONT></TD>" & vbcrlf
    Else
    Response.Write "<TD><FONT style=arial size=2>" & adoRS
("DESCRIPTION").value & "</FONT></TD>" & vbcrlf
    End If
    Response.Write "<TD ALIGN=CENTER><FONT style=arial size=2>" & adoRS
("BalanceSheetCode").value & "</FONT></TD>" & vbcrlf
    Response.Write "<TD ALIGN=RIGHT><FONT style=arial size=2>" & 
FormatNumber(adoRS("JanAsset").value,2,-1,0,-1) & "</FONT></TD>" & vbcrlf
    Response.Write "<TD ALIGN=RIGHT><FONT style=arial size=2>" & 
FormatNumber(adoRS("FebAsset").value,2,-1,0,-1) & "</FONT></TD>" & vbcrlf
    Response.Write "<TD ALIGN=RIGHT><FONT style=arial size=2>" & 
FormatNumber(adoRS("MarAsset").value,2,-1,0,-1) & "</FONT></TD>" & vbcrlf
    Response.Write "<TD ALIGN=RIGHT><FONT style=arial size=2>" & 
FormatNumber(adoRS("AprAsset").value,2,-1,0,-1) & "</FONT></TD>" & 
vbcrlf    
    Response.Write "<TD ALIGN=RIGHT><FONT style=arial size=2>" & 
FormatNumber(adoRS("MayAsset").value,2,-1,0,-1) & "</FONT></TD>" & vbcrlf
    Response.Write "<TD ALIGN=RIGHT><FONT style=arial size=2>" & 
FormatNumber(adoRS("JunAsset").value,2,-1,0,-1) & "</FONT></TD>" & vbcrlf
    Response.Write "<TD ALIGN=RIGHT><FONT style=arial size=2>" & 
FormatNumber(adoRS("JulAsset").value,2,-1,0,-1) & "</FONT></TD>" & vbcrlf
    Response.Write "<TD ALIGN=RIGHT><FONT style=arial size=2>" & 
FormatNumber(adoRS("AugAsset").value,2,-1,0,-1) & "</FONT></TD>" & vbcrlf 
    Response.Write "<TD ALIGN=RIGHT><FONT style=arial size=2>" & 
FormatNumber(adoRS("SepAsset").value,2,-1,0,-1) & "</FONT></TD>" & vbcrlf
    Response.Write "<TD ALIGN=RIGHT><FONT style=arial size=2>" & 
FormatNumber(adoRS("OctAsset").value,2,-1,0,-1) & "</FONT></TD>" & vbcrlf
    Response.Write "<TD ALIGN=RIGHT><FONT style=arial size=2>" & 
FormatNumber(adoRS("NovAsset").value,2,-1,0,-1) & "</FONT></TD>" & vbcrlf
    Response.Write "<TD ALIGN=RIGHT><FONT style=arial size=2>" & 
FormatNumber(adoRS("DecAsset").value,2,-1,0,-1) & "</FONT></TD>" & 
vbcrlf               
    adoRS.MoveNext
  Loop
 

  Response.Write "</TABLE>"
  Response.Write "<BR>"
  adoConn.Close 


%>
Message #2 by Lawrence Yap <assoc_con@y...> on Mon, 13 May 2002 15:11:59 -0700 (PDT)
Hello Raymond,

     No problem.  I still want to suggest though to
use a SELECT CASE for your download functionality so
that you're not limited to "Excel" files only.  It'll
pretty much handle any type of files that falls in the
"octect" stream.

take care,
Lawrence

--- raymond.gonsalves@v... wrote:
> Hello Lawrence,
> 
> Thank you for helping me solve my problem:
> 
> i.e. When the save dialogbox prompts the user to
> save the file it suggests 
> the filename "theASPpostingpage.asp" as the file to
> save and in the Save 
> as File Type box it list "Active Server Page".
>  
> I would like it to suggest the filename to be
> "myfile.xls" so the users do 
> not accidently save the file as an *.asp file on
> their hard drive.
> 
> I'm not using the File System Object. I'm exporting
> data from a table on 
> my ASP page to Excel so I'm building the Excel file
> on the fly. Your code 
> that helped me was:
> 
> Response.Clear
> Response.AddHeader
> "Content-Disposition","attachment;
> filename=Assets.xls"
> Response.CharSet = "UTF-8"
> Response.ContentType = "application/msexcel"
> 
> MANY THANKS!!!
> 
> Sincerly,
> Raymond Gonalves
> Rye, NY
> 
>
_________________________________________________________________________
> The complete routine for exporting from the table to
> Excel is as follows:
> 
> <%@ Language=VBScript %>
> <%
> 
> Response.Buffer=True
> 
>   Dim adoConn
>   Dim adoCmd
>   Dim adoRS
>   Dim sTRConnectionSTRing
> 
> Response.Clear
> Response.AddHeader
> "Content-Disposition","attachment;
> filename=Assets.xls"
> Response.CharSet = "UTF-8"
> Response.ContentType = "application/msexcel"
>   
>   'create ADO Objects
>   Set adoConn 
> Server.CreateObject("ADODB.Connection")
>   Set adoCmd = Server.CreateObject("ADODB.Command")
>   Set adoRS = Server.CreateObject("ADODB.Recordset")
>   
>   STRConnSTRing="Provider=MSDASQL.1;Persist Security
> Info= 
> False;UserID=dstprof;Password=dstprof;Data
> Source=DISTPROF" 
>  
>   'Get the POs   
>    adoCmd.CommandText = "SELECT * FROM CIRASSET
> WHERE Country='" & Session
> ("countryvalue") & "' and projectyear between " &
> Session("ExcelYearfrom") 
> & " and " & Session("ExcelYearto")		' &
> Session("countryvalue")
> 
>   'Open the Connection
>   adoConn.Open sTRConnSTRing
> 
>   'associate the Command with the Open Connection
>   adoCmd.ActiveConnection = adoConn
> 
>   'reTRieve recordset
>   Set adoRS = adoCmd.Execute
> 	Response.Write "<FONT
> color='000080'><STRONG>Asset</STRONG></FONT>"
> 	Response.Write "<TABLE border='1' cellPadding='1'
> cellSpacing='1' 
> width='1025'>"
> 	
> 	Response.Write "<TR bgcolor='#E6E6FA'>"
> 	'Response.Write "<TD width='25'></TD>"
> 	Response.Write "<TD width='50'><FONT size=2 
> color='#000080'>Year</FONT></TD>"
> 	Response.Write "<TD width='50'><FONT size=2 
> color='#000080'>Asset#</FONT></TD>"
> 	Response.Write "<TD width='250'><FONT size=2 
> color='#000080'>Description</FONT></TD>"
> 	Response.Write "<TD width='75'><FONT size=2 
> color='#000080'>Balance Sheet Code</FONT></TD>"
> 	Response.Write "<TD width='125'><FONT size=2 
> color='#000080'>January</FONT></TD>"
> 	Response.Write "<TD width='125'><FONT size=2 
> color='#000080'>February</FONT></TD>"
> 	Response.Write "<TD width='125'><FONT size=2 
> color='#000080'>March</FONT></TD>"
> 	Response.Write "<TD width='125'><FONT size=2 
> color='#000080'>April</FONT></TD>"	
> 	Response.Write "<TD width='125'><FONT size=2 
> color='#000080'>May</FONT></TD>"
> 	Response.Write "<TD width='125'><FONT size=2 
> color='#000080'>June</FONT></TD>"
> 	Response.Write "<TD width='125'><FONT size=2 
> color='#000080'>July</FONT></TD>"
> 	Response.Write "<TD width='125'><FONT size=2 
> color='#000080'>August</FONT></TD>"
> 	Response.Write "<TD width='125'><FONT size=2 
> color='#000080'>September</FONT></TD>"
> 	Response.Write "<TD width='125'><FONT size=2 
> color='#000080'>October</FONT></TD>"
> 	Response.Write "<TD width='125'><FONT size=2 
> color='#000080'>November</FONT></TD>"
> 	Response.Write "<TD width='125'><FONT size=2 
> color='#000080'>December</FONT></TD>"	
> 	Response.Write "</TR>"
> 
> 
>   Do While not adoRS.EOF 
>     Response.Write "<TR>" & vbcrlf
>     'Response.Write "<TD><FONT size=2>
> <Ahref='EditPO.asp?Projkey=1&pono=" 
> & adoRS("PONO").value & "'>Edit</A></FONT></TD>" &
> vbcrlf		
>     Response.Write "<TD><FONT style=arial size=2>" &
> adoRS
> ("PROJECTYEAR").value & "</FONT></TD>" & vbcrlf
>     Response.Write "<TD ALIGN=CENTER><FONT
> style=arial size=2>" & adoRS
> ("ASSET_LINE_NO").value & "</FONT></TD>" & vbcrlf
>     If adoRS("DESCRIPTION").value = "" Then
>       Response.Write "<TD><FONT style=arial size=2>"
>       Response.Write "&nbsp;"
>       Response.Write "</FONT></TD>" & vbcrlf
>     Else
>     Response.Write "<TD><FONT style=arial size=2>" &
> adoRS
> ("DESCRIPTION").value & "</FONT></TD>" & vbcrlf
>     End If
>     Response.Write "<TD ALIGN=CENTER><FONT
> style=arial size=2>" & adoRS
> ("BalanceSheetCode").value & "</FONT></TD>" & vbcrlf
>     Response.Write "<TD ALIGN=RIGHT><FONT
> style=arial size=2>" & 
> FormatNumber(adoRS("JanAsset").value,2,-1,0,-1) &
> "</FONT></TD>" & vbcrlf
>     Response.Write "<TD ALIGN=RIGHT><FONT
> style=arial size=2>" & 
> FormatNumber(adoRS("FebAsset").value,2,-1,0,-1) &
> "</FONT></TD>" & vbcrlf
>     Response.Write "<TD ALIGN=RIGHT><FONT
> style=arial size=2>" & 
> FormatNumber(adoRS("MarAsset").value,2,-1,0,-1) &
> "</FONT></TD>" & vbcrlf
>     Response.Write "<TD ALIGN=RIGHT><FONT
> style=arial size=2>" & 
> FormatNumber(adoRS("AprAsset").value,2,-1,0,-1) &
> "</FONT></TD>" & 
> vbcrlf    
>     Response.Write "<TD ALIGN=RIGHT><FONT
> style=arial size=2>" & 
> FormatNumber(adoRS("MayAsset").value,2,-1,0,-1) &
> "</FONT></TD>" & vbcrlf
>     Response.Write "<TD ALIGN=RIGHT><FONT
> style=arial size=2>" & 
> FormatNumber(adoRS("JunAsset").value,2,-1,0,-1) &
> "</FONT></TD>" & vbcrlf
>     Response.Write "<TD ALIGN=RIGHT><FONT
> style=arial size=2>" & 
> FormatNumber(adoRS("JulAsset").value,2,-1,0,-1) &
> "</FONT></TD>" & vbcrlf
>     Response.Write "<TD ALIGN=RIGHT><FONT
> style=arial size=2>" & 
> FormatNumber(adoRS("AugAsset").value,2,-1,0,-1) &
> "</FONT></TD>" & vbcrlf 
>     Response.Write "<TD ALIGN=RIGHT><FONT
> style=arial size=2>" & 
> FormatNumber(adoRS("SepAsset").value,2,-1,0,-1) &
> "</FONT></TD>" & vbcrlf
>     Response.Write "<TD ALIGN=RIGHT><FONT
> style=arial size=2>" & 
> FormatNumber(adoRS("OctAsset").value,2,-1,0,-1) &
> "</FONT></TD>" & vbcrlf
>     Response.Write "<TD ALIGN=RIGHT><FONT
> style=arial size=2>" & 
> FormatNumber(adoRS("NovAsset").value,2,-1,0,-1) &
> "</FONT></TD>" & vbcrlf
>     Response.Write "<TD ALIGN=RIGHT><FONT
> style=arial size=2>" & 
> FormatNumber(adoRS("DecAsset").value,2,-1,0,-1) &
> "</FONT></TD>" & 
> vbcrlf               
>     adoRS.MoveNext
>   Loop
> 
=== message truncated ===


__________________________________________________
Do You Yahoo!?
LAUNCH - Your Yahoo! Music Experience
http://launch.yahoo.com

  Return to Index  
About Wrox  |  Contact Us  |  Subscribe to an RSS Feed of New Wrox Titles
Copyright © 2000-2004 by John Wiley & Sons, Inc. or related companies. All rights reserved. Please read our Privacy Policy