ExcelWorksheet object falling apart after being returned from method

Dec 9, 2015 at 2:14 PM
Edited Dec 9, 2015 at 2:28 PM
This is the strangest thing I have seen, ever.

Using 4.0.4

From method A, I'm calling a simple method that opens an excel file and returns the first worksheet. In that simple method the ExcelWorksheet object does exist and is fully populated. Once the ExcelWorksheet object is returned to the calling method the ExcelWorksheet object is only partial there. For example the Dimension and Cells sub object are null. There are more null objects but those are the ones I need. You can see from the code below it is pretty basic.

This was all working great with EPPlus 3.1.3.3
        public static void Excel(string path, ref FileViewModel file)
        {

                    ........
            
                    // Calling simple method to get first worksheet
                    var worksheet = ProcessExcel(path);
                    //Once back here the worksheet variable is not fully populated

                    dt = WorksheetToDataTable(worksheet);
                   
                    ........

            }
            finally
            {
                if (_sqlConn.State == ConnectionState.Open)
                    _sqlConn.Close();
            }
        }

        private static ExcelWorksheet ProcessExcel(string fileName)
        {
            try
            {
                var fi = new FileInfo(fileName);
                using (ExcelPackage p = new ExcelPackage(fi))
                {
                    var dtSheets = p.Workbook.Worksheets;

                    // Get list of sheets
                    if (dtSheets != null && dtSheets.Count > 1)
                        throw new ApplicationException("Too many sheets in file!");

                    // get the first worksheet in the workbook
                    ExcelWorksheet ws = dtSheets == null ? null : dtSheets.First();
                    //  Everything is good up to here.
                    return ws;
                }
            }
            catch (Exception ex)
            {
                if (ex.HResult == -2146233088)
                    throw new ApplicationException(Messages.OldExcelFile);
                throw;
            }
        }
This seems to have started to happen with EPPlus 4.0.1.1
Dec 9, 2015 at 6:58 PM
Though I believe it is still an issue with EPPlus I believe I have found a workaround. It seems to be caused by the "using" statement in my "ProcessExcel" method. If I leave the ExcelPackage variable floating in memory everything is fine.
        private static ExcelPackage _p = null;
        public static void Excel(string path, ref FileViewModel file)
        {

                    ........
            
                    // Calling simple method to get first worksheet
                    var worksheet = ProcessExcel(path);
                    //Once back here the worksheet variable is not fully populated

                    dt = WorksheetToDataTable(worksheet);
                   
                    // dispose of object, clear memory
                    _p = null;
                    ........

            }
            finally
            {
                if (_sqlConn.State == ConnectionState.Open)
                    _sqlConn.Close();
            }
        }

        private static ExcelWorksheet ProcessExcel(string fileName)
        {
            try
            {
                var fi = new FileInfo(fileName);

                // This leaves the worksheet open, which isn't great.
                _p = new ExcelPackage(fi);
                    var dtSheets = p.Workbook.Worksheets;

                    // Get list of sheets
                    if (dtSheets != null && dtSheets.Count > 1)
                        throw new ApplicationException("Too many sheets in file!");

                    // get the first worksheet in the workbook
                    ExcelWorksheet ws = dtSheets == null ? null : dtSheets.First();
                    //  Everything is good up to here.
                    return ws;
            }
            catch (Exception ex)
            {
                if (ex.HResult == -2146233088)
                    throw new ApplicationException(Messages.OldExcelFile);
                throw;
            }
        }