Write data into Excel file using Apache POI

IN my previous post, I posted code for reading excel file using Apache POI library. In this post I'll cover, how can we write data into excel. Here is the code:

package com.qa.test;

import java.io.FileOutputStream;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class SpreadSheetWriter {
	
	 /** This method writes data to new excel file **/
    public static void writeDataToExcelFile(List> data, String fileName) 
    {
    	try{
	    	HSSFWorkbook myWorkBook = new HSSFWorkbook();
	        HSSFSheet mySheet = myWorkBook.createSheet();
	        HSSFRow myRow = null;
	        HSSFCell myCell = null;
	        
	        //Create header row
	        createHeaderRow(mySheet);
	        
	        int rowNum = 1;
	        Iterator> iter = data.iterator();
	        while(iter.hasNext())
	        {
	        	myRow = mySheet.createRow(rowNum++);
	        	int cellNum = 0;
	        	
				List key = iter.next();
				for(String values: key)
				{
					myCell = myRow.createCell(cellNum++);
					myCell.setCellValue(values);
				}
			}
	
	        FileOutputStream out = new FileOutputStream(fileName);
            myWorkBook.write(out);
            out.close();
        }catch(Exception e){ 
        	e.printStackTrace();
        }         
   }
    
 }

Read Excel file using apache POI library

If you want to read Excel file in Java, Apache POI is the library which supports to read/write microsoft documents. Here is the code to read an Excel file.

import java.io.FileInputStream;
import java.io.InputStream;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;

public class ReadExcel {
 
 public static void main(String[] args){
  
  try{
   InputStream myxls = new FileInputStream("C:\\exceltest.xls");
   HSSFWorkbook wb     = new HSSFWorkbook(myxls);
   
   HSSFSheet sheet = wb.getSheetAt(0); 
   
   Iterator rows = sheet.rowIterator();
   
   System.out.println("Total Rows :: " + sheet.getLastRowNum());
   
   //Iterate over each row
   while (rows.hasNext()) 
   {
    HSSFRow row = (HSSFRow) rows.next();
    if(checkIfRowEmpty(row)) 
     continue;
    
    int minColIndex = row.getFirstCellNum();
    int maxColIndex = row.getLastCellNum();
    
    for(int colIndex = minColIndex; colIndex <= maxColIndex; colIndex++) 
    {
     HSSFCell cell = row.getCell(colIndex);
     
     // POI can't recognize empty cells of Excel, so we should create new cells with a blank value
     if(cell == null) 
     {
        cell = row.createCell(colIndex);
           cell.setCellValue(" ");
     }    
     String value = "";
     
     switch(cell.getCellType())
     {
      case HSSFCell.CELL_TYPE_NUMERIC:
       value = Double.toString(cell.getNumericCellValue());
       break;
      case HSSFCell.CELL_TYPE_BOOLEAN:
       value = Boolean.toString(cell.getBooleanCellValue());
       break;
      case HSSFCell.CELL_TYPE_BLANK:
       value = "";
       break;
      case HSSFCell.CELL_TYPE_ERROR:
       value = "";
       break;
      case HSSFCell.CELL_TYPE_FORMULA:
       value = "";
       break;
      default:
       value = cell.getStringCellValue();
       break;
     }
     System.out.print(value + "|");
    }
    System.out.println();
   }
  }
  catch(Exception e){
   e.printStackTrace();
  }
 }
 
 public static boolean checkIfRowEmpty(HSSFRow row) 
 {
  int minColumIndex = row.getFirstCellNum();
  int maxColumnIndex = row.getLastCellNum();
  
  boolean isRowEmpty = true;
  for(int columIndex = minColumIndex; columIndex <= maxColumnIndex; columIndex++) 
  {
   HSSFCell cell = row.getCell(columIndex);
   if(cell == null || cell.toString().trim().isEmpty() || cell.toString().length() == 0){
    
   }
   else{
    isRowEmpty = false;
    return isRowEmpty;
   }
  }
  return isRowEmpty;
 }
}

Running multiple version of firefox

If you want to run multiple versions of firefox on same machine, follow below steps:

1. Close your all running instances of firefox
2. Download older versions of firefox from http://www.oldversion.com/Mozilla-Firefox.html and do a custom install, install them on a specific version directory. For example I installed them as below:
C:\Program Files\Mozilla Firefox_6.0.2
C:\Program Files\Mozilla Firefox_7.0.1
3. Create profiles for every version by following steps:
    A. Goto Run command (or press WINDOWS+R)
    B. C:\Program Files\Mozilla Firefox_6.0.2 -P
    C. It will open a profile window, Create a profile name with 'Firefox6.0.2'
4. Repeat step 3 for all firefox versions, create unique profile for each version
5. To run a specific version of firefox use below command:
<path to firefox executable> -no-remote -P <profile name>


C:\Program Files\Mozilla Firefox_6.0.2\firefox.exe -no-remote -P Firefox6.0.2
C:\Program Files\Mozilla Firefox_7.0.1\firefox.exe -no-remote -P Firefox7.0.1

Selenium: Taking screenshot of a webpage

I had a requirement where I had to take screenshots of different URLs on different browsers. Instead of taking it manually everytime, thought of taking it programatically using Selenium. Here is the program which takes the screenshot of a webpage using selenium:

ScreeshotTest.java

import com.thoughtworks.selenium.DefaultSelenium;
import com.thoughtworks.selenium.Selenium;
import junit.framework.TestCase;

public class ScreeshotTest extends TestCase{
    private Selenium browser;
    private static int seleniumServerPort = 2001;
    private static SeleniumServerControl seleniumServerControl = null;
    private final static String URL = "http://www.vishnuagrawal.blogspot.com";

    public void setUp() { 
        seleniumServerControl = SeleniumServerControl.getInstance();
        seleniumServerControl.startSeleniumServer(seleniumServerPort);
        browser = new DefaultSelenium("localhost", seleniumServerPort, "*firefox", URL); 
    }

    public void tearDown() { 
        browser.stop(); 
        seleniumServerControl.stopSeleniumServer();
    }

    public void testFlashApp() throws Exception{
        browser.start();
        browser.setTimeout("120000");
        browser.open(URL);
        browser.captureEntirePageScreenshot("D:\\ss.png", "");
    }
}

SeleniumServerControl.java

import org.openqa.selenium.server.RemoteControlConfiguration;
import org.openqa.selenium.server.SeleniumServer;

public class SeleniumServerControl {

  private static final SeleniumServerControl instance = new SeleniumServerControl();
  private SeleniumServer server = null;

  public static SeleniumServerControl getInstance() {
    return instance;
  }

  protected SeleniumServerControl() {
  }

  public void startSeleniumServer() {
    startSeleniumServer(RemoteControlConfiguration.DEFAULT_PORT);
  }

  public void startSeleniumServer(int port) {
    if (server == null) {
      try {
        RemoteControlConfiguration settings = new RemoteControlConfiguration();
        //File f = new File("/home/user/.mozilla/firefox/default");
        //settings.setFirefoxProfileTemplate(f);
        //settings.setReuseBrowserSessions(true);
        settings.setSingleWindow(true);
        settings.setPort(port);
        server = new SeleniumServer(settings);
        System.out.println(" selenium server " + server.toString());
      } 
      catch (Exception e) {
        System.err.println("Could not create Selenium Server because of: "  + e.getMessage());
        e.printStackTrace();
      }
    }
    try {
      server.start();
    } 
    catch (Exception e) {
      System.err.println("Could not start Selenium Server because of: " + e.getMessage());
      e.printStackTrace();
    }
  }

  public void stopSeleniumServer() {
    if (server != null) {
      try {
        server.stop();
        server = null;
      } 
      catch (Exception e) {
        System.err.println("Could not stop Selenium Server because of: " + e.getMessage());
        e.printStackTrace();
      }
    }
  }
}

Windows: remove initial characters from file names

In my continuation of last post, if you do not have a specific pattern to delete, but you know the number of characters to delete, you can use following script. Following script will remove initial 5 characters from the file name. (you can change 5 to any number, ofcourse)

@ECHO ON
FOR %%F IN ("*.mp3") DO CALL :process "%%F"
GOTO :EOF

:process
SET oldname=%1
SET "newname=%~nx1"
SET "newname=%newname:~5%"
RENAME %oldname% "%newname%"

 

Windows: remove prefixes from file names

Whenever I download any film songs from internet, the file name have a specific prefix (basically the website name, from where I downloaded the songs). To keep the file names shorter, I wish to remove the prefixes from all files. Doing this task manually consumes so much time, so here is the script to do it at one go.

File name is in format "[Site.name] Film Name - Song Number - Song Title.mp3". Save the following contents in a file (let say rename.bat) and run this file in windows command prompt. It will change all file names to "Film Name - Song Number - Song Title.mp3"

#################################
::rename.bat
#################################
@ECHO OFF
FOR %%F IN ("[*] *") DO CALL :renamer "%%F"
GOTO :EOF

:renamer
SET oldname=%1
SET "newname=%~nx1"
SET "newname=%newname:*] =%"
RENAME %oldname% "%newname%"

Shell Script: Find number of elements in an array

shell (bash) provides one-dimensional array variables. Any variable may be used as an array and there is no maximum limit on the size of an array. Below is the way to find length of array in shell script.

#!/bin/bash
SERVERS=(SERVER1 SERVER2 SERVER3 SERVER4)
# set length of an array
arrayLength=${#SERVERS[*]}
# or
#arrayLength=${#SERVERS[@]}

#Traverse each entry
for (( i=0; i<${arrayLength}; i++ ));
do
  echo ${SERVERS[$i]}
done

My first AdSense paycheck

Yesterday I got my first AdSense paycheck. It feels good to get first paycheck from Google AdSense. A reward for sharing your knowledge to the world :)

Excel to testlink results upload

In my previous post I described about a utility which uploads my tests into Testlink. Once my tests are uploaded, I need to execute them, right.. ? But executing tests one by one from Testlink UI is quite slow process and somehow degrades productivity of the QA person. (as compare to when we use excel sheet for test cases tracking and mark Pass/Fail in the excel sheet itself, of which my team is habitual).
To overcome this, I wrote another utility which uploads tests results from excel into Testlink and your lead/manager can access reports from testlink directly. Excel should have following columns:

||Project Name||Test Suite Title||Test Case Title||Test Case Summary||Pre conditions||Step Actions||Expected Results||Priority||Keywords||TestPlan Name||Build Name||Comments||testlink login name||

1. Test Status may have values (PASS, FAIL, BLOCKED). Any other value will update test status as 'Not Run'.
2. Test Plan name should exactly match with as in Testlink
3. Build name should exactly match with as in Testlink
4. Comments can be used to specify bug number
5. User name should be testlink login name

So the whole process of uploading tests and their results goes as below:
1. QA creates tests in above mentioned column format (Project Name, suite name, status, testplan name, qa build name, comments, user name can left blank for now).
2. Create a test suite in Testlink
3. Update testlink project name and test suite name in above excel.
4. Upload excel from php of my previous post
5. Create a test plan (e.g. Release X.0) and QA Build (eg. QA1) in testlink
6. Once you execute your tests, update the excel with test status, testplan name, qa build name, comments , user name (testlink login name)
7. Upload the excel from php of my this post (see below)
8. That’s it, now anyone can access test reports from testlink UI.

<?php
// Test CVS
// Vishnu Agrawal

ini_set("display_errors", "1");
error_reporting(E_ALL ^ E_NOTICE);
date_default_timezone_set('America/Los_Angeles');

if($_FILES['xls_file']['tmp_name'])
{ 
    move_uploaded_file($_FILES['xls_file']['tmp_name'],'uploads/'.$_FILES['xls_file']['name']);
    $xls_file = 'uploads/'.$_FILES['xls_file']['name'];
    
    include('../config_db.inc.php');
    
    mysql_connect(DB_HOST,DB_USER,DB_PASS);
    mysql_select_db(DB_NAME);
    
    require_once 'Excel/reader.php';
    
    
    // ExcelFile($filename, $encoding);
    $data = new Spreadsheet_Excel_Reader();
    
    
    // Set output Encoding.
    $data->setOutputEncoding('CP1251');
    
    /***
    * if you want you can change 'iconv' to mb_convert_encoding:
    * $data->setUTFEncoder('mb');
    *
    **/
    
    /***
    * By default rows & cols indeces start with 1
    * For change initial index use:
    * $data->setRowColOffset(0);
    *
    **/
    
    
    
    /***
    *  Some function for formatting output.
    * $data->setDefaultFormat('%.2f');
    * setDefaultFormat - set format for columns with unknown formatting
    *
    * $data->setColumnFormat(4, '%.3f');
    * setColumnFormat - set format for column (apply only to number fields)
    *
    **/
    
    $data->read($xls_file);
    
    /***
    
    
     $data->sheets[0]['numRows'] - count rows
     $data->sheets[0]['numCols'] - count columns
     $data->sheets[0]['cells'][$i][$j] - data from $i-row $j-column
    
     $data->sheets[0]['cellsInfo'][$i][$j] - extended info about cell
        
        $data->sheets[0]['cellsInfo'][$i][$j]['type'] = "date" | "number" | "unknown"
            if 'type' == "unknown" - use 'raw' value, because  cell contain value with format '0.00';
        $data->sheets[0]['cellsInfo'][$i][$j]['raw'] = value if cell without format 
        $data->sheets[0]['cellsInfo'][$i][$j]['colspan'] 
        $data->sheets[0]['cellsInfo'][$i][$j]['rowspan'] 
    **/
    
    $values = '';
    
    for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) {
        $values[] = $data->sheets[0]['cells'][$i];
    }
    $rownum = 0;
    $test_update = 0;
    $test_update_existing = 0;
    $test_update_fail = 0;
    
    foreach ($values as $colnum)
    {    
        $rownum++;
        if($rownum > 1)
        {
            $excel_project_name = trim($colnum[1]);
            $excel_suite_name = trim($colnum[2]);
            $excel_tc_title = trim($colnum[3]);
            $excel_tc_summary = trim($colnum[4]);
            $excel_pre_condition = trim($colnum[5]);
            $excel_tc_step = trim($colnum[6]);
            $excel_tc_result = trim($colnum[7]);
            $excel_tc_priority = trim($colnum[8]);
            $excel_tc_keywords = trim($colnum[9]);
            $excel_tc_test_result = trim($colnum[10]);
            $excel_tc_testplan = trim($colnum[11]);
            $excel_tc_build = trim($colnum[12]);
            $excel_tc_comment = trim($colnum[13]);
            $excel_tc_username = trim($colnum[14]);
            
            if (!$excel_project_name) {
                if($excel_suite_name && $excel_tc_title) {
                    echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: Testlink project name is not Specified</font>\");                    
                    $test_update_fail++;
                }                
                continue;
            }
            if (!$excel_suite_name) {
                echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: Testlink Suite name is not Specified</font>\");
                $test_update_fail++;
                continue;
            }
            if (!$excel_tc_title) {
                echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: Testlink tese case title is not Specified</font>\");
                $test_update_fail++;
                continue;
            }
            if (!$excel_tc_testplan) {
                echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: Testlink test plan name is not Specified</font>\");
                $test_update_fail++;
                continue;
            }
            if (!$excel_tc_build) {
                echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: Testlink test build name is not Specified</font>\");
                $test_update_fail++;
                continue;
            }
            
            // Get Project id - Start
            $query_project = mysql_query("SELECT testprojects.id, nodes_hierarchy.name FROM testprojects, nodes_hierarchy WHERE testprojects.id = nodes_hierarchy.id AND nodes_hierarchy.name='$excel_project_name'");
            if(mysql_num_rows($query_project) != 1) {
                echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: " . mysql_num_rows($query_project) . " test project found with name <b>$excel_project_name</b></font>\");
                $test_update_fail++;
                continue;
            }
            $res_project = mysql_fetch_assoc($query_project);
            $project_id = $res_project["id"];
            //echo " Project id is " . $project_id . "\" ;
            // Get Project id - End
            
            // Get testplan id - Start
            $query_testplan = mysql_query("SELECT testplans.*, NH.name FROM testplans testplans, nodes_hierarchy NH WHERE testplans.id=NH.id AND NH.name = '$excel_tc_testplan' AND NH.parent_id=$project_id");
            if(mysql_num_rows($query_testplan) != 1) {
                echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: " . mysql_num_rows($query_testplan) . " test project found with name <b>$excel_tc_testplan</b></font>\");
                $test_update_fail++;
                continue;
            }
            $res_testplan = mysql_fetch_assoc($query_testplan);
            $testplan_id = $res_testplan["id"];
            //echo " testplan id is " . $testplan_id . "\" ;
            // Get testplan id - End
            
            // Get build id - Start
            $query_build = mysql_query("SELECT builds.id, builds.name, builds.notes FROM builds builds WHERE builds.testplan_id = $testplan_id  AND builds.name= '$excel_tc_build' AND builds.active = 1 AND builds.is_open = 1");
            if(mysql_num_rows($query_build) != 1) {
                echo "<font color=#ff0000>Test result upload for test row $rownum failed. REASON: " . mysql_num_rows($query_build) . " build found with name <b>$excel_tc_build</b>. Either build name do not exists or build is not active/open</font>\";
                $test_update_fail++;
                continue;
            }
            $res_build = mysql_fetch_assoc($query_build);
            $build_id = $res_build["id"];
            //echo " build id is " . $build_id . "\" ;
            // Get build id - End
            
            // Get tester id - Start
            $query_tester = mysql_query("SELECT * from users  where login = '$excel_tc_username'");
            if(mysql_num_rows($query_tester) != 1) {
                echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: " . mysql_num_rows($query_tester) ." Users with name <b>$excel_tc_username</b></font>\");
                $test_update_fail++;
                continue;
            }
            $res_tester = mysql_fetch_assoc($query_tester);
            $tester_id = $res_tester["id"];
            //echo " tester id is " . $tester_id . "\" ;
            // Get tester id - End
            
            // Get test case status - Start
            if (strcasecmp($excel_tc_test_result, 'PASS') == 0) {
                $test_status = 'p';
            }
            elseif(strcasecmp($excel_tc_test_result, 'FAIL') == 0) {
                $test_status = 'f';
            }
            elseif(strcasecmp($excel_tc_test_result, 'BLOCKED') == 0) {
                $test_status = 'b';
            }
            else {
                echo ("<font color=#ff0000>Incorrect test result <b>$excel_tc_test_result</b> in row $rownum</font>\");
                $test_status = 'n';
            }
            // Get test case status - End
            
            // Get test Suite id - Start
            $query_testsuitenames = mysql_query("SELECT TS.*, NH.name, NH.parent_id FROM testsuites TS JOIN nodes_hierarchy NH ON NH.id = TS.id WHERE NH.name = '$excel_suite_name'");
            
            $count = 0;
            $test_suite_id = 0;
            while($suites = mysql_fetch_assoc($query_testsuitenames)) {
                $query_get_project = mysql_query("SELECT _id FROM (SELECT @r AS _id, (SELECT @r := parent_id FROM nodes_hierarchy WHERE id = _id) AS parent FROM ( SELECT @r := $suites[id] ) vars, nodes_hierarchy h WHERE @r IS NOT NULL) AS t WHERE t.parent IS NULL");
                $res_get_project = mysql_fetch_assoc($query_get_project);
                if(count($res_get_project) == 1) {
                    if($res_get_project[_id] == $project_id) {
                        $count++;
                        $test_suite_id = $suites[id];
                    }
                }
            }
            
            if($count != 1 && $test_suite_id ==0) {
                echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: " . $count . " suite name found with name <b>$excel_suite_name</b> </font>\");
                $test_update_fail++;
                continue;
            }            
            //echo " suite id is " . $test_suite_id . "\" ;
            // Get test Suite id - End
            
            //Get test case details - Start
            $query_testcase = mysql_query("SELECT DISTINCT NH_TCASE.id,NH_TCASE.name,NH_TCASE_PARENT.id AS parent_id, NH_TCASE_PARENT.name AS tsuite_name, TCV.version, TCV.tc_external_id, TCV.id AS tcversion_id FROM nodes_hierarchy NH_TCASE, nodes_hierarchy NH_TCASE_PARENT, nodes_hierarchy NH_TCVERSIONS, tcversions TCV  WHERE NH_TCASE.node_type_id = 3 AND NH_TCASE.name = '$excel_tc_title' AND TCV.id=NH_TCVERSIONS.id AND NH_TCVERSIONS.parent_id=NH_TCASE.id AND NH_TCASE_PARENT.id=NH_TCASE.parent_id AND NH_TCASE_PARENT.id = $test_suite_id AND NH_TCASE_PARENT.node_type_id = 2  ORDER BY TCV.version desc LIMIT 1");
            if(mysql_num_rows($query_testcase) != 1) {
                echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: " . mysql_num_rows($query_testcase) . " test cases found with name <b>$excel_tc_title</b></font>\");
                $test_update_fail++;
                continue;
            }
            $res_testcase = mysql_fetch_assoc($query_testcase);
            $testcase_id = $res_testcase["id"];
            $testcase_version = $res_testcase["version"];
            $testcase_version_id = $res_testcase["tcversion_id"];
            //echo "Test case details " . $testcase_id . "<>" . $testcase_version . "<>" . $testcase_version_id . "\";
            //Get test case details - End
            
            $test_notes = $excel_tc_comment;
            
            $query_tcversion = mysql_query("SELECT * from testplan_tcversions where testplan_id = $testplan_id AND tcversion_id = $testcase_version_id");
            if (mysql_errno()) { 
                echo "MySQL error ".mysql_errno().": ".mysql_error()."\n\When selecting data from testplan_tcversions\"; 
                $test_update_fail++;
                continue;
            }
                
            //Check if test case is already linked with test plan, we don't need to update the table again
            if(mysql_num_rows($query_tcversion) == 0) {    
                mysql_query("BEGIN");
                $objQuery1 = mysql_query("INSERT INTO testplan_tcversions(testplan_id, tcversion_id, author_id) VALUES($testplan_id, $testcase_version_id, $tester_id)");
                $case_id = mysql_insert_id();
                $objQuery2 = mysql_query("INSERT INTO user_assignments(feature_id, user_id, build_id, assigner_id) VALUES($case_id, $tester_id, $build_id, $tester_id)");
                
                if(($objQuery1) and ($objQuery2))  {  
                    //*** Commit Transaction ***//  
                    mysql_query("COMMIT");  
                }  
                else  {  
                    //*** RollBack Transaction ***//  
                    mysql_query("ROLLBACK");  
                    echo "ERROR :: Rollbacked transaction of updating user assignment for row $rownum ...";
                    $test_update_fail++;
                    continue;
                }
            }
            
            // Convert current Timestamp into a string, safe for MySql
            $currentTSExecution = date("Y-m-d H:i:s", time());
            
            if (strcasecmp($test_status, 'n') != 0) {    //update test status only if test status is not 'n'
                $query_executions = mysql_query("SELECT * from executions WHERE build_id = $build_id AND testplan_id = $testplan_id AND tcversion_id = $testcase_version_id AND tcversion_number = $testcase_version");
                if (mysql_errno()) { 
                    echo "MySQL error ".mysql_errno().": ".mysql_error()."\n\When selecting data from executions\"; 
                    $test_update_fail++;
                    continue;
                }
                
                //Check if test case is not already updated in executions, then we need to add a new entry otherwise update existing entry
                if(mysql_num_rows($query_executions) == 0) {            
                    mysql_query("INSERT INTO executions(build_id, tester_id, execution_ts, status, testplan_id, tcversion_id, tcversion_number, notes) VALUES($build_id, $tester_id, '$currentTSExecution', '$test_status', $testplan_id, $testcase_version_id, $testcase_version, '$test_notes')");
                    if (mysql_errno()) { 
                        echo "MySQL error ".mysql_errno().": ".mysql_error()."\n\When insertng data into executions\"; 
                        $test_update_fail++;
                        continue;
                    }        
                }
                elseif(mysql_num_rows($query_executions) == 1) {
                    mysql_query("UPDATE executions SET tester_id = $tester_id, execution_ts = '$currentTSExecution', status = '$test_status', notes = '$test_notes' WHERE build_id = $build_id AND testplan_id = $testplan_id AND tcversion_id = $testcase_version_id AND tcversion_number = $testcase_version");
                    if (mysql_errno()) { 
                        echo "MySQL error ".mysql_errno().": ".mysql_error()."\n\When updating data into executions\"; 
                        $test_update_fail++;
                        continue;
                    }
                    $test_update_existing++;
                    $test_update--;
                }
                else {
                    echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: " . mysql_num_rows($query_executions) . " execution status found ...</font>\");
                    $test_update_fail++;
                    continue;
                }
            }
            $test_update++;
        }        
    }    
    echo "<font color=#0000ff>Tests updated (New): \$test_update</b> </font>\";
    echo "<font color=#0000ff>Tests updated (Existing): <b>$test_update_existing</b> </font>\";
    echo "<font color=#0000ff>Tests update Failed: <b>$test_update_fail</b> </font>\";
}
?>
<!DOCTYPE unspecified PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Testlink Testcase Results Uploader</title>
</head>
<body>
<form name="frm" action="" method="post" enctype="multipart/form-data">

<table>
<tr><td>Use this page to upload test results from Excel sheet to Testlink, Excel file should have following columns.</td></tr>
</table>

<table border="1">
<tr>
<th>Test Project Name from Testlink</th>
<th>Test Suite Name from Testlink</th>
<th>Test Case Title</th>
<th>Test Case Summary</th>
<th>Pre Conditions</th>
<th>Step Actions</th>
<th>Expected Results</th>
<th>Priority</th>
<th>Keywords</th>
<th>Test Status</th>
<th>Testplan Name from Testlink</th>
<th>Build Name from Testlink</th>
<th>Comments (can be used to specify Bug#)</th>
<th>Username (Testlink login name)</th>
</tr>
</table>

<p>

<table>
<tr>
<th align="left">NOTE:</th>
</tr>
<tr>
<td>
<ol>
<li>Tests should be in the first sheet of the excel file</li>
<li>First row will be ignored as it will have column headers</li>
<li>Test Project name should exactly match with Testlink project name in which you want to upload your tests. </li>
<li>Test Suite Title name should exactly match with Testlink test suite name in which you want to upload your tests. </li>
<li>Test Case Title is the title which will be used to list test in the testlink tree</li>
<li>Multiple keywords are supported (comma separated)</li>
<li>Priority column may have values Low/Medium/High. If no value (or wrong value) is specified, test will be assigned Medium priority.</li>
<li>Test Status may have values (PASS, FAIL, BLOCKED). Any other value will update test status as 'Not Run'.</li>
<li>Test Plan name should exactly match with as in Testlink</li>
<li>Build name should exactly match with as in Testlink</li>
<li>Comments can be used to specify bug number</li>
<li>User name should be testlink login name</li>
<li>If test case title already exists for the given suite in testlink, new test will not be added, existing will be updated</li>
</ol>
</td>
</tr>
</table>

<table>
<tr>
    <td><b>Testlink Test Result Uploader:</b></td>
</tr>
<tr>
    <td><input type="file" name="xls_file" />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<a href="pub_dashboard.xls">Sample file format for testcase</a> </td>
</tr>
<tr>
    <td><input type="submit" name="submit" value="submit" /> </td>
</tr>
<tr>
    <td></td>
</tr>
</table>
</form>
</body>
</html>

Automatically login a ssh session using putty

Putty is used to connect to remote server and for day to day tasks developer login into remote machine using putty. User have to input username/password for every login. This process can be automated to save some time.

1. Downlaod PuTTY

2. Download PuTTYgen

3. Run PuTTYgen

4. Select SSH-2 DSA as the "Type of Key" to generate

5. Click on generate button and move your mouse around so that randomness is generated

6. Click on “Save Private Key” and save it to your computer

7. Copy the entire content inside the box to your clipboard (this is your generated public key).

8. Login to your SSH server using putty (by using user name and password)

9. Create file ~/.ssh/authorized_keys and put your public key on a single line (from step 7)

10. Make this file readable (chmod +r )

11. Open PuTTY, navigate to Connection->Data and fill in the auto-login username

12. Navigate to Connection->SSH->Auth and under Private-key, browse to the file you had saved earlier on your computer. (from step 6)

You are done. Now whenever you login into your remote machine, it will login automatically.