Whether we are using a data driven framework or not, we may come across different scenarios where we need to read data from an excel sheet.
we can get the neighbor data by changing the row or column number.
How we read data may depends on the structure which data is stored in excel sheet.
Lets consider some such scenarios.
- Row and Column of data is known
If this is the case , we need to pass path of the excel file, sheet name , row and column to our method to retrieve data. Such a method is given below.
This method will return the test found in the given location.
This method will return the test found in the given location.
public string GetExcelCellText(string fileName, string sheetname, int row, int col) { List<int> cellDetails = new List<int>(); Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Open(@fileName, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets[sheetname]; Range cells = xlWorkSheet.Cells[row, col]; string cellText = cells.Value.ToString(); WriteLog("info", "Text found in Data Sheet : " + cells.Value); xlWorkBook.Close(true, null, null); xlApp.Quit(); Marshal.ReleaseComObject(xlWorkSheet); Marshal.ReleaseComObject(xlWorkBook); Marshal.ReleaseComObject(xlApp); return cellText; }
- Row and Column of data is unknown
In this case , we don't know the position of the data we need to extract in excel sheet.so we need to loop through all the data in the given sheet and get the row and column number.
If we know any neighbor text of the data we actually needed, we could use this method.
A sample is given below.
public List<int> ExcelTextCellFinder(string strFileName, string strSheetname, string strTextToFind) { List<int> cellDetails = new List<int>(); Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Open(@strFileName, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets[strSheetname]; Range cells = xlWorkSheet.Cells; Range match = cells.Find(strTextToFind, LookAt: Excel.XlLookAt.xlPart) as Excel.Range; string matchAdd = match != null ? match.Address : null; cellDetails.Add(Convert.ToInt32(match.Row)); cellDetails.Add(Convert.ToInt32(match.Column)); // WriteLog("info", "Text found at Cell "+ matchAdd); xlWorkBook.Close(true, null, null); xlApp.Quit(); Marshal.ReleaseComObject(xlWorkSheet); Marshal.ReleaseComObject(xlWorkBook); Marshal.ReleaseComObject(xlApp); return cellDetails; }
we can get the neighbor data by changing the row or column number.
var retUserList = client.ExcelTextCellFinder(strFilePath, "Users", sUserType); string sUser = client.GetExcelCellText(strFilePath, "Users", retUserList[0], (retUserList[1]) + 1);
- Get all rows in first column in to an Array
In below method we can retrieve all the row data in the first column in the given excel file.
public IDictionary<string, string> GetExcelFirstRowColumn(string fileName) { Application xlApp; Workbook xlWorkBook; Worksheet xlWorkSheet; Range range; int rCnt; int rw = 0; int cl = 0; xlApp = new Application(); //open the excel xlWorkBook = xlApp.Workbooks.Open(@fileName); //get the first sheet of the excel xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.get_Item(1); range = xlWorkSheet.UsedRange; // get the total row count rw = range.Rows.Count; //get the total column count cl = range.Columns.Count; IDictionary<string, string> myRows = new Dictionary<string, string>(); // traverse all the row in the excel for (rCnt = 1; rCnt <= rw; rCnt++) { myRows.Add((string)(range.Cells[rCnt, 1] as Range).Value2.ToString(), (string)(range.Cells[rCnt, 2] as Range).Value2.ToString()); } //release the resources xlWorkBook.Close(true, null, null); xlApp.Quit(); Marshal.ReleaseComObject(xlWorkSheet); Marshal.ReleaseComObject(xlWorkBook); Marshal.ReleaseComObject(xlApp); return myRows; }
No comments:
Post a Comment