473,224 Members | 1,975 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,224 software developers and data experts.

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 3588
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

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

Similar topics

1
by: Jerry O | last post by:
Hi , I have my asp successfully creating excel file viewable through IE using the header: Response.ContentType = "application/vnd.ms-excel" If the user does not have excel installed they...
3
by: John | last post by:
Is there a way to code the button that's available in the query window--microsoft excel icon that exports to excel. I know transferspreadsheet will do this---but I want the query, which is in a...
2
by: nutthatch | last post by:
I want to be able to import an Excel spreadsheet into Access 2K using the macro command Transferspreadsheet. However, the file I am importing (over which I have no control) contains some records...
5
by: KC | last post by:
Hi, I have code similar to this.. Dim xlApp As Object xlApp = CreateObject("Excel.Application", "\\MyServer") The call is from a asp.net (Intranet) application. \\Myserver is a network...
1
by: Benny Ng | last post by:
Hi,All, Export Method: ------------------------------------------------------------------------- strFileNameExport = "Results" Response.Clear() Response.Buffer = True...
1
by: Michael Twua | last post by:
In asp.net with vb.net,I can't create a excel object,such as excel.application,excel.workBook,excel.workSheet. It displayed that "System.UnauthorizedAccessException Refuse Access". I have import...
2
by: CVerma | last post by:
I'm using an html input control (System.web.UI.HTMLControls.HTMLInputFile) to upload files such as msword, excel, jpg, and pdf. I have the encType property set in the form:...
1
by: Michael Tkachev | last post by:
Hi Everybody, I created an Excel file in the ASP.Net. When I wrote this file on the disk I tryed to release COM objects. But I couldn't do it. When my method finished an "Excel" process exists...
0
by: Microsoft NewsGroups | last post by:
I am creating a TAB delimited file with .XLS extention using the Response.ContentType = "application/ms-excel" method. Normally I would place this on it's own form and delete all the HTML in the...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.