locked
Exception thrown on excel cell read C# RRS feed

  • Question

  • I have the following method which is passed an Excel Worksheet object and an integer:

    public static void State(Excel.Worksheet xlWorkSheet, int Row)
    {
        double StartVal;
        StartVal = xlWorkSheet.Cells[Row, 21].Value2;
        //OtherCode
    }
    

    In prior method calls I have been able to successfully extract the cell values using the same format however in this instance I receive the following exception:

    Managed Debugging Assistant 'FatalExecutionEngineError' : 'The runtime has encountered a fatal error. The address of the error was at 0x73fabe0e, on thread 0x2e5c. The error code is 0xc0000005. This error may be a bug in the CLR or in the unsafe or non-verifiable portions of user code. Common sources of this bug include user marshaling errors for COM-interop or PInvoke, which may corrupt the stack.'

    If I enable Native code debugging the exception thrown pop-up in Visual Studio 2017 seems to suggest that this comes from clrjit.dll. I have searched unsuccessfully for answers so far, with most responses seem to suggest that this is a difficult problem to debug but I have at least managed to track it down to the line that throws the exception. Any help would be appreciated, thanks.

    • Moved by CoolDadTx Wednesday, October 10, 2018 2:02 PM Office related
    Wednesday, October 10, 2018 10:25 AM

All replies

  • Hello, 

    Sorry, in what you provided no enough information to provide recommendation.

    Simplest - you have an Updates through Windows Update (updated partially or queuing for installation) - need to complete an update.

    More problematic - you didn't do something in your Excel application. 


    Sincerely, Highly skilled coding monkey.

    Wednesday, October 10, 2018 11:58 AM
  • In the following example I read in a specific cell which is expected to be of type double but use TryParse to ensure it is.

    using System;
    using System.IO;
    using System.Runtime.InteropServices;
    using Excel = Microsoft.Office.Interop.Excel;
    
    namespace ExcelSample
    {
        internal class ExcelCode
        {
            /// <summary>
            /// 
            /// </summary>
            /// <param name="pFileName">Existing file to read</param>
            /// <param name="pSheetName">Sheet name in pFileName</param>
            /// <param name="pCellAddress">Cell address in pSheetName e.g. A1</param>
            /// <returns></returns>
            public double GetCellValue(string pFileName, string pSheetName, string pCellAddress)
            {
                double cellValue = 0;
    
                if (!File.Exists(pFileName)) return cellValue;
    
                var proceed = false;
    
                Excel.Application xlApp = null;
                Excel.Workbooks xlWorkBooks = null;
                Excel.Workbook xlWorkBook = null;
                Excel.Worksheet xlWorkSheet = null;
                Excel.Sheets xlWorkSheets = null;
                Excel.Range xlCells = null;
    
                xlApp = new Excel.Application {DisplayAlerts = false};
                xlWorkBooks = xlApp.Workbooks;
                xlWorkBook = xlWorkBooks.Open(pFileName);
    
                xlApp.Visible = false;
    
                xlWorkSheets = xlWorkBook.Sheets;
    
                for (var index = 1; index <= xlWorkSheets.Count; index++)
                {
                    xlWorkSheet = (Excel.Worksheet)xlWorkSheets[index];
    
                    if (xlWorkSheet.Name == pSheetName)
                    {
                        proceed = true;
                        break;
                    }
    
                    Marshal.FinalReleaseComObject(xlWorkSheet);
    
                    xlWorkSheet = null;
    
                }
    
                if (proceed)
                {
                    xlCells = xlWorkSheet.Range[pCellAddress];
                    if (double.TryParse(xlCells.Value.ToString(), out double value))
                    {
                        cellValue = value;
                    }
    
                }
    
                xlWorkBook.Close();
                xlApp.UserControl = true;
                xlApp.Quit();
    
                ReleaseComObject(xlCells);
                ReleaseComObject(xlWorkSheets);
                ReleaseComObject(xlWorkSheet);
                ReleaseComObject(xlWorkBook);
                ReleaseComObject(xlWorkBooks);
                ReleaseComObject(xlApp);
    
                return cellValue;
            }
    
            public static void ReleaseComObject(object obj)
            {
                try
                {
                    Marshal.ReleaseComObject(obj);
                    obj = null;
                }
                catch (Exception)
                {
                    obj = null;
                }
            }
        }
    }

    Call

    using System;
    using System.IO;
    using System.Windows.Forms;
    using ExcelSample;
    
    namespace WindowsFormsApplication1_CS
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                var ops = new ExcelCode();
                double result = ops.GetCellValue(
                    Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Demo.xlsx"), "Sheet1", "B2");
                Console.WriteLine(result);
            }
    
        }
    }

    Result is 12.34


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, October 10, 2018 12:25 PM
  • Hi JW1993,

    Did Karen's answer resolved your problem?

    You can mark it as answer if it helped and please help us close the thread.

    Best Regards,

    Lina


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Friday, October 12, 2018 8:00 AM
  • Unfortunately not, my application seems happy enough to read a varying amount of cells but after that amount it seems to crash with the following error message:

    Managed Debugging Assistant 'FatalExecutionEngineError' : 'The runtime has encountered a fatal error. The address of the error was at 0x737dbe0e, on thread 0x5a88. The error code is 0xc0000005. This error may be a bug in the CLR or in the unsafe or non-verifiable portions of user code. Common sources of this bug include user marshaling errors for COM-interop or PInvoke, which may corrupt the stack.'

    It breaks on the call stack at 

      mscorlib.dll!string.InternalSubString(int startIndex = 0, int length = 27) Unknown

    Tuesday, October 16, 2018 9:03 AM
  • Would you consider using a free library instead of using Excel automation if dealing with .xlsx files?

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, October 16, 2018 11:50 AM
  • Potentially yes, what do you suggest?

    We have managed to achieve what we want using VBA so it is quite frustrating not being able to achieve the same here. It appears to be some sort of memory access permission issue.

    Tuesday, October 16, 2018 1:06 PM
  • Hello,

    I recommend using SpreadSheetLight.

    • Right click on Soluton Explorer in Visual Studio
    • Select "Manage Nuget packages for this solution"
    • Select the Browse tab
    • Type in SpreadSheetLight in the search textbox and press enter.
    • Select the project to install the package.
    • In your class add using SpreadSheetLight.

    Sample code to read from Sheet1, D2 as a Double.

    var fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Demo.xlsx");
    var sheetName = "Sheet1";
    double value = 0;
    using (var doc = new SLDocument(fileName,sheetName))
    {
        value = doc.GetCellValueAsDouble("D2");
        // get same cell as above but specifying row/column
        value = doc.GetCellValueAsDouble(2, 4);
    }

    SpreadSheetLight site has full documents/help file.

    http://spreadsheetlight.com/


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, October 16, 2018 4:18 PM