?????? ??????This article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.
????? ??? ??? Microsoft SQL Server ???? ???? ?? ????? ???? ?? ????????? ????? ???????? ????? ????? ?? ?? ?? ???? ????? ?? ??? ????????? ?? ???? ??????? ???????? (http://localhost) ?? ??????? ????? ??? ?? ????? ???? ???? ?? ???? ??? ??? ????? ?? ????? ???? ??????? ???????? ??, ???????????? ?????in the URLs of the sample with the name of
your Web server.
Generate the DataSet
In this section, you create the HTTP handler and the client-side
components that are needed to retrieve order information. TheDataSetis returned to the main frame as plain XML; theDataSetis not transformed.
Create an empty Web project named ExcelTransform. ??? ???? ?? ???, ????? ????? ?? ???? ????::
Imports System.Web
Imports System.Xml
Imports System.Xml.Xsl
Imports System.Data
Imports System.Data.SqlClient
Public Class GetData
Implements IHttpHandler
Private sConn As String = _
"User ID=<username>;Password=<strong password>;Initial Catalog=Northwind;Data Source=YourSQLServer;"
Public ReadOnly Property IsReusable() As Boolean _
Implements IHttpHandler.IsReusable
Get
Return False
End Get
End Property
Public Sub ProcessRequest(ByVal context As HttpContext) _
Implements IHttpHandler.ProcessRequest
Dim conn As SqlConnection
Dim sOrderRequested As String
sOrderRequested = context.Request.Item("OrderID")
If Not (sOrderRequested > "") Then
'=== If no order is requested, assume that this is a request
'=== to fill the drop-down list in the Header.htm template
'=== with the list of OrderIDs.
'Get a DataSet for a list of OrderIDs.
Dim sSQL As String = "Select OrderID from Orders"
conn = New SqlConnection(sConn)
conn.Open()
Dim cmd As New SqlCommand(sSQL, conn)
Dim rdr As SqlDataReader = cmd.ExecuteReader
'Open the header template for the frameset and fill
'in the <option> child nodes for the drop-down lists.
Dim sHTML As String, sOrderID As String
Dim xmlDoc As New XmlDocument()
xmlDoc.Load(context.Server.MapPath("header.htm"))
Dim oElem As XmlElement = _
xmlDoc.DocumentElement.GetElementsByTagName("select").Item(0)
Dim oChild As XmlElement
Do While rdr.Read
sOrderID = rdr.GetInt32(0).ToString
oChild = xmlDoc.CreateElement("option")
oChild.SetAttribute("value", sOrderID)
oChild.InnerText = sOrderID
oElem.AppendChild(oChild)
Loop
rdr.Close()
conn.Close()
'Return the modified header template.
context.Response.Write(xmlDoc.InnerXml)
Else
'=== If an order is requested, create a DataSet for that
'=== order and return the results to the client browser.
'Build a DataSet for the order.
conn = New SqlConnection(sConn)
conn.Open()
Dim ds As DataSet = New DataSet("Order")
Dim CustDa As SqlDataAdapter = New SqlDataAdapter( _
"SELECT OrderID, CompanyName, Address, City, Region, PostalCode, Country, Freight " & _
"FROM Customers " & _
"INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID " & _
"WHERE (((Orders.OrderID)=" & sOrderRequested & "))", conn)
CustDa.Fill(ds, "Customer")
Dim ItemsDa As SqlDataAdapter = New SqlDataAdapter( _
"SELECT Products.ProductName, [Order Details].Quantity, " & _
" [Order Details].[UnitPrice]*[Quantity]*(1-[Discount]) AS ItemTotal " & _
"FROM Products INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID " & _
"WHERE (([Order Details].[OrderID]) = " & sOrderRequested & ")", conn)
ItemsDa.Fill(ds, "Items")
conn.Close()
SendResults(context, ds)
context.Response.End()
End If
End Sub
Private Sub SendResults(ByVal context As HttpContext, ByVal ds As DataSet)
'Write the XML for the DataSet.
context.Response.ContentType = "text/xml"
context.Response.Output.Write(ds.GetXml)
context.Response.End()
End Sub
End Class
Private Sub SendResults(ByVal context As HttpContext, ByVal ds As DataSet)
Dim sOrderID As String = ds.Tables(0).Rows(0).Item(0)
'Set up the response for Excel.
context.Response.ContentType = "application/vnd.ms-excel"
context.Response.Charset = ""
'Transform the DataSet XML using transform.xslt
'and return the results to the client in Response.Outputstream.
Dim tw As XmlTextWriter
Dim xmlDoc As XmlDataDocument = New XmlDataDocument(ds)
Dim xslTran As XslTransform = New XslTransform()
xslTran.Load(context.Server.MapPath("transform.xslt"))
xslTran.Transform(xmlDoc, Nothing, context.Response.OutputStream)
context.Response.End()
End Sub
Private Sub SendResults(ByVal context As HttpContext, ByVal ds As DataSet)
Dim sOrderID As String = ds.Tables(0).Rows(0).Item(0)
'First, save the XML representation of the DataSet in a file
'and add a processing instruction to the XML so that it can be
'transformed client-side.
Dim tw As XmlTextWriter
tw = New XmlTextWriter(context.Server.MapPath("order" & sOrderID & ".xml"), System.Text.Encoding.UTF8)
tw.Formatting = Formatting.Indented
tw.Indentation = 3
tw.WriteStartDocument()
tw.WriteProcessingInstruction("xml-stylesheet", _
"type='text/xsl' href='http://localhost/ExcelTransform/transform.xslt'")
ds.WriteXml(tw)
tw.Close()
'Second, transform the DataSet XML and save it to a file.
Dim xmlDoc As XmlDataDocument = New XmlDataDocument(ds)
Dim xslTran As XslTransform = New XslTransform()
xslTran.Load(context.Server.MapPath("transform.xslt"))
tw = New XmlTextWriter(context.Server.MapPath("order" & sOrderID & ".xls"), System.Text.Encoding.UTF8)
tw.Formatting = Formatting.Indented
tw.Indentation = 3
tw.WriteStartDocument()
xslTran.Transform(xmlDoc, Nothing, tw)
tw.Close()
'Optionally, redirect to the saved transformation.
context.Response.Redirect( _
"http://localhost/ExcelTransform/order" & sOrderID & ".xls")
context.Response.End()
End Sub
When you build your own XSLT file for Excel, first create a
workbook template in Excel that contains the formatting and formulas that you
need, and then save the workbook in Spreadsheet XML. You can then modify the
XML so that it contains the XSL expressions and elements that you need to
correctly transform yourDataSetXML. When you modify the XML that you saved from Excel, note the
following:
The cells in a worksheet are represented by a<table></table>element in XML. The<table></table>has two attributes,ss:ExpandedColumnCount, ??ss:ExpandedRowCount, ?? ????????? ??? ?????? ?? ?????????? ??? ????? ??? ???? ??? (??????, "????? ??????")? ??? ????????????? ??? ????????? ?? ????? (?? ?????) ?? ?? ??? ?????? ??, ???????? ?? ????? ?? ????????? ???? ?? ???ss:ExpandedRowCount??? ???????transform.XSLT??? ?? ?????? ?? ??? ??? ????? ?? ??????DataSetXML.
Windows Live
Facebook
Twitter
Linkedin
Digg it
Yahoo
Delicious
StumbleUpon
Yammer
Reddit
Technorati
FriendFeed
Email
???? ?????? ????????
????? ?? ???? ????
