Close/dispose Syncfusion XlsIO instance from outside of instantiating method

Question

What I'm attempting to do is to pass a file location and worksheet name to an instance of Syncfusion's XLSIO and read information from it to drive a Selenium script in C#. I made that work. I ran into a problem when I tried to break it up into external classes so I can call different worksheets for different parts of the Selenium script.

With the current code, I end up with a null object at var range = worksheet.UsedRange; because while the sheet object is successfully passed, the underlying objects (excelEngine and workbook) have been disposed of from within the ExcelDataReader method.

If I remove the close and dispose, it all runs and logs in as expected. However, I still need to clean up because leaving the excel instance and spreadsheet open causes other problems when I need to use it later.

If I try to close/dispose after I create the list, the objects are not part of that method and it can't find them to close/dispose of them.

So, how do I either pass everything that the list creation method needs even though I've already disposed of the engine or how do I dispose of the engine after leaving that method? Alternately, should I break up the methods differently?

Note: the reason I broke out the ExcelDataReader from the list creation is because I plan to use that code with different sheet arguments passed in to driver different parts of the overall automation project. If I can't make this work, I'll have to drop the functionality in the ExcelDataReader into the list creation block for each different list. I can do that and I know it works, but it just feels sloppy.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Syncfusion.XlsIO;
using System.IO;
using System.Data;
using System.Runtime.Remoting.Messaging;

namespace ConsoleApp7
{

    public class Login
    {
        public string Usr { get; set; }
        public string Pwd { get; set; }
    }

    public class DataFeed
    {
        public string FileName { get; set; }
        public string Worksheet { get; set; }
    }

    interface IListBuilder
    {
        void BuildList(DataFeed feed);
    }

    enum ListType
    {
        LoginList,
        UrlList, 
    }

public static class DoTheWork
{

    public class LoginList : IListBuilder
    {
        public void BuildList(DataFeed feed)
        {

            var sheet = DoTheWork.ExcelDataReader(feed);
            DoTheWork.loginList(sheet);

        }


    }






    public static IWorksheet ExcelDataReader(DataFeed feed)
    {


        // instantiate Syncfusion xslio
        ExcelEngine excelEngine = new ExcelEngine();
        IApplication application = excelEngine.Excel;
        //read file to stream
        IWorkbook workbook = application.Workbooks.Open(feed.FileName);
        // get worksheet
        var sheet = workbook.Worksheets[feed.Worksheet];

        workbook.Close();
        excelEngine.Dispose();
         return sheet;
    }




    public static List<Login> loginList(IWorksheet sheet)
    {
        var worksheet = sheet;

        var range = worksheet.UsedRange;

        List<Login> loginList = new List<Login>();

        for (var i = 2; i <= range.LastRow; i++)
        {
            if (!string.IsNullOrEmpty(range[i, 1].Text) && !string.IsNullOrEmpty(range[i, 2].Text))
            {
                loginList.Add(new Login
                {
                    Usr = range[i, 1].Text,
                    Pwd = range[i, 2].Text,

                });
            }
        }



        return loginList;
    }

}
}

This is how I'm calling it from inside of main:

        IListBuilder list = new DoTheWork.LoginList();
        DataFeed feed = new DataFeed
        {
            FileName = @"C:\source\repos\ConsoleApp7\TestData.xlsx",
            Worksheet = "usr"
        };

        IWorksheet sheet = DoTheWork.ExcelDataReader(feed);
        var logins = DoTheWork.loginList(sheet);
        var xUserName = logins[0].Usr;
        var xPassword = logins[0].Pwd;

Answer 1

we recommend to use IWorkbook.Close() method outside the ExcelDataReader method as in the following code snippet.

        IWorksheet sheet = DoTheWork.ExcelDataReader(feed); 
        var logins = DoTheWork.loginList(sheet);             
        sheet.Workbook.Close();            // Closing the workbook 

We have prepared a simple sample with your code snippet to achieve your requirement which can be downloaded from the following link :

Sample Link : Sample