Databases
StackOverflow2013
Postsdbo
POASP.NET: How to convert schema to Excel xsl
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POASP.NET: How to convert schema to Excel xsl
    primarykey
    data
    text
    <p>I have the following xml schema:</p> <pre><code>&lt;?xml version="1.0" encoding="utf-8"?&gt; &lt;xsd:schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsd="http://www.w3.org/2001/XMLSchema" attributeFormDefault="unqualified" elementFormDefault="qualified" version="1.0"&gt; &lt;xs:element name="PercentTimeReport"&gt; &lt;xs:complexType&gt; &lt;xs:sequence&gt; &lt;xs:element name="ParamStartDate" type="xs:dateTime" /&gt; &lt;xs:element name="ParamEndDate" type="xs:dateTime" /&gt; &lt;xs:element name="ParamQuarterInt" type="xs:unsignedByte" /&gt; &lt;xs:element name="ParamProjID" nillable="true" /&gt; &lt;xs:element name="ParamStaffID" nillable="true" /&gt; &lt;xs:element name="ParamPercentRange" type="xs:unsignedByte" /&gt; &lt;xs:element name="Items"&gt; &lt;xs:complexType&gt; &lt;xs:sequence&gt; &lt;xs:element maxOccurs="unbounded" name="Item"&gt; &lt;xs:complexType&gt; &lt;xs:sequence&gt; &lt;xs:element name="ID" type="xs:unsignedShort" /&gt; &lt;xs:element name="EmployeeName" type="xs:string" /&gt; &lt;xs:element name="StaffID" type="xs:unsignedShort" /&gt; &lt;xs:element name="Status" type="xs:string" /&gt; &lt;xs:element name="Date" type="xs:dateTime" /&gt; &lt;xs:element name="Department" type="xs:string" /&gt; &lt;xs:element name="DepartmentCode" type="xs:string" /&gt; &lt;xs:element name="Project" type="xs:string" /&gt; &lt;xs:element name="ProjectID" type="xs:unsignedByte" /&gt; &lt;xs:element name="Hours" type="xs:unsignedByte" /&gt; &lt;xs:element name="HoursPerWeek" type="xs:decimal" /&gt; &lt;xs:element name="PercentTime" type="xs:decimal" /&gt; &lt;xs:element name="ActualContact" type="xs:boolean" /&gt; &lt;xs:element name="Body" type="xs:string" /&gt; &lt;xs:element name="Issue" type="xs:string" /&gt; &lt;xs:element name="Activity" type="xs:string" /&gt; &lt;/xs:sequence&gt; &lt;/xs:complexType&gt; &lt;/xs:element&gt; &lt;/xs:sequence&gt; &lt;/xs:complexType&gt; &lt;/xs:element&gt; &lt;/xs:sequence&gt; &lt;/xs:complexType&gt; &lt;/xs:element&gt; &lt;/xsd:schema&gt; </code></pre> <p>and I'd like to generate an excel file that only shows the Items table in an ASP.NET 2.0 Web application. I really don't understand this <a href="http://aspalliance.com/471_Convert_XML_To_an_Excel_Spreadsheet_Using_XSL.all" rel="nofollow noreferrer">code that I found</a> (see below), or XSLT enough to get the output format that I need. Does anyone know XSLT, that could tell me how to modify the XSLT below to:</p> <p>1) Hide the elements other than Items (e.g. ParamStartDate, ParamEndDate, etc.).</p> <p>2) Output the table with the nested "Items" complex element.</p> <p>Currently, the xsl below produces each of the elements as column headers, and the Items cell contains all the items on one row. I essentially need to go one level deeper.</p> <p></p> <pre><code>&lt;xsl:template match="/"&gt; &lt;Workbook 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" xmlns:html="http://www.w3.org/TR/REC-html40"&gt; &lt;xsl:apply-templates/&gt; &lt;/Workbook&gt; &lt;/xsl:template&gt; &lt;xsl:template match="/*"&gt; &lt;Worksheet&gt; &lt;xsl:attribute name="ss:Name"&gt; &lt;xsl:value-of select="local-name(/*/*)"/&gt; &lt;/xsl:attribute&gt; &lt;Table x:FullColumns="1" x:FullRows="1"&gt; &lt;Row&gt; &lt;xsl:for-each select="*[position() = 1]/*"&gt; &lt;Cell&gt; &lt;Data ss:Type="String"&gt; &lt;xsl:value-of select="local-name()"/&gt; &lt;/Data&gt; &lt;/Cell&gt; &lt;/xsl:for-each&gt; &lt;/Row&gt; &lt;xsl:apply-templates/&gt; &lt;/Table&gt; &lt;/Worksheet&gt; &lt;/xsl:template&gt; &lt;xsl:template match="/*/*"&gt; &lt;Row&gt; &lt;xsl:apply-templates/&gt; &lt;/Row&gt; &lt;/xsl:template&gt; &lt;xsl:template match="/*/*/*"&gt; &lt;Cell&gt; &lt;Data ss:Type="String"&gt; &lt;xsl:value-of select="."/&gt; &lt;/Data&gt; &lt;/Cell&gt; &lt;/xsl:template&gt; </code></pre> <p> </p> <p>Desired Excel Output (Only show Items complexType as Table):</p> <pre> ID EmployeeName StaffID .... Issue Activity 1 John Smith 231 .... text text 2 Kate Henderson 101 .... text2 text3 . .... . .... N .... </pre> <p>Current Output:</p> <pre> ParamStartDate ParamEndDate .... ParamPercentRange Items '01/01/2010' '04/01/2010' .... 6 '1John Smith231...texttext....' </pre> <p>As always, any help greatly appreciated.</p> <p>Thank You</p> <p>P.S. Alejandro, here's the output with your changes:</p> <pre><code>&lt;?xml version="1.0" encoding="utf-8"?&gt; &lt;Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40"&gt; &lt;Worksheet ss:Name="PercentTimeReport"&gt; &lt;Table x:FullColumns="1" x:FullRows="1"&gt; &lt;Row /&gt; &lt;/Table&gt; &lt;/Worksheet&gt; &lt;/Workbook&gt; </code></pre> <p>Here is a sample of my original output:</p> <pre><code>&lt;?xml version="1.0" encoding="utf-8"?&gt; &lt;Workbook 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" xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:user="urn:my-scripts"&gt; &lt;Worksheet ss:Name="PercentTimeReport"&gt; &lt;Table x:FullColumns="1" x:FullRows="1"&gt; &lt;Row&gt; &lt;Cell&gt;&lt;Data ss:Type="String"&gt;ParamStartDate&lt;/Data&gt;&lt;/Cell&gt;&lt;Cell&gt;&lt;Data ss:Type="String"&gt;ParamEndDate&lt;/Data&gt;&lt;/Cell&gt;&lt;Cell&gt;&lt;Data ss:Type="String"&gt;ParamQuarterInt&lt;/Data&gt;&lt;/Cell&gt;&lt;Cell&gt;&lt;Data ss:Type="String"&gt;ParamPercentRange&lt;/Data&gt;&lt;/Cell&gt;&lt;Cell&gt;&lt;Data ss:Type="String"&gt;Items&lt;/Data&gt;&lt;/Cell&gt; &lt;/Row&gt; &lt;Row&gt; &lt;Cell&gt;&lt;Data ss:Type="String"&gt;2010-07-01T00:00:00&lt;/Data&gt;&lt;/Cell&gt;&lt;Cell&gt;&lt;Data ss:Type="String"&gt;2010-09-30T00:00:00&lt;/Data&gt;&lt;/Cell&gt;....&lt;/Cell&gt; &lt;/Row&gt; &lt;/Table&gt; &lt;/Worksheet&gt; &lt;/Workbook&gt; </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSephrial
    1. USSephrial
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COPlease note that you need to indent code by 4 spaces (e.g. by clicking on the corresponding toolbar button). Otherwise, XML code won't be visible.
      singulars
      1. POASP.NET: How to convert schema to Excel xsl
      1. USDirk Vollmar
    2. COSure, I just need to produce an excel file with a single sheet containing the following columns: ID, EmployeeName, StaffID, Status, Date, Department, DepartmentCode, Project, ProjectID, Hours, TotalHours, HoursPerWeek, PercentTime, ActualContactCount, ActualContactFlag, Body, Issue, Activity.
      singulars
      1. POASP.NET: How to convert schema to Excel xsl
      1. USSephrial
    3. COCurrently it creates a table with columns: ParamStartDate, ParamEndDate, ..., Items.
      singulars
      1. POASP.NET: How to convert schema to Excel xsl
      1. USSephrial
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload