How to format an Excel workbook while streaming MIME content

Article translations Article translations
Article ID: 271572 - View products that this article applies to.
This article was previously published under Q271572
Expand all | Collapse all

SUMMARY

This article demonstrates how you can use Active Server Pages (ASP) to create a formatted workbook that can be streamed as MIME content to Microsoft Excel.

MORE INFORMATION

Excel can persist storage information in a format that is an HTML/XML hybrid. When you save an Excel 2000 workbook as a Web page, Excel creates a file that contains a combination of HTML tags and XML tags that have special meaning to Excel. A workbook saved in this format can go from Excel to the browser and back to Excel again (a "round trip") without losing workbook integrity.

When you open a workbook saved as a Web page in Internet Explorer, the HTML tags are used to render the document. When you open a workbook in this format in Excel, Excel uses the XML tags for settings that might pertain to the workbook, worksheets, rows and columns. For more information on using HTML and XML with Excel 2000, see "Microsoft Office HTML and XML Reference" at the following MSDN Web site:
http://msdn2.microsoft.com/en-us/library/Aa155477(office.10).aspx
Using ASP, you can create your own documents in Excel's HTML/XML format so that when the document is streamed to the client browser, it is displayed in Excel. You can control the cell placement of data and specify any workbook or worksheet settings that are supported by Excel's HTML/XML format. To create an Excel workbook in the HTML/XML format and then stream it as MIME content to Excel on the client, follow these steps:
  1. Paste the following code into Notepad:
    <%@ Language=VBScript %>
    <%
      ' Check for a value passed on the address bar.
      if (Request.QueryString("i")) = "" then bFirst = true
      ' If we have a value for "i", we know that we can display the
      ' data in Excel.
      if (bFirst = false) then
        ' Buffer the content and send it to Excel.
        Response.Buffer = true
        Response.ContentType = "application/vnd.ms-excel" 
    %>
    <HTML xmlns:x="urn:schemas-microsoft-com:office:excel">
    <HEAD>
    <style>
      <!--table
      @page
         {mso-header-data:"&CMultiplication Table\000ADate\: &D\000APage &P";
    	mso-page-orientation:landscape;}
         br
         {mso-data-placement:same-cell;}
    
      -->
    </style>
      <!--[if gte mso 9]><xml>
       <x:ExcelWorkbook>
        <x:ExcelWorksheets>
         <x:ExcelWorksheet>
          <x:Name>Sample Workbook</x:Name>
          <x:WorksheetOptions>
           <x:Print>
            <x:ValidPrinterInfo/>
           </x:Print>
          </x:WorksheetOptions>
         </x:ExcelWorksheet>
        </x:ExcelWorksheets>
       </x:ExcelWorkbook>
      </xml><![endif]--> 
    </HEAD>
    <BODY>
    <TABLE>
    <%
       ' Build a multiplication table from 1,1 to i,j.
       for i = 1 to CInt(Request.QueryString("i"))
         Response.Write "  <TR>" + vbCrLf
         for j = 1 to CInt(Request.QueryString("j"))
           if (j = 1) or (i = 1) then
             Response.Write "    <TD bgcolor=""#FFF8DC"">"
           else
             Response.Write "    <TD bgcolor=""#B0C4DE"">"
           end if
    	   Response.Write CStr(i*j) + "</TD>" + vbCrLf
         next
         Response.Write "  </TR>" + vbCrLf
       next
    %>
    </TABLE>
    </BODY>
    </HTML>
    <%
      else
      ' The user hasn't loaded the page yet. Prompt them for
      ' values for the table.
    %>
    <HTML>
    <BODY>
    Please enter indices for the multiplication table:<BR>
    <FORM action="xlmime.asp" method=GET>  
      i = <INPUT type="text" name=i style="WIDTH: 25px"><BR>
      j = <INPUT type="text" name=j style="WIDTH: 25px"><BR><BR/>
      <INPUT type="submit" value="Submit"><BR/>
    </FORM>
    </BODY>
    </HTML>
    <%
      end if
    %>
    					
  2. Save the file as XLMime.asp in the virtual root directory of your web server. (The default virtual root is C:\Inetpub\Wwwroot.)
  3. Start Internet Explorer and browse to http://YourWebServer/xlmime.asp, where YourWebServer is the name of your Web server.
  4. In the Web page that appears, supply numeric values in each of the text boxes provided and then click Submit. Excel in-place activates in the browser with a new workbook. The new workbook contains formatted data in the number of rows (i) and the number of columns (j) that you specified. Also, if you check the Page Setup information for the worksheet, you will notice that the orientation is set to landscape and that a custom header exists.

REFERENCES

For more information about streaming MIME content to Office applications, click the following article numbers to view the articles in the Microsoft Knowledge Base:
266263 BUG: Word 2000 and Excel 2000 display ASP source when using MIME type to stream data
199841 How to display ASP results using Excel in IE with MIME types
For more information on Office Automation, visit the Microsoft Office Development support site:
http://support.microsoft.com/ofd

Properties

Article ID: 271572 - Last Review: August 14, 2007 - Revision: 6.3
APPLIES TO
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Active Server Pages 4.0
Keywords: 
kbhowto KB271572

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com