如何使用 Visual Basic 或 ASP 创建 Excel 2002 和 Excel 2003 的 XML 电子表格

文章翻译 文章翻译
文章编号: 285891 - 查看本文应用于的产品
展开全部 | 关闭全部

概要

Microsoft Excel 2002 和 Microsoft Office Excel 2003 支持 XML 格式,它们可以使用该格式加载和保存工作簿(或 XML 电子表格)。通过使用此 XML 电子表格格式,可以为 Excel 创建含多个工作表的、带格式的工作簿,而无需借助自动化功能。如果您需要创建 Excel 工作簿,但却无法自动化 Excel(如在 Web 服务器上或从某个服务中),或者运行代码的系统中未安装 Excel,则最好使用此方法。

本文阐释了如何创建 XML 模板,该模板与可扩展样式表语言 (XSL) 转换配合使用时,将生成一个可直接在 Excel 中打开的、带格式的工作簿。本文分别就 Active Server Pages (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 中,新建一个标准 EXE 项目。
  2. 在“项目”菜单上,单击“引用”。选择“Microsoft ActiveX 数据对象 2.5”(或更高版本)和“Microsoft XML 3.0”的类型库。
  3. 向 Form1 中添加 CommandButton,然后将下面的代码添加到该按钮的“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 罗斯文示例数据库的正确安装路径。
  4. 按 F5 以运行该程序。
  5. 单击 Form1 上的按钮。罗斯文数据库中 Orders Detail 表的数据将显示在已创建的 Excel 工作簿模板中。
该示例使用 Workbooks 集合的 Open 方法生成 XML 表格并自动化 Excel,从而将 XML 以新工作簿的形式打开。还可以直接将 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 罗斯文示例数据库的正确安装路径。

  2. 启动 Internet Explorer 并浏览到 http://YourWebServer/ExcelXML.asp,其中 YourWebServer 是您的 Web 服务器的名称。罗斯文数据库中 Orders Detail 表的数据将显示在已创建的 Excel 工作簿模板中。

其他说明

使用 XML 表格格式无法保留以下 Excel 功能:
  • 图表
  • OLE 对象
  • 绘制图形或自选图形
  • VBA 项目
  • 组及分级显示

下载

XMLSpread.exe 包含本文所介绍的 XML 样式表、Visual Basic 项目和 ASP 脚本。 可以从 Microsoft 下载中心下载以下文件:
收起这个图片展开这个图片
下载
立即下载 Xmlsprd.exe 程序包。 发布日期:2001 年 3 月 27 日

有关如何下载 Microsoft 支持文件的更多信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
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
有关将 MIME 内容以流的形式传输到 Microsoft Office 应用程序的更多信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
266263 BUG:在使用 MIME 类型以流的方式传送数据时 Word 2000 和 Excel 2000 显示 ASP 源
199841 如何在 IE 中使用 Excel 以 MIME 类型显示 ASP 结果
271572 如何在以流的形式传输 MIME 内容时设置 Excel 工作簿的格式
307021 如何使用 Visual Basic .NET 向 Microsoft Excel 2002 传输 XML 数据

属性

文章编号: 285891 - 最后修改: 2006年2月20日 - 修订: 7.0
这篇文章中的信息适用于:
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 标准版
  • Microsoft Active Server Pages 4.0
  • Microsoft Visual Basic 6.0 专业版
关键字:?
kbhowto kbdownload KB285891
Microsoft和/或其各供应商对于为任何目的而在本服务器上发布的文件及有关图形所含信息的适用性,不作任何声明。 所有该等文件及有关图形均"依样"提供,而不带任何性质的保证。Microsoft和/或其各供应商特此声明,对所有与该等信息有关的保证和条件不负任何责任,该等保证和条件包括关于适销性、符合特定用途、所有权和非侵权的所有默示保证和条件。在任何情况下,在由于使用或运行本服务器上的信息所引起的或与该等使用或运行有关的诉讼中,Microsoft和/或其各供应商就因丧失使用、数据或利润所导致的任何特别的、间接的、衍生性的损害或任何因使用而丧失所导致的之损害、数据或利润负任何责任。

提供反馈

 

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