How to generate Excel file from XML using XSLT

Hi Guys,

Today, I got a new task to generate customised MS Excel file using XSLT where the source data is available in XML file. I have an experience in creating the HTML and PDF files using XSLT. But never heard of creating the Excel. After googling, I found out that SpreadsheetML is a Markup Laungage used to write the XSLT transformation for creating the Excel files. You can use WordProcessingML to create Word document out of XML.

SpreadsheetML is defined by Microsoft people. You just need to mention in XSLT that you are using MS office spreadsheet functions by defining the different namespaces in the XSL declaration (in the first line).
Then define the Workbook properties and styles which can be used for the work sheets.

When you executed the XSLT, the output is generated. Save the output with the extension as .xls and open it in Excel. You could see the file.

Raw XML:

<companies name="My Companies">
    <company>
        <companyName>Cool Beans</companyName>
        <serviceID>1</serviceID>
        <startDate>01-01-2014 00:00:00</startDate>
        <endDate>01-02-2014 00:00:00</endDate>
        <hours>2</hours>
    </company>
    <company>
        <companyName>Hot Beans</companyName>
        <serviceID>2</serviceID>
        <startDate>01-01-2014 00:00:00</startDate>
        <endDate>01-02-2014 00:00:00</endDate>
        <hours>2</hours>
    </company>
    <company>
        <companyName>Evil Beans</companyName>
        <serviceID>3</serviceID>
        <startDate>01-03-2014 00:00:00</startDate>
        <endDate>01-04-2014 00:00:00</endDate>
        <hours>2</hours>
    </company>
</companies>

XSLT File:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0" xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
 <xsl:output method="xml" indent="yes"/>
 <xsl:template match="/">
  <xsl:processing-instruction name="mso-application">progid="Excel.Sheet"</xsl:processing-instruction>
  <Workbook>
   <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
    <WindowHeight>10005</WindowHeight>
    <WindowWidth>10005</WindowWidth>
    <WindowTopX>120</WindowTopX>
    <WindowTopY>135</WindowTopY>
    <ProtectStructure>False</ProtectStructure>
    <ProtectWindows>False</ProtectWindows>
   </ExcelWorkbook>
   <Styles>
    <Style ss:ID="head_cell_style">
     <Font ss:Bold="1" x:Family="Swiss"/>
     <Borders>
      <Border ss:LineStyle="Continuous" ss:Position="Left" ss:Weight="1"/>
      <Border ss:LineStyle="Continuous" ss:Position="Top" ss:Weight="1"/>
     </Borders>
     <Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>
     <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
    </Style>
    <Style ss:ID="solid_row_style">
     <Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>
     <Borders>
      <Border ss:LineStyle="Continuous" ss:Position="Left" ss:Weight="1"/>
      <Border ss:LineStyle="Continuous" ss:Position="Right" ss:Weight="1"/>
     </Borders>
    </Style>
    <Style ss:ID="solid_row_top_style">
     <Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>
     <Borders>
      <Border ss:LineStyle="Continuous" ss:Position="Left" ss:Weight="1"/>
      <Border ss:LineStyle="Continuous" ss:Position="Right" ss:Weight="1"/>
      <Border ss:LineStyle="Continuous" ss:Position="Top" ss:Weight="1"/>
     </Borders>
    </Style>
    <Style ss:ID="value_row_style">
     <Borders>
      <Border ss:LineStyle="Continuous" ss:Position="Left" ss:Weight="1"/>
      <Border ss:LineStyle="Continuous" ss:Position="Right" ss:Weight="1"/>
      <Border ss:LineStyle="Continuous" ss:Position="Top" ss:Weight="1"/>
      <Border ss:LineStyle="Continuous" ss:Position="Bottom" ss:Weight="1"/>
     </Borders>
    </Style>
   </Styles>
   <xsl:apply-templates select="companies"/>
  </Workbook>
 </xsl:template>
 <xsl:template match="companies">
  <Worksheet ss:Name="{@name}">
   <Table ss:DefaultRowHeight="13.2" ss:ExpandedColumnCount="5" x:FullColumns="1" x:FullRows="1">
    <Column ss:Index="1" ss:Width="100"/>
    <Column ss:Index="2" ss:Width="100"/>
    <Column ss:Index="3" ss:Width="100"/>
    <Column ss:Index="4" ss:Width="100"/>
    <Column ss:Index="5" ss:Width="100"/>
    <Row>
     <!-- Header Row -->
     <xsl:apply-templates select="company[1]/*" mode="headers"/>
    </Row>
    <xsl:apply-templates select="company"/>
    <Row>
     <!-- Last Row -->
     <Cell ss:Index="4"  ss:StyleID="value_row_style">
      <Data ss:Type="String">Total:</Data>
     </Cell>
     <Cell ss:Formula="=SUM(R[-{count(company)}]C:R[-1]C)"  ss:StyleID="value_row_style">
      <Data ss:Type="Number"/>
     </Cell>
    </Row>
   </Table>
  </Worksheet>
 </xsl:template>
 <xsl:template match="company[1]/*" mode="headers">
  <Cell ss:StyleID="head_cell_style">
   <Data ss:Type="String">
    <xsl:value-of select="name()"/>
   </Data>
  </Cell>
 </xsl:template>
 <xsl:template match="company">
  <Row>
   <xsl:apply-templates select="*"/>
  </Row>
 </xsl:template>
 <xsl:template match="companyName|serviceID|startDate|endDate">
  <Cell  ss:StyleID="value_row_style">
   <Data ss:Type="String">
    <xsl:value-of select="."/>
   </Data>
  </Cell>
 </xsl:template>
 <xsl:template match="hours">
  <Cell>
   <Data ss:Type="Number">
    <xsl:value-of select="."/>
   </Data>
  </Cell>
 </xsl:template>
</xsl:stylesheet>

Generated Ouput.xls:

<?xml version="1.0" encoding="UTF-8"?><?mso-application progid="Excel.Sheet"?><Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>10005</WindowHeight>
  <WindowWidth>10005</WindowWidth>
  <WindowTopX>120</WindowTopX>
  <WindowTopY>135</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="head_cell_style">
   <Font ss:Bold="1" x:Family="Swiss"/>
   <Borders>
    <Border ss:LineStyle="Continuous" ss:Position="Left" ss:Weight="1"/>
    <Border ss:LineStyle="Continuous" ss:Position="Top" ss:Weight="1"/>
   </Borders>
   <Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>
   <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
  </Style>
  <Style ss:ID="solid_row_style">
   <Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>
   <Borders>
    <Border ss:LineStyle="Continuous" ss:Position="Left" ss:Weight="1"/>
    <Border ss:LineStyle="Continuous" ss:Position="Right" ss:Weight="1"/>
   </Borders>
  </Style>
  <Style ss:ID="solid_row_top_style">
   <Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>
   <Borders>
    <Border ss:LineStyle="Continuous" ss:Position="Left" ss:Weight="1"/>
    <Border ss:LineStyle="Continuous" ss:Position="Right" ss:Weight="1"/>
    <Border ss:LineStyle="Continuous" ss:Position="Top" ss:Weight="1"/>
   </Borders>
  </Style>
  <Style ss:ID="value_row_style">
   <Borders>
    <Border ss:LineStyle="Continuous" ss:Position="Left" ss:Weight="1"/>
    <Border ss:LineStyle="Continuous" ss:Position="Right" ss:Weight="1"/>
    <Border ss:LineStyle="Continuous" ss:Position="Top" ss:Weight="1"/>
    <Border ss:LineStyle="Continuous" ss:Position="Bottom" ss:Weight="1"/>
   </Borders>
  </Style>
 </Styles>
 <Worksheet ss:Name="My Companies">
  <Table ss:DefaultRowHeight="13.2" ss:ExpandedColumnCount="5" x:FullColumns="1" x:FullRows="1">
   <Column ss:Index="1" ss:Width="100"/>
   <Column ss:Index="2" ss:Width="100"/>
   <Column ss:Index="3" ss:Width="100"/>
   <Column ss:Index="4" ss:Width="100"/>
   <Column ss:Index="5" ss:Width="100"/>
   <Row>
    <Cell ss:StyleID="head_cell_style">
     <Data ss:Type="String">companyName</Data>
    </Cell>
    <Cell ss:StyleID="head_cell_style">
     <Data ss:Type="String">serviceID</Data>
    </Cell>
    <Cell ss:StyleID="head_cell_style">
     <Data ss:Type="String">startDate</Data>
    </Cell>
    <Cell ss:StyleID="head_cell_style">
     <Data ss:Type="String">endDate</Data>
    </Cell>
    <Cell ss:StyleID="head_cell_style">
     <Data ss:Type="String">hours</Data>
    </Cell>
   </Row>
   <Row>
    <Cell ss:StyleID="value_row_style">
     <Data ss:Type="String">Cool Beans</Data>
    </Cell>
    <Cell ss:StyleID="value_row_style">
     <Data ss:Type="String">1</Data>
    </Cell>
    <Cell ss:StyleID="value_row_style">
     <Data ss:Type="String">01-01-2014 00:00:00</Data>
    </Cell>
    <Cell ss:StyleID="value_row_style">
     <Data ss:Type="String">01-02-2014 00:00:00</Data>
    </Cell>
    <Cell>
     <Data ss:Type="Number">2</Data>
    </Cell>
   </Row>
   <Row>
    <Cell ss:StyleID="value_row_style">
     <Data ss:Type="String">Hot Beans</Data>
    </Cell>
    <Cell ss:StyleID="value_row_style">
     <Data ss:Type="String">2</Data>
    </Cell>
    <Cell ss:StyleID="value_row_style">
     <Data ss:Type="String">01-01-2014 00:00:00</Data>
    </Cell>
    <Cell ss:StyleID="value_row_style">
     <Data ss:Type="String">01-02-2014 00:00:00</Data>
    </Cell>
    <Cell>
     <Data ss:Type="Number">2</Data>
    </Cell>
   </Row>
   <Row>
    <Cell ss:StyleID="value_row_style">
     <Data ss:Type="String">Evil Beans</Data>
    </Cell>
    <Cell ss:StyleID="value_row_style">
     <Data ss:Type="String">3</Data>
    </Cell>
    <Cell ss:StyleID="value_row_style">
     <Data ss:Type="String">01-03-2014 00:00:00</Data>
    </Cell>
    <Cell ss:StyleID="value_row_style">
     <Data ss:Type="String">01-04-2014 00:00:00</Data>
    </Cell>
    <Cell>
     <Data ss:Type="Number">2</Data>
    </Cell>
   </Row>
   <Row>
    <Cell ss:Index="4" ss:StyleID="value_row_style">
     <Data ss:Type="String">Total:</Data>
    </Cell>
    <Cell ss:Formula="=SUM(R[-3]C:R[-1]C)" ss:StyleID="value_row_style">
     <Data ss:Type="Number"/>
    </Cell>
   </Row>
  </Table>
 </Worksheet>
</Workbook>

Output in Excel:

How to sort date in jQuery datatable

Hi Guys,

I got a task today to implement the date sorting in jQuery Data table. Name and plain number sorting are available in jQuery by default. But, there is nothing available in jQuery to sort the dates as the dates can be in any format. I have picked up an example where the date format is dd/mm/yyyy.

Perform the following steps to achieve custom sorting:
1) Need to tell datatable that the particular column is going to implement the custom sorting by defining sType in the aoColumns attribute.
2) Then implement both the ascending and descending methods to perform the sorting in the respective orders.

Data Table definition:

var newTable = $('#newReqTable').dataTable({
  "bProcessing" : true,
  "bAutoWidth" : true,
  "bPaginate" : true,
  "bInfo" : false,
  "sScrollY":"450px",
  "sScrollX":"100%", 
  "bScrollCollapse":true,
  "aoColumns": [
                { "sType": "ukdate" },
                null,
                null,
                null,
                null
            ]
 });

Add method for Ascending order:

When the strings a and b are formed, make sure that it should be concatenated likewise Year+Month+Date. This is because, the date format for US is MM/DD/YYYY whereas UK is DD/MM/YYYY.

jQuery.fn.dataTableExt.oSort['ukdate-asc']  = function(a,b) {
  
     var usDatea = a.split('-'); 
     var usDateb = b.split('-'); 

     var x = (usDatea[2] + usDatea[1] + usDatea[0]) * 1;
     var y = (usDateb[2] + usDateb[1] + usDateb[0]) * 1;
     console.log('ASC - first - '+a+', second - '+b+', X - '+x+',Y - '+y);
     return ((x < y) ? -1 : ((x > y) ?  1 : 0));
 };

Add method for Descending order:

jQuery.fn.dataTableExt.oSort['ukdate-desc'] = function(a,b) {
  
  var usDatea = a.split('-'); 
     var usDateb = b.split('-'); 


     var x = (usDatea[2] + usDatea[1] + usDatea[0]) * 1;
     var y = (usDateb[2] + usDateb[1] + usDateb[0]) * 1;
     console.log('DESCCCC - first - '+a+', second - '+b+', X - '+x+',Y - '+y);
     return ((x < y) ? 1 : ((x > y) ?  -1 : 0));
 };

References:

From Datatable

Example on Time sorting

Different way of Sorting

How to display the folder structures like a tree structure in Unix

I found the following command to display the folder structure of a project or any folders in a tree-like structure.

find . -type d -print | sed -e 's;[^/]*/;|____;g;s;____|; |;g'

Referred from here...

Plugins not shown in Updated or Available sections of Jenkins CI tool

I have seen cases where the plugins are not displayed under the tabs Update and Available. It can be achieved in any of the following ways.

Prerequisite:
Make sure that you add the JDK, Ant and Maven installed/referred under the Manage Jenkins ---> Configure System.

Fix:
1) Go to: Manage Jenkins ---> Manage Plugins ---> Advanced, then click Check Now in the bottom right-hand corner.
2) See if the textbox is filled with the entry http://updates.jenkins-ci.org/update-center.json in Manage Jenkins ---> Manage Plugins ---> Advanced ---> Update Site

Increase the Memory in Tomcat

Just add the following contents to the file located in \bin\catalina.bat (for Windows) or \bin\catalina.sh (for unix). Increase the -Xmx... and permGen setting based on your memory capacity and application needs.

JAVA_OPTS="-Djava.awt.headless=true -Dfile.encoding=UTF-8 -server -Xms1536m -Xmx1536m -XX:NewSize=256m -XX:MaxNewSize=256m -XX:PermSize=256m -XX:MaxPermSize=256m -XX:+DisableExplicitGC"

Other way of doing is:
Create a file named setenv.bat and place it in %CATALINA_HOME%\bin\
And then add the following content to the file

set JAVA_OPTS=-Dfile.encoding=UTF-8 -Xms128m -Xmx1024m -XX:PermSize=64m -XX:MaxPermSize=256m

Tomcat will execute the file setenv.bat if its there.

How to remove files recursively in Git

A tool used to generate .bak files when it is used for comparing two files. One way to fix this is to mentioned the extension .bak in .gitignore file. The other way is to manually find and the remove the files recrusively

find . | grep .bak | xargs rm -rf

How to debug Angular JS using Batarang

Batarang is a tool used to debug Angular JS application. There is a plugin available for Chrome, which can be downloaded from Batarang

The detailed explanation on how to use the tool is available in Youtube - AngularJS-Batarang Debugger

How to left pad zeros for a column in Sql Server

I had a requirement where I need to left pad zeros for a column in MS SQL Server. I thought the below methods would be helpful for the concerned.

select empid,          
       replicate('0', 6 - len(salary)) + cast (salary as varchar) as salary
from   emp;  

empid       salary
----------- -----------
1           000300
2           000030
3           500000
4           001000

(4 row(s) affected)

How to implement the HTTPS protocol in jboss for the web application

By default, all the web applications deployed on any App/Web server will be accessed through HTTP protocol. HTTPS protocol is same as HTTP, but with security implemented in it. HTTPS is a secured protocol, used to make a secured login. To achieve this, we need to create a self-signed certificate or use the third party/vendor created certificates and use it in your applications.

Add or modify the following piece of code into the standalone.xml file

<subsystem xmlns="urn:jboss:domain:web:1.1" default-virtual-server="default-host" native="false">
 <connector name="https" protocol="HTTP/1.1" scheme="https" socket-binding="https" secure="true">
  <ssl password="ppcseportal" certificate-key-file="C:\Temp\certs\ks" verify-client="false" certificate-file="C:\Temp\certs\ks"/>
 </connector>
 <virtual-server name="default-host" enable-welcome-root="true">
  <alias name="localhost"/>
  <alias name="abc.com.au"/>
  <alias name="example.com"/>
 </virtual-server>
</subsystem>

Application logging is not working in jBoss

Custom application logging was not creating a separate log file, but instead it was writing in the jBoss logging.

The default behavior of jBoss is that the custom log or stdout everything goes into the jBoss server logging or on the console. The only way to say to the server not to write our logging into the console, add the following piece of code in the Web project.

Create a jboss-deployment-structure.xml with the following content and place it in the META-INF/ directory if you are deploying an EAR or in either the META-INF/ or WEB-INF/ directory if you are deploying a WAR. Add the following content into the above file.

<jboss-deployment-structure xmlns="urn:jboss:deployment-structure:1.2">
    <deployment>
        <!-- Exclusions allow you to prevent the server from automatically adding some dependencies -->
        <exclusions>
            <module name="org.apache.log4j" />
        </exclusions>
    </deployment>
</jboss-deployment-structure>

Include the log4j.properties or log4j.xml file in the src/ directory in your deployment (to make sure its available in the classpath).
Deploy your application.

java.lang.IllegalStateException: More than the maximum number of request parameters (GET plus POST) in jBoss AS 7.1

Following error occurs while submitting the request in jBoss AS 7.1.

java.lang.IllegalStateException: More than the maximum number of request parameters (GET plus POST) for a single request ([512]) were detected. Any parameters beyond this limit have been ignored. To change this limit, set the maxParameterCount attribute on the Connector.

Explanation: jBoss limits the number of parameters to be sent to the server. The default value for the number of parameters is 512. I had a scenario where the number of fields were getting increased because of the business, which I could not control. I made the configuration to accept more number of parameters. Add the following piece of code in the standalone.xml file.

<extensions> ....
</extensions>
 <system-properties>
        <property name="org.apache.tomcat.util.http.Parameters.MAX_COUNT" value="2000"/>
  </system-properties>
<management> ....
</management>

How to insert code into blogger

Programmers know that blogger doesn't support code insertion (usefull if you need to demonstrate how to program something by copy/pasting code samples). Thus, you need to swicth  Blogger's editor to HTML and paste HTMLized text.

The nicest solution I found to format your code to HTML is to use this online code formatter : http://hilite.me/

import org.springframework.stereotype.Controller;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RequestParam;

import org.springframework.web.bind.annotation.ResponseBody;

@Controller

public class Home {

 @RequestMapping("/hello")

 public @ResponseBody

 String hello(@RequestParam(value = "name") String name,

   @RequestParam(value = "gender") String gender,

   @RequestParam(value = "email") String email,

   @RequestParam(value = "phone") String phone,

   @RequestParam(value = "city") String city) {

  System.out.println(name);

  System.out.println(gender);

  System.out.println(email);

  System.out.println(phone);

  System.out.println(city);

  String str = "{\"user\": { \"name\": \"" + name + "\",\"gender\": \""

    + gender + "\",\"email\": \"" + email + "\",\"phone\": \""

    + phone + "\",\"city\": \"" + city + "\"}}";

  return str;

 }

}