如何使用oledb C#.net在Excel中插入新行

问题描述

我正在尽力在excel文件中插入新的数据行.请看一看.我使用C#.net框架(3.5)面对此问题

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)

代码:

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

推荐答案

所以您的解决方案已经接近完成，我知道这已经超过四个月了，但是可以帮助其他人.我遇到了同样的问题，终于让它起作用了.

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"表示第一行具有标题单元格.您可以使用列名进行插入.

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

    }

}

这篇关于如何使用oledb C#.net在Excel中插入新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！