Trouble with displaying Excel data in a C# program using the oledb library

Question

I have a sample code that I want to use to read the content of an Excel file in a DataGridView in a Winform application in Visual Studio 2022 with C# language, but the content of the Excel file is not displayed in the DataGridView. I use Windows 11.

To test the content of the Excel file, I can easily read it with a console application and display it in the program, but I cannot do the same in the DataGridView. I remember that a few months ago I had read data from an Excel file with the same code and used it in a program, but now I can’t and I don’t know where the problem is.

I updated Visual Studio 2022 to the latest version. Could the problem be related to the update?

    using System;
    using System.Data;
    using System.Data.OleDb;
    using System.Windows.Forms;
    
    namespace WindowsFormsApp1
    {
        public partial class Form1 : Form
        {
    // Declare the connection and the adapter objects
    private OleDbConnection conn;
    private OleDbDataAdapter adapter;

    // Declare the DataSet object to hold the data from the Excel file
    private DataSet ds;

    // Declare the path and the name of the Excel file
    private string path = @"C:\\Users\\Hamed\\Desktop\\ConsoleApp1\\sample.xlsx";
    private string fileName = "sample.xlsx";

    public Form1()
    {
        InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {
        // Create the connection string based on the file extension
        string connectionString = "";
        string fileExtension = System.IO.Path.GetExtension(fileName);
        if (fileExtension == ".xls")
            connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
        else if (fileExtension == ".xlsx")
            connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";

        // Create the connection and the adapter objects
        conn = new OleDbConnection(connectionString);
        adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$A1:C10]", conn);

        // Create the DataSet object and fill it with the data from the Excel file
        ds = new DataSet();
        adapter.Fill(ds);

        // Bind the data to the DataGridView
        dataGridView1.AutoGenerateColumns = true;
        dataGridView1.DataSource = ds.Tables[0];
    }

    private void button1_Click(object sender, EventArgs e)
    {
        // Create the command builder object to generate the update commands
        OleDbCommandBuilder cmdBuilder = new OleDbCommandBuilder(adapter);

        // Update the Excel file with the changes made in the DataGridView
        adapter.Update(ds.Tables[0]);

                // Show a message to confirm the update
                MessageBox.Show("The Excel file has been updated.");
            }
        }
    }

I made a DataGridView named dataGridView1 and then wrote this code. I have not made any other settings. I tested with .NET 8 and 7 as well as with .NET Framework 4.8, but it didn't work. My problem is that the content is not displayed without any errors in DataGridView

Answer 1

The problem with my work was that I had forgotten to put the "Form1_Load" in the "Events > Load" section, and that's why the "Form1_Load" was not executed at all.

---

Note: Make sure that the official Microsoft package for OleDB is installed for you. To install it, you can go to Nugget Package Manager and install it.