Tag: dataset

I am assuming that you already have an Excel file with one “Sheet1” data table in it. Microsoft ACE driver is already installed;

Standard syntax to query Excel table is “SELECT * FROM [Sheet1$]”. Here is the code that does the magic.

// Connect EXCEL sheet with OLEDB using connection string
 using (OleDbConnection conn = new OleDbConnection(connectionString))
    {
        conn.Open();
        OleDbDataAdapter objDA = new System.Data.OleDb.OleDbDataAdapter
        ("select * from [Sheet1$]", conn);
        DataSet excelDataSet = new DataSet();
        objDA.Fill(excelDataSet);
        dataGridView1.DataSource = excelDataSet.Tables[0];
    }
			
	//In above code '[Sheet1$]' is the first sheet name with '$' as default selector,
        // with the help of data adaptor we can load records in dataset		
	
	//write data in EXCEL sheet (Insert data)
 using (OleDbConnection conn = new OleDbConnection(connectionString))
    {
        try
        {
            conn.Open();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = @"Insert into [Sheet1$] (month,mango,apple,orange) 
            VALUES ('DEC','40','60','80');";
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            //exception here
        }
        finally
        {
             conn.Close();
             conn.Dispose();
        }
    }
			
//update data in EXCEL sheet (update data)
using (OleDbConnection conn = new OleDbConnection(connectionString))
	{
        try
        {
            conn.Open();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = "UPDATE [Sheet1$] SET month = 'DEC' WHERE apple = 74;";
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            //exception here
        }
        finally
        {
            conn.Close();
            conn.Dispose();
        }
    }

$ in select statement means that table already exists in Excel File. If we are going to create a new worksheet then we will not use $ sign. OLEDB does not support DELETE query.

Resource;

https://www.codingame.com/playgrounds/9014/read-write-excel-file-with-oledb-in-c-without-interop