Selenium : Create Excel ,Read and Write Excel into a Array

Selenium : Create Excel ,Read and Write Excel into a Array

Link to download JAR files for Apache POI : http://poi.apache.org/download.html#POI-3.9 and http://www.apache.org/dyn/closer.cgi/poi/release/bin/poi-bin-3.9-20121203.zip (might change according to the new update ).

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.InputStream;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.usermodel.Cell;

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;

public class Excel_Operations

{

public static void main(String[] args)

{

System.out.print(Excel_Operations.create_Excel("C:\\Created_with_selenium.xls","D1"));

System.out.print(Excel_Operations.read_excel("C:\\Created_with_selenium.xls","D1"));

String sData[][]=new String[1][1];

sData[0][0]="00";

//sData[0][1]=1;

//sData[1][0]=2;

//sData[1][1]=3;

System.out.print(Excel_Operations.write_excel("C:\\Created_with_selenium.xls", "D1", sData));

}

//-------------------------------------------------------------------------------------

public static boolean create_Excel(String sPath,String sSheet_name)

{

Workbook wb = new HSSFWorkbook(); // or new XSSFWorkbook();

Sheet sheet1 = wb.createSheet(sSheet_name);

try{

FileOutputStream fileOut = new FileOutputStream(sPath);

wb.write(fileOut);

fileOut.close();

return true;

}catch(Exception E)

{

System.out.print("could not create an excel file");

return false;

}

}

//---------------------------------------------------------------------------

public static String read_excel(String sPath,String sSheet_name)

{

String sData="";

try

{

InputStream oFile = new FileInputStream(sPath);

Workbook wb = WorkbookFactory.create(oFile);

Sheet sheet = wb.getSheet(sSheet_name);//.getSheet("Sheet1");

for (Row row : sheet) //Row Count=sheet.getLastRowNum();

{

for (Cell cell : row) //Column Count= row.getLastCellNum();

{

switch (cell.getCellType())//1- string

{

case 0://cell.CELL_TYPE_NUMERIC:

sData=sData+cell.getNumericCellValue();

break;

case 1://cell.CELL_TYPE_STRING:

sData=sData+cell.getStringCellValue();

break;

}

sData=sData+"|";

}

sData=sData+"\n";

}

}catch(Exception e)

{

System.out.print("File not found");

}

return sData;

}

//-------------------------------------------------------------------------------

public static boolean write_excel(String sPath,String sSheet_name,String oData[][])

{

Workbook wb = new HSSFWorkbook();

Sheet sheet = wb.createSheet(sSheet_name);

int iRows=oData.length;

int iCols=oData[0].length;

for (int iR_count=0;iR_count<=iRows-1;iR_count++ )

{

Row row = sheet.createRow(iR_count);

for (int iC_count=0;iC_count<=iCols-1;iC_count++)

{

row.createCell(iC_count).setCellValue(oData[iR_count][iC_count]);

}

}

try{

FileOutputStream fileOut = new FileOutputStream(sPath);

wb.write(fileOut);

fileOut.close();

}catch (Exception e)

{

System.out.print("file not created ");

return false;

}

return true;

}

}

Selenium : Excel operations

Selenium : Excel operations 

Please visit http://poi.apache.org/spreadsheet/quick-guide.html#NewWorkbook  for more details 

Link to download JAR files for Apache POI : http://poi.apache.org/download.html#POI-3.9 and http://www.apache.org/dyn/closer.cgi/poi/release/bin/poi-bin-3.9-20121203.zip (might change according to the new update ).

//New Workbook

Workbook wb = new HSSFWorkbook();

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

Workbook wb = new XSSFWorkbook();

FileOutputStream fileOut = new FileOutputStream("workbook.xlsx");

wb.write(fileOut);

fileOut.close();

//New Sheet

Workbook wb = new HSSFWorkbook(); // or new XSSFWorkbook();

Sheet sheet1 = wb.createSheet("new sheet");

Sheet sheet2 = wb.createSheet("second sheet");

// Note that sheet name is Excel must not exceed 31 characters

// and must not contain any of the any of the following characters:

// 0x0000

// 0x0003

// colon (:)

// backslash (\)

// asterisk (*)

// question mark (?)

// forward slash (/)

// opening square bracket ([)

// closing square bracket (])

// You can use org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)}

// for a safe way to create valid names, this utility replaces invalid characters with a space (' ')

String safeName = WorkbookUtil.createSafeSheetName("[O'Brien's sales*?]"); // returns " O'Brien's sales "

Sheet sheet3 = wb.createSheet(safeName);

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

//Working with different types of cells

Workbook wb = new HSSFWorkbook();

Sheet sheet = wb.createSheet("new sheet");

Row row = sheet.createRow((short)2);

row.createCell(0).setCellValue(1.1);

row.createCell(1).setCellValue(new Date());

row.createCell(2).setCellValue(Calendar.getInstance());

row.createCell(3).setCellValue("a string");

row.createCell(4).setCellValue(true);

row.createCell(5).setCellType(Cell.CELL_TYPE_ERROR);

// Write the output to a file

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

//Iterate over rows and cells

Sheet sheet = wb.getSheetAt(0);

for (Row row : sheet) {

for (Cell cell : row) {

// Do something here

}

}

//Getting the cell contents

To get the contents of a cell, you first need to know what kind of cell it is

(asking a string cell for its numeric contents will get you a

NumberFormatException for example). So, you will want to switch on the cells

type, and then call the appropriate getter for that cell.

In the code below, we loop over every cell in one sheet, print out the cells

reference (eg A3), and then the cells contents.

// import org.apache.poi.ss.usermodel.*;

Sheet sheet1 = wb.getSheetAt(0);

for (Row row : sheet1) {

for (Cell cell : row) {

CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());

System.out.print(cellRef.formatAsString());

System.out.print(" - ");

switch (cell.getCellType()) {

case Cell.CELL_TYPE_STRING:

System.out.println(cell.getRichStringCellValue().getString());

break;

case Cell.CELL_TYPE_NUMERIC:

if (DateUtil.isCellDateFormatted(cell)) {

System.out.println(cell.getDateCellValue());

} else {

System.out.println(cell.getNumericCellValue());

}

break;

case Cell.CELL_TYPE_BOOLEAN:

System.out.println(cell.getBooleanCellValue());

break;

case Cell.CELL_TYPE_FORMULA:

System.out.println(cell.getCellFormula());

break;

default:

System.out.println();

}

}

}

//Reading and Rewriting Workbooks

InputStream inp = new FileInputStream("workbook.xls");

//InputStream inp = new FileInputStream("workbook.xlsx");

Workbook wb = WorkbookFactory.create(inp);

Sheet sheet = wb.getSheetAt(0);

Row row = sheet.getRow(2);

Cell cell = row.getCell(3);

if (cell == null)

cell = row.createCell(3);

cell.setCellType(Cell.CELL_TYPE_STRING);

cell.setCellValue("a test");

// Write the output to a file

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

VBS : Tutorial 5 (Funtions )

VBS : Tutorial 5 (Funtions )

Functions

 

Example 1 :         Syntax

 

Call func_name

Function Func_name()

                msgbox 1+2

end function

 

 

Example 2:          Returning Value

msgbox func_name

 

Function func_name()

                func_name=1+2

end function

 

Example :Calculate age of a person

 

Dim sVar1

sVar1=inputbox("e.g.  1947 "," Please Enter Years")

msgbox fn(sVar1)

 

Function fn(x)

                fn= year(date)-x

End Function

 

 

Example 3:          Sub-routine

Call func_name

 

Sub Func_name()

                msgbox 1+2

end Sub

 

Example 4:          Function vs Sub

msgbox func_name

 

Sub Func_name()

                func_name = 1+2

end Sub

 

Example 5 :         Sending Values to Functions

 

Dim a,b

a=3

b=4

 

msgbox Add(a,b)

 

Function Add(x,y)

                Add=x+y

End Function

 

 

Example 6:          Sending arrays to function

 

Dim a(9)

Call Add(a)

 

Function Add(x)

                msgbox isarray(x)           

End Function

 

Example 7 :         Catching the Return Value  and Flags in framework

 

Dim a(9),bVar

bVar = Add(a)

msgbox  bVar

 

Function Add(x)

                Add=isarray(x) 

End Function

 

Example 8:          Function accept multiple arguements but returns only 1 value

 

Example 9:          Variables are Global Unless declared ,-----NOT DECLARED i.e., PASS BY REF

msgbox a

call fn

call fn2()

msgbox a

 

Function fn()

                a=5

                msgbox a

end function

 

Function fn2()

                msgbox a

                a="dfsdf"

                msgbox a

end function

 

Example 10: ---             DECLARED

msgbox a

call fn

call fn2()

msgbox a

 

Function fn()

                a=5

                msgbox a

end function

 

Function fn2()

                Dim a

                msgbox a

                a="dfsdf"

                msgbox a

end function

 

Example

dim a

a=10.5

msgbox a

call fn

call fn2()

msgbox a

 

Function fn()

dim a

                a=5

                msgbox a

end function

 

Function fn2()

dim a

                msgbox a

                a="piyush"

                msgbox a

end function

 

Example 9 :         Pass By Value and pass By Ref  , By Default --> ByRef

 

 

-------------------------------------------

By Default variables are passed ByRef

------------------------------------------

Ex Byval

 

Option Explicit

Dim strTest

 

strTest = "FOO"

RunTest (strTest)

msgbox  strTest

 

Sub RunTest(ByVal strIn)

                WScript.Echo strIn

                strIn = "BAR"

                msgbox strIn

End Sub

 

OUTPUT:

FOO

 BAR

 FOO

------------------------------------------------------

The default is ByRef. This stands for "By Reference"

any changes that you make inside the Sub/Function will be seen outside the Sub/Function. Here is a demonstration:

 

Option Explicit

 

Dim strTest

 

strTest = "FOO"

RunTest (strTest )

msgbox  strTest

 

Sub RunTest(strTest)

                WScript.Echo strTest

                strTest = "BAR"

                msgbox strTest

End Sub

 

OUTPUT: >>

FOO

 BAR

 BAR

 

Example 1:  Redim and preserve

Redim a(1,2)

Redim a(2,3)

 

Example 2

Redim preserve  a(1,2)

Redim a(2,3)