wordpress hit counter
XSL transformation of Spreadsheet ML to HTML (with Paging) – Part 2 - OpenXML Developer - Blog - OpenXML Developer
Goodbye and Hello

OpenXmlDeveloper.org is Shutting Down

There is a time for all good things to come to an end, and the time has come to shut down OpenXmlDeveloper.org.

Screen-casts and blog posts: Content on OpenXmlDeveloper.org will be moving to EricWhite.com.

Forums: We are moving the forums to EricWhite.com and StackOverflow.com. Please do not post in the forums on OpenXmlDeveloper.org. Instead, please post in the forums at EricWhite.com or at StackOverflow.com.

Please see this blog post for more information about my plans moving forward.  Cheers, Eric

XSL transformation of Spreadsheet ML to HTML (with Paging) – Part 2

XSL transformation of Spreadsheet ML to HTML (with Paging) – Part 2

  • Comments 3

                  By Muthu Kumar Arjunan of Sonata Software Ltd.

 

This article explains how to “Page” an HTML table generated from Spreadsheet ML document, using XSL transformation & XMLHttp & Java Script.

 

XSL transformation of Spreadsheet ML to HTML – Part 1” dealt with conversion of spreadsheet ML document into a HTML table.

While this part explains “Paging” the HTML table generated.

 

This article's intended audience includes:

a)      Developers of heterogeneous applications.

b)      Developers who are interested in displaying Spreadsheet ML document in a browser.

c)      All those who are interested in learning how to convert XML to HTML table (with paging).

 

Source code is downloadable.

Note: The downloadable (zip file) is an asp net application. Yet care has been taken to avoid usage of Microsoft’s native dlls on Open XML packaging (Sytem.IO.Packaging). Thus ensuring code conversion of downloadable (asp net application) to other heterogeneous platforms can be done with ease, if required.

 

 

Paging is a mechanism of retrieving a prescribed number of records to be displayed on the selected page.

 

To page the Spreadsheet ML data (rows) that is displayed in HTML table, we need two XSLTs to do the server-side transformations to HTML.

 

1) Xml2HTML.xsl: 

 

This XSLT generates the whole HTML page (from the generic xml of Spreadsheet ML) which includes the HTML table that holds the first set of Spreadsheet ML data/rows (prescribed number of records/rows for paging).

 

This generated HTML page also holds links like “Next page” and “Previous page”.

 

The parameters for the XSLT gives us control over the number of records displayed per page, display of headers, etc.

 

2) Xml2HTMLTable.xsl:

 

  This XSLT is used for the generation of subsequent (Prescribed) number of records on click of links like “Next page” and “Previous page”.

 

On click of “Next/Prev page”, the request to server for the subsequent set of records is sent using “XMLHttp”. The XSLT residing in the server transforms the next set of records (“XLRow” in generic xml format of SpreadSheet ML) into table (HTML) and returns the HTML content.

 

 Advantage of using XMLHttp :

             XmlHttp is used for retrieving the Next/Previous set of records because:

a)      Only the XML data is sent to the server (not the whole HTML form), thus decreasing the request/receive time, making it faster.

b)      The whole HTML page is not reloaded in the browser; only the HTML table is repopulated with the XML (data fetched from the server using XMLHttp). Hence no flickering –feel for the end-user.

 

 

 

XSLT for Transformation into HTML page

 

Xml2HTML.XSL:

 

<?xml version="1.0" ?>

<xsl:stylesheet version="1.0"

    xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

  <xsl:param name="blnMakeHeaderFromElementName">true</xsl:param>

  <xsl:param name="blnRetainFirstRowValAsHeader">true</xsl:param>

  <xsl:param name="valStartRecordIndex">0</xsl:param>

  <xsl:param name="valMaxRecordsPerPage">10</xsl:param>

  <xsl:param name="SourceFileName"></xsl:param>

  <xsl:param name="PortUsedbyCasiniServer">1639</xsl:param>

 

  <!--<xsl:sort order="ascending"/>-->

 

  <xsl:template match="/">

 

    <HTML>

      <STYLE>

        BODY {margin:0}

        .rowEven {font:12pt Verdana; background-color:#FFFFCC;border-bottom:1px solid #CC88CC}

        .rowOdd {font:12pt Verdana; border-bottom:1px; background-color: #e7e7e7 }

        .header {font:bold 12pt Verdana;background-color:#C0C0C0; padding:2px; border:2px outset grey}

        .rowHeader {font:bold 12pt Verdana;background-color:#FFCC99; padding:2px; border:2px outset black}

        .up {background-color:#CCFF99; cursor:hand;}

        .down {background-color:#FF9933;cursor:hand;}

      </STYLE>

      <script>

        var lineofpage = <xsl:value-of select="$valMaxRecordsPerPage"/>;

        <![CDATA[

    // Total lines in a page

    // The first item index number in a page

var low = 0;

// The last item index number in a page

var high = lineofpage;

 

var xslDoc ;

var strXSLT1;

var BaseUrl;

var NextOrPrev;

BaseUrl="http://localhost/AsynchronousProcessing/";

var PortUsed;

PortUsed=']]><xsl:value-of select="$PortUsedbyCasiniServer"/><![CDATA[';

var SourceFileName;

var BaseUrlWithPort;

BaseUrlWithPort="http://localhost:" + PortUsed +"/AsynchronousProcessing/";

SourceFileName=']]><xsl:value-of select="$SourceFileName"/><![CDATA[';

 

 

function sort(key) {

alert("Implement ur Sort Functionality " +key);

}

 

 

function init()

{

                low = 0;

                high = low + lineofpage;

}

 

function getXMLData()

{

 

var XMLString= "Concatvalue=" +SourceFileName + ";" + low + ";" + high + ";" + lineofpage ;

//alert(XMLString);

var xmlHTTP = new ActiveXObject("Microsoft.XMLHTTP");

var strUrl= BaseUrlWithPort +  "Default.aspx";

//alert(strUrl);

  xmlHTTP.open("POST", strUrl, false);

  xmlHTTP.setRequestHeader('Content-Type','application/x-www-form-urlencoded');

  alert("Sending to Server Using XMLHTTP for the " + NextOrPrev + " " + lineofpage + " Records... " + XMLString);

  xmlHTTP.send(XMLString);    

  var xmlDOM = new ActiveXObject("Microsoft.XMLDOM");

  alert("Got back from Server after XSLT conversion in Server ...." + xmlHTTP.ResponseText);

  xmlDOM.loadXML(xmlHTTP.ResponseText);

  if (xmlDOM.parseError != 0)

  {

   alert("Error occurred: " + xmlDOM.parseError.reason);

   return false;

  }

  else

  {

  document.getElementById('root').outerHTML=xmlDOM.xml;

  }

}

 

 

function prevpg() {

                    // Set the page bound

    high = low;

    low = high - lineofpage;

    NextOrPrev="Previous";

    getXMLData();

 

}

 

function nextpg() {

                // Set the page bound

                    low = high;

                    high = low + lineofpage;

      NextOrPrev="Next";

      getXMLData();

}

 

 

 

function Move(e)

{

  document.layers['Menu'].left=e.pageX;

  document.layers['Menu'].top=e.pageY;

}

 

  function Move2()

  {

  document.all["Menu"].style.left=event.clientX + document.body.scrollLeft - document.body.clientLeft;

  document.all["Menu"].style.top=event.clientY + document.body.scrollTop  - document.body.clientTop;

  document.all["Menu"].style.display = 'block';

  }

 

  function Follow()

  {

  if (document.all) Move2()

  }

 

  function Loaded()

  {

    if (document.layers)

    {

    window.captureEvents(Event.MOUSEMOVE);

    window.onmousemove=Move;

    }

  }

 

  function BacktoOrigin()

  {

  document.all["Menu"].style.left=0;

  document.all["Menu"].style.top=0;

  document.all["Menu"].style.display = 'none';

  }

]]>

      </script>

      <BODY id="listing" onLoad="Loaded();init();" >

        <div ID="Menu" NAME="Menu"

      STYLE="position:absolute;top:10000;left:10000;background-color:#33FFCC;padding:2px;">

          <b>Edit</b>(Not-functional)

          <br></br>

          <b>Delete</b>(Not-functional)

        </div>

       

          <TABLE border="1">

            <TR>

              <TD>

                <b class="rowEven">

                  <u> Guide Lines:</u>

                </b>

                <b>

                  Double Click on Row(table)to see the its HTML Data.

                </b>

              </TD>

            

            </TR>

            <TR>

              <TD>

                <b>

                  Move Mouse over the <IMG SRC="image/ArrowHead.gif"  WIDTH="9" HEIGHT="14"></IMG> image to view the popup menu(div).

                </b>

                <i>Currently the Menu(div) is Non-functional.</i>

                <i>Implementation of Menu's functionality (edit/delete) is left to the imagination

                of the reader.Source code is downloadable </i>

              </TD>

            </TR>

            <TR>

              <TD>

                <b>

                  Max Records displayed/Page specified in Xml2HTML.xsl(param:<i>valMaxRecordsPerPage</i>) : <xsl:value-of select="$valMaxRecordsPerPage"/>

                </b>

              </TD>

            </TR>

          </TABLE>

        <br></br>

        <label class="up" onClick="prevpg()">

          <b>

            <i>&lt;-- prev  page</i>

          </b>

        </label>

        <label class="down" onClick="nextpg()">

          <b>

            <i> next page --&gt;</i>

          </b>

        </label>

       

          <TABLE cellspacing="0" border="1" align="center"  id="root">

            <!--make the element name as header (A,B,C,D......)-->

            <xsl:if test="not($blnMakeHeaderFromElementName='false')">

              <TR>

                <xsl:for-each select="root/XLRow[1]" >

                  <TD width="12" class="header" onClick="BLOCKED SCRIPTalert(document.getElementById('root').outerHTML);" >

                    #

                  </TD>

                  <xsl:for-each select="./*" >

                    <TD class="header">

                      <xsl:attribute name="name" >

                        <xsl:value-of select="name()"/>

                      </xsl:attribute>

                      <xsl:attribute name="id">

                        <xsl:value-of select="concat(name(),string(position()))"/>

                      </xsl:attribute>

                      <xsl:value-of select="name()"/>

                    </TD>

                  </xsl:for-each>

                </xsl:for-each>

              </TR>

            </xsl:if>

            <!--make the First Row -cell Values as  header -->

            <xsl:if test="not($blnRetainFirstRowValAsHeader='false')">

              <TR >

                <xsl:for-each select="root/XLRow[1]" >

                  <TD width="12" class="rowHeader" onClick="BLOCKED SCRIPTalert('Implement Addition of New Rows to table -Onclick of + ');">

                    +

                  </TD>

                  <xsl:for-each select="./*" >

                    <TD class="rowHeader">

                      <xsl:attribute name="name">

                        <xsl:value-of select="name()"/>

                      </xsl:attribute>

                      <xsl:attribute name="id">

                        <xsl:value-of select="concat(name(),string(position()))"/>

                      </xsl:attribute>

 

                      <xsl:value-of select="."/>

                    </TD>

                  </xsl:for-each>

                </xsl:for-each>

              </TR>

            </xsl:if>

            <xsl:for-each select="root/XLRow" >

              <xsl:if test="(position() &gt; number($valStartRecordIndex) and position() &lt; (number($valMaxRecordsPerPage)+ number($valStartRecordIndex) + 1))">

                <xsl:choose>

                  <xsl:when test="(not($blnRetainFirstRowValAsHeader='false') and position()=1)">

                    <!--Do nothing & skip this row,because this row is already made as header-->

                  </xsl:when>

                  <xsl:otherwise>

                    <TR ondblClick="BLOCKED SCRIPTalert(this.outerHTML)" onClick="BLOCKED SCRIPTthis.style.backgroundColor='CCFF33';">

                      <xsl:attribute name="name">

                        <xsl:value-of select="string('XLRow')"/>

                      </xsl:attribute>

                      <xsl:attribute name="id" >

                        <xsl:value-of select="concat('TR',string(position()))"/>

                      </xsl:attribute>

                      <xsl:choose>

                        <xsl:when test="position() mod 2 = 0">

                          <xsl:attribute name="class">

                            <xsl:value-of select="string('rowEven')"/>

                          </xsl:attribute>

                        </xsl:when>

                        <xsl:otherwise>

                          <xsl:attribute name="class">

                            <xsl:value-of select="string('rowOdd')"/>

                          </xsl:attribute>

                        </xsl:otherwise>

                      </xsl:choose>

                      <TD width="12">

                        <IMG SRC="image/ArrowHead.gif"  WIDTH="9" HEIGHT="14" onMouseOver="BLOCKED SCRIPTFollow();" onMouseOut="BLOCKED SCRIPTBacktoOrigin();"  >

                          <xsl:attribute name="id">

                            <xsl:value-of select="concat('IMG',string(position()))"></xsl:value-of>

                          </xsl:attribute>

                        </IMG>

                        <xsl:value-of select="position()"/>

                      </TD>

                     

                          <xsl:for-each select="./*" >

                            <TD >

                              <xsl:attribute name="name">

                                <xsl:value-of select="name()"/>

                              </xsl:attribute>

                              <xsl:attribute name="id">

                                <xsl:value-of select="concat(name(),string(position()))"/>

                              </xsl:attribute>

                              <xsl:value-of select="."/>

                            </TD>

                          </xsl:for-each>

                       

 

                    </TR>

                  </xsl:otherwise>

                </xsl:choose>

              </xsl:if>

            </xsl:for-each>

          </TABLE>

</BODY>

</HTML>

</xsl:template>

</xsl:stylesheet>

 

 

 

 

 

 

The Parameters for the XSLT (above):

 

Parameter:

Description

 

valMaxRecordsPerPage

 

Maximum number of Records /Page to be displayed to the user.

Configure it to suit your needs; the default value is 10 records/Page.

 

 

 

valStartRecordIndex

 

The start index of records to be fetched. (0 is the default value)

 

 

 

* PortUsedbyCasiniServer

 

What is Cassini Web Server?

Cassini Web Server is a simple, light-weight, redistributable web server software capable of simultaneously running multiple ASP.NET applications and static HTML web sites on Microsoft Windows computers that either don't have IIS installed, or can't run IIS at all, like Windows XP Home Edition.

It runs/connects on a random port, which is displayed in the url.

 

For example:

 When you run the asp net application (downloadable) on your computer, the url resembles this :

http://localhost:1639/AsynchronousProcessing/SubmitLongProcess.aspx

Where the random port used by Cassini server is 1639 —

The value to this parameter (PortUsedbyCasiniServer) holds the random port used by the Cassini server when running the asp net application (downloadable) in your computer.

 

 

 

SourceFileName

 

The Generic xml file name(generated from Spreadsheet ML document)

 

 

blnMakeHeaderFromElementName

 

Add header to HTML table generated constituting the node names (Example A, B, C…) (Ref : Part 1)

 

 

blnRetainFirstRowValAsHeader

 

Add header to HTML table generated constituting the first node values. (Ref : Part 1)

 

 

 *   à Mandatory parameters for the application (downloadable) to run.

 While the rest of the parameters are optional (can use the default values).

 

First let’s look at the GUI of the downloadable asp net application:

 

GUI of the asp net application:

 

 

 

Note the Port number used by the Cassini server and update the corresponding parameter in “Xml2HTML.XSL”.

On click of “Convert2XML and Display as table with Paging” displays the transformed html page with a table holding the number of records specified for paging. It also shows links for “Next/prev Page” and a few guidelines to the user.

 

 

HTML Page generated:

 

 

 

On Click of Next/prev page link, an xmlhttp request is sent to the server, which then fetches the next set of records.

 

Submit using XmlHttp:

 

 

Receive Next set of records using XmlHttp :

 

 

Display next set of Records:

 

 

:

 

The XSLT used for fetching  the HTML table containing the next/prev set of records from the server is

 

Xml2HTMLTable.XSL :

 

 

<?xml version="1.0" ?>

<xsl:stylesheet version="1.0"

    xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >

  <xsl:output method="xml" omit-xml-declaration="yes" />

  <xsl:param name="blnMakeHeaderFromElementName">true</xsl:param>

  <xsl:param name="blnRetainFirstRowValAsHeader">true</xsl:param>

  <xsl:param name="valStartRecordIndex">0</xsl:param>

  <xsl:param name="valMaxRecordsPerPage">0</xsl:param>

    <xsl:template match="/">

    <TABLE cellspacing="0" border="1" align="center"  id="root">

      <!--make the element name as header (A,B,C,D......)-->

      <xsl:if test="not($blnMakeHeaderFromElementName='false')">

        <TR>

          <xsl:for-each select="root/XLRow[1]" >

           

            <TD width="12" class="header" onClick="BLOCKED SCRIPTalert(document.getElementById('root').outerHTML);">

              #

            </TD>

            <xsl:for-each select="./*" >

              <TD class="header">

                <!--<xsl:attribute name="onClick">

                  sort('<xsl:value-of select="name()"/>')

                </xsl:attribute>-->

                <xsl:attribute name="name">

                  <xsl:value-of select="name()"/>

                </xsl:attribute>

                <xsl:attribute name="id">

                  <xsl:value-of select="concat(name(),string(position()))"/>

                </xsl:attribute>

                <xsl:value-of select="name()"/>

              </TD>

            </xsl:for-each>

          </xsl:for-each>

        </TR>

      </xsl:if>

      <xsl:if test="not($blnRetainFirstRowValAsHeader='false')">

        <TR onClick="BLOCKED SCRIPTalert(this.outerHTML);">

          <xsl:for-each select="root/XLRow[1]" >

            <TD width="12" class="rowHeader" onClick="BLOCKED SCRIPTalert('Implement Addition of New Rows to table -Onclick of + ');">

              +

            </TD>

            <xsl:for-each select="./*" >

              <TD class="rowHeader">

                <xsl:attribute name="name">

                  <xsl:value-of select="name()"/>

                </xsl:attribute>

                <xsl:attribute name="id">

                  <xsl:value-of select="concat(name(),string(position()))"/>

                </xsl:attribute>

                <xsl:value-of select="."/>

              </TD>

            </xsl:for-each>

          </xsl:for-each>

        </TR>

      </xsl:if>

 

      <xsl:for-each select="root/XLRow" >

        <xsl:choose>

          <xsl:when test="(not($blnRetainFirstRowValAsHeader='false') and position()=1)">

            <!--Do nothing & skip this row,because this row is already made as header-->

          </xsl:when>

          <xsl:otherwise>

            <xsl:if test="(position() &gt; number($valStartRecordIndex) and position() &lt; (number($valMaxRecordsPerPage)+ number($valStartRecordIndex)+1))">

              <TR ondblClick="BLOCKED SCRIPTalert(this.outerHTML)" onClick="BLOCKED SCRIPTthis.style.backgroundColor='CCFF33';">

                <xsl:attribute name="name">

                  <xsl:value-of select="string('XLRow')"/>

                </xsl:attribute>

                <xsl:attribute name="id" >

                  <xsl:value-of select="concat('TR',string(position()))"/>

                </xsl:attribute>

                <xsl:choose>

                  <xsl:when test="position() mod 2 = 0">

                    <xsl:attribute name="class">

                      <xsl:value-of select="string('rowEven')"/>

                    </xsl:attribute>

                  </xsl:when>

                  <xsl:otherwise>

                    <xsl:attribute name="class">

                      <xsl:value-of select="string('rowOdd')"/>

                    </xsl:attribute>

                  </xsl:otherwise>

                </xsl:choose>

                <TD width="12">

                  <IMG SRC="image/ArrowHead.gif"  WIDTH="9" HEIGHT="14" onMouseOver="BLOCKED SCRIPTFollow();" onMouseOut="BLOCKED SCRIPTBacktoOrigin();" >

                    <xsl:attribute name="id">

                      <xsl:value-of select="concat('IMG',string(position()))"></xsl:value-of>

                    </xsl:attribute>

                  </IMG>

                  <xsl:value-of select="position()"/>

                </TD>

                    <xsl:for-each select="./*" >

                      <TD >

                        <xsl:attribute name="name">

                          <xsl:value-of select="name()"/>

                        </xsl:attribute>

                        <xsl:attribute name="id">

                          <xsl:value-of select="concat(name(),string(position()))"/>

                        </xsl:attribute>

                        <xsl:value-of select="."/>

                      </TD>

                    </xsl:for-each>

              </TR>

            </xsl:if>

          </xsl:otherwise>

        </xsl:choose>

      </xsl:for-each>

    </TABLE>

</xsl:template>

</xsl:stylesheet>

 

The parameters for this XSLT (Xml2HTMLTable.XSL) like valStartRecordIndex & valMaxRecordsPerPage are automatically updated by the javascript functions, on click of Next/Prev links in the HTML page (generated using Xml2HTML.XSL) and are sent to server using these updated parameters, the HTML table containing the next/prev set of records is generated in the server and sent back to client through xmlHttp.

 

That’s all guys, Happy sharing the article with you!

Attachment: Dowloads.zip
Page 1 of 1 (3 items)