public static void main(String[] args) throws InterruptedException, IOException, InvalidFormatException {
FileInputStream file = new FileInputStream(new File("C:\\Test.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);
//collection api List<String> num1 = new ArrayList<String>();
//get row count int index = workbook.getSheetIndex("Addition");
sheet = workbook.getSheetAt(index); int number=sheet.getLastRowNum()+1;
//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: double pname = cell.getNumericCellValue(); String total2 = Double.toString(pname); num1.add(total2); break; case Cell.CELL_TYPE_STRING: String pquantity = cell.getStringCellValue(); num1.add(pquantity); break; } }
Thread.sleep(5000); //clisck on autosuggest driver.findElement(By.xpath("/html/body/div/div[3]/form/div[2]/div[2]/div[1]/div[2]/div[2]/div[1]/div/ul/li[1]/div/div[2]")).click();
Thread.sleep(5000); //collect all calculator elemnts List<WebElement> element = driver.findElements(By.xpath("//span[@class='cwbts']")); int rownum = 0; int cellnum = 0; //Iterate between list and webelement, then match to click for(int i = 6;i<num1.size();){
for(int h =0;h<element.size();h++){ String g = element.get(h).getText(); if((num1.get(i)).equals(g)){ element.get(h).click(); Thread.sleep(5000); }
}
for(int h =0;h<element.size();h++){
switch(num1.get(i+2)){ case "+": driver.findElement(By.xpath("/html/body/div[1]/div[5]/div[4]/div[7]/div[1]/div[3]/div/div[2]/div[2]/div/div/ol/li/div[1]/div/div/div[3]/div[2]/div[2]/div[5]/div[4]/div/span")).click(); break; case "*": driver.findElement(By.xpath("/html/body/div[1]/div[5]/div[4]/div[7]/div[1]/div[3]/div/div[2]/div[2]/div/div/ol/li/div[1]/div/div/div[3]/div[2]/div[2]/div[3]/div[4]/div/span")).click(); break; case "-": driver.findElement(By.xpath("/html/body/div[1]/div[5]/div[4]/div[7]/div[1]/div[3]/div/div[2]/div[2]/div/div/ol/li/div[1]/div/div/div[3]/div[2]/div[2]/div[4]/div[4]/div/span")).click(); break; case"/": driver.findElement(By.xpath("/html/body/div[1]/div[5]/div[4]/div[7]/div[1]/div[3]/div/div[2]/div[2]/div/div/ol/li/div[1]/div/div/div[3]/div[2]/div[2]/div[2]/div[4]/div/span")).click(); break; }
} for(int h =0;h<element.size();h++){ String g = element.get(h).getText();
List<String> output = new ArrayList<String>(); int t=0; output.add(driver.findElement(By.xpath("/html/body/div/div[5]/div[4]/div[7]/div[1]/div[3]/div/div[2]/div[2]/div/div/ol/li/div[1]/div/div/div[2]/div[3]/div/div[2]/span")).getText()); System.out.println(output.get(t));
//Writing on excel InputStream inp = new FileInputStream("C:\\Test.xlsx"); Workbook wb = WorkbookFactory.create(inp); Sheet sheet2 = wb.getSheetAt(0);
Row row = sheet2.getRow(rownum+1); rownum++;
Cell cell = row.createCell(cellnum + 4); cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(output.get(t)); try { //Write the workbook in file system FileOutputStream out = new FileOutputStream("C:\\Test.xlsx"); wb.write(out); out.close(); } catch (Exception e) { e.printStackTrace(); }