XML transformation for Excel using XSL does not display columns and rows

XSL_NEWBIE · November 20th, 2012, 02:10 PM

Hello
I have an XSL template that I using to transform XML data into a file that opens in Excel. I am doing this transformation in Oracle. The resultant XML file that is created seems to have well-formed tags however when this is opened in Excel the data is all being displayed in one cell of the Excel spreadsheet when I am expecting to see multiple rows and columns where the tags <Study>, <Study_Site>, <Country> etc. form the column headers. Please can you help me find the cause of the formatting problem. Any help is gratefully received!
Thanks.

The XML input is:

Code:

<?xml version="1.0"?>
<Root>
 <ROW>
  <Study>VOL_DATA</Study>
  <Study_x0020_Site>100022</Study_x0020_Site>
  <Country>-</Country>
  <Investigator>Dr Investi Gator</Investigator>
  <Subject>220</Subject>
  <Termination_x0020_Status/>
  <Termination_x0020_Date/>
  <Discrepancy_x0020_Status>UNREVIEWED</Discrepancy_x0020_Status>
  <Discrepandy_x0020_ID>683922</Discrepandy_x0020_ID>
  <Discrepancy_x0020_Text>The value entered of ALIEN for Race was not found in the expected list of values.</Discrepancy_x0020_Text>
  <Creation_x0020_Date>07-Aug-2012</Creation_x0020_Date>
  <Days_x0020_since_x0020_Creation>105</Days_x0020_since_x0020_Creation>
  <Last_x0020_Modification_x0020_Date/>
  <Days_x0020_last_x0020_modified/>
  <Report_x0020_Execution>20-Nov-2012</Report_x0020_Execution>
 </ROW>
 <ROW>
  <Study>VOL_DATA</Study>
  <Study_x0020_Site>100022</Study_x0020_Site>
  <Country>-</Country>
  <Investigator>Dr Investi Gator</Investigator>
  <Subject>220</Subject>
  <Termination_x0020_Status/>
  <Termination_x0020_Date/>
  <Discrepancy_x0020_Status>UNREVIEWED</Discrepancy_x0020_Status>
  <Discrepandy_x0020_ID>683822</Discrepandy_x0020_ID>
  <Discrepancy_x0020_Text>The value entered of 50 for Height is below the expected minimum of 100.</Discrepancy_x0020_Text>
  <Creation_x0020_Date>07-Aug-2012</Creation_x0020_Date>
  <Days_x0020_since_x0020_Creation>105</Days_x0020_since_x0020_Creation>
  <Last_x0020_Modification_x0020_Date/>
  <Days_x0020_last_x0020_modified/>
  <Report_x0020_Execution>20-Nov-2012</Report_x0020_Execution>
 </ROW>
</Root>

The XSL template is:

Code:

<?xml version="1.0" encoding="ISO-8859-1"?>  
<?mso-application progid="Excel.Sheet"?>
<xsl:stylesheet version="1.0" 
   xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
   xmlns:c="urn:schemas-microsoft-com:office:component: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">
   
 <xsl:template match="/">
 <Workbook>
 <xsl:call-template name="DocumentProperties"/>
 <xsl:call-template name="OfficeDocumentSettings"/>
 <xsl:call-template name="ExcelWorkbook"/>
 <xsl:call-template name="Styles"/>
 <xsl:apply-templates select="/*" mode="worksheet"/>
 </Workbook>
 </xsl:template>
 
 <!-- Worksheets -->
 <xsl:template match="*" mode="worksheet">
 <xsl:variable name="position" select="position()"/>
 <Worksheet ss:Name="{concat(''Sheet'', $position)}">
 <Table ss:ExpandedColumnCount="{count(./*[1]/*)}" ss:ExpandedRowCount="{count(./*) + 2}" x:FullColumns="1" x:FullRows="1">
 <xsl:apply-templates select="*" mode="row"/>
 </Table>
 <xsl:call-template name="WorksheetOptions"/>
 </Worksheet>
 </xsl:template>
 
 <!-- Rows -->
 <xsl:template match="*" mode="row">
 <Row>
 <xsl:apply-templates select="*" mode="cell"/>
 </Row>
 </xsl:template>
 
 <!-- Cells -->
 <xsl:template match="*" mode="cell">
 <xsl:variable name="type">
 <xsl:choose>
 <xsl:when test="number(.) = .">Number</xsl:when>
 <xsl:otherwise>String</xsl:otherwise>
 </xsl:choose>
 </xsl:variable>
 <Cell>
 <Data ss:Type="{$type}">
 <xsl:value-of select="."/>
 </Data>
 </Cell>
 </xsl:template>
 
 <!-- Columns -->
 <xsl:template match="*">
 <xsl:variable name="name" select="name(.)"/>
 <xsl:variable name="length">
 <xsl:call-template name="length">
 <xsl:with-param name="nodeset" select="//parent::*/parent::*/*/*[name(.) = $name]"/>
 </xsl:call-template>
 </xsl:variable>
 <xsl:variable name="width">
 <xsl:choose>
 <xsl:when test="($length * 5.75) &lt; 56.25">56.25</xsl:when>
 <xsl:otherwise>
 <xsl:value-of select="$length * 5.75"/>
 </xsl:otherwise>
 </xsl:choose>
 </xsl:variable>
 <xsl:variable name="style">
 <xsl:choose>
 <xsl:when test="parent::*/parent::*/*/*[name(.) = $name] = number(parent::*/parent::*/*[1]/*[name(.) = $name])">
 <xsl:choose>
 <xsl:when test="string-length(parent::*/parent::*/*/*[name(.) = $name][contains(.,''.'')]) = 0">s23</xsl:when>
 <xsl:otherwise>s24</xsl:otherwise>
 </xsl:choose>
 </xsl:when>
 <xsl:otherwise>s22</xsl:otherwise>
 </xsl:choose>
 </xsl:variable>
 <Column ss:StyleID="{$style}" ss:AutoFitWidth="0" ss:Width="{$width}"/>
 </xsl:template>
 
 <!-- DocumentProperties -->
 <xsl:template name="DocumentProperties">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
 <Author>author</Author>
 <Company>company</Company>
 <Version>1</Version>
 </DocumentProperties>
 </xsl:template>
 
 <!-- OfficeDocumentSettings -->
 <xsl:template name="OfficeDocumentSettings">
 <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
 <DownloadComponents/>
 <LocationOfComponents HRef="file:///\\phlfsnt01\DOWNLOAD\OfficeXPSrc\"/>
 </OfficeDocumentSettings>
 </xsl:template>
 
 <!-- ExcelWorkbook -->
 <xsl:template name="ExcelWorkbook">
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
 <WindowHeight>9210</WindowHeight>
 <WindowWidth>15195</WindowWidth>
 <WindowTopX>0</WindowTopX>
 <WindowTopY>60</WindowTopY>
 <ProtectStructure>False</ProtectStructure>
 <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 </xsl:template>
 
 <!-- Styles -->
 <xsl:template name="Styles">
 <Styles>
 <Style ss:ID="Default" ss:Name="Normal">
 <Alignment ss:Vertical="Bottom"/>
 <Borders/>
 <Font/>
 <Interior/>
 <NumberFormat/>
 <Protection/>
 </Style>
 </Styles>
 </xsl:template>
 
 <!-- WorksheetOptions -->
 <xsl:template name="WorksheetOptions">
 <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
 <Print>
 <ValidPrinterInfo/>
 <HorizontalResolution>1200</HorizontalResolution>
 <VerticalResolution>1200</VerticalResolution>
 </Print>
 <ProtectObjects>False</ProtectObjects>
 <ProtectScenarios>False</ProtectScenarios>
 </WorksheetOptions>
 </xsl:template>
 
 <!-- Determine length of the node name or the longest node(s), which ever is longer -->
 <xsl:template name="length">
 <xsl:param name="nodeset"/>
 <xsl:variable name="longest">
 <xsl:call-template name="longest">
 <xsl:with-param name="nodeset" select="$nodeset"/>
 </xsl:call-template>
 </xsl:variable>
 <xsl:choose>
 <xsl:when test="string-length(name($nodeset[1])) > string-length($longest)">
 <xsl:value-of select="string-length(name($nodeset[1]))"/>
 </xsl:when>
 <xsl:otherwise>
 <xsl:value-of select="string-length($longest)"/>
 </xsl:otherwise>
 </xsl:choose>
 </xsl:template>
 
 <!-- Find the nodes with the longest string-length -->
 <xsl:template name="longest">
 <xsl:param name="nodeset"/>
 <xsl:param name="length" select="0"/>
 <xsl:choose>
 <xsl:when test="count($nodeset[string-length(.) > $length]) > 0">
 <xsl:call-template name="longest">
 <xsl:with-param name="nodeset" select="$nodeset[string-length(.) > $length]"/>
 <xsl:with-param name="length" select="string-length($nodeset[string-length(.) > $length][1])"/>
 </xsl:call-template>
 </xsl:when>
 <xsl:otherwise>
 <xsl:value-of select="$nodeset"/>
 </xsl:otherwise>
 </xsl:choose>
 </xsl:template>
 </xsl:stylesheet>

The output I am seeing is:

Code:

<?xml version="1.0"?>
-<Workbook xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet"> -<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"> <Author>author</Author> <Company>company</Company> <Version>1</Version> </DocumentProperties> -<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office"> <DownloadComponents/> <LocationOfComponents HRef="file:///\\phlfsnt01\DOWNLOAD\OfficeXPSrc\"> </LocationOfComponents> </OfficeDocumentSettings> -<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"> <WindowHeight>9210</WindowHeight> <WindowWidth>15195</WindowWidth> <WindowTopX>0</WindowTopX> <WindowTopY>60</WindowTopY> <ProtectStructure>False</ProtectStructure> <ProtectWindows>False</ProtectWindows> </ExcelWorkbook> -<Styles> -<Style ss:Name="Normal" ss:ID="Default"> <Alignment ss:Vertical="Bottom"> </Alignment> <Borders/> <Font/> <Interior/> <NumberFormat/> <Protection/> </Style> </Styles> -<Worksheet ss:Name="Sheet1"> -<Table x:FullRows="1" x:FullColumns="1" ss:ExpandedRowCount="4" ss:ExpandedColumnCount="15"> -<Row> -<Cell> <Data ss:Type="String">VOL_DATA</Data> </Cell> -<Cell> <Data ss:Type="Number">100022</Data> </Cell> -<Cell> <Data ss:Type="String">-</Data> </Cell> -<Cell> <Data ss:Type="String">Dr Investi Gator</Data> </Cell> -<Cell> <Data ss:Type="Number">220</Data> </Cell> -<Cell> <Data ss:Type="String"/> </Cell> -<Cell> <Data ss:Type="String"/> </Cell> -<Cell> <Data ss:Type="String">UNREVIEWED</Data> </Cell> -<Cell> <Data ss:Type="Number">683922</Data> </Cell> -<Cell> <Data ss:Type="String">The value entered of ALIEN for Race was not found in the expected list of values.</Data> </Cell> -<Cell> <Data ss:Type="String">07-Aug-2012</Data> </Cell> -<Cell> <Data ss:Type="Number">105</Data> </Cell> -<Cell> <Data ss:Type="String"/> </Cell> -<Cell> <Data ss:Type="String"/> </Cell> -<Cell> <Data ss:Type="String">20-Nov-2012</Data> </Cell> </Row> -<Row> -<Cell> <Data ss:Type="String">VOL_DATA</Data> </Cell> -<Cell> <Data ss:Type="Number">100022</Data> </Cell> -<Cell> <Data ss:Type="String">-</Data> </Cell> -<Cell> <Data ss:Type="String">Dr Investi Gator</Data> </Cell> -<Cell> <Data ss:Type="Number">220</Data> </Cell> -<Cell> <Data ss:Type="String"/> </Cell> -<Cell> <Data ss:Type="String"/> </Cell> -<Cell> <Data ss:Type="String">UNREVIEWED</Data> </Cell> -<Cell> <Data ss:Type="Number">683822</Data> </Cell> -<Cell> <Data ss:Type="String">The value entered of 50 for Height is below the expected minimum of 100.</Data> </Cell> -<Cell> <Data ss:Type="String">07-Aug-2012</Data> </Cell> -<Cell> <Data ss:Type="Number">105</Data> </Cell> -<Cell> <Data ss:Type="String"/> </Cell> -<Cell> <Data ss:Type="String"/> </Cell> -<Cell> <Data ss:Type="String">20-Nov-2012</Data> </Cell> </Row>
</Table> -<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"> -<Print> <ValidPrinterInfo/> <HorizontalResolution>1200</HorizontalResolution> <VerticalResolution>1200</VerticalResolution> </Print> <ProtectObjects>False</ProtectObjects> <ProtectScenarios>False</ProtectScenarios> </WorksheetOptions> </Worksheet> </Workbook>

WilliamYou · December 1st, 2012, 10:23 AM

Hello.

From what I see you are missing a Cell elements to encapsulate your <Data />

Code:

<Cell>
    <Data></Data>
</Cell>

Hope this helps.
--

William