如何从C＃更新Excel工作表行

I'm frustrated because i can't solve this problem...

我很沮丧,因为我无法解决这个问题......

Well, i has been making a desktop application in C#, i almost complete the application, But i have a big problem.

好吧,我一直在用C#制作桌面应用程序,我几乎完成了应用程序,但我有一个大问题。

My application import an excel workbook who has some information that it is showed in a Gridview. It works ok, but my app update or modified the data that is showed in a gridview, only one column that is called Balance.

我的应用程序导入一个excel工作簿,其中包含一些在Gridview中显示的信息。它工作正常,但我的应用程序更新或修改了gridview中显示的数据,只有一列称为Balance。

I want to update this column in my excel workbook, is like an update condition in SQL, for example I have this information that is imported to my app.

我想在我的Excel工作簿中更新此列,就像SQL中的更新条件,例如我将此信息导入到我的应用程序中。

If i modify in my app some row, for example the row that has the order "12345", i want to update in my excel workbook the column called Balance of that row.

如果我在我的应用程序中修改某行,例如具有订单“12345”的行,我想在我的excel工作簿中更新名为该行的Balance的列。

I has been trying with this code:

我一直在尝试使用此代码:

    string order = textBox1.Text;
    string balance = textBox2.Text;

    filePath = openFileDialog1.FileName;
    extension = Path.GetExtension(filePath);
    header = "Yes";
    OleDbConnection MyConnection = new OleDbConnection(conStr); ;
    OleDbCommand myCommand = new OleDbCommand();
    string sql = null;
    MyConnection.Open();
    myCommand.Connection = MyConnection;
    sql = "UPDATE [" + sheetName + "] SET [" + sheetName + "].[Order]=123 WHERE [" + sheetName + "].[Balance]=12313";
    myCommand.CommandText = sql;
    myCommand.ExecuteNonQuery();
    MyConnection.Close();

And if i debug the code it is the sentence that is executed, and i think that it is ok, but it doesn't works.

如果我调试代码它是执行的句子,我认为它没关系,但它不起作用。

UPDATE ['12234$'] SET ['12234$'].[Order]=123 WHERE ['12234$'].[Balance]=12313

It give me this error:

它给了我这个错误:

Message=No value given for one or more required parameters.
  Source=Microsoft Access Database Engine

2 个解决方案

#1

This is just a thought - but is your SQL-like string set up correctly?

这只是一个想法 - 但是你的SQL类似的字符串是否设置正确?

If you're trying to update the balance for a certain order, you'd want that to be in your SET command.

如果您尝试更新某个订单的余额,则需要将其置于SET命令中。

So, something along the lines of...

所以,有些东西......

UPDATE ['12234$'] SET ['12234$'].[Balance]=12313 WHERE ['12234$'].[Order]=12345

#2

Consider using an NuGet package to work with Excels instead. EPPlus is an excellent one.

考虑使用NuGet包来代替Excel。 EPPlus是一个很好的。

using (var package = new ExcelPackage(@"someFile.xlsx")
{
   var sheet = package.Workbook.Worksheets.First();
   sheet.Cells["E2"].Value = 12345.32M;
   package.Save();
}

Sql is no longer possible, but you can use Linq:

Sql不再可能,但你可以使用Linq:

using (var package = new ExcelPackage(@"someFile.xlsx")
{
   var sheet = package.Workbook.Worksheets.First();
   int rowIndex = sheet.Cells["C2:C5"].First(x => int.Parse(x.Value.ToString()) == 12345).Start.Row;
   const int balanceColumnIndex = 5;
   sheet.Cells[rowIndex, balanceColumnIndex].Value = 12313M;
   package.Save();
}

I wrote a series about EPPlus on my blog.

我在博客上写了一篇关于EPPlus的系列文章。

#1