Selenium WebDriver Java Framework Course Limited Time Offer for $20

Selenium WebDriver Java Framework Course Limited Time Offer for $20

 

Print

How to read a specific cell value in Excel spreadsheet

When automating test cases, sometimes you need to read a specific cell value of an Excel spreadsheet for data driven testing.  Below is the code snippet for your references. 

 

using System.Linq;
using System.Text;
using System.Reflection;
using Microsoft.Office.Interop.Excel;

namespace com.seleniummaster
{
//this method will read specific cell value in Excel spreadsheet
public static string ReadExcel(string filepath, string filename, string sheetname, int rowNumber, int colNumber)
{
string valueofcell;

string filePathAndName = @filepath + @"\" + filename;

//creating ExcelApp
try
{
Application xlApp = new Application();

Workbook xlWorkbook = xlApp.Workbooks.Open(filePathAndName);
Worksheet xlWorkSheet = (Worksheet)xlWorkbook.Sheets.get_Item(sheetname);
Range xlRange = xlWorkSheet.UsedRange;
if (xlRange.Cells[rowNumber, colNumber].Value2 != null)
{
//closing workbook
valueofcell = xlRange.Cells[rowNumber, colNumber].Value2.ToString();
//if(valueofcell == null){
xlWorkbook.Close(true, Type.Missing, Type.Missing);
return valueofcell;
}
else
{
//closing workbook
xlWorkbook.Close(true, Type.Missing, Type.Missing);
return "Empty Cell";
}

}
catch (Exception e)
{
Console.WriteLine("Excel read error");
return e.ToString();

}

}
}