none
Range.GetEnumerator() and null RRS feed

  • Question

  • Hello,

    can the return value of range.GetEnumerator() be null (in C# ) [range is an Microsoft.Office.Interop.Excel.Range] ?

    tia

      Hendrik Schmieder

    Tuesday, April 26, 2016 2:03 PM

Answers

  • Hi, h_schmieder

    According to your description, I have made a sample to try to reproduce this issue, unfortunately, I can't. You could refer to below code and result:

    Excel.Application excelApp = new Excel.Application();
    Excel.Workbook workBook= excelApp.Workbooks.Open("D:\\Data.xlsx");
    Excel.Worksheet workSheet = workBook.ActiveSheet;
    
    Excel.Range range = workSheet.get_Range("A1","A4");
    
    System.Collections.IEnumerator wsEnumerator = range.GetEnumerator();
    
    while (wsEnumerator.MoveNext())
    {
        var item = wsEnumerator.Current;
    
        if(((Excel.Range)item).Value2!=null)
        {
                        
            Console.WriteLine(((Excel.Range)item).Value);
        }
        else
        {
            Console.WriteLine("Current is Null");
        }
    }



    In addition could you provide full sample code and exception, that will help us reproduce and resolve it.

    Thanks for your understanding.


    Wednesday, May 4, 2016 2:18 AM

All replies

  • Why don't you run a test with different scenarios?

    Best regards, George

    Tuesday, April 26, 2016 2:48 PM
  • >>>can the return value of range.GetEnumerator() be null (in C# ) [range is an Microsoft.Office.Interop.Excel.Range] ?

    According to your description, please correct me if I have any misunderstandings on your question, it seems that you want to iterate through an Excel range, refer to below code:
    foreach(Excel.Range r in rng.Cells)
    {
        Debug.Print(r.Value2);
    }

    In addition could you provide more information about your issue, that will help us reproduce and resolve it.

    Thanks for your understanding.
    Wednesday, April 27, 2016 3:02 AM
  • Sorry for the delay.

    I got a log file from a customer and from the linenumber in the strack trace he got an

    System.NullReferenceException at the line with the code

    while (it.MoveNext() && !found)

    with

    bool found = false;
    IEnumerator it = range.GetEnumerator();
    

    So since found can't be null, it must be null and it can only be null if GetEnumerator() returned null.

    The only other solution I see is that the line number in the stack trace doesn't exactly Points to the problematic line [code is written in C# using .Net 4.5.1]

      Hendrik

    Tuesday, May 3, 2016 12:34 PM
  • Hi, h_schmieder

    According to your description, I have made a sample to try to reproduce this issue, unfortunately, I can't. You could refer to below code and result:

    Excel.Application excelApp = new Excel.Application();
    Excel.Workbook workBook= excelApp.Workbooks.Open("D:\\Data.xlsx");
    Excel.Worksheet workSheet = workBook.ActiveSheet;
    
    Excel.Range range = workSheet.get_Range("A1","A4");
    
    System.Collections.IEnumerator wsEnumerator = range.GetEnumerator();
    
    while (wsEnumerator.MoveNext())
    {
        var item = wsEnumerator.Current;
    
        if(((Excel.Range)item).Value2!=null)
        {
                        
            Console.WriteLine(((Excel.Range)item).Value);
        }
        else
        {
            Console.WriteLine("Current is Null");
        }
    }



    In addition could you provide full sample code and exception, that will help us reproduce and resolve it.

    Thanks for your understanding.


    Wednesday, May 4, 2016 2:18 AM
  • I can't reproduce this specifc issue.

    internal static Excel.Range GetCellsWithFormula(Excel.Range range)
    {
    	Excel.Range cells = range;
    	try
            {
    		if (cells != null)
                    {
    			if (!IsOneCell(cells))
    			{
    				try
    				{
    					cells = cells.SpecialCells(Excel.XlCellType.xlCellTypeFormulas);
    				}
    				catch
    				{
    					cells = null;
    				}
    			}
    			else
    			{
    				if (!cells.HasFormula)
    				{
    					cells = null;
    				}
    			}
    		}
                    return cells;
    	}
    }
    
    internal static bool IsCellFreezed(ref string[] args)
    {
    	bool isFreezed = false;
    
            Excel.Range sel = null;
    
            try
            {
    		sel = GetCellsWithFormula();
    
                    if (sel != null)
                    {
    			string tmpHost = null, tmpDB = null, tmpCube = null;
    			string[] tmpCoords = null;
    
    			IEnumerator it = sel.GetEnumerator();
    			Excel.Range tmpRange;
    			string formula;
    			while (it.MoveNext() && !isFreezed)
    			{
    				tmpRange = (Excel.Range)it.Current;
    				formula = tmpRange.Formula;
    				if (ConnectHelper.CheckFormulaData(formula))
    				{
    					tmpCoords = Getcoord4CurrFormulaArray(ref tmpHost, ref tmpDB, ref tmpCube, formula);
    					isFreezed = IsCellFreezed(tmpHost, tmpDB, tmpCube, tmpCoords);
    				}
    			}
    
    			if (GetRangeCount(sel) == 1)
    			{
    				int len = tmpCoords.Length;
    				args = new string[len + 3];
    				args[0] = tmpHost;
    				args[1] = tmpDB;
    				args[2] = tmpCube;
    				Array.Copy(tmpCoords, 0, args, 3, len);
    			}
    		}
    	}
    
    	return isFreezed;
    }
    

    The stack trace in the log says that the System.NullReferenceException occured in

    IsCellFreezed(String[]& args)

    at line

     while (it.MoveNext() && !isFreezed)

      Hendrik Schmieder

    Wednesday, May 4, 2016 10:02 AM
  • Hi, h_schmieder

    Based on your sample code, I suggest that you could set breakpoint to debug your application to make sure whether GetCellsWithFormula() method returns Range object is not null or add line code like below:
    if(sel!=null)
    {
       IEnumerator it = sel.GetEnumerator();
       Excel.Range tmpRange;
       string formula;
       .......
    }

    In addition could you provide more information, for example sample Excel file, full sample code etc., that will help us reproduce and resolve it.

    Thanks for your understanding.
    Thursday, May 5, 2016 4:55 AM