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;

}

}