468,291 Members | 1,497 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,291 developers. It's quick & easy.

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

115 100+
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:
Expand|Select|Wrap|Line Numbers
  1. <xsl:stylesheet version="1.0" 
  2.   xmlns="urn:schemas-microsoft-com:office:spreadsheet"
  3.   xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
  4.   xmlns:msxsl="urn:schemas-microsoft-com:xslt"
  5.   xmlns:user="urn:my-scripts"
  6.   xmlns:o="urn:schemas-microsoft-com:office:office"
  7.   xmlns:x="urn:schemas-microsoft-com:office:excel"
  8.   xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" > 
  9.  
  10. <xsl:template match="/">
  11.   <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
  12.     xmlns:o="urn:schemas-microsoft-com:office:office"
  13.     xmlns:x="urn:schemas-microsoft-com:office:excel"
  14.     xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
  15.     xmlns:html="http://www.w3.org/TR/REC-html40">
  16.       <xsl:apply-templates/>
  17.   </Workbook>
  18. </xsl:template>
  19.  
  20. <xsl:template match="/*">
  21.   <Worksheet>
  22.     <xsl:attribute name="ss:Name">
  23.     <xsl:value-of select="local-name(/*/*)"/>
  24.     </xsl:attribute>
  25.     <Table x:FullColumns="1" x:FullRows="1">
  26.  
  27.       <xsl:for-each select="*[position() = 1]/*">
  28.         <Column ss:Width="120"/>
  29.       </xsl:for-each>      
  30.  
  31.       <Row>
  32.         <xsl:for-each select="*[position() = 1]/*">
  33.           <Cell><Data ss:Type="String">
  34.           <xsl:value-of select="local-name()"/>
  35.           </Data></Cell>
  36.         </xsl:for-each>
  37.       </Row>
  38.       <xsl:apply-templates/>
  39.     </Table>
  40.   </Worksheet>
  41. </xsl:template>
  42.  
  43.  
  44. <xsl:template match="/*/*">
  45.   <Row>
  46.     <xsl:apply-templates/>
  47.   </Row>
  48. </xsl:template>
  49.  
  50.  
  51. <xsl:template match="/*/*/*">
  52.   <Cell>
  53.     <Data ss:Type="String">
  54.           <xsl:value-of select="."/>
  55.     </Data>
  56.   </Cell>
  57. </xsl:template>
  58.  
  59. </xsl:stylesheet>
May 13 '08 #1
4 3324
jkmyoung
2,057 Expert 2GB
Can we see your source xml, and an example of where the missing cell is causing a problem?
May 13 '08 #2
ShadowLocke
115 100+
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:

Expand|Select|Wrap|Line Numbers
  1. <NewDataSet>
  2.     <DataTable>
  3.         <FULL_NAME>Actual Data Removed</FULL_NAME>
  4.         <SPECIALTY>Actual Data Removed</SPECIALTY>
  5.         <LOCATION>Actual Data Removed</LOCATION>
  6.         <ENTRY_DATE>Actual Data Removed</ENTRY_DATE>
  7.         <ACTION>Actual Data Removed</ACTION>
  8.     </DataTable>
  9.  
  10.     <DataTable>
  11.         <FULL_NAME>Actual Data Removed</FULL_NAME>
  12.         <LOCATION>Actual Data Removed</LOCATION>
  13.         <ENTRY_DATE>Actual Data Removed</ENTRY_DATE>
  14.         <ACTION>Actual Data Removed</ACTION>
  15.     </DataTable>
  16.  
  17.     <DataTable>
  18.         <FULL_NAME>Actual Data Removed</FULL_NAME>
  19.         <SPECIALTY>Actual Data Removed</SPECIALTY>
  20.         <LOCATION>Actual Data Removed</LOCATION>
  21.         <ENTRY_DATE>Actual Data Removed</ENTRY_DATE>
  22.         <ACTION>Actual Data Removed</ACTION>
  23.     </DataTable>
  24. </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!
May 13 '08 #3
jkmyoung
2,057 Expert 2GB
You can do this with a set of defined headers. Eg:
Expand|Select|Wrap|Line Numbers
  1. <xsl:template match="/*/*">
  2.   <Row>
  3.      <xsl:call-template name="Data">
  4.         <xsl:with-param name="nodename" select="'FULL_NAME'"/>
  5.      </xsl:call-template>
  6.      <xsl:call-template name="Data">
  7.         <xsl:with-param name="nodename" select="'SPECIALTY"/>
  8.      </xsl:call-template>
  9.      <xsl:call-template name="Data">
  10.         <xsl:with-param name="nodename" select="'LOCATION'"/>
  11.      </xsl:call-template>
  12.      <xsl:call-template name="Data">
  13.         <xsl:with-param name="nodename" select="'ENTRY_DATE'"/>
  14.      </xsl:call-template>
  15.      <xsl:call-template name="Data">
  16.         <xsl:with-param name="nodename" select="'ACTION"/>
  17.      </xsl:call-template>
  18.   </Row>
  19. </xsl:template>
  20. <xsl:template name="Data">
  21. <xsl:param name="nodename"/>
  22.   <Cell>
  23.     <Data ss:Type="String">
  24.           <xsl:value-of select="*[local-name() = $nodename]"/>
  25.     </Data>
  26.   </Cell>
  27. </xsl:template>
  28.  
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.
Expand|Select|Wrap|Line Numbers
  1. <my:mapping xmlns:my="internal">
  2.         <data name="FULL_NAME"/>
  3.         <data name="SPECIALTY"/>
  4.         <data name="LOCATION"/>
  5.         <data name="ENTRY_DATE"/>
  6.         <data name="ACTION"/>
  7. </my:mapping>
  8. <xsl:template match="/*/*">
  9.   <Row>
  10.     <xsl:variable name="self" select="."/>
  11.     <xsl:for-each select="document('')//data">
  12.       <xsl:variable name="name" select="@name"/>
  13.       <xsl:apply-templates select="$self\*[local-name() = $name]"/>
  14.     </xsl:for-each>
  15.   </Row>
  16. </xsl:template>
  17.  
May 14 '08 #4
ShadowLocke
115 100+
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.
May 14 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Michael Tkachev | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.