????? ??????? Visual Basic ?? ASP ?????? ???? ?????? XML ?? Excel 2002 ? Excel 2003

?????? ????????? ?????? ?????????
???? ???????: 285891 - ??? ???????? ???? ????? ????? ??? ???????.
????? ???? | ?? ????

??????

???? Microsoft Excel 2002 Microsoft Office Excel 2003 ??? ????? XML ???? ?????? ?? ????? ???????? ??? ???????? (?? ????? ?????? XML). ???????? ????? ???? ?????? XML ??? ????? ????? multi-sheet ? ????? ???????? ??? Excel ??? ??????? ???????. ?? ???? ??????? ??? ??????? ????? ????? ??? ????? Excel ?????? ????? ??? feasible ?????? Excel (??? ??? ???? ??? ?? ?? ????) ?? ?? ??? ????? Excel ??? ???? ????? ????????? ???????? ?????? ??.

????? ??? ??????? ???? ??? ????? ????? ???? XML ???? ??? ????????? ?? ????? ??? ????? ??????? ??????? (XSL) ? ???? ?????? ???? ???? ???? ???? ?????? ?? Excel. XML ?? ?? ????? ??????? ??? ?? "????? ?????? ?????? (ASP)" ? "Visual Basic. ?? ????? ??? ????? ????????? ???????? 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 ???? ??? ??????? ?? ??? ?????? ?? "Orders.xsl" ?? ????? ???? ?????? XML.

    ??????: ?? ???? ??? ????? ?? ???? ?????? ??? ???? ? ??? ??? ????? ??????? ?????? ?????? ???? ?? ??? ????? ???? .xml ??? ??? ?????.
  13. ?? ?????? Excel.
  14. ???? Orders.xsl ?? ?? ???? ?? ??? ???????.
  15. ????? ??????? ??? < ? xml ??????? = "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 ????? ?????? ????? ADO ??????? XML ??? ???? ?????? XML

  1. ?? Visual Basic? EXE ????? ???? ????? ???????.
  2. ?? ??????? ??????? ???? "?????". ??? Microsoft XML 3.0 ?????? ??????? ?? Microsoft 2.5 ???? ?????? ActiveX (?? ????).
  3. ????? CommandButton ??? Form1 ?????? ???????? ???????? ??????? ??? ????? ???? ?????:
        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
    					
    ?????? ??? ??? ?????? ?? ?????? ??????? ????? (sConn Const) ??? ????? ??? ???? ??????? ?????? ??? ????? Northwind Access ????? ????????.
  4. ???? F5 ?????? ????????.
  5. ???? ??? ???? ??? Form1. ???????? ?? "?????? ????????" ???? ?????? ?? ????? ???????? Northwind ?? ???? ???? Excel ???? ??? ???????.
???? ??????? ?????? ???? ?????? 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 ????? ?????? ????? ADO ??????? XML ??? ???? ?????? XML

  1. ?? ???? ???????? ???????? ??????? ?? ???????. ??? ????????? ???????? ??? ExcelXML.asp ?? ?????? ????? ??????? ????? ?? ??? ??????. ?????? ?????? ????? ??????? ????????? ?? 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
    
    %>
    					
    ?????? ??? ??? ?????? ?? ?????? ??????? ????? (sConn Const) ??? ????? ??? ???? ??????? ?????? ??? ????? Northwind Access ????? ????????.

  2. ??? ????? Internet Explorer ?? ?????? ??? http:// YourWebServer / ExcelXML.asp ??? ???? YourWebServer ?? ??? ???? ???. ???????? ?? "?????? ????????" ???? ?????? ?? ????? ???????? Northwind ?? ???? ???? Excel ???? ??? ???????.

??????? ??????

????? Excel ??????? ?? ???? ?????? ???? ???? ?? XML ????? ???? ??????:
  • ????????
  • ?????? OLE
  • ????? ????? ?? ??????? ?????????
  • ?????? VBA
  • ???????? "?" ???? ??????

???????

????? XMLSpread.exe ??? ???? ????? XML ????? Visual Basic ? ?????? ASP ??? ??????? ?? ??? ???????. ????? ????? ?????? ??????? ?? "???? ??????? ?? Microsoft":
?? ??? ??????????? ??? ??????
Download
Download the Xmlsprd.exe package now.????? ???????:, ?? ???? 2001

?????? ??? ???? ?? ????????? ??? ????? ????? ????? ??? Microsoft? ???? ??? ??? ??????? ?????? ?????? ?? "????? ??????? ?? Microsoft:
119591????? ?????? ??? ????? ??? Microsoft ?? ??????? ??? ??????
???? Microsoft ???? ??? ????? ????? ?? ?????????. ??????? Microsoft ???? ?? ?????? ??? ??????? ??????? ??? ??????? ???? ?? ??? ?????. ??? ????? ????? ??? ?????? ????? ?????? ???? ????? ??? ??? ????? ??? ??????? ??? ???? ?? ??? ?????.

?????

?????? ??? ???? ?? ????????? ??? ??? ??????? ??????? ADO ?? XML ?????, ???? ???? ???? ????? Microsoft (MSDN) ?????? ??? ?????:
http://msdn2.microsoft.com/en-us/library/ms681538.aspx
?????? ??? ???? ?? ????????? ??? ??? ????? MIME ??? Microsoft Office ?????????? ???? ??? ????? ???????? ??????? ?????? ?? "????? ??????? ?? Microsoft:
266263BUG: Word 2000 ?Excel 2000 ???? ???? ASP ??? ??????? ??? MIME ??? ??? ????????
199841????? ??? ASP ????? ???????? Excel ?? ?????? IE ?? ????? MIME
271572??? ??? ????? ???? Excel ????? ??? ????? MIME
307021????? ??? ?????? XML ??? Microsoft Excel 2002 ???????? Visual Basic .NET

???????

???? ???????: 285891 - ????? ??? ??????: 29/???/1428 - ??????: 7.4
????? ???
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Active Server Pages 4.0
  • Microsoft Visual Basic 6.0 Professional Edition
????? ??????: 
kbmt kbdownload kbhowto KB285891 KbMtar
????? ????
???: ??? ????? ??? ?????? ???????? ?????? ????? ???? ????? ?????????? ????? ?? ????????? ?????? ????. ???? ???? ?????????? ???? ?? ???????? ???????? ?????? ????????? ????? ????????? ???????? ????? ???????? ?????? ?? ?????? ??? ?? ???????? ???????? ?? ????? ??????? ?????? ??? ??????? ?????? ??. ?????? ?? ???? ??? ??????? ???????? ????? ?? ???? ????? ?????? ??? ????? ??? ????? ??????? ?? ????? ?? ?????? ??? ??? ??????? ??????? ?? ????? ????? ????? ????? ?????. ?? ????? ???? ?????????? ??????? ??? ????? ?? ??????? ?? ????? ?????? ?? ??? ????? ?? ????? ??????? ?? ???????? ?? ??? ???????. ???? ???? ?????????? ???????? ??? ????? ?????? ??????? ??????
???? ??? ????? ??????? ?????? ??????????285891

????? ???????

 

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