Excel to DataGridView

Question

Additional information: The Microsoft Office Access database engine could not find the object 'C:\Users\username\Documents\sampleData.xls'. Make sure the object exists and that you spell its name and the path name correctly.

The Error is highlighted at

theDataAdapter.Fill(spreadSheetData);

Here's the sample data I used (tried in .csv , .xls , .xlsx )

Name    Age     Status      Children
Johnny  34      Married     3
Joey    21      Single      1
Michael 16      Dating      0
Smith   42      Divorced    4

Here's the code associated:

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.IO;
using System.Data.OleDb;

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

        private void btnImport_Click(object sender, EventArgs e)
        {
            var frmDialog = new System.Windows.Forms.OpenFileDialog();
            if (frmDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {

                string strFileName = frmDialog.FileName;
                System.IO.FileInfo spreadSheetFile = new System.IO.FileInfo(strFileName);


                scheduleGridView.DataSource = spreadSheetFile.ToString();
                System.Diagnostics.Debug.WriteLine(frmDialog.FileName);
                System.Diagnostics.Debug.WriteLine(frmDialog.SafeFileName);

                String name = frmDialog.SafeFileName;                   

                String constr = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES""", frmDialog.FileName);

                OleDbConnection myConnection = new OleDbConnection(constr);

                OleDbCommand onlineConnection = new OleDbCommand("SELECT * FROM [" + frmDialog.FileName + "]", myConnection);

                myConnection.Open();

                OleDbDataAdapter theDataAdapter = new OleDbDataAdapter(onlineConnection);
                DataTable spreadSheetData = myConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                theDataAdapter.Fill(spreadSheetData);
                scheduleGridView.DataSource = spreadSheetData;
            }
        }
    }
}

scheduleGridView is the DataGridViews name, & btnImport is the name for the import Button.

I've installed 2007 Office System Driver: Data Connectivity Components; which gave me the AccessDatabaseEngine.exe, but from there I've been stuck here without understanding how to get around this. It should go without saying that the filepath is correct in its entirety. There is no odd characters in the path name either (spaces, underlines, etc)

Mini Update :: (another dead end it seems like)

Although the initial error says, "could not find the object 'C:\Users\username\Documents\sampleData.xls'"

In the Debugger the exception is read as When I look at details the exception as "C:\Users\username\Documents\sampleData.xls"

So I thought the error was that it wasn't taking the path as a literal, but this article C# verbatim string literal not working. Very Strange backslash always double Shows very clearly that that is not my issue.

Answer 1

I am guessing you may be mistaken by what is returned from the following line of code…

DataTable spreadSheetData = myConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

The DataTable returned from this line will have nine (9) columns (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, TABLE_GUID, DESCRIPTION, TABLE_PROPID, DATE_CREATED and DATE_MODIFIED). This ONE (1) DataTable returned simply “Describes” the worksheet(s) and named range(s) in the entire selected Excel workbook. Each row in this DataTable represent either a worksheet OR a named range. To distinguish worksheets from named ranges, the “TABLE_NAME” column in this DataTable has the name of the worksheet or range AND ends each “Worksheet” Name with a dollar sign ($). If the “TABLE_NAME” value in a row does NOT end in dollar sign, then it is a range and not a worksheet.

Therefore, when the line

OleDbDataAdapter theDataAdapter = new OleDbDataAdapter(onlineConnection);

Blows up and says it cannot file the “filename” error… is somewhat expected because this line is looking for a “worksheet” name, not a filename. On the line creating the select command…

OleDbCommand onlineConnection = new OleDbCommand("SELECT * FROM [" + frmDialog.FileName + "]", myConnection);

This is incorrect; you have already selected the filename and open the file with

String constr = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES""", frmDialog.FileName);
OleDbConnection myConnection = new OleDbConnection(constr);
myConnection.Open();

The correct OleDbCommand line should be…

OleDbCommand onlineConnection = new OleDbCommand("SELECT * FROM [" + sheetName + "]", myConnection);

The problem here is that the current code is not getting the worksheet names. Therefore, we cannot “select” the worksheet from the workbook then fill the adapter with the worksheet.

The other issue is setting the DataGridView’s DataSource to spreadSheetData… when you get the worksheet(s) from an Excel “Workbook”, you must assume there will be more than one sheet. Therefore a DataSet will work as a container to hold all the worksheets in the workbook. Each DataTable in the DataSet would be a single worksheet and it can be surmised that the DataGridView can only display ONE (1) of these tables at a time. Given this, below are the changes described along with an added button to display the “Next” worksheet in the DataGridView since there may be more than one worksheet in the workbook. Hope this makes sense.

int sheetIndex = 0;
DataSet ds = new DataSet();

public Form1() {
  InitializeComponent();
}

private void btnImport_Click(object sender, EventArgs e) {
  var frmDialog = new System.Windows.Forms.OpenFileDialog();
  if (frmDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK) {
    String constr = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES""", frmDialog.FileName);
    OleDbConnection myConnection = new OleDbConnection(constr);
    myConnection.Open();
    DataTable spreadSheetData = myConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    string sheetName = "";
    DataTable dt;
    OleDbCommand onlineConnection;
    OleDbDataAdapter theDataAdapter;
    // fill the "DataSet" each table in the set is a worksheet in the Excel file
    foreach (DataRow dr in spreadSheetData.Rows) {
      sheetName = dr["TABLE_NAME"].ToString();
      sheetName = sheetName.Replace("'", "");
      if (sheetName.EndsWith("$")) {
        onlineConnection = new OleDbCommand("SELECT * FROM [" + sheetName + "]", myConnection);
        theDataAdapter = new OleDbDataAdapter(onlineConnection);
        dt = new DataTable();
        dt.TableName = sheetName;
        theDataAdapter.Fill(dt);
        ds.Tables.Add(dt);
      }
    }
    myConnection.Close();
    scheduleGridView.DataSource = ds.Tables[0];
    setLabel();
  }
}

private void setLabel() {
  label1.Text = "Showing worksheet " + sheetIndex + " Named: " + ds.Tables[sheetIndex].TableName + " out of a total of " + ds.Tables.Count + " worksheets";
}

private void btnNextSheet_Click(object sender, EventArgs e) {
  if (sheetIndex == ds.Tables.Count - 1)
    sheetIndex = 0;
  else
    sheetIndex++;
  scheduleGridView.DataSource = ds.Tables[sheetIndex];
  setLabel();
}

Answer 2

I solved it. Well there was a workaround. I used the Excel Data Reader found in this thread: How to Convert DataSet to DataTable

Which led me to https://github.com/ExcelDataReader/ExcelDataReader ^ The readme was fantastic, just went to solution explorer, right click on references, manage NuGet Packages, select browse in the new box, enter ExcelDataReader, then in the .cs file be sure to include, "using Excel;" at the top, the code mentioned in the first link was essentially enough, but here's my exact code for those wondering.

var frmDialog = new System.Windows.Forms.OpenFileDialog();
        if (frmDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
        {


            /*string strFileName = frmDialog.FileName;
            //System.IO.FileInfo spreadSheetFile = new System.IO.FileInfo(strFileName);
            System.IO.StreamReader reader = new System.IO.StreamReader(strFileName);
            */


            string strFileName = frmDialog.FileName;

            FileStream stream = File.Open(strFileName, FileMode.Open, FileAccess.Read);

            //1. Reading from a binary Excel file ('97-2003 format; *.xls)
            IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
            //...
            //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
            //IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
            //...
            //3. DataSet - The result of each spreadsheet will be created in the result.Tables
            //DataSet result = excelReader.AsDataSet();
            //...
            //4. DataSet - Create column names from first row
            excelReader.IsFirstRowAsColumnNames = true;
            DataSet result = excelReader.AsDataSet();

            DataTable data = result.Tables[0];

            //5. Data Reader methods
            while (excelReader.Read())
            {
                //excelReader.GetInt32(0);
            }


            scheduleGridView.DataSource = data;
            excelReader.Close();