locked
How to insert data into excel 2007 using OleDbConnection in C#

    Question

  • i want to insert, delete and display data into and from excel 2007
    Tuesday, June 07, 2011 11:31 AM

Answers

  • Hi Magma

    First of all we are declaring the ODBC namespace which we will use an Excel DNS; 

    using System.Data.Odbc;

    then we will write these codes for connecting to a specific Excel file;

    String strConn = @"Dsn=Excel Files;dbq=excel_file_path;defaultdir=excel_file_dir;driverid=1046;maxbuffersize=2048;pagetimeout=5";
                OdbcConnection objConn = new OdbcConnection(strConn);
                objConn.Open();
                OdbcDataAdapter adp = new OdbcDataAdapter("select * from [Sheet1$]", objConn);
                DataSet ds = new DataSet();
                adp.Fill(ds);
                dataGridView1.DataSource = ds.Tables[0];
                objConn.Close();

    Thats all by using these codes you have got the datas you wanted to...

    Cheers;)

    Regards,

    Umisha

    Source Code-World

    • Proposed as answer by Aspen VJ Thursday, June 09, 2011 3:32 AM
    • Marked as answer by Aspen VJ Thursday, June 16, 2011 1:48 AM
    Tuesday, June 07, 2011 11:40 AM
  • Hi Magma09,

    Here is a way to insert data into excel using OleDB:

     try
                {
                    System.Data.OleDb.OleDbConnection MyConnection ;
                    System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
                    string sql = null;
                    MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\csharp.net-informations.xls';Extended Properties=Excel 8.0;");
                    MyConnection.Open();
                    myCommand.Connection = MyConnection;
                    sql = "Insert into [Sheet1$] (id,name) values('5','e')";
                    myCommand.CommandText = sql;
                    myCommand.ExecuteNonQuery();
                    MyConnection.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show (ex.ToString());
                }

     But although the Jet OLE DB Provider allows you to insert and update records in an Excel workbook, it does not allow DELETE operations. If you try to perform a DELETE operation on one or more records, you receive the following error message :
    Deleting data in a linked table is not supported by this ISAM

    You can refer to this KB: http://support.microsoft.com/default.aspx?scid=kb;EN-US;316934

    If you want to delete row in excel in C#, you can refer to this link:
    http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/920180bf-1c84-40f7-b547-ba9532e309cd


    Vin Jin [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Proposed as answer by Aspen VJ Thursday, June 09, 2011 3:32 AM
    • Marked as answer by Aspen VJ Thursday, June 16, 2011 1:48 AM
    Thursday, June 09, 2011 3:32 AM

All replies

  • Hi Magma

    First of all we are declaring the ODBC namespace which we will use an Excel DNS; 

    using System.Data.Odbc;

    then we will write these codes for connecting to a specific Excel file;

    String strConn = @"Dsn=Excel Files;dbq=excel_file_path;defaultdir=excel_file_dir;driverid=1046;maxbuffersize=2048;pagetimeout=5";
                OdbcConnection objConn = new OdbcConnection(strConn);
                objConn.Open();
                OdbcDataAdapter adp = new OdbcDataAdapter("select * from [Sheet1$]", objConn);
                DataSet ds = new DataSet();
                adp.Fill(ds);
                dataGridView1.DataSource = ds.Tables[0];
                objConn.Close();

    Thats all by using these codes you have got the datas you wanted to...

    Cheers;)

    Regards,

    Umisha

    Source Code-World

    • Proposed as answer by Aspen VJ Thursday, June 09, 2011 3:32 AM
    • Marked as answer by Aspen VJ Thursday, June 16, 2011 1:48 AM
    Tuesday, June 07, 2011 11:40 AM
  • hello Umisha,

    thxs for your help, but still i need your help on insertion and deletion if you can help.

     

    Tuesday, June 07, 2011 12:44 PM
  • As mentioned by Umisha, you can use odbc connection and commands and do insert, update,delete, select same like from a database table.
    Malayalam SMS Website http://aspspider.info/smsmallu
    Tuesday, June 07, 2011 12:56 PM
  • Hi Magma09,

    Here is a way to insert data into excel using OleDB:

     try
                {
                    System.Data.OleDb.OleDbConnection MyConnection ;
                    System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
                    string sql = null;
                    MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\csharp.net-informations.xls';Extended Properties=Excel 8.0;");
                    MyConnection.Open();
                    myCommand.Connection = MyConnection;
                    sql = "Insert into [Sheet1$] (id,name) values('5','e')";
                    myCommand.CommandText = sql;
                    myCommand.ExecuteNonQuery();
                    MyConnection.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show (ex.ToString());
                }

     But although the Jet OLE DB Provider allows you to insert and update records in an Excel workbook, it does not allow DELETE operations. If you try to perform a DELETE operation on one or more records, you receive the following error message :
    Deleting data in a linked table is not supported by this ISAM

    You can refer to this KB: http://support.microsoft.com/default.aspx?scid=kb;EN-US;316934

    If you want to delete row in excel in C#, you can refer to this link:
    http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/920180bf-1c84-40f7-b547-ba9532e309cd


    Vin Jin [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Proposed as answer by Aspen VJ Thursday, June 09, 2011 3:32 AM
    • Marked as answer by Aspen VJ Thursday, June 16, 2011 1:48 AM
    Thursday, June 09, 2011 3:32 AM