Solved

XML/XSL using Saxon processor to Output to Excel file

Posted on 2004-09-10
11
953 Views
Last Modified: 2013-11-19
Hello,

Below is the current XSL file I'd like to use, but would like the column heading not hard-coded because depending on the query, those headings can change.  Below the XSL file are two examples of the XML file that could possibly be used with the desired results (in excel format).  Thanks.

Current XSL:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:saxon="http://icl.com/saxon" extension-element-prefixes="saxon" version="1.0">
<xsl:variable name="input" select="document('ResultSet.xml')"/>
<xsl:template match="/">
     <saxon:output href="result.xls" method="text">
<xsl:text>LASTNAME</xsl:text>
<xsl:text>&#x9;</xsl:text>
<xsl:text>FIRSTNAME</xsl:text>
<xsl:text>&#x9;</xsl:text>
<xsl:text>OTHER</xsl:text>
<xsl:text>&#013;</xsl:text>
     <xsl:for-each select="$input/ResultSet/ResultSetData/Row">
          <xsl:for-each select="Column" >
            <xsl:value-of select="."/>
     <xsl:if test="not(position() = last())">
<xsl:text>&#x9;</xsl:text>
  </xsl:if>
</xsl:for-each>
<xsl:text>&#013;</xsl:text>
</xsl:for-each>
      </saxon:output>
       </xsl:template>
</xsl:stylesheet>

XML Example 1 with desired results (xls)

<?xml version="1.0" ?>
<ResultSet>
 <ResultSetData>
  <Row>
   <Column name="LASTNAME">Shmoe</Column>
   <Column name="FIRSTNAME">Joe</Column>
   <Column name="OTHER">Other Data</Column>
  </Row>
  <Row>
   <Column name="LASTNAME">Smith</Column>
   <Column name="FIRSTNAME">John</Column>
   <Column name="OTHER">Other Data2</Column>
  </Row>
</ResultSetData>
</ResultSet>

=

                    Column1        Column2         Column3
Row1            LASTNAME     FIRSTNAME    OTHER
Row2            Shmoe           Joe                Other Data
Row3            Smith             John              Other Data2


Or, it can look like this:

2.

<?xml version="1.0" ?>
<ResultSet>
 <ResultSetData>
  <Row>
   <Column name="ID">234234</Column>
   <Column name="PURCHASEID">5555</Column>
   <Column name="OTHER">Other Data</Column>
  </Row>
  <Row>
   <Column name="ID">567567</Column>
   <Column name="PURCHASEID">77777</Column>
   <Column name="OTHER">Other Data2</Column>
  </Row>
</ResultSetData>
</ResultSet>

=

                    Column1        Column2           Column3
Row1            ID                  PURCHASEID     OTHER
Row2            234234           5555                Other Data
Row3            567567           77777              Other Data2
0
Comment
Question by:jpegvarn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
11 Comments
 

Author Comment

by:jpegvarn
ID: 12027521
Sorry, I also forgot to mention: Is there any way to have the file name of the xls be today's date, with the second (just incase someone needs to do it more than once in the same day...the file will not be overwritten).  Thanks.

Current: <saxon:output href="result.xls" method="text">
0
 
LVL 26

Expert Comment

by:rdcpro
ID: 12028573
http://www.experts-exchange.com/Web/Web_Languages/XML/Q_21098399.html#11834944

The operative part is:

         <xsl:apply-templates select="Row[1]/Column" mode="mHeading"/>
and

     <xsl:template match="Column" mode="mHeading">
          <xsl:value-of select="@name"/>
          <xsl:if test="position() != last()">
               <xsl:text>,</xsl:text>
          </xsl:if>
     </xsl:template>
 

As for the date, you could add it to the transform as a parameter, then call the transformation, I suppose. At least, that's how I always do it for reporting applications, whether or not I even need the date.  

Regards,
Mike Sharp
0
 
LVL 26

Expert Comment

by:rdcpro
ID: 12028734
Like:

<xsl:param name="pDate" />

<saxon:output href="result{$pDate}.xls" method="text">

but you need to set the parameter in Java, before you call the transform.  You can attach nodes, or whatever you want.

Or you can use the Saxon saxon:script extension, and use the java getUTCDate() function or some such to get the date.

<saxon:script implements-prefix="dates" language="java"
              src="java:com.icl.saxon.functions.Extensions">
       Java code here to get the date

 


Regards,
Mike Sharp
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Expert Comment

by:wadalhag
ID: 12040001
Hi jpegvarn
Is it possible to change your original XML from
<?xml version="1.0" ?>
<ResultSet>
 <ResultSetData>
.
.
.

To

<?xml version="1.0" ?>
<ResultSet>
<Date>date data</Date>
<ResultSetData>
.
.
.

If this possible, you would be able to name the file using <date> from the source file.

0
 

Author Comment

by:jpegvarn
ID: 12044726
Unfortunatley not, I am sending a reponse to another server which gives me the formatted xml results.
0
 
LVL 3

Expert Comment

by:wadalhag
ID: 12045422
The following will do the job for you and the result file will be in the format “Mon Sep 13 170906 GMT+0100 2004.xls”
*****  the new style sheet *****
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:saxon="http://icl.com/saxon"
xmlns:date="http://www.jclark.com/xt/java/java.util.Date"
extension-element-prefixes="saxon" version="1.0">
<xsl:variable name="input" select="document('ResultSet.xml')"/>
<xsl:template match="/">
<xsl:variable name="filename">
 <xsl:if test="function-available('date:to-string') and function-available('date:new')">
<xsl:value-of select="concat(translate(date:to-string(date:new()),':',''),'.xls')"/>
 </xsl:if>
</xsl:variable>

<saxon:output href="{$filename}" method="text">
<xsl:text>LASTNAME</xsl:text>
<xsl:text>&#x9;</xsl:text>
<xsl:text>FIRSTNAME</xsl:text>
<xsl:text>&#x9;</xsl:text>
<xsl:text>OTHER</xsl:text>
<xsl:text>&#013;</xsl:text>

  <xsl:for-each select="$input/ResultSet/ResultSetData/Row">
        
          <xsl:for-each select="Column" >
            <xsl:value-of select="."/>
     <xsl:if test="not(position() = last())">
<xsl:text>&#x9;</xsl:text>
  </xsl:if>
</xsl:for-each>
<xsl:text>&#013;</xsl:text>
</xsl:for-each>
      </saxon:output>
       </xsl:template>
</xsl:stylesheet>
0
 

Author Comment

by:jpegvarn
ID: 12045530
Nice, works great.

Any chance on getting the answer to the original question above?

"Below is the current XSL file I'd like to use, but would like the column heading not hard-coded because depending on the query, those headings can change.  Below the XSL file are two examples of the XML file that could possibly be used with the desired results (in excel format). " 

thanks.
0
 
LVL 26

Expert Comment

by:rdcpro
ID: 12045886
Did you have some objection to my suggestion?  That is, replacing:

<xsl:text>LASTNAME</xsl:text>
<xsl:text>&#x9;</xsl:text>
<xsl:text>FIRSTNAME</xsl:text>
<xsl:text>&#x9;</xsl:text>
<xsl:text>OTHER</xsl:text>
<xsl:text>&#013;</xsl:text>

with

<xsl:for-each select="Row[1]/Column" />
          <xsl:value-of select="@name"/>
          <xsl:if test="position() != last()"><xsl:text>&#013;</xsl:text>
               <xsl:text>,</xsl:text>
          </xsl:if>
          <xsl:if test="position() = last()">
               <xsl:text>&#013;</xsl:text>
          </xsl:if>
</xsl:for-each>
 
0
 
LVL 3

Accepted Solution

by:
wadalhag earned 500 total points
ID: 12046009
here it's

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:saxon="http://icl.com/saxon"
xmlns:date="http://www.jclark.com/xt/java/java.util.Date"
extension-element-prefixes="saxon" version="1.0">
<xsl:variable name="input" select="document('ResultSet.xml')"/>
<xsl:template match="/">
<xsl:variable name="filename">
 <xsl:if test="function-available('date:to-string') and function-available('date:new')">
<xsl:value-of select="concat(translate(date:to-string(date:new()),':',''),'.xls')"/>
 </xsl:if>
</xsl:variable>

<saxon:output href="{$filename}" method="text">
<xsl:for-each select="$input/ResultSet/ResultSetData/Row[1]/Column">
<xsl:value-of select="@name"/> <xsl:text>&#x9;</xsl:text></xsl:for-each>
<xsl:text>&#013;</xsl:text>

  <xsl:for-each select="$input/ResultSet/ResultSetData/Row">
       
          <xsl:for-each select="Column" >
            <xsl:value-of select="."/>
     <xsl:if test="not(position() = last())">
<xsl:text>&#x9;</xsl:text>
  </xsl:if>
</xsl:for-each>
<xsl:text>&#013;</xsl:text>
</xsl:for-each>
      </saxon:output>
       </xsl:template>
</xsl:stylesheet>
0
 

Author Comment

by:jpegvarn
ID: 12047592
Sorry for not responding to you rdcpro, but I did try that solution and it was throwing errors and I didn't understand why (the error was pointing toward a closing tag).  Sorry I didn't send back the error at the time, but it was at the end of the day.

Wadalhag, your solution worked perfectly, thank you.

Please go here to see what else I need done for 500 points, thanks guys:

http://www.experts-exchange.com/Web/Web_Languages/XML/Q_21129355.html
0
 
LVL 3

Expert Comment

by:wadalhag
ID: 12049260
thanks jpegvarn
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Windows Tool to Build Android and iOS App 3 148
.php tree directory? 5 95
How to write an English digest paper 6 145
Fulfillment API php code sample 1 95
Introduction Knockoutjs (Knockout) is a JavaScript framework (Model View ViewModel or MVVM framework).   The main ideology behind Knockout is to control from JavaScript how a page looks whilst creating an engaging user experience in the least …
I found this questions asking how to do this in many different forums, so I will describe here how to implement a solution using PHP and AJAX. The logical flow for the problem should be: Write an event handler for the first drop down box to get …
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question