I'm writing a program which uses "Excel Sheets".

I am loading them into a DataGridView via a Button. If I'm finished working with the Sheet I can press the same button to load in another Sheet. The problem is even though I am clearing and refreshing my DataGridView the old Sheet isn't going away and the new Sheet just gets added beneath the old one. Why is that?

My Code for the Button Click Event:

private void button1_Click(object sender, EventArgs e)
{
    //Excell Prozess kill (wenn "Datei öffnen" geklickt)
    Process[] excelProcs = Process.GetProcessesByName("EXCEL");
    foreach (Process proc in excelProcs)
    {
        proc.Kill();
    }

    //Preparing Excel
    Excel.Workbook theWorkbook = null;
    Excel.Application ExcelObj = null;
    Excel.Sheets sheets = null;
    Excel.Worksheet worksheet = null;

    if (dataGridView1.DataSource != null)
    {
        dataGridView1.DataSource = null;
        dataGridView1.Refresh();
    }
    else
    {
        dataGridView1.Columns.Clear();
    }

    //Deklaration
    string filePath = string.Empty;
    string fileExt = string.Empty;

    //File dialog
    OpenFileDialog dialog = new OpenFileDialog();
    dialog.Filter = "Excel Datei (*.xlsx *.xls)|*.xlsx; *.xls; |All Files (*.*)|*.*";

    if (dialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
    {
        filePath = dialog.FileName; //get the path of the file  
        fileExt = Path.GetExtension(filePath); //get the extension from the file

        //File Path zeigen
        label1.Text = filePath;

        //Nur .xls und .xlsx 
        if (fileExt.CompareTo(".xls") == 0 || fileExt.CompareTo(".xlsx") == 0)
        {
            try
            {
                ExcelObj = new Excel.Application();

                //open ExcelObj and load it into theWorkbook
                theWorkbook = ExcelObj.Workbooks.Open(filePath);

                sheets = theWorkbook.Worksheets;
                //Get the reference of second worksheet
                worksheet = (Excel.Worksheet)sheets.get_Item(1);
                //Get the name of worksheet
                string strWorksheetName = worksheet.Name;

                //string for database con
                String constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="  
                                filePath
                                   ";Extended Properties='Excel 12.0 XML;HDR=NO;';";

                OleDbConnection con = new OleDbConnection(constr);


                //select sheet from excel file
                OleDbCommand oconn = new OleDbCommand("Select * From ["   strWorksheetName   "$]", con);

                //open db
                con.Open();

                OleDbDataAdapter sda = new OleDbDataAdapter(oconn);
                sda.Fill(data);

                //insert data into datagridview
                dataGridView1.DataSource = data;

                //Select all from table date
                DataRow[] rows = data.Select();

                //close db
                con.Close();

                //close ExcelObj
                ExcelObj.Quit();
            }

            //if error -> show
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message.ToString());
            }

            finally
            {
                //ComObject release
                if (sheets != null) Marshal.ReleaseComObject(sheets);
                if (ExcelObj != null) Marshal.ReleaseComObject(ExcelObj);
                if (worksheet != null) Marshal.ReleaseComObject(worksheet);
                if (theWorkbook != null) Marshal.ReleaseComObject(theWorkbook);

            }

I believe that the problem is caused by the "Excel File" not being released properly inside the "Preparing Excel" section, but "Excel" is being closed in many situations via the "Excel Prozess Kill" Code. I watched it a couple of times via Task Manager. So what's causing the problem? Any Opinions?

CodePudding user response：

There are a couple of head scratching things in your current code that you may want to reconsider.

First… killing ALL the “Excel” processes is well… odd. Your code possibly did NOT start these processes; however the code is going to kill them. To heck with the person who started running Excel and stepped away for coffee.

The code “appears” to be releasing the Excel process properly, however, it seems logical to release the Excel app “after” you have released the workbook. Specifically, release the sheets, then worksheet, then theWorkbook and finally the app ExcelObj. Because the code “appears” to properly release the Excel com objects, I would dump all the code that kills the Excel processes.

Next, I suggest looking for a third-party solution when working with Excel files. For non-commercial use I find EPPlus to be a good alternative as opposed to OleDb and/or Interop and can be found in NuGet. Just a thought.

And finally I DO have an easy solution to your issue. From what I can decipher as you do not show its definition, is the variable data. It appears to me that this data object is a DataTable. And in my small tests, as you describe, when you select a second Excel file, it does indeed “append” itself to the existing data DataTable. Since the code “appears” to never clear the data table before filling it, then when you call…

sda.Fill(data);

a second time, then the adapter obligingly “appends” the data.

To fix this, simply clear the data table before you fill it…

data = new DataTable();
sda.Fill(data);