How to insert a new row in excel using oledb c#.ne

问题:

I'm trying my best to insert a new data row in excel file. please have a look. i'm facing this problem using C#.net framework (3.5)

code:

try{
       string ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\rising rent\\csharp-Excel.xls;Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;MAXSCANROWS=15;READONLY=FALSE;ImportMixedTypes=Text'";
       OleDbConnection conn = new OleDbConnection(ConnectionString);
       conn.Open();
       OleDbCommand cmd = new OleDbCommand("INSERT INTO [Inventory$] (C_DATE) VALUES('555')",conn);
       cmd.ExecuteNonQuery();
       conn.Close();

}
catch (Exception ex)
{
       MessageBox.Show(ex.ToString());
}

Error is this please have a look and share your views

"System.Data.OleDb.OleDbException: Operation must use an updateable query. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at RisingRentACar.Inventory.button1_Click(Object sender, EventArgs e) in C:\Users\Hamza Hafeez\Documents\Visual Studio 2015\Projects\RisingRentACar\RisingRentACar\Inventory.cs:line 82"

回答1:

So your solution is close, and I know this is over four months old, but to help others out. I was having the same issue and finally got it to work.

You don't need all of that in the connection string. Here is what worked for me.

string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" 
+ FileNameAndPath + ";Extended Properties=\"Excel 12.0 Xml; HDR=YES\";";

"HDR=YES" means that the first row has header cells. You can insert by using column names.

Secondly the query has to have [] around the column names. Like:

command.CommandText = "Insert into [Sheet1$] ([ColumnName]) values('Value')";

Hope this helps others like me who looked at this post searching for an answer to this problem.

Here is my whole solution:

private void InsertData(List<string> columnNames, List<string> theValues)
{

    OleDbConnection connection = null;
    OleDbCommand command = null;
    string connectionString = "";
    string columns = "";
    string values = "";

    try
    {

        connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + txtDestination.Text + ";Extended Properties=\"Excel 12.0 Xml; HDR=YES\";";


        using (connection = new OleDbConnection(connectionString))
        {

            connection.Open();

            for (int index = 0; index < columnNames.Count; index++)
            {

                columns += (index == 0) ? "[" + Regex.Replace(columnNames[index], @"\t|\n|\r", "\"") + "]" : ", [" + Regex.Replace(columnNames[index], @"\t|\n|\r", "\"") + "]";
                values += (index == 0) ? "'" + Regex.Replace(theValues[index], @"\t|\n|\r", "\"") + "'" : ", '" + Regex.Replace(theValues[index], @"\t|\n|\r", "") + "'";

            }

            using (command = connection.CreateCommand())
            {

                command.CommandText = string.Format("Insert into [Sheet1$] ({0}) values({1})", columns, values);

                command.ExecuteNonQuery();


            }

        }

    }
    catch (Exception ex)
    {

        ProcessError(ex);

    }

}