package reader;
import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.util.ArrayList; import java.util.Iterator; import java.util.List; import java.util.Vector;
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.poifs.filesystem.POIFSFileSystem; import org.apache.poi.ss.usermodel.Cell; import org.openqa.selenium.By; import org.openqa.selenium.WebDriver; import org.openqa.selenium.ie.InternetExplorerDriver;
public class ReadExcel { static String homeurl="http://192.168.5.19:79/Home.aspx";
public static void main( String [] args ) throws InterruptedException, IOException {
String fileName="E:\\excellogin.xls";;
//Read an Excel File and Store in a Vector
Vector dataHolder=readExcelFile(fileName);
//Print the data read
printToConsole(dataHolder);
// writeDataToExcelFile(fileName);
}
public static Vector readExcelFile(String fileName)
{
/** --Define a Vector
--Holds Vectors Of Cells
*/
Vector cellVectorHolder = new Vector();
List cellDataList = new ArrayList();
try{
/** Creating Input Stream**/
//InputStream myInput= ReadExcelFile.class.getResourceAsStream( fileName );
FileInputStream myInput = new FileInputStream(fileName);
/** Create a POIFSFileSystem object**/
POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
/** Create a workbook using the File System**/
HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
/** Get the first sheet from workbook**/
HSSFSheet mySheet = myWorkBook.getSheetAt(0);
/** We now need something to iterate through the cells.**/
Iterator rowIter = mySheet.rowIterator();
while(rowIter.hasNext()){
HSSFRow myRow = (HSSFRow) rowIter.next();
Iterator cellIter = myRow.cellIterator();
Vector cellStoreVector=new Vector();
while(cellIter.hasNext()){
HSSFCell myCell = (HSSFCell) cellIter.next();
cellStoreVector.addElement(myCell);
}
cellVectorHolder.addElement(cellStoreVector);
}
}catch (Exception e){e.printStackTrace(); }
return cellVectorHolder;
}
private static void printToConsole(List cellDataList) throws InterruptedException, IOException
{
for (int i = 1; i < cellDataList.size()+1; i++)
{
List cellTempList = (List) cellDataList.get(i);
//System.out.print(cellTempList + "\t");
final String sUrl = "http://192.168.5.19:79/ExATLogin.aspx?";
WebDriver d1 = new InternetExplorerDriver();
d1.get(sUrl);
HSSFCell hssfCell1 = (HSSFCell) cellTempList.get(0);
String stringCellValue1 = hssfCell1.toString();
System.out.print("UserName:" + stringCellValue1 + "\t");
d1.findElement(By.id("txt_UserName")).clear();
d1.findElement(By.id("txt_UserName")).sendKeys(stringCellValue1);
HSSFCell hssfCell = (HSSFCell) cellTempList.get(1);
String stringCellValue = hssfCell.toString();
System.out.print("Password:" + stringCellValue + "\t");
d1.findElement(By.id("txt_Password")).clear();
d1.findElement(By.id("txt_Password")).sendKeys(stringCellValue);
Thread.sleep(3000);
d1.findElement(By.name("btn_Login")).click();
String curUrl=d1.getCurrentUrl();
System.out.println("\n");
System.out.println(curUrl);
if(homeurl.equals(curUrl)){
System.out.println("Login successful");
}
else
System.out.println("Login not successful");
Thread.sleep(3000);
}
}
private static void write1() throws IOException{
FileInputStream file = new FileInputStream(new File("E:\\excellogin.xls"));
HSSFWorkbook workbook = new HSSFWorkbook(file);
HSSFSheet sheet = workbook.getSheetAt(0);
Cell cell = null;
Cell cell1 = null;
Cell cell2 = null;
int row=1;
for(;row<5;row++){
cell=sheet.getRow(row).getCell(0);
cell1=sheet.getRow(row).getCell(1);
if(cell.equals("superadmin") && cell1.equals("Exdion")){
cell2 = sheet.getRow(row).getCell(2);
cell2.setCellValue(cell.getStringCellValue().concat("pass"));
}
else{
cell2 = sheet.getRow(row).getCell(2);
cell2.setCellValue(cell.getStringCellValue().concat("fail"));
}
}
file.close();
FileOutputStream outFile =new FileOutputStream(new File("E:\\excellogin.xls"));
workbook.write(outFile);
outFile.close();
}
}
my question is i want to wite the result in to the same excel file....... please help me. if username is superadmin and password is Exdion. then the status is pass othewise fail.