How to modify the table structure in SQL Server

Once the table is created in SQL Server, you can modify the table structure either through GUI or through script. I am going to list down the possible cases of updating the table structure

Add a column:

ALTER TABLE "TABLE-NAME" ADD "COLUMN-NAME" varchar(50) NULL

Modify the Data Type:

ALTER TABLE "TABLE-NAME" ALTER COLUMN "COLUMN-NAME" "NEW DATA TYPE" NULL

Modify the NOT-NULL Constraint:

ALTER TABLE "TABLE-NAME" ALTER COLUMN "COLUMN-NAME" VARCHAR(30) NOT NULL

Left or Right Padding a column in SQL Server

I had a task to retrieve a number column and do left padding with zeros. The total number of digit is 7. I used the REPLICATE method to achieve this.

For example, the empNum in an Employee table is 123 and I want to do the left padding with zeros thereby totalling it to be with 7 digits. It should look like 0000123.

Select REPLICATE('0',7 - LEN(empNum) + CAST (empNum AS varchar) as EmpNum from Employee

How to find a String that starts/ends with in jQuery

There are several ways to find a String that startswith or endswith the specified value. I have used the regular expression

if (str.match("^Hello")) {
// ...
}

if (str.match("World$")) {
// ...
}

How to disable a click event on an element in jQuery/CSS

There are number of ways to bind the click event to an element. We can use off() or unbind() method provided by jQuery remove the click event. We can also do it using CSS

Add the following class to the CSS file. The particular property of the element can be added with this class for certain business conditions.

CSS:

.inactiveLink {
pointer-events: none;
cursor: default;
}

jQuery:

$("a[id=submit]").addClass('inactiveLink');

How to load the environment specific property files

When we work on the industrywise development, there will be multiple environments such as Dev, Test, UAT and Production. Some of the configurations would be specific to environments such as folder location and logger file.

There are multiple ways to achieve this. One of the easiest way is to add the environment variable in the respective machine and add the property file in the project.

We will first configure for Dev environment and which can be used for all other boxes.

1) Add the Environment variable by any one of the ways
Right click on MyComputer --> Properties --> Advanced Properties --> Environment Variable --> Add the entry ENVIRONMENT and value as dev
OR
Enter the following command in Command Line. Set ENVIRONMENT=dev

2) Add the property files likewise in project\resources. dev-env-config.properties,test-env-config.properties,uat-env-config.properties and prod-env-config.properties

3) Use the method System.getEnv("ENVIRONMENT") to get the specific environment

4) Complete java code to access the property is below

public class EnvironmentProperties {
 private static Properties props = new Properties();

 private static Properties load() {
  ClassLoader classLoader = Thread.currentThread().getContextClassLoader();
  String env = System.getenv("ENVIRONMENT");
  System.out.println("Environment ---  " + env);
  try {
   props.load(classLoader.getResourceAsStream("resources/" + env + "-env-config.properties"));
  } catch (IOException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  return props;
 }

 public static String getProp(String key) {
  load();
  return props.getProperty(key);
 }
}

How to redirect to a JSP page when the session is timed out- using jQuery

Session time out, when the web page is idle for number of minutes. If the user has not performed any operation on a web page, then the server should kick out the user in order to maintain the security. It can be achieved by mentioning the session-config attribute in web.xml and writing Filters or Interceptors. If the web page is timed out, then the current web page will not indicate that it is timed out. If you perform any operation on the idle page, it will take you to the Login page according to the implementation in Filter or Interceptor.

Some users may want to explicitly be alerted if the session is timed out. It can be achieved in jQuery.

Steps:
1) Write the mouse or keyboard listener and if there is any operation then change the lastActivity to the current time
2) Write a function to check for the idle time for every second
3) If the idle time more than certain minute, then redirect to session time out page

Main.JSP

<head>
<script type="text/javascript">
$(document).ready(function(){
 var lastActivity = new Date().getTime();
    var checkTimeout;
    var timeOut = 1000 * 16;
    checkTimeOut = function(){
        if(new Date().getTime() > lastActivity + timeOut){
            console.log('TIMED OUTTUUUUU');
            // redirect to timeout page
            //$(window.location).attr('href','/WebContent/pages/mat.html');
            window.location='/pages/sessionTimeOut.jsp';
        }else{
            console.log('Calling timeout again');
            window.setTimeout(checkTimeOut, 1000); // check once per second
        }
    }
 
    $('html').mousemove(function(event){
     lastActivity = new Date().getTime();
        //console.log("mouse move X:"+event.pageX+" Y:"+event.pageY);
    });
    $('html').click(function(event){
     lastActivity = new Date().getTime();
       console.log("mouse click X:"+event.pageX+" Y:"+event.pageY);
    });
    $('html').keyup(function(event){
     lastActivity = new Date().getTime();
       console.log("keyboard event: key pressed "+event.keyCode);
    });
    checkTimeOut();
});
</script>
</head>

sessionTimeOut.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
 pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<link href="../css/bootstrap.css" rel="stylesheet" type="text/css" />
<link href="../css/main.css" rel="stylesheet" type="text/css" />
<title>Session timed out</title>
</head>
<body>
 <div class="row">
  <div class="navbar navbar-default navbar-fixed-top"></div>
 </div>
 <div class="page-header col-md-12">
  <h1>Session timed out</h1>
 </div>
 <div class="container">
  <div class="col-md-12">
   <h4>
    Please <u><a href="http://storesonline.devbox/">Click
      Here... </a> </u>to login again
   </h4>
  </div>
 </div>

</body>
</html>

How to print web page automatically in Java

There are different ways of printing the web page such as using windows printing service. If you need to customize web page like you want to create a table or extract certain content and the final page to be printed, you need to follow the steps.

I initially created an HTML page out of Java (POJO) using Freemarker
Generated PDF out of HTML file using iText, FlyingSaucer Library. FlyingSaucer is required as it is applying external or internal CSS styles attached in the HTML file.
Call the defaultPrinter attached to the server and then print it.


Create HTML Page:

It creates output.html page

public void processFreeMarker() throws IOException, TemplateException {
  
  Configuration cfg = new Configuration();

  
  cfg.setClassForTemplateLoading(this.getClass(), "templates");
  cfg.setDefaultEncoding("UTF-8");
  cfg.setLocale(Locale.US);
  cfg.setTemplateExceptionHandler(TemplateExceptionHandler.RETHROW_HANDLER);

  Map<String, Object> input = new HashMap<String, Object>();

  input.put("title", "Vogella example");

  input.put("exampleObject", new ValueExampleObject("Java object", "me"));

  List<ValueExampleObject> systems = new ArrayList<ValueExampleObject>();
  systems.add(new ValueExampleObject("Android", "Google"));
  systems.add(new ValueExampleObject("iOS States", "Apple"));
  systems.add(new ValueExampleObject("Ubuntu", "Canonical"));
  systems.add(new ValueExampleObject("Windows7", "Microsoft"));
  input.put("systems", systems);

  // 2.2. Get the template

  Template template = cfg.getTemplate("helloworld.ftl");

  // 2.3. Generate the output

  // Write output to the console
  Writer consoleWriter = new OutputStreamWriter(System.out);
  template.process(input, consoleWriter);

  // For the sake of example, also write output into a file:
  Writer fileWriter = new FileWriter(new File("output.html"));
  try {
   template.process(input, fileWriter);
  } finally {
   fileWriter.close();
  }

 }

Generate PDF:

public void printPdf() throws DocumentException, IOException {
  String url = new File(HTML).toURI().toURL().toString();
  OutputStream os = new FileOutputStream(PDF);

  ITextRenderer renderer = new ITextRenderer();
  renderer.setDocument(url);
  renderer.layout();
  renderer.createPDF(os);

  os.close();
 }

Print PDF:

private void print(String fileName) throws CFAException {

  // get the printer service by printer name
  PrintService pss = PrintServiceLookup.lookupDefaultPrintService();
  System.out.println("********* PSSSS ******** " + pss);
  System.out.println("Printer - " + pss.getName());
  DocPrintJob job = pss.createPrintJob();
  DocAttributeSet das = new HashDocAttributeSet();
  Doc document;
  try {
   document = new SimpleDoc(new FileInputStream(new File(fileName)), DocFlavor.INPUT_STREAM.AUTOSENSE, das);

   PrintRequestAttributeSet pras = new HashPrintRequestAttributeSet();
   // job.setPrintService(pss);
   // job.setPrintable(template);
   // if (job.printDialog()) {
   job.print(document, pras);
  } catch (FileNotFoundException e) {
   throw new CustomException("", "File to be printed is not found. Please contact System Administrator", e);
  } catch (PrintException e) {
   throw new CustomException("", "Error occured while printing. Please contact System Administrator", e);
  }
 }

Reference:
StackOverflow.com

Oracle-Java Print Services


Maven Plugin:
Flying saucer 9.0.0 maven plugin is not working. So, use the following plugins to create PDF using Flying Saucer

<dependency>
   <groupId>org.xhtmlrenderer</groupId>
   <artifactId>core-renderer</artifactId>
   <version>R8pre2</version>
  </dependency>
  <dependency>
   <groupId>com.lowagie</groupId>
   <artifactId>itext</artifactId>
   <version>2.0.8</version>
  </dependency>

Supporting Libraries:
freemarker-2.3.19.jar
itext-4.2.1.jar
core-renderer-R8-final.jar
com.lowagie.text-2.1.7.jar

Java.PrintServiceLookUp is not working in jBoss AS 7

I had a task to print a file as part of the web application. The app is deployed in jBoss AS 7.1.1. Java has given the ability to pull the network printers connected to the server by calling the following line.

PrintServiceLookup.lookupPrintServices()

You can also get the default printer connected to the server by using the code:

PrintServiceLookup.lookupDefaultPrintService()

When I use the above code in standalone java program, it pulls the printers and I was able to print it, but the same is not working while doing it through Web application. jBoss does not allow the java print services to look up on the printers connected to it. After doing analysis, I found out the following configuration solving the issues

1) Add extra tag in modules/sun/jdk/main/module.xml of jbossAs as mentioned: path name="sun/print"

<dependencies>
        <system export="true">
            <paths>
                <path name="com/sun/script/javascript"/>
                <path name="com/sun/jndi/dns"/>
                <path name="com/sun/jndi/ldap"/>
                <path name="com/sun/jndi/url"/>
                <path name="com/sun/jndi/url/dns"/>
                <path name="com/sun/security/auth"/>
                <path name="com/sun/security/auth/login"/>
                <path name="com/sun/security/auth/module"/>
                <path name="sun/misc"/>
                <path name="sun/io"/>
                <path name="sun/nio"/>
                <path name="sun/nio/ch"/>
                <path name="sun/security"/>
                <path name="sun/security/krb5"/>
                <path name="sun/util"/>
                <path name="sun/util/calendar"/>
                <path name="sun/util/locale"/>
                <path name="sun/security/provider"/>
                <path name="META-INF/services"/>
    
    <!-- Updated for implementing SAML  on 22nd June 2015-->
     <path name="javax/xml/crypto/dsig"/>
                 <path name="javax/xml/crypto"/>
                 <path name="javax/xml/crypto/dsig/dom"/>
                 <path name="javax/xml/crypto/dsig/keyinfo"/>
                 <path name="com/sun/org/apache/xml/internal/security/transforms/implementations"/>
                 <path name="org/jcp/xml/dsig/internal/dom"/>
     
     <!-- Updated for adding PrintServices to print the files from jBoss server-->
     <path name="sun/print"/>
            </paths>
            <exports>
                <include-set>
                    <path name="META-INF/services"/>
                </include-set>
            </exports>
        </system>
    </dependencies>

2) Open up resources.jar from your JRE, and extract META-INF/services/javax.print.PrintServiceLookup and copy to location modules/sun/jdk/main/service-loader-resources/META-INF/services of JbossAS.

3) Restart JBoss and run the application.

How to get the generated sequence/key after the insertion into DB

There is a table with multiple columns where one of the columns is an ID, which indicates the running sequence number. When a row is inserted into the Database, the DB will generate the sequential number and insert it. When the same operation is achieved through Java program and you want to use the generated sequence number for the consequence process in Java, you need to write the following code.

Connection con = getDBConnection();
Statement stmt = con.prepareStatement("Your SQL Query, Statement.RETURN_GENERATED_KEYS");
stmt.setString(1,"Input");

if(stmt.executeUpdate()>0)
{
    ResultSet keySet=stmt.getGeneratedKeys();
    if(keySet!=null && keySet.next())
    {
        id = keySet.getLong(1);
        keySet.close();
    }
}

How to implement SAML SSO in jBoss AS 7.1

SSO is nothing but Single-Sign-On, which is mostly required in the intranet applications where users are not required to enter their credentials, which will be used from the desktop login or AD. There are different way of implementing the SSO in the J2EE Web applications. You can use RSA and install the implemented product in the Web server and you can get all the details in the Payload of the URL. You can also use SAML to implement SSO. SAML is Security Assertion Markup Language. We are going to see how to implement SSO using SAML into jBoss AS 7.1

RedHat has released a separate plugin called PicketLink, through which you can implement the SSO. It has many features like authentication, authorization, social logins and the list is going on. PicketLink has released different version. Since jBoss has moved to EAP and Wildfly, latest version of PicketLink cannot be used for AS 7.1. We need to use the PicketLink-V2.0.2 for configuring the SAML in jBoss AS 7.1

Download the PicketLink sample files from here
jBoss AS 7.1.1 already comes with PicketLink and is available in the location - jboss\modules\org\picketlink.
Go to the folder named main in the above location and open module.xml file
Add a new line and comment the last but one line. Refer the below code

<module xmlns="urn:jboss:module:1.1" name="org.picketlink">
                             <resources>
                                      <resource-root path="picketlink-fed-2.0.2.Final.jar"/>
                                      <resource-root path="picketlink-bindings-2.0.2.Final.jar"/>
                                      <resource-root path="picketlink-bindings-jboss-2.0.2.Final.jar"/>
                                      <resource-root path="picketlink-trust-jbossws-2.0.2.Final.jar"/>
                             </resources>
                             <dependencies>
                                      <module name="javax.api"/>
                                      <module name="javax.security.auth.message.api"/>
                                      <module name="javax.security.jacc.api"/>
                                      <module name="javax.transaction.api"/>
                                      <module name="javax.xml.bind.api"/>
                                      <module name="javax.xml.stream.api"/>
                                      <module name="javax.servlet.api"/>
                                      <module name="org.jboss.common-core"/>
                                      <module name="org.jboss.logging"/>
                                      <module name="org.jboss.as.web"/>
                                      <module name="org.jboss.security.xacml"/>
                                      <module name="org.picketbox"/>
                                      <module name="javax.xml.ws.api"/>
                                      <module name="org.apache.log4j"/>
                                      <!-- <module name="org.apache.santuario.xmlsec"/> --> <!-- Comment this line out -->
                                      <module name="sun.jdk"/> <!-- Add this new module dependency -->
                             </dependencies>
                    </module>
- See more at: https://developer.jboss.org/wiki/HowToConfigurePicketLink202WithJBossAS711#sthash.yYySpqIH.dpuf

You need to update the sun.jdk module definition. Go to the location jboss\module\sun\jdk\main and open module.xml and add the following lines in the respective place

<module xmlns="urn:jboss:module:1.1" name="sun.jdk">
                         <resources>
                              ...
                         </resources>
                         <dependencies>
                              <system export="true">
                                   <paths>
                                        ...
                                        <!-- Add this lines -->
                                        <path name="javax/xml/crypto/dsig"/>
                                        <path name="javax/xml/crypto"/>
                                        <path name="javax/xml/crypto/dsig/dom"/>
                                        <path name="javax/xml/crypto/dsig/keyinfo"/>
                                        <path name="com/sun/org/apache/xml/internal/security/transforms/implementations"/>
                                        <path name="org/jcp/xml/dsig/internal/dom"/>                                       
                                   </paths>
                              </system>
                         </dependencies>
                    </module>         
- See more at: https://developer.jboss.org/wiki/HowToConfigurePicketLink202WithJBossAS711#sthash.yYySpqIH.dpuf

Add the following lines in the standalone.xml of the jBoss AS

<subsystem>
<security-domains>
<security-domain name="idp" cache-type="default">
                    <authentication>
                        <login-module code="UsersRoles" flag="required">
                            <module-option name="usersProperties" value="users.properties"/>
                            <module-option name="rolesProperties" value="roles.properties"/>
                        </login-module>
                    </authentication>
                </security-domain>
                <security-domain name="picketlink-sts" cache-type="default">
                    <authentication>
                        <login-module code="UsersRoles" flag="required">
                            <module-option name="usersProperties" value="sts-users.properties"/>
                            <module-option name="rolesProperties" value="sts-roles.properties"/>
                        </login-module>
                    </authentication>
                </security-domain>
                <security-domain name="sp" cache-type="default">
                    <authentication>
                        <login-module code="org.picketlink.identity.federation.bindings.jboss.auth.SAML2LoginModule" flag="required"/>
                    </authentication>
                </security-domain>
                <security-domain name="cache-test" cache-type="default">
                     <authentication>
                        <login-module code="org.picketlink.identity.federation.bindings.jboss.auth.SAML2STSLoginModule" flag="required">
                            <module-option name="password-stacking" value="useFirstPass"/>
                            <module-option name="configFile" value="sts-config.properties"/>
                            <module-option name="cache.invalidation" value="true"/>
                        </login-module>
                    </authentication>
                </security-domain>
            </security-domains>
</subsystem>

Unzip the files downloaded in the first step and place the WAR files in the deployment folder of the jBoss AS.

After restarting the server, hit the URL localhost:8080/employee. Enter the username and password as tomcat. The values are available in user.properties, part of idp.war file. IDP will perform the authentication and then allow it to Employee application.

How to convert HTML to PDF using iText

I got a new task today to generate a PDF out of HTML files. After doing some analysis on this, I found that there are couple of ways to achieve this.
1) You can convert the HTML to XSL-FO format and then generate the PDF using Apache XSL-FO
2) Generate PDF using iText libraries.

We are going to see how to generate PDF using iText lib. It can be achieved by using two different classes.

1) HTMLWorker (com.itextpdf.text.html.simpleparser.HTMLWorker) - Deprecated since v-5.5.2 - Java Doc

try {
    String k = "<html><body> Generate PDF using HTMLWorker Class </body></html>";
    OutputStream file = new FileOutputStream(new File("C:\\HtmlWorker.pdf"));
    Document document = new Document();
    PdfWriter.getInstance(document, file);
    document.open();
    HTMLWorker htmlWorker = new HTMLWorker(document);
    htmlWorker.parse(new StringReader(k));
    document.close();
    file.close();
} catch (Exception e) {
    e.printStackTrace();
}

2)XMLWorker (com.itextpdf.tool.xml.XMLWorker)

try {
    String k = "<html><body> Generate PDF using XML Worker</body></html>";
    OutputStream file = new FileOutputStream(new File("C:\\XMLWorker.pdf"));
    Document document = new Document();
    PdfWriter writer = PdfWriter.getInstance(document, file);
    document.open();
    InputStream is = new ByteArrayInputStream(k.getBytes());
    XMLWorkerHelper.getInstance().parseXHtml(writer, document, is);
    document.close();
    file.close();
} catch (Exception e) {
    e.printStackTrace();
}

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: