Data Driven Framework -- how to read and write in excel sheet using Selenium WebDriver with java

Question

Here I'm using this code to read and I want to write out put on the same sheet.......I want to write results for every sys output according to putting output on relevant fields how to read and write the existing excel sheet? 1

How to write data into an Excel file using Selenium WebDriver?

@Test(priority=1)
public void Shift() throws Exception {
String dupshiftname=Skadmin.getData(62, 1);
String validshiftname=Skadmin.getData(63, 1);    

driver.findElement(By.linkText("ADMIN")).click();
driver.findElement(By.id("sessionname")).sendKeys(dupshiftname);
if (actualTitle19.contentEquals(expectedTitle19)){
System.out.println("2.Test Passed!-Submitting without shift name alert      displayed as[Shift name is required]");

//Here i want this above output to be written on excel sheet at particular cell }

public static String getData(int r, int c) throws EncryptedDocumentException,   InvalidFormatException, IOException
{    
FileInputStream FIS=new FileInputStream("G://workspace//sample pro//src//testData//excel.xlsx");
  Workbook WB=WorkbookFactory.create(FIS);

DataFormatter formatter = new DataFormatter(); //creating formatter using the default locale
Cell cell = WB.getSheet("Sheet1").getRow(r).getCell(c);
String str = formatter.formatCellValue(cell); //Returns the formatted value  of a cell as a String regardless of the cell type.

return str;
}

score 0 · Answer 1 · answered Aug 24 '16 at 08:50

for reading and writing in an excel file using java, you need api for this (Apache POI)

Code to read an excel file:

public class ReadExcel 
{
    public static void main(String[] args) 
    {
        try
        {
            FileInputStream file = new FileInputStream(new File("path\\to\\excel\\file.xlsx"));

            //Create Workbook instance holding reference to .xlsx file
            XSSFWorkbook workbook = new XSSFWorkbook(file);

            //Get first/desired sheet from the workbook
            XSSFSheet sheet = workbook.getSheetAt(0);

            //Iterate through each rows one by one
            Iterator<Row> rowIterator = sheet.iterator();
            while (rowIterator.hasNext()) 
            {
                Row row = rowIterator.next();
                //For each row, iterate through all the columns
                Iterator<Cell> cellIterator = row.cellIterator();

                while (cellIterator.hasNext()) 
                {
                    Cell cell = cellIterator.next();
                    //Check the cell type and format accordingly
                    switch (cell.getCellType()) 
                    {
                        case Cell.CELL_TYPE_NUMERIC:
                            System.out.print(cell.getNumericCellValue() + "t");
                            break;
                        case Cell.CELL_TYPE_STRING:
                            System.out.print(cell.getStringCellValue() + "t");
                            break;
                    }
                }
                System.out.println("");
            }
            file.close();
        } 
        catch (Exception e) 
        {
            e.printStackTrace();
        }
    }
}

Hope this will help you

score 0 · Answer 2 · answered Aug 24 '16 at 10:03

Code to write in an excel sheet.

Code:

public class WriteExcelDemo 
{
    public static void main(String[] args) 
    {
        //Blank workbook
        XSSFWorkbook workbook = new XSSFWorkbook(); 

        //Create a blank sheet
        XSSFSheet sheet = workbook.createSheet("Employee Data");

        //This data needs to be written (Object[])
        Map<String, Object[]> data = new TreeMap<String, Object[]>();
        data.put("1", new Object[] {"ID", "NAME", "LASTNAME"});
        data.put("2", new Object[] {1, "Amit", "Shukla"});
        data.put("3", new Object[] {2, "Lokesh", "Gupta"});
        data.put("4", new Object[] {3, "John", "Adwards"});
        data.put("5", new Object[] {4, "Brian", "Schultz"});

        //Iterate over data and write to sheet
        Set<String> keyset = data.keySet();
        int rownum = 0;
        for (String key : keyset)
        {
            Row row = sheet.createRow(rownum++);
            Object [] objArr = data.get(key);
            int cellnum = 0;
            for (Object obj : objArr)
            {
               Cell cell = row.createCell(cellnum++);
               if(obj instanceof String)
                    cell.setCellValue((String)obj);
                else if(obj instanceof Integer)
                    cell.setCellValue((Integer)obj);
            }
        }
        try
        {
            //Write the workbook in file system
            FileOutputStream out = new FileOutputStream(new File("howtodoinjava_demo.xlsx"));
            workbook.write(out);
            out.close();
            System.out.println("howtodoinjava_demo.xlsx written successfully on disk.");
        } 
        catch (Exception e) 
        {
            e.printStackTrace();
        }
    }
}

Hope this will solve your problem

Rajan thanks for your help, this will create a new file to write, i want to read and write on same (existing) excel file — Rajesh Natarajan, Aug 25 '16 at 09:52
I got you, I will send you the working code tomorrow for sure. Right now I am not able to access my laptop — rajan, Aug 25 '16 at 16:44

score 0 · Answer 3 · answered Aug 26 '16 at 05:44

Here you go.

Code: 

public class ReadWrite {

    public static void main(String[] args) {

        try {
            File excel = new File("D://raju.xlsx");
            FileInputStream fis = new FileInputStream(excel);
            XSSFWorkbook book = new XSSFWorkbook(fis);
            XSSFSheet sheet = book.getSheetAt(0);

            Iterator<Row> itr = sheet.iterator();

            // Iterating over Excel file in Java
            while (itr.hasNext()) {
                Row row = itr.next();

                // Iterating over each column of Excel file
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {

                    Cell cell = cellIterator.next();

                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        System.out.print(cell.getStringCellValue() + "\t");
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        System.out.print(cell.getNumericCellValue() + "\t");
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        System.out.print(cell.getBooleanCellValue() + "\t");
                        break;
                    default:

                    }
                }
                System.out.println("");
            }

            // writing data into XLSX file
            Map<String, Object[]> newData = new HashMap<String, Object[]>();
            newData.put("1", new Object[] { 1, "DELL", "7K", "Auto",
                    "USD" });


            Set<String> newRows = newData.keySet();
            int rownum = sheet.getLastRowNum();

            for (String key : newRows) {
                Row row = sheet.createRow(rownum++);
                Object[] objArr = newData.get(key);
                int cellnum = 0;
                for (Object obj : objArr) {
                    Cell cell = row.createCell(cellnum++);
                    if (obj instanceof String) {
                        cell.setCellValue((String) obj);
                    } else if (obj instanceof Boolean) {
                        cell.setCellValue((Boolean) obj);
                    } else if (obj instanceof Date) {
                        cell.setCellValue((Date) obj);
                    } else if (obj instanceof Double) {
                        cell.setCellValue((Double) obj);
                    }
                }
            }

            // open an OutputStream to save written data into Excel file

            FileOutputStream os = new FileOutputStream(excel);
            book.write(os);
            System.out.println("Writing on Excel file Finished ...");

            // Close workbook, OutputStream and Excel file to prevent leak
            os.close();
            book.close();
            fis.close();

        } catch (FileNotFoundException fe) {
            fe.printStackTrace();
        } catch (IOException ie) {
            ie.printStackTrace();
        }
    }
}

I hope this will solve your problem

Rajan, please can u add this code to my code in appropriate place...i want to print sysoutprntln results in excel sheet for every @Tests..... — Rajesh Natarajan, Aug 26 '16 at 06:24

Data Driven Framework -- how to read and write in excel sheet using Selenium WebDriver with java

3 Answers3