Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. How To Format Cell Data When You Are Creating an Excel File With an Active Server Pages Page

How To Format Cell Data When You Are Creating an Excel File With an Active Server Pages Page

View products that this article applies to.

Summary

This article demonstrates how to format a field to preserve the leading digits (such as the "000" in "0001112") or a specific formula when you create an Excel file with an Active Server Pages (ASP) page.

↑ Back to the top

More information

If you set the 
Response.ContentType = "application/vnd.ms-excel"
in an ASP page, Excel will be launched to process the document and display it as an Excel spreadsheet. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
199841 How To Display ASP Results Using Excel in IE with MIME Types
If the table field has a leading zero, then the zero (or zeros) will be stripped because Excel treats the field as a numeric value.

You can specify the cell as a string value to preserve the format in the ASP page. You can even specify the formula for calculation in your code.

The following page, Test.asp, demonstrates how you can do this: 
<%@ Language=VBScript %>
   <%
      'Change HTML header to specify Excel's MIME content type
      Response.Buffer = TRUE
      Response.ContentType = "application/vnd.ms-excel"
   %>
   <HTML>
   <BODY>
   <!-- Our table which will be translated into an Excel spreadsheet -->
   <TABLE WIDTH=75% BORDER=1 CELLSPACING=1 CELLPADDING=1>
   <TR>
      <TD>Book ID</TD>
      <TD>Book Title</TD>
      <TD>Price</TD>
      <TD>Shipping Fee</TD>
      <TD>Tax Rate</TD>
      <TD>Total Cost</TD>
   </TR>
   <TR>
      <TD>="0001"</TD>
      <TD>The Perfect Programming Book</TD>
      <TD>13.00</TD>
      <TD>5.00</TD>
      <TD>8.25%</TD>
      <TD>=Sum(c2:d2)*(1+E2)</TD>
   </TR>
   </TABLE>
   </BODY>
   </HTML>

↑ Back to the top

References

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
247318 BUG: Word 2000 and Excel 2000 Do Not Redirect Correctly When Using Response.Redirect
264143 FIX: ASP Session Variables Empty When Office 2000 MIME Types Are Streamed with Internet Explorer

↑ Back to the top

Keywords: KB260239, kbscript, kbhowto

↑ Back to the top

Article Info
Article ID : 260239
Revision : 4
Created on : 2/12/2007
Published on : 2/12/2007
Exists online : False
Views : 465