XSL ".NET DataSet" to "Excel": Detect missing column

[ShadowLocke]

Hi,

Im not very experienced with XSL, this is my first run in with it. I found an XSL file that converts a dataset to an excel readable format.

The problem I have is when the dataset has a null value for a column, it does not add an empty element for the column. Is there any way I can detect this?

Excel.xsl:

<xsl:stylesheet version="1.0" 
  xmlns="urn:schemas-microsoft-com:office:spreadsheet"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
  xmlns:msxsl="urn:schemas-microsoft-com:xslt"
  xmlns:user="urn:my-scripts"
  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="/">
  <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">
      <xsl:apply-templates/>
  </Workbook>
</xsl:template>
 
<xsl:template match="/*">
  <Worksheet>
    <xsl:attribute name="ss:Name">
    <xsl:value-of select="local-name(/*/*)"/>
    </xsl:attribute>
    <Table x:FullColumns="1" x:FullRows="1">
 
      <xsl:for-each select="*[position() = 1]/*">
        <Column ss:Width="120"/>
      </xsl:for-each>      
 
      <Row>
        <xsl:for-each select="*[position() = 1]/*">
          <Cell><Data ss:Type="String">
          <xsl:value-of select="local-name()"/>
          </Data></Cell>
        </xsl:for-each>
      </Row>
      <xsl:apply-templates/>
    </Table>
  </Worksheet>
</xsl:template>
 
 
<xsl:template match="/*/*">
  <Row>
    <xsl:apply-templates/>
  </Row>
</xsl:template>
 
 
<xsl:template match="/*/*/*">
  <Cell>
    <Data ss:Type="String">
          <xsl:value-of select="."/>
    </Data>
  </Cell>
</xsl:template>
 
</xsl:stylesheet>

[jkmyoung]

Can we see your source xml, and an example of where the missing cell is causing a problem?

[ShadowLocke]

Can we see your source xml, and an example of where the missing cell is causing a problem?

Here is what the xml looks like:

<NewDataSet>
    <DataTable>
        <FULL_NAME>Actual Data Removed</FULL_NAME>
        <SPECIALTY>Actual Data Removed</SPECIALTY>
        <LOCATION>Actual Data Removed</LOCATION>
        <ENTRY_DATE>Actual Data Removed</ENTRY_DATE>
        <ACTION>Actual Data Removed</ACTION>
    </DataTable>
 
    <DataTable>
        <FULL_NAME>Actual Data Removed</FULL_NAME>
        <LOCATION>Actual Data Removed</LOCATION>
        <ENTRY_DATE>Actual Data Removed</ENTRY_DATE>
        <ACTION>Actual Data Removed</ACTION>
    </DataTable>
 
    <DataTable>
        <FULL_NAME>Actual Data Removed</FULL_NAME>
        <SPECIALTY>Actual Data Removed</SPECIALTY>
        <LOCATION>Actual Data Removed</LOCATION>
        <ENTRY_DATE>Actual Data Removed</ENTRY_DATE>
        <ACTION>Actual Data Removed</ACTION>
    </DataTable>
</NewDataSet>

(Second <datatable> is missing specialty)

Actually, after seeing this I am thinking its not possible. What I did to solve my problem is went to the actual SQL select and used DECODE to check for nulls and return an empty string instead.

Still, if you think it can be done let me know!

Thanks for your time!

[jkmyoung]

You can do this with a set of defined headers. Eg:

<xsl:template match="/*/*">
  <Row>
     <xsl:call-template name="Data">
        <xsl:with-param name="nodename" select="'FULL_NAME'"/>
     </xsl:call-template>
     <xsl:call-template name="Data">
        <xsl:with-param name="nodename" select="'SPECIALTY"/>
     </xsl:call-template>
     <xsl:call-template name="Data">
        <xsl:with-param name="nodename" select="'LOCATION'"/>
     </xsl:call-template>
     <xsl:call-template name="Data">
        <xsl:with-param name="nodename" select="'ENTRY_DATE'"/>
     </xsl:call-template>
     <xsl:call-template name="Data">
        <xsl:with-param name="nodename" select="'ACTION"/>
     </xsl:call-template>
  </Row>
</xsl:template>
<xsl:template name="Data">
<xsl:param name="nodename"/>
  <Cell>
    <Data ss:Type="String">
          <xsl:value-of select="*[local-name() = $nodename]"/>
    </Data>
  </Cell>
</xsl:template>
 

Or if you have the headers specifically sent to you (in a seperate xml file, or somewhere in the same file) you can also do the same type of thing, though the code is somewhat more complicated. Eg: this is where the headers are in the xslt file, although they can be gotten from elsewhere.

<my:mapping xmlns:my="internal">
        <data name="FULL_NAME"/>
        <data name="SPECIALTY"/>
        <data name="LOCATION"/>
        <data name="ENTRY_DATE"/>
        <data name="ACTION"/>
</my:mapping>
<xsl:template match="/*/*">
  <Row>
    <xsl:variable name="self" select="."/>
    <xsl:for-each select="document('')//data">
      <xsl:variable name="name" select="@name"/>
      <xsl:apply-templates select="$self\*[local-name() = $name]"/>
    </xsl:for-each>
  </Row>
</xsl:template>
 

[ShadowLocke]

Interesting. I'm gonna give another go at it later on this week. The predefined headers might be the way I go. Possibly creating the XSL sheet on the fly as oppossed to using a predfined file.