Wednesday, October 22, 2014

Comparing worksheets in older version of Excel

What would you do if there are two big Excel worksheets with different data types and you have to compare them quickly and efficiently? Indeed, there is the Sheet comparison feature in latest version of Excel (Excel 2013 and higher), but what if you have to make it available for many other testers who have older versions?
Well, many think that there is no way other than using VBA and creating Macros. I say, that would be my last choice. Excel macros are not anti-virus friendly and some users won't or even can't use macros because of that. Also many users have limited knowledge of Excel, hence we can't expect them to run or probable modify macros for their needs.
 I use macros only if cell data must get processed, converted and re-shaped to be comparable with the other sheet. If I have to create a macro, will try my best to make it as simple as possible, and accessible easy (probably by implementing buttons in the sheets).

There is another way, however, that won't need to use macros. Here I give you some examples of how we can compare two sheets using IF function in Excel Formulas.

As you know, with IF function we can examine some criteria and the set cell content based on that examination. for example we want to set A1 to True if  2+2=4 Else set the cell value to False:

Now assume we have Sheet1 and Sheet2 like this:


Name datetime Score
John 4/29/2014 0:00 17
John 4/29/2014 1:00 11
Mike 4/29/2014 2:00 19


John4/29/2014 0:0018
Mike4/29/2014 1:0012
Mike4/29/2014 2:0019

We can create the third Sheet to compare each row properly:

For normal textual content such as "Name" or even "Hour" column use a formula like this in topmost cell and drag it down to where you think maximum number of the rows of comparing sheets:
will be:

=IF(Sheet1!A1=Sheet2!A1,Sheet1!A1,CONCATENATE("False: ",Sheet1!A1," <> ",Sheet2!A1)

If you have date and time (datetime) in the column: You can even compare specific pieces of the Date/Time such as Hour, Day or Minute by formatting it as TEXT()
=IF(Sheet1!B1=Sheet2!B1,TEXT(Sheet1!B1,"m/d/yyy H:mm"),CONCATENATE("False: ",TEXT(Sheet1!B1,"m/d/yyy H:mm")," <> ",TEXT(Sheet2!B1,"m/d/yyy H:mm")))

The outcome will be something like:


Name datetime Score
John 4/29/2014 0:00 False: 17 <> 18
False: John <> Mike 4/29/2014 1:00 False: 11 <> 12
Mike False: 4/29/2014 2:00 <> 4/29/2015 2:00

It's almost done. Do you want it more professional and to be able to find diffs faster? use conditional formatting feature for all cells. Something like:" If Cell Value contains 'Fail' then Format it to this bg color".  Here is how it would look after:

Name datetime Score
John 4/29/2014 0:00 False: 17 <> 18
False: John <> Mike 4/29/2014 1:00 False: 11 <> 12
Mike False: 4/29/2014 2:00 <> 4/29/2015 2:00 9

This would help a lot for big worksheets, MUCH FASTER THAN MACROS!

Wednesday, October 15, 2014

A workaround to use JMS in LoadUI

As you know, LoadUI is not supporting JMS (yet, but it's been a long time).
I figured a way to integrate JMS in LoadUI without too much hassle. It's a simple groovy script that acts as JMS subscriber; and no, we can't use it directly in LoadUI runner, because it's groovy compiler is legacy (won't let to cast object to JNDI context).

It is also useful is some certain scenarios when JMeter is involved. Yes, JMeter is a very reliable tool to work with JMS but it has some flaws:
  1. JMeter doesn't provide Parallel processing. Therefore, if we have two Topics (for example, for Successful and Failed messages separately), then you have no choice unless to toggle between them every second till you get the response. This might work for Queues, but won't be the best way for Topics, since we need to listen to topics constantly (if they are remote and not durable) and then there is a chance to not receive the message because JMeter was listening to the wrong topic at that moment.
  2. JMeter consumes the queue messages. Hence we can't re-use the same message, neither we would be able to check the message queue later after the test. 
My solution keeps your messages in a database you won't lose them because of message consumption. You won't need JMS sampler and HermesJMS; Instead you'll use a jdbc connection to your own local database!

Thanks to sgilda,  here is a groovy code I've modified to read JMS messages:

import javax.naming.InitialContext;
import javax.jms.Connection;
import javax.jms.ConnectionFactory;
import javax.jms.Destination;
import javax.jms.MessageConsumer;
import javax.jms.MessageProducer;
import javax.jms.Session;
import javax.jms.TextMessage;
import javax.naming.Context;

//public class HelloWorldJMSClient {
    // Set up all the default values
    String DEFAULT_MESSAGE = "Hello, P :)!";
    String DEFAULT_CONNECTION_FACTORY = "ConnectionFactory";
    String DEFAULT_DESTINATION = "/EXECUTE"; //better to be a queue :)
    int waitTime = 5000;
    int count = 1; //number of messages we expect to read/write
    String DEFAULT_USERNAME = "";
    String DEFAULT_PASSWORD = "";
    String INITIAL_CONTEXT_FACTORY = "org.jnp.interfaces.NamingContextFactory";
    String PROVIDER_URL = "jnp://";
    String selector ='' // Given Selector
    //public static void main(String[] args) throws Exception {

        ConnectionFactory connectionFactory = null;
        Connection connection = null;
        Session session = null;
        MessageProducer producer = null;
        MessageConsumer consumer = null;
        Destination destination = null;
        TextMessage message = null;
        Context context = null;
        try {
            // Set up the context for the JNDI lookup
            final Properties env = new Properties();
            env.put(Context.PROVIDER_URL, System.getProperty(Context.PROVIDER_URL, PROVIDER_URL));
//            env.put(Context.SECURITY_PRINCIPAL, System.getProperty("username", DEFAULT_USERNAME));
//            env.put(Context.SECURITY_CREDENTIALS, System.getProperty("password", DEFAULT_PASSWORD));
            context = new InitialContext(env);

            // Perform the JNDI lookups
            String connectionFactoryString = System.getProperty("connection.factory", DEFAULT_CONNECTION_FACTORY);
   "Attempting to acquire connection factory " + connectionFactoryString  ;
            connectionFactory = (ConnectionFactory) context.lookup(connectionFactoryString);
   "Found connection factory \"" + connectionFactoryString + "\" in JNDI";

            String destinationString = System.getProperty("destination", DEFAULT_DESTINATION);
  "Attempting to acquire destination \"" + destinationString + "\"");
            destination = (Destination) context.lookup(destinationString);
  "Found destination \"" + destinationString + "\" in JNDI");

            // Create the JMS connection, session, producer, and consumer
            connection = connectionFactory.createConnection();
            session = connection.createSession(false, Session.AUTO_ACKNOWLEDGE);
            producer = session.createProducer(destination);
            consumer = session.createConsumer(destination,selector);

//            String content = DEFAULT_MESSAGE;
//  "Sending " + count + " messages with content: " + content);
//            // Send the specified number of messages
//            for (int i = 0; i < count; i++) {
//                message = session.createTextMessage(content+" No."+(i+1));
//                producer.send(message);
//            }

            // Then receive the same number of messages that were sent
            for (int i = 0; i < count; i++) {
                message = (TextMessage) consumer.receive(waitTime);
                if (message != null)
      "Received message with content " + message.getText());
      "Correlation ID: " + message.getJMSCorrelationID());
      "Timestamp: " + message.getJMSTimestamp());
      "MessageID: " + message.getJMSTimestamp());
                  log.warn "Nothing found in "+DEFAULT_DESTINATION;

        } catch (Exception e) {
            log.error "Exception error: "+(e.getMessage());
            throw e;
        } finally {
            if (context != null) {

            // closing the connection takes care of the session, producer, and consumer
            if (connection != null) {
//    }

Please note: Probably you'll need to add some jars to your classpath, such as: jms-1.1.jar, netty.jar, jnp-client.jar and other libraries corresponding to your JMS server (HornetQ, ActiveMQ, ...)

  1. Commented part is for sending messages. you won't need it.
  2. Set your parameters in lines 14-24
  3. The "selector" is optional, it is currently set to consume every message regardless of the selector content.

Last step:  What you need to do is to open you Groovy/Java console and change this script to an infinite loop, inserting all received messages (including Header elements, message body and IDs) in your local database with a simple jdbc connection. Run this while you are running your LoadUI/JMeter test, 

Use jdbc connection with a select (selector) query statement for a given "timeout" in a loop. You can do it easily with JMeter Loop controller , or SoapUI groovy steps.