Trouble with references while reading from Excel

Best practices, code snippets for common functionality, examples, and guidelines.
jt1
Posts: 16
Joined: Fri Sep 25, 2015 4:18 pm

Trouble with references while reading from Excel

Post by jt1 » Tue Mar 08, 2016 5:01 pm

Not sure if this is the correct place to put this.

In Ranorex
Ranorex Version: 5.4.4.26486
.NET Version: 4.0.30319.42000

Visual Studio
.NET Version: 4.6.00081

I am working on reading from an Excel sheet that is already open.
try
{
xl.Application excelApp = (xl.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
excelApp.Visible = true;
xl.Workbook workbook = (xl.Workbook)excelApp.ActiveWorkbook;
xl.Worksheet reportSheet = ((xl.Worksheet)workbook.Sheets[1]);
xl.Range range = reportSheet.UsedRange;
int rows = range.Rows.Count;
int cols = range.Columns.Count;
				
for(int i = 2; i < rows; i++)
{
	for(int j = 1; j < cols; j++)
	{
		if(reportSheet.Cells[i,j].Value2 != null)
		{
			Console.WriteLine(range.Cells[i,j].Value2.ToString());
		}
		else
		{
			Console.WriteLine("Empty Cell");
		}
					
	}
}
    Console.ReadKey();
}
catch(Exception ex)
{
    Report.Log(ReportLevel.Warn, "Module", "Failed to assign opened report to excelApp. " + ex.Message, new RecordItemIndex(1));
}
The process works in Visual Studio. However when I try the exact same code in Ranorex it fails to build, giving me

'object' does not contain a definition for 'Value2' and no extension method 'Value2' accepting a first argument of type 'object' could be found (are you missing a using directive or an assembly reference?) (CS1061)

I believe this is a problem with a difference in versions, but I'm not sure how to approach the issue. Also I am not sure I'm reading from Excel in the best way. Any advice is appreciated.

krstcs
Ranorex Guru
Posts: 2683
Joined: Tue Feb 07, 2012 4:14 pm
Location: Austin, Texas, USA

Re: Trouble with references while reading from Excel

Post by krstcs » Tue Mar 08, 2016 6:55 pm

First, you should try compiling in both IDEs using the SAME version of .NET. Since Ranorex doesn't fully support some features in 4.6, I would set VS to 4.0 and try to compile again.

If you get the same error in VS as in Ranorex when you compile on the same version, then it's not a Ranorex issue, but a .NET version issue. Then, you could try to compile both in .NET 4.6 and see what happens.

If what you see NOW continues to happen, then it MIGHT be a Ranorex issue, so try updating to Ranorex 5.4.5 and see if it continues. You should keep Ranorex updated as much as possible becuase the Ranorex team (and the rest of us... :D ) cannot continue to support older versions.
Shortcuts usually aren't...

jt1
Posts: 16
Joined: Fri Sep 25, 2015 4:18 pm

Re: Trouble with references while reading from Excel

Post by jt1 » Wed Mar 16, 2016 2:10 pm

I found a fix. Before I tried like this, which did not work.
if(reportSheet.Cells[i,j].Value2 != null)  
        {  
            Console.WriteLine(range.Cells[i,j].Value2.ToString());  
        }
Separating the range assignment and the value2 assignment did the trick.
Edit: after doing a bit more work, I found a few more problems and fixed them. This is the working code.
for(int i = 1; i < rows; i++)
{
     for(int j = 1; j < cols; j++)
     {
         var value = (xl.Range)reportSheet.Cells[i,j];
         if (value.Value2 != null && !string.IsNullOrEmpty(value.Value2.ToString()))
         {
	         Report.Log(ReportLevel.Info, "Successfully accessed value: " + value.Value2);
         }
         else
         {
                 j++;
	         Report.Log(ReportLevel.Info, "No value in cell (" + i + ", " + j + ")");
	       }
     }
}