Write Data in Data sheet in particular Cell in Particular Testcase using setCellData() function - not working (Selenium WebDriver in C#) | Selenium Webdriver Tutorial with c# |Selenium C# training

Selenium C# Training Forum

Write Data in Data sheet in particular Cell in Particular Testcase using setCellData() function - not working (Selenium WebDriver in C#)

M
Md Shafikul Islam Post on 13/09/2019

Hello Genius,

I have developed Hybrid Framework. I have three Sheets in Excell which are Keywords, Testcases and Data. I want to Edit Data in particular Cell in Particular Testcase in Data sheet using setCellData() function. I have tried many different way but it is not updating the data. It seems like setCellData() function is useless. 

In the video, Instructor described only reading the data, not setting the Data. I have attached all the code and StackTrace in the Word file. Please look into it.

TCID	Desc	Keyword	Object	Object1	Data
LoginTC8525	 	openBrowser	 	 	Browser
LoginTC8525	 	navigate	url1	 	 
LoginTC8525	 	input	username_name	 	ProviderIDNumber

LoginTC8525	 	 
Runmode	Browser	ProviderIDNumber
Y	Chrome	1234

public void executeKeywords(string testUnderExecution, ExcelReaderFile xls, Dictionary<string,string> testData)
        {
            
            app = new AppKeywords(test);

            int rows = xls.getRowCount(Constants.SHEETNAME);
            for(int rNum = 2; rNum <= rows; rNum++)
            {
                //app.reportFailure("Error Message");
                string TCID = xls.getCellData(Constants.SHEETNAME, Constants.TCID_COL, rNum);
                if (TCID.Equals(testUnderExecution))
                {
                    string data = null;
                    string data1 = null;
                    string keyword = xls.getCellData(Constants.SHEETNAME, Constants.KEYWORD_COL, rNum);
                    string objct = xls.getCellData(Constants.SHEETNAME, Constants.OBJECT_COL, rNum);
                    string objct1 = xls.getCellData(Constants.SHEETNAME, Constants.OBJECT1_COL, rNum);
                    string key = xls.getCellData(Constants.SHEETNAME, Constants.DATA_COL, rNum);
                    string k = null;

                    //string key2 = app.setCellData(Constants.SHEETNAME2, "ProviderId", rNum, app.getProviderId());
                    if (!key.Equals(""))
                    {
                        if(!key.Equals("") && key.Equals("ProviderIDNumber"))
                        {
                            //k = testData["ProviderIDNumber"];
                            

                            int rows2 = xls.getRowCount(“Data”);
                            for (int rNum1 = 2; rNum1 <= rows2; rNum1++)
                            {
                                k = xls.getCellData("Data", "ProviderIDNumber", rNum1);                                

                                if (!k.Equals(""))
                                {
                                    data1 = app.setCellData(“Data”, "ProviderIDNumber", rNum1, app.getProviderId());
                                }
                                
                            }

                        }                        

                        data = testData[key];
                    }                   

                    test.Log(Status.Info, TCID + "-----" + keyword + "-----" + objct + "-----" + data);

                    string result = "";
                    if (keyword.Equals("openBrowser"))
                        result = app.openBrowser(data);
                    else if (keyword.Equals("navigate"))
                        result = app.navigate(objct);
                    else if (keyword.Equals("input"))
                        result = app.input(objct, data1);
                    //else if (keyword.Equals("input"))
                    //    result = app.input(objct, data);
                    else if (keyword.Equals("click"))
                        result = app.click(objct);
                    else if (keyword.Equals("clickingon"))
                        result = app.clickingon(objct);
                    else if (keyword.Equals("closeBrowser"))
                        result = app.closeBrowser();
                    else if (keyword.Equals("wait"))
                        //result = app.wait(objct);
                        result = app.wait(data);
                    else if (keyword.Equals("Login"))
                        result = app.Login(testData);
                    else if (keyword.Equals("select"))
                        //result = app.wait(objct);
                        result = app.select(objct, data);
                    else if (keyword.Equals("verifyLoginDetails"))
                        result = app.verifyLoginDetails(testData);
                    else if (keyword.Equals("verifyElementPresent"))
                        result = app.verifyElementPresent(objct);
                    else if (keyword.Equals("verifyElementNotPresent"))
                        result = app.verifyElementNotPresent(objct);
                    else if (keyword.Equals("TCCMSLogin"))
                        result = app.TCCMSLogin(testData);
                    else if (keyword.Equals("FiscalLogin"))
                        result = app.FiscalLogin(testData);
                    /* else if (keyword.Equals("verifyFiscalLogin"))
                         result = app.verifyFiscalLogin(testData["ExpectedResult"]);*/

                    else if (keyword.Equals("verifyTCCMSLogin"))
                        result = app.verifyTCCMSLogin(testData["ExpectedResult"]);
                    else if (keyword.Equals("defaultLogin"))
                        result = app.defaultLogin();
                    else if (keyword.Equals("clickAndWait"))
                        result = app.clickAndWait(objct, objct1);
                    else if (keyword.Equals("selectDate"))
                        //result = app.selectDate(data);
                        result = app.selectDate(objct, data);
                    else if (keyword.Equals("switchToParentWindow"))
                        result = app.switchToParentWindow();
                    else if (keyword.Equals("clearTextFromTextBox"))
                        result = app.clearTextFromTextBox(objct);
                    else if (keyword.Equals("alert"))
                        result = app.alert(objct);
                    else if (keyword.Equals("selectFromDropDownListSelectByText"))
                        result = app.selectFromDropDownListSelectByText(objct, data);
                    else if (keyword.Equals("selectFromDropDownListSelectByValue"))
                        result = app.selectFromDropDownListSelectByValue(objct, data);
                    else if (keyword.Equals("takeScreenshotAsNeeded"))
                        result = app.takeScreenshotAsNeeded();
                    //else if (keyword.Equals("getProviderId"))
                    //    result = app.getProviderId();

                    if (!result.Equals(Constants.PASS))
                    {
                        app.reportFailure(result);
                        Assert.Fail(result);
                    }

                }
            }            
        }
public string getProviderId()
        {
            try
            {
                con = new OracleConnection(connectionString);
                con.Open();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.StackTrace);
                Console.WriteLine("Could not establish the connection");
            }
            try
            {
                cmd = new OracleCommand();
                cmd.Connection = con;
                cmd.CommandText = "select tccmsqa.get_provider_id from dual";
                providerId = (string)cmd.ExecuteScalar();
                Console.WriteLine("Provider Id = " + providerId);
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error in firing the query");
                Console.WriteLine(ex.StackTrace);
            }
            try
            {
                if (reader != null)
                    reader.Close();
                if (con != null)
                    con.Close();
            }
            catch (Exception ex)
            {

            }
            return providerId;
        }

public string setCellData(string sheetName, string colName, int rowNum, string data)
        {
            fs = new FileStream(path, FileMode.Open, FileAccess.Read);
            workbook = new XSSFWorkbook(fs);
            if (rowNum <= 0)
                return "";
            int colNum = -1;
            int index = workbook.GetSheetIndex(sheetName);
            if (index == -1)
                return "";
            sheet = workbook.GetSheetAt(index);
            row = sheet.GetRow(0);
            for (int i = 0; i < row.LastCellNum; i++)
            {
                if (row.GetCell(i).StringCellValue.Equals(colName))
                {
                    colNum = i;
                }
            }
            if (colNum == -1)
                return "";
            row = sheet.GetRow(rowNum - 1);
            if (row == null)
                row = sheet.CreateRow(rowNum - 1);
            cell = row.GetCell(colNum - 1);
            if (cell == null)
                cell = row.CreateCell(colNum);

            ICellStyle cs = workbook.CreateCellStyle();
            cs.WrapText = true;
            cell.CellStyle = cs;
            cell.SetCellValue(data);

            FileStream f = new FileStream(path, FileMode.Create, FileAccess.ReadWrite);
            workbook.Write(f);
            f.Close();
            fs.Close();

            return data;

        }

Comments: 33 line is “ProviderIDNumber” but in the debug mode, cursor is not going inside of the “if” condition. Separately, I have used only “app.setCellData” which is not even updating the data in the cell.




StackTrace:
==========
Test Name:	loginTC8525(System.Collections.Generic.Dictionary`2[System.String,System.String])
Test FullName:	TCCMS_Automation.TestCases.LoginTC8525.loginTC8525(System.Collections.Generic.Dictionary`2[System.String,System.String])
Test Source:	C:\Users\DE01BAR\TCCMS_Automation\DEV\TCCMS_Automation\TestCases\LoginTC8525.cs : line 17
Test Outcome:	Failed
Test Duration:	0:02:19.992

Result StackTrace:	
at TCCMS_Automation.Keywords.executeKeywords(String testUnderExecution, ExcelReaderFile xls, Dictionary`2 testData) in C:\Users\DE01BAR\TCCMS_Automation\DEV\TCCMS_Automation\Keywords.cs:line 137
   at TCCMS_Automation.TestCases.LoginTC8525.loginTC8525(Dictionary`2 data) in C:\Users\DE01BAR\TCCMS_Automation\DEV\TCCMS_Automation\TestCases\LoginTC8525.cs:line 30
Result Message:	Fail

 

Please help me if any of our friend implemented. It's very urgent.

 

Kind regrads,

Shafik