December 27, 2019

How to Read data from Excel file using C#

  December 27, 2019
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.

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.

        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;

        }
logoblog

Thanks for reading How to Read data from Excel file using C#

Previous
« Prev Post

No comments:

Post a Comment

How to send email from current Outlook instance using Java + VBScript

If we are using .Net, we can easily access outlook and send emails using its own libraries. But what if we are using Java? Here we can seek ...