package Utility;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Color;
import org.apache.poi.xssf.usermodel.*;

public class ExcelUtils {

    private XSSFSheet excelWSheet;
    private XSSFWorkbook excelWBook;
    private XSSFCell cell;
    private List<String> testSteps;
    private List<String> usedPages;
    private List<String> parameters;
    private List<String> testCases;

    /**
     * open excel file
     *
     * @param path to the file
     * @throws Exception
     */
    public void setExcelFile(String path) throws Exception {
        FileInputStream excelFile = new FileInputStream(path);
        excelWBook = new XSSFWorkbook(excelFile);

    }

    /**
     * set excel sheet
     *
     * @param sheetName name of the sheet
     */
    public void setExcelWSheet(String sheetName) {
        excelWSheet = excelWBook.getSheet(sheetName);
    }

    /**
     * read data from excel
     *
     * @param rowNum number of the row
     * @param colNum number of the column
     * @return data from cell
     * @throws Exception
     */
    public String getStringCellData(int rowNum, int colNum) throws Exception {
        String cellData;
        cell = excelWSheet.getRow(rowNum).getCell(colNum);
        cellData = cell.getStringCellValue();
        return cellData;
    }

    public void setCellData(int rowNum, int colNum, String value)
    {
        cell = excelWSheet.getRow(rowNum).getCell(colNum);
        cell.setCellValue(value);
    }

    /**
     * get number of test steps
     *
     * @return number of test steps
     * @throws Exception
     */
    public int getNumberOfTestSteps() throws Exception {
        cell = excelWSheet.getRow(1).getCell(4);
        double numberOfTestSteps = cell.getNumericCellValue();
        return (int) numberOfTestSteps;
    }

    private List<String> getDataFromColumn(List<String> list, int cellNumber) throws Exception {
        for (int i = 1; i <= getNumberOfTestSteps(); i++)//i=1 because first row in excel is for names of columns
        {
            cell = excelWSheet.getRow(i).getCell(cellNumber);
            if (cell == null) {
                list.add(null);
            } else {
                list.add(getStringCellData(i, cellNumber));
            }
        }
        return list;
    }

    /**
     * get list of test steps
     *
     * @return list of test steps
     * @throws Exception
     */
    public List<String> getTestSteps() throws Exception {
        testSteps = new ArrayList<String>();
        testSteps = getDataFromColumn(testSteps, 1);
        return testSteps;
    }

    /**
     * get list of used pages
     *
     * @return list of used pages
     * @throws Exception
     */
    public List<String> getNamesOfUsedPages() throws Exception {
        usedPages = new ArrayList<String>();
        usedPages = getDataFromColumn(usedPages, 0);
        return usedPages;
    }

    /**
     * get parameters for used methods
     *
     * @return list of parameters
     * @throws Exception
     */
    public List<String> getParameters() throws Exception {
        parameters = new ArrayList<String>();
        parameters = getDataFromColumn(parameters, 2);
        return parameters;
    }

    /**
     * get list of test cases
     *
     * @return list of test cases
     */
    public List<String> getTestCases() {
        testCases = new ArrayList<String>();
        int numberOfSheets = excelWBook.getNumberOfSheets();
        for (int i = 0; i < numberOfSheets; i++) {
            testCases.add(excelWBook.getSheetName(i));
        }
        return testCases;
    }





}
