How To Use XSL to Transform Excel XML Spreadsheet for Server-Side Use

???? ?????? ???? ??????
???? ID: 278976 - ?? ???????? ?? ?????? ??? ?? ?? ???? ???? ???? ??.
??? ?? ??????? ???? | ??? ?? ??????? ????

??????

Excel 2002 introduces new XML functionality that enables developers to extract formatted data from worksheets in an XML Spreadsheet (XMLSS) format. TheValue:property of aRangeobject can return cell formatting and contents in XMLSS. XMLSS is well-formed XML that can be loaded by the Microsoft XML Parser for manipulation at runtime. ?? ?????? ?? ?? ??????? ????? ????????? ???? ?? ??? ??? ????? ????????? ?? ??? XML ???? ?? ??????? ???? ?? ??? ??? ?? ???? ?? ??? ??????, ?????? ??? Excel ????????????? ?? ????? ??? ??????? ?? ??? ??? ???? ????????????? ??? ??? ?????? ?? ?????? XML ???? ??????? ???? ?? ?? ?????? ????? ????? (ASP) ????????? ?? ??? illustrates ???

?? ???? ???? Excel ????????????? ????, ?? ASP ????????? ?? ?? stylesheet ?? ??? ????? ?? ???? ?????? ?????? ?? ??? ??? ??? ?? ??????? ?? ?????? ?? ?????????? ??? ???? ????? ?? ????? ???????? ??? ?? explanations ?? ??? ?? ?????? ???? ?? ?? ???? ?????? ???? ?? ??? ??? ?? ???? ?? ???? ??? ???? ?? ????? ?? ??????? ???? ?? ???; ??????? ????????? ?? ??? ?????? ??, ?? ???? ?? ??? ??? "???????" ??? ??????

???? ???????

?? ????? ??? ?? ???? ????? ?? ????? ?? ??? ???? ???? ?? ???????? ?? ??? ??? ????? ???? ?? ???:
  • ASP ?????????. ?????-???? ??? ?? ?? ???? ?? ??? ??????? ?? ?????? ?? ???? ?? ?? ASP ????????? ??? ????????? expects ?????? ?? ?? ??????? XML ?????? ??? ??? ????????? ??????? ?? XML ?? ???? ?? ?? ??? ????? ???? ?? ActiveX ???? ???????? (ADO) ?? ????? ???? ????????? ????? ??????? ?? ???? ?? ???? ?????? ???
  • Excel ?????????????. ???????-???? ??? ???? ????????? ?? ??? ???? ????????????? ??? Excel ????????????? ???? ?? ?????????? ?? ?? ???? ??? ??????? ???? ?? ??? ASP ????????? ?? ??? interacts ?????? ?? ??? ???
  • XSL Stylesheet. ??? stylesheet ???????????? Excel XMLSS ASP ????????? ?? ???????? ?? ?? ????? XML ?????? ??? ??? Excel ??? ?????? stylesheet ??? ?? ???? ?? ?? XMLSS ???? ?? ??? ASP ????????? ?? ??? ???? ?? ??? ????? ?? ????? ???? ?? ???? ????? XML ?????????????

??? 1: ????? ASP ???? ?????? ?????????

???? ??? ????? (??????? ??? ??????? ?? C:\Inetpub\Wwwroot) ?? ??????? ??? ??????? ??? OrderProc ??? ?? ?? ??? ??????? ?????? OrderProc ??????? ??? ???? ?? ?? ????????? ?? ??? OrderEntry.asp ??? ?? ??? ?? ????? ?????? ????????? ????? ????????? Access ??????? ?? ????? ???? ??; ?? Northwind.mdb ???? ?? ??? ?? ??? ???? Office ??????? ?? ??? ???? ?? ??? ??????? ???????? (sConn) ?? ??????? ???? ?? ???????? ?? ???? ???
<%@ Language="vbscript" CodePage="65001"%>

<%
    Response.Buffer = True
    Response.ContentType = "text/xml"
  
    Dim oDataXML                    'Custom Data XML passed in by caller.
    Dim oConn                       'ADO Connection to Northwind database.
    Dim oOrdersRS, oDetailsRS       'ADO Recordsets for the Orders table and Order Details Table.
    Dim oItems                      'Collection of nodes meeting the match. "Order/Items/Item"
    Dim oItem                       'Single node in oItems.
    Dim sCustID                     'Customer ID for the new order.
    Dim sOrderID                    'Order ID of the newly created record. in Orders table
    Dim sStatus                     'Status of order processing.
    Dim bContinue                   'Flag that indicates whether or not to continue processing the order.

    Const sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\program files\microsoft office\office10\samples\northwind.mdb"
    On Error Resume Next

    'Load the XML passed into the request.
    Set oDataXML = Server.CreateObject("Microsoft.XMLDOM")
    oDataXML.Load Request
    bContinue = True

    'Obtain the Customer ID. If no customer id is provided, report an error.
    sCustID = oDataXML.selectSingleNode("Order/CustomerID").Text
    If sCustID="" Then
       sStatus = "There is no customer specified for the order"
       bContinue = False
    End If

    'Obtain collection of "items" for this order. If the item count = 0, report an error.
    If bContinue Then
       Set oItems = oDataXML.SelectNodes("Order/Items/Item")
       if oItems.length = 0 Then
          sStatus = "There are no items to process for this order"
          bContinue = False
       End If
    End If

    'Open a connection to the Northwind database.
    If bContinue Then
       Set oConn = CreateObject("ADODB.Connection")
       oConn.Open sConn
       if err.Number <> 0 Then
          sStatus = err.Description
          bContinue = False
       end if

    End If

    'Open the Orders and Order Details tables and add the new records.
    If bContinue Then

       Set oOrdersRS = CreateObject("ADODB.Recordset")
       oOrdersRS.Open "SELECT * FROM Orders", oConn, 2, 3
       Set oDetailsRS = CreateObject("ADODB.Recordset")
       oDetailsRS.Open "SELECT * FROM [Order Details]", oConn, 2, 3

       'Add a new entry in the Orders table.
       oOrdersRS.AddNew
       oOrdersRS.Fields("CustomerID").Value = sCustID
       oOrdersRS.Fields("OrderDate").Value = CDate(Now)
       oOrdersRS.Update
       sOrderID = oOrdersRS.Fields("OrderID").Value
        
       'And a new record for each item in the order XML to the Order Details table.
       If err.number = 0 Then
          For Each oItem In oItems
              oDetailsRS.AddNew
              oDetailsRS.Fields("OrderID").Value = sOrderID
              oDetailsRS.Fields("ProductID").Value = oItem.childnodes(0).Text
              oDetailsRS.Fields("Quantity").Value = CLng(oItem.childnodes(1).Text)
              oDetailsRS.Fields("UnitPrice").Value = CLng(oItem.childnodes(2).Text)
              oDetailsRS.Fields("Discount").Value = 0
              oDetailsRS.Update
          Next
       End If

       if err.Number <> 0 Then
          sStatus = err.Description
       else
          sStatus = "Success"
       end if

       'Close the recordsets and connection.
       oDetailsRS.Close
       oOrdersRS.Close
       oConn.Close

    End If
  
    'Return the resulting XML (the Order status).
    Dim sResult
    sResult = "<?xml version=""1.0""?>"
    sResult = sResult & "<OrderProcessed>" 
    sResult = sResult & "<Status>" & sStatus & "</Status>"
    sResult = sResult & "<OrderID>" & sOrderID & "</OrderID>"
    sResult = sResult & "</OrderProcessed>"
    Response.Write sResult
    Response.End
 
%> 
				
?? ???? ?? ?????? ?? ??????????? ??????? ????, ?? ??? ?? ASP ????????? expects ??????????? structured XML ????:

<?xml version="1.0"?>
<Order>
    <CustomerID>BOTTM</CustomerID>
    <Items>
        <Item>
            <ProductID>4</ProductID>
            <Quantity>11</Quantity>
            <Price>20.25</Price>
        </Item>
        <Item>
            <ProductID>18</ProductID>
            <Quantity>2</Quantity>
            <Price>63.7</Price>
        </Item>
    </Items>
</Order>
					
?? XML designates ID "BOTTM" ?? ??? ?????? ?? ??? ??? ???? ???? ????? ?? ?????? ?? ????: ?????? ?? ID ?? 4 ?? 11 ??????? ?? ?????? ?? 18 ?? ID ?? ??? 2 ????????

ASP ????????? ?? ?? ??????? ?? ????? ???? ??? ??????? ?? ??? ?????? ??? ?? ????????? ???? ?? ??? ??? ?????? ???????? ??? ??????? ??? ?????? ID ?? ?? ?? ?? ?? ???? ?????? ???? ?????; ?? ???????? ???? ???? ?? ???, ??? ASP ????????? ???? ?? ??????? ???? ?? ?? ?? ?????? ???? ???

ASP ????????? ??????? ???? ?????? ??? ?? ?? ???? ?? ?????? ???? ?? ??? XML ???? ???? ??? ?? XML ????? ?? ?????? ?? ???? ?????? ?? ????? ???? ?? ?? ?? ?? ????? ?? ???? ?????? ???? ??:

<?xml version="1.0"?>
<OrderProcessed>
    <Status>Success</Status>
    <OrderID>11078</OrderID>
</OrderProcessed>
					

??? 2: ?? ???? ????????? ?? ??? ????????????? ???????? ?????

  1. Excel ???, ???? ?? ????????????? ??????? ?????
  2. ???? ??? ???? ???? A1, A3, B3 ?? C3 ???????????:
    A1:   Customer ID    B1:                C1:
    A2:                  B2:                C2:
    A3:   Product ID     B3:   Quantity     C3:   Price
    					
  3. ???? A1:B1 ?? ??? ????? ????? ???????????? ???????? ??,????? ????-????? ????, ?? ???? ????????. ???????? ?????????? ????? ???, ?? ??? ????????? ??????? ????? ????OK. ?? ????????? ??? ????? ??Customer_ID??? B1 ?? ????
  4. ???? A3:C8 ?? ??? ????? ????? ???????????? ???????? ??,????? ????-????? ????, ?? ???? ????????. ???????? ?????????? ????? ???, ?? ??? ????????? ???????? ????? ????OK. ?? ????????? ??? ????? ??Product_ID,??????, ????????? ??? A4:A8, B4:B8 ?? C4:C8 ?? respectively ?????
  5. Visual Basic ?????? ??????? ???? ?? ??? ALT+F11 ?????..
  6. Visual Basic ?????? ???, ?????????? ???????? ??,???????. ????? ?????? ??? ??????? ??? ??????:
    Sub ProcessOrder()
    
        Const sFolder = "http://YourWebServer/OrdrProc/"
    
        'Load a new DOMDocument based on the XMLSS of the range A1:C8.
        Dim oRangeXML
        Set oRangeXML = CreateObject("Microsoft.XMLDOM")
        oRangeXML.LoadXML Range("A1:C8").Value(xlRangeValueXMLSpreadsheet)
            
        'Transform the XMLSS to custom XML that the ASP can
        'interpret as a new "order".
        Dim oXSL, oOrderXML
        Set oXSL = CreateObject("Microsoft.XMLDOM")
        oXSL.Load ThisWorkbook.Path & "\OrderEntry.xsl"
        Set oOrderXML = CreateObject("Microsoft.XMLDOM")
        oRangeXML.transformNodeToObject oXSL, oOrderXML
        
        'Submit the XMLSS to the ASP page for processing.
        Set oXMLHTTP = CreateObject("Microsoft.XMLHTTP")
        oXMLHTTP.Open "Post", sFolder  & "/OrderEntry.asp", False
        oXMLHTTP.setRequestHeader "Content-Type", "text/xml; charset=""UTF-8"""
        oXMLHTTP.send oOrderXML
        
        'Retrieve the results of the processing by the ASP page.
        Dim oResult
        Set oResult = CreateObject("Microsoft.XMLDOM")
        oResult.Load oXMLHTTP.responseXML
    
        'Check the returned XML -- if the Status is "Success", fill in the
        'Order # and display a message. If the Status is not "Success",
        'report the error.
        Dim sStatus As String
        sStatus = oResult.selectsinglenode("OrderProcessed/Status").Text
        If sStatus = "Success" Then
            MsgBox "Thank you. Your order number is " & _
                oResult.selectsinglenode("OrderProcessed/OrderID").Text
        Else
            MsgBox sStatus
        End If
    
    End Sub
    					
    ???:: ?????YourWebServer???? ??? ????? ?? ??? ?? ??? sFolder ???????? ????

  7. Visual Basic ?????? ?? ??? ????, ?? Excel ?? ???? ??????
  8. ???? ???? ????? Invoice1.xls ?? ??? ??? OrdrProc ??????? ??? ????????????? ?? ???????
"???? ???????" ?? essentially ???? A1:C8 ??? ?????? ???? ??????? ?? ??? XMLSS retrieves ?? ??? ?? ??? ???DOMDocument???????? ??? ?? ?? ??? ??????????? Stylesheet ???? (XSL) stylesheet ????? ???DOMDocument?? XML ?????? ?? ??? ??? ???? ?? ???? ASP ?? ???????? ?? ???? ??? ?? ??? XMLSS ???? ??? ?????? ?? ????? ???? ??XMLHTTPASP ????????? ?? ??? XML ???? ?? ??? ????? ?? ????? ???? ?? ??? ???????? ??? ASP ????????? ???? ??? ??????? ???? ?? ?? ???? ??? ???? ?????? ?? ??????? ?? ??? ?????? ?? ??? ???? XML ???? ???

???:: ??????? ??? ????? ?? ????? ???? XML ???? ?? ??? XMLSS transforming ?? ????? ???? ?? ??? ???????-???? ?????? ??? ???? ??? ?? XMLSS ASP ????????? ?? ??? ????? ?? ASP ?? ??? ?? ???????? ?? ???? ??? ?? ?? ???? ????

??? 3: ??? Stylesheet ?????

OrdrProc ??????? ??? OrderEntry.xsl ??? ?? ??? ?? ????? ????? ?? XSL ??? ???????:
<?xml version='1.0'?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/TR/WD-xsl">

  <xsl:template match="/">
    <xsl:pi name="xml">version="1.0"</xsl:pi>

    <Order>
      <CustomerID><xsl:value-of select="Workbook/Worksheet/Table/Row/Cell[NamedCell[@ss:Name='Customer_ID'] $and$ Data[@ss:Type!='Error']]"/></CustomerID>
      <Items>
        <xsl:for-each select="Workbook/Worksheet/Table/Row[Cell[NamedCell[@ss:Name='Product_ID'] $and$ Data[@ss:Type!='Error']]]">
          <Item>
            <xsl:apply-templates/>
          </Item>
        </xsl:for-each>
      </Items>
    </Order>

  </xsl:template>

  <xsl:template match="Cell[NamedCell[@ss:Name='Product_ID']]">
    <ProductID><xsl:value-of select="Data"/></ProductID>
  </xsl:template>

  <xsl:template match="Cell[NamedCell[@ss:Name='Quantity']]">
    <Quantity><xsl:value-of select="Data"/></Quantity>
  </xsl:template>

  <xsl:template match="Cell[NamedCell[@ss:Name='Price']]">
    <Price><xsl:value-of select="Data"/></Price>
  </xsl:template>

</xsl:stylesheet>
				
XSL ???????????? XMLSS ?? ????? ???? XML (??? 1 ??? illustrated) ??????????? ??:
  • XSL ?????? ID ??? ????????? ?? ?? ??? <cell>??? ?? ?????? <namedcell>?? <data>?????? ????? ????? ?? ?? traversing ?? ????? ??? ???? ???? ?? ?? ?? <namedcell>?? ss:Name ??????? 'Customer_ID' ?? ?? ?? ?? ????? ?? <data>ss:Type ??????? ?? '??????' ?? ????? ???? ??.</data></namedcell></data></namedcell></cell>
  • ???? ?? ??? ???? ?? ?????? ?????, ?? ??? XSL ?????? ??? <row>?? ??? ????? ?? <cell>??? ??? ??? <namedcell>'Product_ID' ?? ?? ss:Name ??????? ?? ??? ?????? ??? ?? ?? <data>'??????' ???? ?? ?? ss:Type ??????? ?? ??? ?????? ???.</data></namedcell></cell></row>
  • ???????? ?? ??? <row>???? ?? XSL ????????? ???? ??, ???? ?? ??? XSL ???? ???????? ?? ??? ???? ?? ??? <namedcell>????? 'Product_ID', '??????' ?? '?????' ?? ?? ss:Name ??????? ?? ??.</namedcell></row>

??? 4: ??? ??? ??? ???? ??? ??????? ???? ?? ??? ????? ??? ?? ?????

  1. Excel ??? Invoice1.xls ?? ??????
  2. ???? ????????? ?? ???????? ???? ?? ??? ?????? ?? ???? ????? ?? ?? ??? ??? ?????? ??????? ?? ??? Sheet1 ??????:
    A1:   Customer ID    B1:   BOTTM        C1:
    A2:                  B2:                C2:
    A3:   Product ID     B3:   Quantity     C3:   Price
    A4:   4              B4:   11           C4:   20.25
    A5:   18             B5:   2            C5:   63.70
    					
    ???:: ?? ????? ??? ?? ?? ???? ?? ??????????? ??????? ???? ?? ??? ????? ?????? ?? ?????? ID ?? ASP ????????? ?? ??? ???? ???? ?????

  3. ????? ????????????? ??,???????? ????? ??????????. ????????ProcessOrder????? ???? ?? ???? ??? ???????????.
  4. ??? ASP ????????? ??????????? ???? ??????? ????, ?? ???? ?? ????? ??????? ?? ???? ?????? ?? ???? Microsoft Access ??? ??????? ???? ?? ????????? ????? ??????? ?????? ASP ?? ??? ?? ???? ID ?? ??? ?? ???? ?????? ??? ???? ?? ????????? ????? ???? ?????? ?? ?? ??? ???? ????? ?????? ??? ?? ?? ???????????? ???? ???? ID. ?? ??? ????? ?? ???? ??????, ?? Excel ??? Invoice1.xls ?? ??????
  5. ??? B1 ??? ?????? ID ????? ????, ?? ?????? ?? ??? ?? ?????? ?? ??? ????? ???? ??? ??????? ???? ??? ?????? ?? ?? ??? ???? ?????? ????? ?? ?????? ID ???? ???? ??? ?? ???? ???

??????? ????

ExcelXML.exe ??? ????? ?? ????? ????? ?? ???? ?? ??? ??? ?? ??? ?? ???? Invoice1.xls ?? ??? ??????? ????????? ??? ??? Invoice2.xls ???? ASP ????????? ?? XSL stylesheet Invoice1.xls ?? ??? ??? ????? ???? ??? ????????? ???? ??, ??????? Invoice2.xls ?? ???????? Excel ????????, ???? ?? ?????????? ???????, ???? ??????? ?? VLOOKUP ?????, ????? ????? ?? ??? ???? ???????? ???? ??? ?????? ???? ?? ??? ?? ???? ????

????? ????? Microsoft ??????? ?????? ?? ??????? ?? ??? ?????? ??::
Excelxml.exe
?????? ??????: ?????? 3, 2001

Microsoft ?????? ??????? ?? ???? ??????? ???? ?? ???? ??? ???? ????? ?? ???, ????? ???? ?????? ?? ????? ?? ???? ?? Microsoft ???????? ??? ?????::
119591?????? ?????? ?? Microsoft ?????? ??????? ???? ??????? ????
Microsoft ????? ?? ??? ?? ????? ?? ????? ?? ??? Microsoft ?? ?? ?????? ?? ?????? ???? ??????? ?????-??? ?????????? ?? ????? ???? ?? ????? ????? ?? ?? ??.. ????? ?? ?? ???????-??????????? ????? ?? ???????? ???? ??? ?? ?? ????? ??? ???? ?? ??????? ???????? ?? ????? ??? ??? ???? ??..

??????

???????? ??????? ?? ??? ?????-???? ?????? ????? ?? ??? XML ?? ASP ?? ????? ???? ??, ????? Microsoft ?????? ??????? (MSDN) ??? ???? ?????:
XML ?? ?????
HTTP://www.Microsoft.com/learning/en/us/syllabi/2500AFinal.mspx

XML ??????????
HTTP://MSDN.Microsoft.com/en-us/library/ms950712.aspx

XSL ?????? ?? ????????????
HTTP://MSDN.Microsoft.com/en-us/library/ms862738.aspx

??????? ?? ????? ?? ??? XML ??? ??? ??
HTTP://MSDN.Microsoft.com/en-us/library/ms763733.aspx

???????? ??????? ?? ??? ?????? ?? Microsoft ???????? ??? ????? ?? ??? ????? ???? ???????? ?? ????? ????:
288215?????: Excel 2002 ?? XML
285891Excel 2002 ?? ??? ?? XML ?????????? ????? ?? ??? Visual Basic ?? ASP ?? ????? ???? ????
288130???????-???? ???????? ?? ??? XML ?????????? ?? ????? ???? ?? ??? ASP ?? ????? ???? ????
(?) Microsoft Corporation 2001, ??? ?????? ????????? ???????? Lori B. Turner, Microsoft Corporation ?????? ???

???

???? ID: 278976 - ????? ???????: 23 ??????? 2011 - ??????: 3.0
???? ???? ???? ??:
  • Microsoft Visual Basic 6.0 Professional Edition
  • Microsoft Excel 2002 Standard Edition
??????: 
kbdownload kbfile kbhowto kbmt KB278976 KbMthi
???? ?????? ????????
??????????: ?? ???? ?? ???? ??????? ?? ????? ?? Microsoft ????-?????? ?????????? ?????? ?????? ???? ??? ??. Microsoft ???? ??? ????-???????? ?? ????-???????? ????? ?????? ?? ???? ???????? ???? ?? ???? ????? ????? ??? ?? ??? ?????? ?? ???? ???? ???? ??? ????? ??. ???????, ????-???????? ???? ????? ???? ???? ???? ???. ?????, ????????, ?????-???? ?? ??????? ?? ???????? ?? ???? ???, ???? ?? ??? ?????? ???? ???? ??? ????? ??? ?? ???? ??. Microsoft ??????? ??? ???? ?? ?????? ?? ??????????, ????????? ?? ??? ?????? ?? ???? ????? ?? ???? ???????? ?? ??? ???? ????? ?? ??? ????????? ???? ??. Microsoft ????-?????? ?????????? ?? ????? ?????? ?? ?? ??? ??.
?????????? ?? ??????? ????????? ??????? ??:278976

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

 

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