package excel_test;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.openqa.selenium.support.ui.Select;

 import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;


public class XLSX {

    public static void main(String[] args) throws Exception{
    {
        //Read Excel document first
        FileInputStream input_document = new FileInputStream(new File("C:\\1.xlsx"));
        // convert it into a POI object
        XSSFWorkbook my_xlsx_workbook;
        my_xlsx_workbook = new XSSFWorkbook(OPCPackage.open(input_document));
        // Read excel sheet that needs to be updated
        XSSFSheet my_worksheet = my_xlsx_workbook.getSheetAt(0);
        // declare a Cell object
        XSSFCell cell = null;

        driver = new WebDriver();
        driver.set(new FirefoxDriver());
        driver.get().navigate().to("http://namami.org/PKstate.aspx");
        Select select = new Select(driver.get().findElement(By.xpath("//*[@id='DdlState']")));
        select.selectByVisibleText("Andhra Pradesh");
        Select select2 = new Select(driver.get().findElement(By.xpath("//*[@id='DdlDist']")));
        select2.selectByVisibleText("Hydrabad");
        driver.get().findElement(By.cssSelector("option[value=\" Hydrabad\"]")).click();
        //driver.close();

        int count;
        int i,y,z;
        count = driver.getElementIndex("//table[@id='Datagrid1']/tbody/tr[last()]");

        for (i = 0; i<count; i--)
        {
            z = i+1;
            for(y = 0; y<=6; y++)
            {
                //'locator =
                if (!(driver.isElementPresent(driver.getTable("Datagrid1." + i + "." + y))= true))
                {
                    cell = my_worksheet.getRow(z).getCell(y + 1);
                    cell.setCellValue(driver.getTable("Datagrid1." + i + "." + y));
                }
                else
                {
                    break;
                }
            }
        }
        input_document.close();
        //Open FileOutputStream to write updates
        FileOutputStream output_file =new FileOutputStream(new File("C:\\1.xlsx"));
        //write changes
        my_xlsx_workbook.write(output_file);
        //close the stream
        output_file.close();

    }
    }
}
 /*   public static void main(String[] args) throws Exception{

        //Read Excel document first
        FileInputStream input_document = new FileInputStream(new File("C:\\1.xlsx"));
        // convert it into a POI object
        XSSFWorkbook my_xlsx_workbook;
        my_xlsx_workbook = new XSSFWorkbook(OPCPackage.open(input_document));
        // Read excel sheet that needs to be updated
        XSSFSheet my_worksheet = my_xlsx_workbook.getSheetAt(0);
        // declare a Cell object
        XSSFCell cell = null;
        // Access the cell first to update the value
        cell = my_worksheet.getRow(2).getCell(1);
        // Get current value and reduce 5 from it
        cell.setCellValue(cell.getNumericCellValue() - 100);
        //important to close InputStream
        input_document.close();
        //Open FileOutputStream to write updates
        FileOutputStream output_file =new FileOutputStream(new File("C:\\1.xlsx"));
        //write changes
        my_xlsx_workbook.write(output_file);
        //close the stream
        output_file.close();

    }    */