none
Saving data from a winform into an excel sheet?

    Question

  • Hi.

    Im creating a simple windows form with some comboboxes, a date selector and a textbox.

    The values thats entered in all of those i want saved into an excel file, using the excel file as sort of a database.

    It should count up the row number on where to save, so next time someone enters info it should be on the next row.

    Any tips on how to do this? Tried some ways but never gotten it to work using Interop.Excel and Office.Core

     

    Please help!

    Monday, February 06, 2012 7:22 AM

Answers

  • Hello,

    If I got it right there is no need to show the Excel workbook within your application, so there is no need to count the rows on the client side. I'll suggest you try to connect to the Excel through OleDb:

    System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\Example.xls';Extended Properties=Excel 8.0;");
    OleDbCommand cmd = new OleDbCommand("insert into ...", connection);
    cmd.ExecuteNonQuery();
    

    Best regards

    Monday, February 06, 2012 8:25 AM
  • Hi,

     

    For performing a database action you need to open a connection. Please do a connection.Open() in your code.

    Thanks,

    Raneesh

     


    ran
    Monday, February 06, 2012 1:28 PM
  • Hi antiHERO_,

    Welcome to the MSDN forum!

    How is it going with the problem currently?

    You may refer to the following threads at the same time:

    How to insert data into excel 2007 using OleDbConnection in C# 
    http://social.msdn.microsoft.com/Forums/en-GB/csharplanguage/thread/8ff16ba8-7aa2-4288-a43c-8a14e3c31522             

    "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 "

    How to save received data into either Excel or Database
    http://social.msdn.microsoft.com/forums/en-us/Vsexpressvb/thread/92F06CE9-F8E6-4675-93C2-736EBC46FD36

    If you need any help, please feel free to let us know.

    Have a nice day!

    yoyo


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, February 08, 2012 1:45 AM

All replies

  • Hello,

    If I got it right there is no need to show the Excel workbook within your application, so there is no need to count the rows on the client side. I'll suggest you try to connect to the Excel through OleDb:

    System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\Example.xls';Extended Properties=Excel 8.0;");
    OleDbCommand cmd = new OleDbCommand("insert into ...", connection);
    cmd.ExecuteNonQuery();
    

    Best regards

    Monday, February 06, 2012 8:25 AM
  • That's right, the application isnt showing the workbook at all, its just used for storage.
    Tried using your code, but i got an error: "ExecuteNonQuery requires an open and available Connection. The connection's current state is closed."

    Im sorry, but im quite new to this. Trying to learn though :)

     

    Thank you for your help!

    Monday, February 06, 2012 11:38 AM
  • Hi,

     

    For performing a database action you need to open a connection. Please do a connection.Open() in your code.

    Thanks,

    Raneesh

     


    ran
    Monday, February 06, 2012 1:28 PM
  • Hi antiHERO_,

    Welcome to the MSDN forum!

    How is it going with the problem currently?

    You may refer to the following threads at the same time:

    How to insert data into excel 2007 using OleDbConnection in C# 
    http://social.msdn.microsoft.com/Forums/en-GB/csharplanguage/thread/8ff16ba8-7aa2-4288-a43c-8a14e3c31522             

    "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 "

    How to save received data into either Excel or Database
    http://social.msdn.microsoft.com/forums/en-us/Vsexpressvb/thread/92F06CE9-F8E6-4675-93C2-736EBC46FD36

    If you need any help, please feel free to let us know.

    Have a nice day!

    yoyo


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, February 08, 2012 1:45 AM