1

Okay so here is my code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.OleDb;

namespace DisplayingDataFromExcelSheets
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            string datasource = @"C:\Users\E180728\Documents\FakeGradesTable.xlsx";
            string cs = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + datasource + ";Extended Properties='Excel 12.0;HDR=YES'";
            OleDbConnection cn = new OleDbConnection(cs);
            Console.WriteLine("Connecting to: " + cs);
            OleDbDataAdapter da = new OleDbDataAdapter("select * from [marks$]", cn);
            DataSet ds = new DataSet();
            da.Fill(ds, "marks");
            dataGridView1.DataSource = ds.Tables[0];

        }
    }
}

When I attempt to run this, the form comes up with a blank DataGridView, and the following shows up in the output:

'DisplayingDataFromExcelSheets.vshost.exe' (CLR v4.0.30319: DisplayingDataFromExcelSheets.vshost.exe): Loaded 'C:\windows\Microsoft.Net\assembly\GAC_32\mscorlib\v4.0_4.0.0.0__b77a5c561934e089\mscorlib.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'DisplayingDataFromExcelSheets.vshost.exe' (CLR v4.0.30319: DisplayingDataFromExcelSheets.vshost.exe): Loaded 'C:\windows\assembly\GAC_MSIL\Microsoft.VisualStudio.HostingProcess.Utilities\14.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualStudio.HostingProcess.Utilities.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'DisplayingDataFromExcelSheets.vshost.exe' (CLR v4.0.30319: DisplayingDataFromExcelSheets.vshost.exe): Loaded 'C:\windows\Microsoft.Net\assembly\GAC_MSIL\System.Windows.Forms\v4.0_4.0.0.0__b77a5c561934e089\System.Windows.Forms.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled. 'DisplayingDataFromExcelSheets.vshost.exe' (CLR v4.0.30319: DisplayingDataFromExcelSheets.vshost.exe): Loaded 'C:\windows\Microsoft.Net\assembly\GAC_MSIL\System\v4.0_4.0.0.0__b77a5c561934e089\System.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'DisplayingDataFromExcelSheets.vshost.exe' (CLR v4.0.30319: DisplayingDataFromExcelSheets.vshost.exe): Loaded 'C:\windows\Microsoft.Net\assembly\GAC_MSIL\System.Drawing\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.Drawing.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'DisplayingDataFromExcelSheets.vshost.exe' (CLR v4.0.30319: DisplayingDataFromExcelSheets.vshost.exe): Loaded 'C:\windows\assembly\GAC_MSIL\Microsoft.VisualStudio.HostingProcess.Utilities.Sync\14.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualStudio.HostingProcess.Utilities.Sync.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'DisplayingDataFromExcelSheets.vshost.exe' (CLR v4.0.30319: DisplayingDataFromExcelSheets.vshost.exe): Loaded 'C:\windows\assembly\GAC_MSIL\Microsoft.VisualStudio.Debugger.Runtime\14.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualStudio.Debugger.Runtime.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'DisplayingDataFromExcelSheets.vshost.exe' (CLR v4.0.30319: DisplayingDataFromExcelSheets.vshost.exe): Loaded 'C:\Users\E180728\Documents\Visual Studio 2015\Projects\ADOProject\DisplayingDataFromExcelSheets\bin\Debug\DisplayingDataFromExcelSheets.vshost.exe'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'DisplayingDataFromExcelSheets.vshost.exe' (CLR v4.0.30319: DisplayingDataFromExcelSheets.vshost.exe): Loaded 'C:\windows\Microsoft.Net\assembly\GAC_MSIL\System.Core\v4.0_4.0.0.0__b77a5c561934e089\System.Core.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'DisplayingDataFromExcelSheets.vshost.exe' (CLR v4.0.30319: DisplayingDataFromExcelSheets.vshost.exe): Loaded 'C:\windows\Microsoft.Net\assembly\GAC_MSIL\System.Xml.Linq\v4.0_4.0.0.0__b77a5c561934e089\System.Xml.Linq.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'DisplayingDataFromExcelSheets.vshost.exe' (CLR v4.0.30319: DisplayingDataFromExcelSheets.vshost.exe): Loaded 'C:\windows\Microsoft.Net\assembly\GAC_MSIL\System.Data.DataSetExtensions\v4.0_4.0.0.0__b77a5c561934e089\System.Data.DataSetExtensions.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'DisplayingDataFromExcelSheets.vshost.exe' (CLR v4.0.30319: DisplayingDataFromExcelSheets.vshost.exe): Loaded 'C:\windows\Microsoft.Net\assembly\GAC_MSIL\Microsoft.CSharp\v4.0_4.0.0.0__b03f5f7f11d50a3a\Microsoft.CSharp.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'DisplayingDataFromExcelSheets.vshost.exe' (CLR v4.0.30319: DisplayingDataFromExcelSheets.vshost.exe): Loaded 'C:\windows\Microsoft.Net\assembly\GAC_32\System.Data\v4.0_4.0.0.0__b77a5c561934e089\System.Data.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'DisplayingDataFromExcelSheets.vshost.exe' (CLR v4.0.30319: DisplayingDataFromExcelSheets.vshost.exe): Loaded 'C:\windows\Microsoft.Net\assembly\GAC_MSIL\System.Deployment\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.Deployment.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'DisplayingDataFromExcelSheets.vshost.exe' (CLR v4.0.30319: DisplayingDataFromExcelSheets.vshost.exe): Loaded 'C:\windows\Microsoft.Net\assembly\GAC_MSIL\System.Net.Http\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.Net.Http.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'DisplayingDataFromExcelSheets.vshost.exe' (CLR v4.0.30319: DisplayingDataFromExcelSheets.vshost.exe): Loaded 'C:\windows\Microsoft.Net\assembly\GAC_MSIL\System.Xml\v4.0_4.0.0.0__b77a5c561934e089\System.Xml.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

The thread 0x3218 has exited with code 0 (0x0). The thread 0x2b8c has exited with code 0 (0x0).

'DisplayingDataFromExcelSheets.vshost.exe' (CLR v4.0.30319: DisplayingDataFromExcelSheets.vshost.exe): Loaded 'C:\Users\E180728\Documents\Visual Studio 2015\Projects\ADOProject\DisplayingDataFromExcelSheets\bin\Debug\DisplayingDataFromExcelSheets.exe'. Symbols loaded.

Running Application

'DisplayingDataFromExcelSheets.vshost.exe' (CLR v4.0.30319: DisplayingDataFromExcelSheets.vshost.exe): Loaded 'C:\windows\Microsoft.Net\assembly\GAC_MSIL\System.Configuration\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.Configuration.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'DisplayingDataFromExcelSheets.vshost.exe' (CLR v4.0.30319: DisplayingDataFromExcelSheets.vshost.exe): Loaded 'C:\windows\Microsoft.Net\assembly\GAC_32\System.Transactions\v4.0_4.0.0.0__b77a5c561934e089\System.Transactions.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

Connecting to: Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Users\E180728\Documents\FakeGradesTable.xlsx;Extended Properties='Excel 12.0;HDR=YES' 'DisplayingDataFromExcelSheets.vshost.exe' (CLR v4.0.30319: DisplayingDataFromExcelSheets.vshost.exe): Loaded 'C:\windows\Microsoft.Net\assembly\GAC_32\System.EnterpriseServices\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.EnterpriseServices.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'DisplayingDataFromExcelSheets.vshost.exe' (CLR v4.0.30319: DisplayingDataFromExcelSheets.vshost.exe): Loaded 'C:\windows\Microsoft.Net\assembly\GAC_32\System.EnterpriseServices\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.EnterpriseServices.Wrapper.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

Exception thrown: 'System.Data.OleDb.OleDbException' in System.Data.dll

I am running Visual Studio 2015. The file is in built in Excel 2010. Both office and VS 2015 are x64 versions on a x64 laptop. Any ideas what might be happening here?

Michael P.
  • 23
  • 3
  • move the code to a button click and you might get a different/better exception – Ňɏssa Pøngjǣrdenlarp Jan 07 '16 at 21:55
  • How do you expect this to work if you never actually open the connection? – Camilo Terevinto Jan 07 '16 at 21:59
  • @cFrozenDeath actually the `.Fill` method calls the open behind the scenes – MethodMan Jan 07 '16 at 22:01
  • @MethodMan I'm looking at the ReferenceSource and I cannot find that anywhere, mind pointing me to the call? http://referencesource.microsoft.com/#System.Data/System/Data/OleDb/OleDbDataAdapter.cs,64ab0786e5403ab3 – Camilo Terevinto Jan 07 '16 at 22:07
  • I am curious to see what happens when the OP adds `cn.Open()` to their code also I wonder if the `sheet name` is correct as well there is a way to do the same thing by just opening up the excel file and not selecting the sheet name I just tested one I would like a copy of that excel file to test – MethodMan Jan 07 '16 at 22:09
  • I added cn.Open() to the code after the OleDbConnection line and got the same results. – Michael P. Jan 08 '16 at 13:27
  • **I just tried it with a button and this time I got a popup error. The line highlighted is the da.Fill and the error is reading:** An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll Additional information: 'marks$' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long. – Michael P. Jan 08 '16 at 13:28

1 Answers1

0

I figured it out. See my last comment for the solution. I found the error by adding the code to a button instead of a form load. It showed that my sheet name was wrong in the excel file. Updated it and it worked just fine. Thanks for all the help guys!

Michael P.
  • 23
  • 3