文章編號: 285891 - 上次校閱: 2006年2月20日 - 版次: 7.0

如何使用 Visual Basic 或 ASP 在 Excel 2002 和 Excel 2003 中建立 XML 試算表

檢視此文章適用的產品。
系統提示本文適用於您使用的作業系統之外的作業系統。與您不相關的文章內容已停用。
全部展開 | 全部摺疊

結論

Microsoft Excel 2002 和 Microsoft Office Excel 2003 支援 XML 格式且可以透過此格式載入及儲存活頁簿 (或 XML 試算表)。您可以使用此 XML 試算表格式，在 Excel 中建立多工作表的格式化活頁簿，而不需使用「自動化」。當您需要建立 Excel 活頁簿時，這個方法可能會很好用，但是如果要將 Excel 自動化 (例如在 Web 伺服器上或從某服務進行)，或者執行程式碼的系統未安裝 Excel，則不適用。

本文將告訴您，如何建立在與「可延伸樣式表語言」(XSL) 轉換搭配使用時，會產生可以在 Excel 中直接開啟之格式化活頁簿的 XML 範本。「動態伺服器網頁」(Active Server Page，ASP) 和 Visual Basic 都可以示範 XML 轉換。在 Visual Basic 程式碼範例的部份，試算表資料完全是用 XML/XSL 產生的，使用最低程度的「自動化」即可在 Excel 中開啟結果。

注意 本文所述的範例已可供下載；如需下載指示，請參閱本文結尾的＜下載＞一節。
回此頁最上方

其他相關資訊

建立活頁簿的 XML 範本

  1. 建立新的資料夾 C:\ExcelXML。
  2. 在 Excel 中，開啟新的活頁簿。將下列資料加入至活頁簿之指定的儲存格 A1:F2 中。儲存格 F2 應該會輸入為公式。
    A1:Order ID    B1:Product ID   C1:Unit Price   D1:Quantity   E1:Discount  F1:Total
A2:aaa         B2:111          C2:222          D2:333        E2:0         F2:=C2*D2*(1-E2)
  3. 在儲存格 F3 中，輸入下列公式：
    =SUM(F$2:F2)
  4. 選取儲存格 A1:F1。在 [格式] 功能表上，按一下 [儲存格]。套用粗體字型、下框線以及實心色彩的儲存格陰影。按一下 [確定]。
  5. 選取欄 A:F。在 [格式] 功能表上，指向 [欄]，然後按一下 [欄寬]。輸入 15 做為新的欄寬，然後按一下 [確定]。在欄 A:F 仍為選取的情況下，在 [格式] 功能表上，按一下 [儲存格]。在 [對齊] 索引標籤上，選取水平對齊清單中的 [置中對齊]，然後按一下 [確定]。
  6. 選取欄 E。在 [格式] 功能表上，按一下 [儲存格]。在 [數字] 索引標籤上，按一下 [百分比] 並指定 0 個小數位數，然後按一下 [確定]。
  7. 選取欄 F。在 [格式] 功能表上，按一下 [儲存格]。在 [數字] 索引標籤上，按一下 [會計專用]，然後按一下 [確定]。
  8. 選取儲存格 A3:F3。在 [格式] 功能表上，按一下 [列高]，輸入 25，然後按一下 [確定]。在 A3:F3 仍為選取的情況下，在 [格式] 功能表上，按一下 [儲存格]。套用上框線至儲存格，然後按一下 [確定]。
  9. 在 [工具] 功能表上，按一下 [選項]。在 [檢視] 索引標籤上，清除 [格線] 核取方塊，然後按一下 [確定]。
  10. 選取列 2。在 [視窗] 功能表上，按一下 [凍結窗格]。
  11. 選取儲存格 A1。
  12. 在 [檔案] 功能表上，按一下 [另存新檔]。瀏覽至先前建立的 C:\ExcelXML 資料夾，再以 [XML 試算表] 格式將活頁簿另存為 Orders.xsl。

    注意：在 [另存新檔] 對話方塊的 [檔案名稱] 方塊中，用雙引號 (") 括住檔名，您的檔名就不會加上 .xml 副檔名。
  13. 結束 Excel。
  14. 使用任何文字編輯器 (例如 [記事本]) 開啟 Orders.xsl。
  15. 將下列內容插入 <?xml version="1.0"?> 標記與 <Workbook> 標記之間：
    <xsl:stylesheet xmlns:xsl="http://www.w3.org/TR/WD-xsl">
<xsl:template match="/">
<xsl:pi name="xml">version="1.0"</xsl:pi>
  16. 在 Orders.xsl 的結尾，附加下列兩行：
    </xsl:template>
</xsl:stylesheet>
  17. 在 Orders.xsl 中找出下列這組標記 (這組標記代表工作表中的儲存格 A2:F2)
       <Row ss:Height="14.25">
    <Cell><Data ss:Type="String">aaa</Data></Cell>
    <Cell><Data ss:Type="Number">111</Data></Cell>
    <Cell><Data ss:Type="Number">222</Data></Cell>
    <Cell><Data ss:Type="Number">333</Data></Cell>
    <Cell><Data ss:Type="Number">0</Data></Cell>
    <Cell ss:Formula="=RC[-3]*RC[-2]*1*(1-RC[-1])"><Data ss:Type="Number">73926</Data></Cell>
   </Row>
    並用下列 XSL 程式碼加以取代：
    <xsl:for-each select="xml/rs:data/z:row">
   <Row ss:AutoFitHeight="0" ss:Height="13.5">
    <Cell><Data ss:Type="String"><xsl:value-of select="@OrderID"/></Data></Cell>
    <Cell><Data ss:Type="Number"><xsl:value-of select="@ProductID"/></Data></Cell>
    <Cell><Data ss:Type="Number"><xsl:value-of select="@UnitPrice"/></Data></Cell>
    <Cell><Data ss:Type="Number"><xsl:value-of select="@Quantity"/></Data></Cell>
    <Cell><Data ss:Type="Number"><xsl:value-of select="@Discount"/></Data></Cell>
    <Cell ss:Formula="=RC[-3]*RC[-2]*(1-RC[-1])"><Data ss:Type="Number">0</Data></Cell>
   </Row>
</xsl:for-each>
  18. 將變更儲存至 Orders.xsl，然後關閉檔案。

使用 Visual Basic，將 XML 持續型 ADO 資料錄集轉換成 XML 試算表

  1. 在 Visual Basic 中，建立新的標準執行檔專案。
  2. 在 [專案] 功能表上，按一下 [設定引用項目]。選取 [Microsoft ActiveX Data Object 2.5] (或更新版本) 和 [Microsoft XML 3.0] 的型別程式庫。
  3. 將 CommandButton 加入至 Form1，然後將下列程式碼加入按鈕的 Click 事件：
        Const sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
                  "C:\program files\microsoft office\office10\samples\Northwind.mdb"
    Const sOutXML = "C:\ExcelXML\Orders.xml"
    Const sXSL = "C:\ExcelXML\Orders.xsl"
    
    'Retrieve an ADO recordset of the Orders Detail table in Northwind.
    Dim nRecords As Long, nFields As Long
    Dim rs As New ADODB.Recordset
    rs.Open "SELECT * FROM [Order Details]", sConn, adOpenStatic, adLockOptimistic
        
    'Persist the recordset to a new DOMDocument and store the record count.
    Dim oXML As New DOMDocument
    rs.Save oXML, adPersistXML
    nRecords = rs.RecordCount
    nFields = rs.Fields.Count
    rs.Close
      
    'Load the XSL (the workbook template with XSL directives) into a DOMDocument.
    Dim oXSL As New DOMDocument
    oXSL.Load sXSL
    
    'Transform the XML using the style sheet.
    Dim oResults As New DOMDocument
    oXML.transformNodeToObject oXSL, oResults
    
    If oXSL.parseError.errorCode <> 0 Then
        MsgBox "Parse Error: " & oResults.parseError.reason
    Else
       'Modify the ss:ExpandedRowCount attribute for the <table> node to
       'indicate the correct number of rows (count of records + 1 row for
       'the header + 1 row for the total).
        Dim oTable As MSXML2.IXMLDOMElement
        Set oTable = oResults.selectSingleNode("Workbook/Worksheet/Table")
        oTable.setAttribute "ss:ExpandedRowCount", nRecords + 2

        '***************************************
        'Save the results to a file.
        Open sOutXML For Output As #1
        Print #1, oResults.xml
        Close #1

        'Open the XML in Excel.
        Dim oExcel As Object
        Set oExcel = CreateObject("Excel.Application")
        oExcel.Workbooks.Open sOutXML
        oExcel.Visible = True
        oExcel.UserControl = True
        '***************************************
    End If
    注意 必要時，請修改連接字串 (Const sConn)，使字串含有指向 Access Northwind 範例資料庫的正確安裝路徑。
  4. 按下 F5 以執行程式。
  5. 按一下 Form1 上的按鈕。Northwind 資料庫中 Orders Detail 表格的資料，會出現在您先前建立的 Excel 活頁簿範本中。
此範例會產生 XML 試算表，並使用 Workbooks 集合的 Open 方法將 XML 開啟為新的活頁簿，以自動化 Excel。另外，您也可以將 XML 直接插入至現有工作表的特定儲存格中。為詳加說明，請以下列內容取代包含在星號內的程式碼：
        'Display the data in a workbook starting at cell B2.
        Dim oExcel As Object, oBook As Object
        Set oExcel = CreateObject("Excel.Application")
        Set oBook = oExcel.Workbooks.Add
        oBook.Worksheets(1).Range("B2").Resize(nRecords + 2, nFields + 1).Value(11) = _
            oResults.xml 'Note: xlRangeValueXMLSpreadsheet=11
        oExcel.Visible = True
        oExcel.UserControl = True
完成此變更之後，請再次執行程式。請注意，這次 XML 試算表資料是插入至新的活頁簿，並由第一個工作表的儲存格 B2 開始。儲存格的資料及格式會套用至此範圍；不過，使用此方法時，列、欄、工作表及活頁簿的特定設定並不會繼續沿用。

使用 ASP，將 XML 持續型 ADO 資料錄集轉換成 XML 試算表

  1. 將下列程式碼貼到 [記事本] 中。將此程式碼另存為 ExcelXML.asp，儲存在 Web 伺服器的虛擬根資料夾中。注意 預設的虛擬根資料夾為 C:\inetpub\wwwroot。
    <%@ Language="vbscript"%>

<%

    Const sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\program files\microsoft office\office10\samples\Northwind.mdb"
    Const sXSL = "C:\ExcelXML\Orders.xsl"
    
    Response.Buffer = True

    'Retrieve an ADO recordset of the Orders Detail table in Northwind.
    Dim rs, nRecords
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open "SELECT * FROM [Order Details]", sConn, 3, 3
        
    'Persist the recordset to a new DOMDocument and store the record count.
    Dim oXML
    Set oXML = CreateObject("Microsoft.XMLDOM")
    rs.Save oXML, 1
    nRecords = rs.RecordCount
    rs.Close
      
    'Load the XSL (the workbook template with XSL directives) into a DOMDocument.
    Dim oXSL
    Set oXSL = CreateObject("Microsoft.XMLDOM")
    oXSL.Load sXSL
    
    'Transform the XML using the style sheet.
    Dim oResults
    Set oResults = CreateObject("Microsoft.XMLDOM")
    oXML.transformNodeToObject oXSL, oResults
    
    If oXSL.parseError.errorCode <> 0 Then
       Response.Write "Parse Error: " & oResults.parseError.reason
    Else

       'Modify the ss:ExpandedRowCount attribute for the <table> node in the XSL.
        Dim oTable
        Set oTable = oResults.selectSingleNode("Workbook/Worksheet/Table")
        oTable.setAttribute "ss:ExpandedRowCount", nRecords + 2

        'Return the resulting XML Spreadsheet for display in Excel.
        Response.ContentType = "application/vnd.ms-excel"
        Response.Charset = "ISO-8859-1"
        Response.Write oResults.XML
        Response.Flush

    End If

%>
    注意 必要時，請修改連接字串 (Const sConn)，使字串含有指向 Access Northwind 範例資料庫的正確安裝路徑。

  2. 啟動 Internet Explorer 並瀏覽至 http://YourWebServer/ExcelXML.asp，其中 YourWebServer 是您的 Web 伺服器名稱。Northwind 資料庫中 Orders Detail 表格的資料，會出現在您先前建立的 Excel 活頁簿範本中。

其他注意事項

下列 Excel 功能無法持續使用 XML 試算表格式：
  • 圖表
  • OLE 物件
  • 繪圖圖形或快取圖案
  • VBA 專案
  • 群組及外框

下載

XMLSpread.exe 含有本文所述的 XML 樣式表、Visual Basic 專案及 ASP 指令碼。 您可以從「Microsoft 下載中心」下載下列檔案：
摺疊此圖像展開此圖像
下載
立即下載 Xmlsprd.exe 套件。 (http://download.microsoft.com/download/excel2002/other/1.0/win98me/en-us/xmlsprd.exe) 發行日期：2001 年 3 月 27 日

如需有關如何下載 Microsoft 支援檔案的詳細資訊，請按一下下面的文件編號，檢視「Microsoft 知識庫」中的文件：
119591? (http://support.microsoft.com/kb/119591/ ) 如何從線上服務取得 Microsoft 支援檔案
Microsoft 已對這個檔案做過病毒的掃描。Microsoft 是利用發佈當日的最新病毒偵測軟體來掃描檔案，看看有沒有病毒感染。檔案會儲存在安全的伺服器上，以避免任何未經授權的更改。
回此頁最上方

?考

如需有關以 XML 格式儲存 ADO 資料錄集的詳細資訊，請參閱下列 Microsoft Developer Network (MSDN) 網站：
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnasdj00/html/wsc.asp (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnasdj00/html/wsc.asp)
如需有關串流 MIME 內容到 Microsoft Offic 應用程式的詳細資訊，請按一下下面的文件編號，檢視「Microsoft 知識庫」中的文件：
266263? (http://support.microsoft.com/kb/266263/ ) BUG: Word 2000 and Excel 2000 Display ASP Source When Using MIME Type to Stream Data
199841? (http://support.microsoft.com/kb/199841/ ) 如何使用 MIME 類型在 IE 中以 Excel 顯示 ASP 結果
271572? (http://support.microsoft.com/kb/271572/ ) 如何在串流 MIME 內容時格式化 Excel 活頁簿
307021? (http://support.microsoft.com/kb/307021/ ) HOW TO: Transfer XML Data to Microsoft Excel 2002 by Using Visual Basic .NET
回此頁最上方
這篇文章中的資訊適用於:
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Active Server Pages 4.0
  • Microsoft Visual Basic 6.0 Professional Edition
回此頁最上方
關鍵字：?
kbhowto kbdownload KB285891
回此頁最上方
Microsoft及(或)其供應商不就任何在本伺服器上發表的文字資料及其相關圖表資訊的恰當性作任何承諾。所有文字資料及其相關圖表均以「現狀」供應，不負任何擔保責任。Microsoft及(或)其供應商謹此聲明，不負任何對與此資訊有關之擔保責任，包括關於適售性、適用於某一特定用途、權利或不侵權的明示或默示擔保責任。Microsoft及(或)其供應商無論如何不對因或與使用本伺服器上資訊或與資訊的實行有關而引起的契約、過失或其他侵權行為之訴訟中的特別的、間接的、衍生性的損害或任何因使用而喪失所導致的之損害、資料或利潤負任何責任。