C# WinForm Excel check empty sheets function-works but too slow(15 sheets 30 seconds)

Question

I have a working Function that checks if sheets from an excel file are empty.

This function is called for every sheet. The function works perfectly. The only problem is that if my Excel file has 50 sheets, calling this function takes like 30 seconds which is too much.

What can i do to optimize this function, so the function to be called once for all sheets in excel? Shall i use linq or still OLEDB.

P.S I will not use Interlop as it triples times to check.

This is my function

public static bool isEmptySheet2(string nameSheet)
        {
            string filePath = Properties.Resources.ResourceManager.GetString("FilePath");
            string fileName = Properties.Resources.ResourceManager.GetString("FileName");
            string fileLocation = filePath + fileName;
            try
            {
                DataTable dt = new DataTable();
                string connstr = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
    "Data Source=" + fileLocation + ";" + "Extended Properties=" + "\"" + "Excel 12.0 Xml;IMEX=1;HDR=NO;" + "\"";
                OleDbConnection conn = new OleDbConnection(connstr);
                string strSQL = "SELECT * FROM [" + nameSheet +"] WHERE [F17]='OK';";
                // string strSQL = "SELECT * FROM [" + "ThisIsSheet5$D4:D4" +"] WHERE [F17]='OK';";



                OleDbCommand cmd = new OleDbCommand(strSQL, conn);

                OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                da.Fill(dt);


                foreach (DataRow row in dt.Rows)
                {
                    foreach (DataColumn col in dt.Columns)
                    {
                        //test for null here
                        if (row[col] != DBNull.Value)
                        {
                            return false;

                        }
                    }
                }

                return true;




            }
            catch (Exception ex)
            {

                Console.WriteLine(ex);
            } return false;
        }

When calling function I enable a button for that sheet.

foreach (var i in sheets)
            {
        if (ExcelUtil.isEmptySheet2(result) == true)
                {

                    //set button false
                    i.btnFood = false;
                }
                else
                {
                    i.btnFood = true;
                }
            }

At the end, i want to enable buttons for that sheet that it is empty or not. The code above is working fine, but

-> I have to call that method for each sheet ->Looping through all that sheet-> opening->checking->closing->opening takes too much time

Are all the workbooks you are reading XLSX or older formats like XLS? — Josh DeLong, Jul 12 '16 at 14:11
I don't have any examples for this exact use case but I always use ClosedXML, EPPlus, Aspose.Cells when I have to do any work with Excel. All of those libraries can be referenced from nuget.org. If you truly are just checking if cell F17 has the value "OK" then you could loop through the sheets a lot faster using the libraries I mentioned. — Josh DeLong, Jul 13 '16 at 17:49

ilkerkaran · Accepted Answer · 2016-07-12T14:08:30.793

You open new connection for every sheet. It is not necessary at all. You can pass connection object to inner method. Or you can modify your query to get all sheets in one select, then check every sheet in the application layer from single dt object.

Edit: You can pass connection object like this:

public static bool isEmptySheet2(string nameSheet,OleDbConnection conn)
        {

        try
        {
            DataTable dt = new DataTable();
            string strSQL = "SELECT * FROM [" + nameSheet +"] WHERE [F17]='OK';";
            // string strSQL = "SELECT * FROM [" + "ThisIsSheet5$D4:D4" +"] WHERE [F17]='OK';";



            OleDbCommand cmd = new OleDbCommand(strSQL, conn);

            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            da.Fill(dt);


            foreach (DataRow row in dt.Rows)
            {
                foreach (DataColumn col in dt.Columns)
                {
                    //test for null here
                    if (row[col] != DBNull.Value)
                    {
                        return false;

                    }
                }
            }

            return true;




        }
        catch (Exception ex)
        {

            Console.WriteLine(ex);
        } return false;

}

your outer method:

string filePath = Properties.Resources.ResourceManager.GetString("FilePath");
            string fileName = Properties.Resources.ResourceManager.GetString("FileName");
            string fileLocation = filePath + fileName;
    string connstr = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
        "Data Source=" + fileLocation + ";" + "Extended Properties=" + "\"" + "Excel 12.0 Xml;IMEX=1;HDR=NO;" + "\"";
                    OleDbConnection conn = new OleDbConnection(connstr);
    foreach (var i in sheets)
                {
            if (ExcelUtil.isEmptySheet2(result,conn) == true)
                    {

                        //set button false
                        i.btnFood = false;
                    }
                    else
                    {
                        i.btnFood = true;
                    }
                }

I know that i open a conenction every time How can i avoid that? Please provide me useful info as i am a newbie in programming. — Marinescu Raluca, Jul 12 '16 at 14:04
I added a code sample as you requested. By the way, don't forget to close your connection when you are done with it — ilkerkaran, Jul 12 '16 at 14:10
Yes, but this method agains executes the query for one sheet, not for all sheets. — Marinescu Raluca, Jul 12 '16 at 14:28
Since you use OleDb to read excel files i am afraid it is not an option to execute multiple select queries in sinle command object. You can check the link out for another usage just like i wrote above. http://stackoverflow.com/questions/18511576/reading-excel-file-using-oledb-data-provider — ilkerkaran, Jul 12 '16 at 14:37
I am willing to change my function using Linq or another library. Just tell me and i ll research. Oledb takes too much time for many sheets. — Marinescu Raluca, Jul 12 '16 at 14:41
There are many readers do the job. You need to put your priorities in order then pick one them accordingly. You can take a look at https://social.msdn.microsoft.com/Forums/vstudio/en-US/a178b2b9-d329-49d2-82f8-960ca118b9c5/what-is-the-best-way-to-read-excel-files-in-c?forum=csharpgeneral But i use EPPlus and i am extremely happy with it. — ilkerkaran, Jul 12 '16 at 14:48

C# WinForm Excel check empty sheets function-works but too slow(15 sheets 30 seconds)

1 Answers1