 |
 |
 |
 |
 |
| |||||||||||||||||||||||||
|
Transformation of a target stream of the XML-given at a conclusion in a browser means ASP.NET and XSLT.
Given clause{article} displaces accent{stress} from job ASP.NET and MSSQL on use of language of patterns XSLT, but in the given sheaf the given technologies represent powerful and floppy means of data presentation. Imposing of patterns allows (and that will be shown in given clause{article}) to transform a stream in table HTML to transform to a Excel-file, and to open it{him} in a browser.
Small endurance{quotation} from last clause{article}, for logical continuation of a material. We have code which deduces in a browser the XML-document (primer.aspx), we shall a little change structure of fields, for brevity a conclusion.
<% Page Language = "VB" %>
<% import Namespace = " Microsoft. Data. SqlXML " %>
<script runat = "server">
Sub Page_Load (sender As Object, e As EventArgs)
Dim cmd as SqlXmlCommand
cmd = New SqlXmlCommand (" Provider=SQLOLEDB; server = (local); database=pubs; uid=sa; password=sa ")
cmd. CommandText = " SELECT Title, Price, Ytd_sales FROM Titles FOR XML AUTO "
Response. ContentType = "text/xml"
cmd. RootTag = "root"
Response. Clear ()
cmd. ExecuteToStream (Response. OutputStream)
End SUb
</script>
As a result of performance of search it is received:
<? xml version = " 1.0" encoding = "utf-8"?>
<root>
<Titles Title = " The Busy Executive's Database Guide " Price = " 19.99" Ytd_sales = "4095"/>
<Titles Title = " Cooking with Computers: Surreptitious Balance Sheets " Price = " 11.95" Ytd_sales = "3876"/>
<Titles Title = " You Can Combat Computer Stress! " Price = " 2.99" Ytd_sales = "18722"/>
<Titles Title = " Straight Talk About Computers " Price = " 19.99" Ytd_sales = "4095"/>
<Titles Title = " Silicon Valley Gastronomic Treats " Price = " 19.99" Ytd_sales = "2032"/>
<Titles Title = " The Gourmet Microwave " Price = " 2.99" Ytd_sales = "22246"/>
<Titles Title = " The Psychology of Computer Cooking "/>
<Titles Title = " But Is It User Friendly? " Price = " 22.95" Ytd_sales = "8780"/>
<Titles Title = " Secrets of Silicon Valley " Price = "20" Ytd_sales = "4095"/>
<Titles Title = " Net Etiquette "/>
<Titles Title = " Computer Phobic AND Non-Phobic Individuals: Behavior Variations " Price = " 21.59" Ytd_sales = "375"/>
<Titles Title = " Is Anger the Enemy? " Price = " 10.95" Ytd_sales = "2045"/>
<Titles Title = " Life Without Fear " Price = "7" Ytd_sales = "111"/>
<Titles Title = " Prolonged Data Deprivation: Four Case Studies " Price = " 19.99" Ytd_sales = "4072"/>
<Titles Title = " Emotional Security: A New Algorithm " Price = " 7.99" Ytd_sales = "3336"/>
<Titles Title = " Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean " Price = " 20.95" Ytd_sales = "375"/>
<Titles Title = " Fifty Years in Buckingham Palace Kitchens " Price = " 11.95" Ytd_sales = "15096"/>
<Titles Title = " Sushi, Anyone? " Price = " 14.99" Ytd_sales = "4095"/>
</root>
Let's write the following text, and we shall save it{him} in a file primer.xsl
<? xml version = " 1.0" encoding = "WINDOWS-1251"?>
<xsl:stylesheet xmlns:xsl = " http: // www.w3.org/1999/XSL/Transform "
xmlns:ms = "urn:schemas-microsoft-com:xslt"
version = " 1.0">
<xsl:template match = "/">
<Table border = "1" cellpadding = "0" id = "fxTable" style = " font: 8pt verdana ">
<! - we Print names stolbcov - we take from the first line->
<xsl:for-each select = " // * [1] / * ">
<! - we Deduce{Remove} the name attributa, i.e. the name of a column->
<th> <xsl:value-of select = " name () "/> </th>
</xsl:for-each>
<! - we Use a pattern for all elements of the document->
<xsl:apply-templates select = " // * "/>
</Table>
</xsl:template>
<xsl:template match = " // * ">
<! - If the site has the parent, i.e. the root element-> is cut
<xsl:if test = " parent:: * ">
<! - we Deduce{Remove} a line of the table->
<tr>
<! - It is touched in line all attributy and it is deduced{removed} their values->
<xsl:for-each select = " * ">
<td> <xsl:value-of select = "."/> </td>
</xsl:for-each>
</tr>
</xsl:if>
</xsl:template>
</xsl:stylesheet>
In a file primer.aspx we shall add the instruction
cmd. XslPath = Server. MapPath ("primer.xsl")
And zakommentarim line Response. ContentType = "text/xml", by default the stream will be is deduced in format HTML.Na the screen seen the following table
Title Price Ytd_sales
The Busy Executive's Database Guide 19.99 4095
Cooking with Computers: Surreptitious Balance Sheets 11.95 3876
You Can Combat Computer Stress! 2.99 18722
Straight Talk About Computers 19.99 4095
Silicon Valley Gastronomic Treats 19.99 2032
The Gourmet Microwave 2.99 22246
The Psychology of Computer Cooking
But Is It User Friendly? 22.95 8780
Secrets of Silicon Valley 20 4095
Net Etiquette
Computer Phobic AND Non-Phobic Individuals: Behavior Variations 21.59 375
Is Anger the Enemy? 10.95 2045
Life Without Fear 7 111
Prolonged Data Deprivation: Four Case Studies 19.99 4072
Emotional Security: A New Algorithm 7.99 3336
Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean 20.95 375
Fifty Years in Buckingham Palace Kitchens 11.95 15096
Sushi, Anyone? 14.99 4095
Primer.xsl - a universal pattern, which transforms the XML-given (at mode AUTO) into the table, with names stolbcov equal to names of fields. In general, in itself an example not so valuable. Sheaf DataSource and DataGrid do{make} all too most. Value XML is shown at non-standard situations. We will address for example from previous clause{article} where there is a conclusion of publications in a cut{section} of Publishers.
<? xml version = " 1.0" encoding = "utf-8"?>
<root>
<the Publisher Number{Room} = "0736">
<the Book the Name = " Emotional Security: A New Algorithm " the Price = " 7.99" Sales = "3336"/>
<the Book the Name = " Is Anger the Enemy? " The price = " 10.95" Sales = "2045"/>
<the Book the Name = " Life Without Fear " the Price = "7" Sales = "111"/>
<the Book the Name = " Prolonged Data Deprivation: Four Case Studies " the Price = " 19.99" Sales = "4072"/>
<the Book the Name = " You Can Combat Computer Stress! " The price = " 2.99" Sales = "18722"/>
</publisher>
<the Publisher Number{Room} = "0877">
<the Book the Name = " Computer Phobic AND Non-Phobic Individuals: Behavior Variations " the Price = " 21.59" Sales = "375"/>
<the Book the Name = " Fifty Years in Buckingham Palace Kitchens " the Price = " 11.95" Sales = "15096"/>
<the Book the Name = " Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean " the Price = " 20.95" Sales = "375"/>
<the Book the Name = " Silicon Valley Gastronomic Treats " the Price = " 19.99" Sales = "2032"/>
<the Book the Name = " Sushi, Anyone? " The price = " 14.99" Sales = "4095"/>
<the Book the Name = " The Gourmet Microwave " the Price = " 2.99" Sales = "22246"/>
<the Book the Name = " The Psychology of Computer Cooking "/>
</publisher>
<the Publisher Number{Room} = "1389">
<the Book the Name = " But Is It User Friendly? " The price = " 22.95" Sales = "8780"/>
<the Book the Name = " Cooking with Computers: Surreptitious Balance Sheets " the Price = " 11.95" Sales = "3876"/>
<the Book the Name = " Net Etiquette "/>
<the Book the Name = " Secrets of Silicon Valley " the Price = "20" Sales = "4095"/>
<the Book the Name = " Straight Talk About Computers " the Price = " 19.99" Sales = "4095"/>
<the Book the Name = " The Busy Executive's Database Guide " the Price = " 19.99" Sales = "4095"/>
</publisher>
</root>
If the management{manual} will want to see the information on each publisher in the separate table at once there is a question - quantity{amount} of publishers (tables) rigidly is not determined. It can vary. Certainly, it is possible to add dynamically DataGrid'?, but on XML it is easier also than a code less. By the way, let's deduce{remove} this the information in HTML-table.s a pattern.
<? xml version = " 1.0" encoding = "WINDOWS-1251"?>
<xsl:stylesheet xmlns:xsl = " http: // www.w3.org/1999/XSL/Transform "
xmlns:ms = "urn:schemas-microsoft-com:xslt"
version = " 1.0">
<xsl:template match = "/">
<xsl:for-each select = " // the Publisher ">
<h2>
<xsl:value-of select = " Number{Room} "/>
</h2>
<Table border = "1" cellpadding = "3" id = "fxTable" style = " font: 8pt verdana ">
<xsl:for-each select = " * [1] / * ">
<! - we Deduce{Remove} the name attributa, i.e. the name of a column->
<th> <xsl:value-of select = " name () "/> </th>
</xsl:for-each>
<xsl:apply-templates select = "Book"/>
</Table>
<br/>
</xsl:for-each>
</xsl:template>
<xsl:template match = "Book">
<tr>
<! - It is touched in line all attributy and it is deduced{removed} their values->
<xsl:for-each select = " * ">
<td> <xsl:value-of select = "."/> </td>
</xsl:for-each>
</tr>
</xsl:template>
</xsl:stylesheet>
Now at occurrence of the new publisher, it is not necessary to alter neither a code, nor a pattern. The new table with informaciej.0736 will automatically appear
The name The price Sales
Emotional Security: A New Algorithm 7.99 3336
Is Anger the Enemy? 10.95 2045
Life Without Fear 7 111
Prolonged Data Deprivation: Four Case Studies 19.99 4072
You Can Combat Computer Stress! 2.99 18722
0877
The name The price Sales
Computer Phobic AND Non-Phobic Individuals: Behavior Variations 21.59 375
Fifty Years in Buckingham Palace Kitchens 11.95 15096
Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean 20.95 375
Silicon Valley Gastronomic Treats 19.99 2032
Sushi, Anyone? 14.99 4095
The Gourmet Microwave 2.99 22246
The Psychology of Computer Cooking
1389
The name The price Sales
But Is It User Friendly? 22.95 8780
Cooking with Computers: Surreptitious Balance Sheets 11.95 3876
Net Etiquette
Secrets of Silicon Valley 20 4095
Straight Talk About Computers 19.99 4095
The Busy Executive's Database Guide 19.99 4095
Very much many ASP.NET-programmers the question of data presentation to the client as the Excel-document interests. It enables the client of the further manipulation the data independently, without participation of the programmer.
How to transform a XML-stream into the XML-document which is distinguished by a browser as the Excel-table, we shall show by the example of the same search in the beginning of this clause{article}.
At once I shall say, that many questions can be solved, having done elementary actions. In Excel to format appearance of the document (a font, color, frameworks of cells and so forth), then to save in XML a kind, and to analyse the text as as occurs.
In a file primer.aspx we shall replace line Response. ContentType = "text/xml" on Response. ContentType = "application/vnd.ms-excel"
The following pattern will transform our data in a browser into the Excel-table
<? xml version = " 1.0" encoding = "utf-8"?>
<xsl:stylesheet version = " 1.0" xmlns:xsl = " http: // www.w3.org/1999/XSL/Transform "
xmlns:html = " http: // www.w3.org/TR/REC-html40 "
xmlns = "urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o = "urn:schemas-microsoft-com:office:office"
xmlns:x = "urn:schemas-microsoft-com:office:excel"
xmlns:ss = "urn:schemas-microsoft-com:office:spreadsheet">
<xsl:template match = "/">
<xsl:processing-instruction name = ">
<xsl:text> progid = " Excel. Sheet " </xsl:text>
</xsl:processing-instruction>
<Workbook>
<Worksheet ss:Name = " Sheet 1 ">
<Table>
<Row>
<xsl:for-each select = " // * [1] / * ">
<Cell>
<Data ss:Type = "String"> <xsl:value-of select = " name () "/> </Data>
</Cell>
</xsl:for-each>
</Row>
<xsl:apply-templates select = " // * "/>
</Table>
</Worksheet>
</Workbook>
</xsl:template>
<xsl:template match = " // * ">
<xsl:if test = " parent:: * ">
<Row>
<xsl:for-each select = " * ">
<Cell> <Data ss:Type = "String"> <xsl:value-of select = "."/> </Data> </Cell>
</xsl:for-each>
</Row>
</xsl:if>
</xsl:template>
</xsl:stylesheet>
The given pattern has some lacks. The first, in him is impossible to insert comments (a kind <!-lja-?n->) and the second, all data are deduced in a text kind, i.e. cells where the numerical information is deduced, will be formatted as the text. What to correct this lack, it is necessary to change algorithm, and to become attached to the analysis of names of attributes. Universality is lost, but the data are deduced according to their type.
<? xml version = " 1.0" encoding = "utf-8"?>
<xsl:stylesheet version = " 1.0" xmlns:xsl = " http: // www.w3.org/1999/XSL/Transform "
xmlns:html = " http: // www.w3.org/TR/REC-html40 "
xmlns = "urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o = "urn:schemas-microsoft-com:office:office"
xmlns:x = "urn:schemas-microsoft-com:office:excel"
xmlns:ms = "urn:schemas-microsoft-com:xslt"
xmlns:ss = "urn:schemas-microsoft-com:office:spreadsheet">
<xsl:template match = "/">
<xsl:processing-instruction name = ">
<xsl:text> progid = " Excel. Sheet " </xsl:text>
</xsl:processing-instruction>
<Workbook>
<Styles>
<Style ss:ID = "s22"> <NumberFormat ss:Format = " Short Date "/> </Style>
<Style ss:ID = "s23"> <NumberFormat ss:Format = "Standard"/> </Style>
</Styles>
<Worksheet ss:Name = " Sheet 1 ">
<Table>
<Row>
<xsl:for-each select = " // * [1] / * ">
<Cell>
<Data ss:Type = "String">
<xsl:value-of select = " name () "/>
</Data>
</Cell>
</xsl:for-each>
</Row>
<xsl:apply-templates select = " // * "/>
</Table>
</Worksheet>
</Workbook>
</xsl:template>
<xsl:template match = " // * ">
<xsl:if test = " parent:: * ">
<Row>
<xsl:for-each select = " * ">
<xsl:choose>
<xsl:when test = " name () = 'pub_id' or
name () = 'advance' or
name () = 'royalty' or
name () = ' ytd_sales' ">
<Cell> <Data ss:Type = "Number"> <xsl:value-of select = "."/> </Data> </Cell>
</xsl:when>
<xsl:when test = " name () = 'price' ">
<Cell ss:StyleID = "s23"> <Data ss:Type = "Number"> <xsl:value-of select = '.'/> </Data> </Cell>
</xsl:when>
<xsl:when test = " name () = 'pubdate' ">
<Cell ss:StyleID = "s22"> <Data ss:Type = "DateTime"> <xsl:value-of select = "."/> </Data> </Cell>
</xsl:when>
<xsl:otherwise>
<Cell> <Data ss:Type = "String"> <xsl:value-of select = "."/> </Data> </Cell>
</xsl:otherwise>
</xsl:choose>
</xsl:for-each>
</Row>
</xsl:if>
</xsl:template>
</xsl:stylesheet>
In this pattern formatting cells is applied, styles are defined{determined} in section <Styles>. All subtleties of application of styles of formatting can be found on the link http: // msdn.microsoft.com/library/default.asp? url =/library/en-us/dnexcl2k2/html/odc_xmlss.asp or with the help of preservation of the table with the formatted cells as the XML-document, and the further search of corresponding style.
In general this all technology of transformation of a XML-stream or HTML, or in a Excel-file. All further perfection will touch only appearance (i.e. application of styles of formatting) or processings of the XML-given, and it already directly deduces on studying of language XSLT.
In conclusion of clause{article} I shall result coordinates of the book on the given language. The author, one of the strongest experts in this area (processing XML).
Michael Kej, XSLT. Spavochnik the programmer. Per. About English SPb:Simvol-PLUS, 2002.-1016 with., silt. ISBN 5-93286-039-1
Under this link http: // msdn.microsoft.com/library/default.asp? url =/library/en-us/odc_xl2003_ta/html/odc_XLxmlhowto_.asp the theory and examples of sheaf ASP.old + XML = the Excel-table lays.
http: // msdn.microsoft.com/library/default.asp? url =/library/en-us/dnexcl2k2/html/odc_xmlss.asp - the directory on SpeadSheet-to elements.
