Giving Your Name and Other Properties
Dear Web Team:
We use Response.ContentType, Response.Write and Response.BinaryWrite frequently on our intranet pages to generate download files from database information. A good example is the contacts sheet that provides a link for the user to click that generates a vCard file containing the contact information for the user they are viewing.
The problem with this approach is that the .asp file (genvcf.asp) is the filename that is "presented" to the browser when downloading the file. Can we control the file name of the response object? (in the above example the Name of the contact would be a good choice...)
Phil Revill
The Web Team replies:
Since you didn't include a little sample for us to work with, we'll use a sample we've used before but with some new information we discovered. This example generates an Excel file in HTML. This makes much less work for the server because it means that there is no need to instantiate and automate Excel on the server. We won't explain much about the main code, just the bits that are new. If you want the basics, read Turning the Tables section in the July 2000 column. However, the answer to you question is to look at the content-disposition line.
The cool new stuff for this example is how to set the document properties for an Excel file when creating it with HTML—the ones you get when you choose properties under the file menu option. When you read the following sample, look for things like how we tell Excel how to display the date in the spreadsheet, what filename to use for the file, and how we specify the office document properties.
<%@ language=vbscript %>
<%
response.buffer = true
response.ContentType = "application/vnd.ms-excel"
response.AddHeader "content-disposition", "inline; filename=dynamic.xls"
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML xmlns:o="urn:schemas-microsoft-com:office:office">
<HEAD>
<title>an Excel page</title>
<!--[if gte mso 9]><xml>
<o:DocumentProperties>
<o:Subject>An experiment with HTML in Excel</o:Subject>
<o:Author>auto-generated</o:Author>
<o:Keywords>HTML</o:Keywords>
<o:Description>Blah blah blah</o:Description>
<o:Created><%= now %></o:Created>
<o:Category>Experiments</o:Category>
<o:Company>My Company</o:Company>
</o:DocumentProperties>
</xml><![endif]-->
<style>
.xl25 { WHITE-SPACE: normal; mso-number-format: "mmm\ d,\ yyyy" }
.head { BACKGROUND: #ccccff; COLOR: green}
</style>
</HEAD>
<body>
<table width=260>
<tr><td class=head colspan=5 align=center><b>Our Data</b></td></tr>
<tr><td class=xl25>5/10/2002</td>
<%
for i = 1 to 4
response.write "<td width=40>"
response.write i + i
response.write "</td>"
next
%>
<tr>
<td colspan=4><b>total</b></td>
<td><b>=sum(B2:E2)</b></td>
</tr>
</table>
</body>
</HTML>
<%
response.flush
response.end
%>
First of all, when it comes to properties, notice that we establish a namespace in the <html> tag so that we can reference the office xml DTD. Also, notice that the title of the document is not stored with the other document properties. It is extracted straight from the html document title. Finally, we specify the date format by using a style sheet declaration. Look closely at style .xl25. If you want to know more about how to format information or what else you can do, experiment with saving an Excel file as html. There are a lot of extra tags that Excel uses so it can take a while to find what you are looking for. This is a great way to dynamically generate Excel files without trying to use the middle step of saving an Excel file on the hard disk, and because it's HTML and not a full Excel file, it makes life easier for any of your viewers who are still using modems.
source