1

I have generate testcases for the login and register page so,i want to display testresult in excel sheet but problem is that when i run testng.xml file then data is overrite into file .i want to display result in multiple sheet in one single workbook For Register Page:

package com.sevenbits.diecast;

    import java.io.File;
    import java.io.FileNotFoundException;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.util.Date;
    import java.util.LinkedHashMap;
    import java.util.Map;
    import java.util.Set;

    import jxl.Workbook;
    import jxl.write.WritableSheet;
    import jxl.write.WritableWorkbook;

    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.openqa.selenium.By;
    import org.openqa.selenium.NoSuchElementException;
    import org.openqa.selenium.WebDriver;
    import org.openqa.selenium.WebElement;
    import org.openqa.selenium.chrome.ChromeDriver;
    import org.openqa.selenium.chrome.ChromeOptions;
    import org.openqa.selenium.support.ui.Select;
    import org.testng.Assert;
    import org.testng.annotations.AfterClass;
    import org.testng.annotations.BeforeClass;
    import org.testng.annotations.DataProvider;
    import org.testng.annotations.Test;

    //@FixMethodOrder(MethodSorters.NAME_ASCENDING)
    public class Register 
    {

        static WebDriver driver;

        String  expectedurl = "http://example.com/account-page/?ihc_register=create_message";

         static HSSFWorkbook workbook;
          static HSSFSheet sheet;
          static Map<String, Object\[\]> testresultdata;

        @BeforeClass
        public static void setup() throws IOException
        {


            System.setProperty("webdriver.chrome.driver", "E:\\Selenium Jar\\chromedriver.exe");
            ChromeOptions options = new ChromeOptions();
            options.addArguments("--disable-extensions");
            driver = new ChromeDriver(options);
            driver.manage().window().maximize();

            workbook = new HSSFWorkbook();

               sheet = workbook.createSheet("Register");

               testresultdata = new LinkedHashMap<String, Object\[\]>();
               testresultdata.put("1", new Object\[\] {"TestCasesNo", "UserName","Email","Name","Password","Confirm Password","Expected Result","Actual Result", "Pass/Fail"});

        }

        @Test
        public void a_register()
        {
            driver.get("http://example.com/");
            WebElement regmain = driver.findElement(By.className("reg"));
            regmain.click();
        }

        @DataProvider(name = "Authentication")

          public static Object\[\]\[\] credentials() {

                return new Object\[\]\[\] { { " ", " "," "," "," "}};

          }


        @Test(dataProvider = "Authentication")
        public void b_blankdata(String uname,String email,String strname,String strpass,String strconpass)
        {
            driver.get("http://example.com//register/");


            WebElement username = driver.findElement(By.name("user_login"));
            username.clear();
            username.sendKeys(uname);

            WebElement mail = driver.findElement(By.name("user_email"));
            mail.clear();
            mail.sendKeys(email);

            WebElement name = driver.findElement(By.name("user_Name"));
            name.clear();
            name.sendKeys(strname);

            Select se = new Select(driver.findElement(By.name("country")));
            se.selectByIndex(3);

            WebElement password = driver.findElement(By.name("pass1"));
            password.clear();
            password.sendKeys(strpass);

            WebElement confirmpassword = driver.findElement(By.name("pass2"));
            confirmpassword.clear();
            confirmpassword.sendKeys(strconpass);

            WebElement submit = driver.findElement(By.id("ihc_submit_bttn"));
            submit.click();

            String actual = driver.getCurrentUrl();

            if(actual.equals(expectedurl))
            {
                Assert.assertTrue(true);
                testresultdata.put("2", new Object\[\] {1d, uname, email,strname,strpass,strconpass,expectedurl,actual ,"Pass"});
            }
            else
            {
                testresultdata.put("2", new Object\[\] {1d, uname, email,strname,strpass,strconpass,expectedurl,actual ,"Fail"});
                Assert.assertTrue(false);

            }

        }
        @DataProvider(name = "Authentication2")

          public static Object\[\]\[\] credentials2() {

                return new Object\[\]\[\] { { "test93", "test93@tristonsoft.com"," test","12Abcd","12Abcd"}};
          }


        @Test(dataProvider = "Authentication2")
        public void d_validdata(String uname,String email,String strname,String strpass,String strconpass)
        {
            driver.get("http://example.com//register/");


            WebElement username = driver.findElement(By.name("user_login"));
            username.clear();
            username.sendKeys(uname);

            WebElement mail = driver.findElement(By.name("user_email"));
            mail.clear();
            mail.sendKeys(email);

            WebElement name = driver.findElement(By.name("user_Name"));
            name.clear();
            name.sendKeys(strname);

            Select se = new Select(driver.findElement(By.name("country")));
            se.selectByIndex(3);

            WebElement password = driver.findElement(By.name("pass1"));
            password.clear();
            password.sendKeys(strpass);

            WebElement confirmpassword = driver.findElement(By.name("pass2"));
            confirmpassword.clear();
            confirmpassword.sendKeys(strconpass);

            WebElement submit = driver.findElement(By.id("ihc_submit_bttn"));
            submit.click();

            String actual = driver.getCurrentUrl();

            if(actual.equals(expectedurl))
            {
                Assert.assertTrue(true);
                testresultdata.put("4", new Object\[\] {3d, uname, email,strname,strpass,strconpass,expectedurl,actual ,"Pass"});
            }
            else
            {
                testresultdata.put("4", new Object\[\] {3d, uname, email,strname,strpass,strconpass,expectedurl,actual ,"Fail"});
                Assert.assertTrue(false);

            }


        }
         @AfterClass
          public void setupAfterSuite() {
                 Set<String> keyset = testresultdata.keySet();
                    int rownum = 0;
                    for (String key : keyset) {
                        Row row = sheet.createRow(rownum++);
                        Object \[\] objArr = testresultdata.get(key);
                        int cellnum = 0;
                        for (Object obj : objArr) {
                            Cell cell = row.createCell(cellnum++);
                            if(obj instanceof Date) 
                                cell.setCellValue((Date)obj);
                            else if(obj instanceof Boolean)
                                cell.setCellValue((Boolean)obj);
                            else if(obj instanceof String)
                            {
                                cell.setCellValue((String)obj);
                                System.out.println(obj);
                            }
                            else if(obj instanceof Double)
                                cell.setCellValue((Double)obj);
                        }
                    }
            try {



                FileOutputStream out =new FileOutputStream("E:\\TestResult.xls");
                workbook.write(out);
                out.close();
                System.out.println("Excel written successfully..");


            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }


          }

            private boolean isElementPresent(By by) {
                try {
                  driver.findElement(by);
                  return true;
                } catch (NoSuchElementException e) {
                  return false;
                }
              }
    }

For Login Page:

package com.sevenbits.diecast;


import java.io.File; 
import java.io.FileNotFoundException;
import java.io.FileOutputStream; 
import java.io.IOException;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Set;
import java.util.concurrent.TimeUnit;

import jxl.Workbook;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell; 
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row; 
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFSheet; 
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openqa.selenium.By;
import org.openqa.selenium.NoSuchElementException;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.chrome.ChromeDriver;
import org.openqa.selenium.chrome.ChromeOptions;
import org.testng.Assert;
import org.testng.TestNGException;
import org.testng.annotations.AfterClass;
import org.testng.annotations.BeforeClass;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;
public class LoginPage
{

    static WebDriver driver;

    String  expectedurl = "http://example.com/account-page/";

    final String userblank1 = " ";
    final String passblank1 = " ";

    String  userblank2  = "rutvisoni";
    String  passblank2  = "";

     static String s1;

     static HSSFWorkbook workbook1;
      static HSSFSheet sheet;
      static Map<String, Object[]> testresultdata;


    @BeforeClass
    public static void setup()throws TestNGException, IOException
    {
        System.setProperty("webdriver.chrome.driver", "E:\\Selenium Jar\\chromedriver.exe");
        ChromeOptions options = new ChromeOptions();
        options.addArguments("--disable-extensions");
        driver = new ChromeDriver(options);
        driver.manage().window().maximize();

            workbook1 = new HSSFWorkbook();

           sheet = workbook1.createSheet("Login");


        /*WritableWorkbook workbook = Workbook.createWorkbook(new File("output.xls"));
        WritableSheet sheet = workbook.createSheet("FirstSheet", 0);
           */


            //sheet=workbook.getSheetAt(1);

           testresultdata = new LinkedHashMap<String, Object[]>();
           testresultdata.put("1", new Object[] {"TestCasesNo", "UserName","Password","Expected Result","Actual Result", "Pass/Fail"});

    }


    @Test
    public void login()
    {
        driver.get("http://example.com/");
        WebElement loginmain = driver.findElement(By.id("menu-item-14235"));
        loginmain.click();
    }

    @DataProvider(name = "Authentication")
      public static Object[][] credentials() {

            return new Object[][] { { " ", " " }};

      }

    @Test(dataProvider = "Authentication")

        public void blankdata(String uname,String Password)
    {

        driver.get("http://example.com//loginregister/");

        WebElement username = driver.findElement(By.name("log"));
        username.clear();
        username.sendKeys(uname);

        WebElement pass = driver.findElement(By.name("pwd"));
        pass.clear();
        pass.sendKeys(Password);

        WebElement submit = driver.findElement(By.name("Submit"));
        submit.click();

        String actual = driver.getCurrentUrl();

        if(actual.equals(expectedurl))
        {
            Assert.assertTrue(true);
            testresultdata.put("2", new Object[] {1d, uname, Password,expectedurl,actual ,"Pass"});
        }
        else
        {
            testresultdata.put("2", new Object[] {1d, uname, Password,expectedurl,actual ,"Fail"});
            Assert.assertTrue(false);

        }

    }


     @AfterClass
  public void setupAfterSuite() {
         Set<String> keyset = testresultdata.keySet();
            int rownum = 0;
            for (String key : keyset) {
                Row row = ((Sheet) sheet).createRow(rownum++);
                Object [] objArr = testresultdata.get(key);
                int cellnum = 0;
                for (Object obj : objArr) {
                    Cell cell = row.createCell(cellnum++);
                    if(obj instanceof Date) 
                        cell.setCellValue((Date)obj);
                    else if(obj instanceof Boolean)
                        cell.setCellValue((Boolean)obj);
                    else if(obj instanceof String)
                    {
                        cell.setCellValue((String)obj);
                        System.out.println(obj);
                    }
                    else if(obj instanceof Double)
                        cell.setCellValue((Double)obj);
                }
            }
    try {
        FileOutputStream out =new FileOutputStream(new File("E:\\TestResult.xls"));
        workbook1.write(out);
        out.close();
        System.out.println("Excel written successfully..");

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }


  }

    private boolean isElementPresent(By by) {
        try {
          driver.findElement(by);
          return true;
        } catch (NoSuchElementException e) {
          return false;
        }
      }
}
  • Multiple sheet in one file . Am I right ? – Sagar007 Dec 19 '16 at 12:25
  • Yes but i want to display data into excel sheet from the two java file – Rutvi Soni Dec 19 '16 at 12:28
  • I still have one doubt. Have you write sheet = workbook.createSheet("Login"); tried in Login class? – Sagar007 Dec 19 '16 at 12:38
  • yes i have create for the login.i am attaching that file also – Rutvi Soni Dec 19 '16 at 12:42
  • I have tried a lot but it is getting updated after new run from another class – Sagar007 Dec 19 '16 at 13:10
0

Actual problem is over writing files by Jar on every test class run. Because of this, Program is not able to store every test data with history.

There is another way to do it (store previous excel data).

Create test specific Excel file and combine it.

Please follow below steps.

Precondition : Create test specific Excel file

1. Create below class file in your project : CopySheets.java

import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeSet;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet; 
import org.apache.poi.ss.usermodel.Workbook;   
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;

    public class CopySheets
    {

    public static Workbook mergeExcelFiles(Workbook book, List<InputStream> inList)
    {
        try
        {
            for (InputStream fin : inList)
            {
                Workbook b = WorkbookFactory.create(fin);
                for (int i = 0; i < b.getNumberOfSheets(); i++)
                {
                    // not entering sheet name, because of duplicated names
                    copySheets(book.createSheet(b.getSheetName(i)), b.getSheetAt(i));
                }
            }
        }
        catch (Exception e)
        {
            // TODO: handle exception
        }

        return book;
    }

    /**
     * @param newSheet the sheet to create from the copy.
     * @param sheet the sheet to copy.
     */
    public static void copySheets(Sheet newSheet, Sheet sheet)
    {
        copySheets(newSheet, sheet, true);
    }

    /**
     * @param newSheet the sheet to create from the copy.
     * @param sheet the sheet to copy.
     * @param copyStyle true copy the style.
     */
    public static void copySheets(Sheet newSheet, Sheet sheet, boolean copyStyle)
    {
        int maxColumnNum = 0;
        Map<Integer, CellStyle> styleMap = (copyStyle) ? new HashMap<Integer, CellStyle>() : null;
        for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++)
        {
            Row srcRow = sheet.getRow(i);
            Row destRow = newSheet.createRow(i);
            if (srcRow != null)
            {
                copyRow(sheet, newSheet, srcRow, destRow, styleMap);
                if (srcRow.getLastCellNum() > maxColumnNum)
                {
                    maxColumnNum = srcRow.getLastCellNum();
                }
            }
        }
        for (int i = 0; i <= maxColumnNum; i++)
        {
            newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
        }
    }

    /**
     * @param srcSheet the sheet to copy.
     * @param destSheet the sheet to create.
     * @param srcRow the row to copy.
     * @param destRow the row to create.
     * @param styleMap -
     */
    public static void copyRow(Sheet srcSheet, Sheet destSheet, Row srcRow, Row destRow, Map<Integer, CellStyle> styleMap)
    {
        // manage a list of merged zone in order to not insert two times a merged zone  
        Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();
        destRow.setHeight(srcRow.getHeight());
        // reckoning delta rows  
        int deltaRows = destRow.getRowNum() - srcRow.getRowNum();
        // pour chaque row  
        for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++)
        {
            Cell oldCell = srcRow.getCell(j); // ancienne cell  
            Cell newCell = destRow.getCell(j); // new cell   
            if (oldCell != null)
            {
                if (newCell == null)
                {
                    newCell = destRow.createCell(j);
                }
                // copy chaque cell  
                copyCell(oldCell, newCell, styleMap);
                // copy les informations de fusion entre les cellules  
                //System.out.println("row num: " + srcRow.getRowNum() + " , col: " + (short)oldCell.getColumnIndex());  
                CellRangeAddress mergedRegion = getMergedRegion(srcSheet, srcRow.getRowNum(), (short) oldCell.getColumnIndex());

                if (mergedRegion != null)
                {
                    //System.out.println("Selected merged region: " + mergedRegion.toString());  
                    CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow() + deltaRows, mergedRegion.getLastRow() + deltaRows, mergedRegion.getFirstColumn(), mergedRegion.getLastColumn());
                    //System.out.println("New merged region: " + newMergedRegion.toString());  
                    CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion);
                    if (isNewMergedRegion(wrapper, mergedRegions))
                    {
                        mergedRegions.add(wrapper);
                        destSheet.addMergedRegion(wrapper.range);
                    }
                }
            }
        }
    }

    /**
     * @param oldCell
     * @param newCell
     * @param styleMap
     */
    public static void copyCell(Cell oldCell, Cell newCell, Map<Integer, CellStyle> styleMap)
    {
        if (styleMap != null)
        {
            if (oldCell.getSheet().getWorkbook() == newCell.getSheet().getWorkbook())
            {
                newCell.setCellStyle(oldCell.getCellStyle());
            }
            else
            {
                int stHashCode = oldCell.getCellStyle().hashCode();
                CellStyle newCellStyle = styleMap.get(stHashCode);
                if (newCellStyle == null)
                {
                    newCellStyle = newCell.getSheet().getWorkbook().createCellStyle();
                    newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
                    styleMap.put(stHashCode, newCellStyle);
                }
                newCell.setCellStyle(newCellStyle);
            }
        }
        switch (oldCell.getCellType())
        {
            case Cell.CELL_TYPE_STRING:
                newCell.setCellValue(oldCell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                newCell.setCellValue(oldCell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_BLANK:
                newCell.setCellType(HSSFCell.CELL_TYPE_BLANK);
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                newCell.setCellValue(oldCell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_ERROR:
                newCell.setCellErrorValue(oldCell.getErrorCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                newCell.setCellFormula(oldCell.getCellFormula());
                break;
            default:
                break;
        }

    }

    /**
     * Récupère les informations de fusion des cellules dans la sheet source pour les appliquer à la sheet destination... Récupère toutes les zones merged dans
     * la sheet source et regarde pour chacune d'elle si elle se trouve dans la current row que nous traitons. Si oui, retourne l'objet CellRangeAddress.
     * 
     * @param sheet the sheet containing the data.
     * @param rowNum the num of the row to copy.
     * @param cellNum the num of the cell to copy.
     * @return the CellRangeAddress created.
     */
    public static CellRangeAddress getMergedRegion(Sheet sheet, int rowNum, short cellNum)
    {
        for (int i = 0; i < sheet.getNumMergedRegions(); i++)
        {
            CellRangeAddress merged = sheet.getMergedRegion(i);
            if (merged.isInRange(rowNum, cellNum))
            {
                return merged;
            }
        }
        return null;
    }

    /**
     * Check that the merged region has been created in the destination sheet.
     * 
     * @param newMergedRegion the merged region to copy or not in the destination sheet.
     * @param mergedRegions the list containing all the merged region.
     * @return true if the merged region is already in the list or not.
     */
    private static boolean isNewMergedRegion(CellRangeAddressWrapper newMergedRegion, Set<CellRangeAddressWrapper> mergedRegions)
    {
        return !mergedRegions.contains(newMergedRegion);
    }
    }

    class CellRangeAddressWrapper implements Comparable<CellRangeAddressWrapper>
    {
    public CellRangeAddress range;

    /**
     * @param theRange the CellRangeAddress object to wrap.
     */
    public CellRangeAddressWrapper(CellRangeAddress theRange)
    {
        this.range = theRange;
    }

    /**
     * @param o the object to compare.
     * @return -1 the current instance is prior to the object in parameter, 0: equal, 1: after...
     */
    public int compareTo(CellRangeAddressWrapper o)
    {

        if (range.getFirstColumn() < o.range.getFirstColumn() && range.getFirstRow() < o.range.getFirstRow())
        {
            return -1;
        }
        else if (range.getFirstColumn() == o.range.getFirstColumn() && range.getFirstRow() == o.range.getFirstRow())
        {
            return 0;
        }
        else
        {
            return 1;
        }

    }
    }

2. Now in your @AfterClass call this lines after writing excel file.

     FileInputStream out1 = new FileInputStream("E:\\Register.xls");

        List<InputStream> strFile = new ArrayList<InputStream>();

        strFile.add(out1);

        Workbook book = new HSSFWorkbook();

        book = CopySheets.mergeExcelFiles(book, strFile);

        out = new FileOutputStream("E:\\Result.xls", true);

        book.write(out);

        out.close();

This will merge your test specific file in **"E:\\Result.xls"**

Alternative :

Note : Best Practice is , Please write this code in @AfterSuite as Under with all test excel.

    @AfterSuite
public static void endExecution()
{
    try
    {
        System.out.println("Execution is Ended..");
        File loginFile = new File("E:\\Login.xls");
        File registerFile = new File("E:\\Register.xls");
        FileInputStream out1 = null;
        FileInputStream out2 = null;

        if (loginFile.exists())
        {
            out1 = new FileInputStream(loginFile.getAbsolutePath());
        }

        if (registerFile.exists())
        {
            out2 = new FileInputStream(registerFile.getAbsolutePath());
        }

        List<InputStream> strFile = new ArrayList<InputStream>();
        if (out1 != null)
        {
            strFile.add(out1);
        }
        if (out2 != null)
        {
            strFile.add(out2);
        }

        File finalOutputfile = new File("E:\\Result.xls");
        if (finalOutputfile.exists())
        {
            finalOutputfile.delete();
            finalOutputfile.createNewFile();
        }

        Workbook book = new HSSFWorkbook();

        book = Copysheet.mergeExcelFiles(book, strFile);
        FileOutputStream out = new FileOutputStream(finalOutputfile, true);
        book.write(out);
        out.close();
    }
    catch (Exception e)
    {
        e.printStackTrace();
    }
}
  • i am folling above steps but i am getting error in copysheet class – Rutvi Soni Dec 20 '16 at 6:32
  • wait i am attaching screenshot – Rutvi Soni Dec 20 '16 at 6:38
  • Hi it is import error Please import statement correctly. – Sagar007 Dec 20 '16 at 6:52
  • ok.problem is solve of import – Rutvi Soni Dec 20 '16 at 7:02
  • Please correct answer if problem is solved. – Sagar007 Dec 20 '16 at 7:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.